Ok, hot-take title aside: this is specifically with regards to relational databases (and hybrid document stores if you use Postgres that way), obviously if you need time series, graph, key-value, there are better engines…

But without further ado.

Yes Just Use Postgres

The idea to write this came from a Reddit post: Why does PostgreSQL + .NET feel so much better than SQL Server these days?

Here at RoushTech we average shipping a new mainline market product about every 2 years, with smaller products dotted around, it's just how the work comes in, but regardless when it comes down to database choice we always "evaluate what our options are", then we choose Postgres, we really should cut out the middle man on this.

What About Other Engines?

OK, again keeping this fair and sticking to RDBMS platforms, let's talk about our options:

  • Microsoft SQL Server - Okay, I'm going to be honest, I love MSSQL… until I have to license it. It's an extremely powerful engine, a good set of tools, some really nice failover options. Two major downsides: we've seen massive performance degradation when running on Linux due to how Microsoft got it to actually run on Linux, and secondly, the biggest one… Licensing. In the past we'd easily drop thousands or tens of thousands on licensing. It's become a major barrier. 2 months of development for an MVP or your SQL server license?
  • MySQL/MariaDB - A very outdated engine, InnoDB hasn't seen meaningful improvements in forever, Oracle is effectively doing nothing with it (and no, MariaDB isn't different enough to warrant its own line), and my favorite deal-breaker in why we almost never run it. ALTER TABLE STATEMENTS ARE IMPLICIT COMMITS, that's right! When your software is running a migration, if it fails, it'll leave the DB in an invalid state because every table alter breaks out of your transaction! That's great!
  • Oracle - Did you look at Microsoft's license and determine you were a glutton for punishment beyond that?
  • [Insert Scale-Out DB Here] - Platforms like CockroachDB are cool, but scale-out DBs are a completely different class, complexities change a lot, design options are limited, latency floors are usually pretty high.
  • SQLite - OK I love SQLite but only really great for local/embedded DBs.

OK But How Does Postgres Stack Up?

This is the wonderful part, and some of the really fun parts people miss out on:

  • It's extremely performant in a lot of cases, we've shoved a ridiculous amount of traffic through it.
  • You can use it as a doc store! Yeah, okay, I usually don't opt for using engines "incorrectly" but Postgres has come a long way, the ability to dig and index into JSON columns is great.
  • You can beat the everliving snot out of an RDBMS if you're a half-decent engineer. We've had single-instance Postgres systems doing tens of thousands of non-trivial transactions per second, and yes, that's even after someone trots out the "well this key-value or document store can do 400k/sec" comparison everyone loves, sure, but those 400k are trivial ops on a single key or document. One non-trivial Postgres transaction is doing joins, constraint checks, and atomic writes across multiple tables. They're not the same unit and the comparison only flatters them if you pretend they are.
  • Has a good amount of tuning options allowing you some decent influence on performance.
  • Has a healthy extension market with a lot of additional bells-and-whistles you can add (PostGIS for spatial, pgvector for embeddings/RAG, TimescaleDB for time-series).
  • The MVCC system is complex but powerful.

Where Am I Going To Get Bit?

  • I've had the query planner lie to me about costs, just straight up lie, "this is cheap" then thread-spins.
  • Like any RDBMS, beware of locking, a sloppy ALTER TABLE statement may be the difference between something that takes 100ms and 100 minutes (or longer). Learn how the engine allocates data.
  • There are many ways to set up clusters, each with their own pros and cons.
  • Postgres uses a process-per-connection model (every client connection forks its own backend OS process), which gets expensive at scale. PGBouncer is your friend, and your enemy. There is no free lunch with connection limits and port limitations, PGBouncer can make these logistics easier, but port exhaustion is still a thing.
  • If you need something truly scale-out, it likely isn't here, but you should also be making $100+m/yr, why are you even here?
  • Postgres is case-sensitive by default, unlike basically every other DB engine on the face of the planet. Configure case-insensitive collations on day one or you'll be patching application bugs forever.
  • Run VACUUM FULL, you'll have a good time (insert massive amounts of sarcasm here). Imagine a hard drive defrag from back in the day but it locks your hard drive while doing so. MSSQL got online index rebuilds right at least, at least if you're paying for Enterprise.
  • Major version upgrades can be annoying, not super bad though.

I Hate To Say It…

But yeah, just use Postgres, our options have narrowed over the years, and I agree, it's annoying, I almost feel like I'm not doing my job, but Microsoft's licensing and Oracle's lack of caring for MySQL did that for me.