UUID vs Integer IDs in PostgreSQL

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

The “UUID vs integer” debate is one of the most common PostgreSQL discussions. Both approaches work, but they have different tradeoffs that matter at scale.

In this post, we’ll cover:

• Integer IDs - The traditional approach with BIGSERIAL
• UUID v4 - Random UUIDs and their performance problems
• UUID v7 - Time-ordered UUIDs that fix the randomness issue
• Performance benchmarks - Real numbers comparing the options
• When to use each - Practical guidelines for your application

Integer IDs: The Traditional Approach

Integer IDs (typically BIGINT with a sequence) have been the PostgreSQL default for decades. They’re simple, fast, and well-understood:

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

The appeal of integers is their efficiency. At 8 bytes, a BIGINT is half the size of a UUID, which means smaller indexes and faster comparisons. Sequential values insert efficiently into B-trees because new rows always go at the end of the index—there’s no need to reorganize existing pages. And when you’re debugging in production, user_id=12345 is a lot easier to type and remember than a 36-character UUID.

The downside is that integers are predictable. If a user sees their order ID is 12345, they know that 12346 and 12344 probably exist too. This can be a security concern if you’re exposing IDs in URLs. Integers are also centralized—you can only generate them from the database, which becomes a bottleneck in distributed systems. And if you ever need to merge databases (say, combining data from two acquired companies), overlapping sequences create painful conflicts.

UUID v4: Random UUIDs

UUID v4 generates 122 bits of randomness, giving you IDs that are virtually guaranteed to be unique across all systems forever:

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id BIGINT NOT NULL,
  total_cents BIGINT NOT NULL
);

The gen_random_uuid() function is built into PostgreSQL 13+, so you don’t need any extensions. Random UUIDs solve the predictability problem—you can’t guess other IDs from one ID—and they enable distributed generation. Your application servers, mobile clients, or batch jobs can all generate valid IDs without coordinating with the database.

However, random UUIDs have a performance problem that only shows up at scale. When you insert a random value into a B-tree index, it lands somewhere random in the index structure. This causes index fragmentation: instead of new values appending neatly to the end, they scatter across the entire index. The database has to read and modify random pages instead of sequential ones, which hurts cache efficiency and increases WAL (write-ahead log) traffic.

Here’s what this looks like in practice. With 10 million rows:

Metric	BIGINT	UUID v4
Insert time (1M rows)	45s	68s
Index size	214 MB	343 MB
Random lookup	0.15ms	0.18ms

The index size difference comes from fragmentation—UUID indexes have more partially-filled pages because random inserts cause page splits.

UUID v7: The Best of Both Worlds

UUID v7, defined in RFC 9562, embeds a timestamp in the first 48 bits:

|-- 48 bits --|-- 4 bits --|-- 12 bits --|-- 62 bits --|
   timestamp      version      rand_a        rand_b

This makes UUIDs roughly time-ordered. IDs generated later have higher values, which means new inserts go near the end of B-tree indexes—just like sequential integers. You get the distributed generation and unpredictability benefits of UUIDs without the fragmentation penalty.

PostgreSQL 18 (expected Fall 2025) adds native uuidv7() generation:

-- PostgreSQL 18+
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT uuidv7(),
  event_type TEXT NOT NULL,
  payload JSONB
);

Until then, you can use the pg_uuidv7 extension:

CREATE EXTENSION IF NOT EXISTS pg_uuidv7;

CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  event_type TEXT NOT NULL,
  payload JSONB
);

You can also generate UUID v7 in your application code. Most languages have libraries: the uuid7 package for Python, uuidv7 for Node.js, and google/uuid for Go all support v7 generation.

Performance Comparison

We ran a benchmark inserting 1 million rows into tables with different ID types:

-- Setup: Three identical tables with different ID strategies
CREATE TABLE bench_bigint (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  data TEXT
);

CREATE TABLE bench_uuid4 (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  data TEXT
);

CREATE TABLE bench_uuid7 (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  data TEXT
);

-- Insert 1M rows into each
INSERT INTO bench_bigint (data)
SELECT 'test data ' || i FROM generate_series(1, 1000000) i;

INSERT INTO bench_uuid4 (data)
SELECT 'test data ' || i FROM generate_series(1, 1000000) i;

INSERT INTO bench_uuid7 (data)
SELECT 'test data ' || i FROM generate_series(1, 1000000) i;

Results on PostgreSQL 16 (AWS RDS db.r6g.large):

ID Type	Insert Time	Index Size	Bloat %
BIGINT	12.3s	21 MB	0%
UUID v4	18.7s	42 MB	38%
UUID v7	14.1s	30 MB	8%

UUID v7 is about 15% slower than BIGINT for inserts—the extra bytes still cost something—but it eliminates most of the fragmentation problem. The 8% bloat is manageable, while 38% bloat from UUID v4 means you’re storing almost 40% more index data than necessary.

Extracting Timestamps from UUID v7

One nice benefit of UUID v7 is that you can extract the creation timestamp directly from the ID. Here’s a function that does it:

CREATE OR REPLACE FUNCTION uuid_v7_to_timestamp(uuid_val UUID)
RETURNS TIMESTAMPTZ AS $$
DECLARE
  bytes BYTEA;
  ms BIGINT;
BEGIN
  bytes := decode(replace(uuid_val::text, '-', ''), 'hex');
  ms := (get_byte(bytes, 0)::BIGINT << 40) |
        (get_byte(bytes, 1)::BIGINT << 32) |
        (get_byte(bytes, 2)::BIGINT << 24) |
        (get_byte(bytes, 3)::BIGINT << 16) |
        (get_byte(bytes, 4)::BIGINT << 8) |
        (get_byte(bytes, 5)::BIGINT);
  RETURN to_timestamp(ms / 1000.0);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Example usage
SELECT uuid_v7_to_timestamp('018f3b5c-8d9e-7abc-8def-0123456789ab');
-- Returns: 2024-05-15 10:30:45.678+00

This can eliminate the need for a separate created_at column in some cases, though you’ll want to keep an explicit timestamp if you need sub-millisecond precision or if you ever update rows.

When to Use Each

BIGINT is the right choice when performance is critical and you’re doing high-volume inserts. It’s also appropriate when IDs are purely internal—never exposed to users—or when you’re working with systems that expect numeric IDs (some legacy systems, certain analytics tools). The key constraint is that you must be able to generate IDs centrally in your database.

UUID v4 makes sense when you need truly random, unpredictable IDs. This might be for security reasons where you want zero information leakage from IDs, or when you’re generating IDs in untrusted client code where you can’t rely on the database. The performance cost is real but acceptable for many applications.

UUID v7 is often the best default for new projects. You get distributed generation and reasonable unpredictability without the fragmentation penalty of random UUIDs. Time-ordering is useful for queries, and you can extract creation timestamps directly from IDs. Unless you have a specific reason to choose otherwise, UUID v7 is a solid choice.

Hybrid Approach: Internal vs External IDs

A common pattern is to use both integer and UUID IDs on the same table:

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id UUID NOT NULL UNIQUE DEFAULT uuid_generate_v7(),
  email TEXT NOT NULL UNIQUE
);

CREATE INDEX ON users (public_id);

Internal queries and foreign keys use id for maximum performance—you’re joining on compact 8-byte integers. API endpoints use public_id so that external users never see sequential integers. This gives you the best of both worlds: fast internal operations and secure external identifiers.

Migrating from UUID v4 to v7

If you have an existing table with UUID v4 and want to switch to v7, the migration is straightforward:

-- Change the default for new rows
ALTER TABLE events
  ALTER COLUMN id SET DEFAULT uuid_generate_v7();

-- Old rows keep their v4 UUIDs (they still work fine)
-- New rows get v7 UUIDs

The UUID type is the same, so v4 and v7 values coexist without issues. Over time, as old rows are deleted and new ones added, you’ll naturally transition to mostly v7. The existing random UUIDs won’t cause problems—they just won’t benefit from the time-ordering optimization.

Wrapping Up

The UUID vs integer debate has a nuanced answer. BIGINT remains the most performant option for internal IDs, especially at high write volumes. UUID v4 has real performance costs from index fragmentation that show up at scale. UUID v7 offers a compelling middle ground: you get distributed generation and non-guessable IDs with time-ordering that plays nicely with B-tree indexes.

For new projects, UUID v7 is often the best default choice. You get the flexibility benefits of UUIDs without paying the fragmentation tax. If you’re already using UUID v4 and seeing index bloat, switching new inserts to v7 is a low-risk improvement that pays off over time.