Best way to store images in PostgreSQL

Question

I am working on a web site that will start off with hundreds of thousands of images in it to hopefully hundreds of millions. Most of the images won't exceed 300K in physical size. What is the best way to store these in PostgreSQL? One thing I know for sure is that the system will be using schemas, partitions, and tablespaces to manage storage.

Solution

If the images do not exceed 300Kb, the easiest way is to use a bytea field. The images are saved in the database: a simple backup will include the images.

Or, PostgreSQL can handle large objects: http://www.postgresql.org/docs/9.3/static/largeobjects.html with which the images will be stored externally to the database.

Alternatively, you can leave the images in the file system, save in a text field the path and use two stored functions (for example in pl/python) that allow you to write and read the external file to store and retrieve images.