Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

cybertec-postgresql.com

Post reply on HN
Print view View original

151–160 of 182 posts

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#151

Post by pritambarhate »

codeflo wrote: post #143
pritambarhate wrote: post #129

A little late to comment here. But for database IDs, I have found that Instagram's technique to generate IDs works very well: https://instagram-engineering.com/sharding-ids-at-instagram-... They are not serially incrementing but still sortable. Thus prevent index fragmentation issues observed with UUIDS. Are 8 bytes in length. So index size is smaller compared to UUIDs. So you get all benefits of serial IDs but they…

> they are not easily guessable I don't see how that's true. From reading the article you linked, you only need a valid shard ID (which you can extract from known IDs), the millisecond (which is guessable) and a 10-bit sequence (which you can easily brute-force). (And that's completely fine if their security model doesn't require unguessable IDs.)

>> which you can easily brute-force

It will results in a very high number of 404s. These can be monitored and the origin IPs can be banned.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#152

Post by squiggleblaz »

ccleve wrote: post #91
inopinatus wrote: post #61

Earlier quoted context omitted.

Caveat programmer: this could be problematic, not in the sense it doesn't work, but in the sense that someone working on backend code may have a preconceived expectation that UUIDs are also effectively a keyspace i.e. they're hard to guess. The validity of that is already challenged by variants defining temporal or logical order, and evaporates completely if you let clients declare their own that you accept at face v…

In Java there is a UUID generator based on SecureRandom. That's about as unguessable as you're going to get.

It's not a question of whether UUIDs can be generated unguessably. They can be, as you point out.

It's whether the UUIDs in your system can be reliably presumed to be unguessable - including the UUIDs that were generated by code which was written after you wrote your query that assumes unguessability.

Today you might say "Oh, this SecureRandom-based UUID generator is unguessable and meets all of our requirements". Tomorrow you might say "Ah, this SecureRandom-based UUID generator is too slow, let's generate them in our Android app instead of on the server". But now the UUIDs stored in your database aren't reliably unguessable, because you accept whatever your client API tells you without verification. How plausible is it, within the timeframes you actually get, to review every query for whether it assumes the trustworthiness of the UUID generation? Better to assume UUIDs have some convenient properties, than to assume that they're unguessable just because the API is cryptographically secure today.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#153

Post by skeletal88 »

Cthulhu_ wrote: post #141
the_gipsy wrote: post #139

Earlier quoted context omitted.

In Spain each person has a unique ID number assigned at birth. The numbers for newborns are geographically pre-distributed to guarantee uniqueness despite delay in paperwork. It is universally accepted that this ID "number" (it actually has one letter too) is all you need to identify yourself, ever. Except that I knew a coworker who had a duplicate ID. An extremely rare event, they messed up the pre-assignment and th…

The fact that it's fixed / can never be changed is a massive problem with social security numbers. That, and the fact it's often used as authentication instead of identification. They're moving away from that slowly, but it's taking a lot of time and effort.

The problem with ssn is specific to the US. Other countries have sane ways of authenticating citizens and the personal id number is just used as a global foreign kes for all government or public or bank database where you need to uniquely identify a citizen.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#154

Post by squiggleblaz »

efxhoy wrote: post #144
eric4smith wrote: post #136

Earlier quoted context omitted.

Note I said "internal use". But how can primary keys result in incorrect joins? Unless you're changing a foreign key, joins will always be correct. Unless I'm doing something wrong in the last 30 years of using SQL.

If you use serial integer ids and accidentally join on the wrong tables/columns you will get rows back even if the join doesn't make sense, because all serian integer ids have values in common. If you're using UUIDs you will "never" get rows back when joining on the wrong ids and spot your mistake.

Value safety instead of type safety. It's reasonably likely to work; even if you can't trust the UUIDs to be generated by a trustworthy source, when you test the query it'll be wrong even if you accidentally join against a table that has a sufficiently similar schema.

I'm more familiar with MySQL where I don't have anything other than basic, automatically converted types.

But in Postgres, where there's more types, can I create a type that is representationally equivalent to a builtin type but not automatically convertable? So that if I say "select * from donkey left join plant on plant.id = donkey.animal" it says "error: cannot compare PlantId with AnimalId, use an explicit cast if you really want to do this". If this were possible and ergonomic, it would be better than a relatively cryptic null in the case that plants and animals have distinct (UUID) PKs.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#155

Post by rossmohax »

pm90 wrote: post #90
rossmohax wrote: post #6

Another alternative is ULID, which can be stored as UUID on a Postgres side, but is more b-tree friendly.

Are there articles/examples on how to use ULIDs in postgres?

ULID and UUID are same size, passing ULID as UUID to PostgreSQL works seamlessly.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#156

Post by eric4smith »

efxhoy wrote: post #144
eric4smith wrote: post #136

Earlier quoted context omitted.

Note I said "internal use". But how can primary keys result in incorrect joins? Unless you're changing a foreign key, joins will always be correct. Unless I'm doing something wrong in the last 30 years of using SQL.

If you use serial integer ids and accidentally join on the wrong tables/columns you will get rows back even if the join doesn't make sense, because all serian integer ids have values in common. If you're using UUIDs you will "never" get rows back when joining on the wrong ids and spot your mistake.

I think if you're joining against wrong tables or columns, then you have bigger problems than if the values are BigInt or UUID's.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#157

Post by codeflo »

pritambarhate wrote: post #151
codeflo wrote: post #143

Earlier quoted context omitted.

> they are not easily guessable I don't see how that's true. From reading the article you linked, you only need a valid shard ID (which you can extract from known IDs), the millisecond (which is guessable) and a 10-bit sequence (which you can easily brute-force). (And that's completely fine if their security model doesn't require unguessable IDs.)

>> which you can easily brute-force It will results in a very high number of 404s. These can be monitored and the origin IPs can be banned.

2^10 is 1024, so hundreds of requests. Not a very high number. And since it’s a counter, even less. Easy to disguise; the official app is likely to do more requests in a one-minute session. And obviously, sophisticated attackers aren’t limited to one IP.

I’m suspecting you meant “easily guessable” in the human sense, not the cryptographic/security sense. My bad if I misunderstood you. Again, I’m not saying Instagram has any security problem, I’m just saying that this ID scheme in particular isn’t a security feature.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#158

Post by BatteryMountain »

hardwaresofton wrote: post #149
BatteryMountain wrote: post #146

Earlier quoted context omitted.

The problem is when reading the data back with Dapper (writing works fine) and it has to parse the guid stored in sqlite back into a .net Guid. Thus I resorted to making the column type text and ToString() all the Guid's before writing, but that's pretty gross. With a normal string field, sqlite can set it as primary key, make unique indexes on it, use it as foreign keys. Thus my attempt to use my own generator the g…

Ahh thanks for noting this -- I've written off writing C# and PHP for the rest of my life for reasons, but this is a fascinating read.

On the other hand, C# + postgres is a super sweet combo. I don't use EntityFramework, so I don't know how well they play together, but if you write sql queries and run them from c#, it's super nice. I typically have a generic repository class that generates all the statements and it works great (I recently made the query generator able to generate inner join if I pass it filters that are not found on the table/object, based on a naming convention (any property that end in "Id" that is not the main entity Id and not a direct property, use the part before the "Id" part as the join table name) - it works great too!).

I also cache certain prepared statements in a static concurrent dictionary, so I can short circuit if the it the query/insert/update has been run before for the specific object type. That way all of the string manipulation can be avoid for each query - I just use a previous statement but with different sql parameters. On every deployment the cache gets cleared because the app basically get restarted, so it won't go stale. It's very cool (at least in my mind). The only indirect rule I have is that nothing else should modify the main schema besides my migrations and not while the app is using it (using FluentMigrator code that execute on app startup).

If I may ask, why don't you like C#? I totally get the php thing - I cannot stand it at all either.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#159

Post by weird-eye-issue »

staticassertion wrote: post #147
weird-eye-issue wrote: post #100

Earlier quoted context omitted.

How is that easier to maintain than literally just having a UUID primary key? I feel like we must be talking about different use cases

If your goal is to use the uuid as a delegated capability it's going to be much more complex to use the primary key for your row than to use a separate key.

Yeah we are talking about different use cases entirely

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#160

Post by FractalHQ »

hermanradtke wrote: post #27
magicpointer wrote: post #5

About UUID as Primary Key and performance, the following article has some insights and benchmarks as well: https://www.2ndquadrant.com/en/blog/sequential-uuid-generato... Essentially, they observed sizeable performance improvements by using UUID generators that are tweaked to get more sequentia resultsl. It results in better indexes. The articles compares sequences, random UUIDs and 2 kinds of sequentialish UUID gene…

I use a ulid[1] as a uuidv4 replacement: https://github.com/ulid/spec

Would you recommend this over https://github.com/ericelliott/cuid ? I use cuid with Postgres and it’s worked out great.
Post reply on HN