PostgreSQL 18: Achieving 23× Faster Inserts with UUID v7

PostgreSQL 18 introduced uuidv7() as a built‑in function for generating time‑ordered UUIDs. When a table’s primary‑key column is changed to use uuidv7() as the default, the index entries become monotonically increasing, allowing PostgreSQL to keep the “hot” B‑tree page in the shared buffer. This reduces page splits, WAL traffic, and disk reads, which translates into markedly faster multi‑row inserts. In a production workload that performed 12 000 inserts per minute on a table with billions of rows, the average insert latency dropped from 0.7 ms to 0.03 ms—a 23‑fold improvement.

Why an exclusive lock is required

Changing the column default is performed with an ALTER TABLE … ALTER COLUMN … SET DEFAULT statement. PostgreSQL acquires an access‑exclusive lock for this operation, which blocks all reads and writes on the target table until the lock is released. For heavily queried tables this can cause unacceptable downtime, so the migration must be orchestrated carefully.

Practical migration pattern

• Set short lock and statement timeouts to avoid long‑running blocks.
• Wrap the ALTER TABLE in an explicit transaction.
• Implement a retry loop with jittered back‑off to capture a brief window when no other session holds a lock.

Example using psql:

BEGIN;
SET LOCAL lock_timeout = '50ms';
SET LOCAL statement_timeout = '100ms';
ALTER TABLE my_table
  ALTER COLUMN id SET DEFAULT uuidv7();
COMMIT;

For tables that rarely release the lock, a PL/pgSQL retry function can be employed:

SET lock_timeout = '100ms';
DO $$
DECLARE
  attempt INT := 0;
  max_attempts INT := 50;
BEGIN
  LOOP
    attempt := attempt + 1;
    BEGIN
      EXECUTE 'ALTER TABLE my_table ALTER COLUMN id SET DEFAULT uuidv7()';
      RAISE NOTICE 'Succeeded on attempt %', attempt;
      EXIT;
    EXCEPTION WHEN lock_not_available THEN
      IF attempt >= max_attempts THEN
        RAISE EXCEPTION 'Failed after % attempts', attempt;
      END IF;
      PERFORM pg_sleep(0.05 + random() * 0.2);  -- 50‑250 ms jitter
    END;
  END LOOP;
END $$;

Context for the performance story

Before the switch, the system used a mix of uuid_generate_v1() (time‑based) and gen_random_uuid() (v4). While v1 already offered some ordering, v4’s randomness caused frequent page splits and cache misses. By standardising on v7, the index became more compact and insert paths stayed on the hot page, delivering the observed 6‑23× speedups across several tables. The only operational cost was managing the exclusive lock, which can be mitigated with the techniques above.

BEGIN;
SET LOCAL lock_timeout = '50ms';
SET LOCAL statement_timeout = '100ms';
ALTER TABLE orders
  ALTER COLUMN id SET DEFAULT uuidv7();
COMMIT;

DO $$
DECLARE
  attempt INT := 0;
  max_attempts INT := 50;
BEGIN
  LOOP
    attempt := attempt + 1;
    BEGIN
      EXECUTE 'ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7()';
      EXIT;
    EXCEPTION WHEN lock_not_available THEN
      IF attempt >= max_attempts THEN
        RAISE EXCEPTION 'Lock acquisition failed';
      END IF;
      PERFORM pg_sleep(0.05 + random()*0.2);
    END;
  END LOOP;
END $$;

In PostgreSQL, B-tree index efficiency relies heavily on the monotonicity of primary key values. When using UUID v4, values are generated randomly and lack sequential ordering. As a result, new index entries are scattered across the B-tree rather than appended to the current "hot" page in the buffer cache. This non-sequential insertion pattern necessitates frequent disk I/O to fetch pages not currently in memory and triggers excessive index page splits, which increase write-ahead log (WAL) volume and CPU overhead.

Transitioning to UUID v7, which incorporates a time-based component, enforces a monotonic insertion order. This ensures that new entries generally land on the most recently accessed index page, significantly improving cache hit ratios and reducing page splits. The following benchmarks demonstrate the impact of this transition on high-concurrency write workloads:

Table	Calls/min	Original Execution Time	New Execution Time	Performance Gain
Table A	12,000	0.7ms	0.03ms	23x
Table B	2,000	0.6ms	0.07ms	9x
Table C	9,500	0.50ms	0.08ms	6x

The observed gains are attributed to the reduction in CPU cycles required for index maintenance and the minimization of I/O wait times. By keeping index pages "hot" in the buffer cache, the database avoids the latency penalties associated with random disk access for fragmented index updates. Engineers should note that while these performance gains are substantial, UUID v7 does expose record creation metadata through its embedded timestamp, a factor that should be considered alongside the operational benefits of improved write throughput and reduced storage fragmentation.

Migrating to UUID v7 requires executing an ALTER TABLE ... ALTER COLUMN SET DEFAULT uuidv7() command. While this operation is computationally fast, it necessitates an ACCESS EXCLUSIVE lock on the target table. This lock level is restrictive, as it conflicts with all concurrent read and write operations, including standard SELECT statements. On high-traffic tables, failing to manage this lock can lead to extended periods of blocked application requests.

To perform this migration without inducing downtime, engineers must adopt a strategy that minimizes lock holding time and utilizes retry logic to circumvent blocked sessions. Key tactical considerations include:

• lock_timeout configuration: Before executing the migration, set a short lock_timeout (e.g., 50ms to 100ms). This forces the statement to abort if it cannot immediately acquire the required lock, preventing the migration process from queueing behind existing transactions.
• Retry Loops: Since the ALTER TABLE command will frequently fail due to the short timeout on busy tables, implement a retry mechanism to attempt the operation repeatedly until a brief window of inactivity allows the lock to be acquired.
• Jittered Backoff: Use a PL/pgSQL block to automate these retries. Incorporating a jittered backoff—typically 50ms to 250ms—prevents the migration process from entering a tight loop that could exacerbate contention.

The following PL/pgSQL block illustrates a robust approach to managing these retries within a production environment:

SET statement_timeout = 0;
SET lock_timeout = '100ms';

DO $$ 
DECLARE 
  attempt INT := 0; 
  max_attempts INT := 50; 
BEGIN 
  LOOP 
    attempt := attempt + 1; 
    BEGIN 
      EXECUTE 'ALTER TABLE my_table ALTER COLUMN id SET DEFAULT uuidv7()'; 
      RAISE NOTICE 'Succeeded on attempt %', attempt; 
      EXIT; 
    EXCEPTION WHEN lock_not_available THEN 
      IF attempt >= max_attempts THEN 
        RAISE EXCEPTION 'Failed to acquire lock after % attempts', attempt; 
      END IF; 
      PERFORM pg_sleep(0.05 + random() * 0.2); 
    END; 
  END LOOP; 
END $$;

If automated retries consistently fail due to sustained high load, monitor pg_stat_activity and pg_locks to identify long-running transactions blocking the migration. While terminating these sessions with pg_cancel_backend can clear the path, this action carries risks to application stability and should be exercised with caution.

When a migration requires an ACCESS EXCLUSIVE lock—such as an ALTER TABLE … ALTER COLUMN SET DEFAULT—any concurrent transaction that holds a conflicting lock will block the operation. Understanding which backends are holding locks and why they are blocked is the first step in managing contention.

Observing active sessions

PostgreSQL exposes live session information through pg_stat_activity. A typical query to list non‑idle sessions and their duration is:

SELECT pid,
       state,
       left(query, 100) AS short_query,
       xact_start,
       state_change,
       age(clock_timestamp(), xact_start) AS tx_duration
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_start;

This view shows which sessions are currently executing, their start time, and how long they have been running, helping you spot long‑running transactions that may be candidates for cancellation.

Identifying lock holders

To pinpoint the exact processes that own locks preventing your migration, join pg_locks with pg_stat_activity:

SELECT blocked_locks.pid          AS blocked_pid,
       blocking_locks.pid         AS blocking_pid,
       blocked_activity.query    AS blocked_statement,
       blocking_activity.query   AS blocking_statement
FROM pg_locks           blocked_locks
JOIN pg_stat_activity   blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks           blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.pid <> blocked_locks.pid
JOIN pg_stat_activity   blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

The result lists each blocked backend together with the backend that holds the conflicting lock.

Optional: cancelling lock‑holding backends

If business requirements allow it, you can abort the blocking transaction to create a brief window for the ALTER TABLE command:

SELECT pg_cancel_backend(blocking_pid);

Use this sparingly, as cancellation may roll back user work and affect application experience. Before invoking it, verify that the blocked operation is safe to abort (e.g., read‑only reporting queries or idempotent background jobs).

Practical workflow

• Set a short lock_timeout (e.g., 50ms) and statement_timeout to avoid long waits.
• Attempt the ALTER TABLE. If it fails with “lock not available”, query pg_stat_activity/pg_locks to locate the blocker.
• When acceptable, run pg_cancel_backend() on the blocker.
• Immediately retry the ALTER TABLE. Optionally wrap the retry logic in a PL/pgSQL loop with jittered back‑off.

By combining real‑time monitoring with controlled cancellation, you can minimize downtime for schema changes that require exclusive locks while preserving overall system stability.

UUID v7 embeds a Unix‑epoch timestamp in the most‑significant bits of the identifier. Because the timestamp is stored in clear binary form, any consumer that can parse a UUID can recover the exact creation moment of a row. This “leak” is a privacy trade‑off: audit logs, user‑generated content, or personally‑identifiable records now carry an implicit time‑stamp that could be correlated with external events (e.g., login times, transaction windows) without additional database queries.

In contrast, UUID v4 is generated from cryptographically‑secure random bits and contains no temporal information. Decoding a v4 value yields only random data, so the creation time must be obtained from a separate column (e.g., created_at) that can be protected or masked according to compliance frameworks such as SOC 2, ISO 27001, or NIST 800‑53.

• When timestamp exposure is acceptable: systems that already store an explicit created_at column and do not treat the timestamp as sensitive can benefit from v7’s monotonic ordering.
• When privacy is paramount: applications handling health records (HIPAA), financial transactions (PCI‑DSS), or any data subject to strict audit requirements should prefer v4 or add a separate, encrypted timestamp column.

Practically, decoding a v7 timestamp is straightforward:

SELECT (uuidv7_column::text)::uuid >> 96 AS epoch_ms
FROM my_table
WHERE id = '018d5c5e-8b1a-7c00-9f2b-3e5d6a7b9c01';

The resulting millisecond value can be converted to a human‑readable date, revealing when the row was inserted.

Despite this privacy consideration, the migration to v7 delivered a strong return on investment. The change required only a single ALTER TABLE … ALTER COLUMN SET DEFAULT uuidv7() per table—a low‑effort schema modification. The primary cost was handling the exclusive lock, which the team mitigated with short lock_timeout settings, jittered retry loops, and optional query cancellation. The performance gains reported (6×‑23× faster multi‑row inserts on heavily accessed tables) outweighed the operational overhead, delivering measurable latency reductions and lower I/O without extensive code changes.

In summary, adopt v7 when insert throughput and index locality are critical and the timestamp exposure aligns with your data‑privacy policy; otherwise, retain v4 for maximal temporal anonymity.