9 ms·

I'll stop short of giving a recommendation or using the word "should", but ill give encouragement to consider using uuid's for keys. I have used them in several

I'll stop short of giving a recommendation or using the word "should", but ill give encouragement to consider using uuid's for keys. I have used them in several systems and have never had any issues with them, and they solve so many issues. The ability to generate a key on the client or on the server or in the database is great for one. And the fact that keys are unique not only in a table but in the system (or many systems) is a huge advantage.

mjgp2 5y ago
Uuidv4 have worse performance when inserting into the btree for the primary key.
- orf 5y ago
  Why would you use a btree for them? Wouldn’t a hash index be ideal?
  - masklinn 5y ago
    > Why would you use a btree for them? 1. because PRIMARY KEY is its own constraint, and the underlying index is not under you control 2. because PRIMARY KEY further restricts UNIQUE, and as of postgres 14 "only B-tree indexes can be declared unique"
  - tomnipotent 5y ago
    Some databases don't give you a choice, and your table data is actually stored in a clustered index sorted by primary key. This means random inserts when using a UUID (without embedded time), and more page splitting.
- williamdclt 5y ago
  That is true, but that has never been a bottleneck for me. We have a table with multiple hundreds of millions of rows, 20ish UUIDs indexes (yes it did get out of hand) and insert time isn't an issue. Ofc, your app performance requirements might vary, and it is objectively true that UUIDs aren't ideal for btree indexes.
  - akra 5y ago
    Then you are lucky. I once found an insert performance drop of sometimes 5x once a table reaches 500,000 elements or so with BTree's and Uuids in particular. Problem is: UUID's often scale well on the app side, especially with multiple writers so the app wants to use them. Unless you are using a time sorted UUID, and you only do inserts into the table (never updates) avoid any feature that creates a BTree on those fields IMO. Given MVCC architecture of Postgres time sorted UUID's are often not enough if you do a lot of updates as these are really just inserts which again create randomness in the index. I've been in a project where to avoid a refactor (and given Postgres usage was convenient) they just decided to remove constraints and anything that creates a B-Tree index implicitly or explicitly. It makes me wish Hash indexes could be used to create constraints. They often use less memory these days in my previous testing under new versions of Postgres, and scale a lot better despite less engineering effort in them. In other databases where updates happen in-place so as not to change order of rows (not Postgres MVCC) a BRIN like index on a time ordered UUID would be often fantastic for memory usage. ZHeap seems to have died. Sadly this is something people should be aware of in advance. Otherwise it will probably bite you later when you have large write volumes, and therefore are most unable to enact changes to the DB when performance drastically goes down (e.g. large customer traffic). This is amplified because writes don't scale in Postgres/most SQL databases.
- tehlike 5y ago
  Use sequential guids.
polskibus 5y ago
Be wary when using guids for PK in clustered indices, in databases that support them, see for example https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega/11938495 https://stackoverflow.com/questions/11938044/what-are-the-be... . Sadly, Postgresql doesn't have them maintained on-the-fly like Ms SQL does so it is less used feature.
gavinray 5y ago
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
- 5y ago
evandwight 5y ago
Do you write logic to handle collisions?
- spullara 5y ago
  "A collision is possible but the total number of unique keys generated is so large that the possibility of a collision is almost zero. As per Wikipedia, the number of UUIDs generated to have atleast 1 collision is 2.71 quintillion. This is equivalent to generating around 1 billion UUIDs per second for about 85 years."
  - fwip 5y ago
    For clarification, that's the number of UUIDs at which you have a 50% chance of at least one collision. Wikipedia also adds: "the probability to find a duplicate within 103 trillion version-4 UUIDs is one in a billion."
- williamdclt 5y ago
  part of the point of UUIDs is that you don't have to. Collision is unlikely enough that your time would be more wisely spent worrying about the sun exploding unless you generate an absolutely absurd amount of data.
- Ginden 5y ago
  Unless you are writing nuclear warhead management system, writing UUID collision handling is waste of time. Client can retry on top level if request failed.
  - afiori 5y ago
    expecting collision to happen is probably a waste of time, but it still sounds like a good idea to bound how much damage a collision might cause.
    - AlfeG 5y ago
      My guess is that Iin 99% collision could occur during insert. Most of the time db will just not allow to insert duplicated record.
lr4444lr 5y ago
Technically there are many advantages, but operationally, I find it extremely useful in systems where an int PK is auto-generated to use it as a pretty good proxy for a relative created time with relation to other records, with built-in indexing that avoids having to index on an actual created datetime column for a lot of less precise lookup purposes. The backend frameworks I use around data access make the security benefits of UUIDs pretty moot.
- Robin_Message 5y ago
  As someone below suggests, ULID is a good alternative which has a temporal aspect in the high bits so it is still a proxy for relative creation time.
tomnipotent 5y ago
I generally use both - a serial for internal RDBMS storage to coerce writes to be in order, and a secondary GUID that is app-specific and generated. The app will never care about the serial, and the RDBMS doesn't care about the GUID other than secondary index look-ups (since they're rarely used for range scans).
christophilus 5y ago
A friend of mine recently recommended CUIDs, which I'd never heard of before. Any thoughts on those? Neither usecuid.org nor https://www.npmjs.com/package/cuid https://www.npmjs.com/package/cuid give me much confidence, though (the former is not https, and the latter has not been updated in 2 years... which might mean it's done, or might mean it's abandoned).
barrkel 5y ago
Random keys have the positive aspect of not creating any hotspots, and they have the negative aspect of not creating any hotspots. So if you have concurrent updates which are correlated with row insertion time, random keys can be a win. On the other hand, if your lookups are correlated with row insertion time, then the relevant key index pages are less likely to be hot in memory, and depending on how large the table is, you may have thrashing of index pages (this problem would be worse with MySQL, where rows are stored in the PK index). (UUID doesn't need to mean random any more though as pointed out below. My commentary is specific to random keys and is actually scar tissue from using MD5 as a lookup into multi-billion row tables.)
hackbinary 5y ago
>> I'll stop short of giving a recommendation or using the word "should" ... I beleive the word you are looking for is "ought". :)
nine_k 5y ago
Sometimes you want your keys to be smaller, so that more of your important indexes would fit in RAM, making searches and joins faster.
crooked-v 5y ago
One huge factor for me: assuming you stick to v4, you can use UUIDs directly as identifiers in public contexts without leaking any business-related info and without having predictable keys.
hn_throwaway_99 5y ago
What will really be nice is when UUID V7 has wide support, because in many ways it is the best of all possible worlds: 1. The UUIDs are generated in increasing order, so none of the b-tree issues others have mentioned with fully random UUIDs. 2. They're true UUIDs, so migrating between DBs is easy, ensuring uniqueness across DBs is easy, etc. 3. They also have the benefit of having a significant amount of randomness, so if you have a bug that doesn't do an appropriate access check somewhere they are more resistant to someone trying to guess the ID from a previous one.

mjgp2 5y ago

Uuidv4 have worse performance when inserting into the btree for the primary key.

orf 5y ago
Why would you use a btree for them? Wouldn’t a hash index be ideal?
- masklinn 5y ago
  > Why would you use a btree for them? 1. because PRIMARY KEY is its own constraint, and the underlying index is not under you control 2. because PRIMARY KEY further restricts UNIQUE, and as of postgres 14 "only B-tree indexes can be declared unique"
- tomnipotent 5y ago
  Some databases don't give you a choice, and your table data is actually stored in a clustered index sorted by primary key. This means random inserts when using a UUID (without embedded time), and more page splitting.
williamdclt 5y ago
That is true, but that has never been a bottleneck for me. We have a table with multiple hundreds of millions of rows, 20ish UUIDs indexes (yes it did get out of hand) and insert time isn't an issue. Ofc, your app performance requirements might vary, and it is objectively true that UUIDs aren't ideal for btree indexes.
- akra 5y ago
  Then you are lucky. I once found an insert performance drop of sometimes 5x once a table reaches 500,000 elements or so with BTree's and Uuids in particular. Problem is: UUID's often scale well on the app side, especially with multiple writers so the app wants to use them. Unless you are using a time sorted UUID, and you only do inserts into the table (never updates) avoid any feature that creates a BTree on those fields IMO. Given MVCC architecture of Postgres time sorted UUID's are often not enough if you do a lot of updates as these are really just inserts which again create randomness in the index. I've been in a project where to avoid a refactor (and given Postgres usage was convenient) they just decided to remove constraints and anything that creates a B-Tree index implicitly or explicitly. It makes me wish Hash indexes could be used to create constraints. They often use less memory these days in my previous testing under new versions of Postgres, and scale a lot better despite less engineering effort in them. In other databases where updates happen in-place so as not to change order of rows (not Postgres MVCC) a BRIN like index on a time ordered UUID would be often fantastic for memory usage. ZHeap seems to have died. Sadly this is something people should be aware of in advance. Otherwise it will probably bite you later when you have large write volumes, and therefore are most unable to enact changes to the DB when performance drastically goes down (e.g. large customer traffic). This is amplified because writes don't scale in Postgres/most SQL databases.
tehlike 5y ago
Use sequential guids.

polskibus 5y ago

Be wary when using guids for PK in clustered indices, in databases that support them, see for example https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega/11938495 https://stackoverflow.com/questions/11938044/what-are-the-be... . Sadly, Postgresql doesn't have them maintained on-the-fly like Ms SQL does so it is less used feature.

gavinray 5y ago

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
5y ago

evandwight 5y ago

Do you write logic to handle collisions?

spullara 5y ago
"A collision is possible but the total number of unique keys generated is so large that the possibility of a collision is almost zero. As per Wikipedia, the number of UUIDs generated to have atleast 1 collision is 2.71 quintillion. This is equivalent to generating around 1 billion UUIDs per second for about 85 years."
- fwip 5y ago
  For clarification, that's the number of UUIDs at which you have a 50% chance of at least one collision. Wikipedia also adds: "the probability to find a duplicate within 103 trillion version-4 UUIDs is one in a billion."
williamdclt 5y ago
part of the point of UUIDs is that you don't have to. Collision is unlikely enough that your time would be more wisely spent worrying about the sun exploding unless you generate an absolutely absurd amount of data.
Ginden 5y ago
Unless you are writing nuclear warhead management system, writing UUID collision handling is waste of time. Client can retry on top level if request failed.
- afiori 5y ago
  expecting collision to happen is probably a waste of time, but it still sounds like a good idea to bound how much damage a collision might cause.
  - AlfeG 5y ago
    My guess is that Iin 99% collision could occur during insert. Most of the time db will just not allow to insert duplicated record.

lr4444lr 5y ago

Technically there are many advantages, but operationally, I find it extremely useful in systems where an int PK is auto-generated to use it as a pretty good proxy for a relative created time with relation to other records, with built-in indexing that avoids having to index on an actual created datetime column for a lot of less precise lookup purposes. The backend frameworks I use around data access make the security benefits of UUIDs pretty moot.

Robin_Message 5y ago
As someone below suggests, ULID is a good alternative which has a temporal aspect in the high bits so it is still a proxy for relative creation time.

tomnipotent 5y ago

I generally use both - a serial for internal RDBMS storage to coerce writes to be in order, and a secondary GUID that is app-specific and generated. The app will never care about the serial, and the RDBMS doesn't care about the GUID other than secondary index look-ups (since they're rarely used for range scans).

christophilus 5y ago

A friend of mine recently recommended CUIDs, which I'd never heard of before. Any thoughts on those? Neither usecuid.org nor https://www.npmjs.com/package/cuid https://www.npmjs.com/package/cuid give me much confidence, though (the former is not https, and the latter has not been updated in 2 years... which might mean it's done, or might mean it's abandoned).

barrkel 5y ago

Random keys have the positive aspect of not creating any hotspots, and they have the negative aspect of not creating any hotspots. So if you have concurrent updates which are correlated with row insertion time, random keys can be a win. On the other hand, if your lookups are correlated with row insertion time, then the relevant key index pages are less likely to be hot in memory, and depending on how large the table is, you may have thrashing of index pages (this problem would be worse with MySQL, where rows are stored in the PK index). (UUID doesn't need to mean random any more though as pointed out below. My commentary is specific to random keys and is actually scar tissue from using MD5 as a lookup into multi-billion row tables.)

hackbinary 5y ago

>> I'll stop short of giving a recommendation or using the word "should" ... I beleive the word you are looking for is "ought". :)

nine_k 5y ago

Sometimes you want your keys to be smaller, so that more of your important indexes would fit in RAM, making searches and joins faster.

crooked-v 5y ago

One huge factor for me: assuming you stick to v4, you can use UUIDs directly as identifiers in public contexts without leaking any business-related info and without having predictable keys.

hn_throwaway_99 5y ago

What will really be nice is when UUID V7 has wide support, because in many ways it is the best of all possible worlds: 1. The UUIDs are generated in increasing order, so none of the b-tree issues others have mentioned with fully random UUIDs. 2. They're true UUIDs, so migrating between DBs is easy, ensuring uniqueness across DBs is easy, etc. 3. They also have the benefit of having a significant amount of randomness, so if you have a bug that doesn't do an appropriate access check somewhere they are more resistant to someone trying to guess the ID from a previous one.