7 ms·

While this is a good overview of the options for primary key generation, there's no silver bullet here. Most projects that are using SQL should just use the gol

by ammmir 4y ago

While this is a good overview of the options for primary key generation, there's no silver bullet here. Most projects that are using SQL should just use the gold standard: an auto-incrementing integer for an internal primary key. And then decouple the public-facing primary key from it into a separate column, whether it be ULID, UUID, or a random-project-slug-123. Also, during debugging, it's a lot nicer to look at short primary keys than to have UUIDs flooding the screen.

cameronh90 4y ago
Why would you use an integer primary key and a public facing UUID? That seems like it's the worst of both worlds: ugly externally visible identifiers, record bloat, a database that you can't easily merge in the event of backups or DR, and having to roundtrip to the DB before you know the ID of a record. I personally stick to UUIDs in pretty much all cases, with the exception of where there are justified and benchmarked performance reasons not to.
- boloust 4y ago
  You're right to point at performance as the main motivator for this setup. The primary key is included in all indexes, including non-clustered indexes, so in some cases there can be quite a large difference between UUID and integer PKs in terms of index size. UUID PKs are also more susceptible to fragmentation.
  - tpetry 4y ago
    Thats not how PostgreSQL works. The primary key is only included in every secondary key for MySQL. PostgreSQL secondary indexes directly point at the page and rowid.
    - dspillett 4y ago
      It works this way in SQL Server too, and some other DBs, if you have a clustered index (usually recommended). The clustering key is included in all non-clustered indexes on the table. Not that this doesn't mean NCIs inherit any extra fragmentation potential from the clustering key, as it is effectively INCLUDed and not considered by of the key of the supporting index. Postgres tables are more like what SQL Server calls a heap table (one without a clustering key). Some of the issues that make clustered tables the standard recommendation in SQL Server are very similar to those that make VACUUM a requirement in postgres. IIRC postgres tables are more efficient than SQL Server's heap tables in most cases because they are the only option so are actively optimised for, where in SQL Server head tables are generally (in all but the few circumstances where they are more efficient) considered a second class type.
- dspillett 4y ago
  > That seems like it's the worst of both worlds: ugly externally visible identifiers Sometimes the external identifier is needed due to interaction with external systems, so it isn't really your choice as the DB/app designer. >, record bloat, Depending on the DB, the opposite can be true. In SQL Server if the integer key is the clustering key, which is usually the case for a table's primary key then you may get a smaller DB then using a UUID alone because the clustering key is included in all non-clustered indexes on the table (so the 4 or 8 bytes saved by just having the UUID and not also an integer ID is quickly lost to a larger amount of bloat). > and having to roundtrip to the DB before you know the ID of a record. For internal use you should just use the integer ID for the most part, the UUID or similar being for external references. Unless of course the UUID is for security purposes (making enumerating records impractical for instance) in which case you'll be using the UUID in your own application directly. In these case you shouldn't look up the ID, just query by the UUID. Usually you are wanting to access the base record anyway so that isn't an extra JOIN, and if you are just referring to child tables (so wouldn't need to reference the main entity if not looking up the internal ID) the extra JOIN is usually insignificant, pulling back a single row, compared to the latency hit of a full round-trip to lookup the ID separately. > I stick to UUIDs … with the exception of where there are justified … performance reasons not to. A perfectly valid approach. Though it does vary by DB, and what looks like a smell to someone with most posgres experience is often the more efficient method elsewhere, so you need to take that into account when looking at other projects (or working on your own if support for varied DBs in the backend is desired or a requirement).
  - cameronh90 4y ago
    What I mean is that in most cases, BOTH autoinc int and UUID is wasteful. For example if you have a situation where you really need the high performance of an integer ID like in your SQL Server example, why introduce a UUID into the equation at all? If you are in the unusual situation of needing such extreme performance that you're worrying about 4 vs 8 bytes on the PK, but also need to obfuscate the public facing ID, _potentially_ int+UUID makes sense. But in my experience that is that a pretty rare situation and there are other things you can do such as using a shorter randomly generated ID such as snowflake, random integers with collision detection (depending on write patterns) or encrypting your ID at the application layer (caveats emptor but unless you're relying on IDs for absolute security they shouldn't be a problem). However, defaulting to autoinc int as FK and publicly visible UUID for "user friendly" ID seems like an odd thing to do. It seems like one of the least useful ID schemes. > For internal use you should just use the integer ID for the most part, the UUID or similar being for external references. I disagree. UUIDs are very useful even as internal identifiers in any area where performance isn't your top concern. UUIDs are better than autoincs in almost every way except being slightly less performant. You could argue for a strictly internal ID that the security and uniqueness advantages don't matter very much, but I think it's better to default to the safest option in case your ID inadvertently becomes public at some point, or indeed in case you want to one day make public a previously internal-only record. But even if you know, for sure, that your ID will only ever be internal, they still make it possible to merge datasets easily, they're easy to correlate across multiple systems, are easier to find in log files, and can be generated at the application layer, which can make a big difference in transaction time in some cases. The only reasons not to use UUIDs are that they are ugly and marginally slower, which for the vast majority of entities doesn't particularly matter. If you're at the point where the rest of your code is so optimal that the UUID is causing you problems, or your records are so tiny that the UUID is a big overhead, then that is an exception that I am very happy to make, but it rarely applies. Too often people assume that the overhead of a UUID is worse than it is because of how long they look, but then when you actually benchmark it, it's swings and roundabouts. Besides, in most cases where I've defaulted to integers, I've come to regret that decision a few years down the line, where some complex system migration or new business requirement would have ended up much easier if we'd just bit the UUID bullet earlier on.
- AtlasBarfed 4y ago
  IMO use a UUID + a "type code" so something like: xxxx-xxxx-xxxxxxxx-xxxx-CUST xxxx-xxxx-xxxxxxxx-xxxx-ADDR It makes seas of UUIDs much easier to reason about Depends on your tolerance of wasted disk space for binary vs char, but you can shorten the binary to base64 or use a record as a primary key if you want. Other advantages of UUIDs: - they can be generated by clients or by the server safely - they can be concurrently and distributedly generated without a central sequence blocking/locking ID generation - they are distinct/unique across system migrations and mergers of systems / data / table - time UUIDs can encode some info about when generated which can help with forensics / debugging in production - no database-specific behavior for sequence generation, no extra database object for the sequence - probably helps with data warehouse / data oceans for keeping data distinct and tracing back to source system - similarly to that, for integrating systems, also makes the ids unique across system boundaries - they are a bit more secure as stated elsewhere
  - cameronh90 4y ago
    For interacting with humans, I rarely expose a UUID directly. I'm not particularly bothered about ugly URLs but I don't work in an industry where SEO is relevant. As you suggest, I sometimes incorporate type information into the ID to convey a bit more context, often in the form of a URL (org.com/customer/xxxxx). If you do need to put a UUID in the UI, depending on the constraints of the application, it might make sense to just display the first group of characters from the UUID and separately handle the very rare collisions you may encounter, similar to git and its short SHAs. For any situation where there will be transcription or copying IDs between systems manually, I will typically add another group that incorporates some metadata about where the ID came from (similar to the type code you mention) and a check digit, but obviously I try to avoid any situation that involves transcribing a UUID.
PestoDiRucola 4y ago
One thing I don’t see being mentioned in this thread (I only skimmed the article, so I don’t know if it’s mentioned there) is that you can run out of numbers when using serial, as they have a max, so if you are planning to have a table which will have over 2147483647 rows, then you might look into other types to use as a unique identifier.
- aeyes 4y ago
  Just use biginteger everywhere, still more efficient than even UUIDv4.
- tylergetsay 4y ago
  For anyone wondering, this is around 60 records per second for a year before you max out
- arp242 4y ago
  You can use bigserial instead of serial, which goes up to 9223372036854775807.
  - dspillett 4y ago
    If your DB supports unsigned integers, or starting sequences from -2,147,483,648, you can double the address range. But if you are at all worried that you'll get within a couple of orders of magnitude of MAXINT32 in the lifetime of your application then you should immediately jump to 64-bit values. Doubling is often just noise and the cost of refactoring if you approach MAXINT32 much faster than expected is more or a problem than the extra storage cost of bigger keys.
    - arp242 4y ago
      PostgreSQL doesn't have unsigned integers.
      - dspillett 4y ago
        Nor does MS SQL Server, though you can start IDENTITY from a maximally negative value. But any time I'd consider that I'd just jump up to an larger integer type instead.
saiya-jin 4y ago
I hate UUIDs with passion currently - not postgres but recently spent so much extra time on relatively small table (35 mil records in few columns) and doing some queries and updating subset of it. UUIDs there is stored in Oracle 'raw' datatype which to me is the worst combination possible, basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and readability, atrocious performance on stored procedures. Absolutely worst DB design I've seen in past 20 years, and we talk about expensive core anonymization service of top big banking package.
- marcosdumay 4y ago
  AFAIK, the Oracle way is to store them as Number. The type is made just large enough for them. But if you want to talk about Oracle's usability, there are much larger fish to fry. I wouldn't recommend anybody to use that database.
  - saiya-jin 4y ago
    more often than not one doesn't have any choice in DB, especially when it comes bundled in product like in my case
- dspillett 4y ago
  > basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and readability, atrocious performance on stored procedures But this give a significant performance benefit for storage (storing in the display format gives 4 bits per byte, or less if you include decorations like the '-' characters, rather than 8) and more importantly when joining (it doesn't need to convert for this, so on a 64-bit architecture each comparison is a pair of 64-bit compares in the CPU) rather than a more complex string comparison. If you are converting between string and binary representations more often than on input to your stored procedures or for output, then something is very wrong (the query planner are likely not able to use indexes that it could too, so scanning instead of seeking).
hot_gril 4y ago
When I first started using Postgres, I sat and thought forever about which PKs to use, and looking back, I was way overthinking it. Combination of unique fields, UUIDs, hashed data... Now I always use bigserial without thinking about it. When my DBA hat is on, it's none of my concern how the user-facing IDs will look; I just know it's gonna be a string of some kind. What's the common use case for the others? I can imagine for weird performance reasons you might want to pick special PKs, but that implies you're exposing them to clients, which you almost never want. The only more reasonable thing I can think of is a UUIDv4 for a special sharded database.
okl 4y ago
Two benefits of UUIDs: 1. You can create a series of related rows without hitting the DB to obtain the next integer. 2. During development, if you mix up ids you will get an error/empty result. With integer keys you might get a row you didn't intend to get, hiding the error.

by ammmir 4y ago

cameronh90 4y ago

Why would you use an integer primary key and a public facing UUID? That seems like it's the worst of both worlds: ugly externally visible identifiers, record bloat, a database that you can't easily merge in the event of backups or DR, and having to roundtrip to the DB before you know the ID of a record. I personally stick to UUIDs in pretty much all cases, with the exception of where there are justified and benchmarked performance reasons not to.

boloust 4y ago
You're right to point at performance as the main motivator for this setup. The primary key is included in all indexes, including non-clustered indexes, so in some cases there can be quite a large difference between UUID and integer PKs in terms of index size. UUID PKs are also more susceptible to fragmentation.
- tpetry 4y ago
  Thats not how PostgreSQL works. The primary key is only included in every secondary key for MySQL. PostgreSQL secondary indexes directly point at the page and rowid.
  - dspillett 4y ago
    It works this way in SQL Server too, and some other DBs, if you have a clustered index (usually recommended). The clustering key is included in all non-clustered indexes on the table. Not that this doesn't mean NCIs inherit any extra fragmentation potential from the clustering key, as it is effectively INCLUDed and not considered by of the key of the supporting index. Postgres tables are more like what SQL Server calls a heap table (one without a clustering key). Some of the issues that make clustered tables the standard recommendation in SQL Server are very similar to those that make VACUUM a requirement in postgres. IIRC postgres tables are more efficient than SQL Server's heap tables in most cases because they are the only option so are actively optimised for, where in SQL Server head tables are generally (in all but the few circumstances where they are more efficient) considered a second class type.
dspillett 4y ago
> That seems like it's the worst of both worlds: ugly externally visible identifiers Sometimes the external identifier is needed due to interaction with external systems, so it isn't really your choice as the DB/app designer. >, record bloat, Depending on the DB, the opposite can be true. In SQL Server if the integer key is the clustering key, which is usually the case for a table's primary key then you may get a smaller DB then using a UUID alone because the clustering key is included in all non-clustered indexes on the table (so the 4 or 8 bytes saved by just having the UUID and not also an integer ID is quickly lost to a larger amount of bloat). > and having to roundtrip to the DB before you know the ID of a record. For internal use you should just use the integer ID for the most part, the UUID or similar being for external references. Unless of course the UUID is for security purposes (making enumerating records impractical for instance) in which case you'll be using the UUID in your own application directly. In these case you shouldn't look up the ID, just query by the UUID. Usually you are wanting to access the base record anyway so that isn't an extra JOIN, and if you are just referring to child tables (so wouldn't need to reference the main entity if not looking up the internal ID) the extra JOIN is usually insignificant, pulling back a single row, compared to the latency hit of a full round-trip to lookup the ID separately. > I stick to UUIDs … with the exception of where there are justified … performance reasons not to. A perfectly valid approach. Though it does vary by DB, and what looks like a smell to someone with most posgres experience is often the more efficient method elsewhere, so you need to take that into account when looking at other projects (or working on your own if support for varied DBs in the backend is desired or a requirement).
- cameronh90 4y ago
  What I mean is that in most cases, BOTH autoinc int and UUID is wasteful. For example if you have a situation where you really need the high performance of an integer ID like in your SQL Server example, why introduce a UUID into the equation at all? If you are in the unusual situation of needing such extreme performance that you're worrying about 4 vs 8 bytes on the PK, but also need to obfuscate the public facing ID, _potentially_ int+UUID makes sense. But in my experience that is that a pretty rare situation and there are other things you can do such as using a shorter randomly generated ID such as snowflake, random integers with collision detection (depending on write patterns) or encrypting your ID at the application layer (caveats emptor but unless you're relying on IDs for absolute security they shouldn't be a problem). However, defaulting to autoinc int as FK and publicly visible UUID for "user friendly" ID seems like an odd thing to do. It seems like one of the least useful ID schemes. > For internal use you should just use the integer ID for the most part, the UUID or similar being for external references. I disagree. UUIDs are very useful even as internal identifiers in any area where performance isn't your top concern. UUIDs are better than autoincs in almost every way except being slightly less performant. You could argue for a strictly internal ID that the security and uniqueness advantages don't matter very much, but I think it's better to default to the safest option in case your ID inadvertently becomes public at some point, or indeed in case you want to one day make public a previously internal-only record. But even if you know, for sure, that your ID will only ever be internal, they still make it possible to merge datasets easily, they're easy to correlate across multiple systems, are easier to find in log files, and can be generated at the application layer, which can make a big difference in transaction time in some cases. The only reasons not to use UUIDs are that they are ugly and marginally slower, which for the vast majority of entities doesn't particularly matter. If you're at the point where the rest of your code is so optimal that the UUID is causing you problems, or your records are so tiny that the UUID is a big overhead, then that is an exception that I am very happy to make, but it rarely applies. Too often people assume that the overhead of a UUID is worse than it is because of how long they look, but then when you actually benchmark it, it's swings and roundabouts. Besides, in most cases where I've defaulted to integers, I've come to regret that decision a few years down the line, where some complex system migration or new business requirement would have ended up much easier if we'd just bit the UUID bullet earlier on.
AtlasBarfed 4y ago
IMO use a UUID + a "type code" so something like: xxxx-xxxx-xxxxxxxx-xxxx-CUST xxxx-xxxx-xxxxxxxx-xxxx-ADDR It makes seas of UUIDs much easier to reason about Depends on your tolerance of wasted disk space for binary vs char, but you can shorten the binary to base64 or use a record as a primary key if you want. Other advantages of UUIDs: - they can be generated by clients or by the server safely - they can be concurrently and distributedly generated without a central sequence blocking/locking ID generation - they are distinct/unique across system migrations and mergers of systems / data / table - time UUIDs can encode some info about when generated which can help with forensics / debugging in production - no database-specific behavior for sequence generation, no extra database object for the sequence - probably helps with data warehouse / data oceans for keeping data distinct and tracing back to source system - similarly to that, for integrating systems, also makes the ids unique across system boundaries - they are a bit more secure as stated elsewhere
- cameronh90 4y ago
  For interacting with humans, I rarely expose a UUID directly. I'm not particularly bothered about ugly URLs but I don't work in an industry where SEO is relevant. As you suggest, I sometimes incorporate type information into the ID to convey a bit more context, often in the form of a URL (org.com/customer/xxxxx). If you do need to put a UUID in the UI, depending on the constraints of the application, it might make sense to just display the first group of characters from the UUID and separately handle the very rare collisions you may encounter, similar to git and its short SHAs. For any situation where there will be transcription or copying IDs between systems manually, I will typically add another group that incorporates some metadata about where the ID came from (similar to the type code you mention) and a check digit, but obviously I try to avoid any situation that involves transcribing a UUID.

PestoDiRucola 4y ago

One thing I don’t see being mentioned in this thread (I only skimmed the article, so I don’t know if it’s mentioned there) is that you can run out of numbers when using serial, as they have a max, so if you are planning to have a table which will have over 2147483647 rows, then you might look into other types to use as a unique identifier.

aeyes 4y ago
Just use biginteger everywhere, still more efficient than even UUIDv4.
tylergetsay 4y ago
For anyone wondering, this is around 60 records per second for a year before you max out
arp242 4y ago
You can use bigserial instead of serial, which goes up to 9223372036854775807.
- dspillett 4y ago
  If your DB supports unsigned integers, or starting sequences from -2,147,483,648, you can double the address range. But if you are at all worried that you'll get within a couple of orders of magnitude of MAXINT32 in the lifetime of your application then you should immediately jump to 64-bit values. Doubling is often just noise and the cost of refactoring if you approach MAXINT32 much faster than expected is more or a problem than the extra storage cost of bigger keys.
  - arp242 4y ago
    PostgreSQL doesn't have unsigned integers.
    - dspillett 4y ago
      Nor does MS SQL Server, though you can start IDENTITY from a maximally negative value. But any time I'd consider that I'd just jump up to an larger integer type instead.

saiya-jin 4y ago

I hate UUIDs with passion currently - not postgres but recently spent so much extra time on relatively small table (35 mil records in few columns) and doing some queries and updating subset of it. UUIDs there is stored in Oracle 'raw' datatype which to me is the worst combination possible, basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and readability, atrocious performance on stored procedures. Absolutely worst DB design I've seen in past 20 years, and we talk about expensive core anonymization service of top big banking package.

marcosdumay 4y ago
AFAIK, the Oracle way is to store them as Number. The type is made just large enough for them. But if you want to talk about Oracle's usability, there are much larger fish to fry. I wouldn't recommend anybody to use that database.
- saiya-jin 4y ago
  more often than not one doesn't have any choice in DB, especially when it comes bundled in product like in my case
dspillett 4y ago
> basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and readability, atrocious performance on stored procedures But this give a significant performance benefit for storage (storing in the display format gives 4 bits per byte, or less if you include decorations like the '-' characters, rather than 8) and more importantly when joining (it doesn't need to convert for this, so on a 64-bit architecture each comparison is a pair of 64-bit compares in the CPU) rather than a more complex string comparison. If you are converting between string and binary representations more often than on input to your stored procedures or for output, then something is very wrong (the query planner are likely not able to use indexes that it could too, so scanning instead of seeking).

hot_gril 4y ago

When I first started using Postgres, I sat and thought forever about which PKs to use, and looking back, I was way overthinking it. Combination of unique fields, UUIDs, hashed data... Now I always use bigserial without thinking about it. When my DBA hat is on, it's none of my concern how the user-facing IDs will look; I just know it's gonna be a string of some kind. What's the common use case for the others? I can imagine for weird performance reasons you might want to pick special PKs, but that implies you're exposing them to clients, which you almost never want. The only more reasonable thing I can think of is a UUIDv4 for a special sharded database.

okl 4y ago

Two benefits of UUIDs: 1. You can create a series of related rows without hitting the DB to obtain the next integer. 2. During development, if you mix up ids you will get an error/empty result. With integer keys you might get a row you didn't intend to get, hiding the error.