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

221–230 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#221

Post by iterateoften »

badestrand wrote: post #186
TeeWEE wrote: post #131

Earlier quoted context omitted.

Big serial is sequential and it’s very easy to guess the next number. So you got the problem of sequential key attack… If you use only uuid in your outwards facing api then you still have the problem of slow queries. Since you need them to find the object (as mentioned below) UUIDv7 has a random part, can be created distributedly, and indexes well. It’s the best choice for modern application that support distributed…

You never expose the bigserial, you generate a ID (like UUID) for external use/identification and simply have an index over that column for fast selects.

If you have an index on the uuid anyways having a separate big serial field for PK doesn’t help that much.

Re: PostgreSQL and UUID as Primary Key

#222

Post by stoperaticless »

quotemstr wrote: post #7

> If you have an option to choose, take a look at TSID maintained by Vlad Mihalcea. TSID: > A Java library for generating Time-Sorted Unique Identifiers (TSID). Wouldn't this TSID thing be more useful if it were implemented as a set of PostgreSQL stored procedures or something than a Java library? Not everyone uses Java.

- Plenty of use-cases want to generate it on application side, so they can just “insert”, instead of “insert+select the id”

- not everybody uses Postgre (either way, somebody will be left out)

Re: PostgreSQL and UUID as Primary Key

#223

Post by pin24 »

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…

Just out of curiosity, why string-encoded UUIDs? A native/binary UUID column type should not take more than 16 bytes, whereas its hexadecimal string representation requires 36 bytes (if we assume 1 byte per character). That would result in an index size more than twice as big as required.

Re: PostgreSQL and UUID as Primary Key

#224

Post by blackenedgem »

kroolik wrote: post #219
badestrand wrote: post #186

Earlier quoted context omitted.

You never expose the bigserial, you generate a ID (like UUID) for external use/identification and simply have an index over that column for fast selects.

Having an index over the uuid is equivalent to it being a PK, so why would you bother having both?

Because it's much better for range queries and joins. When you inevitably need to take a snapshot of the table or migrate the schema somehow you'll be wishing you had something else other than a UUID as the PK.

Re: PostgreSQL and UUID as Primary Key

#225

Post by mixmastamyk »

hoffs wrote: post #199
mixmastamyk wrote: post #161

Earlier quoted context omitted.

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

The details part is so miniscule that I doubt it even matters. You'd have difficult time trying to enumerate uuidv7s anyways.

Leaking time leaks information about customer growth and usage. It may matter to your competitors.

Re: PostgreSQL and UUID as Primary Key

#226

Post by branko_d »

I'm surprised author didn't mention foreign keys: since primary key is often referenced by foreign keys, then if you have a "fat" PK all your FKs will also be "fat". This can be solved by using the UUID as an alternate key and the regular integer as a primary key in the "top" table, and then referencing that integer from all the "child" tables.

Obviously, this has ramifications for data write perf, but may well be worth it depending on the use case. Technically, you could also make the integer alternate (and leave UUID as primary), but that may not de desirable for clustered / index-organized tables (for DBMSes that support them).

Re: PostgreSQL and UUID as Primary Key

#227

Post by OskarS »

zxexz wrote: post #206
OskarS wrote: post #197

Earlier quoted context omitted.

You store the key in the database, right? Like, if the database leaks, it doesn’t matter if your ids are sequeneced or unsequenced, because all data has leaked anyway. The key leaking doesn’t seem like a realistic security issue.

Ideally if you do this, you store the key in a separate schema with proper roles so that you can call encrypt() with the database role, which can't select the key. Even then, the decrypted metadata should not be particularly sensitive - and should immutably reference a point in time so you can validate against some known key revocation retroactively. My take is it's rarely necessary to have a token, that you give to…

This is a very weird thread: half the people are arguing that having these timestamps is not a realistic security problem at all, and the other half is arguing that any fix to it has to have Fort Knox level security policies.

It seems to me: the actual value of knowing these ids/timestamps to a hacker is tiny, but it's not nothing (German tank problem and all that). Like, if a hacker was able to decode the timestamps, it's not ideal, but it's not like a catastrophe either (especially given that half the people in this thread thinks it has no security value at all). Given that threat model, a simple scheme like I suggested seems fine to me.

Re: PostgreSQL and UUID as Primary Key

#228

Post by groestl »

blackenedgem wrote: post #224
kroolik wrote: post #219

Earlier quoted context omitted.

Having an index over the uuid is equivalent to it being a PK, so why would you bother having both?

Because it's much better for range queries and joins. When you inevitably need to take a snapshot of the table or migrate the schema somehow you'll be wishing you had something else other than a UUID as the PK.

Ha? Please elaborate.

Re: PostgreSQL and UUID as Primary Key

#229

Post by kroolik »

groestl wrote: post #228
blackenedgem wrote: post #224

Earlier quoted context omitted.

Because it's much better for range queries and joins. When you inevitably need to take a snapshot of the table or migrate the schema somehow you'll be wishing you had something else other than a UUID as the PK.

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

Re: PostgreSQL and UUID as Primary Key

#230

Post by rezonant »

blackenedgem wrote: post #224
kroolik wrote: post #219

Earlier quoted context omitted.

Having an index over the uuid is equivalent to it being a PK, so why would you bother having both?

Because it's much better for range queries and joins. When you inevitably need to take a snapshot of the table or migrate the schema somehow you'll be wishing you had something else other than a UUID as the PK.

This. Highly recommend using a numeric primary key + UUID. Using UUID relations internally can have some strategic advantages, but when UUIDv4 is used as the only primary key, you completely lose the ability to reliably iterate all records across multiple independent queries.

Also, the external thing isn't just for exposing it out to your own apps via APIs, but way more importantly for providing an unmistakable ID to store within external related systems. For example, in your Stripe metadata.

Doing this ensures that ID either exists in your own database or does not, regardless of database rollbacks, database inconsistencies etc. In those situations a numeric ID is a big question mark: Does this record correspond with the external system or was there a reuse of that ID?

I've been burnt taking over poorly managed systems that saved numeric IDs externally, and in trying to heal and migrate that data, ran into tons of problems because of ill-considered rollbacks of the database. At least after I leave the systems I build won't be subtly broken by such bad practices in the future.

Post reply on HN