Re:Unexpected downsides of UUID keys in PostgreSQL
by biztos on Thursday June 22, 2023 @09:41AM
(#36432018)
I'm a big fan of SERIAL and BIGSERIAL, for aesthetic/cognitive reasons more than performance, but it can be really nice to have a data-generating process generate the primary ID of a piece of data, which you can then log before you put it in the database and look up the same ID everywhere when debugging.
Not to mention the distributed database situation.
In fact I like that enough that I sometimes use ULIDs for secondary unique keys just so I have something to log, even if the primary key is numeric.
It's nice that you can store ULIDs in Postgres natively as UUIDs, and it's really nice to have a timestamp embedded in the ULID if you ever need it... but it's also really tempting to use it in public-facing stuff and thus leak your creation timestamp.
Re:Unexpected downsides of UUID keys in PostgreSQL
by danpalmer on Thursday June 22, 2023 @10:10AM
(#36432436)
Absolutely, these are also good things to take into account. There are really situations for all of these things, and this is why I don't like the approach of trying to "solve" this problem. Lots of people say "UUIDs solve IDs", or "UUIDs cause too many issues to use", or "ULIDs solve UUID issues", and in reality this is just another place where there are trade-offs, and what works for one company/project/dbms/table may not work for another, or may even cease to work over time as requirements or scale change.
I find auto-incrementing IDs far more elegant in many ways. They are much easier to make sense of for users, they give you some loose metadata (ordering, sometimes a rough time range) which can be handy in debugging stuff.
At my previous workplace most things were auto-incrementing IDs, and while we got bitten by them a few times, there was significant debugging value in seeing a user ID as an integer. Internally, order IDs and customer support ticket IDs were also auto-incrementing, but we offset them so that even numbers were orders and odd numbers were support tickets, and this helped quite a bit with debugging or even just non-technical users sending each other references. Not that this can't be done with prefixes on UUIDs with a bit of extra work.
I wouldn't necessarily recommend auto-incrementing IDs or UUIDs over the other, but I don't think one is more elegant at all.
> to optimize access times by a millisecond or two. UUID is totally worth the cost.
2ms * 20 queries is 40ms, which could take a typical page load for a web app from 150ms to 190ms, which is quite a regression.