3 ms·

I don’t understand the part where monotonicity of UUIDs is discussed. UUIDs should never be assumed monotonic, or in a specific format per se. If you strictly n

by sedatk 2y ago

I don’t understand the part where monotonicity of UUIDs is discussed. UUIDs should never be assumed monotonic, or in a specific format per se. If you strictly need monotonicity, just use an integer counter. Let UUIDs be black boxes, and assume that v7 is just a better black box that deals with DB indexes better.

bongodongobob 2y ago
Integer counters are a problem because they leak information. In most cases I've encountered that's not acceptable.
- sgarland 2y ago
  So don’t expose them in the URL. Or have separate internal and external IDs. So many options that don’t destroy B+trees.
- sedatk 2y ago
  Monotonic UUIDs leak information too.
- switch007 2y ago
  UUIDv7, for example, leaks the timestamp I’ve met more than one architect who hands waves that fact away during a “leaking integers is bad!” campaign
paulddraper 2y ago
The monotonicity can be useful in multiple contexts: colocating database data by time, providing "sooner than" comparisons. Integers are monotonic but can't be distributed like UUIDs. Unless you make them 128 bits ;) As usual, most people are not dumb most of the time, even if it seems that way.
- cm2187 2y ago
  And if you have a clustered index like in MS SQL Server, a non monotonic uuid results in inserting the data in the middle of the table (bad performance) rather than appending to the end.
  - Tostino 2y ago
    For the Postgres fans out there, it also kills performance on that side of the fence. You have things like wal amplification due to using things like UUID v4 (random prefix). I think v7 should greatly help with that.
    - sgarland 2y ago
      It also hurts query performance in some circumstances even in Postgres, due to the Visibility Map.
- sgarland 2y ago
  > [integers] can’t be distributed like UUIDs They can, to an extent. The use of integers as a primary key has been a solved problem for quite some time, usually by either interleaving distribution among servers, or a coordinator handing chunks out. If you mean enabling the ability to do joins across physical databases, my counter to that is it’s an unsupported method by any RDBMS, and should be discouraged. You can’t have foreign key constraints across DBs, and without those, I in no way trust the application to consistently do the right thing and maintain referential integrity. I’ve seen too many instances of it going wrong. The only way I can see it working is something involving Postgres’ FDW, but I’m still not convinced that could maintain atomic updates on its own; maybe with a Pub/Sub in addition? This rapidly gets hideously complicated. Just design good, normalized schema that can maintain performance at scale. Then when/if it doesn’t, shard with something that handles the logic for you and is tested, like Vitess or Citus.
  - paulddraper 2y ago
    For example, imagine a client that can generate a UUID and at a later time save that to remote database. Or imagine two separate databases that get merged.
    - sgarland 2y ago
      > For example, imagine a client that can generate a UUID and at a later time save that to remote database. DBs can return inserted data to you; Postgres and SQLite can return the entire row, and MySQL can return the last generated auto-increment ID. > Or imagine two separate databases that get merged. This is sometimes a legitimate need, yes, but it does make me smirk a bit since it goes against the concept of microservices owning their own domain (which I never thought was a great idea for most). However, it’s also quite possible to merge DBs that used integers. Depending on the amount of tables and their relationships (or rather, lack of formally defined ones) it may be challenging, but nothing that can’t be handled. I mostly just question the desire to dramatically harm DB performance (in the case of UUIDv4) for the sake of undoing future problems more easily.
      - 2y ago
- sedatk 2y ago
  > colocating database data by time, providing "sooner than" comparisons. If you need to perform date/time related operations, use date/time related data types, not an unrelated type that happens to have some arbitrary timestamp embedded in its binary layout. > Integers are monotonic but can't be distributed like UUIDs. Yes, use UUIDs if you need distribution, use integers if you need monotonicity. If you need "monotonic and distributed", you need an external authority for proper distribution of those IDs. Then, an integer would still work.
  - paulddraper 2y ago
    > use date/time...not...timestamp :/
sgarland 2y ago
The nice thing about them is you don’t have to assume, though, because the version is baked into an octet. Does the 3rd field start with a 4? v4. 7? v7. Etc. Re: monotonicity, as I view it, v7 is the best compromise I can make with devs as a DBRE where the DB isn’t destroyed, and I don’t have to try to make them redesign huge swaths of their app.
- sedatk 2y ago
  The part I'm talking about proposes "counters" in UUID, not just date/time.

by sedatk 2y ago

bongodongobob 2y ago

Integer counters are a problem because they leak information. In most cases I've encountered that's not acceptable.

sgarland 2y ago
So don’t expose them in the URL. Or have separate internal and external IDs. So many options that don’t destroy B+trees.
sedatk 2y ago
Monotonic UUIDs leak information too.
switch007 2y ago
UUIDv7, for example, leaks the timestamp I’ve met more than one architect who hands waves that fact away during a “leaking integers is bad!” campaign

paulddraper 2y ago

The monotonicity can be useful in multiple contexts: colocating database data by time, providing "sooner than" comparisons. Integers are monotonic but can't be distributed like UUIDs. Unless you make them 128 bits ;) As usual, most people are not dumb most of the time, even if it seems that way.

cm2187 2y ago
And if you have a clustered index like in MS SQL Server, a non monotonic uuid results in inserting the data in the middle of the table (bad performance) rather than appending to the end.
- Tostino 2y ago
  For the Postgres fans out there, it also kills performance on that side of the fence. You have things like wal amplification due to using things like UUID v4 (random prefix). I think v7 should greatly help with that.
  - sgarland 2y ago
    It also hurts query performance in some circumstances even in Postgres, due to the Visibility Map.
sgarland 2y ago
> [integers] can’t be distributed like UUIDs They can, to an extent. The use of integers as a primary key has been a solved problem for quite some time, usually by either interleaving distribution among servers, or a coordinator handing chunks out. If you mean enabling the ability to do joins across physical databases, my counter to that is it’s an unsupported method by any RDBMS, and should be discouraged. You can’t have foreign key constraints across DBs, and without those, I in no way trust the application to consistently do the right thing and maintain referential integrity. I’ve seen too many instances of it going wrong. The only way I can see it working is something involving Postgres’ FDW, but I’m still not convinced that could maintain atomic updates on its own; maybe with a Pub/Sub in addition? This rapidly gets hideously complicated. Just design good, normalized schema that can maintain performance at scale. Then when/if it doesn’t, shard with something that handles the logic for you and is tested, like Vitess or Citus.
- paulddraper 2y ago
  For example, imagine a client that can generate a UUID and at a later time save that to remote database. Or imagine two separate databases that get merged.
  - sgarland 2y ago
    > For example, imagine a client that can generate a UUID and at a later time save that to remote database. DBs can return inserted data to you; Postgres and SQLite can return the entire row, and MySQL can return the last generated auto-increment ID. > Or imagine two separate databases that get merged. This is sometimes a legitimate need, yes, but it does make me smirk a bit since it goes against the concept of microservices owning their own domain (which I never thought was a great idea for most). However, it’s also quite possible to merge DBs that used integers. Depending on the amount of tables and their relationships (or rather, lack of formally defined ones) it may be challenging, but nothing that can’t be handled. I mostly just question the desire to dramatically harm DB performance (in the case of UUIDv4) for the sake of undoing future problems more easily.
    - 2y ago
sedatk 2y ago
> colocating database data by time, providing "sooner than" comparisons. If you need to perform date/time related operations, use date/time related data types, not an unrelated type that happens to have some arbitrary timestamp embedded in its binary layout. > Integers are monotonic but can't be distributed like UUIDs. Yes, use UUIDs if you need distribution, use integers if you need monotonicity. If you need "monotonic and distributed", you need an external authority for proper distribution of those IDs. Then, an integer would still work.
- paulddraper 2y ago
  > use date/time...not...timestamp :/

sgarland 2y ago

The nice thing about them is you don’t have to assume, though, because the version is baked into an octet. Does the 3rd field start with a 4? v4. 7? v7. Etc. Re: monotonicity, as I view it, v7 is the best compromise I can make with devs as a DBRE where the DB isn’t destroyed, and I don’t have to try to make them redesign huge swaths of their app.

sedatk 2y ago
The part I'm talking about proposes "counters" in UUID, not just date/time.