Thread: pdf saving into DB vs. saving file location ?
Hello list, A question about saving PDF (size around 160kb) into postgresql large object columns vs. saving into a directory. May I know the performance differences? Pros and crons please? Each year, 20 new pdfs will be saved into DB (each around 160KB). Thanks a lot! Emi
In response to Emi Lu <emilu@encs.concordia.ca>: > Hello list, > > A question about saving PDF (size around 160kb) into postgresql large > object columns vs. saving into a directory. > > May I know the performance differences? Pros and crons please? > > Each year, 20 new pdfs will be saved into DB (each around 160KB). We store a WHOLE lot more binary data in our DBs than that. The big advantage to us is that we store the data in TEXT fields, and use Slony to replicate it, so in the event of hardware failure, we don't have to worry about the DB looking for data that isn't on the file server. Never measured the actual performance difference, but we haven't had any performance issues with keeping the data in the DB. I'm sure keeping it on the filesystem is faster, though. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On Wed, 2011-03-23 at 16:01 -0400, Emi Lu wrote: > > A question about saving PDF (size around 160kb) into postgresql large > object columns vs. saving into a directory. > > May I know the performance differences? Pros and crons please? > > Each year, 20 new pdfs will be saved into DB (each around 160KB). This question *might* make sense if you were storing 200 new PDFs per day. OS will probably cache the PDFs if you are serving it much enough. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
On Wed, Mar 23, 2011 at 04:05:44PM -0400, Bill Moran wrote: > We store a WHOLE lot more binary data in our DBs than that. The > big advantage to us is that we store the data in TEXT fields, and > use Slony to replicate it, so in the event of hardware failure, we > don't have to worry about the DB looking for data that isn't on > the file server. Also, of course, you don't have to cope with keeping your filesystem's and database's versions of the world in sync. If you put the data in the database, it follows the transaction rules of the rest of the transaction. If you put it on the filesystem, you have to manage that yourself. A -- Andrew Sullivan ajs@crankycanuck.ca
On Wed, Mar 23, 2011 at 4:01 PM, Emi Lu <emilu@encs.concordia.ca> wrote: > A question about saving PDF (size around 160kb) into postgresql large object > columns vs. saving into a directory. > > May I know the performance differences? Pros and crons please? > > Each year, 20 new pdfs will be saved into DB (each around 160KB). > Just store them in bytea fields. No need for large objects at all. We store images this way, and put a CDN in front of the web server that pulls them from the DB, so the DB is rarely hit for such things. The benefit of having everything managed within a single transaction to the DB is worth the complexity of having the CDN. Seeing as you only have about 20 per year, that's just a no-brainer to store them in the DB.