4 ms·
Choosing a Postgres Primary Key
- kiwicopple 4y agoThis article comes with a complementary postgres extension which we wrote to benchmark various UUIDs: https://github.com/t3hmrman/pg_idkit https://github.com/t3hmrman/pg_idkit In case you don't read the article, my favourite idea is using the `ksuid` type as both an ID and a *created_at* field: > One interesting feature would be using at least partially time-based UUIDs for created_at columns -- we could save space by virtualizing our created_at columns: CREATE TABLE users ( id text PRIMARY KEY DEFAULT idkit_ksuid_generate(), name text, ); SELECT *, idkit_ksuid_extract_timestamptz(id) FROM users;
- kiwicopple 4y agoAlso the conclusion for those who skip to the comments: * integers and serial have obvious benefits for simplicity, storage, and sortability. You might not want to expose them to the world though. * If you want the ultimate in collision avoidance UUIDv4 is OK * UUIDv1 could have been great, but it doesn't lexicographically sort. * The best time-based ID seems to be xid, with good performance and sort friendliness * If you want to be a little more standards-oriented, UUID v6/v7