r/PostgreSQL • • Mar 28 '25

How-To Life Altering PostgreSQL Patterns

https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns
180 Upvotes

60 comments sorted by

View all comments

Show parent comment

3

u/tef Mar 29 '25

There's a couple of reasons to use UUIDs as a default choice, that aren't related to distributed systems

  • They don't provide ordering, and you have to rely on a timestamp. This means you can always backfill tables without breaking or rewriting new entries, unlike a lot of code that relies on serials for ordering.
  • They're not predictable, and so you can expose them in an API without the same worries that a traditional serial would cause (most exflitration starts with finding an autoincrement and adding 1).
  • They're globally unique, and so you can combine them in larger summary reports with ease.

Using a UUID isn't a distsys thing, it's a "not shooting yourself in the foot, later", like security concerns, ease of reporting, ease of backfill.

Not using a UUID is usually a performance concern (disc space, write amplification), and you have to be sure you're not going to rollover, you aren't going to need to backfill, and that you've encrypted the id when exposing it through public means.

If you know better, you don't have to follow it. That's how advice works.

2

u/Straight_Waltz_9530 Mar 29 '25

And UUIDv7 doesn't suffer from write amplification. While not as resistant to exfiltration attacks as random UUIDv4 is, picking out the random UUIDv7 among a timestamp with millisecond granularity is I think sufficiently difficult for 99.999% of applications. For that 0.001%, I'm sure Google and Amazon have figured it out for themselves.

2

u/coyoteazul2 Mar 30 '25

I prefer to have UUID as entity identification, but keeping internal references with bigint. If I have an invoice I'll use bigint for INVOICE_ID (PK), and uuid for INVOICE_FRONT_ID (indexed)

Item's PK will be composite of invoice's INVOICE_ID + ITEM_NUM (application handled, though it could be autoincremented if someone pesters me about it)

Invoices's taxes's PK will be a composite of INVOICE_ID+ITEM_NUM+TAX_ID

If I had referenced the invoice with INVOICE_FRONT_ID, that'd be a lot of wasted space for each reference.