From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Big image tables maintenance
Date: Mon, 17 Sep 2018 08:45:17 -0500
Message-ID: <d056c4b6-30d5-25de-37cc-107a2382ae96@gmail.com> (raw)
In-Reply-To: <CAB3gP9JOKratDxJ54X+DR4axeK0k9ZO2wFw2_PiNovknCB8DsQ@mail.gmail.com>
References: <CAB3gP9JOKratDxJ54X+DR4axeK0k9ZO2wFw2_PiNovknCB8DsQ@mail.gmail.com>
On 09/17/2018 07:38 AM, still Learner wrote:
> Hi ,
>
> 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.
>
> 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.
> so thinking of using any no-sql mostly mongo-DB, is it recommended? Or
> PostgreSQL itself can handle?
>
> 3. Taking the backup of 20TB data, is big task. Any more feasible solution
> other than online backup/pg_dump?
pgbackrest and barman are popular options.
(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...)
>
> Each image retrieval is
> Currently, we are on pg 9.4 and moving to 10.5 soon.
> Thanks,
> GJ.
--
Angular momentum makes the world go 'round.
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: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Big image tables maintenance
In-Reply-To: <d056c4b6-30d5-25de-37cc-107a2382ae96@gmail.com>
* 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