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

121–130 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#121

Post by SoftTalker »

sgarland wrote: post #109
oppositelock wrote: post #6

Earlier quoted context omitted.

Random UUID's are super useful when you have distributed creation of UUID's, because you avoid conflicts with very high probability and don't rely on your DB to generate them for you, and they also leak no information about when or where the UUID was created. Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It does well enough for all practical purposes if you need random…

> Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It literally is the one thing in the entire stack that must always be happy. Every stateful service likely depends on it. Sad DBs means higher latency for everyone, and grumpy DBREs getting paged.

DBRE? I guess DBA is too old fashioned for the cool kids?

Re: PostgreSQL and UUID as Primary Key

#122

Post by sgarland »

big_whack wrote: post #81
sgarland wrote: post #76

Earlier quoted context omitted.

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.

Postgres doesn't necessarily pad to 8 bytes; it depends on the next column's type. EDB has a good writeup on this (https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/), but also here's a small example:

  CREATE TABLE foo
    (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid INT NOT NULL);

  CREATE TABLE bar
    (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid BIGINT NOT NULL);

  CREATE TABLE baz
    (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid BIGINT NOT NULL);

  -- fill each with 1,000,000 rows, then index

  CREATE INDEX {foo,bar,baz}_iid_idx ON {foo,bar,baz}(iid);

  SELECT table_name,
         pg_size_pretty(pg_table_size(quote_ident(table_name))) "table_size",
         pg_size_pretty(pg_indexes_size(quote_ident(table_name))) "index_size"
  FROM information_schema.tables
  WHERE table_schema = 'public';

   table_name | table_size | index_size
  ------------+------------+------------
   foo        | 35 MB      | 43 MB
   bar        | 42 MB      | 43 MB
   baz        | 42 MB      | 43 MB
`foo` has an INT followed by an INT, and its table size is 35 MB. `bar` has an INT followed by a BIGINT, and its table size is 43 MB; this is the same size for `baz`, despite `baz` being a BIGINT followed by a BIGINT.

Re: PostgreSQL and UUID as Primary Key

#123

Post by sgarland »

riku_iki wrote: post #80
sgarland wrote: post #76

Earlier quoted context omitted.

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.

> 4 bytes * 2^31 (because Postgres doesn’t allow have unsigned ints, unlike MySQL) is 8.6 GB I didn't get your point. When it is 2^31, you definitely need bigint already. When it is much smaller, it will be much smaller overhead. Also, per docs ( https://www.postgresql.org/docs/current/storage-page-layout.... ), each postgres row has 23 bytes overhead, so your transition from 8->4 bytes will bring marginal improvemen…

Not all tables need even the capacity for 2^30 rows, much less 2^31, or 2^63. If you have a reference table with things like timezone information, color schemes, etc. and are using anything other than a SMALLINT (2^15), you're probably wasting space.

As to the maximal 8.6 GB mentioned, that's not nothing, _especially_ for RAM. Disk is cheap, but RAM isn't. If you have a smaller instance – say, an r6i.xlarge on AWS (4 vCPU, 32 GB RAM), that's 1/4 of the entire DB's memory.

Re: PostgreSQL and UUID as Primary Key

#124

Post by therealdrag0 »

netcraft wrote: post #17

Another day, another article saying not to use UUIDs as PKs. I've maintained systems using UUIDs stored as char(36) with million record tables without issue - This is not an endorsement, just explaining that this is bikeshedding. Should you use v7 when you can? Sure. Would int/bigint be faster in your benchmarks? Sure. But the benefits totally outweigh the speed differences until you get to a very large system. But i…

Million records isn’t very many. But ya we have tables with billions of records and v4 UUIDs hasn’t been a blocker.

Re: PostgreSQL and UUID as Primary Key

#125

Post by sgarland »

SoftTalker wrote: post #121
sgarland wrote: post #109

Earlier quoted context omitted.

> Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It literally is the one thing in the entire stack that must always be happy. Every stateful service likely depends on it. Sad DBs means higher latency for everyone, and grumpy DBREs getting paged.

DBRE? I guess DBA is too old fashioned for the cool kids?

Listen, I didn't make the title up, I just grabbed onto it from the SRE world because I love databases.

There are some pragmatic differences I've found, though - generally, DBAs are less focused on things like IaC (though I know at least one who does), SLIs/SLOs, CI/CD, and the other things often associated with SRE. So DBRE is SRE + DBA, or a DB-focused SRE, if you'd rather.

Re: PostgreSQL and UUID as Primary Key

#126

Post by sarreph »

We chose ULID for our Postgres PK recently, and this article helped a lot in making that decision: https://brandur.org/nanoglyphs/026-ids

I personally prefer ULID since it is compat with a UUID type and you also get a timestamp lexicographically built into the ID so that sorting by ID also means sorting by timestamp. There are multiple PG extensions to make it easy to drop in and use.

Re: PostgreSQL and UUID as Primary Key

#127

Post by riku_iki »

sgarland wrote: post #123
riku_iki wrote: post #80

Earlier quoted context omitted.

> 4 bytes * 2^31 (because Postgres doesn’t allow have unsigned ints, unlike MySQL) is 8.6 GB I didn't get your point. When it is 2^31, you definitely need bigint already. When it is much smaller, it will be much smaller overhead. Also, per docs ( https://www.postgresql.org/docs/current/storage-page-layout.... ), each postgres row has 23 bytes overhead, so your transition from 8->4 bytes will bring marginal improvemen…

Not all tables need even the capacity for 2^30 rows, much less 2^31, or 2^63. If you have a reference table with things like timezone information, color schemes, etc. and are using anything other than a SMALLINT (2^15), you're probably wasting space. As to the maximal 8.6 GB mentioned, that's not nothing, _especially_ for RAM. Disk is cheap, but RAM isn't. If you have a smaller instance – say, an r6i.xlarge on AWS (4…

so, what about my argument that PG has 23 bytes overhead per row and your space win is very small compared to that overhead?

Re: PostgreSQL and UUID as Primary Key

#128

Post by njtransit »

incrudible wrote: post #43
njtransit wrote: post #34

Earlier quoted context omitted.

Using 32 bit ints for IDs is insane in today’s world. If an attacker can control record generation, e.g. creating a record via API, then they can easily exhaust your ID space. A lot of kernel vulnerabilities stem from using incrementing 32 bit integers as an identifier. If you’re considering using 32 bits for an ID, don’t do it!

If an attacker can create billions of records through your API, maybe that is a problem you need to address either way.

It’s about 100 records per second for a year and a half, or 10,000 records per second for 5 days. Both are easily achievable. As an engineer, why would you ever knowingly design such a system when it’s trivial to not have this vulnerability in the first place.

It’s like hosting an internal app at a company that contains a SQL injection. “Well, if a hacker can access this app, then that’s a problem that needs addressing either way.” Sure, that may be true, but it’s also true that you’re not a good software engineer.

Re: PostgreSQL and UUID as Primary Key

#129

Post by TeeWEE »

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…

If you have distributed data creation. (Creating data on the client). And a CRDT style mechanism for syncing, then you can’t use bigserial because of the simple fact that it is sequential. The best solution here is uuidv7. Since you can generate these at the client even when offline.

Re: PostgreSQL and UUID as Primary Key

#130

Post by saaspirant »

sarreph wrote: post #126

We chose ULID for our Postgres PK recently, and this article helped a lot in making that decision: https://brandur.org/nanoglyphs/026-ids I personally prefer ULID since it is compat with a UUID type and you also get a timestamp lexicographically built into the ID so that sorting by ID also means sorting by timestamp. There are multiple PG extensions to make it easy to drop in and use.

How do you deal with ulid exposing the timestamp (since is lexicographically sortable) ? Maybe your ULID is not public facing? Or this is not an issue for your application?

I want to use something url friendly too since uuid sucks..

Post reply on HN