71–80 of 345 posts
It would be nice for these comparisons to also include 'int64' so people can see how much of an overhead UUID's are compared to the traditional approach.
The size of that problem depends on your situation
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.
Earlier quoted context omitted.
Why should they be done in application code?
There’s no need since they are random, so you might as well generate them on your application servers which are easier to scale, to offload the write database.
For my workloads at least, I think it's worth the tradeoff of forcing Postgres to muster 15 random bytes.
If you're generating random UUIDs as the primary key, how do you not run into key collisions? Having to search the entire table before inserting is slow, and catching the error and trying again is also annoying.
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.
there is unlikely significant performance degradation for int vs big int, but it will be huge PITA, if 10 years later and tons of legacy code written that table will grow over 4B rows..
You’re going to know well in advance before hitting this limit becomes an issue, and you’ll have plenty of time to either take a bit of downtime and do a column conversion, or do an online migration.
Okay but why
My somewhat naive understanding was that random UUIDs were not that big of a deal in Postgres because it does not cluster by primary key. And of course a UUID (16 bytes) is larger than a serial (4 bytes) or bigserial (8 bytes) by a factor of 2-4 . This certainly might matter for an index, but on a whole table level where you have 20+ bytes overhead per row this doesn't seem that big of a deal for anything except very…
Random UUID's are super useful when you have distributed creation of UUID's, because you avoid conflicts with very high probability and don't rely on your DB to generate them for you, and they also leak no information about when or where the UUID was created. Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It does well enough for all practical purposes if you need random…
See Snowflake IDs for a scheme that gives you the benefit of random UUIDs but are strictly increasing. Which is really UUIDv7 but fits in your bigint column. No entropy required.
> inserting UUID v7 is ~2x faster and inserting regular UUID v4 Okay but why
"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."
Earlier quoted context omitted.
there is unlikely significant performance degradation for int vs big int, but it will be huge PITA, if 10 years later and tons of legacy code written that table will grow over 4B rows..
It’s double the size. 4 bytes * 2^31 (because Postgres doesn’t allow have unsigned ints, unlike MySQL) is 8.6 GB. That is quite a difference for an index, not to mention the table overhead. You’re going to know well in advance before hitting this limit becomes an issue, and you’ll have plenty of time to either take a bit of downtime and do a column conversion, or do an online migration.
I didn't get your point. When it is 2^31, you definitely need bigint already. When it is much smaller, it will be much smaller overhead.
Also, per docs (https://www.postgresql.org/docs/current/storage-page-layout....), each postgres row has 23 bytes overhead, so your transition from 8->4 bytes will bring marginal improvement.