5 ms·

Yeah I recognize the arguments for UUID keys: - Avoids people being able to just iterate through records, or to discover roughly how many records of a thing

Yeah I recognize the arguments for UUID keys: - Avoids people being able to just iterate through records, or to discover roughly how many records of a thing you have - Allows you to generate the key before the row is saved I think I default to auto-increment ID's due to: - Familiarity bias - They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they might be created) - Easier to read (when you have less than +1,000,000 rows in a table) I agree and think you're right in that UUID's are probably a better default. Though you can never find a "definitive" guide/rule online or in the docs unfortunately.

yardstick 5y ago
I prefer UUIDs as well but one other benefit of an integer type key is in the index. UUIDs, especially v4, make for bloated indexes. If there’s a very specific performance reason I’ll use int, otherwise uuid.
- fdr 5y ago
  This is what I do as well. Most schema structure is on tables that are not very large, and it's nice especially when dealing with multiple environments to prevent errors, or to produce schemas with some polymorphism or joins between several tables without a chain of id-resolving joins in-between. There are UUID variants that can work well with indices, which shrinks the case for big-integers yet further, to micro-optimizing cases that are situational.
- tehlike 5y ago
  You can use sequential guids. That's what I do. Works well for not bloating indices.
- AlfeG 5y ago
  One of hidden pros is that UUID has no gaps visible to product owner or manager. More than once in my life I have try convince people, that the gap in sequence caused by transaction rollback is OK. Several times we were «fixing data» in db manually
spiffytech 5y ago
> They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they might be created) UUIDv7 (currently a draft spec[0]) are IDs that can be sorted in the chronological order they were created In the meantime, ulid[1] and ksuid[2] are popular time-sortable ID schemes, both previously discussed on HN[3] [0] https://datatracker.ietf.org/doc/html/draft-peabody-dispatch-new-uuid-format#section-4.4 https://datatracker.ietf.org/doc/html/draft-peabody-dispatch... [1] https://github.com/ulid/spec https://github.com/ulid/spec [2] https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid [3] ulid discussion: https://news.ycombinator.com/item?id=18768909 https://news.ycombinator.com/item?id=18768909 UUIDv7 discusison: https://news.ycombinator.com/item?id=28088213 https://news.ycombinator.com/item?id=28088213
- gavinray 5y ago
  Wow, neat. TIL
- stjohnswarts 5y ago
  Hmmm that's really useful :) . thanks!
- codetrotter 5y ago
  Yup this. Ulid is great, and implemented in many languages. For example there’s a crate named Ulid that you can use that gives you ulid’s in Rust.
  - djbusby 5y ago
    Came here to also hype ULID. It's just like INT, but 128bit, timestamps, every lang, and moving data around is trivial because no ID ever collide with anything else ever. Makes finding crap in my logs easier. (But UUID has that feature too) - it's the sorting that does it for me.
  - ChristianGeek 5y ago
    Is there a PostgreSQL extension for ulid?
jrockway 5y ago
Something I always liked but have never done -- start auto-increment IDs at some number other than 1. I worked on a system where account IDs started with 1000000, invoice IDs started with 2000000, etc. That way, if you saw a random number laying around like "1001234", you knew it was an account ID. "2000123", an invoice! I don't remember how often it helped things, but it was more than 0 times, and it always "felt" good. I never loved the implicit upper bound, but it was also never a problem. (And remember, the values are in different tables, so the only disadvantage is that your millionth user has ID 2000000. To the trained eye they look like an invoice; but there is no way for the system itself to treat that as an invoice, so it's only confusing to humans. If you use auto-increment keys that start at 1, you have the same problem. Account 1 and Invoice 1 are obviously different things.)
- Twisol 5y ago
  > but there is no way for the system itself to treat that as an invoice, so it's only confusing to humans. I agree in principle, but then you have Windows skipping version 9 because of all of the `version.hasPrefix("9")` out in the world that were trying to cleverly handle both 95 and 98 at once. If a feature of data is exploitable, there's a strong chance it will be exploited.
- 9dev 5y ago
  I use this for internal items, as in: real customer IDs start at 10,000, so we have 9,999 possible users/invoices/whatever for our own usage in the database. That makes it easy to filter demo accounts of your sales employees, or production test accounts, in queries: just add an offset. Re: the upper bound: if you reach a million customers, you have lots of other nice problems, like how to spend all the money they earn you :-)
  - gavinray 5y ago
    This is clever. You could use "is_test_account" or whatnot, but that adds an unnecessary column (for ~99% of records) I like this -- will keep it in mind!
- avar 5y ago
jrochkind1 5y ago
The person you're replying to didn't even mention those advantages though! The ones they did mention are more significant to me. Of your disadvantages... if I wanted to know when rows were created, I'd just add a created timestamp column. But "easier to read" is for real -- it's easy when debugging something to have notes referencing rows 12455 and 98923823 or in some cases even keep them in your head. But UUIDs are right out. And you definitely don't want to put UUIDs in a user-facing URL -- which is actually how you get around "avoids people being able to just iterate through records", really whether you have UUIDs or numeric pks I think putting internal PK's in URLs or anything else publicly exposed is a bad idea, just keep your pks purely internal. Once you commit to that, the comparison between UUIDs vs numeric as pks changes again.
- Semaphor 5y ago
  > I think putting internal PK's in URLs or anything else publicly exposed is a bad idea Why? Both as a user and as a dev I love that I can just change the PK to get to a specific post, item, whatever instead of changing the whole link.
  - jrochkind1 5y ago
    I mean, the links are going to be a template either way, so you can change the identifier part to change the item; it's just a question of whether the identifier portion is the internal rdbms PK, or something else. It's considered undesirable because it's basically an "implementation detail", it's good to let the internal PK change without having to change the public-facing URLs, or sometimes vice versa, when there are business reasons to do so. And then there are the at least possible security implications (mainly "enumeration attacks") and/or "business intelligence to your competitors" implications too. But it's true that plenty of sites seem to expose the internal PK too, it's not a complete consensus. Just kind of a principle to separate internal implementation from public interface. Here's a short 2008 HN discussion on it, in which people have both opinions: https://news.ycombinator.com/item?id=19876901 https://news.ycombinator.com/item?id=19876901 Here's a more recent and lengthy treatment: https://czep.net/21/obfuscate.html https://czep.net/21/obfuscate.html

yardstick 5y ago

I prefer UUIDs as well but one other benefit of an integer type key is in the index. UUIDs, especially v4, make for bloated indexes. If there’s a very specific performance reason I’ll use int, otherwise uuid.

fdr 5y ago
This is what I do as well. Most schema structure is on tables that are not very large, and it's nice especially when dealing with multiple environments to prevent errors, or to produce schemas with some polymorphism or joins between several tables without a chain of id-resolving joins in-between. There are UUID variants that can work well with indices, which shrinks the case for big-integers yet further, to micro-optimizing cases that are situational.
tehlike 5y ago
You can use sequential guids. That's what I do. Works well for not bloating indices.
AlfeG 5y ago
One of hidden pros is that UUID has no gaps visible to product owner or manager. More than once in my life I have try convince people, that the gap in sequence caused by transaction rollback is OK. Several times we were «fixing data» in db manually

spiffytech 5y ago

> They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they might be created) UUIDv7 (currently a draft spec[0]) are IDs that can be sorted in the chronological order they were created In the meantime, ulid[1] and ksuid[2] are popular time-sortable ID schemes, both previously discussed on HN[3] [0] https://datatracker.ietf.org/doc/html/draft-peabody-dispatch-new-uuid-format#section-4.4 https://datatracker.ietf.org/doc/html/draft-peabody-dispatch... [1] https://github.com/ulid/spec https://github.com/ulid/spec [2] https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid [3] ulid discussion: https://news.ycombinator.com/item?id=18768909 https://news.ycombinator.com/item?id=18768909 UUIDv7 discusison: https://news.ycombinator.com/item?id=28088213 https://news.ycombinator.com/item?id=28088213

gavinray 5y ago
Wow, neat. TIL
stjohnswarts 5y ago
Hmmm that's really useful :) . thanks!
codetrotter 5y ago
Yup this. Ulid is great, and implemented in many languages. For example there’s a crate named Ulid that you can use that gives you ulid’s in Rust.
- djbusby 5y ago
  Came here to also hype ULID. It's just like INT, but 128bit, timestamps, every lang, and moving data around is trivial because no ID ever collide with anything else ever. Makes finding crap in my logs easier. (But UUID has that feature too) - it's the sorting that does it for me.
- ChristianGeek 5y ago
  Is there a PostgreSQL extension for ulid?

jrockway 5y ago

Something I always liked but have never done -- start auto-increment IDs at some number other than 1. I worked on a system where account IDs started with 1000000, invoice IDs started with 2000000, etc. That way, if you saw a random number laying around like "1001234", you knew it was an account ID. "2000123", an invoice! I don't remember how often it helped things, but it was more than 0 times, and it always "felt" good. I never loved the implicit upper bound, but it was also never a problem. (And remember, the values are in different tables, so the only disadvantage is that your millionth user has ID 2000000. To the trained eye they look like an invoice; but there is no way for the system itself to treat that as an invoice, so it's only confusing to humans. If you use auto-increment keys that start at 1, you have the same problem. Account 1 and Invoice 1 are obviously different things.)

Twisol 5y ago
> but there is no way for the system itself to treat that as an invoice, so it's only confusing to humans. I agree in principle, but then you have Windows skipping version 9 because of all of the `version.hasPrefix("9")` out in the world that were trying to cleverly handle both 95 and 98 at once. If a feature of data is exploitable, there's a strong chance it will be exploited.
9dev 5y ago
I use this for internal items, as in: real customer IDs start at 10,000, so we have 9,999 possible users/invoices/whatever for our own usage in the database. That makes it easy to filter demo accounts of your sales employees, or production test accounts, in queries: just add an offset. Re: the upper bound: if you reach a million customers, you have lots of other nice problems, like how to spend all the money they earn you :-)
- gavinray 5y ago
  This is clever. You could use "is_test_account" or whatnot, but that adds an unnecessary column (for ~99% of records) I like this -- will keep it in mind!
avar 5y ago

jrochkind1 5y ago

The person you're replying to didn't even mention those advantages though! The ones they did mention are more significant to me. Of your disadvantages... if I wanted to know when rows were created, I'd just add a created timestamp column. But "easier to read" is for real -- it's easy when debugging something to have notes referencing rows 12455 and 98923823 or in some cases even keep them in your head. But UUIDs are right out. And you definitely don't want to put UUIDs in a user-facing URL -- which is actually how you get around "avoids people being able to just iterate through records", really whether you have UUIDs or numeric pks I think putting internal PK's in URLs or anything else publicly exposed is a bad idea, just keep your pks purely internal. Once you commit to that, the comparison between UUIDs vs numeric as pks changes again.

Semaphor 5y ago
> I think putting internal PK's in URLs or anything else publicly exposed is a bad idea Why? Both as a user and as a dev I love that I can just change the PK to get to a specific post, item, whatever instead of changing the whole link.
- jrochkind1 5y ago
  I mean, the links are going to be a template either way, so you can change the identifier part to change the item; it's just a question of whether the identifier portion is the internal rdbms PK, or something else. It's considered undesirable because it's basically an "implementation detail", it's good to let the internal PK change without having to change the public-facing URLs, or sometimes vice versa, when there are business reasons to do so. And then there are the at least possible security implications (mainly "enumeration attacks") and/or "business intelligence to your competitors" implications too. But it's true that plenty of sites seem to expose the internal PK too, it's not a complete consensus. Just kind of a principle to separate internal implementation from public interface. Here's a short 2008 HN discussion on it, in which people have both opinions: https://news.ycombinator.com/item?id=19876901 https://news.ycombinator.com/item?id=19876901 Here's a more recent and lengthy treatment: https://czep.net/21/obfuscate.html https://czep.net/21/obfuscate.html