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.
> 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.
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'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 agree, the reason why I had submitted this HN submission post was because I was getting way too deep into the weeds of new databases like surrealdb/tigerbeetle/scylladb etc.
which while are an interesting problem to think through because well, it makes me feel smarter knowing these databases. I still felt like postgresql was enough, (at best something like clickhouse or redis combined with postgresql might be more than enough for many things)
But the fact of the matter is that sometimes you don't even need postgresql. I actually just deploy sqlite apps in golang/rust, a website that I had made that is a single binary: https://mirror.forum
Sqlite is more than enough as well and there are a lot of people pushing sqlite above its weight as well (Turso and the likes)
but its nice knowing that Sqlite/postgresql are more than enough for many use cases. Both are really amazing for a lot of things :-D
Its amazing how we've for the most part atleast now simplified the architecture to have good options. Like using Sqlite and especially Postgresql in the start is a decision that you would rarely go wrong at. I personally feel like although I use sqlite a lot more in projects because i value the simplicity it gives but postgresql also holds a somewhat good relationship in my heart :-D
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.
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.
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.
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.
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.
As @f3408fh has said, I am also curious to knowing more about it if possible.
I had always thought that trading firms might be some of the sole exceptions of over-engineering as I had went into the rabbit-holes of FPGA's and found that some of the use-cases of that technology was trading firms using it to literally shave off even a few milliseconds and so the amount of optimization there
So I am a bit curious how postgres/some dotnet services and SQS queues is able to create a trading firm and scale it and even sell it for 140M$. It feels like my understanding of the situation was a bit uncertain then but could you please elaborate more if possible?
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.
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.
100% agreed. Had recently a few million rows of stats in a relational DB (not exactly Postgres but it'd be the same) and the table was already way bigger than the same data in a TSDB. And that's just the storage side, before query performance even comes into it.
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.
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.
The page makes an argument that having a bunch of disparate databases doing specialized things means you have a higher maintenance burden, since you are maintaining more things and will be paged for more failures, but in my 20+ years maintaining production infrastructure, I find that it is often much more difficult to maintain one large database than multiple smaller ones.
You have pushed your entire infrastructure into a single failure domain, for one thing. You make it certain that if your database fails, EVERYTHING fails.
In addition, there is resource contention and workload variability. As you start to push your postgres instance, all the workloads hitting your database are going to be competing for resources. While postgres itself is good at parallelizing the work it is doing, all that work is still going to be hitting the same database, and competing for the same kcache. Your entire infrastructure might degrade in performance at the same time.
Any issue with one component can cascade very easily if they all share the same database. If your login functionality has a bug and is creating churn on your database, it can lock everything.
With multiple databases, you have a much smaller blast radius when you do database operations. You isolate your workloads and can independently scale them.
Admittedly, all of these issues occurred at a place that had high traffic and high availability requirements. Honestly, though, if your load is so low that you never feel infrastructure pressure, it probably doesn't matter what strategy you use.
25 comments
[ 328 ms ] story [ 1219 ms ] threadHere's Malcom Gladwell discussing spagetti sauce which feels oddly relevant: https://youtu.be/iIiAAhUeR6Y?si=UJUUiF6H0j6IY3lL
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.
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.
which while are an interesting problem to think through because well, it makes me feel smarter knowing these databases. I still felt like postgresql was enough, (at best something like clickhouse or redis combined with postgresql might be more than enough for many things)
But the fact of the matter is that sometimes you don't even need postgresql. I actually just deploy sqlite apps in golang/rust, a website that I had made that is a single binary: https://mirror.forum
Sqlite is more than enough as well and there are a lot of people pushing sqlite above its weight as well (Turso and the likes)
but its nice knowing that Sqlite/postgresql are more than enough for many use cases. Both are really amazing for a lot of things :-D
Its amazing how we've for the most part atleast now simplified the architecture to have good options. Like using Sqlite and especially Postgresql in the start is a decision that you would rarely go wrong at. I personally feel like although I use sqlite a lot more in projects because i value the simplicity it gives but postgresql also holds a somewhat good relationship in my heart :-D
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.
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.
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.
I had always thought that trading firms might be some of the sole exceptions of over-engineering as I had went into the rabbit-holes of FPGA's and found that some of the use-cases of that technology was trading firms using it to literally shave off even a few milliseconds and so the amount of optimization there
So I am a bit curious how postgres/some dotnet services and SQS queues is able to create a trading firm and scale it and even sell it for 140M$. It feels like my understanding of the situation was a bit uncertain then but could you please elaborate more if possible?
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.
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 enough - https://news.ycombinator.com/item?id=39273954 - Feb 2024 (316 comments)
You have pushed your entire infrastructure into a single failure domain, for one thing. You make it certain that if your database fails, EVERYTHING fails.
In addition, there is resource contention and workload variability. As you start to push your postgres instance, all the workloads hitting your database are going to be competing for resources. While postgres itself is good at parallelizing the work it is doing, all that work is still going to be hitting the same database, and competing for the same kcache. Your entire infrastructure might degrade in performance at the same time.
Any issue with one component can cascade very easily if they all share the same database. If your login functionality has a bug and is creating churn on your database, it can lock everything.
With multiple databases, you have a much smaller blast radius when you do database operations. You isolate your workloads and can independently scale them.
Admittedly, all of these issues occurred at a place that had high traffic and high availability requirements. Honestly, though, if your load is so low that you never feel infrastructure pressure, it probably doesn't matter what strategy you use.