PostgreSQL and UUID as Primary Key • Hacker News Board

Re: PostgreSQL and UUID as Primary Key

#251

Post by hu3 » Sat, Jul 06, 2024, 1:46 PM UTC

GiorgioG wrote: post #240

UUIDs are miserable to work with. For 99% of use cases, sequential IDs are fine. Can they be guessed? Sure, but your software should guard against unauthorized access. Security through obscurity or randomness is a poor excuse for using UUIDs as PKs. If you don't want to expose your IDs, use a slug.

Agreed.

The dev experience of debugging with UUIDs involved degrades so much, it is depressing.

For example it's much harder to spot patterns and wrong IDs in SQL query results if you are looking at these giant blobs of random characters called UUIDs.

Re: PostgreSQL and UUID as Primary Key

#252

Post by groestl » Sat, Jul 06, 2024, 1:48 PM UTC

kroolik wrote: post #229

groestl wrote: post #228

Earlier quoted context omitted.

Ha? Please elaborate.

When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range

Okay, but in a live DB, typically you won't have only inserts while migrating, won't you?

Re: PostgreSQL and UUID as Primary Key

#253

Post by sgarland » Sat, Jul 06, 2024, 1:48 PM UTC

riku_iki wrote: post #127

sgarland wrote: post #123

Earlier quoted context omitted.

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?

It’s orthogonal, and also technically incorrect – the row overhead itself is indeed 23 bytes (less NULL bitmap), but it gets aligned to 24 bytes. Run pg_column_size(ROW()) if you’d like to check.

The fact that this overhead exists has nothing to do with the 8.6 GB of wasted space on the INTs.

Re: PostgreSQL and UUID as Primary Key

#254

Post by [deleted] » Sat, Jul 06, 2024, 1:49 PM UTC

asah wrote: post #236

kroolik wrote: post #229

Earlier quoted context omitted.

When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range

would creation/lastmod timestamps cover this requirement?

[deleted]

Re: PostgreSQL and UUID as Primary Key

#255

Post by hu3 » Sat, Jul 06, 2024, 1:49 PM UTC

laserDinosaur wrote: post #247

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.

>Call me old fashion but I really like integer autoincrement primary keys. Just hope you never have to merge tables from two databases together.

This is exceptionally rare in most projects.

I know of only one person in my entire career that had to do this. And they managed it just fine despite working with auto-incrementing big ints.

Yet some folks advocate that all projects should pay an expensive insurance against this elusive event of two databases being merged.

Re: PostgreSQL and UUID as Primary Key

#256

Post by sgarland » Sat, Jul 06, 2024, 1:51 PM UTC

nirvdrum wrote: post #180

riku_iki wrote: post #127

Earlier quoted context omitted.

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

Is that an innate property or a current implementation detail?

It’s the current implementation as of PG 8.2, I think. It’s [0] been there for a long time, in any case, and is unlikely to change in the near future.

[0]: https://www.postgresql.org/docs/current/storage-page-layout....

Re: PostgreSQL and UUID as Primary Key

#257

Post by valenterry » Sat, Jul 06, 2024, 1:55 PM UTC

blackenedgem wrote: post #250

kroolik wrote: post #229

Earlier quoted context omitted.

When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range

It's not even necessarily it being strictly monotonic. That part does help though as you don't need to skip rows. For me the bigger thing is the randomness. A uid being random for a given row means the opposite is true; any given index entry points to a completely random heap entry. When backfilling this leads to massive write amplification. Consider a table with rows taking up 40 bytes, so roughly 200 entries per pa…

Isn't that solved because UUIDv7 can be ordered by time?

Re: PostgreSQL and UUID as Primary Key

#258

Post by Koffiepoeder » Sat, Jul 06, 2024, 1:57 PM UTC

foreigner wrote: post #203

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.

Be advised using the last auto-incremented value as a "high water mark" and getting everything greater than that is not 100% reliable, because in some scenarios auto-incrementing values can be written out of order.

Yup, not many people seem to know about CACHE vals and/or sequence reservation. It's a problem that I've had to explain a couple of times already to colleagues. And unfortunately hidden assumptions are oft the most dangerous kind.

Re: PostgreSQL and UUID as Primary Key

#259

Post by groestl » Sat, Jul 06, 2024, 2:01 PM UTC

stouset wrote: post #97

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…

UUIDv7 are good enough that you can just avoid thinking and use it as a “default”. The worst consequences of doing so are some slightly impacted performance and leak of some timing information but these are extremely minor drawbacks. Using completely random UUIDs is IMO the worst choice. It’s fine right up until it isn’t, and then you are stuck in hell with no good way out.

> Using completely random UUIDs is IMO the worst choice

Can't say that without context. I've worked in systems where even the version bits were randomized, and for good reason (i know, technically, no UUID anymore).

Re: PostgreSQL and UUID as Primary Key

#260

Post by laserDinosaur » Sat, Jul 06, 2024, 2:08 PM UTC

hu3 wrote: post #255

laserDinosaur wrote: post #247

Earlier quoted context omitted.

>Call me old fashion but I really like integer autoincrement primary keys. Just hope you never have to merge tables from two databases together.

This is exceptionally rare in most projects. I know of only one person in my entire career that had to do this. And they managed it just fine despite working with auto-incrementing big ints. Yet some folks advocate that all projects should pay an expensive insurance against this elusive event of two databases being merged.

>And they managed it just fine despite working with auto-incrementing big ints.

I wonder how. I've had to do several big merges in my career, and it was always a nightmare because of all the external systems which were already referencing and storing those pre-existing ints. Sure, merging the databases is easy if you don't mind regenerating all the Id's, but it's not usually that simple.