Unexpected downsides of UUID keys in PostgreSQL

Post by uppiiii765 » Thu, Jun 22, 2023, 3:52 PM UTC

pgaddict wrote: post #54

jongjong wrote: post #20

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... Ho…

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, b…

Post by ilyt » Thu, Jun 22, 2023, 4:02 PM UTC

xahrepap wrote: post #141

unshavedyak wrote: post #123

Earlier quoted context omitted.

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…

ULIDs have made our MySql caches breathe a sigh of relief. One place we're avoiding ULIDs (and other counters) is in publicly-facing IDs. Preferring random to help keep them unguessable. (say what you will about security-through-obscurity). So we do ULIDs for private IDs. Random UUIDs for public IDs. Seems to work well.

Post by ilyt » Thu, Jun 22, 2023, 4:04 PM UTC

coredog64 wrote: post #103

jongjong wrote: post #95

Earlier quoted context omitted.

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 U…

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.

    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)

Post by [deleted] » Thu, Jun 22, 2023, 4:13 PM UTC

Timpy wrote: post #71

For me, the visual noise is a downside for UUIDs. A lot of time investigating data issues means glancing at query results and deciding if something looks unexpected. I just can't parse a UUID with my eyes that quick. I've come up against this at my current job a little too often and I'm cursing the decision to switch to UUIDs. I know, it's a balancing act of competing concerns. But for us moving to UUIDs was future p…

Post by arp242 » Thu, Jun 22, 2023, 4:26 PM UTC

michaelsalim wrote: post #78

Timpy wrote: post #71

For me, the visual noise is a downside for UUIDs. A lot of time investigating data issues means glancing at query results and deciding if something looks unexpected. I just can't parse a UUID with my eyes that quick. I've come up against this at my current job a little too often and I'm cursing the decision to switch to UUIDs. I know, it's a balancing act of competing concerns. But for us moving to UUIDs was future p…

Interesting take. I do agree there's some visual noise there, but not how you described it. UUID just takes so much screen space compared to its integer counterpart. If you don't have a good UI sorting things out it's quite annoying. Otherwise it's pretty great.