News for hackers. Stuff that matters.
Re:PostgreSQL and UUID as Primary Key by fabian2k on Friday July 05, 2024 @04:27PM (#40885700)
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 narrow tables with large row counts.

So far my impression is that there are a whole lot of other things I need to worry about in Postgres before I spend time considering serial vs. random UUID vs. ordered UUID. Am I wrong here and this is something that really matters and you should invest more time in?

Re:PostgreSQL and UUID as Primary Key by oppositelock on Friday July 05, 2024 @04:40PM (#40885808)
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 randomness.

Re:PostgreSQL and UUID as Primary Key by Spivak on Friday July 05, 2024 @08:08PM (#40887181)
> 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

See Snowflake IDs for a scheme that gives you the benefit of random UUIDs but are strictly increasing. Which is really UUIDv7 but fits in your bigint column. No entropy required.

Re:PostgreSQL and UUID as Primary Key by sgarland on Friday July 05, 2024 @09:51PM (#40887645)
> 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.

Re:PostgreSQL and UUID as Primary Key by SoftTalker on Friday July 05, 2024 @10:51PM (#40887854)
DBRE? I guess DBA is too old fashioned for the cool kids?
Re:PostgreSQL and UUID as Primary Key by sgarland on Friday July 05, 2024 @11:03PM (#40887899)
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 by matharmin on Saturday July 06, 2024 @05:28AM (#40889242)
Postgres is usually completely happy enough with UUIDv4. Overall architecture (such as allowing distributed id generation, if relevant) is more important than squeezing out that last bit of performance, especially for the majority of web applications who don't work with 10 million+ rows.
Re:PostgreSQL and UUID as Primary Key by sgarland on Saturday July 06, 2024 @09:32AM (#40890364)
If your app isn’t working with billions of rows, you really don’t need to be worrying about distributed anything. Even then, I’d be suspicious.

I don’t think people grasp how far a single RDBMS server can take you. Hundreds of thousands of queries per second are well in reach of a well-configured MySQL or Postgres instance on modern hardware. This also has the terrific benefit of making reasoning about state and transactions much, much simpler.

Re: last bit of performance, it’s more than that. If you’re using Aurora, where you pay for every disk op, using UUIDv4 as PK in Postgres will approximately 7x your IOPS for SELECTs using them, and massively (I can’t quantify it on a general basis; it depends on the rest of the table, and your workload split) increase them for writes. That’s not free. On RDS, where you pay for disk performance upfront, you’re cutting into your available performance.

About the only place it effectively doesn’t matter except at insane scale is on native NVMe drives. If you saturate IOPS for one of those without first saturating the NIC, I would love to see your schema and queries.

Re:PostgreSQL and UUID as Primary Key by fovc on Saturday July 06, 2024 @09:39AM (#40890392)
Scale isn’t the only reason to have distributed systems. You could very well have a tiny but distributed system
Re:PostgreSQL and UUID as Primary Key by wibblewobble125 on Saturday July 06, 2024 @10:09AM (#40890579)
Sometimes distribution is not for performance but tenant isolation for regulatory or general isolation purposes. I work in such an industry.
Re:PostgreSQL and UUID as Primary Key by sgarland on Saturday July 06, 2024 @10:21AM (#40890629)
Fair point. You can still use monotonic IDs with these, via either interleaving chunks to each DB, or with a central server that allocates them – the latter approach is how Slack handles it, for example.
Re:PostgreSQL and UUID as Primary Key by globular-toast on Saturday July 06, 2024 @05:06AM (#40889157)
The whole point of UUID is distributed creation. There's nothing about random ones (UUIDv4) that makes it better for this purpose.
Re:PostgreSQL and UUID as Primary Key by kevin_nisbet on Friday July 05, 2024 @05:01PM (#40885976)
> So far my impression is that there are a whole lot of other things I need to worry about in Postgres before I spend time considering serial vs. random UUID vs. ordered UUID. Am I wrong here and this is something that really matters and you should invest more time in?

Like any sort of optimization I believe this will depend on your workload and what's important.

For me when I switched to UUIDv7 a few months ago, it was basically no effort to switch v4 to v7 on a relatively new system. I was observing much higher batch insertion latencies than I expected, and producing inserts that touch less of the btree on an index created a very noticeable reduction in insertion latencies. But my workloads and latencies may look nothing like yours. On amazon RDS instances with EBS volumes and relatively low memory, insertion latency stood out, so using strategies that reduce the number of disk blocks that are needed has an outsized performance impact.

This of course would produce different results on different hardware / system sizing.

Re:PostgreSQL and UUID as Primary Key by munk-a on Friday July 05, 2024 @05:20PM (#40886113)
To:

> Am I wrong here and this is something that really matters and you should invest more time in?

Specifically, no - you don't need to worry about it. Reconfiguring your tables to use a different style of unique identifier if your tables have a unique identifier is a bit of a pain but no more so than any other instance of renaming a column - if you want to minimize downtime you add the new column, migrate data to the column, deploy code that utilizes the new column and then finally retire the old column. Even if the previous version of the table lacked any sort of unique key it is still possible to add one after the fact (it's a bit technically harder to properly keep them in sync but it is possible to do safely).

It's just a question of the cost of doing so and the benefits of it - I work in a system that exclusively uses integral keys and our data is such that we don't really suffer any downsides from that choice - if you're working in a larger system with less confidence in the security practices of other teams then avoiding sequential keys so that you have obscurity to fall back on if someone really drops the ball on real security isn't the worst idea... but I think the really compelling reason to prefer UUIDs is for the power of distributed generation... that really only applies to inherently decentralized or astoundingly large products though - and if your product eventually grows to astoundingly large you'll have plenty of time to switch first (probably the wake-up call will be closing in on running out of 4 bit serial unique keys).

Re:PostgreSQL and UUID as Primary Key by big_whack on Friday July 05, 2024 @05:52PM (#40886366)
Reconfiguring tables to use a different kind of unique ID (primary key in this context) can be a much bigger pain than an ordinary column rename if it is in use by foreign key constraints.
Re:PostgreSQL and UUID as Primary Key by snicker7 on Friday July 05, 2024 @10:43PM (#40887826)
OR if the primary key is exported out of the DB, i.e. for constructing URLs.
Re:PostgreSQL and UUID as Primary Key by masklinn on Friday July 05, 2024 @04:35PM (#40885761)
While Postgres does not cluster table, uuids do affect indexes significantly, random insertion causes increased index slack which translates to cache bloat and thus longer traversal and lower cache residency.
Re:PostgreSQL and UUID as Primary Key by sgarland on Friday July 05, 2024 @08:31PM (#40887276)
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 by stouset on Friday July 05, 2024 @09:18PM (#40887504)
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.

Re:PostgreSQL and UUID as Primary Key by groestl on Saturday July 06, 2024 @10:01AM (#40890531)
> 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 by stouset on Saturday July 06, 2024 @10:33AM (#40890677)
I’m talking about default choices. UUIDv7 has few significant downsides for most cases. UUIDv4 has serious downsides. There are obviously cases where the latter is appropriate but that will come from unique requirements.
Re:PostgreSQL and UUID as Primary Key by groestl on Saturday July 06, 2024 @10:55AM (#40890794)
How about secure by default?
Re:PostgreSQL and UUID as Primary Key by stouset on Sunday July 07, 2024 @04:41PM (#40900312)
“Secure” is only meaningful against a defined threat model.

Most threat models for database IDs do not require their creation timestamp to be secret. Meanwhile every use case for database IDs requires them to be looked up in an index.

Re:PostgreSQL and UUID as Primary Key by groestl on Wednesday July 10, 2024 @04:05AM (#40924693)
I've run multiple info-exfiltrating attacks based on timestamps and sequential identifiers against various actors. There you have your threat model. While I don't have any clue about your "most", I suggest you don't ignore this vector for your opsec.
Re:PostgreSQL and UUID as Primary Key by feydaykyn on Saturday July 06, 2024 @05:13AM (#40889189)
A dev at $WORK thought it wasn't a big deal too because they "tested" on their unrestricted dev setup with 3 rows in the table.

Of course production crashed when their latest sql query did a multijoin on the real table with a few millions of rows. The size of the uuid needed to join filled the available RAM, everything slowed to a crawl and the system collapsed.

The uuid as primary key can be seen as a risk multiplicator : it will amplify any performance issue you may have, converting a temporary slowness into a full system stop.

Re:PostgreSQL and UUID as Primary Key by nextaccountic on Saturday July 06, 2024 @08:31AM (#40890053)
Postgres doesn't automatically reorder rows by clustering on a primary key but you can perform clustering manually, and if the table isn't completely filled it can attempt to maintain rows in order (as long as there is room for it)

https://www.postgresql.org/docs/current/sql-cluster.html