Unexpected downsides of UUID keys in PostgreSQL

Re:Unexpected downsides of UUID keys in PostgreSQL by unshavedyak on Thursday June 22, 2023 @11:29AM (#36433671)

Is that worth the pain of dealing with randomized inserts? I guess i just don't mind creating a ULID (or i guess UUIDv7 is newly proposed and sortable) and inserting that.

Native DB support is irrelevant to me for randomized bits unless it affects storage, sorting, paging, etc. Does it?

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by code-e on Thursday June 22, 2023 @02:12PM (#36436366)

It does affect storage and sorting. A native UUID type uses 16 bytes. The alternative is text encoding (32 bytes for hex), or maybe a raw BYTEA. Postgres also has SortSupport for the UUID type, which basically means if the first 8 bytes only has one matching row, then the remaining 8 bytes can be skipped. Combine that with a ULID where the most first half is basically a timestamp, you'll get performance close to using a single 8byte BIGSERIAL.

You can also write a plpgsql function to generate these ULIDs in the database.

[ Parent | on HN ]