16

I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it).

I've spent hours researching bigserial vs uuid primary keys, and it seems no one can agree on what the disadvantages of uuid would be (if any). My database isn't that complicated: it's a series of tables with pretty basic relationships, I'm typically only inserting one row at a time, I have a handful of jsonb fields I'm using here and there. Write speed/frequency will only be significantly high on one table in particular.

The reason I started looking into UUIDs wasn't because I think I'll ever run out of bigint keys (9 quintillion, if I recall), but more from a standpoint of obfuscation. Right now I'm having to hash the IDs on the front-end to avoid showing the user DB IDs in the URL (e.g. /things/2732). Using Hashids, I can instead have URLs like /things/To2jZP13dG. But I thought I could go a step further and just use UUIDs which don't give any clues as to # of records. What I don't like is the added effort of having to encode IDs before passing them to the back end and decoding them there, and then when querying batches of 50-100 items to return to the client, having to mass encode all those IDs before returning them to the client.

One argument for random UUIDs (uuid v4) was:

If your primary key is an incrementing ID, those are stored physically next to each other. That database page can have contention as many people are writing to it. Random IDs prevent contention by spreading the writes all over the DB

But then I found a contradictory statement here:

Regular random UUIDs are distributed uniformly over the whole range of possible values. This results in poor locality when inserting data into indexes - all index leaf pages are equally likely to be hit, forcing the whole index into memory. With small indexes that's fine, but once the index size exceeds shared buffers (or RAM), the cache hit ratio quickly deteriorates.

I know the folks at Heroku are fans of using UUIDs as primary keys. For whatever it's worth, I wasn't planning on having Postgres auto-generate the IDs at all. Instead, my API server would generate a v4 UUID and pass that to the database (this would allow my API server & front end client to be more efficient and not have to always use RETURNING id statements in my queries).

Does anyone have a canonical answer on the true cost of INSERT statements when using uuid as the primary key?

0

2 Answers 2

14

It is easy enough to benchmark this, but the INSERT performance of UUIDs will be worse, because they are bigger and slower to generate.

But it doesn't sound like you are building a high performance application anyway (then you probably wouldn't be using JSON), so it probably won't make much difference.

Finally, you want to use UUIDs for security reasons (which I won't discuss here). Security always puts a penalty on performance and usability, so consider it as the price you are paying for security.

6
  • When you say "generate" (referring to inserting rows with UUID primary keys), are you talking about the process of storing the actual UUID or indexing it, or generating a default value? I won't be using gen_random_uuid() as a default value. The database will simply store whatever UUID is passed to it in the INSERT statement.
    – ffxsam
    Apr 2, 2021 at 18:55
  • Yes, I was talking about calculating the UUID. Even if you don't do that in the database (which makes sense), it will still take time and CPU. Apr 6, 2021 at 6:07
  • @LaurenzAlbe, Quick check. Since Postgres does not have clustered index on PK fields, meaning that the randomness of UUIDs won't be an issue while insertion (Page Thrashing) because Postgres arrange tables in a heap and inserts just go at the "bottom" without the DB worrying about order, does this mean in PGSQL using autoincremented int the same as UUID? [In MySQL it is not]
    – Mu-Majid
    Jul 17, 2021 at 1:03
  • @Mu-Majid Inserting into the table is always the least of your worries. Inserting into the index is where performance matters. Here, UUIDs will be inserted all over the place, potentially defying caching. Better than theorizing is to run a simple benchmark. then you know if you have to worry. Jul 17, 2021 at 4:32
  • 1
    On my machine it is slower: explain analyze select nextval('xxx_id_seq') from generate_series(1, 10e6);: Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=8 ) (actual time=2252.235..8008.346 rows=10000000 loops=1) Planning time: 0.060 ms Execution time: 8457.624 ms explain analyze select gen_random_uuid() from generate_series(1, 10e6); Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=1 6) (actual time=2209.497..25637.137 rows=10000000 loops=1) Planning time: 0.204 ms Execution time: 26123.776 ms
    – dnk8n
    Dec 8, 2022 at 18:30
5

What Laurenz said is true, but I've actually found a more measurable difference in performance when you try to use those UUID fields in predicates (e.g. in JOIN, WHERE, and HAVING clauses). Again, it'll depend on the amount of data between the tables your predicates are for, but a comparison between a 16 byte value and another 16 byte value is somewhat significantly different than one between a 4 byte and 4 byte value, for example, if the fields were INT data types. But as Laurenz mentioned, that is a valid price to pay for security.

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.