How to Use UUID Data Types in PostgreSQL

Auto-incrementing integer primary keys have been the default for so long that a lot of developers never stop to question them — until they run into the problems that come with distributed systems, public-facing IDs, or merging data from multiple sources. That’s usually the point where UUIDs enter the conversation. PostgreSQL has native, first-class support for UUIDs as a proper data type, and this article covers everything from basic syntax to generation strategies, indexing considerations, and the trade-offs you need to understand before committing to them as primary keys.

What Is the UUID Data Type?

A UUID (Universally Unique Identifier) is a 128-bit value, typically represented as a 36-character string in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, where each x is a hexadecimal digit. PostgreSQL stores UUID values natively as a genuine 16-byte binary type — not as text — which means comparisons and storage are efficient, and the type enforces proper UUID formatting on input.

SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;

If you pass in something that isn’t a validly formatted UUID, Postgres rejects it immediately:

SELECT 'not-a-uuid'::uuid;
-- ERROR:  invalid input syntax for type uuid

Basic Table Setup

CREATE TABLE users (
    user_id uuid PRIMARY KEY,
    email text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now()
);

Inserting an explicit UUID:

INSERT INTO users (user_id, email)
VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'jane@example.com');

More commonly, you’ll want PostgreSQL to generate the UUID automatically.

Generating UUIDs

There are a few different ways to generate UUID values in PostgreSQL, and which one you should use has changed over the years as native support has improved.

Native gen_random_uuid() (Recommended, PostgreSQL 13+)

Since PostgreSQL 13, gen_random_uuid() is available built into core — no extension required. It generates a version 4 (random) UUID.

CREATE TABLE orders (
    order_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id uuid NOT NULL,
    order_total numeric(10,2)
);

INSERT INTO orders (customer_id, order_total)
VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 149.99);

SELECT * FROM orders;

The order_id gets automatically populated without you having to specify it.

The pgcrypto Extension (Older Versions)

Before PostgreSQL 13, gen_random_uuid() wasn’t in core — you needed the pgcrypto extension:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT gen_random_uuid();

If you’re on an older Postgres version, this is still the way to go. On modern versions, it’s no longer necessary, though the extension still works if it’s already in use elsewhere in your database.

The uuid-ossp Extension (Legacy)

You’ll still see uuid-ossp referenced in a lot of older tutorials and codebases:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT uuid_generate_v4();  -- random UUID
SELECT uuid_generate_v1();  -- time-based UUID (includes MAC address component)

For new projects on PostgreSQL 13+, there’s generally no reason to reach for uuid-ossp anymore — the built-in gen_random_uuid() covers the common case (random v4 UUIDs) without an extra dependency.

UUIDv7 and Time-Ordered UUIDs

One real limitation of standard random (v4) UUIDs is that they’re not sequential — inserting them into a B-tree-indexed primary key column causes random insert patterns, which can hurt index locality and cache performance on very large, high-throughput tables. This has driven growing interest in UUIDv7, a newer UUID version that embeds a timestamp prefix, giving you roughly-sortable, time-ordered UUIDs while keeping the rest of the value random.

As of recent PostgreSQL versions, native UUIDv7 generation support is an active area of development — check your specific PostgreSQL version’s release notes and documentation for current built-in support, since this has been evolving quickly. In the meantime, several well-maintained third-party extensions and PL/pgSQL implementations exist for generating UUIDv7-style values if your workload is sensitive to index insert locality.

Comparing UUIDs to Serial/Identity Columns

This is the trade-off decision most teams actually need to make, and it’s worth laying out honestly.

Arguments for UUID primary keys:

• They can be generated client-side or by any node in a distributed system without coordinating with a central sequence, which matters a lot for offline-first apps, multi-region writes, or merging data from independent systems.
• They don’t leak information about row count or creation order the way sequential integers do (a competitor can’t infer “how many orders you have” from an exposed order ID).
• They’re safer to expose directly in public URLs or APIs without needing an additional obfuscation layer.

Arguments against (or for sticking with integers):

• A bigint is 8 bytes; a uuid is 16 bytes. That’s double the storage per row, and it compounds across every foreign key referencing that column too.
• Random UUIDs (v4) hurt B-tree index locality on insert-heavy tables, since new values land in random positions throughout the index rather than appending at the end.
• Integers are more human-friendly for debugging, logs, and manual database work.

A common, genuinely reasonable middle ground: use a bigint (or bigserial/identity column) as the actual primary key for internal joins and indexing efficiency, and add a separate uuid column (with a unique constraint) as the public-facing identifier used in URLs and APIs. This gets you compact, fast internal joins along with safe, non-guessable external IDs.

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    customer_id bigint NOT NULL,
    order_total numeric(10,2)
);

Indexing UUID Columns

UUID columns index cleanly with a standard B-tree index, same as any other type:

CREATE UNIQUE INDEX idx_orders_public_id ON orders (public_id);

The performance consideration isn’t about whether B-tree indexing “works” on UUIDs — it does — it’s about insert pattern locality, as mentioned above. For extremely high-throughput insert workloads on very large tables, random UUID inserts can lead to more index page splits and less cache-friendly access patterns compared to monotonically increasing keys. For most applications, this difference is not noticeable in practice; it becomes a real concern mainly at large scale with heavy write throughput.

Using UUIDs as Foreign Keys

CREATE TABLE order_items (
    item_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id uuid NOT NULL REFERENCES orders(order_id),
    product_name text,
    quantity integer
);

This works exactly like any other foreign key relationship — Postgres enforces referential integrity the same way regardless of the underlying key type.

Practical Use Cases

1. Distributed and Offline-First Applications

When multiple independent services, devices, or offline clients need to generate new records without a central coordinator issuing sequential IDs, UUIDs (generated client-side if needed) avoid ID collisions without any coordination overhead.

2. Public-Facing Identifiers

API resource IDs, password-reset tokens, and any identifier exposed in a URL benefit from UUIDs’ non-guessability — an attacker can’t simply increment a number to enumerate your users’ records.

3. Merging Data from Multiple Sources

When importing or merging datasets from separate systems (a very common scenario during company mergers, multi-tenant consolidation, or data migrations), sequential integer IDs from different sources are guaranteed to collide. UUIDs sidestep this entirely.

4. Idempotency Keys

Many APIs use a client-generated UUID as an idempotency key to safely retry requests without risk of duplicate processing:

CREATE TABLE payment_requests (
    idempotency_key uuid PRIMARY KEY,
    amount numeric(10,2),
    processed_at timestamptz
);

Troubleshooting Common Issues

“Invalid input syntax for type uuid” errors. The value being inserted or cast isn’t in valid UUID format. Double-check for missing hyphens, incorrect length, or non-hex characters. Case doesn’t matter — Postgres normalizes UUID casing automatically — but structural formatting does.

gen_random_uuid() not found. This means you’re on a PostgreSQL version older than 13 and haven’t installed pgcrypto. Either upgrade, or run CREATE EXTENSION pgcrypto; first.

Slower-than-expected bulk inserts on UUID primary keys. This is the classic random-insert-locality problem described above. If it’s genuinely a bottleneck, consider a bigint primary key with a UUID as a secondary unique identifier, or investigate time-ordered UUID generation strategies.

Confusing UUID versions. Not all UUIDs are created equal — v1 embeds a timestamp and MAC-derived component (which can leak information you may not want exposed), v4 is fully random, and newer schemes like v7 aim for time-sortability without the MAC address leakage of v1. Know which version your generation function produces and whether that’s appropriate for your use case.

Storage bloat concerns. Remember every UUID column costs 16 bytes versus 8 for a bigint, and this multiplies across every table that stores it as a foreign key. On very large tables, this is a real, measurable cost — factor it into your schema design rather than defaulting to UUIDs everywhere without consideration.

Best Practices

• Use gen_random_uuid() on PostgreSQL 13+ rather than reaching for the uuid-ossp extension out of habit.
• Consider the bigint-primary-key-plus-UUID-public-id pattern for high-throughput tables where you want both fast internal joins and safe external identifiers.
• Always validate UUID format on the application side too, for a better user-facing error message than a raw database error.
• Be deliberate about which UUID version you’re generating — random (v4) for general use, and investigate time-ordered options if insert locality on a large table becomes a genuine performance concern.
• Don’t default to UUIDs everywhere reflexively — evaluate whether your actual use case (distributed generation, public exposure, data merging) genuinely needs them, versus a simpler integer identity column.

UUIDs in Multi-Table Schemas

UUIDs genuinely shine once you’re working across a schema with several related tables, since every table can generate its own identifiers independently without any coordination:

CREATE TABLE customers (
    customer_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL,
    email text NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id uuid NOT NULL REFERENCES customers(customer_id),
    order_total numeric(10,2),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    item_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id uuid NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_name text,
    quantity integer,
    unit_price numeric(10,2)
);

INSERT INTO customers (name, email) VALUES ('Jane Doe', 'jane@example.com')
RETURNING customer_id;

The RETURNING clause is particularly useful with UUID primary keys, since — unlike an auto-incrementing integer where you might predict or infer the next value — a randomly generated UUID genuinely can’t be known until the database generates it. Capturing it immediately with RETURNING avoids a separate round-trip query just to look up the ID you need for a follow-up insert.

Bulk Insert Performance with UUIDs

If you’re inserting large volumes of data with UUID primary keys, it’s worth understanding the practical performance implications a bit more concretely. Because random UUIDs don’t have any natural ordering, bulk inserts into a UUID-primary-keyed table cause index writes to land at random positions throughout the B-tree, rather than appending sequentially at the end the way auto-incrementing keys do.

-- Benchmark comparison pattern: measure bulk insert timing
EXPLAIN ANALYZE
INSERT INTO orders (customer_id, order_total)
SELECT customer_id, (random() * 500)::numeric(10,2)
FROM customers, generate_series(1, 100000);

For most applications, this difference simply isn’t noticeable — the overhead becomes meaningful mainly on very large tables (tens of millions of rows or more) under sustained high-throughput write loads. If you’re in that regime and profiling shows index maintenance as a genuine bottleneck, options include using a time-ordered UUID generation scheme (UUIDv7-style), partitioning the table, or falling back to the bigint-primary-key-with-UUID-secondary-identifier pattern described earlier.

UUIDs and Row-Level Security

UUIDs pair particularly well with PostgreSQL’s row-level security features in multi-tenant applications, since a non-guessable identifier adds a meaningful layer of defense even before row-level security policies are evaluated:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY customer_orders_policy ON orders
    USING (customer_id = current_setting('app.current_customer_id')::uuid);

Even if row-level security were somehow bypassed or misconfigured, an attacker working with sequential integer IDs could trivially enumerate every order in the system by simply incrementing a number. With UUID primary keys, that kind of blind enumeration attack is effectively infeasible, which is a genuine defense-in-depth benefit on top of whatever access control policies are already in place.

Validating and Working with UUIDs in Application Code

Because the uuid type enforces valid formatting at the database layer, it’s tempting to skip validation in application code entirely — but that usually produces a worse user experience, since a raw Postgres constraint violation error is not something you want surfacing directly to an end user. It’s worth validating UUID-shaped input at the application boundary too, purely for better error messages, even though the database will catch any invalid value regardless.

-- A quick way to test whether a string is a valid UUID without inserting it
SELECT '123e4567-e89b-12d3-a456-426614174000' ~*
    '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$' AS looks_valid;

This kind of pre-check is a nice-to-have for a friendlier error message, but it should never be treated as a substitute for the database’s own enforcement — always let the uuid column type do the actual validation work, since regex-based pre-checks can drift out of sync with the true UUID specification over time.

UUID Storage Format and Byte Order

It’s worth knowing, mostly for cross-system interoperability reasons, that PostgreSQL stores uuid values as a genuine 16-byte binary value internally, not as the 36-character hyphenated string you see when querying it. When integrating with external systems or client libraries, it’s worth confirming they handle UUID byte ordering consistently with Postgres’s representation — mismatched byte ordering between systems has historically caused subtle bugs in a few language ecosystems where UUID libraries didn’t agree on canonical byte layout, particularly around older Microsoft-style GUID implementations that use a different internal byte order convention than the RFC 4122 standard Postgres follows. This is a fairly rare gotcha in modern tooling, but worth a quick check if you’re seeing UUIDs that look “scrambled” compared to what another system expects.

A Note on UUID Predictability and Security

It’s worth being precise about what UUIDs actually guarantee from a security standpoint, since “UUIDs are unguessable” is sometimes stated more strongly than it should be. A version 4 (random) UUID generated by a cryptographically sound random number generator — which gen_random_uuid() is — is genuinely infeasible to guess or enumerate. However, if a UUID is generated using a weaker source of randomness, or if you’re using a version like v1 that embeds a timestamp and MAC-derived component, the effective unpredictability is meaningfully lower, since part of the value is derived from predictable or semi-predictable inputs rather than being fully random. For anything security-sensitive — password reset tokens, session identifiers, API keys — confirm you’re generating with gen_random_uuid() (or an equivalent cryptographically secure generator) specifically, rather than assuming “it’s a UUID” alone is a sufficient security property.

Wrapping Up

UUIDs solve real problems — safe public exposure, coordination-free generation across distributed systems, and painless data merging — that sequential integers simply can’t. PostgreSQL’s native UUID type, combined with the built-in gen_random_uuid() function, makes them straightforward to adopt without any extension overhead on modern versions. The decision isn’t “UUIDs are better” or “integers are better” in the abstract — it’s about matching the identifier strategy to what your specific system actually needs, and for a lot of production schemas, the honest answer is a hybrid of both.