CODEWITHSUNDEEP
postgresqlindexinguuid
alex.bour
alex.bour

Reputation: 2964

Index instead of primary key on UUID type in PostgreSQL

First, I have read a few posts about this, like this one: Postgresql: UUID or SEQUENCE for primary key?

My question is quite simple: my IDs in my table are UUID v4 (created in Rails or from an iOS app). As UUID by default is unique, can I remove the primary key on ID and just add an index on it? The main (and uniq?) goal is to save time (a few ms) on inserting (PostgreSQL won't have to verify if the ID is already used) at each insert.

Is-it a good choice ? Or do I keep the PK to add another verification of the uniqueness before inserting ?

For info, the table will manage maybe 10 millions records.

Upvotes: 3

Views: 8905

Answers (2)

Kirk Roybal
Kirk Roybal

Reputation: 17837

Both of the keys that you are describing are apparently being used as surrogate keys. Surrogate meaning that they are not derived from the incoming data, and therefore have no relationship to it other than providing uniqueness.

You do not need 2 keys for the purpose of providing uniqueness, so the answer to your question is that you can drop one or the other of the keys. The size of the table is not really a factor here, as uuid_v4() will provide uniqueness for vastly larger datasets than 10M rows.

Having 2 keys for uniqueness is not just unnecessary, it is also a bottleneck. Both values must be created at insertion time, and both must be validated for uniqueness. Deleting one of them is a clearly better practice.

Upvotes: 2

S-Man
S-Man

Reputation: 23676

First: UUIDs are not really unique. But the chance to generate double values is really really low (How unique is UUID?).

But there are some other issues with UUID. UUIDs are made for exchange data between different points. So if you think of two databases which communicate both would share the same data sets with the same UUID. Now think about an archive were data sets from many sources are stored. You could have data sets with the same UUID from some old communications.

So it depends on your current (and maybe future possible?) use cases if this could create any problems.

Furthermore I am not sure if it creates any advantages against a simple integer value concerning the space of your primary key index. Notice that every primary key automatically creates an internal index per default (so there's no need to create a separate index nonetheless). So a primary key index for an integer column might be smaller and faster.

Upvotes: 2

Related Questions