UUID or SEQUENCE for primary key?

Question

I am coming from MySQL, and in MySQL you can use AUTOINCREMENT for a row's unique id as the primary key.

I find that there is no AUTOINCREMENT in Postgresql, only SEQUENCE or UUID.I have read somewhere that we can use UUID as the primary key of a table. This has the added advantage of masking other user's id (as I want to build APIs that take the ID in as a parameter). Which should I use for Postgresql?

Answer 1

A sequence in PostgreSQL does exactly the same as AUTOINCREMENT in MySQL. A sequence is more efficient than a uuid because it is 8 bytes instead of 16 for the uuid. You can use a uuid as a primary key, just like most any other data type.

However, I don't see how this relates to masking of an user ID. If you want to mask the ID of a certain user from other users, you should carefully manage the table privileges and/or hash the ID using - for instance - md5().

If you want to protect a table with user data from snooping hackers that are trying to guess other IDs, then the uuid type is an excellent choice. Package uuid-ossp has several flavours. The version 4 is then the best choice as it has 122 random bits (the other 6 are used for identification of the version). You can create a primary key like this:

id uuid PRIMARY KEY DEFAULT uuid_generate_v4() 

and then you will never have to worry about it anymore.

---

PostgreSQL 13+

You can now use the built-in function gen_random_uuid() to get a version 4 random UUID.

Answer 2

You can use UUID as primary key in your table as it will be unique. However do keep in mind that UUID will occupy a bit more space as compared to SEQUENCE. And also they are not very fast. But yes they are for sure unique and hence you are guaranteed to get a consistent data.

You can also refer:

• UUID Primary Keys in PostgreSQL
• UUID vs. Sequences