store big files directly in database or file system?

[getget]

Could any one give some comments on the better way to store files, like pdf, doc and image, in database or file system? What are their advantages and disadvantages respectively ?
A decision should be made about saving files, but I am not sure which approach I should follow.
Thanks.

[Sxooter]

It all depends. Generally, the best way to handle this is to store the path in the database and the file elsewhere. I.e. if you store a pdf in the directory /files/pdf001.pdf you could then store '/files/pdf001.pdf' in your database and use that to refer to it. The advantage here is that you are storing a large pdf somewhere other than your database, which means your database can use its cache and shared memory to hold only important data, like that path we referenced.

On the other hand, you might have 6 front end servers and want to have all data served from a database, period, and in that setup you would build on big old honkin db server with lots of ram to store this stuff.

Basically, storing large things in your database increases the load on the database server, so if you're gonna do it, you might need to increase the horsepower of your database server.

[malcruzg]

Hi,
I'm confused 😕

Postgres supports BLOB? By the OID or sblob?

Anyone could have an example of up-downloading files to a postgres dbserver from PHP?

Very thanks in advance! 🙂

[Sxooter]

My recommendation is to base64 encode your data and store it in a varchar / text field. Then it's easy to transport your code from one db to another.

Large objects, as they are called, were introduced to overcome the issues originally presented by the old 8k row limit, now long since gone. Since the size limit of a field in a row is ~2 gigabytes, you may as well just store it as base64 (or otherwise encoded) in a varchar or bytea encoded.

Note that postgresql AUTOMATICALLY compresses easily compressable text, which base64 encoded is, so you don't have to worry about the encoding overhead much, if any.