UUID vs bigint for users table
I’m trying to decide on format for the private key of my public.users table (and subsequently all tables that reference the users with a foreign key).
Let’s simplify and say I’m choosing between int8 and UUIDv4. The Supabase docs has a great article that concludes that integer is the most performant. But it doesn’t mention any considerations around row level security, which seems to require UUID (or email) from the users.auth() table.
Is it a choice between either best performance or access to RLS policies? Am I thinking about this right?
9 Replies
Can you point at article that talks about the difference?
It is general not considered enough to worry about from my reading, Especially if you then have to always do a join to check the jwt UUID on a table versus just use the auth.uid() as the primary key for your user_id columns to reference.
It is general not considered enough to worry about from my reading, Especially if you then have to always do a join to check the jwt UUID on a table versus just use the auth.uid() as the primary key for your user_id columns to reference.
by int8 are you saying 8 bit or 8 byte integer? 8-bit makes no sense, so I'll assume int64. The UUID is just a 128 bit integer, so only 2x the size. Given that there will be some padding in the blocks stored to disk based on other fields and how many rows you fit per block, the difference is going to be negligible for storage. Value comparisons will also not make a noticeable difference in speed.
As for RLS, you are correct that the ID you get from the API auth system will be the UID. However, you can map it to anything you like. I have a mapping table that converts to an integer Company ID with filtering on permissions. All of my other data is connected to a Company ID. For me, that's just a regular INTEGER, since I don't expect to have two billion of them. 🙂
if UUID is your limiting factor, you're going to have enough money / users at that point to pay someone/ a whole team fix it.
uuid is also much easier to port to other databases as you are likely to not run into conflicts. This is especially useful if you are merging multiple companies data
There are advantages to both.
more advantages to uuid, less to bigint in my research and experience
this debate comes up often on the internet. here's a reddit post from two years ago debating the same thing https://www.reddit.com/r/PostgreSQL/comments/t1b58s/q_uuids_for_primary_keys_a_good_idea/
Reddit
From the PostgreSQL community on Reddit
Explore this post and more from the PostgreSQL community
Here is the referenced article: https://supabase.com/blog/choosing-a-postgres-primary-key
Supabase
Choosing a Postgres Primary Key
Turns out the question of which identifier to use as a Primary Key is complicated -- we're going to dive into some of the complexity and inherent trade-offs, and figure things out
so, let me get this straight. the author puts an arbitrary metric of 1m ids to be created.
compares the results of 12 different id generation techniques.
Puts "To be fair, generation speed shouldn't be a deal breaker as it's unlikely to be the bottle neck for most applications."
Also, he lists out that "integers and serial have obvious benefits for simplicity, storage, and sortability. You might not want to expose them to the world though." and then lists uuid variations for the remainder of usage.
seems to me that the article is also trying to tell you to use uuids
I use UUID for anything to do with a user from auth, even though all my table link thru my profile id. It seems easier to filter data tables with a join as you already have the UUID in your client and in RLS. I personally don't want a user to know they are the 10th user of my app also. For things (like Vick mentions for companies) I would probably use int4. I do for chatroom id.