Storing Images in PostgreSQL

by

Updating to 2012, when we see that image sizes, and number of images, are growing and growing, in all applications…

We need some distinction between “original image” and “processed image”, like thumbnail.

As Jcoby’s answer says, there are two options, then, I recommend:

  • use blob (Binary Large OBject): for original image store, at your table. See Ivan’s answer (no problem with backing up blobs!), PostgreSQL additional supplied modules, How-tos etc.

  • use a separate database with DBlink: for original image store, at another (unified/specialized) database. In this case, I prefer bytea, but blob is near the same. Separating database is the best way for a “unified image webservice”.

  • use bytea (BYTE Array): for caching thumbnail images. Cache the little images to send it fast to the web-browser (to avoiding rendering problems) and reduce server processing. Cache also essential metadata, like width and height. Database caching is the easiest way, but check your needs and server configs (ex. Apache modules): store thumbnails at file system may be better, compare performances. Remember that it is a (unified) web-service, then can be stored at a separate database (with no backups), serving many tables. See also PostgreSQL binary data types manual, tests with bytea column, etc.

NOTE1: today the use of “dual solutions” (database+filesystem) is deprecated (!). There are many advantages to using “only database” instead dual. PostgreSQL have comparable performance and good tools for export/import/input/output.

NOTE2: remember that PostgreSQL have only bytea, not have a default Oracle’s BLOB: “The SQL standard defines (…) BLOB. The input format is different from bytea, but the provided functions and operators are mostly the same”,Manual.

EDIT 2014: I have not changed the original text above today (my answer was Apr 22 ’12, now with 14 votes), I am opening the answer for your changes (see “Wiki mode”, you can edit!), for proofreading and for updates.
The question is stable (@Ivans’s ’08 answer with 19 votes), please, help to improve this text.

More Related Contents:

  1. How to reset postgres’ primary key sequence when it falls out of sync?
  2. PostgreSQL LIKE query performance variations
  3. Calculating and saving space in PostgreSQL
  4. Passing user id to PostgreSQL triggers
  5. Do nullable columns occupy additional space in PostgreSQL?
  6. how to emulate “insert ignore” and “on duplicate key update” (sql merge) with postgresql?
  7. How do I efficiently select the previous non-null value?
  8. How do I do large non-blocking updates in PostgreSQL?
  9. How to configure PostgreSQL to accept all incoming connections
  10. Postgresql : Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections [duplicate]
  11. Dynamic SQL (EXECUTE) as condition for IF statement
  12. uWSGI, Flask, sqlalchemy, and postgres: SSL error: decryption failed or bad record mac
  13. Prevent recursive trigger in PostgreSQL
  14. serial in postgres is being increased even though I added on conflict do nothing
  15. Can’t connect to Postgresql on port 5432
  16. Spark Dataframes UPSERT to Postgres Table
  17. PostgreSQL Error: Relation already exists
  18. How to make a select with array contains value clause in psql
  19. Optimize Postgres query on timestamp range
  20. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  21. Update timestamp when row is updated in PostgreSQL
  22. Postgresql query array of objects in JSONB field
  23. Multicolumn index on 3 fields with heterogenous data types
  24. Select from a table variable
  25. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  26. input file appears to be a text format dump. Please use psql
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. Spring Docker container cannot access Postgres Docker container
  29. Format specifier for integer variables in format() for EXECUTE?
  30. PostgreSQL composite primary key

Leave a Comment