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

141–150 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#141

Post by necovek »

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've never played enough with UUIDs in Postgres, but I wonder if you could publicly expose only the random bits (i.e. the second half if an UUIDv7) and have another non-b-tree index on SUBSTR(id, 16) for quick lookups based on that value. Similar is done for "short commit hashes" too.

Though I would wonder why go with UUIDs in that case at all?

Re: PostgreSQL and UUID as Primary Key

#142

Post by openthc »

rjurney wrote: post #138

I've started using python-ulid as ULIDs seem superior to UUIDs. https://pypi.org/project/python-ulid/

I love ULID too; but it's really just UUIDv7 (or v8) with a mustache (it's all just 128bit IDs). And in the PG world, where there isn't native support for ULID one can use UUIDv7/8 and bridge that gap. We use ULID extensively in exposed IDs, it's very URL friendly, copy/paste friendly, etc -- but it's a UUID datatype in the DB. (ie: ULID => UUID => DB)

Re: PostgreSQL and UUID as Primary Key

#143

Post by big_whack »

sgarland wrote: post #122
big_whack wrote: post #81

Earlier quoted context omitted.

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…

You seem to think you're disagreeing with me but afaict you're just demonstrating my point, unless your point is just about how (int, int) will get packed. That's what I meant about the column order of indexes. If you have two ints and a bigint, but you need to index it like (int, bigint, int), then you aren't gaining anything there either.

As your example shows, there is no benefit in index size (e.g for supporting FKs) in going from int to bigint for a single key. You end up with the same index size no matter what, not twice the size which was what I took your original post to mean.

Re: PostgreSQL and UUID as Primary Key

#144

Post by bfung »

TeeWEE wrote: post #132
jpollock wrote: post #115

Earlier quoted context omitted.

Each object has an external key and an internal key. This separation allows you to migrate to other layouts, technologies, etc. without breaking your customer's links or records. Internally, your database looks like: User ID - uint128 external_id - UUID (of some sort) name - string Post ID - uint128 UserId - uint128 (User.ID) external_id - UUID ... Then you have secondary indices on the external_id columns in both ta…

So it can’t use the internal id index, result: slow lookups for external ids.

> secondary indices on the external_id columns in both tables

pick your poison, slower lookup or more disk usage

Re: PostgreSQL and UUID as Primary Key

#145

Post by azinman2 »

arp242 wrote: post #136
TeeWEE wrote: post #129

Earlier quoted context omitted.

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.

That's not true, you can increment by 2, 10, 100, or any number. I'm not saying that's necessarily the best solution, but it's not true that you can't use it.

But if you’re distributed or offline incrementing by an arbitrary amount can still create collisions unless you’re willing to increment by very very large amounts at random, in which case you’ve effectively reinvented uuid

Re: PostgreSQL and UUID as Primary Key

#146

Post by sbuttgereit »

jrochkind1 wrote: post #98
sbuttgereit wrote: post #46

The article had a link to the PostgreSQL commitfest for UUIDv7 support, but as far as I can't tell it looks unlikely that it will actually be in PostgreSQL 17. The most recent action was the committer being removed from the task and I believe version 17 is already well past feature freeze.... Is my understanding correct? This is what I think is going on, but I can't find any substantiated facts too point me to a defi…

That is too bad, I too would be very eager to see natively supported in Postgres. It doesn't seem like it should be so complex to implement on top of pg's existing uuid support, but famous last words?

It looks like the feature had made good progress with the contention really being around the fact that the standard hadn't yet become an RFC. However, the new UUID standard did reach that milestone back in early May, but the feature kinda went radio silent... at least insofar as any public record... up to a couple days ago when the assigned reviewer/committer was removed.

But no word on if it's delayed, or there's some problem, etc. It's still tagged as 17... and some of the related work has been committed, but not the UUIDv7 itself so it seems.

Re: PostgreSQL and UUID as Primary Key

#147

Post by lazide »

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.

By sitting in every parking lot, yes. Which requires physical presence. And hedge funds do indeed do it against some targets specifically as a leg up, and pay quite a bit of money to do so, presumably because it is worth it to them.

It certainly helped the Allies in the war, as previous intelligence had the rate of tank production much higher, and they were expending a lot of effort trying to exceed that previous false number.

Information is power, etc. etc.

As to if it's worth it for your SaaS to mitigate is up to you of course. I know I notice when things like Invoice #'s, my userID, customerID, etc. get shown, and it's a small number, or small delta. And that impacts my behavior. Does it also impact someone like a VC’s behavior? Or a competitor? Unknown.

But Hell, a bunch of people right now are probably launching SaaS's with NO-OP auth flows for documents or images, or with trivial external SQL injection flaws, which is a way bigger problem.

But it doesn't make the vulnerability/problem non-existent. Also feel free to use 16 bit auto incrementing primary keys for all your core tables if you want.

But if we're in an engineering discussion on the topic, knowledgeable folks will bring up the issues with it, because that is what they do. Most would feel it is their duty to do it, even.

Re: PostgreSQL and UUID as Primary Key

#148

Post by lazide »

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 doesn’t require an attacker to create billions of rows.

All it requires is for there to be billions of rows. 32 bits is nutty small considering how little overhead there is in storing 64 bits instead.

Re: PostgreSQL and UUID as Primary Key

#149

Post by lazide »

arp242 wrote: post #140
njtransit wrote: post #128

Earlier quoted context omitted.

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 addressi…

> It’s like hosting an internal app at a company that contains a SQL injection It's nothing like that at all because the wrong SQL injection can completely ruin people's lives due to leaking stuff it shouldn't whereas the worst an int exhaustion can do is bring some app offline. Whoopdie-doo. Okay, that's not brilliant, but it's not comparable at all. And there's a reason there aren't tons of "int exhaustion attacks"…

So taking down the apps ability to insert any rows into the table (and hence breaking the app) isn’t going to impact anyone? Including the apps ability to make money?

This does happen and break people. You usually don’t hear about it (except on the SRE side) because it is so obvious when it happens to someone they really don’t like talking about it.

Re: PostgreSQL and UUID as Primary Key

#150

Post by openthc »

osigurdson wrote: post #90

I'm surprised there is no mention of hash based indexes (i.e. CREATE INDEX ... USING hash...) since lookups would conceivably always use equality.

With UUIDv7/v8 (and ULID) there are some timestamps in the front half. I've seen spots where the query was in the style of `uuid_col >= 'SOME_UUID_0000' AND ulid_col <= 'SOME_UUID_FFFF'` When one is using them ast the timing for record create/insert these things happen.
Post reply on HN