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

171–180 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#171

Post by inopinatus »

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.

There are jurisdictions e.g. Germany in which a consecutive sequence for invoice numbers is a mandatory, legislated requirement (mercifully, gaps are generally permitted, with caveats)

For extra spice, in some places this is legislated as a per-seller sequence, and in others as a per-customer sequence, so there’s no policy you can apply globally, and this once again highlights the separation of concerns between a primary key and a record locator/identifier.

Re: PostgreSQL and UUID as Primary Key

#172

Post by arp242 »

riku_iki wrote: post #169
arp242 wrote: post #154

Earlier quoted context omitted.

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

> cherry picked some fictional example

What an incredibly rude and dismissive accusation. Here's my table: https://github.com/arp242/goatcounter/blob/master/db/schema.... – number of IDs is actually 7, not 6.

I can give a lot more details and context on all of that and why it works the way it works and the savings are certainly not insignificant and theoretical, but save me real money in server costs every month. But I have little interest in talking to you further since you're just going to insult everyone who tries to inject the slightest nuance into "always use bigserial"-extremism. So good day to you.

Re: PostgreSQL and UUID as Primary Key

#173

Post by riku_iki »

arp242 wrote: post #172
riku_iki wrote: post #169

Earlier quoted context omitted.

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

> cherry picked some fictional example What an incredibly rude and dismissive accusation. Here's my table: https://github.com/arp242/goatcounter/blob/master/db/schema.... – number of IDs is actually 7, not 6. I can give a lot more details and context on all of that and why it works the way it works and the savings are certainly not insignificant and theoretical, but save me real money in server costs every month. But…

> https://github.com/arp242/goatcounter/blob/master/db/schema.... - number of IDs is actually 7, not 6.

the point is that some/many of those ids are not timezone and country, and potentially can grow high in cardinality.

Re: PostgreSQL and UUID as Primary Key

#174

Post by karmajunkie »

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.

i mean, you might care if the investors you’re trying to woo for that bridge round figure out your churn is a lot higher than you’re willing to admit… or worse, your traction is terrible.

Re: PostgreSQL and UUID as Primary Key

#175

Post by t-writescode »

sgarland wrote: post #110
tills13 wrote: post #71

Earlier quoted context omitted.

The problem here is that auto increment ints are guessable. The size of that problem depends on your situation

If your API is relying purely on the user ID for AuthZ, you have much bigger problems.

It's not just authz, it can also be a reflection of how many customers you have, and that can also be its own problem.

Or, let's say you are a middleware company and you're trying to contract your services to two other companies. These companies are competitors of each other, but they're also the biggest in their space.

If you have use, let's say, autoincrementing ints for orders or operations, then the companies could use the distance between order numbers as a way of calculating how much business the other business is doing.

If that functionality is possible / easy, that may be a reason for those customers to stop using you, and you've now lost one or two of your major contracts.

Apparently this has been brought up by others and is called the German Tank Problem.

Re: PostgreSQL and UUID as Primary Key

#176

Post by dgb23 »

jpollock wrote: post #115
saaspirant wrote: post #108

Earlier quoted context omitted.

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

A uuid is just an uint128, or a byte array of the same size. A DB can use them as fixed size keys just fine, and they convert quickly from/into their string representaion.

The problem is indexing and generation.

For example sqlite already generates sequential ids whether you use them or not.

Non sequential uuids suck for indexing as the table grows. There are some benchmarks and explanations to look up.

The benefit is you can merge aggregates from different DB instances without a fuss. And you can generate uuids without asking your DB.

Re: PostgreSQL and UUID as Primary Key

#177

Post by JodieBenitez »

inopinatus wrote: post #171
mewpmewp2 wrote: post #152

Earlier quoted context omitted.

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.

There are jurisdictions e.g. Germany in which a consecutive sequence for invoice numbers is a mandatory, legislated requirement (mercifully, gaps are generally permitted, with caveats) For extra spice, in some places this is legislated as a per-seller sequence, and in others as a per-customer sequence, so there’s no policy you can apply globally, and this once again highlights the separation of concerns between a pri…

> There are jurisdictions e.g. Germany in which a consecutive sequence for invoice numbers is a mandatory

Same in France. I thought it was a pretty common requirement.

Re: PostgreSQL and UUID as Primary Key

#178

Post by wodenokoto »

inopinatus wrote: post #171
mewpmewp2 wrote: post #152

Earlier quoted context omitted.

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.

There are jurisdictions e.g. Germany in which a consecutive sequence for invoice numbers is a mandatory, legislated requirement (mercifully, gaps are generally permitted, with caveats) For extra spice, in some places this is legislated as a per-seller sequence, and in others as a per-customer sequence, so there’s no policy you can apply globally, and this once again highlights the separation of concerns between a pri…

> consecutive sequence for invoice numbers is a mandatory, legislated requirement (mercifully, gaps are generally permitted, with caveats)

That’s surprising. In Denmark gaps are not allowed. You have to account for all invoices and if you have an invoice numbered 50, then you have at least 50 invoices to account for.

Re: PostgreSQL and UUID as Primary Key

#179

Post by riku_iki »

vbezhenar wrote: post #39
rangerelf wrote: post #36

Earlier quoted context omitted.

I read your post and hear echoes of "Who would ever need more than 2 digits for the year in this timestamp column?" Never again.

Using 2 digits for year is as wrong as using 8 bytes for year.

scale of damage is very different, so "as" is up to discussion.

Re: PostgreSQL and UUID as Primary Key

#180

Post by nirvdrum »

riku_iki wrote: post #127
sgarland wrote: post #123

Earlier quoted context omitted.

Not all tables need even the capacity for 2^30 rows, much less 2^31, or 2^63. If you have a reference table with things like timezone information, color schemes, etc. and are using anything other than a SMALLINT (2^15), you're probably wasting space. As to the maximal 8.6 GB mentioned, that's not nothing, _especially_ for RAM. Disk is cheap, but RAM isn't. If you have a smaller instance – say, an r6i.xlarge on AWS (4…

so, what about my argument that PG has 23 bytes overhead per row and your space win is very small compared to that overhead?

Is that an innate property or a current implementation detail?
Post reply on HN