Most (all?) large-scale high-volume image storage architectures I'm aware of do not store billions of images as blobs in a database. Instead, they typically store the images as files. The database only holding metadata info and a pointer to the image filename location (or url if using AWS S3 or CDN).
E.g. Facebook is one of the largest MySQL sites (if not the largest) and they don't store photos directly in MySQL.
TWO more images were added today! At this rate, I'm going to need to buy a whole datacenter by next week.
EDIT: I uploaded an image into your database https://imgz.org/i3joC5jC/
Not sure is it some intentional irony or an actual bug.
I've been meaning to investigate trying storing images in postgres on a hobby web project where it could be convenient; I'd still want to make sure I was streaming bytes from postgres to the client, not loading the whole image into memory and only once fully loaded sending it to the client. Looking at the pg API's for my language/platform of choice, it looked at least plausible to do this with a BLOB.
Storing large files in PostgreSQL is possible, but I would advise against it.
PostgreSQL uses a technique called TOAST to store large objects. If you store something in a bytea column, it'll first be compressed (either deflate or a new algorithm that I forgot the name). Then it will be split into 8KB chunks (page size) and stored together with all the other data.
Since most image formats are already compressed, this just wastes a lot of CPU cycles.
Also, the way storage is allocated in PostgreSQL may bite you in the ass. By default, storage is not returned to the OS. If you delete all images, the data will stay on disk and marked as reusable. Only if you do a full rewrite of the table will the free space be made available again.
Also, backups will become a pain. The easiest way to backup PostgreSQL servers is to just do a pg_dump. It works great up to a few gigabytes of data. If you have images or other large objects in the database, then pg_dump will quickly become unfeasible and you will have to find another way to do backups. Not a deal breaker, but something you should be aware.
Also, for best performance, the database should be on fast storage (eg. NVME SSD), whereas the image data typically doesn't need fast storage.
## A Practical Example:
One project I work on is an image database with 50k images. The high resolution previews are around 20GB. The PostgreSQL database with all the metadata is around 20MB.
It takes 10 seconds to do a full backup of the database. Everytime I change something in the application, I first create a dump of the database (which is just a few MB compressed). Then I do my changes, and if I make a mistake I always have a backup that I could restore in 10 seconds.
If the images were stored in the PostgreSQL database, a full backup would take much longer and would require a fast internet connection. And it would be much harder to work with.
My recommendation: Use the filesystem for storing files.
Size/speed of backups is a good point to consider.
In my opinion the only neat thing about large objects is that you can edit files with a posix-like API in a database transaction, but I can't think of a scenario where that would actually be useful.
If you want to commit ten things every second all week, don't transfer so much data in a single transaction that you hold any important locks for half a minute. And if you use replication, test while replicating.
If the blobs you store are small enough to not disturb your commit rate, then pg's drawbacks are IMO smaller than those of the alternatives, particularly if you want some sort of commit that returns when both image data and something else have been committed.
So you've stored images in the db in production and had it work out fine?
I don't want to commit ten things a second. It would be a pretty read-heavy write-light load. Sounds like that would avoid one path of riskiness at least.
Many people answer a question like this: "How good is postgres at storing big blobs, relative to small things?" Postgres is indeed worse at storing large things. However, I think the most interesting question is something along these lines: "What's best, storing everything in Postgres and accepting that inefficiency for the big blobs, or implementing transactions, replication and/or backup with a part of the data in Postgres and the big blobs elsewhere?"
I'm awfully fond of having working backups, and postgres' performance problems with the blobs haven't been big enough that I've really noticed.
The problems relate mostly to backups/copies/reloads, that kind of thing, and copying an image doesn't cause anything to hiccup. A transaction that transferred 1MB over a modem line was a problem, that could block other commits for a long time, on today's networks 1MB doesn't take long.
Binary data in databases is usually not a great idea, but tolerable in low amounts.
I can't imagine storing images in a database, you're probably better off with a document store.
Relational Databases (well, the ones that I know the internals of) tend to have soft limits on row sizes, in PostgreSQL this is 8kb (which is very small for an image); to overcome this limitation Postgres uses what they call a TOAST table, which forces compression by default, which absolutely crumbles on binary data.
https://www.postgresql.org/docs/13/storage-toast.html
But, if it works, I'm not complaining- and I'm very certain that other such sites have worse architectural flaws in the beginning- this one is pretty easy to overcome if it becomes an issue and can be alleviated with aggressive caching of CDNs/varnish to buy you time if there's immediate issues. :)
> The large object implementation breaks large objects up into “chunks” and stores the chunks in rows in the database. A B-tree index guarantees fast searches for the correct chunk number when doing random access reads and writes.
You might try Foreign Data Wrapping.
In which case I still wouldn't because the database is going to be spending more time in doing round trips, better my application server does that, there's usually more of them.
But yes, there's probably time for that optimization later, if the service takes off.
Whoa now... you're putting images in the database?