8 ms·

I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs to optimize access times by a millisecond or two. UUID is totally worth

I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs to optimize access times by a millisecond or two. UUID is totally worth the cost. UUID actually performs much better than I thought based on the author's example with a COUNT query. COUNT queries aren't very efficient because typically, all records are traversed; here we're talking about a 50% slowdown on 10 million records... How often do you need to access 10 million contiguous records? Most of the time, for user-facing apps, you'll be accessing 100 contiguous records at most and, in fact, most queries will access a single record... I suspect that the average per-query performance loss for a typical app is probably less than 5%. Also, you could simply index by a separate date/timestamp field if you need records to be ordered by time and probably won't incur any performance cost. IMO, unless you're building an app for high-frequency trading, auto-incrementing IDs aren't worth the pain and lack of flexibility.

jongjong 3y ago
Recently, I wrote a client-side wallet app for cryptocurrency use case and it required transactions to be created and signed entirely on the client-side and so the transaction UUID had to be created on the client side (before signing) and I was surprised at how elegant and simple the application's logic turned out to be (both on the front end and back end). One of the best things about UUIDs is that if your front end tries to create a resource and fails due to a bad connection, you can simply resend that exact same resource for creation again (with the same UUID) and you don't need to worry about duplicate records being inserted into the database since you would get an ID collision; it results in much simpler/cleaner code which is idempotent and deterministic by default. With auto-incrementing IDs, if there is a connection issue or other failure, it's not possible to cleanly figure out if a resource was already created or not since only the database knows what the ID of that resource was until that ID is sent back and reaches the client across the network. Just because the client did not receive a successful response from the server, it does not mean that the resource was not created; and if it was, you cannot know since the client has no way of referencing that resource in a reliable way. It just seems completely wrong that the client (which created the resource) cannot reference it until the database has inserted it. It's a hack to pretend that resource creation starts in the database, when in fact, it starts on the front end.
- gradys 3y ago
  You can achieve this without UUID keys if you attach UUIDs to the requests. This has the advantage of working for all kinds of mutating requests, not just creates, but requires that you store these request UUIDs for some period of time as well.
  - jongjong 3y ago
    It's still not as elegant because it doesn't work in a multi-process or multi-server back end; if the request fails, you need to make sure that the retry request will hit the same server process (assuming you're keeping the UUIDs in memory on the server). You can use some in-memory data store like Redis to share the request UUIDs across multiple processes/hosts but that can add a lot more complexity, latency and risks and is just not worth it IMO. Even in a single-host setup, it can be a problem because what happens if your process crashes and restarts just after a resource was created in the database but before the ID was sent to the client (with success message)? You would end up with a duplicate record in your DB after the retry since your newly restarted server would not have the UUID in its memory (even though the resource was in fact already created on the server a few milliseconds before the last crash). With the Redis (or similar) solution, you need to make sure that the request UUIDs expire after some time and are cleaned up to avoid memory bloat which is a pain... I mean that complex solution probably uses up a lot more resources than just using UUIDs in the database as IDs.
    - ramchip 3y ago
      You can use the DB for this as well, just make a table e.g. "requests" with two columns, the user and the request token. Old entries can be purged on a cron job. If you add a column to store params as well then you can also do better validation: > Responding when a customer changes request parameters on a subsequent call where the client request ID stays the same > We design our APIs to allow our customers to explicitly state their intent. Take the situation where we receive a unique client request token that we have seen before, but there is a parameter combination that is different from the earlier request. We find that it is safest to assume that the customer intended a different outcome, and that this might not be the same request. In response to this situation, we return a validation error indicating a parameter mismatch between idempotent requests. To support this deep validation, we also store the parameters used to make the initial request along with the client request identifier. https://aws.amazon.com/builders-library/making-retries-safe-with-idempotent-APIs/ https://aws.amazon.com/builders-library/making-retries-safe-...
dewey 3y ago
> sacrifice the beauty and elegance of UUIDs Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?
- maxgee 3y ago
  Nothing. Integers are simpler and faster. They're also incorrect about COUNTs requiring a full record scan.
  - xkqd 3y ago
    How are integers simpler? Even generation of unique integers is more complicated, let alone migrating them or moving them around.
  - phito 3y ago
    Nothing? Really? Have you ever thought about that?
  - catlifeonmars 3y ago
    1. You have to handle race conditions if there is ever more than one process writing to the DB at one time 2. It’s ambiguous whether a number is an ID. E.g. when looking at logs 3. Numbers leak information about table size, row age, and crucially future row IDs.
- ceeam 3y ago
  Their uniqueness of course. > To be able to generate keys independently of the database > To move sets of related records between different databases without having to deal with renumbering everything
  - jandrewrogers 3y ago
    Unfortunately, UUIDs aren't as universally unique in practice as people think. The standard flavors are banned in quite a few organizations that care a lot about data integrity for valid reasons. Standard UUIDs work for some applications but definitely not all. For most applications you are better off using a sensible structured key, like incrementing an integer or similar, and encrypting it if you want to obscure it. Encrypting a 128-bit value is approximately free on modern CPUs.
whack 3y ago
> The moral of the story is that data locality matters, and it can pop up in the most surprising of places. Using random is typically the worst thing you can do for locality, so if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option The author isn't suggesting abandoning UUIDs, he is suggesting using something like UUIDv7 which preserves locality. For most use-cases, this seems like a very reasonable recommendation with no significant downsides I can think of.
- deleted 3y ago
  [deleted]
- fauigerzigerk 3y ago
  I agree that UUIDv7 will usually be the best choice. The one downside I can think of is that it may sometimes be necessary to let people know the identity of a thing without also telling them exactly when that thing was created.
  - pkilgore 3y ago
    COMB UUIDs can help here.
    - fauigerzigerk 3y ago
      Why do COMB UUIDs not have this problem? Do they not contain a timestamp?
      - pkilgore 3y ago
        It just orders ids based on a monotonic clock that wraps after a certain time -- it's enough to get a lot of of the performance benefit of ordered keys without directly exposing the unix wall clock time they were created (absent a dedicated attacker with the ability to create ids without you picking up on it for a timing attack, I suppose). Whether that tradeoff makes sense probably depends.
  - remram 3y ago
pgaddict 3y ago
It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, because of FPW and stuff like that. With serial IDs we'd write a fraction of that. It doesn't take much to hit max_wal_size and trigger a checkpoint, starting a new cycle with FPWs. Got a replica? Well, now you need to send the WAL over network. Is the bandwidth limited (another DC?), sorry to hear that. Is the replica sync and you have to wait. Well, that's unfortunate. In other words, the lack of locality seems like a detail but at scale it's actually a damn huge deal.
- deleted 3y ago
  [deleted]
- catlifeonmars 3y ago
  At scale you’re probably sharded across multiple DBs and you’re already operating through replicas. Point being you’re less likely to hit a warm cache as you scale up anyway as your application layer gets load balanced to different DB endpoints.
  - jongjong 3y ago
    Yes exactly, with proper sharding, raw performance is not as important; to some extent, you trade it away for improved concurrency. In fact, I struggle to see how one would implement sharding with auto-incrementing integers (you would get ID collisions for different resources across different shards/database instances); there needs to be a way to uniquely refer to resources across potentially multiple databases and UUIDs are one of the best ways to achieve that. Auto-incrementing IDs simply don't scale beyond a single host so I don't see how they can be good for scalability. Too many devs conflate raw performance with scalability. They are not the same at all - In fact, high scalability often incurs a performance overhead.
    - 3y ago
deleted 3y ago
[deleted]
danpalmer 3y ago
> I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs... 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.
- biztos 3y ago
  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.
  - danpalmer 3y ago
    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.

jongjong 3y ago

Recently, I wrote a client-side wallet app for cryptocurrency use case and it required transactions to be created and signed entirely on the client-side and so the transaction UUID had to be created on the client side (before signing) and I was surprised at how elegant and simple the application's logic turned out to be (both on the front end and back end). One of the best things about UUIDs is that if your front end tries to create a resource and fails due to a bad connection, you can simply resend that exact same resource for creation again (with the same UUID) and you don't need to worry about duplicate records being inserted into the database since you would get an ID collision; it results in much simpler/cleaner code which is idempotent and deterministic by default. With auto-incrementing IDs, if there is a connection issue or other failure, it's not possible to cleanly figure out if a resource was already created or not since only the database knows what the ID of that resource was until that ID is sent back and reaches the client across the network. Just because the client did not receive a successful response from the server, it does not mean that the resource was not created; and if it was, you cannot know since the client has no way of referencing that resource in a reliable way. It just seems completely wrong that the client (which created the resource) cannot reference it until the database has inserted it. It's a hack to pretend that resource creation starts in the database, when in fact, it starts on the front end.

gradys 3y ago
You can achieve this without UUID keys if you attach UUIDs to the requests. This has the advantage of working for all kinds of mutating requests, not just creates, but requires that you store these request UUIDs for some period of time as well.
- jongjong 3y ago
  It's still not as elegant because it doesn't work in a multi-process or multi-server back end; if the request fails, you need to make sure that the retry request will hit the same server process (assuming you're keeping the UUIDs in memory on the server). You can use some in-memory data store like Redis to share the request UUIDs across multiple processes/hosts but that can add a lot more complexity, latency and risks and is just not worth it IMO. Even in a single-host setup, it can be a problem because what happens if your process crashes and restarts just after a resource was created in the database but before the ID was sent to the client (with success message)? You would end up with a duplicate record in your DB after the retry since your newly restarted server would not have the UUID in its memory (even though the resource was in fact already created on the server a few milliseconds before the last crash). With the Redis (or similar) solution, you need to make sure that the request UUIDs expire after some time and are cleaned up to avoid memory bloat which is a pain... I mean that complex solution probably uses up a lot more resources than just using UUIDs in the database as IDs.
  - ramchip 3y ago
    You can use the DB for this as well, just make a table e.g. "requests" with two columns, the user and the request token. Old entries can be purged on a cron job. If you add a column to store params as well then you can also do better validation: > Responding when a customer changes request parameters on a subsequent call where the client request ID stays the same > We design our APIs to allow our customers to explicitly state their intent. Take the situation where we receive a unique client request token that we have seen before, but there is a parameter combination that is different from the earlier request. We find that it is safest to assume that the customer intended a different outcome, and that this might not be the same request. In response to this situation, we return a validation error indicating a parameter mismatch between idempotent requests. To support this deep validation, we also store the parameters used to make the initial request along with the client request identifier. https://aws.amazon.com/builders-library/making-retries-safe-with-idempotent-APIs/ https://aws.amazon.com/builders-library/making-retries-safe-...

dewey 3y ago

> sacrifice the beauty and elegance of UUIDs Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?

maxgee 3y ago
Nothing. Integers are simpler and faster. They're also incorrect about COUNTs requiring a full record scan.
- xkqd 3y ago
  How are integers simpler? Even generation of unique integers is more complicated, let alone migrating them or moving them around.
- phito 3y ago
  Nothing? Really? Have you ever thought about that?
- catlifeonmars 3y ago
  1. You have to handle race conditions if there is ever more than one process writing to the DB at one time 2. It’s ambiguous whether a number is an ID. E.g. when looking at logs 3. Numbers leak information about table size, row age, and crucially future row IDs.
ceeam 3y ago
Their uniqueness of course. > To be able to generate keys independently of the database > To move sets of related records between different databases without having to deal with renumbering everything
- jandrewrogers 3y ago
  Unfortunately, UUIDs aren't as universally unique in practice as people think. The standard flavors are banned in quite a few organizations that care a lot about data integrity for valid reasons. Standard UUIDs work for some applications but definitely not all. For most applications you are better off using a sensible structured key, like incrementing an integer or similar, and encrypting it if you want to obscure it. Encrypting a 128-bit value is approximately free on modern CPUs.

whack 3y ago

> The moral of the story is that data locality matters, and it can pop up in the most surprising of places. Using random is typically the worst thing you can do for locality, so if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option The author isn't suggesting abandoning UUIDs, he is suggesting using something like UUIDv7 which preserves locality. For most use-cases, this seems like a very reasonable recommendation with no significant downsides I can think of.

deleted 3y ago
[deleted]
fauigerzigerk 3y ago
I agree that UUIDv7 will usually be the best choice. The one downside I can think of is that it may sometimes be necessary to let people know the identity of a thing without also telling them exactly when that thing was created.
- pkilgore 3y ago
  COMB UUIDs can help here.
  - fauigerzigerk 3y ago
    Why do COMB UUIDs not have this problem? Do they not contain a timestamp?
    - pkilgore 3y ago
      It just orders ids based on a monotonic clock that wraps after a certain time -- it's enough to get a lot of of the performance benefit of ordered keys without directly exposing the unix wall clock time they were created (absent a dedicated attacker with the ability to create ids without you picking up on it for a timing attack, I suppose). Whether that tradeoff makes sense probably depends.
- remram 3y ago

pgaddict 3y ago

It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, because of FPW and stuff like that. With serial IDs we'd write a fraction of that. It doesn't take much to hit max_wal_size and trigger a checkpoint, starting a new cycle with FPWs. Got a replica? Well, now you need to send the WAL over network. Is the bandwidth limited (another DC?), sorry to hear that. Is the replica sync and you have to wait. Well, that's unfortunate. In other words, the lack of locality seems like a detail but at scale it's actually a damn huge deal.

deleted 3y ago
[deleted]
catlifeonmars 3y ago
At scale you’re probably sharded across multiple DBs and you’re already operating through replicas. Point being you’re less likely to hit a warm cache as you scale up anyway as your application layer gets load balanced to different DB endpoints.
- jongjong 3y ago
  Yes exactly, with proper sharding, raw performance is not as important; to some extent, you trade it away for improved concurrency. In fact, I struggle to see how one would implement sharding with auto-incrementing integers (you would get ID collisions for different resources across different shards/database instances); there needs to be a way to uniquely refer to resources across potentially multiple databases and UUIDs are one of the best ways to achieve that. Auto-incrementing IDs simply don't scale beyond a single host so I don't see how they can be good for scalability. Too many devs conflate raw performance with scalability. They are not the same at all - In fact, high scalability often incurs a performance overhead.
  - 3y ago

deleted 3y ago

[deleted]

danpalmer 3y ago

> I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs... 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.

biztos 3y ago
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.
- danpalmer 3y ago
  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.