Saving Documents in PG vs File system

ADJob mats at
Wed Nov 14 13:04:41 EST 2018


> A question for those of you who store documents in your Omnis system.  We currently do, and we have a separate DB for the storage, and keep the metadata in the main database.
> However, in certain cases, the PG database size is now over 100gb when exported.  And 99% of that data is static, so a waste of time in backing it up etc every day.
> 1	I could split the database into 2 parts as well, 1 for new items, which will periodically move items to the 2nd archive database.
> 2	But I want to explore storing the files in the file system, and passing them to the user when asked for.  Can PostGres save files locally, and then read team and send them back to a user within a SQL call?

AFAIK Postgresql cannot interact with the operating system. I think you will have to use Python, Go or any other app to do this stuff. (I have been told)

> 3	I also remember Marten Verhoven showing how he uses Symbolic links to files that would normally be hidden AND inaccessible unless given.
> So what do others do at this point with numbers of docs exceeding 100Gb now?

Store max 100 K files each within the database as large_objects. Bigger files should IMHO be stored in the file system with some sort of reference in the database.



More information about the omnisdev-en mailing list