5mins of Postgres E59: UUIDs vs Serial for Primary Keys - what's the right choice?

Показать описание

Today, we talk about deciding whether to use UUIDs vs bigints for your primary key.

*Find the article on our blog here:*

*Learn more about pganalyze:*

*Check out the pganalyze library for eBooks, webinars, and more:*

📑 *What we have discussed in this episode of 5mins of Postgres:*

*UUIDs vs serials for keys - by Christophe Pettus*

*Christophe Pettus on Twitter*

*Identity Crisis: Sequence v. UUID as Primary Key - by Brandur*

*Brandur on Twitter*

*Postgres Hackers thread about UUIDv7*

*5mins of Postgres E58: Handling integer sequence overflow without downtime in Postgres*

*pganalyze newsletter issue of April 2022 about ULIDs*

*5mins of Postgres E10: max_wal_size, Postgres full page writes and UUID vs BIGINT primary keys*

*Effective indexing in Postgres - eBook*

Рекомендации по теме

Комментарии

You could use a sequential key as primary key and then have a uuid as just a property. That way you can use the index in relations / server side stuff, and the uuid’s with client interactions

luca

Here in the Netherlands the tax people require that your order numbers (well, the invoice numbers at least)) are consecutive, if you do have gaps then you have to account for the gaps as they see gaps as potential fraud. Random numbers are therefor simply not an option.

One downside to UUID's that have made me shy away from them is that they are impossible to reliably pass form one person to another if you cannot copy/paste them. The length and composition simply make it impossible to reliably transfer more than a handfull, which is really really bad when you have a customer on the phone complaining about a number of orders that need to be fixed *NOW*.

As for distributed systems, al you need is a way to make sure none of the systems generate the same number. A prefix will do that.

vinny

I am trying ULID, it goes like using ULID and then converting it to UUID. So it can be sequential and yet unique and able to store in the UUID field

stephenljk

Currently spending the next 6 weekends purging data from a large complex 3nf database with UUID FKs between all of the tables. On an extremely large instance(192G Ram, 48 vCPUs) I'm getting <31 deletes/sec with all the ugly UUID FK lookups.

awksedgreep

5mins of Postgres E59: UUIDs vs Serial for Primary Keys - what's the right choice?

5mins of Postgres E59: UUIDs vs Serial for Primary Keys - what's the right choice?

UUIDs vs Serial for Primary Keys - what's the right choice? #postgres #postgresql

5mins of Postgres E10: max_wal_size, Postgres full page writes and UUID vs BIGINT primary keys

Enable uuid in PostgreSQL

Which is better? UUID or Int?

Stop Using UUID!

Scaling Postgres Episode 102 UUID Use Cases | pg_settings | Automated Restore | Parallel Future

Databases: How should I index a UUID in Postgres? (3 Solutions!!)

Better Than GUID - Have You Heard Of ULID Before? #shorts

#UUID Datatype #Primary Key in #PostgreSQL Universally Unique Identifier

UUIDs are Bad for Performance in MySQL - Is Postgres better? Let us Discuss

Unconventional ways to index UUIDs in PostgreSQL | Citus Con: An Event for Postgres 2023

UUID | Postgres.FM 051 | LIVE episode

30% Faster, UUID Downsides, Growing WAL, Processes vs. Threads | Scaling Postgres 271

Using UUID as a primary key for Spring Boot and Postgres database #tutorial

5mins of Postgres E44: Reducing replication lag with maintenance_io_concurrency in Postgres 15

Database Auto increment vs UUID - Which is Right for You?

ULID vs UUID: Which One Should You Use?

MODERN way to generate GUID/UUID in JavaScript #javascript #shorts

5mins of Postgres E1: Using Postgres statistics to improve bad query plans, pg_hint_plan extension

5mins of Postgres E34: Making the most of Parallel VACUUM and why SQL/JSON was pushed to Postgres 16

How to create a UUID or GUID in Python #shorts

UUID no postgreSQL - Ter um id UUID em vez de um ID numérico em seu banco

UUID: Universally Unique Identifiers v4 and v7 design