Unique index or unique constraint for external UUID column in PostgreSQL when mapping IDs?

Question

I'm using PostgreSQL version 16 and implementing an "external ID mapping" pattern, where I use an INT data type as the primary key and a UUID column as an external identifier to expose through my API to clients. The table structure looks like this:

CREATE TABLE stores (
    store_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    store_name citext,
    external_id uuid DEFAULT gen_random_uuid()
);

The main purpose of using the external_id UUID is to expose it to API clients while keeping the internal store_id hidden. The external_id will be primarily used for lookups and references by the API clients. For example, when a client wants to retrieve or update a specific store, they will use the external_id in their API requests instead of the internal store_id.

I want to ensure the uniqueness of the external_id column since it will be used as the main identifier for clients. However, I'm unsure about the best approach to achieve this. One option is to create a unique index on the external_id column. However, I've heard that B-tree indexes, which are the default index type in PostgreSQL, may not be optimal for UUID values (v4) due to their non-sortable nature. I'm considering using a hash index instead, but I'm not sure if it will enforce uniqueness, even though UUID collisions are rare.

Alternatively, I could add a unique constraint on the external_id column. Would this be a better approach compared to using a unique index when mapping external IDs? Does a unique constraint automatically create an index to enforce uniqueness efficiently and will it work well with the UUID type?

Given that the external_id will be primarily used for lookups by API clients, I want to ensure that the chosen approach provides optimal performance for queries based on the external_id column. I've considered options like obfuscating or encrypting/decrypting the IDs on the API layer, but I feel it's more straightforward to use external IDs directly.

I have also thought about using UUIDs as the primary key, especially with the introduction of UUID v7, which provides time-based sorting. However, the lack of native support for UUID v7 in PostgreSQL has kept me from pursuing this route.

Considering the "external ID mapping" pattern and the primary usage of external_id for lookups, what would be the recommended approach for such a design?

Answer 1

A unique constraint is implemented with a unique index, so if one won't work, the other won't work either. However, UUIDs are perfectly sortable (they are nothing but large numbers), so either will work just fine.

I recommend the constraint over the index, because constraints are more portable (they are defined by the SQL standard) and have additional features (they can serve as the target of a foreign key constraint).

Actually, you should do away with store_id and use the UUID as primary key of the table. The artificial primary key just wastes space and (more importantly) processing power.