UUID vs BIGSERIAL as Primary Key in PostgreSQL: Which Should You Use?

Choosing a primary key in PostgreSQL is a foundational schema decision that affects storage, index efficiency, cache behavior, WAL volume, join performance, and application architecture. The two most common options—BIGINT generated by a sequence (BIGSERIAL / GENERATED ... AS IDENTITY) and UUID—have very different characteristics once tables grow beyond a few million rows.

This post examines the storage-engine mechanics unique to PostgreSQL, quantifies the performance differences with realistic benchmarks, supplies production-ready code patterns, and evaluates the practical alternatives.

PostgreSQL Storage Model: Heap + Separate Indexes

Unlike InnoDB (which uses a clustered primary-key index), PostgreSQL stores table data in a heap. Rows are placed according to the free-space map; there is no permanent physical ordering by primary key. The primary-key constraint is enforced by a separate B-tree index that stores the key value plus a TID (tuple identifier) pointing into the heap.

Consequences:

• Inserts into the table itself are largely unaffected by key randomness—new tuples simply go into free space.
• The primary-key index still suffers from random inserts: leaf-page splits, lower fill factor, poorer cache locality, and higher WAL traffic (especially when full_page_writes is on).
• Secondary indexes and foreign-key columns also store a copy of the primary key, so key width multiplies across the schema.
• Sequential keys keep the “right-hand side” of the B-tree hot, maximizing buffer-cache hits and minimizing page splits.

BIGSERIAL / IDENTITY Mechanics

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);

• 8-byte signed integer (range ≈ 9 × 10¹⁸).
• Sequence object supplies values; nextval is extremely cheap.
• Inserts land at the rightmost leaf of the primary-key index → near-perfect fill factor, minimal WAL amplification, excellent cache locality.
• Prefer the SQL-standard GENERATED ... AS IDENTITY over the older BIGSERIAL syntax (the latter is merely a convenience wrapper).

UUID Mechanics

PostgreSQL’s native uuid type is a 16-byte binary value. Generation options:

Random UUIDv4 keys produce scattered index inserts. UUIDv7 places a 48-bit Unix-millisecond timestamp in the most-significant bits, restoring near-sequential behavior while remaining globally unique and client-generatable.

Storage Footprint

Because every foreign key and secondary index stores the primary-key value, a 16-byte key versus an 8-byte key compounds across the entire schema. Narrow tables amplify the relative difference; wide tables mute it.

Performance Characteristics

Benchmarks from multiple independent sources (Cybertec, 2ndQuadrant/EDB-style tests, Ardent, QueryPlane, recent UUIDv7 measurements on PG 16–18) show a consistent pattern:

Insert Throughput (relative)

Typical observed numbers on modern hardware (1 M–10 M row inserts):

• BIGINT: highest TPS, smallest WAL volume.
• UUIDv7: usually within 10–25 % of BIGINT.
• UUIDv4: noticeably slower and generates substantially more WAL (random leaf pages force more full-page images).

Index Size & Bloat

After loading ~10 M rows:

Query Latency

• Point lookups by primary key: BIGINT usually fastest; UUIDv7 is very close; UUIDv4 incurs extra buffer traffic once the index no longer fits in cache.
• Range scans / ORDER BY id: sequential keys win decisively because of sequential index leaf access and better heap locality after clustering or natural insertion order.
• Joins on the primary key: wider keys increase memory and CPU cost; the difference is measurable but rarely dramatic unless the working set is huge.

WAL Amplification

Random index inserts dirty many more leaf pages. With full_page_writes = on (the default and recommended setting), each newly dirtied page after a checkpoint writes a full 8 kB image into the WAL. Sequential keys keep the set of dirty pages tiny; random UUIDv4 can inflate WAL volume by an order of magnitude on large indexes.

Code Examples

Schema Definitions

-- Preferred sequential primary key (SQL standard)
CREATE TABLE orders (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id   BIGINT NOT NULL,
    amount        NUMERIC(12,2) NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- UUIDv7 primary key (PostgreSQL 18+)
CREATE TABLE orders_uuid (
    id            UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id   BIGINT NOT NULL,
    amount        NUMERIC(12,2) NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Hybrid: sequential internal key + public UUID
CREATE TABLE orders_hybrid (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id     UUID NOT NULL DEFAULT uuidv7(),
    customer_id   BIGINT NOT NULL,
    amount        NUMERIC(12,2) NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (public_id)
);

Generating UUIDv7 on Older PostgreSQL Versions

-- Pure-SQL UUIDv7 (works on PG 13+)
CREATE OR REPLACE FUNCTION uuidv7() RETURNS uuid
LANGUAGE sql VOLATILE AS $$
  SELECT encode(
    set_bit(
      set_bit(
        overlay(
          uuid_send(gen_random_uuid())
          placing substring(int8send((extract(epoch from clock_timestamp()) * 1000)::bigint) from 3)
          from 1 for 6
        ),
        52, 1
      ),
      53, 1
    ),
    'hex'
  )::uuid;
$$;

Alternatively use a compiled extension such as pg_uuidv7 or generate the value in the application.

C# Example (client-side UUIDv7)

using System;
// Use a library that implements RFC 9562 UUIDv7, e.g. UUIDNext or similar

public static class IdFactory
{
    public static Guid NewUuidV7() => Uuid.NewDatabaseFriendly(Database.PostgreSql);
    // or any standards-compliant UUIDv7 generator
}

// Insert with client-generated key
await connection.ExecuteAsync(
    @"INSERT INTO orders_uuid (id, customer_id, amount)
      VALUES (@id, @customerId, @amount)",
    new { id = IdFactory.NewUuidV7(), customerId, amount });

JavaScript / Node.js

import { v7 as uuidv7 } from 'uuid'; // modern uuid package

const id = uuidv7(); // string form
// or pass as binary if using a driver that accepts Buffer

Application Pattern (Hybrid)

// Generate public UUID client-side; let PostgreSQL assign the clustering-friendly key
var publicId = IdFactory.NewUuidV7();
var newId = await connection.QuerySingleAsync<long>(
    @"INSERT INTO orders_hybrid (public_id, customer_id, amount)
      VALUES (@publicId, @customerId, @amount)
      RETURNING id",
    new { publicId, customerId, amount });

Alternatives Comparison

Decision Guidelines

Prefer BIGINT GENERATED ALWAYS AS IDENTITY when:

• The database is the single source of truth for ID generation.
• Maximum insert throughput, minimal storage, and lowest cache pressure matter.
• IDs are internal (never exposed to clients or URLs).

Prefer UUIDv7 (or hybrid) when:

• Multiple independent writers / microservices / offline clients must generate IDs.
• You need globally unique identifiers that can be merged across databases without collision.
• Public-facing identifiers must be opaque and non-guessable.

Avoid UUIDv4 as a primary key on any table expected to exceed a few million rows or to sustain high write rates. The index fragmentation, WAL amplification, and cache inefficiency are real and measurable.

Hybrid pattern is frequently the pragmatic winner: keep a sequential 8-byte primary key for clustering and joins, and expose a UUIDv7 (or ULID) as a unique secondary column for external use. Foreign keys stay narrow, indexes stay dense, and the application still obtains the architectural benefits of client-generated identifiers.

Practical Recommendations

1. Prefer GENERATED ALWAYS AS IDENTITY over the legacy BIGSERIAL / SERIAL syntax.
2. Never store UUIDs as text or varchar; always use the native uuid type.
3. On PostgreSQL 18+ use the built-in uuidv7(). On older versions generate client-side or install a lightweight extension / pure-SQL function.
4. Monitor index bloat (pgstattuple, pg_stat_user_indexes) and WAL volume when experimenting with random keys.
5. After bulk-loading random UUIDs, consider REINDEX (or concurrent reindex) if fragmentation becomes measurable.
6. Keep foreign-key columns the same type and width as the referenced primary key; wider keys cascade through the entire schema.

Conclusion

In PostgreSQL the performance gap between sequential BIGINT keys and random UUIDv4 keys is real but less catastrophic than in engines that cluster on the primary key. The dominant costs are larger indexes, more page splits, poorer buffer-cache hit rates, and increased WAL traffic.

UUIDv7 largely closes the insert and locality gap while preserving global uniqueness and client-side generation. For the majority of systems the cleanest design is still a sequential BIGINT primary key (for internal efficiency) combined with a UUIDv7 unique column when a public, distributed identifier is required.

Measure on your own hardware and data shape—the relative costs change with row width, concurrency, and whether the working set fits in shared_buffers. With that data in hand, the choice between UUID and BIGSERIAL becomes straightforward rather than ideological.

---

Tools and Versions

Need some UUIDs for testing? Try our tools for generating UUIDv4   UUIDv7   ULID   NanoID and decoding validating.
For a detailed comparison of UUID versions, visit our Complete Guide to UUID Versions.