Are there performance issues storing files in PostgreSQL?

Question

Is ok storing files like HTML pages, images, PDF, etc in a table in PostgreSQL or it is slow? I read some articles saying that this is not recommended, but I don't know if is true.

The column types I have in mind are BLOB (as far as I know it stores in a file) or bytea type, but others are applicable also.

Answer 1

You have basically two choices. You can store the data right in the row or you can use the large object facility. Since PostgreSQL now uses something called TOAST to move large fields out of the table there should be no performance penalty associated with storing large data in the row directly. There remains a 1 GB limit in the size of a field. If this is too limited or if you want a streaming API, you can use the large object facility, which gives you something more like file descriptors in the database. You store the LO ID in your column and can read and write from that ID.

I personally would suggest you avoid the large object facility unless you absolutely need it. With TOAST, most use cases are covered by just using the database the way you'd expect. With large objects, you give yourself additional maintenance burden, because you have to keep track of the LO IDs you've used and be sure to unlink them when they're not used anymore (but not before) or they'll sit in your data directory taking up space forever. There are also a lot of facilities that have exceptional behavior around them, the details of which escape me because I never use them.

For most people, the big performance penalty associated with storing large data in the database is that your ORM software will pull out the big data on every query unless you specifically instruct it not to. You should take care to tell Hibernate or whatever you're using to treat these columns as large and only fetch them when they're specifically requested.