From: Stephen Frost <sfrost@snowman.net>
To: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Big image tables maintenance
Date: Mon, 17 Sep 2018 10:01:08 -0400
Message-ID: <20180917140108.GV4184@tamriel.snowman.net> (raw)
In-Reply-To: <d056c4b6-30d5-25de-37cc-107a2382ae96@gmail.com>
References: <CAB3gP9JOKratDxJ54X+DR4axeK0k9ZO2wFw2_PiNovknCB8DsQ@mail.gmail.com>
<d056c4b6-30d5-25de-37cc-107a2382ae96@gmail.com>
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 columns (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. Is it possible? Doing this it should not
> >affect the performance.
>
> That's called "vertical partitioning", which I don't think Postgres supports.
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 system as the count is huge,
>
> 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_dump
> runs a *lot* faster with "--compress=0". The backups are 2.25x larger 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
Attachments:
[application/pgp-signature] signature.asc (818B, ../20180917140108.GV4184@tamriel.snowman.net/2-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sfrost@snowman.net, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Big image tables maintenance
In-Reply-To: <20180917140108.GV4184@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox