4 ms·

Show HN: pg_idkit, a Postgres extension for generating exotic UUIDs

hardwaresofton 4y ago
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 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 that it's an exciting time to be all-in on Postgres. [0]: https://supabase.com/blog/choosing-a-postgres-primary-key https://supabase.com/blog/choosing-a-postgres-primary-key [1]: https://github.com/tcdi/pgx https://github.com/tcdi/pgx
- Existenceblinks 4y ago
  Are they index-able? I mean the same perf as pg uuid.
  - Tostino 4y ago
    You still use the uuid storage type, so all existing indexes that work should be fine. This is about the generation portion.
    - hardwaresofton 4y ago
      This lib actually generates text right now, not uuid. I plan on adding UUID generation (under a different function name) for schemes that support it, but the problem is that not all schemes translate cleanly to a postgres uuid type.
  - hardwaresofton 4y ago
    The types are text -- if you use a generation mechanism that is convertable to uuid then you can coerce the values to the uuid type. I think it's rare to have the kind of case where the inefficiency of indexing text vs uuid actually matters -- usually sorting is a bit more important.
    - nextaccountic 4y ago
      Does this mean that (for example) an ULID won't take 16 bytes, but instead 26?
      - hardwaresofton 4y ago
        ULID in particular is fine -- as it is compatible with UUID (it can relatively easily be converted to something that looks like a UUID), but others are not (ex. nanoid which is variable). I wanted the extension to be consistent in what it outputs for the default function scheme (idkit_<something>_generate). Arguably `idkit_<something_generate_text` is more explicit and thus clearer but generate and generate_uuid aren't terrible either.

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 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 that it's an exciting time to be all-in on Postgres. [0]: https://supabase.com/blog/choosing-a-postgres-primary-key https://supabase.com/blog/choosing-a-postgres-primary-key [1]: https://github.com/tcdi/pgx https://github.com/tcdi/pgx

Existenceblinks 4y ago
Are they index-able? I mean the same perf as pg uuid.
- Tostino 4y ago
  You still use the uuid storage type, so all existing indexes that work should be fine. This is about the generation portion.
  - hardwaresofton 4y ago
    This lib actually generates text right now, not uuid. I plan on adding UUID generation (under a different function name) for schemes that support it, but the problem is that not all schemes translate cleanly to a postgres uuid type.
- hardwaresofton 4y ago
  The types are text -- if you use a generation mechanism that is convertable to uuid then you can coerce the values to the uuid type. I think it's rare to have the kind of case where the inefficiency of indexing text vs uuid actually matters -- usually sorting is a bit more important.
  - nextaccountic 4y ago
    Does this mean that (for example) an ULID won't take 16 bytes, but instead 26?
    - hardwaresofton 4y ago
      ULID in particular is fine -- as it is compatible with UUID (it can relatively easily be converted to something that looks like a UUID), but others are not (ex. nanoid which is variable). I wanted the extension to be consistent in what it outputs for the default function scheme (idkit_<something>_generate). Arguably `idkit_<something_generate_text` is more explicit and thus clearer but generate and generate_uuid aren't terrible either.