PostgreSQL for Everything

My general rule of thumb is "Use Postgres until you've discovered why you can't use Postgres."

Anything you introduce is another moving part you have to operate and maintain, and in the beginning, Postgres can probably handle it. Wait for load, see where its failing, and then you'll have a better idea if adding another tool is worth the cost.

This kind of post (Postgres! It's all you need!) is getting pretty tiresome. Postgres does not even come close to a full replacement for Elastic, and that's just the first bullet.

Looking down the list it is pretty easy to go: Yes, postgres can be used instead of that for extremely basic use cases, but it all goes out the window you actually need any of the power of these other tools.

I use SQLite for everything, and I'm perfectly happy with it. I'm aware of the concurrent writer issues, but at my scale it doesn't even matter.

These types of articles needed to be written because we have gone way too much in the other direction. The issue is that people use too many tools prematurely when they are not needed at their stage. So yea, in most cases, you are probably better off just with Postgres. I m a culprit of this myself so I wouldn't say that I know better. It is just too tempting to setup too many tools to feel cooler or feeling that "we must use elasticsearch as no one does search in db".

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.

At Comper we have a very hot key-value store for annotating git data. We maintain a parallel git-blame data structure so we can do incremental "git blame -w -M -C -C". Typically a very expensive operation, but if you make it incremental, you can make it very cheap when new commits need to be analyzed. However, building the git blame tree is still pretty intensive for large repos.

We currently use rocksdb with storage on the same node, and hit rocksdb 1000s of times per second during our analysis. About 20% writes, 80% reads. The issue is that we need to start scaling horizontally, for burstable workers and zero-downtime deployment. So we're thinking to offload to an external kv service instead of a local rocksdb.

TiKV seems a good replacement, about 3-4x slower, but very scalable. Reading this article, I think a separate postgres cluster with unlogged tables might be a good idea. If anyone has some experience to share, let me know!

I tend to agree with quite a few points in the article, but some topics warrant some careful scrutiny.

* As a message queue:

Only if your required features are very basic, like if you need cluster communication and run your own coordination protocol on top.

* High Volume Time Series:

TimeScale works, but composes badly with other workloads on the same DB server ( from an operational perspective at scale )

* Vector Database:

The same issues as with TimeScale.. PgVector for example lives in its own seperate "world" and the query planner sees it as a very opaque thing. Forget about adding vector storage to an existing high volume db, that must server other complex queries.. PGVector will either trash your caches, or take over your cpu so that workloads that used to work fine stall. This is IMO not a pgvector problem itself ( Kudos to those guys ) but rather that postgresql extension apis are not very good at exposing custom costs and tradeoffs to the system as a whole.

* Raw Data:

Works for small files... why anyone would want to store large amounts of data in it would be a mystery, where it shines is accessing LOTS of small files where internal caching etc help a lot compared to raw filesystem access ( also a bit dependent on the filesystem and its tuning though )

* Microservice:

If your service is ONLY exposing json data from some database model, then it should not exist at all IMO. Create a view and be done with it.