PostgreSQL is enough (2024) (opens in new tab)

I'd go further, and say that most of the time, "SQLite is enough".

But, yes, PostgreSQL is all I ever use for anything that needs to be big. I ported a big old web app that had ScyllaDB, Elastic Search, Redis, and probably some other stuff I've forgotten. It got PostgreSQL+PostGIS (it's a mapping app), that's it. I'm sure there's some situation where it would be worth looking at all that other stuff, but it's ridiculous to build all that complexity in before you even have users.

I have worked some places where a significant amount of business logic lived in database functions and triggers and, hoo boy, that was really hard to reason about. If you're disciplined enough to have migrations around that implemented all that stuff, you're still going to have an unpleasant time piecing all of it together when debugging. But often you're in a state where you don't even have those breadcrumbs and you're pulling out your hair trying to figure out what's going on.

Lots of the alternatives that this site claims Postgres will do are things you'd only consider well past the point that Postgres would be viable.

Kafka? No one wants to operate Kafka, if it's a serious contender it's because you need things only it can do. Same with Elasticsearch, it sucks to operate, sucks to build a second stack just for search, so you'd only consider it at the point that Postgres is no longer suitable. Same with Snowflake.

PostgreSQL is good enough until it's not good enough, when you realize all the bad design decisions that were made before it hits scale. It is the decisions people make around not partitioning, HA, replication that makes it not good enough.

I love Postgres. I buy using it for many many things.

I really don’t understand why everyone insists that you should use it as a work/message queue.

There are lots of purpose-built bullet proof queuing systems that are simple to setup and administer (or just use SQS).

Your queue is likely to have very different access patterns than the rest of your data, and sticking it in Postgres means you’re probably going to end up setting up partitions or optimizing auto-vacuum on that table way earlier than you probably need to mess around with this things in your scaling.

If your queue has more than a few hundred jobs a day (or you anticipate that like anytime soon), just use a queue.

> But the bar should be high: only after pushing Postgres to its limits, documenting why it was insufficient, and accepting the operational cost of the alternative

Why do I need to push Postgres to its limits before using a different solution? Throwing a hosted Redis in front of some hot-path API calls is very straightforward and easier to reason about than materialized views or UNLOGGED tables.

I like postgres. I use it for lots of projects. I like other databases too. I think thats okay.

Here's Malcom Gladwell discussing spagetti sauce which feels oddly relevant: https://youtu.be/iIiAAhUeR6Y?si=UJUUiF6H0j6IY3lL

While I love postgres, I take issue with coupling too much application logic to the DB. It’s much easier to update/rollback stateless containers/cloud functions/VMs than to recover a DB.

Moving business logic into database functions is the shortest path to insanity.

Postgres has its advantages, but for message queues I’d stick with SQS. I built out a trading firm last year that was basically Postgres, some dotnet services and SQS queues and we got acquired for $140M. You can build some fairly formidable systems if you keep them simple.

Postgres as a relational DBMS is enough, a point worth making against people trying to abstract it away with stuff like ORMs.

Besides that, you usually won't need nosql thanks to jsonb, and other special types like in Postgis cover other use cases. SQL is better than dealing with various DSLs like you'd see for timeseries. Then there are things you may want separate tooling for but can also do in Postgres if you want to avoid more infra: pubsub/queues, search w/ pgvector, graph DBs, KV stores, caches.

A lot of the other examples here look ridiculous though, like no I'm not hosting a webserver on Postgres. Database functions should be used sparingly too. Never touched triggers since I normally have a general-purpose language driving DB changes, but could see why someone else might use them.

PostgreSQL is a powerhouse. It has a solution for everything. Especially when you start a project you might be better off just using PostgreSQL instead of a specialized solution. You can optimize it later.

This could use a debugging guide or two. Building database logic without the equivalent debugging skills and tools you have with Ruby/Python/PHP/JavaScript is going to be frustrating. Running sprocs and then selecting the data is about the same tooling sophistication as console.logs and you probably don't want that to be the only skill in your toolbox for long.

The first two linked resources:

  Simplify: move code into database functions
  Just Use Postgres for Everything

Are disqualifying enough to not warrant further reading.

A relational database is one form of persistent storage. They are great for managing persistent representations of key abstractions and their relationships.

They are not application frameworks nor scalable messaging systems by design.

Previously...

PostgreSQL is enough - https://news.ycombinator.com/item?id=39273954 - Feb 2024 (316 comments)

is it though? not for time series workloads with billions of rows it isn’t

Using Pgsql for everything shows you've been drinking the internet kool-aid. And that site is like a creepy shrine saying pgsql is the alpha and omega.

Like any tool, it works until it doesn't. And when it doesn't it takes a herculean effort to unwind it.

I looked at the first entry and yeah, just say no to moving your business logic into your database. Because change happens...and don't you want that happening in something more plastic than your RDBMS? But it's a great way to bind your solution to Postgres forever.

As an aside, I've used oracle, sybase, informix, mysql, postgresql, rdb, db2, mssql, and a few more that I can't remember. And the idea that pgsql is always the answer is the wrong answer to probably the wrong question.

pgvector ftw

Is it recomended for beginers?