(2024-08-16) McCue Just Use Postgres

Ethan McCue: Just use Postgres. Advice: When you are making a new application that requires persistent storage of data, like is the case for most web applications, your default choice should be PostgreSql.

Why not sqlite?

sqlite is a pretty good database, but its data is stored in a single file. This implies that whatever your application is, it is running on one machine and one machine only. Or at least one shared filesystem.

If you are making a desktop or mobile app, that's perfect. If you are making a website it might not be.

Why not DynamoDB, Cassandra, or MongoDB?

The thrust of it is that databases that are in the same genre as DynamoDB - which includes Cassandra and MongoDB - are fantastic if - and this is a load bearing if:
You know exactly what your app needs to do, up-front
You know exactly what your access patterns will be, up-front

this sort of database is basically a giant distributed hash map

Whatever queries you need to make, you need to encode that knowledge in one of those indexes before you store it

It's annoying because, especially with MongoDB, people come into it having been sold on it being a more "flexible" database. Yes, you don't need to give it a schema. Yes, you can just dump untyped JSON into collections. No, this is not a flexible kind of database. It is an efficient one.

The artist formerly known as Redis is best known for being an efficient out-of-process cache. You compute something expensive once and slap it in Valkey so all 5 or so HTTP servers you have don't need to recompute it.

Why not Datomic?

You don't store data in tables. It's all "entity-attribute-value-time" (EAVT) pairs.

Why not Valkey?

It only works with JVM languages. Outside of Clojure, a relatively niche language, its API sucks.

Why not XTDB?

Clojure people make a lot of databases.

It's new. Its SQL API is something that popped up in the last year. It recently changed its whole storage model. Will the company behind it survive the next 10 years? Who knows!

Why not Kafka?

It is a very good append only log. It works amazingly well if you want to do event sourcing

Up to a certain scale, a table in Postgresql works perfectly fine as an append only log.

Why not ElasticSearch?

Is searching over data the primary function of your product?

Why not MSSQL or Oracle DB?

Genuine question you should ask yourself: Are these worth the price tag?

Why not MySQL?

My problem is that I've only spent ~6 months of my professional career working with it. I genuinely don't know enough to compare it intelligently to Postgres.

Why not some AI vector DB?

AI is a bubble. A load-bearing bubble, but a bubble. Don't build a house on it if you can avoid it.