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

331–340 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#331

Post by lazide »

sgarland wrote: post #241
karmajunkie wrote: post #174

Earlier quoted context omitted.

i mean, you might care if the investors you’re trying to woo for that bridge round figure out your churn is a lot higher than you’re willing to admit… or worse, your traction is terrible.

How is a monotonically incrementing integer going to reveal churn or traction? You’re not reusing IDs.

Sign up for two accounts over the space of, say, a month.

See how big the delta is between the account IDs.

That gives you potential traction.

If you have access to revenue data (every investor is going to want this first thing), that gives you rough churn too.

Do the same for creating users, if you can see user Id’s.

Re: PostgreSQL and UUID as Primary Key

#332

Post by tinodb »

withinboredom wrote: post #239
wodenokoto wrote: post #178

Earlier quoted context omitted.

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

In the Netherlands gaps aren’t allowed either, and I’m surprised that it is elsewhere, as that allows to you get rid of unwanted invoices whenever you want.

However you are allowed to have multiple sequences, differentiated through a prefix, but all starting at 0. That’s what we recently did to switch invoice generation tools (we actually still run both of them alongside each other atm).

Of course you could still drop some invoices from the end when you do this, but I guess tax authorities accept that risk.

Re: PostgreSQL and UUID as Primary Key

#333

Post by necovek »

mjevans wrote: post #153
necovek wrote: post #141

Earlier quoted context omitted.

I've never played enough with UUIDs in Postgres, but I wonder if you could publicly expose only the random bits (i.e. the second half if an UUIDv7) and have another non-b-tree index on SUBSTR(id, 16) for quick lookups based on that value. Similar is done for "short commit hashes" too. Though I would wonder why go with UUIDs in that case at all?

Offhand, I remember _reading_ about that but haven't ever used it in practice so please test and confirm... # Postgres can Index Function Results (including what you described) # Postgres does not magically know to USE those results. To use them you must E.G. JOIN (probably works for WHERE?) or ORDER or LIMIT by the same parameter that went into the Index (which is how it's matched up). Generally, the Primary Key sho…

Yeah, it works for "partial indexes" very well. There are limits to what gets matched, for sure.

In the old times, if you created a "partial index" with a condition of "field_foo IS TRUE" it would not match queries asking for "field_foo = TRUE" (it did actual text matching). Probably some of that is improved today.

Re: PostgreSQL and UUID as Primary Key

#334

Post by necovek »

mythrwy wrote: post #323
necovek wrote: post #133

Earlier quoted context omitted.

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.

Thanks: I guess we've moved away from true sources of entropy for RNG and it's mostly pseudo-RNGs seeded by truly random numbers if we can achieve such performance today.

I've done a quick benchmark of /dev/urandom locally, and I get ~450 MB/s out, which is roughly 28M worth of UUIDs per second — on my Ryzen 6850U laptop. Even that amounts to sub-microsecond times.

I distinctly remember a time where you could only get a few MB/s, and external sources of true entropy were outright expensive for anything more than a few KB/s.

Re: PostgreSQL and UUID as Primary Key

#335

Post by tinodb »

bob1029 wrote: post #220

I see some comments conflating privacy of sequence statistics with global uniqueness considerations and UX. If your concern is globally unique identifiers (i.e. so that you can merge tables across multiple instances of your database), then UUID is exactly what you want. This is entirely what it is designed for. If your concern is the privacy of sequence statistics, then UUID incidentally solves your problem. It may n…

Most of the comments here seem to gear towards “you must always…”. But as you rightfully point out, “it depends” and is an architectural trade off depending our the required qualities.

Re: PostgreSQL and UUID as Primary Key

#336

Post by sarreph »

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

I don’t have a problem with people knowing the timestamp - it’s only precise down to the second.

Re: PostgreSQL and UUID as Primary Key

#337

Post by withinboredom »

tinodb wrote: post #332
withinboredom wrote: post #239

Earlier quoted context omitted.

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

In the Netherlands gaps aren’t allowed either, and I’m surprised that it is elsewhere, as that allows to you get rid of unwanted invoices whenever you want. However you are allowed to have multiple sequences, differentiated through a prefix, but all starting at 0. That’s what we recently did to switch invoice generation tools (we actually still run both of them alongside each other atm). Of course you could still dro…

The prefixes have to be in order though. You cannot start a prefix with A after already using prefix starting with B.

Re: PostgreSQL and UUID as Primary Key

#338

Post by westurner »

Merad wrote: post #318
westurner wrote: post #315

Earlier quoted context omitted.

What needs to be stored as text if there is a native uuid type? Chapter 8. Data Types > Table 8.2. Numeric Types: https://www.postgresql.org/docs/current/datatype-numeric.htm... : > bigint: -9223372036854775808 to +9223372036854775807 > bigserial: 1 to 9223372036854775807 2*63 == 9223372036854775807 Todo UUID /? postgres bigint UUID: https://www.google.com/search?q=postgres+bigint+uuid : - UUIDs are 128 bits, and the…

Many people store UUID's as text in the database. Needles to say, this is bad. TFA starts by proposing that it's bad, then does some tests to show why. I'm not quite sure what all the links have to do with the topic at hand.

Which link are you concerned about the topicality of, in specific?

Shouldn't we then link to the docs on how many bits wide db datatypes are, whether a datatype is prefix or suffix searchable, whether there's data leakage in UUID namespacing with primary NIC MAC address and UUIDv7, and whether there will be overflow with a datatype less wasteful than the text datatype for uuids when there is already a UUID datatype for uuids that one could argue to improve if there is a potential performance benefit

Re: PostgreSQL and UUID as Primary Key

#339

Post by stouset »

groestl wrote: post #278
stouset wrote: post #271

Earlier quoted context omitted.

I’m talking about default choices. UUIDv7 has few significant downsides for most cases. UUIDv4 has serious downsides. There are obviously cases where the latter is appropriate but that will come from unique requirements.

How about secure by default?

“Secure” is only meaningful against a defined threat model.

Most threat models for database IDs do not require their creation timestamp to be secret. Meanwhile every use case for database IDs requires them to be looked up in an index.

Re: PostgreSQL and UUID as Primary Key

#340

Post by rjurney »

openthc wrote: post #142
rjurney wrote: post #138

I've started using python-ulid as ULIDs seem superior to UUIDs. https://pypi.org/project/python-ulid/

I love ULID too; but it's really just UUIDv7 (or v8) with a mustache (it's all just 128bit IDs). And in the PG world, where there isn't native support for ULID one can use UUIDv7/8 and bridge that gap. We use ULID extensively in exposed IDs, it's very URL friendly, copy/paste friendly, etc -- but it's a UUID datatype in the DB. (ie: ULID => UUID => DB)

But I thought ULID will work in a UUID field in Postgres? Will it in other DBs?
Post reply on HN