pg_idkit

Been waiting for UUIDv7 for years -- maybe it's time to archive pg_idkit[0], or maybe instead just switch the UUIDv7 version to do the native thing rather than the Rust code. [0]: https://github.com/VADOSWARE/pg_idkit

If you like this (I do very much), you might also like pg_idkit[0] which is a little extension with a bunch of other kinds of IDs that you can generate inside PG, thanks to the seriously awesome pgrx[1] and Rust. [0]: https://github.com/VADOSWARE/pg_idkit [1]: https://github.com/pgcentralfoundation/pgrx

Just put some elbow grease into the automated release process, so happy to release v0.2.1[0] of pg_idkit! It should be really easy to start/try it out now, assuming you have docker installed: docker run \ --rm \ -e POSTGRES_PASSWORD=replace_this \ -p 5432 \ --name pg_idkit \ ghcr.io/vadosware/pg_idkit:0.2.1-pg16.1-alpine3.18-amd64 And to connect to that instance you can run: docker exec -it pg_idkit psql -U postgres After you're in, generate an ID: SELECT idkit_uuidv7_generate() If you've got a chance to try it out (or there's a reason you can't), would love to hear feedback! Or even better yet, file an issue[1] [0]: https://github.com/VADOSWARE/pg_idkit/releases/tag/v0.2.1 [1]: https://github.com/VADOSWARE/pg_idkit/issues/new/choose

Yup this is one of the reasons I put together a light extension for this: https://github.com/VADOSWARE/pg_idkit There are a lot of options for UUID extensions (lots of great pure SQL ones!), but I wanted to get as many ID generation strategies in one place Also note that native UUID v7 is slated to land in pg17: https://commitfest.postgresql.org/44/4388/

I wouldn't use ULIDs on a new project. The spec doesn't seem like it has much stewardship, the last commit was in 2019 with a bunch of open PRs and issues: https://github.com/ulid/spec/issues I went through this exploration a while back for a new project and decided on uuidv7s, which are binary compatible with ULIDs but will likely find more support as they get added to the original UUID RFC. Either UUIDv7 or XIDs seem like better choices than ULIDs for new projects. * Supabase on different primary key considerations: https://supabase.com/blog/choosing-a-postgres-primary-key * Postgres extension for generating various kinds of IDs: https://github.com/VADOSWARE/pg_idkit

Hey HN, It turns out choosing a good database-optimized UUID (and deciding whether to use serial, etc) isn't quite so simple, and I finally got a chance to do some exploration, write about it[0]. One of the reasons Postgres is the best open source database out there is it's extensibility -- so I hacked up a small extension for generating some of the more exotic (but crucially, lexicograhically sortable) UUID generation mechanisms: https://github.com/t3hmrman/pg_idkit This idea has been bumbling around my head for a while, but I finally got a chance to build it while working with Supabase on a post about IDs[0]! Most of the heavy lifting is done by pgx[1] which is an amazing framework for building Postgres extensions in Rust. I think we are very early to the trend of amazing postgres extensions built in Rust, and it's yet another reason th

This article comes with a complementary postgres extension which we wrote to benchmark various UUIDs: 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;