Choosing a Postgres Primary Key
supabase.com
supabase.com
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;* 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