> After some performance checks it became clear that PostgreSQL was even faster than reading from the file system for our use-case. PostgreSQL uses the file system very efficiently for its data - and it adds a lot of caching and efficient reading and writing strategies that can outperform writing and reading raw data on a file system.
This goes against conventional knowledge. I've always heard (and followed best practice) to avoid storing binary data in BYTEA columns that should otherwise be put on a filesystem or an object storage like S3.
I'd like to find out more about this, because in many cases it would be very convenient indeed to store it in the database itself.
In our multi-tenant e-commerce application we handled it like this:
* Metadata for each file is stored in the database
* The application accesses files through an abstraction based on that metadata, and doesn't care if the actual file is stored in S3 or the database
* File types which are small and few, are stored in the database. For example letterheads, logos, terms-and-conditions. (a few gigabytes total)
* File types which are big (e.g. CSV-reports) or many (e.g. invoice-PDFs) are stored in S3 (several terabytes total)
* Development and test systems often use the database for everything and don't have an associated S3 bucket.
* Most of the application remains usable without S3 (access to invoice PDFs and CSV-reports is not critical)
In our case the DB was Mongo, but I expect Postgres to work the same.
We actually stored all files in the database originally, and only migrated after it reached several terabytes. It worked perfectly fine, but was rather expensive. So next time I'd go for S3 for the start.
The primary reason to avoid doing so is avoiding thrashing your buffers, along with increased size of backups, WAL bloat, etc.
Can you? Yes. Should you? Not at anything beyond a toy scale, unless you want to pay for more RAM to ensure that your normal OLTP queries don’t take a performance hit.
I had a system that has ~600gb of blob data in bytea that could have easily been an S3 bucket + db reference. It made backups way more of a pain than necessary.
It was intentional in the design, because I wanted total consistency with a single backup for the system. It worked great for years. But as we got more and more clients, it really should have been migrated to the above design to make sure our backups could be taken / restored faster.
So the original advice actually still stands. You can still start off with Posgres, store it in BYTEA columns, and then work on a plan to use an object storage as you grow. S3 may not be possible and you will be evaluating other options like Minio
You know what, I agree...original advice stands. I should have known that the data I was storing would eventually grow larger than reasonable, so in this case it would have been smarter to design it properly from the start. Hindsight.
But there are still plenty of cases I would say BYTEA is perfectly reasonable choice.
I guess I just don’t see the point for most applications. If you’re using a DBaaS, as most are, object storage from the same provider is almost certainly going to be cheaper, and it’s a trivial amount of code to handle the separate push / pull code.
> After some performance checks it became clear that PostgreSQL was even faster than reading from the file system for our use-case. PostgreSQL uses the file system very efficiently for its data - and it adds a lot of caching and efficient reading and writing strategies that can outperform writing and reading raw data on a file system.
This goes against conventional knowledge. I've always heard (and followed best practice) to avoid storing binary data in BYTEA columns that should otherwise be put on a filesystem or an object storage like S3.
I'd like to find out more about this, because in many cases it would be very convenient indeed to store it in the database itself.