Why PostgreSQL High Availability Matters and How to Achieve It

Unfortunately Spanner isn’t open source. Yugabyte and Citus are close but have annoying issues. Cockroach isn’t 100% compatible (and has its own issues) and things like FoundationDB which are truly HA and comparable to Spanner in terms of consistency and fault tolerance are not easily plugged into Postgres as the underlying storage engine since sadly it’s only a key value store.

edit: when I say close, I'm talking strictly about HA, not general functionality.

lately I've been thinking of using FoundationDB, which is closest to Spanner in terms of ACID and serializability and mvsqlite.

Then, I was thinking, since SQLite doesn't have online schema changes (nor does mvsqlite) to have a schema such as:

   [UUID, Data, Version, CreatedAt, UpdatedAt]

In SQLite you can also create indexes on expressions so technically all of your JSON or Proto could also have indexes.

When you get into sharded DBs, a lot of limitations aren't obvious at first. I've used Citus a long time ago and Spanner much more recently. Spanner feels almost like NoSQL: Each table is like a distributed KV store. Indexes are just tables where the PK is the indexed col(s) and the val is the main table's PK, though you can also store additional cols (aka denormalize) in the index to avoid an extra join. You can't combine two indexes to filter on two cols; you need one composite index. More advanced things like order-limit, WHERE (NOT) IN, and subqueries tend to be slow. The query planner is pretty limited, and often I just force things. You also have to really know what you're doing with the pkeys. Which is all understandable, given its requirements.

I forget the limitations of Citus ACID, but they're significant. Spanner has full ACID, but even basic operations are quite slow. Single-node Postgres actually doesn't have full ACID unless you put it in the slower SERIALIZABLE mode, but you don't really need it.

I agree with those who say it's better to focus on sharding at the application layer if possible. If you can't do that, it's probably due to the underlying nature of your problem, in which case sharding at the DB layer in an efficient way tends to be even harder. Sometimes it makes sense, but it's not magic.

or for k8s their operator: https://github.com/zalando/postgres-operator (docker image: https://github.com/zalando/spilo) we've also tried other operators which were easier to get started, but they failed miserably (crunchyrolls operator is basically based on the zalando one)

I looked at Planetscale thinking I might be willing to just use MySQL, but even if I were to take a risk with their confusing pricing it turns out there are no foreign keys.

And there is no equivalent of Cloud run on AWS.

It just seems like there is a gap here in when it comes to managed databases, not just GCP laking any kind of "free tier" option, but just a lack of players in the space between free and established business pricing for HA.