add pg_idkit support

[gperdomor]

Feature request

Additional context

Based on supabase blog post, would be nice having this extension in subabase cloud instances

Related to supabase/postgres#318

[kiwicopple]

Edit: 2024-02

This is available now as a pure plggsql extension:
https://github.com/kiwicopple/pg-extensions/tree/main/pg_idkit

The other ID kit is written in Rust and we don't yet have pl_rust on Supabase (because it isn't stable enough to offer for most customers yet). This version is pure SQL so you can install it simply by pasting the SQL code in your database

---

thanks @gperdomor - I'll convert this to a discussion since it's also opened on the postgres repo

[gperdomor]

Any update relate for this? 😄

[ozum]

It would be beneficial to support modern, standard-based, and future-proof keys. AFAIK, it does not have any negative impact on Supabase instances.

[logemann]

unfortunately the needed dbdev extension install script is also broken. Filed a bug at dbdev. What a rabbit hole. I read the blog about better UUIDs, then decided to go on with UUID v7, realized rust extension not available, realized the pure SQL port cant be installed, and so on.

[peterkogo]

Admittedly, I was little disappointed after reading through the great blog post just to find that the extension is not supported 😄
+1

[wemgl]

I just read through the entire blog post, and was ready to get some UUIDv7 IDs setup with pg_idkit, only to discover this discussion while trying to figure out how to get it working. I've been experiencing a few small disappointments like this with Supabase over the past few weeks that are really starting to add up.

[kamto7]

Is there any other alternative as of today, I need to use pg_idkit

[sampl]

Are there any other ways to generate uuid v7's in Supabase Postgres?

Admittedly, I was little disappointed after reading through the great blog post just to find that the extension is not supported

Me too!

[Lan-Hekary]

This is a very useful extension any news about adding it to Supabase yet ?
Here is another one what would be useful too https://github.com/pksunkara/pgx_ulid as it implements UILD in a very good way.
Also for anyone interested ion using XID this implementation is in plpgsql and is wasy enough to setup until they add a proper extension.

[sampl]

Here's a copy/paste-able uuid v7 and v8 sql extension: https://gist.github.com/kjmph/5bd772b2c2df145aa645b837da7eca74

[Tonku]

can I use this in auth.users table for generating user id?

[bitnom]

should be able to. performance will probably suck compared to native so don't rely on it for generating many rows quickly

[nCrafts]

This would be really, really nice to have

[ozum]

Kindly reminder. Please add this extension.

[Tonku]

How can I use uuid 7 in the auth.users table ? I have the uuid v7 function , modifying the table dent work.
create table
auth.users (
instance_id uuid null,
id uuid not null default uuid_generate_v7 (),
aud character varying(255) null,

[Monu18765]

What??? you guys wrote a blog without adding any support.

[iveshenry18]

Seconding this—you specifically recommend this in this blog post. Very bad form not to support it.

[rhyek]

yeah, i don't get this. i'm going to be generating the uuid in application code for now, but given the blog post and how old this discussion is it baffles me the lack of support. when creating a new table using the web UI for local development and specifying type uuid for a new table the recommended default value given is to use uuid v4, when the blog post specifically says it provides terrible indexing performance.

[arobert93]

Any news on this?

[CAJazzer]

Ditto. I have used uuid_v7 in other stacks for the obvious reasons. Hoping to see this soon in Supabase.

[bitnom]

Anyone interested in uuid please also see this related request: https://github.com/orgs/supabase/discussions/19499

I like using uuid as primary key but there is a penalty. pgx_ulid seems like the best way to mitigate as well as having other benefits.

[iwasrobbed]

Anyone interested in nanoids can use a SQL version directly: https://github.com/viascom/nanoid-postgres

[iwasrobbed]

And update: apparently this breaks real-time websocket listeners... very strange bug

Switching back to uuidv4 fixed it again oddly enough :(

It doesn't parse the ids correctly and breaks inserts and deletes (e.g. here where the id wasn't a number but a nanoid that started with 8)

[inian]

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

[fnimick]

For anyone else reading, please consider this alternative which adds a data type for ulid to prevent invalid data insertions: https://github.com/orgs/supabase/discussions/679#discussioncomment-5272444