If you’ve tackled scaling databases for logging or auditing, you’ve undoubtedly faced the eternal question: how do you choose your primary key?

Traditional integer IDs work well initially, but rapidly falter when scaling beyond a single database instance. UUIDs stepped in to solve distributed generation, but not without drawbacks. UUID version 4 (random UUIDs), while easy to generate, scatter your data randomly across your indexes, causing bloat and slowing inserts. Enter UUIDv7, the elegant and powerful solution you’ve probably been waiting for.

A Quick History of UUIDs

UUIDs (Universally Unique Identifiers) have long been pivotal in distributed systems because they enable each node or client to generate unique identifiers independently—no central coordination required. Early versions like UUIDv1 combined timestamps with hardware addresses, whereas UUIDv4 went fully random for simplicity and privacy.

Example of UUIDv1:

6ba7b810-9dad-11d1-80b4-00c04fd430c8

Example of UUIDv4:

550e8400-e29b-41d4-a716-446655440000

Despite their popularity, UUIDv4 IDs introduce severe performance issues, primarily from their randomness causing scattered data placement in indexes, leading to fragmented storage and slower operations. In other words, they can be hell for database performance at scale.

Storage Considerations

UUIDs typically occupy 128 bits (16 bytes). While slightly larger than standard BIGINT identifiers (8 bytes), they offer an enormous address space, ensuring uniqueness across vast distributed systems without collision concerns. However, improper storage (such as storing UUIDs as strings rather than native binary formats) significantly inflates storage requirements. PostgreSQL’s native UUID type efficiently stores UUIDs in binary form, keeping overhead minimal.

What’s Special About UUIDv7?

UUID version 7 elegantly solves the randomness problem by embedding a timestamp directly within the identifier. Specifically, the first 48 bits of a UUIDv7 represent the timestamp in milliseconds since the Unix epoch, ensuring newly generated UUIDs sort after previous ones. This inherent order provides excellent insert locality—ideal for append-only workloads such as logs, audits, or event-driven data.

Here’s what a typical UUIDv7 looks like:

0188cd2f-76b5-7dc3-b6fb-8c09a2a024e8

Notice the structured format, embedding a clear timestamp-based ordering right at the start.

Leveraging UUIDv7 with PostgreSQL

PostgreSQL (from 8.3 onwards) has built-in support for UUID data types, making integration straightforward without requiring extensions. Here’s a simple schema setup:

CREATE TABLE audit_log (
 id UUID PRIMARY KEY,
 user_id BIGINT NOT NULL,
 action TEXT NOT NULL
) PARTITION BY RANGE (id);

-- Example partitions aligned by UUID ranges derived from timestamp intervals
CREATE TABLE audit_log_2025_06 PARTITION OF audit_log
    FOR VALUES FROM ('0188b200-0000-0000-0000-000000000000') TO ('0188dd80-0000-0000-0000-000000000000');

CREATE TABLE audit_log_2025_07 PARTITION OF audit_log
    FOR VALUES FROM ('0188dd80-0000-0000-0000-000000000000') TO ('01890900-0000-0000-0000-000000000000');

By partitioning directly on UUID ranges that correspond to date intervals, you align partitions naturally by time, removing complexity and guesswork. Data automatically flows into the correct partitions based on the inherent timestamp ordering of UUIDv7.

Later PostgreSQL Versions

While PostgreSQL handles UUIDv7 effectively without extensions, later PostgreSQL versions offer additional extensions specifically supporting UUIDv7. These extensions further streamline UUIDv7 generation and provide built-in functions to simplify interactions, such as directly extracting timestamps or effortlessly aligning partitions. This added support simplifies schema management and provides even greater flexibility.

Advantages of UUIDv7

• Zero coordination: Generate globally unique IDs independently without needing centralized services.
• Optimized storage and indexing: UUIDv7’s built-in timestamp significantly reduces index fragmentation, accelerating insert performance.
• Simplified partition management: Naturally aligned time-based partitions simplify archiving and ongoing management, avoiding manual interventions or guesswork.

Disadvantages to Consider

While UUIDv7 addresses many shortcomings of earlier UUID formats, it’s not without trade-offs. First, although 128 bits is manageable, it’s still twice the size of a BIGINT, meaning slightly more memory and storage consumption. Second, because UUIDv7 encodes the timestamp directly in the ID, it inherently leaks creation time. This may not be desirable in applications where exposing data timing or order has privacy or competitive implications, but for backend internal audit/logs not a consideration/

Though, you technically don’t need an additional created field TIMESTAMP which takes 8 bytes to store, so in the end UUIDv7 nets out to be neutral in the storage consideration.

The Power of Partitioning in PostgreSQL

Partitioning is one of PostgreSQL’s most powerful tools for managing large datasets efficiently. By breaking large tables into smaller, more manageable pieces—typically by range, list, or hash—PostgreSQL can prune irrelevant partitions during queries, dramatically improving performance.

For append-only workloads like logging or auditing, range partitioning by time (or time-derived UUIDv7 ranges) allows you to isolate active data from cold, archived data. It also makes purging old records as simple as dropping a partition, which is far more efficient than issuing delete statements. Coupled with UUIDv7’s time-sortable nature, partitioning becomes a natural fit for scale and manageability.

Practical Takeaway

UUIDv7 combines the best of both worlds—UUID’s scalability and uniqueness with sequential IDs’ performant indexing characteristics. It’s a compelling choice for logging, auditing, or any large-scale, append-only PostgreSQL datasets. With UUIDv7, you’ll stop worrying about partition range guesswork and enjoy a smoother, cleaner approach to scaling your databases.