6 ms·

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

• 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.
    • coredog64 3y ago
      We did auto-increment integers with multiple servers like 20 years ago: The caveat is that you have to know how many servers are in the set in advance. Each server increments by the population size, and their starting number is their position within the pool. Not hyper scale, but good enough for failover or a 3-5 node setup.
      • catlifeonmars 3y ago
        Oh this is clever! I guess it’s actually very similar conceptually to a vector clock. (In that you have partitioned the space of natural numbers into K countably infinite sequences). If you wanted to (practically speaking anyway) overcome the requirement that K is known, you could borrow prefix based counting from the p-adics.
      • viraptor 3y ago
        Or increment by more than you need. If you think you'll only ever have 5 nodes, increment by 20. Lots of space for expansion and in practice you'll hit the datatype limits just a bit earlier - you'd need to work around them anyways.
      • egeozcan 3y ago
        Hi/Lo algorithm also works fine for most cases
      • ilyt 3y ago
        IIRC that's what MySQL Galera is doing by default mysql> CREATE TABLE animals ( -> id MEDIUMINT NOT NULL AUTO_INCREMENT, -> name CHAR(30) NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.34 sec) mysql> INSERT INTO animals (name) VALUES -> ('dog'),('cat'),('penguin'), -> ('lax'),('whale'),('ostrich'); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM animals; +----+---------+ | id | name | +----+---------+ | 3 | dog | | 6 | cat | | 9 | penguin | | 12 | lax | | 15 | whale | | 18 | ostrich | +----+---------+ 6 rows in set (0.00 sec)
  • magicalhippo 3y ago
    Reminds me of a talk about ZFS performance, where they presented some benchmark results showing that as the number of concurrent clints doing pure sequential IO increases, the more the load appears as random IO to the filesystem. So with high enough concurrent load it's effectively all random IO and that's the primary thing worth optimizing for.
• ok123456 3y ago
  Then just make a generated column that's a 32bit integer hash of the uuid for this particular case and create an index on that? Use that during expensive queries that blow up your cache locality if it matters.
  • insanitybit 3y ago
    A 32bit integer hash won't have locality either.
    • ok123456 3y ago
      Ok. Pick a resolution where you won't get collisions and that is a native datatype.
      • patrec 3y ago
        I feel that you don't understand what locality is about: the property of IDs that were generated closely together in time to be close together numerically. You don't get that by "hashing" the UUID (which makes no sense anyway, since you might as well just take some truncation of the UUID). In fact the whole idea behind a hash is to destroy this property of the input data. The reason the numerical locality matters is that it is much more efficient to in-sequence-insert several numbers that are close together into an index than to in-sequence-insert the identical amount of randomly distributed numbers. The GP was talking about the first graph here: 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...
        • ok123456 3y ago
          I thought you were talking about cache locality within the CPU. Real cache locality. If you care about the insert location, why not just add a brin index on a timestamp field and use that instead of assuming that the index is sequential.
          • 3y ago
• unshavedyak 3y ago
  Out of my scope, but why are UUIDs even discussed? ULIDs ~~(and i think Nanoids?)~~ don't suffer these same problems. Locality and ordering alone make me[1] think ordered ULIDs (and friends) are the only thing worth discussing. Is there some value to UUIDs over ULIDs that make these discussions largely revolve around Autoincrement vs UUIDs rather than Autoincrement vs ULIDs(and friends)? [1]: Again, totally out of my wheel house. edit: Apparently UUIDv7 exists, which is similar to ULID, so my question pertains to UUIDv5 and below, i think. edit2: I think Nanoids do suffer the same problem. They're just small... i think.
  • letitbeirie 3y ago
    Postgres supports UUIDs natively
    • unshavedyak 3y ago
      Is that worth the pain of dealing with randomized inserts? I guess i just don't mind creating a ULID (or i guess UUIDv7 is newly proposed and sortable) and inserting that. Native DB support is irrelevant to me for randomized bits unless it affects storage, sorting, paging, etc. Does it?
      • code-e 3y ago
        It does affect storage and sorting. A native UUID type uses 16 bytes. The alternative is text encoding (32 bytes for hex), or maybe a raw BYTEA. Postgres also has SortSupport for the UUID type, which basically means if the first 8 bytes only has one matching row, then the remaining 8 bytes can be skipped. Combine that with a ULID where the most first half is basically a timestamp, you'll get performance close to using a single 8byte BIGSERIAL. You can also write a plpgsql function to generate these ULIDs in the database.
    • lolinder 3y ago
      ULIDs are byte-compatible with UUIDs, so the only thing Postgres's native support gives you over ULIDs is that Postgres can generate new UUIDs for you instead of having to do it in the application before insertion.
• qaq 3y ago
  this sounds horrible until you realise that a modern SSD will write that in about 1.5 sec.
  • pgaddict 3y ago
    That was just an example calculation, to illustrate the write amplification factor, of course. You can scale it up pretty arbitrarily. I mentioned only WAL for simplicity, but it also has to modify and write out the index pages themselves, and write them out eventually. And that's going to be mostly random I/O. Flash storage is good at handling that, ofc, but if things are adding up like this ... Not to mention you still have to copy the WAL over network to replica, or perhaps to multiple replicas. And if you have physical backups with PITR, you gotta keep all the WAL somewhere too.
    • qaq 3y ago
      sure but not many workloads are writing out million inserts per second.
• uppiiii765 3y ago
  Mmmhhh should that not be highly efficient for flash based storage?
• nine_k 3y ago
  If you want locality, speed, simplicity, etc above all, use an incremented integer and be done with it. UUIDs belong where you can't afford that simplicity. Where you e.g. cannot coordinate the creation of your primary keys. Or where you cannot allow them to be predictable. There you pay the price. In practice I noticed that the size of PKs and their poor locality start to play a role only after a huge basket of lower-hanging fruit has been collected. There are relatively few places where their role is dramatic.
  • gregmac 3y ago
    > UUIDs belong where you can't afford that simplicity. Where you e.g. cannot coordinate the creation of your primary keys. Or where you cannot allow them to be predictable. There you pay the price. You often don't realize you you have non-simple needs until your application is reasonably mature, and in production. If you already picked integer keys, you now either forever deal with the issues caused by not using UUIDs, or you deal with the unknown-but-non-zero pain of converting to UUIDs.
    • esafak 3y ago
      The age old startup bargain: do you want to pay a small cost up front, or a large one later? If only you'd seen it coming!
  • marcosdumay 3y ago
    > Where you e.g. cannot coordinate the creation of your primary keys. You are writing your data into a DBMS. Coordinating the creation of primary keys is one of the cheapest tasks around, if you can't do that, how is your database still online? > Or where you cannot allow them to be predictable. You don't need to export your PKs for the rest of the world. You can have non-predictable data outside of your PK. Yes, a different column will still have some of the problems with index maintenance, but it becomes a much smaller problem if only one table cares about the value.
    • nine_k 3y ago
      You can almost always opt for artificial PKs that are more performant. But sometimes you have to make these keys public, e.g. as user or other resource IDs. You want to make them UUIDs so they won't be predictable. Not having to join everywhere with the UUID-to-artificial-PK table may be a bigger performance win than the losses from larger size of UUIDs. Sometimes you have a distributed / sharded system, and don't want the keys to clash, and also avoid assigning ranges. Sometimes you have to accept someone else's ID, not originating in your system. In cases like that, large random numbers, e.g. UUID v4, work reasonably well. Of course when you just have one DB, and a relative slow stream of new rows, it's easy to fully control PK creation. And this covers the majority of practical cases.