Date: Mon, 17 Sep 2018 09:58:20 -0400
From: Stephen Frost <sfrost@snowman.net>
To: still Learner <stilllearner23@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Big image tables maintenance
Message-ID: <20180917135820.GU4184@tamriel.snowman.net>
References: 
 <CAB3gP9JOKratDxJ54X+DR4axeK0k9ZO2wFw2_PiNovknCB8DsQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="9tXCsIaeOidoWqu8"
Content-Disposition: inline
In-Reply-To: 
 <CAB3gP9JOKratDxJ54X+DR4axeK0k9ZO2wFw2_PiNovknCB8DsQ@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--9tXCsIaeOidoWqu8
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greetings,

(limiting this to -admin, cross-posting this to a bunch of different
lists really isn't helpful)

* still Learner (stilllearner23@gmail.com) wrote:
> I have a 10 TB size table with multiple bytea columns (image & doc)and
> makes 20TB of DB size. I have a couple of issues to maintain the DB.

*What* are those issues..?  That's really the first thing to discuss
here but you don't ask any questions about it or state what the issue is
(except possibly for backups, but we have solutions for that, as
mentioned below).

> 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.  Is it possible? Doing this it should not affect
> the performance.

How large are these images?  PostgreSQL will already pull out large
column values and put them into a side-table for you, behind the scenes,
using a technique called TOAST.  Documentation about TOAST is available
here:

https://www.postgresql.org/docs/current/static/storage-toast.html

> 2. I can't maintain files on File system as the count is huge, so thinking
> of using any no-sql mostly mongo-DB, is it recommended? Or PostgreSQL
> itself can handle?

I suspect you'd find that your data size would end up being much, much
larger if you tried to store it as JSON or in a similar system, and
you're unlikely to get any performance improvement (much more likely the
opposite, in fact).

> 3. Taking the backup of 20TB data, is big task. Any more feasible solution
> other than online backup/pg_dump?

Absolutely, I'd recommend using pgBackRest which supports parallel
online backup and restore.  Using pg_dump for a large system like this
is really not a good idea- your restore time would likely be
particularly terrible and you have no ability to do point-in-time
recovery.  Using pgBackRest and a capable system, you'd be able to get a
complete backup of 20TB in perhaps 6-12 hours, with similar time on the
recovery side.  If you wish to be able to recover faster, running a
replica (as well as doing backups) may be a good idea, perhaps even a
time-delayed one.

> Each image retrieval is

Unfinished thought here..?

> Currently, we are on pg 9.4 and moving to 10.5 soon.

That's definitely a good plan.

Thanks!

Stephen

--9tXCsIaeOidoWqu8
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJbn7L8AAoJEO1sijiDR2RVQSgP/Rnl1lEzKJOFifs+c8/cbKt/
SGLjX0RjieFRtCzMuIpgQRGEsUOvU4l0OrcKtJbRPHHg0aocWBhho+chJQbxaO+z
+c21xuwCPRKO6sZza/a6JY2oHbWkL04ouqxVOe64Uhjl/93PF8i4outpnI+5ytWv
xJ9GUT/WHtK8CfLzM7b7ZxS9m95b4pTPXNo9synEJZY/CqTHoqUNaFtQ3tkDXCD/
NOOqMuWsJJ3RgOTEAs8/2jlZAudqRA/eIfmTPoNPCGESJNDN9DYxCJRvFARRWNwR
1MuuJdLLbZxsw1x7HoUZ+ZYKsccE9XmmYNvXm5nWR+2sptMCADXnF+OfBCfHZ/0L
F5tVIB/biyTH+uzblhfdItAePoHSeEpwKfS0mR4L8SHqnZIU41DCZAdnCFb69scf
bRK/0mXQ9HL+06myyHB8WSdUyg6CPyFxWQ7xMZtP8qFPRH+Cdlg7kHBMnPMKj1Kk
yhINWgg2XiGSe2OgsSYqBbFTgJZUWgST0okHYbFTCBJsoArNkHTW1vMn0WS/8S52
5Qa9NrxAaROJdWUjJ1ixls4DqZVuPEtwxH8ZsWeB3u/8bW6n0gpY3vsG47okuEly
NojHjJDVt/unW679H+vougTlzGCaYPoTjc4sLirE8Hlt0mF2qtNsJGLwGKHNMTtT
G2OpnCzgIjnEw3btwu1e
=oFLr
-----END PGP SIGNATURE-----

--9tXCsIaeOidoWqu8--