Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL and UUID as Primary Key

maciejwalkowiak.com

Post reply on HN
Print view View original

81–90 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#81

Post by big_whack »

sgarland wrote: post #76
riku_iki wrote: post #28

Earlier quoted context omitted.

there is unlikely significant performance degradation for int vs big int, but it will be huge PITA, if 10 years later and tons of legacy code written that table will grow over 4B rows..

It’s double the size. 4 bytes * 2^31 (because Postgres doesn’t allow have unsigned ints, unlike MySQL) is 8.6 GB. That is quite a difference for an index, not to mention the table overhead. You’re going to know well in advance before hitting this limit becomes an issue, and you’ll have plenty of time to either take a bit of downtime and do a column conversion, or do an online migration.

Postgres pads tuples to 8 bytes alignment so an indexed single-column int takes the same space as an indexed bigint. That's the usual case for indexed foreign keys.

Differences can appear in multicolumn indexes because two ints takes 8 bytes while two bigints takes 16, however the right layout of columns for an index is not always the layout that minimizes padding.

Re: PostgreSQL and UUID as Primary Key

#82

Post by afavour »

DEADMINCE wrote: post #68
afavour wrote: post #64

Earlier quoted context omitted.

Conversely: if using bigserial for a primary key introduces crippling performance problems to your system, maybe that is a problem you need to address either way.

Conversely? Who mentioned there being a performance impact?

It’s the only reason I could think of for why you wouldn’t use a bigserial column. Maybe there is another?

Re: PostgreSQL and UUID as Primary Key

#83

Post by vbezhenar »

DEADMINCE wrote: post #69
vbezhenar wrote: post #40

Earlier quoted context omitted.

What architecture? Both amd64 and ARM64 can work with 32-bit integers just fine.

Not optimally.

It is not optimal to use 8-byte integers instead of 4-bytes.

CPU works just as fast with both, however your CPU cache is limited and you'll put more 4-byte integers into your L1.

I don't really understand what you want to convey. CPU is very fast with any kind of integer size. There's no performance penalty to use 1-byte integer compared to 8-byte integer. And there's performance penalty when your L1 or L2 or L3 cache is busted and you need to go to the next memory layer.

Re: PostgreSQL and UUID as Primary Key

#84

Post by [deleted] »

bozey07 wrote: post #74
eproxus wrote: post #67

Earlier quoted context omitted.

There’s no need since they are random, so you might as well generate them on your application servers which are easier to scale, to offload the write database.

IMO marking the row as generated makes its intent clearer. It also means less code - no generating UUIDs, shorter queries = more robust. For my workloads at least, I think it's worth the tradeoff of forcing Postgres to muster 15 random bytes.

[deleted]

Re: PostgreSQL and UUID as Primary Key

#85

Post by sgarland »

fabian2k wrote: post #3

My somewhat naive understanding was that random UUIDs were not that big of a deal in Postgres because it does not cluster by primary key. And of course a UUID (16 bytes) is larger than a serial (4 bytes) or bigserial (8 bytes) by a factor of 2-4 . This certainly might matter for an index, but on a whole table level where you have 20+ bytes overhead per row this doesn't seem that big of a deal for anything except very…

You still get performance hits from Visibility Map lookups, and WAL bloat.

As a DBRE, I believe it always matters, and you should invest time in it. Pragmatically, it is unlikely to have noticeable effects until your tables are at least in the 1E5 rows range, if not higher. Unfortunately, by that point, it’s likely that you’ll find other things of higher importance to deal with, so the problem will only grow.

Re: PostgreSQL and UUID as Primary Key

#86

Post by vbezhenar »

solidsnack9000 wrote: post #52
inopinatus wrote: post #12

The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, esp…

One challenge with PNR is actually restricting the alphabet appropriately. They sure are easy to say aloud -- just five or six letters in many cases -- but how do you ensure you have (a) enough letters to get a reasonable bitwidth and (b) not form ridiculous words?

Do you ensure that your software does not form ridiculous numbers? Imagine that some christian person gets "666" number. What a scandal.

Do you ensure that your software does not form ridiculous words in every language? Or just another US-centric thing?

The idea of avoiding identifiers to be ridiculous is ridiculous to me, honestly...

Re: PostgreSQL and UUID as Primary Key

#87

Post by tommy_axle »

declan_roberts wrote: post #61

Call me old fashion but I really like integer autoincrement primary keys. It's easy to understand and obviously simple to sort. Furthermore when working on large batch projects you can just store the last primary key as your high water mark and get everything greater than that. I suppose TSID works to this end, but certainly more complicated.

It is simpler but like everything else it depends on the application. For a private app you can generally get away with it. Something that's more public facing? The ID will most likely leak information. As systems mature and you introduce things like replication, having IDs that are more universal starts looking good. In general, starting off with a uuid like uuid v4 or nanoid is a good bet.

Re: PostgreSQL and UUID as Primary Key

#88

Post by lazide »

afavour wrote: post #82
DEADMINCE wrote: post #68

Earlier quoted context omitted.

Conversely? Who mentioned there being a performance impact?

It’s the only reason I could think of for why you wouldn’t use a bigserial column. Maybe there is another?

Information leakage - it leaks how many records have been created [https://en.m.wikipedia.org/wiki/German_tank_problem]

Re: PostgreSQL and UUID as Primary Key

#89

Post by marcosdumay »

vbezhenar wrote: post #83
DEADMINCE wrote: post #69

Earlier quoted context omitted.

Not optimally.

It is not optimal to use 8-byte integers instead of 4-bytes. CPU works just as fast with both, however your CPU cache is limited and you'll put more 4-byte integers into your L1. I don't really understand what you want to convey. CPU is very fast with any kind of integer size. There's no performance penalty to use 1-byte integer compared to 8-byte integer. And there's performance penalty when your L1 or L2 or L3 cach…

> CPU works just as fast with both

They don't. CPUs have as many issues with data alignment as with cache sizes.

Post reply on HN