> I understand that postgres is pretty decent at storing large binary data though?
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.
I've never used PostgreSQLs large objects. You have to use this awkward API to use them, they aren't stored efficiently either, and as far as I can tell it's just a legacy feature that nobody has bothered to deprecate yet.
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.
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.