I don't see much discussion of the issue here - I get the impression Pg doesn't see heavy use in sharded environments. MySQL folks seem to mostly favour the modulo primary key generation approach. I know that the MS-SQL crowd at least strongly prefer UUIDs, but they have very strong in-database UUID support.
I have no firsthand experience with any of these approaches so I can't offer you a considered opinion. The composite primary key (serverid,sequenceid) approach avoids the need for a pre-defined maximum number of servers, but can be slow to index and can require more storage, especially because of tuple headers. In really busy systems it also limits the total amount of primary key space - but with BIGINT primary keys, that's unlikely to be something you need to worry about. That works great until you need more than 100 instances, at which point you're really, REALLY boned. , server 2 will generate primary keys 002, 102, 202, 302. That way, if n=100, server 1 will generate primary keys 001, 101, 201, 301. Every key generation sequence increments by "n" whenever it generates a key, with an offset of the server/database id. The other way people seem to do this is by assigning a unique instance id to each server/database out of a maximum "n" instances decided at setup time. Overall, UUIDs seem to be a favoured approach. Foreign key relationships may be slower too. Doesn't set any pre-determined limit on how many servers/databases may be in a cluster.Ĭon: Slower than modulo key generation approach, uses more storage. PostgreSQL includes one function to generate a UUID: genrandomuuid. Pro: No need for (serverid,serverseq) pair primary keys or hacks with modulus based key generation. Second, the Primary Key column usually has an associated B+Tree index to speed up. It would be extremely helpful if someone could help me figure this out, as it is critical for my project. Hence, the need for a unique key across all databases. My requirement states that UUID would be perfect in my case as I will be having many small databases which will link up to a global database using the UUID. In other words, the only advantage from GUID PK is ability to generate it at client side. > I am interested in finding out the pros/cons of using UUID as a primary key field. GUID primary keys are usually required, when you need meaningful primary keys before inserting data in database (e.g., there are client apps, that later synchronize data with main database). Subject: Re: UUID performance as primary keyĬc: pgsql-performance(at)postgresql(dot)org
Wouldn't UUID PK cause a significant drop in insert performance because every insert is now out of order, which leads to a constant re-arranging of the B+ tree? The amount of random IO's that's going to generate would just kill the performance.
0 kommentar(er)
