How PostgreSQL 18's `uuidv7()` and `uuid_extract_timestamp()` Speed Up Time-Series Queries by 44% Without `created_at`

When designing tables, you've probably written id UUID PRIMARY KEY DEFAULT gen_random_uuid() and then automatically added created_at TIMESTAMPTZ NOT NULL DEFAULT now() right below it. I did the same for years and even baked it into our team's schema template. But now that PostgreSQL 18 ships uuidv7() as a native function, there's good reason to revisit this habit.

UUIDv7 embeds a Unix epoch millisecond timestamp in its upper 48 bits, meaning the ID itself already carries the creation time. In other words, a single id column can handle both primary key lookups and time-series range filters without a created_at column at all. This post covers everything from the internal structure of uuidv7(), to range query patterns that fully leverage indexes, to extracting the creation time with uuid_extract_timestamp(), and three pitfalls I learned the hard way.

Core Concepts

UUIDv7 Structure — An ID with a Built-In Timestamp

Note: PostgreSQL 18 is scheduled for general availability in September 2025. In the meantime, you can try it today via the pg_uuidv7 extension or on PostgreSQL 18-based platforms like Neon.

Standardized by RFC 9562 (finalized May 2024), UUIDv7 divides its 128 bits as follows:

UUIDv7 Structure (128 bits)
├── [0-47]   48-bit Unix epoch timestamp (milliseconds)
├── [48-59]  12-bit sub-millisecond fraction (guarantees monotonicity)
├── [60-63]   4-bit version (0111 = 7)
├── [64-65]   2-bit variant
└── [66-127] 62-bit random

Because the upper 48 bits are a millisecond timestamp, lexicographic ordering is equivalent to chronological ordering. On top of that, PostgreSQL 18 adds a 12-bit sub-millisecond fraction, guaranteeing monotonicity — meaning multiple UUIDs generated within the same millisecond in the same session will never be out of order.

Monotonicity is the property that guarantees each newly generated value is always greater than or equal to the previous one. From a B-tree index perspective, insertions always land near the rightmost leaf, minimizing page splits and fragmentation. This is the opposite of UUIDv4, which is fully random and must be inserted anywhere in the B-tree.

Benchmarks on tables with millions of rows show approximately 44% reduction in query execution time compared to UUIDv4, with buffer hits dropping from 101 to 3. Results will vary by data scale and hardware, but the direction is clear.

uuid_extract_timestamp() — Extracting the Creation Time from an ID

SELECT
  uuidv7()                         AS new_uuid,
  CURRENT_TIMESTAMP                AS actual_time,
  uuid_extract_timestamp(uuidv7()) AS extracted_time;

Previously, uuid_extract_timestamp() was limited to UUID v1. Its expanded support for v7 in PostgreSQL 18 makes it far more practical. Passing a non-v7 UUID returns NULL.

In environments where UUIDv4 and UUIDv7 data coexist, it's safer to verify uuid_extract_version(id) = 7 beforehand. Have you ever called uuid_extract_timestamp() on a legacy table only to get a result full of NULLs?

Practical Application

Example 1: Designing a Table Without created_at

Honestly, when I first saw this pattern, my reaction was "how does dropping one column matter that much?" But when you actually do the math, removing a column also removes its index. The overhead of updating two indexes on every write transaction drops to one.

-- Old pattern: requires maintaining two indexes — PK + created_at
CREATE TABLE events (
  id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  payload    JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_events_created_at ON events(created_at);
 
-- PostgreSQL 18 pattern: one B-tree index on the id column is enough
CREATE TABLE events (
  id      UUID  PRIMARY KEY DEFAULT uuidv7(),
  payload JSONB
);

Example 2: Time-Series Range Queries and Index Usage

Once you build that table, the pitfall becomes visible the moment you try a time-series range query. The most natural first attempt looks like this:

-- Intuitive but doesn't use the index (caution)
SELECT id, uuid_extract_timestamp(id) AS ts, payload
FROM events
WHERE uuid_extract_timestamp(id) >= NOW() - INTERVAL '7 days'
ORDER BY id DESC
LIMIT 100;

I wrote queries this way at first, then stopped cold when I looked at EXPLAIN ANALYZE. Using uuid_extract_timestamp(id) directly in a WHERE clause creates a function-based expression that triggers a Sequential Scan instead of an index scan.

-- EXPLAIN ANALYZE output (no index used)
Seq Scan on events  (cost=0.00..3541.00 rows=333 width=48)
  Filter: (uuid_extract_timestamp(id) >= (now() - '7 days'::interval))

To fully leverage the index, I recommend creating a helper function that converts a timestamp into a UUIDv7 boundary value.

In one sentence, what this function does is: convert a timestamp into the smallest possible UUIDv7 value for that millisecond.

CREATE OR REPLACE FUNCTION uuid_from_timestamp(ts TIMESTAMPTZ)
RETURNS UUID AS $$
DECLARE
  -- EXTRACT(EPOCH) returns floating point, so cast explicitly to ::BIGINT
  ms  BIGINT := (EXTRACT(EPOCH FROM ts) * 1000)::BIGINT;
  hex TEXT;
BEGIN
  hex := lpad(to_hex(ms), 12, '0')
      -- version bits 0111(=7) → hex '7', next 12 bits are minimum '000'
      || '7000'
      -- variant minimum 10000000 → hex '80', next 8 bits are minimum '00'
      || '8000'
      || '000000000000';
  RETURN (
    substring(hex, 1, 8)  || '-' ||
    substring(hex, 9, 4)  || '-' ||
    substring(hex, 13, 4) || '-' ||
    substring(hex, 17, 4) || '-' ||
    substring(hex, 21)
  )::UUID;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Declaring it IMMUTABLE allows the query planner to apply constant folding, which is also beneficial for performance.

One important caveat: because this function produces the smallest UUIDv7 value for a given millisecond, using it for the upper bound in the same way will introduce a boundary bug. uuid_from_timestamp('2025-01-31 23:59:59+00') returns the minimum UUID for that millisecond, so any UUID generated in that same millisecond with random bits greater than zero will fall outside the range. The safe approach is to use the start of the next boundary as the upper bound.

-- Safe range query that uses an Index Scan on the id index
SELECT id, payload
FROM events
WHERE id >= uuid_from_timestamp('2025-01-01 00:00:00+00')
  AND id <  uuid_from_timestamp('2025-02-01 00:00:00+00')  -- next month's start as upper bound
ORDER BY id;

-- EXPLAIN ANALYZE output (index used)
Index Scan using events_pkey on events  (cost=0.43..8.45 rows=1 width=48)
  Index Cond: ((id >= '01942e80-...'::uuid) AND (id < '0194a800-...'::uuid))

Example 3: Cursor-Based Pagination

The helper function we built earlier fits naturally into cursor-based pagination as well. OFFSET-based pagination degrades linearly as depth increases — even with an index, a large OFFSET 10000 still requires counting and skipping all preceding rows. UUIDv7's monotonically increasing, time-ordered nature makes it a natural fit for keyset pagination.

-- First page
SELECT id, uuid_extract_timestamp(id) AS created_at, payload
FROM events
ORDER BY id
LIMIT 20;
 
-- Use the last id from the previous page as a cursor
-- $1 = last id from previous page (passed as a bind parameter from the application)
SELECT id, uuid_extract_timestamp(id) AS created_at, payload
FROM events
WHERE id > $1
ORDER BY id
LIMIT 20;

Because the WHERE id > :cursor condition hits the B-tree index directly, you can expect consistent response times even on tables with millions of rows. One thing worth adding: I once exposed these cursor values directly in an external API response, only to realize later that creation timestamps could be extracted from the UUIDs — and got called out by my team for it. When exposing cursors externally, it's better to encrypt them or wrap them in opaque tokens.

Trade-off Analysis

Advantages

Disadvantages and Caveats

VIRTUAL vs. STORED generated columns: PostgreSQL 18 adds support for VIRTUAL generated columns that compute on read without being stored, but these cannot be indexed. If you want to index uuid_extract_timestamp(id) frequently, opt for GENERATED ALWAYS AS (...) STORED, or keep a traditional created_at column.

The Most Common Mistakes in Practice

1. Using uuid_extract_timestamp() filters directly in production queries: The pattern WHERE uuid_extract_timestamp(id) >= ... causes a Sequential Scan. Running EXPLAIN ANALYZE will reveal Seq Scan on events plain as day. Use the uuid_from_timestamp() helper to construct boundary UUIDs and rewrite it as WHERE id >= ... AND id < ....
2. Attaching the function to legacy tables without migration: Applying uuid_extract_timestamp() to an existing id column filled with UUIDv4 values will return NULL for all legacy data. Attaching the function without a migration only works for newly created records. Apply it to new tables first, or take a phased migration approach.
3. Trusting sub-millisecond ordering in multi-node environments: Monotonicity is only guaranteed within the same session. Expecting global ordering within the same millisecond across multiple servers or sessions will lead to unexpected ordering reversals. If strict global ordering is required, design your system with that constraint in mind.

Closing Thoughts

A single id column acting as primary key, timestamp container, and time-series index at once — that's the most practical change UUIDv7 brings.

Here are 3 steps you can take right now:

1. Set up a PostgreSQL 18 environment: Spin up the postgres:18beta image via Neon's free tier or Docker and verify behavior with a single line: SELECT uuidv7(), uuid_extract_timestamp(uuidv7());. On PostgreSQL 17 or earlier, you can preview the same function signatures using the pg_uuidv7 extension (CREATE EXTENSION pg_uuidv7;).
2. Add the helper function and redesign your tables: Add the uuid_from_timestamp() function introduced above to your migration file, and try writing new tables with the DEFAULT uuidv7() + no created_at pattern. If migrating existing tables is too costly, apply it to new tables first and run both patterns in parallel — that's the realistic approach.
3. Check query plans: Use EXPLAIN (ANALYZE, BUFFERS) to compare the Buffers hit/read numbers between your existing WHERE created_at >= ... query and the WHERE id >= uuid_from_timestamp(...) AND id < uuid_from_timestamp(...) query. The moment you see the difference between Seq Scan and Index Scan in real numbers, the case for switching becomes self-evident.

References

• PostgreSQL 18 Official Documentation — UUID Functions (9.14)
• PostgreSQL 18 Release Notes
• RFC 9562 — Universally Unique IDentifiers (UUIDs)
• UUIDv7 Comes to PostgreSQL 18 — The Nile
• UUID v7 in PostgreSQL 18 — Better Stack Community
• PostgreSQL 18 UUIDv7 Support — Neon
• UUIDv7 in PostgreSQL 18: What You Need to Know — DbVisualizer
• Exploring PostgreSQL 18's new UUIDv7 support — Aiven
• PostgreSQL 18's UUIDv7: Faster and Secure Time-Ordered IDs — Hashrocket
• PostgreSQL UUID Performance: Benchmarking Random (v4) and Time-based (v7) — DEV Community
• Postgres UUIDv7: Performance vs. Privacy
• Postgres UUIDv7 + per-backend monotonicity — brandur.org
• Get Excited About Postgres 18 — Crunchy Data
• uuid_extract_timestamp() — pgPedia