4 ms·
Postgres supports UUIDs natively
by letitbeirie 3y ago
Postgres supports UUIDs natively
- unshavedyak 3y agoIs 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?
- code-e 3y agoIt 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.
- lolinder 3y agoULIDs are byte-compatible with UUIDs, so the only thing Postgres's native support gives you over ULIDs is that Postgres can generate new UUIDs for you instead of having to do it in the application before insertion.
- letitbeirie 3y agoIt also buys you that support in the driver libraries. I ran into this last year - storing them as UUIDs works great, unless all of the drivers for the language you're using (Go, in my case) try to cast/validate those bytes as a UUID before you can access them.