3 ms·

>> . Every table's primary key should be a single `bigserial id` col unless you have strong reasons and benchmarks showing otherwise. ("They have UUIDs" isn't a

>> . Every table's primary key should be a single `bigserial id` col unless you have strong reasons and benchmarks showing otherwise. ("They have UUIDs" isn't a good enough reason; simply store those in a separate col.) As with all "rules", context is really important. Without knowing anything about your context, its hard to really agree or disagree with your rules. If I could give my 1995 self a tip though, it would be to use a UUID as the primary key for all tables. More specifically I would tell him to nske it a client-generated UUID. it certainly wasn't necessary-then-, but 15 years later it would have made do many problems into not-problems. Specifically as we wanted to merge data, put subsets on remote devices, and so on. Sequential id fields are by far my biggest regret. But again, that's me, for my context. Ids were fine in the original context. But then the context changed... Yes, I'm aware of the space and performance implications - but in my context those are way, way, down the problem tree.

thorin 3y ago
If you'd done UUIDs in 1995 ( if that was even possible ) you'd have a had horrendous indexing problems, mainly relating to clustering. This was an issue when I started using SQL Server in around 2006. Was also a concern in Oracle so I would be surprised if Postgres wouldn't have had similar problems. I think they are a non issue now though. A discussion of some of the pros and cons: https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega https://stackoverflow.com/questions/11938044/what-are-the-be...
- hot_gril 3y ago
  In my experience, it's not horrendous in Postgres but still noticeably worse than bigserial. There are often reasons to have UUIDs as secondary keys. But your DB uses primary keys for disk layout, joins, etc, so I've never seen a good reason to use UUIDs there. Even the CitusDB (horizontally-scaling Postgres) examples use bigserials. I've always regretted using UUID pkeys and never regretted using bigserial. Side bonus of bigserials is you get ordering, which matters in some contexts.

thorin 3y ago

If you'd done UUIDs in 1995 ( if that was even possible ) you'd have a had horrendous indexing problems, mainly relating to clustering. This was an issue when I started using SQL Server in around 2006. Was also a concern in Oracle so I would be surprised if Postgres wouldn't have had similar problems. I think they are a non issue now though. A discussion of some of the pros and cons: https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega https://stackoverflow.com/questions/11938044/what-are-the-be...

hot_gril 3y ago
In my experience, it's not horrendous in Postgres but still noticeably worse than bigserial. There are often reasons to have UUIDs as secondary keys. But your DB uses primary keys for disk layout, joins, etc, so I've never seen a good reason to use UUIDs there. Even the CitusDB (horizontally-scaling Postgres) examples use bigserials. I've always regretted using UUID pkeys and never regretted using bigserial. Side bonus of bigserials is you get ordering, which matters in some contexts.