PostgreSQL for Everything(raphaelbauer.com) |
PostgreSQL for Everything(raphaelbauer.com) |
I demonstrated this with ClickHouse: https://github.com/ClickHouse/pg_clickhouse/blob/main/doc/of...
We're working on a chdb based mechanism to copy to/from s3, maybe with fdw on top we can back table in s3
You can try similar things with pg_duckdb & pg_lake
I have decided to use clickhouse with that config because of missing S3 for logs and metrics for long term store.
<clickhouse>
<storage_configuration>
<disks>
<audit_s3>
<type>s3</type>
<endpoint>https://S3-EndPoint/{{ audit_bucket_name }}/clickhouse/</endpoint>
<access_key_id>{{ clickhouse_audit_s3_access_key }}</access_key_id>
<secret_access_key>{{ clickhouse_audit_s3_secret_key }}</secret_access_key>
</audit_s3>
</disks>
<policies>
<audit_tiered>
<volumes>
<default>
<disk>default</disk>
</default>
<audit_s3>
<disk>audit_s3</disk>
</audit_s3>
</volumes>
</audit_tiered>
</policies>
</storage_configuration>
</clickhouse>multiple processes connected to it.
But I sure wish it was 'core' and we didn't have to worry about it potentially going away, becoming de-supported..
I wanted to see how far I could push that toolset. It worked surprisingly well. Django's capabilities meant such things as multi-user login pages, access controls and remote monitoring were very easy.
But a lot of companies are trying to solve that, notably multigres, neki and even pgdog.
0: https://www.postgresql.org/docs/19/ddl-property-graphs.html
so yes, i'm still a postgres maximalist (worker queues still in pg [1]) but (especially in the age of quick LLM prototypes) it's always worth measuring the more purpose-built approach.
[0]: https://setoku.com
My tip: store your company's source code on a samba file server. Only when that no longer performs well, switch to other systems like Git.
In most of the applications we build or maintain we use PostgreSQL + cloud storage. That's it. And it works very well, also for: storing JSON, full text search, as a queue, as a vector database. Other software may be better at providing those features, but I'm extremely happy we only need to understand & manage PostgreSQL.
Coming from storing billions of rows in Clickhouse and performing dozens of materialized operations I shudder to think about what that would look like in a DB that doesn’t even support declarative IVM.
It is already a quite smooth experience, but there is work to make it even easier than that.
I work on Lakebase, opinions my own.
Hopefully there is some value in this - one click multipurpose postgres fleet.
By picking the tools before understanding the model and building bespoke architecture.
You pick the tools that the business model requires. It might be a relational data store. It might not be. You might want an event store. You might want to reduce costs with lambdas and DynamoDB. You may need a pub/sub event broker.
The OP clearly loves Postgres. Cool. They also have limited experience with complex systems architectures because if they had that experience, they would have never written this article.
I don't think that's ever saved money.
> because if they had that experience, they would have never written this article.
That's not true.
I spent about a year as a consultant in the AWS space, visited about ~15 clients of varying sizes.
More often than not there's a single pg aurora instance responsible for 50%+ of the bill. Even worse are the serverless aurora offenders.
All the indexes and guarantees of PG don't come cheaply and dynamodb pricing is not cheap but comparatively reasonable. It really is a good product if you know how to use it.
No. If you're struggling to build software against a DB and then abstract parts to use Redis or ES or whatever in the future, that's kinda a skill issue you or your team have with building poor software to begin with. Nothing to do with using a DB for multiple things like a Queue/Search etc.
It’s the business modeling skill that most developers lack, so they skip it and believe an ERD will magically cover all invariants.
For instance, for many simple needs MySQL is simpler than Postgres, with similar performance and consistency.
* No need for a connection pool, while many use cases with Postgres require PgBouncer and Co.
* Easy sort (and basic search) of multilingual text, because MySQL has case insensitive UTF8 collations.
* No need to VACUUM, which can be a hard problem (it was, the last time I used Postgres).
For full text search, I once worked on a project that considered several alternatives for this, including Postgres. Manticore Search was finally chosen because it was more performant, with better search results.
Tbf you can also achieve this in Postgres, it's just not present by default. From the docs [0]: CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
Not sure if I'm missing anything here, but if I want case-insensitive search I simply create an index on lower(column) and use that to query.
VACUUM is something you need to pay attention to at scale. And at that point you need to know your DB anyway and tune it. For smaller applications (and I don't mean only toy applications) it usually isn't an issue.
Or even pg_trgm trigram indexes, which are case-insensitive by default and support similarity search to accept typos and misspellings.
is there a strong evidence you even need client side connection pool at all? What is the purpose?
The limitation is that you have many clients with connection pools, they hold internal PG connection without allowing it to be reused by other clients..
The job queue runs on the cache db, scheduling jobs to move data from bigquery into postgres. It’s pretty neat.
Now we’ve run into near-real-time requirements so clickhouse is getting thrown into the mix.
It’s pretty funny the lengths we go to to implement user facing analytics that’s basically just “you are visitor number X” from 1995.
42 is not the answer to everything.
42 is the Answer to the Ultimate Question about Life, the Universe, and Everything.
Different use cases have different scalability limits in PG, when you get to them you need to deal with them.
It would be perfect if it had somewhat transparent sharding, I mean a way to add another instance and distribute load without having to stop everything.
There are solutions, but they tend to be involved and when you get to that point in many cases it makes sense to just move that workload to something else that scales better.
Considering adding mongo for unstructured data? Just use postgres jsonb.
Building a search index? Postgres is fine too.
Considering using redis for fragment caching? Just use an unlogged table in postgres with key value columns. Need pub/sub? Well just use postgres listen/notify.
Using postgres for everything has served me very well.
- https://www.reddit.com/r/PostgreSQL/comments/1vbo5j8/raw_xml...
- your post did not have a single word on XML hence my comment
Not 100% sure about using PG for file system at scale however. I'd love to hear more on the challenges (vacuum, toast, anything else?)
D1?
NVMe drives + Litestream + object storage(S3/R2..). sqlite simplifies things for the entire long tail of apps/services that aren't the Ubers and AirBNBs of the world.
Also, apparently Litestream could use a filesystem instead of an object store?
I love Postgres. Im using it for majority of my career in IT. It never failed me, even on very big scale.
Amazing piece of software.
then Sqlite works as well too. can run the whole thing on Cloudflare.
running Postgres isn't difficult. but dealing with a VPS for low traffic is a headache that's not necessary.
No deamon. Single file per DB. Less configuration overhead.
Supporting more than 1 writer per process. Strict typing. Access controls. Replication at scale is more effecient than copy-pasting files (seems SQLite has improved on this one).
But for big, multi-writer work PG is the way to go.
This is a not great start. I assume it refers to MyISAM which has not been relevant for over a decade at this point. InnoDB made different design than PG decisions and was (and perhaps still is) faster at point lookups.
https://sqlfordevs.com/books+courses/timescale/05-continuous...
Still not totally declarative, but it at least gets IVM into core in a way people can make use of.
I’m currently paying digital ocean about 30 dollars a month for 4 commercial websites with fully featured Postgres.
https://medium.com/revolut/recording-more-events-but-where-w...
It's easy to use Postgres poorly in ways that result in painful centralized bottlenecks.
(Obviously this is largely true for anything, but I think that in 2026, where there's also a lot of more-specialized/less-fleible but much-easier-to-scale well-supported mature alternatives, you should be VERY wary of making everything have a single central SPOF. What are your users going to expect in terms of maintenance windows, etc.)
I'd be cautious with articles that say things like "All cloud providers allow you to run (and scale!) PostgreSQL by clicking a single button." with no mention of how long that will take and what options should be set to make it faster, or the costs of those things.
Ultimate the entire processing got removed from our team and no longer needs to do these deployments (acquisition transitions)...
We quickly replaced part by part by easier, less costly parts.
Software development is not just writing code; I think all HN users know that.
I find the opposite to be true. I cut out the decentralization and get it all one one machine, and the bugs go away and the perf improves.
Yeah, backwards compatibility is not a thing for Java, Rust, C++, etc. :eye-roll:
Meanwhile in SQL if you need to make a backwards-incompatible change to your schema you can always use VIEWs and INSTEAD OF triggers to maintain backwards compatibility for code you've not fixed yet.
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.
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'm a huge PG fan, so I start everything with it, but SQLite is sane, and it generally has a happy upgrade path to PG If you need it.
Both are amazing technologies.
* 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.
In tech stack choices, I prefer staying simple as long as feasible. That said, you also need to know and understand concepts at a thorough level.
The above comment from the article suggests PG as a central server that simplifies event architecture. As if the "annoying, custom and needed skillset" around those specific alternatives are unnecessary baggage.
If you know anything about queues, scaling, availability, access semantics, message formats and concepts such as delivery guarantees, you find out very quickly that the server which stores a queued message is not the high-order bit in that equation.
I think a good way to think of this is: What empirical metric will the introduction of kafka (or whatever you choose) move in a positive direction? Latency? Oncall burden? The amount of code you have to maintain? Do you have correctness or data integrity metrics that this change would register on? etc.
This isn't at all intended as an unanswerable question. Many or most organizations will easily say "yes" that they expect some improvement on some metrics by adopting the "right" system for the job. But lots of other organizations are cargo culting "well we know this is the right way, so we should do it this way" long before any metrics they care about would demonstrate the improvement.
Anyway databases nowadays are a commodity. A sticky commodity but nonetheless they are replaceable; increasingly so in the age of AI where data migrations are easier than ever.
> 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.
* Metadata for each file is stored in the database
* The application accesses files through an abstraction based on that metadata, and doesn't care if the actual file is stored in S3 or the database
* File types which are small and few, are stored in the database. For example letterheads, logos, terms-and-conditions. (a few gigabytes total)
* File types which are big (e.g. CSV-reports) or many (e.g. invoice-PDFs) are stored in S3 (several terabytes total)
* Development and test systems often use the database for everything and don't have an associated S3 bucket.
* Most of the application remains usable without S3 (access to invoice PDFs and CSV-reports is not critical)
In our case the DB was Mongo, but I expect Postgres to work the same.
We actually stored all files in the database originally, and only migrated after it reached several terabytes. It worked perfectly fine, but was rather expensive. So next time I'd go for S3 for the start.
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.
I had a system that has ~600gb of blob data in bytea that could have easily been an S3 bucket + db reference. It made backups way more of a pain than necessary.
It was intentional in the design, because I wanted total consistency with a single backup for the system. It worked great for years. But as we got more and more clients, it really should have been migrated to the above design to make sure our backups could be taken / restored faster.
I've done that before and the code was a mess. It works at the beginning but APIs do much more than piping data from the database. When you start dealing with ACL, external calls, code reuse, etc. It's just nice to have all the tools available to you from something like Python or Go.
The main caveat as someone who works on mostly average web CRUD apps, is that "Use PG/SQLite for everything" usually falls flat when the tools I use day to day don't support that use case super well or have rougher edges.
If your framework/ORM/whatever of choice doesn't support the full feature set of that driver compared to Redis/ES/Whatever you're replacing, you'll find yourself going down rabbit holes doing workarounds instead of staying with the "happy path" and just using separate tech for what it's specialised in.
If you already are doing most of these sorts of features by yourself instead of with frameworks, maybe it's fine, but this does start to feel like a time-to-release hindrance if you don't want to fiddle with the minutia.
Shameless plug: https://github.com/jankovicsandras/plpgsql_bm25 BM25 search implemented in PL/pgSQL ( Unlicense / Public domain )
The repo includes also plpgsql_bm25rrf.sql : PL/pgSQL function for hybrid search ( plpgsql_bm25 + pgvector ) with Reciprocal Rank Fusion; and Jupyter notebook examples.
I found MariaDB to be wonderfully simple to use for somewhat casual use cases: https://mariadb.com/docs/server/ha-and-performance/optimizat... and still reach for it in some personal projects, however the whole growing MySQL incompatibility is a big issue if the tech you use only officially supports MySQL and you can't (easily) get MariaDB specific DB drivers.
Personally, one of the best things about PostgreSQL is transactional DDL, every DB should support it. Also they handle JSON pretty nicely (though I'd prefer not to store data like that unless necessary) alongside excellent plugins like pgvector and PostGIS.
On the other hand, for things like queues, or even any sort of blob storage, I'd look at things like RabbitMQ or Garage (S3 compatible). Sometimes specialized software is nice for keeping things logically separated. I maintain that it's good to be able to divide your stack up by mechanisms/concerns (rather than business domain necessarily).
Just kidding. Of course there's doom for postgres: https://github.com/cedardb/DOOMQL (pure SQL) and https://github.com/DreamNik/pg_doom (extension).
Oh and there's https://github.com/snaplet/postgres-wasm that allows to run everything else in postgres ^^
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!
MySQL was generally faster, and while MyISAM was a bit limited Innodb was pretty powerful, and you had the choice. It was also simpler (imo) and avoided a lot of the xid/vacuum issues.
That said, still love Postgres. But at the time it started eclipsing MySQL, MySQL felt better positioned.
This seems like one more "When all you have is a hammer, everything looks like a nail" take. I agree with the other commenters who advocate for best-of-breed (e.g. Kafka, etc. for a message queue). PS I freakin love PostgreSQL as a relational (or even a time-series or OLAP) DB.
But at scale you probably don't want to manage a bunch of mission critical systems that were jacked into your database server. The database is slow? How do we monitor that?
So I would definitely begin like this, but you need to have a plan to break all of these out sooner or later.
The relational model and sql force us to simplify our data models too much by eliminating relationships or just not dealing with them.
Think about a nested json blob from some web service api and storing it in SQL in normalized tables. No one is going to do that. Everything just becomes a denormalized mess and everything is hacked around it.
Instead of modeling things in the proper way, most of the world's data is modeled in a way so that we don't have join explosions in sql queries because they look scary. Data pipelines become these scary batch transformations where data is dumped somewhere else without anyway to trace back where it came from.
I encounter so many end-user applications and systems where you wonder: "why couldn't they allow a list of items here instead of a single box" or "why can't this reference this other thing".
Postgres has built in data types and functions that allows it to work with unstructured json documents, like you would use in MongoDB.
The lack of a rigid schema makes it super fun as well. Does this attribute exist in this row? Who knows! Maybe there's a long-forgotten version lurking, waiting to be retrieved, that will utterly bork the calling app.
I use a SQL databases as needed. I've used Postgres, Sqlite, Duckdb, Json files with AWS Athena, Oracle enterprise for ERP systems (a multitude of schemas and objects with interoperability), and others.
I'm currently, deploying Duckdb with AWS S3 Tables (Iceberg) to see how it fits for a use case I have.
IT is great and always changing. Keep trying new things.
Cheers
> Three Strike Dismissal in One-On-Ones
> Keith Rabois recommends to dismiss your report when you feel bad about an upcoming one on one more than three times in a row. Interesting. …
I don't think this is accurate and smells like an LLM hallucination to me.
From the Timescale/Tiger Data _pgvectorscale_ project's README:
> pgvectorscale builds on pgvector with higher performance embedding search and cost-efficient storage for AI applications.
I think this is where the confusion originates. I believe pgvector is primarily Andrew Kane (@ankane) and a cadre of OSS contributors.
As an aside, I've used Timescale/Tiger Data products and was very happy with them and their support. Their team was very engaged and responsive to all of our questions. They also fixed a pretty gnarly indexing bug I uncovered in pgvectorscale in an impressively short amount of time.
And yes, Andrew Kane (et al) are the people to thank for pgvector.
We (Tiger Data) developed pgvectorscale and pg_textsearch (and timescaledb, and some others)
It's very hard to best-of-both worlds event-driven system + RDBMS-storage, it's very easy to end up with worst-of-both-worlds. Hello distributed transactions!
Again, you just should think about all the ways you want to use it and the maintenance/uptime requirements your users are going to have in advance.
Trying to manage a highly available and durable rabbitmq or other message system that can also be recovered from backup to an offsite mirror infrastructure in the worst case is actually incredibly difficult. Usually these systems are designed with the assumption that you can just regenerate messages based on database state anyways in worst case scenarios.
In this use case your database already is highly available and can recover on an offsite backup if you have suitable wall shipping going on. So you've done all the hard work once, may as well reuse it unless you truly have some mind bogglingly large message throughput needs.
Finally, we had a need of a queue that was more than just first in first out. We wanted to fairly balance workloads across users and tenants. Whenever you have such a need postgresql lets you design this type of queue far easier than trying to do some elaborate multi-queue setup with a traditional queue.
While the GP may have been referencing the former, embracing "PostgreSQL for Everything" often prohibits the latter.
I think that the "use Postgres for everything" messaging was a necessity, even if it is overstated. Use it until you can demonstrate it doesn't meet your near term needs. When that happens, shift. It wasn't that many years ago when I'd enter situations where people were knee deep in FAANG level infrastructure when postgres on a relatively small instance would have more than been sufficient. I'd suggest they look at converting to postgres to save money & all the energy they spend maintaining their soup. "It won't scale the way we need it!". Sometimes they were demonstrably wrong. Other times they were half-right, in that the real problems was terrible decisions made at the software layer, leading to a situation that required heavier duty infra. Almost never were they actually right* though.
Might they have been right 5 years later? Perhaps. But I know for a fact that none of the ones I encountered were.
As the old saying goes; just because you can do something doesn't mean you should do it.
> I think that the "use Postgres for everything" messaging was a necessity, even if it is overstated. Use it until you can demonstrate it doesn't meet your near term needs. When that happens, shift.
The problem with this approach is, once "use PostgreSQL for everything" is identified as being no longer be feasible, it has already become an inextricable component underpinning system functionality. Thus making "[w]hen that happens, shift" extremely difficult.
Contrast the above with only using PostgreSQL (or any other RDBMS) to manage data and their relationships, eschewing stored procedures as well, and the "when <insert condition> happens, shift" decision becomes much more feasible to entertain.
If you know you're gonna be ok with that for a long time, go nuts. I'm just saying: think about it in advance!
The cost pain for spikes is also a thing - some of Aurora's billing models look potentially promising but I haven't used them in practice - though it's also somethings that's harder to avoid with alternatives. Distributed DBs aren't generally super friendly to dynamic scaling IME.
My example for android app:
https://f-droid.org/pl/packages/io.github.rumcajs.offlineweb...
Note that I am not android experienced programmer, and I am still learning.
SQLite is embedded for local applications with one writer mostly.
Postgres is for a client-server architecture with many writers.
When you start a project, you generally know which architecture you need.
In my previous company we used MongoDB, for almost everything, including text logs, request logs, job-queues and small template files. We added S3 since storing terabytes of files in a database is expensive. Now, I wouldn't choose MongoDB for a new app, since the data model and query language suck, but it performed reasonably across many use-cases.
For my next application I will use Postgres as primary database. Probably will integrate S3 before going live, to avoid the necessary data migration later, but haven't decided yet if that's a premature optimization.
use what you're familiar with, until it stops working. then use something else. postgres just goes a lot further than a lot of other tools before you get to the "use something else" phase. and postgres is the database a lot of people are familiar with.
- I run my B2B application, Postgres only, and it is perfect for my 50k MAU. No complaints, sleeping soundly with the low complexity and a two man team.
- I work at FAANG, where we have 1 billion DAU, and this is a joke. Would fall over immediately. The dedicated ops teams for Kubernetes, Elastic, and Redis have never complained about scaling issues.Size matters!
For most of the application out there elastic (or kafka or any other specialized tool) is just too much(and too costly). They can do fine with postgres or mysql. Actually, I'd argue that in a lot of cases even postgres is too much, probably sqlite is enough.
Nobody is saying that a huge ecommerce store with complicated filtered search logic should throw away their Elasticsearch cluster and switch to Postgres.
I tried to use rabbitmq for a small app, installed it, configured it and then it didn't work. Spent a day jumping through hoops getting it right.
Dumped it and used postgres, in half an hour. Worked like a charm.
PG is great and I work with it daily, but it's also not a problem to think about scale early and at least have a notional plan for what to and how to know when scale is becoming an issue in your system as you're designing it. Even PG is overkill and sqlite is more than enough for some of my projects.
There are a lot of specialized tools available, but you definitely don't need to put every one in your toolbox. Experience and observation help you make those edits -- and of course there's almost always room for improvement, but "good enough" definitely exists (until it doesn't anymore :D).
If you’re just starting out, keep things simple. Otherwise, you probably already know exactly why you need something more than Postgres.
What do you suggest for a language like Malayalam which has no native support, preferably with low RAM requirements?
Posts like this can be tiresome, yet the general consensus among developers seems to be "yeah, Postgre/SQLite is ok for 99% of the cases, but MY case is going to be in the 1%, because I am going to be the next Facebook".
Yes Postgres can work in the small for a lot of things, it can even work at surprising scale if you use it according to its strengths.
But if you use it for things it doesn't shine at, at inappropropriate scale - you'll almost certainly run into issues. And resolving those can often be a bigger challenge than choosing a more suitable solution in the first place. But often I think younger/less experienced engineers just have to burn themselves, thus why this never seems to die.
Yeah, this has nothing to do with Postgres. If the service is accessing a database that isn't internal to the service, then that database is already a standalone service in itself.
> The server is not the most important thing, and each new kind of server that exists in the system is an appreciable increase in maintenance burden.
Sure, but compared to what? It's right to consider complexity, but understanding tradeoffs requires depth. The OP's original premise was that learning all the things about specific queuing services was unnecessary chafe; that INSERTs, SELECTs and UPDATEs are all anyone needs.
The maintenance burden sits with your producers & consumers (or publishers/subscribers, whatever your nomenclature...), whether you want it there or not.
My learned experience is that as soon as you start moving messages that are beyond trivial and carry different operational characteristics, you're going to have to understand those deeper concepts anyway.
- Query planner is much worse (just yesterday I had to USE INDEX to sped up a query by 300x, I'm near-certain postgres would just have gotten it right) - Indexes are much more limited: no GIST, no GIN - No transactional lock (`pg_advisory_xact_lock` in postgres). This one was very surprising, it's a really useful thing and I had to implement it myself as a lock table
One application I'm working on (CRUD, but with fairly complicated business logic) is seeing large performance improvements, especially on reports which join many rows from 10+ tables.
I regularly test the same dataset on both PostgreSQL and MySQL -- we support both, and this new optimizer puts them pretty close.
The current default optimizer has some pathological cases where reports run 8-10 times slower on MySQL compared to PostgreSQL. Not with this one.
https://blogs.oracle.com/mysql/the-hypergraph-optimizer-is-n...
PostgreSQL also had more features back then, e.g. the JSON support is very nice if you need to do anything that doesn't neatly fit into the relational model.
Then people who knew something got involved (or likely were involved all along - but I never followed MySQL so I'm not sure) and fixed those because they matter and suddenly the crowd shut up.
Also postgres is a "proper" db, so I'm glad it generally won out.
And MySQL apparently still doesn't support transactional DDL (i.e. BEGIN, ALTER, ALTER, UPDATE, COMMIT), which is quite nice for db schema version migrations.
MySQL caught up as well as far as I know, but it still may have some poor defaults that are widely used.
Another thing: those were times when web applications were practically 99% reads, and not so great ACID was a non-issue.
Postgres is OK, but it has really a lot of quirks that are not that obvious.
I hate it when people already start out with 10 or more different systems/services for a few messages per second.
Also it is very "unconventional". Everything has to be done in its weird and quirky way
That transition can be super super painful as you don't quite have enough work for the dedicated person.
You could argue it's because postgres requires less poking though I would say you don't need the DBA for when things go right.
Of course most people are just handing the management off to the cloud and that's potentially why, but it doesn't cover everything
Postgres, on the other hand, has a million knobs, many of them interact, you'll find conflicting advice for some of them, and it can rapidly fall over if you aren't keeping a close eye on long-running transactions. It's also more performant than MySQL in _most_ situations (hello, clustered index), if you've tuned it correctly. It also of course has far more extensibility out of the box, with tons of index types that are extremely helpful, if you know how and when to use them.
This difference is why I'm always frustrated when people parrot "just use Postgres" as though that solves all problems. It's an extremely powerful tool that can replace most of your stack, yes, but it also would really, really like you to RTFM. Not random Medium blog posts, the canonical documentation.
This is the myth people who parrot "just use Postgres" believe. It is false, obviously
I became the Kafka guy at my current company, it took me about a week of reading and every time I had further question, I didn't have to bother anyone, I could Google and get data I needed.
When it's some NIH thing, you have to bother coworkers and knowledge is whatever is in YOUR company knowledge base with no ability to get knowledge from outside the company.
EDIT: You could also leverage contractors or outside support if not homegrown software.
I worked at a startup with massive NIH syndrome, once. We even used our own in-house programming language, because it was "better than anything else out there on the market." It did have a lot of nifty features that others don't have: a pretty novel type system, programmatic macros, a built-in build system and other fun bells and whistles -- but also not-so-fun ones like having no syntax highlighter, LSP, or debugger, and having to constantly shuffle around your code to avoid ICEs in the compiler.
The compiler wasn't the product, but we found ourselves fighting that thing more actively than any of the real problems our custom programming language was supposed to solve. The CTO found himself spending all his nights and weekends mostly trying to get the compiler to not explode.
A few years later, after I had long left (for that reason, among many) I heard they switched to Python. Can't imagine how long it took them to get that all rewritten.
A DSL can work, but not for the features you list. Those features you already get from existing languages anyway!
If you need general programming language features like excellent type system, programmatic macros, a build system (doesn't need to be built into the language), etc... then use a general purpose programming language.
I have a DSL for backend/endpoints, and exactly none of those are in my feature list. What it has are things like easy way to specify access-control directives[1], the SQL query to execute, mapping request variables to SQL parameters, mapping SQL results-sets to response fields, etc.
I have another DSL for a test program. Both of those DSLs have specs that's literally 2x screens of bullet points and examples. LLMs can output those DSL programs because the spec for the DSL is so small.
For general purpose programming stuff (while loops, conditionals, etc) my DSLs break out to Python.
A good indicator that you shouldn't be creating a new language for production is when you find yourself implementing conditionals, loops, etc.
===========================
[1] Limit endpoint to specific roles, or members of the same team, or both, or even to the user itself - someone calling `/user/profile/update` should only be allowed if the profile they are updating is theirs, for example.
Anyway, it is a basic practice of keeping test and dev environment as close as feasible to production, to avoid missing issues and wrong assumptions.
Containers are great for this during development.
Testcontainers are great for tests in particular when you don't want to use some mocked in-memory DB because those have the same issues as using a different DB during development: https://testcontainers.com/
why not just construct SQL queries in a type-safe DSL, like, say, JetBrains Exposed does? you are writing what's basically SQL that your compiler understands and your existing tooling checks for free. (granted, C# may need an additional Roslyn analyzer, but it's still simpler than either guessing what transaction LINQ will make or writing SQL in strings.)
But there are still plenty of cases I would say BYTEA is perfectly reasonable choice.
I don’t even think SQLite lets you fully update a table schema.
If you start trying to use it for sql and not just storing rows you run into these everywhere. SQLite is not serving the same needs as Postgres.
CREATE TRIGGER update_customer_emails_trigger INSTEAD OF UPDATE ON customer_emails BEGIN UPDATE customers SET email = new.email, name = new.name WHERE id = old.id; END;
Maybe that's not as flexible as you need.
SQLites process for table updates can be pretty onerous if their ALTER TABLE lacks support. Its a 12 step process the docs have the temerity to call "simple" instead of "tedious and risky" - https://www.sqlite.org/lang_altertable.html#otheralter
[1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
though they also have "pglite" running in wasm.
same concept though, sync slices to an embedded db.
In 2026 that advice feels antiquated. SQLite now is absolutely useful now for concurrent, mutli-writer applications.
If you mean database size, SQLite can handle massive amounts of data. I've seen 281 TB quoted as theoretical max size.
From multi-client architecture and with regional HA?
Size of the data, read/write ratio, number of "requests"... lots of axes that change how much pain or not you're in by just pointing to pg
Or maybe you are looking at it from "just swipe your credit card at AWS" perspective, in which case "just use Postgres" articles are for a different audience.
Then a system gets most of the benefits of not accidentally making it torturous to rearchitect for scale, without paying the headcount / complexity cost until it's needed.
I think both your questions, about correctness and about the comparison point, are answered by thinking through the empirical metrics you're trying to improve.
I often feel a bit out of step with other engineers on this point, but to me, "correctness" is not a binary yes/no, it is also a continuum. It's one of the metrics you probably want to be pushing to a large number of 9s, but trying to get to actually 100% has low ROI for what most people work on. (Not for everything! But for most things.) If you can already get to five 9s with postgres, but more 9s with kafka, is that worth it? Maybe, but maybe not. If you're missing way more messages than that and corrupting data because of it or some other bad thing, and this is the culprit, then that's certainly a good time to compare the trade offs of fixing your implementation or switching to a "proper" system for this.
And to "compared to what?", the answer is, compared to the current values of the metrics you've decided you care about. One of those might be the complexity of the implementation and the necessity to maintain specific expertise in a bespoke implementation of non-trivial complexity. This, to me, is usually the compelling reason to "buy" rather than build. But I think it is too often left too implicit and vibes-y instead of making it explicit and seeking to quantify the impact.
Edit: Never mind, found it https://www.postgresql.org/docs/19/pgplanadvice.html
It was fun while it lasted and I had a lot of fun working on it. But it was really not a good business fit. The programmatic macro system was supposed to allow us to build customer-facing DSLs on top of it, but everybody just wanted Python anyways.
> The programmatic macro system was supposed to allow us to build customer-facing DSLs on top of it,
Honestly, it sounds a lot like Lisp.
As a former Lisper, I don't doubt that it was a bundle of fun :-)
During the dot com era it was common to develop and launch on MySQL with the intention of migrating to something else if they became successful (though your typical LAMP stack developer regarded Oracle and SQL Server as being deeply 'weird', so many were willing to stick with MySQL despite the well-known limitations of MyISAM).
From where I'm standing, it seems that PostgreSQL became clearly preferable for new projects from the mid 2000s onwards, but it was only the Oracle acquisition that began to push existing users off MySQL.
edit to add: MySQL did have operational advantages even later than mid 2000s since it had master/master replication from very early (I don't know how sound it was, I doubt it was perfect). That was a real reason to choose it. We still don't have it in Postgres without extensions and even there citus is not really the same.
(I remember it being heavily touted in the first edition of the O'Reilly "High Performance MySQL" book. The second edition was about twice the length, with most of the additional pagecount going into detailed explanations of why you should actually be very careful and do lots of testing before deciding to rely on master/master!)
Actually, I remember that one of the drivers away from MySQL before Oracle came along was the 4.x and 5.x period, when there were various performance regressions. And even when those were sorted out, the introduction of InnoDB made people realise that MySQL's apparent speed advantage was really just down to MyISAM lacking referential integrity and transactions.
Certainly, there were people clinging on to MySQL 3.23 for read-heavy data warehousing applications for a very long time.
Yes we saw the same thing play out with MongoDB. Ack without fsync is indeed very fast.