8 ms·

What's the problem using UUID as a PK? I really like 128bit numbers for PK, more than 64bit-int or anything text. I'm generally putting a ULID or UUIDV7/8 in t

What's the problem using UUID as a PK? I really like 128bit numbers for PK, more than 64bit-int or anything text. I'm generally putting a ULID or UUIDV7/8 in there.

VWWHFSfQ 2y ago
128-Bit numbers are absolutely humongous and just by virtue of being a primary key means they get duplicated for every single relation. So if you have your user ID, for instance, as a 128bit uuid, any table and column that references the user ID is now also a 128-bit uuid. It's not a problem if you have very small amounts of data. But once your tables start to grow it will become misery to work with. In my opinion, the best pattern is to have just a regular bigserial for primary keys to use for internal database relations since postgres is extremely good at generating and optimizing PK sequences (You're taking away all that optimization just because you want an enormous random number to be the primary key). Then just have a uuid field for application-level identifiers and natural keys. You'll save yourself a whole lot of headache with this pattern.
- londons_explore 2y ago
  UUID's are for when an auto-incrementing ID can't be used, usually due to scale (if you will be inserting millions of records per second from millions of clients). However, postgres isn't suited to such scale (something like spanner IS). Therefore, you probably shouldn't be using UUID's with postgres. The other reason for UUID's is because the system is badly architected and , for example, the client gets to choose their own ID which are written into a DB. Such systems generally are best avoided.
  - bruce511 2y ago
    Also think about systems that are "beyond one database". Like data that lives in multiple places at the same time (on phone local storage, in database, consolidated and syncd either other databases etc.) In other words once you move beyond '1 sql database' they become important. (I'm not downvoting you, just disagreeing with you.)
    - djbusby 2y ago
      This is why I asked at the start of the thread. We *need* 128bit numbers to make the uniqueness work across loads of systems (like a distributed/federated type thing)
      - hot_gril 2y ago
        Maybe, but they don't need to be the PKs. And you only need some 128bit number at all if you want one generator not to be able to guess the others' numbers, like if clients are generating them.
        bruce511 2y ago
        Are you mixing up primary key with clustered key? Primary Key serves to uniquely identify the row. This is used to reference the tow in other tables. (So the Invoice table contains the Primary Key value for the customer.) The Clustered key describes the physical sort order on the disk. Clustering on your most common search improves performance for that search. Searching on other criteria becomes equally slow. So, it's really common to search through invoices by date. So a date key is ideal for a clustered index on that table. Looking up all invoices for a date range is faster than all invoices by, say, customer. Clustering the customer or employee table by date though isn't great, I don't want to often see my employees on take-on order. We teach Primary Keys at Uni, but in my era anyway they didn't put the same emphasis on selecting a correct clustered key. So I feel like people tend to assume the Primary Key is also the clustered key.
        hot_gril 2y ago
        I'm used to Postgres, which doesn't have clustered key. If this is MySQL and you're using a clustered key, pkey is still how you'd identify rows in foreign keys etc, so I think the same advice of bigserial pkey applies. We learned using natural pkeys in college, which in practice burned me enough that I'll never do it anymore. I just set some uniqueness constraints.
    - deathanatos 2y ago
- pdhborges 2y ago
  > But once your tables start to grow it will become misery to work with. I buy the data layout argument for databases with clustered tables without any smi-sequencial uuid support. But the storage argument looks vanishingly applicable to me: if someone needs to add a column to one of these tables it basically offsets a 4 byte optimization already.
  - bruce511 2y ago
    8 Byte (assuming 128 bit instead of 64 bit) but yeah. It's not quite as simple as saving 8 bytes per row though. It's 8 bytes for the UUID, plus 8 for at least the PK, plus 8 more for any other keys the UUID is in. Then you need to do that for any foreign-key-fields and keys those are in as well. However, unless your table has very few non-uuid columns, the rest of the table size will dwarf the extra n*8 bytes here. And if you are storing any blobs (like pictures, documents etc) then frankly all the uuids won't add up to anything. In summary, whether using uuids or not is right for you depends a Lot on your context. An Insert Only log table, with a billion rows, is a very different use case to say a list of employees or customers. Generally I'm very comfortable with uuids for tables of say less than 100m rows. Very high inserting though suggests tables bigger than that, and perhaps benefits from different strategies. When it comes to data stored in multiple places (think on-phone first, syncd to cloud, exported from multiple places to consolidated BI systems), uuids solve the Lot of problems. Context is everything.
- xyzzy_plugh 2y ago
  They're not "absolutely humongous" but merely two times as large as a 64-bit number. That's nothing. They're not even that bad for performance, assuming you have random writes regardless. UUIDs work incredibly well. Where they don't work well is when they're stored in textual encoding (base16 with dashes) which I've encountered far more than I'd like. They also don't work (random) for append-only insertions like events, for which ULID (or newer UUID variants) are more appropriate. But for primary keys? Totally fine. > extremely good at generating and optimizing PK sequences (You're taking away all that optimization just because you want an enormous random number to be the primary key). Please share the benchmarks to back this up. UUIDs optimize down perfectly fine. If you're trying to squeeze every last ounce of performance out of the database then certainly this is a yak you can shave. But most Postgres users have 1001 problems that are much bigger than UUIDs. If you've managed to avoid folks writing shitty queries and this is the bottleneck you're facing then I salute you. Otherwise, don't worry. UUIDs are fine.
  - sgarland 2y ago
    > but merely two times as large as a 64-bit number. That's nothing. It's literally 2x as large. This starts to matter quite a bit at the millions/billions of rows scale. > They're not even that bad for performance, assuming you have random writes regardless. > Please share the benchmarks to back this up. I've done [0] benchmarks, with [1] source to recreate it. This was for loading data, but it demonstrates quite nicely the problem of massive B+tree splits. An integer PK loads data ~2x as quickly as a UUIDv4 PK stored as `uuid` type, and ~3x as fast as one stored as `text` type. > But most Postgres users have 1001 problems that are much bigger than UUIDs. All of which become compounded by the use of non-k-sortable keys. > If you've managed to avoid folks writing shitty queries and this is the bottleneck you're facing then I salute you. Using UUIDs as a PK without having an extremely good reason to do so demonstrates a lack of understanding of how the underlying technology works. Worse, it demonstrates a lack of understanding of a basic data structure, something that devs should have near and dear to their heart. With Postgres specifically, the use of UUIDv4 (or ULID) PKs will also cause an enormous amount of WAL bloat [2], unless you happen to be rolling your own DB and are using ZFS for the file system, and you've disabled `full_page_writes` (which is the only safe way to do so). I have personally seen this take down prod, because the other nodes couldn't keep up with the changes being issued (fun fact, most AWS instances under `.4xlarge` in size have a baseline network speed that's much lower than rated). [0]: https://gist.github.com/stephanGarland/ee38c699a9bb999894d760a10c227bc4 https://gist.github.com/stephanGarland/ee38c699a9bb999894d76... [1]: https://gist.github.com/stephanGarland/fe0788cf2332d6e241ff3ac6d9440caf https://gist.github.com/stephanGarland/fe0788cf2332d6e241ff3... [2]: https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-page-writes/ https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-pa...
- moomoo11 2y ago
  Quick question. So you’re saying use bigserial for PK (instead of uuid) and have another column where I set a uuid in case of application level identifier. But wouldn’t that require a index on it if it’s going to be an identifier that’s used to query?
  - VWWHFSfQ 2y ago
    Yes but you only have a single index containing the UUIDs instead of duplicated everywhere that is using it as a foreign key relation. Those indexes quickly become explosive in relations like e.g. m2m tables.
    - deleted 2y ago
      [deleted]
sgarland 2y ago
UUIDv4 specifically (though ULID [EDIT: this is incorrect, I was thinking of NanoID] has the same problem). Anything non-k-sortable will bloat a B+tree index, causing far more reads than should be required. MySQL (technically InnoDB) and SQL Server have a huge problem with them due to their design as a clustering index – since the tuples are physically located, or clustered, around the PK, the random nature means that doing a simple query like "SELECT id FROM customer WHERE date_created > NOW() - INTERVAL 1 WEEK" means that the hits are going to be on tons of different pages. Even if everything is cached, that's still a lot of extra work. Postgres has its own problems with them as well. In the above query, it's possible that it could be an index-only scan. Great, you don't have to dereference the pointer to find the tuple in the heap! Except you still have to go through the Visibility Map, and it's going to be extremely bloated with a UUIDv4 PK. Additionally, due to the combination of how Postgres manages the A in ACID (via `full_page_writes`) and its MVCC implementation, a change to any indexed column in a table results in the entire row being re-written. While logically sequential tuples may be physically co-located (which would be ideal from a write perspective), the index pages are highly unlikely to be if there are non-k-sortable keys, so now you've bloated the WAL as well. If you must use a UUID (I maintain you generally don't – PlanetScale uses integers internally [0], so clearly it can be done at scale), UUIDv7 is probably the best from a DB performance perspective. [0]: https://github.com/planetscale/discussion/discussions/366 https://github.com/planetscale/discussion/discussions/366
- auspiv 2y ago
  UUID7 basic information/examples - https://uuid7.com https://uuid7.com
  - deleted 2y ago
    [deleted]
- quadrature 2y ago
  >though ULID has the same problem I thought that ULID was meant to solve these issues by being sortable, is that not right ?.
hot_gril 2y ago
What's the problem with bigserial as a PK?

What's the problem using UUID as a PK? I really like 128bit numbers for PK, more than 64bit-int or anything text. I'm generally putting a ULID or UUIDV7/8 in there.

VWWHFSfQ 2y ago

128-Bit numbers are absolutely humongous and just by virtue of being a primary key means they get duplicated for every single relation. So if you have your user ID, for instance, as a 128bit uuid, any table and column that references the user ID is now also a 128-bit uuid. It's not a problem if you have very small amounts of data. But once your tables start to grow it will become misery to work with. In my opinion, the best pattern is to have just a regular bigserial for primary keys to use for internal database relations since postgres is extremely good at generating and optimizing PK sequences (You're taking away all that optimization just because you want an enormous random number to be the primary key). Then just have a uuid field for application-level identifiers and natural keys. You'll save yourself a whole lot of headache with this pattern.

londons_explore 2y ago
UUID's are for when an auto-incrementing ID can't be used, usually due to scale (if you will be inserting millions of records per second from millions of clients). However, postgres isn't suited to such scale (something like spanner IS). Therefore, you probably shouldn't be using UUID's with postgres. The other reason for UUID's is because the system is badly architected and , for example, the client gets to choose their own ID which are written into a DB. Such systems generally are best avoided.
- bruce511 2y ago
  Also think about systems that are "beyond one database". Like data that lives in multiple places at the same time (on phone local storage, in database, consolidated and syncd either other databases etc.) In other words once you move beyond '1 sql database' they become important. (I'm not downvoting you, just disagreeing with you.)
  - djbusby 2y ago
    This is why I asked at the start of the thread. We *need* 128bit numbers to make the uniqueness work across loads of systems (like a distributed/federated type thing)
    - hot_gril 2y ago
      Maybe, but they don't need to be the PKs. And you only need some 128bit number at all if you want one generator not to be able to guess the others' numbers, like if clients are generating them.
      - bruce511 2y ago
        Are you mixing up primary key with clustered key? Primary Key serves to uniquely identify the row. This is used to reference the tow in other tables. (So the Invoice table contains the Primary Key value for the customer.) The Clustered key describes the physical sort order on the disk. Clustering on your most common search improves performance for that search. Searching on other criteria becomes equally slow. So, it's really common to search through invoices by date. So a date key is ideal for a clustered index on that table. Looking up all invoices for a date range is faster than all invoices by, say, customer. Clustering the customer or employee table by date though isn't great, I don't want to often see my employees on take-on order. We teach Primary Keys at Uni, but in my era anyway they didn't put the same emphasis on selecting a correct clustered key. So I feel like people tend to assume the Primary Key is also the clustered key.
        hot_gril 2y ago
        I'm used to Postgres, which doesn't have clustered key. If this is MySQL and you're using a clustered key, pkey is still how you'd identify rows in foreign keys etc, so I think the same advice of bigserial pkey applies. We learned using natural pkeys in college, which in practice burned me enough that I'll never do it anymore. I just set some uniqueness constraints.
  - deathanatos 2y ago
pdhborges 2y ago
> But once your tables start to grow it will become misery to work with. I buy the data layout argument for databases with clustered tables without any smi-sequencial uuid support. But the storage argument looks vanishingly applicable to me: if someone needs to add a column to one of these tables it basically offsets a 4 byte optimization already.
- bruce511 2y ago
  8 Byte (assuming 128 bit instead of 64 bit) but yeah. It's not quite as simple as saving 8 bytes per row though. It's 8 bytes for the UUID, plus 8 for at least the PK, plus 8 more for any other keys the UUID is in. Then you need to do that for any foreign-key-fields and keys those are in as well. However, unless your table has very few non-uuid columns, the rest of the table size will dwarf the extra n*8 bytes here. And if you are storing any blobs (like pictures, documents etc) then frankly all the uuids won't add up to anything. In summary, whether using uuids or not is right for you depends a Lot on your context. An Insert Only log table, with a billion rows, is a very different use case to say a list of employees or customers. Generally I'm very comfortable with uuids for tables of say less than 100m rows. Very high inserting though suggests tables bigger than that, and perhaps benefits from different strategies. When it comes to data stored in multiple places (think on-phone first, syncd to cloud, exported from multiple places to consolidated BI systems), uuids solve the Lot of problems. Context is everything.
xyzzy_plugh 2y ago
They're not "absolutely humongous" but merely two times as large as a 64-bit number. That's nothing. They're not even that bad for performance, assuming you have random writes regardless. UUIDs work incredibly well. Where they don't work well is when they're stored in textual encoding (base16 with dashes) which I've encountered far more than I'd like. They also don't work (random) for append-only insertions like events, for which ULID (or newer UUID variants) are more appropriate. But for primary keys? Totally fine. > extremely good at generating and optimizing PK sequences (You're taking away all that optimization just because you want an enormous random number to be the primary key). Please share the benchmarks to back this up. UUIDs optimize down perfectly fine. If you're trying to squeeze every last ounce of performance out of the database then certainly this is a yak you can shave. But most Postgres users have 1001 problems that are much bigger than UUIDs. If you've managed to avoid folks writing shitty queries and this is the bottleneck you're facing then I salute you. Otherwise, don't worry. UUIDs are fine.
- sgarland 2y ago
  > but merely two times as large as a 64-bit number. That's nothing. It's literally 2x as large. This starts to matter quite a bit at the millions/billions of rows scale. > They're not even that bad for performance, assuming you have random writes regardless. > Please share the benchmarks to back this up. I've done [0] benchmarks, with [1] source to recreate it. This was for loading data, but it demonstrates quite nicely the problem of massive B+tree splits. An integer PK loads data ~2x as quickly as a UUIDv4 PK stored as `uuid` type, and ~3x as fast as one stored as `text` type. > But most Postgres users have 1001 problems that are much bigger than UUIDs. All of which become compounded by the use of non-k-sortable keys. > If you've managed to avoid folks writing shitty queries and this is the bottleneck you're facing then I salute you. Using UUIDs as a PK without having an extremely good reason to do so demonstrates a lack of understanding of how the underlying technology works. Worse, it demonstrates a lack of understanding of a basic data structure, something that devs should have near and dear to their heart. With Postgres specifically, the use of UUIDv4 (or ULID) PKs will also cause an enormous amount of WAL bloat [2], unless you happen to be rolling your own DB and are using ZFS for the file system, and you've disabled `full_page_writes` (which is the only safe way to do so). I have personally seen this take down prod, because the other nodes couldn't keep up with the changes being issued (fun fact, most AWS instances under `.4xlarge` in size have a baseline network speed that's much lower than rated). [0]: https://gist.github.com/stephanGarland/ee38c699a9bb999894d760a10c227bc4 https://gist.github.com/stephanGarland/ee38c699a9bb999894d76... [1]: https://gist.github.com/stephanGarland/fe0788cf2332d6e241ff3ac6d9440caf https://gist.github.com/stephanGarland/fe0788cf2332d6e241ff3... [2]: https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-page-writes/ https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-pa...
moomoo11 2y ago
Quick question. So you’re saying use bigserial for PK (instead of uuid) and have another column where I set a uuid in case of application level identifier. But wouldn’t that require a index on it if it’s going to be an identifier that’s used to query?
- VWWHFSfQ 2y ago
  Yes but you only have a single index containing the UUIDs instead of duplicated everywhere that is using it as a foreign key relation. Those indexes quickly become explosive in relations like e.g. m2m tables.
  - deleted 2y ago
    [deleted]

sgarland 2y ago

UUIDv4 specifically (though ULID [EDIT: this is incorrect, I was thinking of NanoID] has the same problem). Anything non-k-sortable will bloat a B+tree index, causing far more reads than should be required. MySQL (technically InnoDB) and SQL Server have a huge problem with them due to their design as a clustering index – since the tuples are physically located, or clustered, around the PK, the random nature means that doing a simple query like "SELECT id FROM customer WHERE date_created > NOW() - INTERVAL 1 WEEK" means that the hits are going to be on tons of different pages. Even if everything is cached, that's still a lot of extra work. Postgres has its own problems with them as well. In the above query, it's possible that it could be an index-only scan. Great, you don't have to dereference the pointer to find the tuple in the heap! Except you still have to go through the Visibility Map, and it's going to be extremely bloated with a UUIDv4 PK. Additionally, due to the combination of how Postgres manages the A in ACID (via `full_page_writes`) and its MVCC implementation, a change to any indexed column in a table results in the entire row being re-written. While logically sequential tuples may be physically co-located (which would be ideal from a write perspective), the index pages are highly unlikely to be if there are non-k-sortable keys, so now you've bloated the WAL as well. If you must use a UUID (I maintain you generally don't – PlanetScale uses integers internally [0], so clearly it can be done at scale), UUIDv7 is probably the best from a DB performance perspective. [0]: https://github.com/planetscale/discussion/discussions/366 https://github.com/planetscale/discussion/discussions/366

auspiv 2y ago
UUID7 basic information/examples - https://uuid7.com https://uuid7.com
- deleted 2y ago
  [deleted]
quadrature 2y ago
>though ULID has the same problem I thought that ULID was meant to solve these issues by being sortable, is that not right ?.

hot_gril 2y ago

What's the problem with bigserial as a PK?