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