But then the DB Team – if you have one – is responsible for 50 databases, each full of their own unique problems.

This will undoubtedly go over poorly, but honestly I think every data decision should be gated through the DB Team (again, if you have them). Your proposed schema isn’t normalized? Straight to jail. You don’t want to learn SQL? Also straight to jail. You want to use a UUIDv4 as a primary key? Believe it or not, jail.

The most performant and referentially sound app in the world, because of jail.

What’s wrong with uuidv4 as PK?

Serial integers always work better than any uuid as PKs, but the thing with uuid4 is that it disrupts any kind of index or physical ordering you decide to put on your data.

Uuids are really for external communication, not in-system organization.

FWIW this isn’t true anymore with newer uuid schemes like v7 that are roughly time sortable.

Serial index forces a synchronisation point on every entity that can create records. If this is only ever a single database that’s fine but plenty of apps can’t scale this way.

They don't. Clustered databases deal with parallel generation of them just fine.

They require periodic synchronization. What isn't a big deal at all and is required by many other database features.

If you have a sharded DB, each instance can get its own range of ints, which are periodically refreshed.

PlanetScale uses int PKs [0], and they seem to have scaled just fine.

[0]: https://github.com/planetscale/discussion/discussions/366

Anything non-k-sortable in a B[+,-]tree will cause a ton of page splits. This is a more noticeable performance impact in RDBMS with a clustered index (MySQL's InnoDB, MS SQL Server) [0], but it also impacts Postgres [1] in multiple [2] ways.

[0]: https://www.percona.com/blog/uuids-are-popular-but-bad-for-p...

[1]: https://www.cybertec-postgresql.com/en/unexpected-downsides-...

[2]: https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-pa...

What's the best non serial option for PKs in your view? Or do you prefer dual PK approach?

No single team should not be responsible for all databases. If such team exists they will either become bottleneck for every other team (by auditing carefully each schema change) or become bloated and not utilized 90% of time, or (most common) they will become nearly useless or even harmful - they will not be really responsible and they will act as dumb proxy - they will introduce latency to the schema updates, but they will not bother to check them very well (why would they? they are not responsible for the whole product, just for the database), some DB refactoring/migrations will be totally abandoned because DB team will make them too painful.

DB team could act as an auditor and expert support, but they should never be fully responsible for DB layer.

> If such team exists they will either become bottleneck for every other team (by auditing carefully each schema change)

That’s the point. Would you send a backend code review to a frontend team? Why do DBs not deserve domain expertise, especially when the entire company depends on them?

> they are not responsible for the whole product, just for the database

I assure you, that’s a lot to be responsible for at scale.

> DB team could act as an auditor and expert support, but they should never be fully responsible for DB layer.

Again, the issue here is when the DB gets borked enough that a SME is required to fix it, they effectively do become responsible, because no CTO is going to accept, “sorry, we’ll be down for a couple of days because our team doesn’t really know how this thing works.”

And if your answer is, “AWS Premium Support,” they’ll just tell you to upsize the instance. Every time. That is not a long-term strategy.