Unexpected downsides of UUID keys in PostgreSQL

Re:Unexpected downsides of UUID keys in PostgreSQL by jongjong on Thursday June 22, 2023 @09:42AM (#36432029)

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.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by coredog64 on Thursday June 22, 2023 @10:05AM (#36432380)

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.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by egeozcan on Thursday June 22, 2023 @10:35AM (#36432777)

Hi/Lo algorithm also works fine for most cases

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by viraptor on Thursday June 22, 2023 @10:13AM (#36432470)

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.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by ilyt on Thursday June 22, 2023 @12:04PM (#36434290)

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)

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by catlifeonmars on Monday July 03, 2023 @02:22PM (#36576921)

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.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by pgaddict on Thursday June 22, 2023 @01:30PM (#36435715)

I respectfully disagree with the notion that sharding makes resource usage somehow less important. Sure, it allows you to overcome the limits that would apply to a single node, but if you stop caring about using resources efficiently (e.g. memory / disk / network bandwidth), it's not very different from building huge and expensive single boxes.

Also, I don't think the blog (or me) suggested going back to using autoincrement IDs. There are other (better) options.

[ Parent | on HN ]