Why you should use Prefix-UUID for Postgres Primary Keys

When I was working at Brex, I was first introduced to the concept of prefixed-UUIDs. These user-defined, human-readable prefixes are prepended to a standard UUID v4 value in your primary key definition.

These prefixes ended up being immensely helpful to me and my team as we built features and integrated with with other services owned by other teams.

Reason 1: Operational Clarity

The primary reason I see for using Prefixed UUIDs is to improve operational clarity. That is, helping internal users interact, monitor, and manage the data in the system. We’ve all seen customer reports that include an ID, but lack enough context to know what it actually refers to. Traditionally, context clues or informal tagging are needed to figure out what that ID actually represents. Are they submitting their userID, or perhaps their customer/organization ID. Is this a reportId or some other business domain ID? Usually this can be guessed by context. However sometime in some support flows, we try to proactively label IDs. In a bug report form, we might ask a customer to supply their userID. This manual user input is potentially flawed. They might not know their userID or supply a different ID entirely.
That then gets put into the system with a misleading label that confuses internal users.

Reason 2: Foreign Key Constraints only go so far

Many engineers don’t consider the operational burden of unclear data models — but they should. But, in that case, you might point to your DBMS and Database Client tracking your tables and tracking foreign keys automatically. Any given row that contains references to ids will also contain a reference to the table that ID is linked to.

That is true, and for many deployments, a single database and schema will get you far. However in a microservices environment that fundamentally breaks down across service boundaries. The 1 Service 1 Database rule breaks foreign key constraint guarantees. The impact of that is a whole separate discussion, but when storing and accessing foreign keys not owned by your service and database, prefix IDs are quite useful. They indicate where a particularly ID came from, in a way that does not require looking at the code connecting services together. It would also be trivial to assign team ownership to prefixes, helping trace responsibility for a given ID.

Reason 3: Observability

Prefixed UUIDs can improve observability by reducing the risk that labels are misused. A common logging pattern is to attach key-value context to a given log line.

console.info({ userId: "some_id", key: "value" });

This pattern relies on two things

• Properly labeling IDs in the context
• Not passing in the wrong id to the labeled key

Now a lot of tooling can be built to automatically assign context values. reducing the chance of labels being misused, but I have seen implementations of logging contexts that look like this multiple times

user_id:
userid:
user:
user_identifier:
user_id_ctx:
user_ctx:

This often leads to expensive stitching and property normalization in your log platform (e.g., Datadog).

Prefixed IDs provide a layer of protection, where even if a label is lying, the underlying value is still telling the truth.

How do I implement Prefixed UUIDs in my application?

Postgres makes this relatively straightforward, but you’ll need to set up a few extensions and functions — it’s not a single toggle.

--- Make sure the crypto and uuid extension is installed to generate UUID_V4s
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" 

--- Define a global function that takes in a prefix and generates the final ID
CREATE OR REPLACE FUNCTION prefixed_uuid(prefix TEXT) RETURNS TEXT AS $$
DECLARE
    new_uuid UUID;
BEGIN
    new_uuid := uuid_generate_v4();
    RETURN prefix || '_' || new_uuid;
END;
$$ LANGUAGE plpgsql;

--- In your table definitions, use the prefixed_uuid function to generate a default value
id TEXT PRIMARY KEY DEFAULT prefixed_uuid('user')

This pattern is easy to adopt if you manage your own schema and connecting clients to the database. Platforms that obscure the database like Supabase are a bit harder to configure.

Performance considerations of Prefixed UUIDs

The key difference here is that the underlying id column is of type text rather than of type uuid

In Postgres that is a technical difference

• Internally stored as 16 bytes, compared to 36 bytes as a string.

For some high throughput workloads, the difference may matter, but for almost all common OLTP workloads, the difference is marginal.

Note: there are more performance considerations between UUID ids and Sequential IDs that are outside the scope of this discussion. If you are willing to use a UUID in the first place, a prefixed UUID is not meaningfully slower.

The Problem with Using a UUID Primary Key in MySQL — PlanetScale

Understand the different versions of UUIDs and why using them as a primary key in MySQL can hurt database performance.