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

291–300 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#291

Post by rattray »

njtransit wrote: post #128
incrudible wrote: post #43

Earlier quoted context omitted.

If an attacker can create billions of records through your API, maybe that is a problem you need to address either way.

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…

Either of those situations should be very easy to catch and prevent with basic rate limiting and monitoring.

Re: PostgreSQL and UUID as Primary Key

#292

Post by kortex »

Has there ever been an attack/leak based on time metadata in UUIDs/ULIDs/Snowflakes/etc (not sequential), in a domain outside of sensitive ones (HIPAA/healthcare, defense)?

I'm skeptical that for the vast majority of uses, have time data in the IDs (and also not leaking it from actual timestamp fields) is an issue, and for the cases where it does, just use random IDs.

Even theoretically, is there a way to translate timestamps into a German tank problem? If I give you a sampled set of timestamps (with no other node identifiers), can you estimate the cardinality of the greater dataset?

Re: PostgreSQL and UUID as Primary Key

#293

Post by riku_iki »

sgarland wrote: post #253
riku_iki wrote: post #127

Earlier quoted context omitted.

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

It’s orthogonal, and also technically incorrect – the row overhead itself is indeed 23 bytes (less NULL bitmap), but it gets aligned to 24 bytes. Run pg_column_size(ROW()) if you’d like to check. The fact that this overhead exists has nothing to do with the 8.6 GB of wasted space on the INTs.

> It’s orthogonal

I disagree its orthogonal and explained why. I guess lets agree on disagree.

Re: PostgreSQL and UUID as Primary Key

#294

Post by rattray »

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…

> Stripe are lying when they say their IDs are random

Where does Stripe make that claim?

I think most of the value of Stripe-style IDs is in their other properties, like the information they bear, readability, copy-pasteability (which uuids lack - double-click to select often stops at hyphens), etc.

Re: PostgreSQL and UUID as Primary Key

#295

Post by jrochkind1 »

stoperaticless wrote: post #217
jrochkind1 wrote: post #104

Earlier quoted context omitted.

I don't know the math here specifically, but being hard to guess is a different quantity than chance of collision when following the algorithm . That is, if you aren't trying to have a collision and following the algorithm that has that aim, a collision can be exceedingly unlikely; but they can still be easy to guess if you are trying to predict someone else's assignment.

to illustrate point above: Sequence that easy to guess, but will never collide until it wraps back to 1. n1=1,n2=2,… (Caveat: if generated only by single node/thread)

Thanks, not sure what's up with the downvotes, it's simply a fact of the math, and in particular the math here.

A computer random number generator with a seed deterministically based on MAC address is another less trivial demonstration of the idea.

Re: PostgreSQL and UUID as Primary Key

#296

Post by Merad »

vbezhenar wrote: post #21
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…

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.

An int PK does not give you 4 billion rows. For example, in Postgres every time an insert transaction has to rollback, the id's that would have been used by that insert are discarded and gone forever. Likewise people often don't realize that Postgres' very convenient upsert syntax (insert on conflict do update) will consume id's from the sequence _every time it runs_. If you do an upsert with 100 rows, it will peel off 100 new id's just in case they're needed... if they aren't used, they're discarded. This can chew through 4 billion id's really, really fast.

Personally I would only use an int PK in a table where you know the bounds of the data with very high confidence. Like, at my last job the software involved working with our client's physical store locations. If we managed to capture the entire North American market for the type of business that we worked with, we'd be looking at about 50k stores.

Re: PostgreSQL and UUID as Primary Key

#297

Post by groestl »

kevincox wrote: post #287
groestl wrote: post #268

Earlier quoted context omitted.

> UUID generated client side and basically upserted it Read and take notes. This is crazy in untrusted environments.

Generating IDs on the client can be very useful for offline-first systems. But you need to check for conflicts and permissions on the server (or be sure to keep the IDs secret which I wouldn't recommend).

Agreed, but in that case "upsert" is also weird, since I'd structure such a system around an immutable log datastructure.

Re: PostgreSQL and UUID as Primary Key

#298

Post by hu3 »

laserDinosaur wrote: post #286
hu3 wrote: post #274

Earlier quoted context omitted.

That was it if I recall. They wrote a small script with the logic involved in the merging. PKs and FKs of only one database had to be incremented by an offset of max(table.pk) + safe margin. They did this for each table. Once this script was tested multiple times with subsets of each database, they stopped production and ran the script against it (with backup fallbacks). A small downtime window in a Sunday. And that…

>they stopped production Oh I see, we're talking about two entirely different worlds here, lol.

Not being able to stop production database for a very short window once in a lifetime is another exceptionally rare business case.

I've seen architecture astronauts make their business pay unreasonable tech insurances by adding complexity to avoid simply pausing production for some minutes when it could have been much cheaper this way.

And from my understanding, in the case I mentioned, they chose to stop production to simplify the process. But they didn't have to.

A mixture of replication plus code changes to write in two databases could also have solved the issue.

Most business die because they can't move fast enough. Not because their production database stopped for a few minutes.

Re: PostgreSQL and UUID as Primary Key

#299

Post by Merad »

westurner wrote: post #284
avg_dev wrote: post #118

Earlier quoted context omitted.

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".

The question is the same; why would you use bigint instead of the native UUID type? Why does OT compare text and UUID instead of char(32) and UUID? What advantage would there be for database abstraction libraries like SQLalchemy and Django to implement the UUID type with bigint or bigserial instead of the native pg UUID type?

Best practice in Postgres is to use always use the text data type and combine it with check constraints when you need an exact length or max length.

See: https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage

Also, I think you're misunderstanding the article. They aren't talking about storing a uuid in a bigint. They're talking about have two different id's. An incrementing bigint is used internally within the db for PK and FK's. A separate uuid is used as an external identifier that's exposed by your API.

Re: PostgreSQL and UUID as Primary Key

#300

Post by grey-area »

laserDinosaur wrote: post #286
hu3 wrote: post #274

Earlier quoted context omitted.

That was it if I recall. They wrote a small script with the logic involved in the merging. PKs and FKs of only one database had to be incremented by an offset of max(table.pk) + safe margin. They did this for each table. Once this script was tested multiple times with subsets of each database, they stopped production and ran the script against it (with backup fallbacks). A small downtime window in a Sunday. And that…

>they stopped production Oh I see, we're talking about two entirely different worlds here, lol.

Stopping production on db B isn't really a requirement, just makes it easier.
Post reply on HN