Saving Images in PostgreSQL

Question

Well, that’s why I’m working on an application that will use a PostgreSQL-based Linux server to serve images in a Windows window with an interface written in C # .NET, although the interface should not matter much. My question is:

• What is the best way to store images in Postgres?

Images are about 4-6 megapixels each, and we save up to 3000. It would also be nice to note: this is not a web application in which there will be almost two front access to the database at once.

Answer 1

An update to 2012, when we see that the size and number of images grow and grow in all applications ...

We need to distinguish between the “original image” and the “processed image”, for example, a thumbnail.

As Jcoby's answer says, there are two options, so I recommend:

• use blob (Binary Large OBject): to store the original image on your desk. See Ivan's answer (no problem with backing up blobs!), Additional PostgreSQL modules supplied , instructions , etc.

• use a separate database with DBlink : to store the original image in another (unified / specialized) database. In this case, I prefer byte, but the drop is almost the same. Partitioning the database is the best way for a "unified web image service."

• Use bytea (BYTE Array): to cache image thumbnails. Cache small images to quickly send them to a web browser (to avoid rendering problems) and reduce processing on the server. Cache also important metadata, such as width and height. Database caching is the easiest way, but check your needs and server settings (for example, Apache modules): storing thumbnails in the file system may be better, compare performance. Remember that this is a (unified) web service that can be stored in a separate database (without backups) serving many tables. See also PostgreSQL Binary Data Types Manual , bytea column tests , etc.

NOTE 1: today the use of "dual solutions" (database + file system) is not recommended (!). There are many advantages to using "database only" instead of double. PostgreSQL has comparable performance and good export / import / input / output tools.

NOTE 2. Remember that PostgreSQL has only bytea, but does not have Oracle BLOB by default: "The SQL standard defines (...) BLOB. The input format is different from bytea, but the functions and operators provided are basically the same," Guide .

---

UPDATE 2014 : I did not change the source text above today (my answer was April 22nd '12, now with 14 votes), I open the answer for your changes (see "Wiki mode", you can change!), For proofreading and for updates .
Stable question (@Ivans '08 answer with 19 votes), please help improve this text.

Answer 2

Re jcoby answer:

bytea, which is a “normal” column, also means that the value is read completely into memory when it is retrieved. Blobs, in contrast, you can pass to stdout. This helps reduce server memory. Especially when you save 4-6 MPix images.

No backup issues. pg_dump provides the -b option to include large objects in the backup.

So, I prefer to use pg_lo_ *, you can guess.

Chris Erickson's answer:

I would say the opposite :). When images are not the only data that you store, do not store them in the file system unless you need to. Such an advantage is always to be aware of your data consistency and have data "in one piece" (DB). BTW, PostgreSQL is great for maintaining consistency.

However, the truth is that reality is often too demanding on performance ;-) and this forces you to serve binary files from the file system. But even then, I am inclined to use the database as the “main” storage for binary files, and all other relations are sequentially connected to each other, while providing some kind of file system-based caching mechanism to optimize performance.

Answer 3

There are two options in the database:

• BYTEA. Saves data in a column exported as part of a backup. Uses standard database functions for saving and retrieving. Recommended for your needs.
• clots. It stores data from the outside, it is usually not exported as part of a backup. Special database functions required for saving and retrieving.

I have used bytea columns with great success in the past, saving 10 + gb images with thousands of rows. PG TOAST functionality pretty much denies any advantage blobs have. You will need to include metadata columns anyway for the file name, content type, size, etc.

Answer 4

Quick update to mid-2015:

You can use the Postgres Foreign Data interface to store files in a more suitable database. For example, put the files in GridFS, which is part of MongoDB. Then use https://github.com/EnterpriseDB/mongo_fdw to access it in Postgres.

This has the advantage that you can access / read / write / reserve it in Postrgres and MongoDB, depending on what gives you more flexibility.

There are also third-party data wrappers for file systems: https://wiki.postgresql.org/wiki/Foreign_data_wrappers#File_Wrappers

As an example, you can use this: https://multicorn.readthedocs.org/en/latest/foreign-data-wrappers/fsfdw.html (see here for a brief usage example)

This gives you the advantage of consistency (all related files are necessarily present) and all other ACIDs, while they still exist on the actual file system, which means that you can use any file system you want and the web server can serve them directly (OS caching is also applicable).

Answer 5

Update from 10 years later. In 2008, the hard drives on which you will work with the database would have completely different characteristics and much higher cost than the drives on which you will store files. These days there are much better solutions for storing files that were not there 10 years ago, and I would cancel this advice and advise readers to take a look at some other answers in this thread.

Original

Do not store images in the database if you do not need it. I understand that this is not a web application, but if there is no common file location that you can point to the location of the file in the database.

//linuxserver/images/imagexxx.jpg 

then maybe you can quickly set up the web server and save the website URLs in the database (as well as the local path). While databases can handle LOB and 3000 images (4-6 megapixels, assuming a 500K image), 1.5 gigabytes are not many, space file systems are much better designed to store large files than a database.

Answer 6

Try it out . I use the binary large object (LOB) format to store the generated PDF documents, some of which are 10+ MB in size, in the database, and this works great.

Answer 7

If your images are small, try saving them as base64 in the text box.

The reason is that while base64 has an overhead of 33%, the compression basically disappears. (See What space does Base64 encode? ) Your database will be larger, but there will be no packets sent by your web server to the client. In html, you can embed base64 in the & lt; tag img src = "">, which can simplify your application, since you will not need to process the images as a binary file in a separate browser selection. Processing images as text also simplifies the process of sending / receiving json, which does not handle binary files very well.

Yes, I understand that you can store the binary in the database and convert it to / from text when you enter and exit the database, but sometimes ORMs create difficulties. It may be easier to think of it as plain text, like all other fields.

This is definitely the right way to handle thumbnails.

(OP images are not small, so this is not exactly the answer to his question.)