Add pgx_ulid extension - Increase Our Performance With ULID Type

[bitnom]

This feature request is for the pgx_ulid extension. I really like using UUID for primary keys but we all know the drawback of performance. This extension will mitigate a significant percentage of that.

There is a related feature request for pg_idkit but it seems that extension would not provide nearly the same level of benefit (generally).

Please see below.

From the README:

There are several different postgres extensions for [ulid][], but all of them have feature gaps. A good extension should have:

• Generator: A generator function to generate [ulid][] identifiers.
• Binary: Data be stored as binary and not text.
• Type: A postgres type ulid which is displayed as [ulid][] text.
• Uuid: Support for casting between UUID and [ulid][]
• Timestamp: Support to cast an [ulid][] to a timestamp
• Monotonic: Support [monotonicity][]

Name	Language	Generator	Binary	Type	UUID	Timestamp	Monotonic
pgx_ulid	Rust	✔️	✔️	✔️	✔️	✔️	✔️
pgulid	PL/pgSQL	✔️	❌	❌	❌	❌	❌
pg_idkit	Rust	✔️	❌	❌	❌	❌	❌
uids-postgres	Rust	✔️	⁉️1	❌	⁉️2	❌	❌
pgsql_ulid	PL/pgSQL	❌	⁉️1	❌	✔️	❌	❌
pg-ulid	C	✔️	❌	❌	❌	❌	❌
ulid-postgres	PL/pgSQL	✔️	❌	✔️	❌	✔️	❌
pg_ulid	Go	✔️	❌	❌	❌	✔️	❌
pg_ulid	C++	✔️	⁉️1	❌	✔️	❌	❌

Why should I use ulid over uuid?

The main advantages are:

• Indexes created over ULIDs are less fragmented compared to UUIDs due to the timestamp and [monotonicity][] that was encoded in the ULID when it was created.
• ULIDs don't use special characters, so they can be used in URLs or even HTML.
• ULIDs are shorter than UUIDs as they are comprised of 26 characters compared to UUIDs' 36 characters.

This extension is approximately 30% faster than both pgcrypto's UUID and pg_uuidv7's UUIDv7 when generating a million identifiers.

Footnotes

1. You can convert the [ulid][] into uuid or bytea and store it like that. ↩ ↩² ↩³

2. Supports casting indirectly through bytea. ↩

[FelixZY]

It might be possible to translate ulid-mssql to use a postgres dialect and get this running until the more performant extension is officially supported.

[bitnom]

you mean indexing a text field of it?

[erolabzait]

AWS Appsync has Ulid built in for DynamoDB, this will help the transition, i really want to try Supabase for new projects.

As a case against UUID watch this 2 videos:

• https://www.youtube.com/watch?v=a-K2C3sf1_Q
• https://www.youtube.com/watch?v=f53-Iw_5ucA

[inian]

Please upvote the extension in our Postgres extensions thread here. It helps us prioritise better among all the extension requests we receive