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:

    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?

1 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.

  • UUID v4 is not sortable, it is completely unique as well its not only numbers example: 84e8b24d-0474-4e87-b7d1-c3868ee3a608. The reason for keeping the primary key as a int is mainly for index performance as store_id will be foreign keys for other tables, UUID v4 doesnt index well from my understanding.
    – Merk don
    Commented May 14 at 0:20
  • Your understanding is flawed. The minus characters in the UUID output are just to make it more readable. UUIDs consist entirely of digits (hexadecimal digits) and are 128-bit unsigned integers. The only drawback when it comes to indexing is the missing locality, and that is no problem for lookup. If you want to UUIDs to be unique, you have to pay the price for an index anyway, so why pay the additional price for another, unnecessary index? Commented May 14 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.