logoalt Hacker News

sgtyesterday at 6:36 PM1 replyview on HN

Intrigued by this

> 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.


Replies

sgarlandyesterday at 6:44 PM

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.

show 2 replies