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

111–120 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#111

Post by solidsnack9000 »

vbezhenar wrote: post #86
solidsnack9000 wrote: post #52

Earlier quoted context omitted.

One challenge with PNR is actually restricting the alphabet appropriately. They sure are easy to say aloud -- just five or six letters in many cases -- but how do you ensure you have (a) enough letters to get a reasonable bitwidth and (b) not form ridiculous words?

Do you ensure that your software does not form ridiculous numbers? Imagine that some christian person gets "666" number. What a scandal. Do you ensure that your software does not form ridiculous words in every language? Or just another US-centric thing? The idea of avoiding identifiers to be ridiculous is ridiculous to me, honestly...

This is just not going work in a customer centric organization. If SABRE gave out PNRs like, I don't know, SEXGOD, it would make customers angry, and that is not even with any curse words. They are heavily filtered.

https://onemileatatime.com/insights/funny-airline-pnr/

Re: PostgreSQL and UUID as Primary Key

#112

Post by sgarland »

lazide wrote: post #92
tommy_axle wrote: post #87

Earlier quoted context omitted.

It is simpler but like everything else it depends on the application. For a private app you can generally get away with it. Something that's more public facing? The ID will most likely leak information. As systems mature and you introduce things like replication, having IDs that are more universal starts looking good. In general, starting off with a uuid like uuid v4 or nanoid is a good bet.

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.

Re: PostgreSQL and UUID as Primary Key

#113

Post by solidsnack9000 »

dmurray wrote: post #63
solidsnack9000 wrote: post #52

Earlier quoted context omitted.

One challenge with PNR is actually restricting the alphabet appropriately. They sure are easy to say aloud -- just five or six letters in many cases -- but how do you ensure you have (a) enough letters to get a reasonable bitwidth and (b) not form ridiculous words?

Take all the Roman alphabet apart from the vowels - 21 characters and length 6 gives you 100 million possibilities which is plenty for most applications. You can still get vaguely offensive sequences like FKNNGR or BLKCNT, but at some point you have to put this down not to your software being offensive or hateful but to humans finding patterns in randomness.

Removing Y turns out to be important, as well...

Re: PostgreSQL and UUID as Primary Key

#114

Post by solidsnack9000 »

codetrotter wrote: post #62
solidsnack9000 wrote: post #52

Earlier quoted context omitted.

One challenge with PNR is actually restricting the alphabet appropriately. They sure are easy to say aloud -- just five or six letters in many cases -- but how do you ensure you have (a) enough letters to get a reasonable bitwidth and (b) not form ridiculous words?

> not form ridiculous words Depends on what you mean by ridiculous. For example https://sqids.org/ ensures that there are no profanities in the generated ids. And it allows you to add additional words that you want to avoid.

It does but it allows common names, the name of the deity, common words, &c. I suspect you have to do something like an earlier poster suggested -- strip out all the vowels to start with...

Re: PostgreSQL and UUID as Primary Key

#115

Post by jpollock »

saaspirant wrote: post #108
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…

Naive question. Above comment suggests using bigserial as internal identifier and uuid as public facing ID. Now let's say there's a user table and post table. Both will have only uuid available in the APIs. So every time API requests a post of the user or user of the post, we will find the the relevant row using uuid right? Since uuid will be sent by the public facing APIs? How would bigserial be used here? I don't k…

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 tables, and the UserId column on Post. You can then join from one to the other using the ID column.

e.g.

  SELECT count(*) FROM
   Post JOIN User 
   ON User.ID = Post.UserID
  WHERE 
   User.external_id = ;

  // Don't forget to bind USER_ID_FROM_REQUEST instead of 
  // concating the string, no Johny-Tables here!
There should be significant performance benefits from using int's for the joining keys (at least compared to strings), but my experience might be old.

Re: PostgreSQL and UUID as Primary Key

#116

Post by sgarland »

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 button and double your DB’s hardware? Boom, problem solved… until it isn’t. Eventually, you run out of scaling, and since you probably don’t have a DBA/DBRE (else they’d have been screaming at you for months), it’s going to be extremely painful to solve now.

The bare minimum I’m asking – as a DBRE – is to use UUIDv7 and store them in Postgres’ native UUID type. That’s all. That’s an incredibly small amount of effort to put forth.

Re: PostgreSQL and UUID as Primary Key

#117

Post by gregoryl »

jerf wrote: post #105
afavour wrote: post #96

Earlier quoted context omitted.

That’s why the original comment suggested both bigserial and a separate UUID for public exposure. More to the point the person I was replying to said: > IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows The implication I took from that was that they were suggesting using serial over bigserial. My c…

My Dream Web Framework, which for a variety of reasons was never and never will be built, has built-in functionality for obscuring IDs in some session-level map, so you can indicate through some sort of type that something is an ID and it automatically allocates some sort of randomized identifier on the way out and converts it back transparently on the way back in. Thus, not only would DB ids in principle never show…

I wrote that style of session mapping for a project long ago. It was fairly easy, but a massive pain in the ass to debug. Ended up needing to record the mappings in the backend for a period of time.

Re: PostgreSQL and UUID as Primary Key

#118

Post by avg_dev »

westurner wrote: post #103
brycelarkin wrote: post #55

Earlier quoted context omitted.

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

Why “bigint generated always as identity” instead of bigserial, instead of Postgres' uuid data type? Postgres' UUID datatype: https://www.postgresql.org/docs/current/datatype-uuid.html#D... django.db.models.fields.UUIDField: https://docs.djangoproject.com/en/5.0/ref/models/fields/#uui... : > class UUIDField: A field for storing universally unique identifiers. Uses Python’s UUID class. When used on PostgreSQL and Mari…

Just noting, the commenter you replied to said:

> use “bigint generated always as identity” instead of bigserial.

The commenter you are replying to was not saying anything about whether to use UUIDs or not; they just said "if you are going to use bigserial, you should use bigint generated always as identity instead".

Re: PostgreSQL and UUID as Primary Key

#119

Post by snicker7 »

big_whack wrote: post #26
munk-a wrote: post #19

Earlier quoted context omitted.

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

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.

OR if the primary key is exported out of the DB, i.e. for constructing URLs.

Re: PostgreSQL and UUID as Primary Key

#120

Post by [deleted] »

westurner wrote: post #103
brycelarkin wrote: post #55

Earlier quoted context omitted.

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

Why “bigint generated always as identity” instead of bigserial, instead of Postgres' uuid data type? Postgres' UUID datatype: https://www.postgresql.org/docs/current/datatype-uuid.html#D... django.db.models.fields.UUIDField: https://docs.djangoproject.com/en/5.0/ref/models/fields/#uui... : > class UUIDField: A field for storing universally unique identifiers. Uses Python’s UUID class. When used on PostgreSQL and Mari…

[deleted]
Post reply on HN