Postgresql: UUID or SEQUENCE for primary key?

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

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, the the uuid type is an excellent choice. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *