Date: Mon, 17 Sep 2018 10:01:08 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Big image tables maintenance
Message-ID: <20180917140108.GV4184@tamriel.snowman.net>
References: 
 <CAB3gP9JOKratDxJ54X+DR4axeK0k9ZO2wFw2_PiNovknCB8DsQ@mail.gmail.com>
 <d056c4b6-30d5-25de-37cc-107a2382ae96@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="I+BhFrIa1PqbPgjZ"
Content-Disposition: inline
In-Reply-To: <d056c4b6-30d5-25de-37cc-107a2382ae96@gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--I+BhFrIa1PqbPgjZ
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Ron (ronljohnsonjr@gmail.com) wrote:
> On 09/17/2018 07:38 AM, still Learner wrote:
> >I have a 10 TB size table with multiple bytea=A0columns (image & doc)and
> >makes 20TB of DB size. I have a couple of issues to maintain the DB.
> >
> >1. I Would like to separate the image column from the 10TB size table,
> >place it in a separate schema. The change should not result in any query
> >change in the application.=A0 Is it possible? Doing this it should not
> >affect the performance.
>=20
> That's called "vertical partitioning", which I don't think Postgres suppo=
rts.

As mentioned, PostgreSQL will already do this for you with TOAST, but
even without that, you could certainly create a simple view..

> >2. I can't maintain files on File=A0system as the count is huge,
>=20
> Eh? *You* aren't supposed to maintain the files on the filesystem;
> *Postgres* is.

I believe the point being made here is that pushing the images out of PG
and on to the filesystem would result in a huge number of files and that
would be difficult for the filesystem to handle and generally difficult
to work with.

> (We have a database like yours, though only 3TB, and have found that pg_d=
ump
> runs a *lot* faster with "--compress=3D0".=A0 The backups are 2.25x large=
r than
> the database, though...)

Unfortunately, your restore time with a pg_dump-based backup is very
high and that's something that I don't think enough people think about.

Having both pgBackRest-based physical backups and pg_dump-based backups
is nice as it allows you to do selective restore when you need it, and
fast full restore when needed.  Of course, that requires additional
storage.

Note that pg_dump/pg_restore also support parallelism, which can help
with how long they take to run.

Thanks!

Stephen

--I+BhFrIa1PqbPgjZ
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJbn7OkAAoJEO1sijiDR2RVs8oQAIeWftiZDCDX3LD1Fc/oEaub
L4Y1X4LJ5GZSDMm5MMTGFRvge0Spno961XGPH1NxaLm8SrNQ+Ygjn3UuIzUOMB8S
M7r1CqIPmoI8Mrzvt1DnjN9MFI0+v/lntA7kgvN5h4n3cyn/Vm4/C+TZ4Agmn0T2
VuA39ETzuMyV9acOi+ArKkcBxj78WBdQI0CZU3F3S5Vn+DXKOpKApHBOYkF9WmCL
rWDO+ap1wyZ0t0ZlU2BGQ8tfVTtBjq14UcYSVuY0srJ6fvREIqBKEK7meuiwdYgV
RHsnsjJxYyXAVE1Q9ulCIgHplP6dHA5T1nHbo8u+RyQlTQK2352AWT+zqHUysDBY
L8UhJUqVVySEu/GoZDr+r3ZIQSsvL3raW5Vs7zbXayA+abCyVwu+JyKr8ewqHjJ4
qgqat2rgjmByJ2ESZLpee/I7R4kHhK8twy1mAoXiHlES+i/w00BuQ1R7s0BDa9kc
afYDlPLu2MjXebMa2EVSHdiZ2hdBoJZ6VvcQHpo8Lv9X4znR6z5kxDhX4bIvapyU
XwO2cg4qKNx8rv51yNTHkRMnOiuCnZ+8JH0ygTvPjodsHKlfiCMLl0o9lMaEvr/H
l6SbPd/ZemDWcGlGI2sVq/4P9AlZzNOahegL4+8oSa5a3Cwhe8hXiD7es49x7QOj
9svPGoMZ47T9Dw4kd/bf
=awlk
-----END PGP SIGNATURE-----

--I+BhFrIa1PqbPgjZ--