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
issuesto 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
shouldnot 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
notall 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.)