Discussion
I don't quite understand why some people default to UUIDs as Primary Keys in Postgres. There are some situations where that makes sense, like database or service distribution/decentralization but there are significant performance drawbacks.
If you only have one primary database, bigint PKs are much more efficient and simpler to reason about. I guess even in a local sharded setup.
If you need a non-guessable public ID, just add a UUID column.
@hukl Postgres 18 added support for UUIDv7 which fixes most of those drawbacks.
@albrecht still is twice the size of bigint, not only in the tables, in indices and foreign keys plus uuid7 will make people think they can depend on sequence. With decentralized uuid4 generation, people neglect how good their available randomness is and assume it being collision free by definition. Writes are still slower, queries need more memory and joins can amplify that.
@hukl mainly because migrating when you have to is a nightmare.
@hukl with uuid you can ensure that foreign key columns are pointing to the right table. If you use incremental ids you likely have several tables with the same set of primary keys.
@th3_cr0wl3r @MaZderMind I'm well aware of the use cases for UUIDs as primary key - I'm just saying I don't understand ppl doing it by default because it comes with its own set of significant (at least to me) drawbacks
@hukl @MaZderMind I use uuid4 by default because changing it in the future because switching in the feature is PITA. In the past I ran into more problems caused by integer primary keys than uuid.
Wrong referenced tables, string/float/int ducktyping issues, idiots trying todo math with primary keys, etc
IMO The main downside of uuid4 is comparatively low insertion speed. But if this is known a switch to uuid7 is easy and does not potentially break dependencies.
@th3_cr0wl3r @MaZderMind Plus the same uneducated users will assume they can depend on the sequence being correct with uuid7.
@th3_cr0wl3r @MaZderMind I'd attribute this mostly to user error though 😊 Still twice the size in tables, indices and foreign keys. Writes are slower, hash joins can amplify the impact on performance and memory consumption. As long as you consider this and deem it less important than the advantages it's all good. But most people I observe defaulting to UUIDs never even looked up potential drawbacks.
@hukl Have been burned once too often by a customer preparing a complicated data-structure on the test-system, asking me to quickly copy it to prod (where, of course, all bigint-ids from test are already used). Renumbering hundreds of rows semi-automated while hoping that no new data was added to prod in the meantime is a royal pita.
Install bonfire.cafe
Get the full app experience
