Good stuff, I turned my gist into an info site and searchable directory (and referenced this article as well, which seems to pay homage to my gist, which in turn inspired the site)
The point of Redis is data structures and algorithmic complexity of operations. If you use Redis well, you can't replace it with PostgreSQL. But I bet you can't replace memcached either for serious use cases.
I do agree, I don’t know why more people don’t just use Postgres. If I’m doing data exploration with lots of data (e.g., GIS, nD vectors), I’ll just spin up a Postgres.app on my macOS laptop, install what little I need, and it just works and is plenty fast for my needs. It’s a really great choice for a lot of domains.
That being said, while I think Postgres is “the right tool for the job” in many cases, sometimes you just want (relative) simplicity, both in terms of complexity and deployment, and should use something like SQLite. I think it’s unwise to understate simplicity, and I use it to run a few medium-traffic servers (at least, medium traffic for the hardware I run it on).
I've found that Postgres consumes (by default) more disk than, for example, MySQL. And the difference is quite significant. That means more money that I have to pay every month. But, sure Postgres seems like I system that integrates a lot of subsystems, that adds a lot of complexity too. I'm just marking the bad points because you mention the good points in the post. You're also trying to sell you service, which is good too.
Nice! How do you "preinstall the extensions" so that you can have eg timescaledb and others available to install in your Postgres? Do you need to install some binaries first?
Elixir + Postgres is the microservices killer...last time I saw VP try to convince a company with this stack to go microservices he was out in less than 6mo
I don't disagree, but I think big enterprises expect support, roadmaps, and the ability to ask for deliverables depending on the sale or context of the service.
Skeptical about replacing Redis with a table serialized to disk. The point of Redis is that it is in memory and you can smash it with hot path queries while taking a lot of load off the backing DB. Also that design requires a cron which means the table could fill disk between key purges.
This kind of thing gets posted every couple of months. Databases like Pinecone and Redis are more cost-effective and capable for their special use case, often dramatically so. In some circumstances the situation favours solving the problem in Postgres rather than adding a database. But that should be evaluated on a case-by-case basis. For example, if you run something at scale and have an ops team the penalty of adding a second database is much smaller.
(I run a medium-sized Postgres deployment and like it, but I don't feel like it's a cost-effective solution to every database problem.)
Love the sentiment! And I'm a user - but what about aggregations? Elasticsearch offers a ton of aggregates out of the box for "free" completely configurable by query string.
Tiger Data offers continuous aggs via hypertable but they need to be configured quite granularly and they're not super flexible. How are you all thinking about that when it comes to postgres and aggregations?
Caching is mentioned in the article: What do you guys feel about using PostgreSQL for caching instead of Redis?
Redis is many times faster, so much that it doesn't seem comparable to me.
A lot of data you can get away with just caching in-mem on each node, but when you have many nodes there are valid cases where you really want that distributed cache.
Lots of familiar things here except for this UNLOGGED table as a cache thing. That's totally new to me. Has someone benched this approach against memcached and redis ? I'm extremely skeptical PGs query / protocol overheads are going to be competitive with memcached, but I'm making this up and have nothing to back it up.
This post is discussing more specialized databases, but why would people choose Oracle/Microsoft DB instead of Postgres? Your own experience is welcome.
Something TFA doesn’t mention, but which I think is actually the most important distinction of all to be making here:
If you follow this advice naively, you might try to implement two or more of these other-kind-of-DB simulacra data models within the same Postgres instance.
And it’ll work, at first. Might even stay working if only one of the workloads ends up growing to a nontrivial size.
But at scale, these different-model workloads will likely contend with one-another, starving one-another of memory or disk-cache pages; or you’ll see an “always some little thing happening” workload causing a sibling “big once-in-a-while” workload to never be able to acquire table/index locks to do its job (or vice versa — the big workloads stalling the hot workloads); etc.
And even worse, you’ll be stuck when it comes to fixing this with instance-level tuning. You can only truly tune a given Postgres instance to behave well for one type-of-[scaled-]workload at a time. One workload-type might use fewer DB connections and depend for efficiency on them having a higher `work_mem` and `max_parallel_workers` each; while another workload-type might use many thousands of short-lived connections and depend on them having small `work_mem` so they’ll all fit.
But! The conclusion you should draw from being in this situation shouldn’t be “oh, so Postgres can’t handle these types of workloads.”
No; Postgres can handle each of these workloads just fine. It’s rather that your single monolithic do-everything Postgres instance, maybe won’t be able to handle this heterogeneous mix of workloads with very different resource and tuning requirements.
But that just means that you need more Postgres.
I.e., rather than adding a different type-of-component to your stack, you can just add another Postgres instance, tuned specifically to do that type of work.
Why do that, rather than adding a component explicitly for caching/key-values/documents/search/graphs/vectors/whatever?
Well, for all the reasons TFA outlines. This “Postgres tuned for X” instance will still be Postgres, and so you’ll still get all the advantages of being able to rely on a single query language, a single set of client libraries and tooling, a single coherent backup strategy, etc.
Where TFA’s “just use Postgres” in the sense of reusing your Postgres instance only scales if your DB is doing a bare minimum of that type of work, interpreting “just use Postgres” in the sense of adding a purpose-defined Postgres instance to your stack will scale nigh-on indefinitely. (To the point that, if you ever do end up needing what a purpose-built-for-that-workload datastore can give you, you’ll likely be swapping it out for an entire purpose-defined PG cluster by that point. And the effort will mostly serve the purpose of OpEx savings, rather than getting you anything cool.)
And, as a (really big) bonus of this approach, you only need to split PG this way where it matters, i.e. in production, at scale, at the point that the new workload-type is starting to cause problems/conflicts. Which means that, if you make your codebase(s) blind to where exactly these workloads live (e.g. by making them into separate DB connection pools configured by separate env-vars), then:
- in dev (and in CI, staging, etc), everything can default to happening on the one local PG instance. Which means bootstrapping a dev-env is just `brew install postgres`.
- and in prod, you don’t need to pre-build with new components just to serve your new need. No new Redis instance VM just to serve your so-far-tiny KV-storage needs. You start with your new workload-type sharing your “miscellaneous business layer” PG instance; and then, if and when it becomes a problem, you migrate it out.
I made the switch from MySQL to postgres a few years ago I didn't really understand what everyone was excited about before I made the switch. I haven't used MySQL since and I think postgres provides everything I need the only thing that I ever snarl at is how many dials and knobs and options there are that's not a bad thing!
Can anyone comment on whether postgres can replace full columnar DB? I see "full text search" but it feels like this is falling a little short of the full power of elastic -- but would be happy to be wrong (one less tech to remember).
This is the future of all devtools in the AI era. There's no reason for tool innovation because we'll just use whatever AIs know best which will always be the most common thing in their training data. It's a self-reinforcing loop. The most common languages, tools, libraries of today are what we will be stuck with for the foreseeable future.
I agree that managing lots of databases can be a pain in the ass, but trying to make Postgres do everything seems like a problem as well. A lot of these things are different things and trying to make Postgres do all of them seems like it will lead to similar if not worse outcomes than having separate dedicated services.
I understand that people were too overeager to jump on the MongoDB web scale nosql crap, but at this point I think there might have been an overcorrection. The problem with the nosql hype wasn't that they weren't using SQL, it's that they were shoehorning it everywhere, even in places where it wasn't a good fit for the job. Now this blog post is telling us to shoehorn Postgres everywhere, even if it isn't a good fit for the job...
88 comments
[ 2.5 ms ] story [ 49.6 ms ] threadhttps://PostgresIsEnough.dev
That being said, while I think Postgres is “the right tool for the job” in many cases, sometimes you just want (relative) simplicity, both in terms of complexity and deployment, and should use something like SQLite. I think it’s unwise to understate simplicity, and I use it to run a few medium-traffic servers (at least, medium traffic for the hardware I run it on).
(I run a medium-sized Postgres deployment and like it, but I don't feel like it's a cost-effective solution to every database problem.)
btw, big fan of postgres :D
Tiger Data offers continuous aggs via hypertable but they need to be configured quite granularly and they're not super flexible. How are you all thinking about that when it comes to postgres and aggregations?
Redis is many times faster, so much that it doesn't seem comparable to me.
A lot of data you can get away with just caching in-mem on each node, but when you have many nodes there are valid cases where you really want that distributed cache.
Im also curious about benchmark results.
If you follow this advice naively, you might try to implement two or more of these other-kind-of-DB simulacra data models within the same Postgres instance.
And it’ll work, at first. Might even stay working if only one of the workloads ends up growing to a nontrivial size.
But at scale, these different-model workloads will likely contend with one-another, starving one-another of memory or disk-cache pages; or you’ll see an “always some little thing happening” workload causing a sibling “big once-in-a-while” workload to never be able to acquire table/index locks to do its job (or vice versa — the big workloads stalling the hot workloads); etc.
And even worse, you’ll be stuck when it comes to fixing this with instance-level tuning. You can only truly tune a given Postgres instance to behave well for one type-of-[scaled-]workload at a time. One workload-type might use fewer DB connections and depend for efficiency on them having a higher `work_mem` and `max_parallel_workers` each; while another workload-type might use many thousands of short-lived connections and depend on them having small `work_mem` so they’ll all fit.
But! The conclusion you should draw from being in this situation shouldn’t be “oh, so Postgres can’t handle these types of workloads.”
No; Postgres can handle each of these workloads just fine. It’s rather that your single monolithic do-everything Postgres instance, maybe won’t be able to handle this heterogeneous mix of workloads with very different resource and tuning requirements.
But that just means that you need more Postgres.
I.e., rather than adding a different type-of-component to your stack, you can just add another Postgres instance, tuned specifically to do that type of work.
Why do that, rather than adding a component explicitly for caching/key-values/documents/search/graphs/vectors/whatever?
Well, for all the reasons TFA outlines. This “Postgres tuned for X” instance will still be Postgres, and so you’ll still get all the advantages of being able to rely on a single query language, a single set of client libraries and tooling, a single coherent backup strategy, etc.
Where TFA’s “just use Postgres” in the sense of reusing your Postgres instance only scales if your DB is doing a bare minimum of that type of work, interpreting “just use Postgres” in the sense of adding a purpose-defined Postgres instance to your stack will scale nigh-on indefinitely. (To the point that, if you ever do end up needing what a purpose-built-for-that-workload datastore can give you, you’ll likely be swapping it out for an entire purpose-defined PG cluster by that point. And the effort will mostly serve the purpose of OpEx savings, rather than getting you anything cool.)
And, as a (really big) bonus of this approach, you only need to split PG this way where it matters, i.e. in production, at scale, at the point that the new workload-type is starting to cause problems/conflicts. Which means that, if you make your codebase(s) blind to where exactly these workloads live (e.g. by making them into separate DB connection pools configured by separate env-vars), then:
- in dev (and in CI, staging, etc), everything can default to happening on the one local PG instance. Which means bootstrapping a dev-env is just `brew install postgres`.
- and in prod, you don’t need to pre-build with new components just to serve your new need. No new Redis instance VM just to serve your so-far-tiny KV-storage needs. You start with your new workload-type sharing your “miscellaneous business layer” PG instance; and then, if and when it becomes a problem, you migrate it out.
I agree that managing lots of databases can be a pain in the ass, but trying to make Postgres do everything seems like a problem as well. A lot of these things are different things and trying to make Postgres do all of them seems like it will lead to similar if not worse outcomes than having separate dedicated services.
I understand that people were too overeager to jump on the MongoDB web scale nosql crap, but at this point I think there might have been an overcorrection. The problem with the nosql hype wasn't that they weren't using SQL, it's that they were shoehorning it everywhere, even in places where it wasn't a good fit for the job. Now this blog post is telling us to shoehorn Postgres everywhere, even if it isn't a good fit for the job...