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

231–240 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#231

Post by rezonant »

iterateoften wrote: post #221
badestrand wrote: post #186

Earlier quoted context omitted.

You never expose the bigserial, you generate a ID (like UUID) for external use/identification and simply have an index over that column for fast selects.

If you have an index on the uuid anyways having a separate big serial field for PK doesn’t help that much.

As mentioned elsewhere, it ensures the ability to perform resumable and consistent batching queries across the data set without missing records.

Ordering over an insertion timestamp is not enough if two records may have the same timestamp: You may miss a record (or visit a record twice) across multiple queries.

Re: PostgreSQL and UUID as Primary Key

#232

Post by shi »

samokhvalov wrote: post #15
aabhay wrote: post #8

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

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

This is a valid point but it highly depends on the use case and larger context on whether this will be relevant or not. If you have a table where you fetch multiple rows close to each other in paginated manner this would be relevant for the performance but if you only fetch individual records by uuid, data locality wouldn’t increase the performance.

Re: PostgreSQL and UUID as Primary Key

#233

Post by orf »

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.

This is simply bad advice. There are very, very few cases where you shouldn’t use a bigserial/bigint as a primary key. Perhaps the only case is where it is impossible for the rows to grow beyond a specific bound: an “AWS region” table or a “countries” table. These are almost always lookup tables of some kind, and the domain makes it impossible for it to grow past a certain bound.

Outside of that, put absolutely no thought into it and pick a bigint/bigserial.

Trying to be clever about this ignores the fact that tables and their usage change, and what you believe now will not be correct later.

And when you realise you’ve been too clever, it’s 3am on a Sunday, your production database has exploded and nothing works. You now need to rewrite 30 tables and their foreign keys ASAP with upper management breathing down your neck.

Re: PostgreSQL and UUID as Primary Key

#234

Post by klabb3 »

sbuttgereit wrote: post #101
klabb3 wrote: post #49

Earlier quoted context omitted.

What database support is needed? Assuming Postgres already has the uuid type and that you can (and often should) do the actual generation of them in application code?

> you can (and often should) do the actual generation of them in application code I can absolutely see scenarios where it would be desirable or convenient to create the these IDs in the application code. However, I can equally assert that you can, and often should, let the database be the source of truth for record identity when using database surrogate keys for that purpose... but I won't. The truth is there are man…

> I can absolutely see scenarios where it would be desirable or convenient to create the these IDs in the application code.

I feel like I’m taking crazy pills. Isn’t the whole (or 99% of the) point of uuids that you can generate them client/application side for idempotency, because entity creation starts there and you want an id earlier than the first round trip completion? If they’re generated by the db (with world-knowledge) then you can auto increment a 64 bit is significantly more performant.

Re: PostgreSQL and UUID as Primary Key

#235

Post by nextaccountic »

fabian2k wrote: post #3

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…

Postgres doesn't automatically reorder rows by clustering on a primary key but you can perform clustering manually, and if the table isn't completely filled it can attempt to maintain rows in order (as long as there is room for it)

https://www.postgresql.org/docs/current/sql-cluster.html

Re: PostgreSQL and UUID as Primary Key

#236

Post by asah »

kroolik wrote: post #229
groestl wrote: post #228

Earlier quoted context omitted.

Ha? Please elaborate.

When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range

would creation/lastmod timestamps cover this requirement?

Re: PostgreSQL and UUID as Primary Key

#237

Post by presentation »

saaspirant wrote: post #130
sarreph wrote: post #126

We chose ULID for our Postgres PK recently, and this article helped a lot in making that decision: https://brandur.org/nanoglyphs/026-ids I personally prefer ULID since it is compat with a UUID type and you also get a timestamp lexicographically built into the ID so that sorting by ID also means sorting by timestamp. There are multiple PG extensions to make it easy to drop in and use.

How do you deal with ulid exposing the timestamp (since is lexicographically sortable) ? Maybe your ULID is not public facing? Or this is not an issue for your application? I want to use something url friendly too since uuid sucks..

You could probably just use random uuids and then encode/decode them to ULID whenever you read/write them.

Re: PostgreSQL and UUID as Primary Key

#238

Post by cqqxo4zV46cp »

thiht wrote: post #200
whynotmaybe wrote: post #70

Earlier quoted context omitted.

As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the t…

I don’t understand how that’s an issue. Do you have an example of a possible attack using UUIDv7 timestamp? Is there evidence of this being a real security flaw?

I don’t understand this thinking. If you understand what’s at play, you can infer the potential security implications. What you’re advocating for is being entirely reactive instead of also being proactive.

Re: PostgreSQL and UUID as Primary Key

#239

Post by withinboredom »

wodenokoto wrote: post #178
inopinatus wrote: post #171

Earlier quoted context omitted.

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.

It's nice when you change invoicing software, to be able to have gaps. For example, before Stripe is invoice 500. This makes it simple for humans to determine where an invoice may be located during the transition year. Further, it means we can plan the entire switch-over in advance, vs. only knowing the invoice number AFTER the switch-over. This makes a huge difference in internal communications to customer support, because you can let them know how things will operate once the switch is done. If you can't have gaps, you won't know how to tell customer support where to find new/old invoices until after the switch.

Re: PostgreSQL and UUID as Primary Key

#240

Post by GiorgioG »

UUIDs are miserable to work with. For 99% of use cases, sequential IDs are fine. Can they be guessed? Sure, but your software should guard against unauthorized access. Security through obscurity or randomness is a poor excuse for using UUIDs as PKs. If you don't want to expose your IDs, use a slug.
Post reply on HN