Re: Big image tables maintenance

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.

They're automatically stored separate, see 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? I've stored millions of documents on a Mac mini. Real server hardware & OS should have no problem--10TB is really not all that much.

> so thinking of using any no-sql mostly mongo-DB, is it recommended? Or PostgreSQL itself can handle?

Only if all you need is the document storage, none of everything else PG offers.

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

That's an argument for keeping the presumably immutable files on the file system. (There are arguments against as well.)