[HN Gopher] PostgreSQL and UUID as Primary Key
___________________________________________________________________
PostgreSQL and UUID as Primary Key
Author : taubek
Score : 124 points
Date : 2024-07-05 18:21 UTC (4 hours ago)
(HTM) web link (maciejwalkowiak.com)
(TXT) w3m dump (maciejwalkowiak.com)
| hu3 wrote:
| Would have been nice to also include bigserial/bigint in the
| INSERT performance comparison to understand the impact of
| migrating those to UUIDv4 and UUIDv7.
| AdamJacobMuller wrote:
| Also testing using postgres-native uuid generation
| fabian2k wrote:
| 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 narrow tables with large
| row counts.
|
| So far my impression is that there are a whole lot of other
| things I need to worry about in Postgres before I spend time
| considering serial vs. random UUID vs. ordered UUID. Am I wrong
| here and this is something that really matters and you should
| invest more time in?
| masklinn wrote:
| While Postgres does not cluster table, uuids do affect indexes
| significantly, random insertion causes increased index slack
| which translates to cache bloat and thus longer traversal and
| lower cache residency.
| oppositelock wrote:
| 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 randomness.
| kevin_nisbet wrote:
| > So far my impression is that there are a whole lot of other
| things I need to worry about in Postgres before I spend time
| considering serial vs. random UUID vs. ordered UUID. Am I wrong
| here and this is something that really matters and you should
| invest more time in?
|
| Like any sort of optimization I believe this will depend on
| your workload and what's important.
|
| For me when I switched to UUIDv7 a few months ago, it was
| basically no effort to switch v4 to v7 on a relatively new
| system. I was observing much higher batch insertion latencies
| than I expected, and producing inserts that touch less of the
| btree on an index created a very noticeable reduction in
| insertion latencies. But my workloads and latencies may look
| nothing like yours. On amazon RDS instances with EBS volumes
| and relatively low memory, insertion latency stood out, so
| using strategies that reduce the number of disk blocks that are
| needed has an outsized performance impact.
|
| This of course would produce different results on different
| hardware / system sizing.
| munk-a wrote:
| 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 the column, deploy code that utilizes
| the new column and then finally retire the old column. Even if
| the previous version of the table lacked any sort of unique key
| it is still possible to add one after the fact (it's a bit
| technically harder to properly keep them in sync but it is
| possible to do safely).
|
| It's just a question of the cost of doing so and the benefits
| of it - I work in a system that exclusively uses integral keys
| and our data is such that we don't really suffer any downsides
| from that choice - if you're working in a larger system with
| less confidence in the security practices of other teams then
| avoiding sequential keys so that you have obscurity to fall
| back on if someone really drops the ball on real security isn't
| the worst idea... but I think the really compelling reason to
| prefer UUIDs is for the power of distributed generation... that
| really only applies to inherently decentralized or astoundingly
| large products though - and if your product eventually grows to
| astoundingly large you'll have plenty of time to switch first
| (probably the wake-up call will be closing in on running out of
| 4 bit serial unique keys).
| big_whack wrote:
| 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.
| quotemstr wrote:
| > If you have an option to choose, take a look at TSID maintained
| by Vlad Mihalcea.
|
| TSID: > A Java library for generating Time-Sorted Unique
| Identifiers (TSID).
|
| Wouldn't this TSID thing be more useful if it were implemented as
| a set of PostgreSQL stored procedures or something than a Java
| library? Not everyone uses Java.
| busterarm wrote:
| It's forked from https://github.com/f4b6a3/tsid-creator. While
| this is also in Java, it has many reimplementations in other
| languages.
| aabhay wrote:
| I think insert performance is a bad way to evaluate performance
| here, no? While B-Tree performance for time sorted keys is better
| on insert, what about during large transactions?
|
| In SQLite, my assumption was that the consensus was towards UUID4
| rather than 7 because it meant less likelihood for page cache
| contention during transaction locks? Would that not also roughly
| map onto a Postgres-flavored system? Or dues Postgres only have
| row-level locking?
| samokhvalov wrote:
| there is also a problem of data locality and blocks present in
| caches (page cache, buffer pool) at any given time, in general
| -- UUIDv4 is losing to bigint and UUIDv7 in this area
| scotty79 wrote:
| Isn't B-Tree with UUIDv4 keys getting more balanced than with
| UUIDv7? Doesn't longer insert time result in faster searches
| later?
| AtlasBarfed wrote:
| UUIDs are guaranteed to be unique?
|
| They often use tricks like including the MAC address of the
| generator machine and other ways to increase uniqueness
| assurances.
|
| It was my understanding that uuids are simply very very unlikely
| to duplicate in situations with random generation.
| wongarsu wrote:
| UUIDv2 uses Mac addresses, but those turned out to be mostly a
| bad idea. Today when people say UUID they mean UUIDv4, which is
| just 124 random bits (and 4 version bits). Assuming a good
| random number generator it's basically impossible to generate
| the same UUIDv4 twice by pure chance. Even if you make billions
| of them per second it's vanishingly unlikely to happen.
| throwawayffffas wrote:
| They are not theoretically guaranteed they are in practice
| though. 2^128 and 122 are big numbers. Even if you are
| producing a billion per second you have a 50% chance of not
| getting a collision for 100 years.
| jacobgorm wrote:
| I've used 128 secure-random bits for ages, not caring for any
| of the UUID version nonsense. Per the birthday paradox, I
| need to have 2*64 entries in my tables to reach 50% collision
| probability, and it will be a while before I can afford that
| much storage anyhow.
| munk-a wrote:
| Your understanding is correct but you're underselling very very
| in this context. It is astronomically unlikely to hit a
| collision with the advised generation methods. If you want a
| possibly easier to grasp parallel git relies on SHA hashes
| never colliding and will break in a really awful way if you can
| produce two commits in a tree with the same hash - it's so
| astoundingly unlikely that people are okay summarizing it as
| "Never gonna happen" - it certainly will eventually, but it
| might not happen until the earth is swallowed by the sun.
| lulzury wrote:
| What you stated makes intuitive sense, but it does make me
| wonder why the RFC states the following in the security
| considerations:
|
| > Implementations SHOULD NOT assume that UUIDs are hard to
| guess. For example, they MUST NOT be used as security
| capabilities (identifiers whose mere possession grants
| access). Discovery of predictability in a random number
| source will result in a vulnerability.
|
| https://datatracker.ietf.org/doc/html/rfc9562#name-
| security-...
| samokhvalov wrote:
| some related stuff:
|
| - https://commitfest.postgresql.org/48/4388/ (original patch
| created live https://www.youtube.com/watch?v=YPq_hiOE-N8)
|
| - https://postgres.fm/episodes/uuid
|
| - https://postgres.fm/episodes/partitioning-by-ulid
|
| - https://gitlab.com/postgres-ai/postgresql-consulting/postgre...
| inopinatus wrote:
| 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, especially a line-of-
| business application. Use UUIDv7 only as an identifier for data
| that is inherently timecoded, otherwise it leaks information
| (even if timeshifted). Do not use hashids - they have no
| cryptographic qualities and are less friendly to everyday humans
| than the integers they represent; you may as well just use the
| sequence ID. As for the encoding, do not use base64 or other
| hyphenated alphabets, nor any identifier scheme that can produce
| a leading '0' (zero) or '+' (plus) when encoded (for the day your
| stuff is pasted via Excel).
|
| Generally, the principles of separation of concerns and
| mechanical sympathy should be top of mind when designing a
| lasting and purposeful database schema.
|
| Finally, since folks often say "I like stripe's typed random IDs"
| in these kind of threads: Stripe are lying when they say their
| IDs are random. They have some random parts but when analyzed in
| sets, a large chunk of the binary layout is clearly metadata,
| including embedded timestamps, shard and reference keys, and
| versioning, in varying combinations depending on the service. I
| estimate they typically have 48-64 bits of randomness. That's
| still plenty for most systems; you can do the same. Personally I
| am very fond of base58-encoded AES-encrypted bigserial+HMAC
| locators with a leading type prefix and a trailing metadata
| digit, and you can in a pinch even do this inside the database
| with plv8.
| vbezhenar wrote:
| 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.
| inopinatus wrote:
| Defaulting to 64-bit integers internally is to me a matter of
| mechanical sympathy, it has little to do with row capacity.
| It's just a word size that current CPUs and memory
| architectures like working with.
| vbezhenar wrote:
| What architecture? Both amd64 and ARM64 can work with
| 32-bit integers just fine.
| riku_iki wrote:
| 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..
| njtransit wrote:
| Using 32 bit ints for IDs is insane in today's world. If an
| attacker can control record generation, e.g. creating a
| record via API, then they can easily exhaust your ID space. A
| lot of kernel vulnerabilities stem from using incrementing 32
| bit integers as an identifier. If you're considering using 32
| bits for an ID, don't do it!
| incrudible wrote:
| If an attacker can create billions of records through your
| API, maybe that is a problem you need to address either
| way.
| rangerelf wrote:
| 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.
| vbezhenar wrote:
| Using 2 digits for year is as wrong as using 8 bytes for
| year.
| GGO wrote:
| I dont understand the recommendation of using bigserial with
| uuid column when you can use UUIDv7. I get that it made sense
| years ago when there was no UUIDv7, but why do people keep
| recommending it over UUIDv7 now beats me.
| nextaccountic wrote:
| Why string encoded column? Is it just to make the table bigger?
|
| Why not just use the UUID type??
| netcraft wrote:
| 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 instead of worrying about this,
| spend your energy on a million other things first and then
| celebrate when UUIDs become your bottleneck.
| eerikkivistik wrote:
| I was gonna say... When your system is large enough to run into
| this specific performance bottleneck, pop a bottle and
| celebrate, you are making enough money to solve that problem.
|
| While knowing this information is useful, most services fail in
| different domains and problems way before you reach that point.
| I'm not sure people really comprehend how hard you can hit a
| single machine before you need to distribute a workload.
| paulddraper wrote:
| I haven't maintained any sizable database system without any
| issues, least of all performance ones.
|
| I call BS.
| tomnipotent wrote:
| This gave me a good chuckle, and has generally been my
| experience. Systems grow often in unpredictable or
| unintuitive ways.
|
| You can pay the cost for something upfront, and the cost of
| maintaining it, and in the long term paid too much for
| something you didn't actually need.
|
| Alternatively you can wait to pay it until you're certain you
| need it but the work involved has become much more
| significant, in which it can cost more than it would have to
| have built and maintained it from the beginning.
|
| Compounding the issue is the build-up-front scenario costs
| fade with time and you don't really think about them, but
| build-when-you-need-it always creates a stir even if the
| costs are less overall than build-up-front.
|
| Either way something will go wrong no matter how many times
| you predict where the cards will fall.
| brigadier132 wrote:
| My strategy is to use v4 Uuids for anything that is not inserted
| frequently and don't need to be ordered (think user ids) and v7
| ids for things that are.
|
| If your dataset is small the overhead from Uuids wont matter, if
| your dataset is large the randomness of Uuids will save your ass
| when you migrate to a distributed solution.
| rootedbox wrote:
| Just a heads up about UUID 7.. be careful when using.
|
| par the RFC
|
| If UUIDs are required for use with any security operation within
| an application context in any shape or form then [RFC4122] UUIDv4
| SHOULD be utilized.
| spoiler wrote:
| I was thinking of adopting UUIDv7 for some of my stuff. So, I'm
| curious: why is this the case? Is it because of the time
| component?
| vbezhenar wrote:
| 1. UUIDv7 allows to extract timestamp
|
| 2. UUIDv7 allows to predict first half, if you know the
| timestamp.
|
| 3. UUIDv7 provides 62 bits of randomness compared to 122 bits
| for UUIDv4.
|
| Whether that's a problem for your particular use-case or not,
| it's up for you to decide. I don't think that UUIDv7 is
| "insecure", it just provides different trade-offs and in some
| situations it might be less secure compared to UUIDv4, but I
| hardly see any attack vector where you could issue 2^62
| requests to brute-force the ID.
| lulzury wrote:
| That's what the RFC states:
|
| > Timestamps embedded in the UUID do pose a very small attack
| surface. The timestamp in conjunction with an embedded
| counter does signal the order of creation for a given UUID
| and its corresponding data but does not define anything about
| the data itself or the application as a whole. If UUIDs are
| required for use with any security operation within an
| application context in any shape or form, then UUIDv4
| (Section 5.4) SHOULD be utilized.
|
| https://datatracker.ietf.org/doc/html/rfc9562#name-
| security-...
| vog wrote:
| Note that the article's link to the UUID v7 standard is meanwhile
| outdated. You should instead head directly to RFC 9562:
|
| https://datatracker.ietf.org/doc/html/rfc9562
|
| (which wasn't yet finished at the time of the article)
| londons_explore wrote:
| 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.
| nubinetwork wrote:
| 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.
| sbuttgereit wrote:
| [delayed]
___________________________________________________________________
(page generated 2024-07-05 23:00 UTC)