3

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.

Sam Carleton
  • 1,339
  • 7
  • 23
  • 45
  • 2
    What will you we using these for? For some cases, it will be better to store each image as a file, and just store the path in the database. – Paul Draper Nov 03 '13 at 01:20
  • Adding to @DanielVérité's comment, you could also use a foreign data wrapper nowadays. – Denis de Bernardy Nov 03 '13 at 09:16
  • @PaulDraper: I did a quick benchmark once with a web application and it showed that for files not exceeding several MB (I don't recall the actual break even point) storing them in Postgres is actually (slightly) faster than using the file system. For SQL Server this seems the same: http://research.microsoft.com/apps/pubs/default.aspx?id=64525 (my tests were inspired by that Microsoft article) –  Nov 03 '13 at 10:34
  • @a_horse_with_no_name, nice article. – Paul Draper Nov 03 '13 at 10:36
  • To display the images in a web application. The current version keeps all the images on the hard drive and it is simply too much to manage. One of the things that will happen is each grouping of images will be stored in a separate tablespace/schema so that when the user is done with a given group, it is quick and easy to drop the tablespace/schema. If I understand PosgreSQL correctly, this will also simplify backups of a specific grouping. – Sam Carleton Nov 03 '13 at 14:26

1 Answers1

3

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.

AndreaBoc
  • 3,077
  • 2
  • 17
  • 22