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

161–170 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#161

Post by mixmastamyk »

GGO wrote: post #32
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…

I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, but why do people keep recommending it over UUIDv7 now beats me.

You're saving storage space but potentially leaking details. Is that ok for your application? No one can answer but your org.

Re: PostgreSQL and UUID as Primary Key

#162

Post by inopinatus »

brycelarkin wrote: post #55
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…

For postgres, you want to use “bigint generated always as identity” instead of bigserial.

I agree and apologise for writing bigserial out of ossified habit. Identity columns hew to the SQL standard, bigserial is a pg-ism. I actually do still use it when the ORM’s DDL generator prefers as much (looking at you Rails), there’s no gain from fighting the middleware.

For other readers: the two are almost the same in behaviour, they differ primarily in that identity columns don’t need a USAGE grant on their associated sequence, and the ALWAYS makes it harder (but despite the term not actually impossible) to override the default value on row insert.

Re: PostgreSQL and UUID as Primary Key

#163

Post by mewpmewp2 »

sgarland wrote: post #116
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…

Milliseconds matter, especially when they compound. If your DB can return a SELECT in sub-msec time (to its network boundary, obviously) instead of 10 msec, that adds up when a given page might require a dozen or more trips. Also, I have never seen devs (PMs, really – devs are the unfortunate souls slogging through tickets) suddenly care about performance-related tech debt. Why would they, when you can just click a b…

But then by default you are leaking potentially business sensitive data with your id if you are using it as public facing, which is unsecure design by default.

I would rather have secure data by default and opt in to optimise when it is clear this info is fine to leak.

Re: PostgreSQL and UUID as Primary Key

#164

Post by bruce511 »

darby_nine wrote: post #134
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…

You really have to understand the usage patterns of a program to give a recommendation. Anyone who doesn't is just a grifter. I don't trust anyone who gives recommendations sight unseen.

100% this. Context is everything, and advice without understanding context should be suspect at best.

That said, I've found that contexts can change. Personally I started with the database being local (send single). It the migrated to remote (web), after that to distributed and from there to mobile.

As contexts gave changed I've found we needed to switch to UUID (and the switch was a pain.) A lot of our problems would simply not exist if we'd used uuid from the start.

So now my "default advice" is uuid, unless there's a -specific- reason not to. For me, performance is not that reason (your database outlives multiple generations of hardware, and faster hardware will dwarf optimisation-via-data-type.)

And don't get me started on natural versus surrogate identifiers- that ship sailed decades ago.)

Re: PostgreSQL and UUID as Primary Key

#166

Post by klooney »

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.

If you expose an API, your customer's CI systems will probably get there eventually.

Re: PostgreSQL and UUID as Primary Key

#167

Post by bruce511 »

sgarland wrote: post #112
lazide wrote: post #92

Earlier quoted context omitted.

The info leak, specifically, is [ https://en.m.wikipedia.org/wiki/German_tank_problem ].

Oh no, someone might know the number of customers, or the rate of signups. Traditional businesses can figure this out by sitting in the parking lot. Why SaaS has decided it’s a huge problem is beyond me.

Yes, sure, it leaks some information - but to be fair printing an invoice also leaks information.

For me the priority is security. If I get a link (visible or invisible) that contains a numeric ID, there's the possibility to tweak that link with another number.

Ideally, the server treats that number as suspect. Every. Single . Time. In practice I only need one developer to miss the check in one place and I have a serious security issue. Like leaking my entire customer list serious. This has happened multiple times -that we know of-.

Yes obscurity is not security. But sequential numbers are insecure by design, and it takes a lot of well-maintained walls to protect them. One crack leaves them very vulnerable.

Re: PostgreSQL and UUID as Primary Key

#168

Post by arp242 »

mewpmewp2 wrote: post #152
arp242 wrote: post #137

Earlier quoted context omitted.

For almost all use cases just showing a UUIDv7 or sequential ID is fine. There are a few exceptions, but it's not the common case.

How would it be fine, e.g. for e commerce which is arguably very large portion of the use cases? You would be immediately leaking how many orders a day your business is getting with sequential id.

> You would be immediately leaking how many orders a day your business is getting with sequential id.

Which is fine for almost all of them. All brick and mortar stores "leak" this too; it's really not that hard to guess number of orders for most businesses, and it's not really a problem for the overwhelming majority.

And "Hi, this is Martin, I'd like to ask a question about order 2bf8aa01-6f4e-42ae-8635-9648f70a9a05" doesn't really work. Neither does "John, did you already pay order 2bf8aa01-6f4e-42ae-8635-9648f70a9a05" or "Alice, isn't 2bf8aa01-6f4e-42ae-8635-9648f70a9a05 the same as what we ordered with 7bb027c3-83ea-481a-bb1e-861be18d21ea?"

Especially for order IDs UUIDs are huge PITA because unlike user IDs and other more "internal" IDs, people can and do want to talk about them. You will need some secondary human-friendly unique ID regardless (possibly obfuscated, if you really want to), and if you have that, then why bother giving UUIDs to people?

Re: PostgreSQL and UUID as Primary Key

#169

Post by riku_iki »

arp242 wrote: post #154
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…

With multiple tables and the same IDs being references more than once, this kind of stuff can really add up. For example I have a table that has about a billion rows and uses bigserial, but that table references about 6 other much smaller tables that use serial. I'm saving 48 bytes per row, or 90GB in total. It's a fairly significant save, and that's just on this one table: I have a bunch of tables like this. If I ha…

> I'm saving 48 bytes per row

you saving 24 bytes per row: downsizing 6 columns from 8 bytes to 4, which is fraction of your table size. If your system is sensitive to such change, you likely should optimize something else.

> Using bigint here would add absolutely nothing. I'm never going to have billions of users. I'm never going to have billions of different operating systems.

I think you cherry picked some fictional examples which you won't combine in one table, and your real entities are different. It adds process safety: you don't need to play mind quizzes to estimate how system can grow/change in a N years.

Re: PostgreSQL and UUID as Primary Key

#170

Post by inopinatus »

dajtxx wrote: post #53
GGO wrote: post #32

Earlier quoted context omitted.

I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, but why do people keep recommending it over UUIDv7 now beats me.

The comment above warns against it due to the embedded timestamp info as a info leak risk. Perhaps that was a problem for them in some circumstance.

It wasn’t a problem for me directly but was observed and related by a colleague: an identifier for an acquired entity embedded the record’s creation timestamp and effectively leaked the date of acquisition despite it being commercial-in-confidence information. Cue post-M&A ruckus at board level.

Just goes to show you can’t inadvertently disclose anything these days.

Post reply on HN