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

321–330 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#321

Post by vbezhenar »

paulddraper wrote: post #192
vbezhenar wrote: post #21

Earlier quoted context omitted.

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. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.

The problems from unnecessary bigserial is nothing compared to the pain of insufficient serial. This is especially true for PostgreSQL which increments sequences for upserts (even if no records are inserted). That's how I've hit 32-bit limits on tables that had only a couple million rows. --- I would only use 32-bit for very selective items that is used in a lot of FKs, like a tenant ID.

I didn't know about upserts incrementing sequence. That makes sense. I don't think I ever used upsert, but that's a good reason to use bigint id (or at least make update count a factor to consider), thanks for pointing out.

Re: PostgreSQL and UUID as Primary Key

#322

Post by melody_calling »

sgarland wrote: post #246
lazide wrote: post #147

Earlier quoted context omitted.

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

> 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. To be clear, this is not directed at you specifically, and I have no idea what your level of expertise is on anything. In general, I’ve found HN commenters level of knowledge to be fairly bimodal. They’re either regurgitating things they read on a Medium blog, or they really kn…

> Every time this topic comes up, people delightedly mention the German Tank Problem, but I have never, not once, seen anyone post an actual example of when a modern business got rekt by a competitor using knowledge gained from monotonic IDs.

At $previous_job (payments provider), the sales engineers would often spot merchants using sequential order IDs and mention it to the account managers.

Rekt? I guess not, but knowing what percentage of their business we processed was extremely valuable information when it came to renegotiate the contract.

Re: PostgreSQL and UUID as Primary Key

#323

Post by mythrwy »

necovek wrote: post #133
mythrwy wrote: post #79

Earlier quoted context omitted.

It tells you in the previous section: "UUID - even though always looks similar - comes in multiple variants. Java's UUID.randomUUID() - returns UUID v4 - which is a pseudo-random value. For us the more interesting one is UUID v7 - which produces time-sorted values. It means that each time new UUID v7 is generated, a greater value it has. And that makes it a good fit for B-Tree index."

The benchmark seems to include generation in the total time. Not sure that's a useful comparison of how b-tree index behaves in each case (as UUIDv7 has fewer bits of randomness, it's also cheaper to generate).

Key generation time (for either type of UUID) is negligible compared to insert times. On the order of fractions of a microsecond for key generation as opposed to several milliseconds for inserts.

Re: PostgreSQL and UUID as Primary Key

#325

Post by lazide »

mewpmewp2 wrote: post #163
sgarland wrote: post #116

Earlier quoted context omitted.

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.

Auto increment ID’s also leak information.

Random UUID’s shouldn’t, but will impact performance, which in most cases will have more concrete impact on the business.

There is no such thing as a free lunch.

Re: PostgreSQL and UUID as Primary Key

#326

Post by lazide »

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.

Eh, using 2 digits for year is a lot ‘more wrong’ than 8 bytes. Since all (current) known use cases can fit in 8 bytes for the conceivable lifespan of human civilization.

2 digit years don’t even support the use case of ‘store the current year’ fully.

Re: PostgreSQL and UUID as Primary Key

#327

Post by lazide »

akira2501 wrote: post #308
lazide wrote: post #147

Earlier quoted context omitted.

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

> Which requires physical presence. It requires physical access. Presence can be achieved by placing a dash camera or window camera strategically. > and they were expending a lot of effort trying to exceed that previous false number. The idea here being that if the allies just made more tanks than the axis they would automatically win? It's a lionized story of intelligence agency cleverness built squarely on the back…

Having a dashcam in this situation is only useful if that dashcam is physically there during the times you care about eh? Which is the physical presence part. With some applied statistics, that can be reduced (no need to sit in every parking lot 24/7 for instance), but there is definitely some sitting in parking lots involved.

No, it’s that they would set tank production quotas (and budgets) based on what they expect to need to produce to crush the enemy. Modulo whatever level of effectiveness had been apparent so far.

Which, for any given set of resources, would mean taking away resources from something else. Either manpower, steel, oil/gas, etc.

So overproducing tanks means fewer aircraft, or less artillery, etc.

Convincing the enemy you’re producing too much (or too little) of something to get them to waste their time and energy is a classic counter intelligence move.

WW2 was won largely with logistics and economic might + reasonable tactics. Applying the economic might effectively was part of that.

Re: PostgreSQL and UUID as Primary Key

#328

Post by akira2501 »

lazide wrote: post #327
akira2501 wrote: post #308

Earlier quoted context omitted.

> Which requires physical presence. It requires physical access. Presence can be achieved by placing a dash camera or window camera strategically. > and they were expending a lot of effort trying to exceed that previous false number. The idea here being that if the allies just made more tanks than the axis they would automatically win? It's a lionized story of intelligence agency cleverness built squarely on the back…

Having a dashcam in this situation is only useful if that dashcam is physically there during the times you care about eh? Which is the physical presence part. With some applied statistics, that can be reduced (no need to sit in every parking lot 24/7 for instance), but there is definitely some sitting in parking lots involved. No, it’s that they would set tank production quotas (and budgets) based on what they expect…

> Convincing the enemy you’re producing too much (or too little) of something to get them to waste their time and energy is a classic counter intelligence move.

So much so that it calls into question the entire idea of clandestine wartime intelligence having any exceptional value.

> WW2 was won largely with logistics and economic might + reasonable tactics.

I think people who say this simply haven't counted the dead.

> Applying the economic might effectively was part of that.

Which had essentially no bearing on our problems with Japan.

Re: PostgreSQL and UUID as Primary Key

#329

Post by [deleted] »

blackenedgem wrote: post #304
kroolik wrote: post #281

Earlier quoted context omitted.

PG bigserial is already strictly monotonic

No they're not, even with a `cache` value of 1. Sequence values are issued at insert rather than commit. A transaction that commits later (which makes all updates visible) can have an earlier value than a previous transaction. This is problematic if you try to depend on the ordering. Nothing is stopping some batch process that started an hour ago from committing a value 100k lower than where you thought the sequence…

[deleted]

Re: PostgreSQL and UUID as Primary Key

#330

Post by lazide »

akira2501 wrote: post #328
lazide wrote: post #327

Earlier quoted context omitted.

Having a dashcam in this situation is only useful if that dashcam is physically there during the times you care about eh? Which is the physical presence part. With some applied statistics, that can be reduced (no need to sit in every parking lot 24/7 for instance), but there is definitely some sitting in parking lots involved. No, it’s that they would set tank production quotas (and budgets) based on what they expect…

> Convincing the enemy you’re producing too much (or too little) of something to get them to waste their time and energy is a classic counter intelligence move. So much so that it calls into question the entire idea of clandestine wartime intelligence having any exceptional value. > WW2 was won largely with logistics and economic might + reasonable tactics. I think people who say this simply haven't counted the dead.…

Uh huh. Populations of conscriptable soldiers or percent of the population killed totally didn’t matter? Those are part of the economic equation.

The war in the pacific was started by, and almost entirely dominated by, economic matters. Japan attacked the US because of US embargoes on fuel, for instance.

And the creation of the atom bomb (which ended it) was only possible due to the insane logistical feats of the US - and its excess economic capacity and resources. Which is why Germany failed, and the USSR only got one after getting the ‘ingredients list’ post war - once the wartime resources had been freed up.

War is hell, so don’t start them. But also, don’t think modern war isn’t won (or lost) based on logistics and economic factors. Which intelligence can help with.

Post reply on HN