Choosing an identifier looks like a small schema decision until the table contains hundreds of millions of rows. The identifier appears in indexes, foreign keys, API routes, logs, messages and exported files. Replacing it later can affect far more than the column where it began.

UUIDv4, UUIDv7 and ULID all support generating identifiers without asking a central database for the next number. Their differences become useful when we examine insertion patterns, ordering, interoperability and the information exposed to callers.

Introduction

This article compares these identifiers as database design choices. The question is not simply which one is newest or shortest. It is which representation and generation policy fit the application's storage engine, workload and public contracts.

Consider an order service running in several regions. Each instance needs to assign an identifier before saving an order and publishing related events. The database must enforce uniqueness, API clients need a stable reference, and support staff need to locate the same order across systems.

An identifier can satisfy that identity requirement without establishing the order in which transactions committed. It also does not prove that a caller is allowed to access a record. Keeping identity, chronology and authorisation separate avoids several mistakes that otherwise appear to be identifier problems.

We will use a hypothetical order application to examine the trade-offs. The examples illustrate decisions to test against your own database rather than promising a universal performance improvement from changing one function call.

Start with the Responsibilities of an ID

A primary key identifies one row. A business reference identifies something meaningful to a person or external organisation. An idempotency key identifies a particular attempted operation. These values may be related, but they solve different problems.

An order might have an internal key, a public order identifier and a human-readable invoice number. Requiring one value to serve all three roles can introduce unnecessary constraints. A legally significant invoice sequence, for example, should not depend on the ordering properties of randomly generated identifiers.

Write down where generation happens. Server-side generation is straightforward when the client only needs an ID after creation. Offline clients may need to create records and relationships before reconnecting. Import pipelines may need to preserve source identifiers while also assigning local ones.

Then identify the scope of uniqueness. A key unique within one tenant is different from a key expected to remain unique after merging several tenants into an analytics store. If uniqueness depends on a composite key, every integration must carry the complete identity.

Finally, decide whether the identifier is public and immutable. Public IDs become part of bookmarks, webhook payloads and customer integrations. Their useful lifetime can outlast the service implementation that first generated them.

Understand the Three Formats

UUIDv4 uses a 128-bit UUID layout with version and variant bits, leaving 122 bits for random data. UUIDv7 places a millisecond Unix timestamp at the front of its UUID layout, with the remaining fields supporting randomness and implementation-specific ordering mechanisms within the standard's rules.

ULID also represents 128 bits. Its layout combines a 48-bit millisecond timestamp with 80 bits of randomness, and its conventional representation is a 26-character Base32 string. It is a separate identifier format, rather than another UUID version.

The authoritative descriptions are RFC 9562 for UUIDs and the ULID specification. A conforming format still leaves operational questions for the generator and storage system.

For a database application, the immediate distinction is insertion distribution. UUIDv4 values are spread across the key space. Time-prefixed values tend to arrive in a narrower region when the database compares their timestamp-bearing bytes in the expected order.

That tendency is useful, but it is not a guarantee of transaction order, globally increasing values, or identical sorting across every database type. Those properties must be considered separately.

Why Index Locality Matters

Imagine a large sorted filing cabinet. Random keys require inserting new records into many different drawers. Time-prefixed keys often direct recent records towards a smaller set of drawers. The analogy explains locality, although a real database also has page management, caching and concurrency controls.

With a B-tree index, inserts must locate a leaf page and maintain its ordering. If incoming keys touch many pages, the active working set can be larger. Pages that cannot accommodate another entry may require additional work to maintain the structure.

When inserts are concentrated near recently used pages, the database may benefit from better cache locality. However, concentration can also create contention at high concurrency. An ordered key is not a substitute for measuring the storage engine's actual bottleneck.

The table's physical organisation matters. A primary key used for clustered storage has different consequences from a primary-key index attached to separately stored rows. Secondary indexes may carry primary-key values, increasing the effect of key width.

PostgreSQL's index type documentation describes B-tree support for equality and ordered comparisons. Use the documentation for your engine to understand how that index relates to the underlying table.

UUIDv4: A Simple Distributed Default

UUIDv4 is attractive when several independent producers need identifiers and there is no requirement to encode time. Most programming environments and database drivers already understand UUID values, reducing the amount of custom integration code.

Random distribution can also be useful when a storage system partitions by key ranges and a time-prefixed key would concentrate new writes. Whether it helps depends on the partition strategy. A database that hashes the identifier before placement changes the relationship between its visible value and physical distribution.

The main application-level limitation is that the identifier provides no useful chronological ordering. Sorting random UUIDs does not return the newest orders. Add an explicit creation timestamp and an appropriate index for that query.

Use a reliable generator with suitable randomness. The practical collision risk of correctly generated values is extremely small, but copied generator state, faulty libraries and application bugs are different failure modes from the ideal random model.

Keep a database uniqueness constraint. If an insertion fails because an ID already exists, investigate whether it represents an accidental duplicate request, a reused business object, or a generation defect. Blindly generating another ID may conceal the real problem.

UUIDv7: Time Locality with UUID Interoperability

UUIDv7 can be a useful candidate when the application wants UUID-compatible storage and tooling while keeping newly generated keys approximately grouped by time. Existing fields, serializers and route parsers that accept UUIDs may require little structural change.

That compatibility needs verification rather than assumption. A validator may explicitly accept only version 4. A database trigger might inspect part of the value. An integration may have undocumented expectations about generated identifiers.

In modern .NET, Guid.CreateVersion7 provides a built-in generation API. Its documented implementation uses the current time and random data in the remaining random subfields.

Guid orderId = Guid.CreateVersion7();

var order = new Order
{
    Id = orderId,
    CreatedAt = DateTimeOffset.UtcNow,
    Status = OrderStatus.Pending
};

The timestamp remains an explicit business field. It can represent the application's chosen creation event, have a documented precision, and be queried without interpreting the identifier.

Do not describe every UUIDv7 generator as strictly increasing. In particular, random values generated within the same timestamp interval need not sort in generation order. If a workflow requires a sequence, give that sequence its own authority and contract.

ULID: A Compact Textual Identifier

ULID is useful when a compact, sortable textual representation fits the surrounding ecosystem. A 26-character identifier can be easier to include in logs and operational interfaces than a conventional hyphenated UUID string.

The textual length does not automatically determine database storage efficiency. A ULID stored as text and a UUID stored in a native 16-byte field are different representations. A binary ULID representation may avoid text overhead, but it needs a consistent conversion contract across libraries and services.

The ULID specification describes a monotonic generation option for values created in the same millisecond. That behaviour belongs to a particular generator's state. It does not make independently running generators agree on a global sequence.

Choose a maintained implementation and check its behaviour for clock changes, concurrency and overflow. Verify whether its advertised monotonic mode is thread-safe and whether the application actually enables that mode.

If storing the conventional text, use a comparison and normalisation policy that preserves the intended order. Do not assume a locale-sensitive collation treats every identifier character exactly like an ordinal comparison. Canonicalise case at a boundary and reject malformed values consistently.

Representation Can Undo the Intended Benefit

A UUID value, its displayed string and a byte array are related representations with different APIs. Mixing them without a documented conversion can cause values to sort differently or fail to round-trip across languages.

For example, an application may create a time-prefixed identifier, write a byte array using one byte-order convention, then compare the stored bytes using another. The visible UUID can look correct while the expected index locality disappears.

Database comparison rules also matter. Microsoft's SQL Server uniqueidentifier documentation explicitly distinguishes its ordering from a simple comparison of the value's bit patterns. Do not assume a UUIDv7 value stored as uniqueidentifier automatically gives timestamp-first index behaviour.

Prefer the database driver's supported native mapping unless there is a measured reason to choose a custom representation. If using a binary field, document the encoding, parsing and ordering in a shared contract and test it across every producer.

Create a small set of fixed identifiers with known timestamps. Store them, retrieve them, sort them in the application and database, and compare the results. This catches integration mistakes before a large benchmark produces misleading conclusions.

Compare IDs with Integer Keys Fairly

An integer generated by the database remains a reasonable primary key for many applications. It is compact, familiar and often well supported by the engine's insertion path. Distributed generation is a requirement to establish, not an automatic requirement for every web service.

An application can combine an internal integer primary key with a unique public UUID. This keeps a compact join key while exposing a different identifier in APIs. The trade-off is another unique index and a mapping that every relevant operation must maintain.

For a single database handling all authoritative writes, a sequence may be simpler than generating keys across services. For offline creation or merging independent data sets, globally usable generated identifiers can reduce coordination.

Do not compare a table containing one integer index with a UUID design containing several additional indexes and conclude that the generator caused all of the difference. Keep the logical schema and access patterns equivalent.

Also consider foreign keys. The cost of a wider identifier is multiplied when it appears in large child tables. A modest difference in one orders table may become more significant across order lines, events and audit records.

Keep Ordering Separate from Identity

Suppose two services generate identifiers during the same millisecond. One immediately commits its transaction. The other pauses for several seconds before writing. An ID comparison cannot reliably tell you which transaction became durable first.

Clock skew creates another distinction. A producer whose clock runs ahead can generate values that sort after records created later by another producer. Synchronised clocks reduce the discrepancy but do not establish a distributed commit sequence.

For a message stream, use the ordering mechanism of the stream or an application sequence assigned by its authoritative writer. For an audit trail, define whether the timestamp means occurrence, observation, ingestion or commit.

A time-prefixed ID is still useful as a stable tie-breaker when the product accepts the chosen ordering. For example, a list can sort by an explicit creation timestamp and then by identifier. The contract must acknowledge that this is the application's presentation order.

Avoid using ID ranges to prove that every earlier operation has completed. A delayed writer can introduce an older generated value after a consumer has already passed that point. Reliable incremental processing needs an appropriate change feed, sequence or checkpoint protocol.

Design Pagination Around a Stable Contract

An order list might use the following conceptual ordering:

ORDER BY created_at DESC, id DESC

Its cursor carries both values from the final returned row. The next query uses the same comparison rules and filters. The identifier makes rows with equal timestamps distinguishable, while the timestamp communicates the chosen time dimension.

The supporting index should reflect the actual scope of the query. For a tenant-specific order list, an index beginning with tenant_id may be more useful than an index on the identifier alone.

Do not expose a raw timestamp extracted from a UUID as a universal continuation token. Changing the generator, importing historical rows or accepting offline writes can undermine assumptions that seemed safe during initial development.

Document what happens when records are created or updated between pages. If the UI requires a frozen snapshot, a keyset cursor alone does not supply it. That guarantee needs additional versioning or snapshot semantics.

This is why replacing random IDs with time-prefixed IDs rarely eliminates the need for explicit query design. It changes one part of the storage behaviour, while filtering, visibility and consistency still determine the user's result.

Treat Public Identifiers as Public Information

An unpredictable identifier can make casual enumeration harder, but knowing or guessing it must not grant access. Every operation still needs authorisation against the caller and the requested resource.

Time-prefixed identifiers can reveal approximate generation time. That may be acceptable for a public blog post and less desirable for a sensitive workflow. Evaluate the information the application exposes rather than assuming an opaque-looking string contains no meaning.

Do not use an order ID as a password-reset secret, invitation credential or bearer token. These values have different security requirements, including expiry, revocation and deliberate resistance to guessing.

When clients provide identifiers, validate their syntax and allowed use. A client-generated UUIDv7 timestamp should not be trusted as the authoritative time of a financial action, account creation or permission change.

Separate public representation from internal implementation where useful. A service can preserve a stable external ID even if its internal database keys change. That separation has a cost, but it can make future migrations much less disruptive.

Plan for Imports, Restarts and Clock Problems

Imports are a common source of misleading benchmarks and unexpected ordering. Historical records may receive freshly generated IDs during import, or the importer may preserve old source IDs. Those choices produce different insertion patterns.

Keep the source creation timestamp independently of local ingestion time. An imported order created last year should not silently appear to have been created today because its new identifier includes today's timestamp.

For stateful monotonic generators, process restarts and restored machine images deserve explicit tests. The generator must follow its documented uniqueness rules without assuming that in-memory counters survive a restart.

Exercise clock rollback and rapid concurrent generation in an isolated test. The desired response may involve preserving a last-seen timestamp or generating values under another documented policy. Avoid inventing custom behaviour around a library without understanding its guarantees.

Operational alerts should distinguish a malformed identifier, a duplicate database key and an ordering surprise. These symptoms have different causes. Capturing the generator version and producer identity in diagnostic context can help investigate them without changing the identifier itself.

Benchmark the Workload You Actually Have

Build comparable tables with the same payload, constraints and secondary indexes. Generate enough rows that the relevant indexes exceed a trivial in-memory working set, while keeping the experiment within a controlled environment.

Measure steady insertion throughput, latency percentiles, CPU, storage activity and index size. Add the read queries the application cares about. A key choice that improves isolated inserts but worsens a frequent join is not automatically a win.

Test both one writer and realistic concurrent writers. Repeat with the expected mix of updates, deletes and reads. Include batches if production uses them rather than inserting every row in an artificial single-row loop.

Report the engine version, schema, hardware allocation, generator and representation. A statement such as “UUIDv7 was faster” is not reproducible without those details. Identify whether the test measured primary-key locality, payload storage, network overhead or some combination.

Use the results to choose between plausible designs. Do not keep adjusting the benchmark until it proves the identifier you wanted to adopt. A negligible difference under realistic traffic is useful evidence that a migration may not be worth its cost.

Migrate Without Breaking Existing References

For a table already using UUIDv4 in a UUID-compatible column, changing future generation to UUIDv7 may allow old and new values to coexist. Whether this is safe depends on validators, integrations and database comparison rules.

There is usually no reason to rewrite every historical primary key merely to make the column contain one version. Rewriting identity affects foreign keys, cached references, audit trails and external consumers. The operational risk can exceed the measured benefit.

If a new public identifier column is required, add it alongside the existing key, backfill it carefully, enforce uniqueness and update readers through a staged migration. Preserve lookup compatibility for existing external references.

During a mixed deployment, old and new application instances may generate different formats. Make that a supported state before rolling out the change. Rollback should remain possible without deleting rows created by the new version.

Monitor invalid-ID errors, failed lookups and duplicate-key exceptions during the rollout. A parser rejecting version 7 can be more damaging to the application than the original performance issue you intended to address.

Make the Schema Express the Intended Guarantees

Consider a simplified order table in which the application supplies the identifier. The following PostgreSQL example separates identity from creation time and places tenant scope in the index used to list recent orders:

CREATE TABLE orders (
    id uuid PRIMARY KEY,
    tenant_id uuid NOT NULL,
    created_at timestamptz NOT NULL,
    status text NOT NULL,
    total_minor_units bigint NOT NULL
);

CREATE INDEX orders_tenant_created_idx
    ON orders (tenant_id, created_at DESC, id DESC);

The primary key enforces uniqueness for the entire table. The second index serves a different access pattern. It does not become redundant merely because some identifiers encode time. A query restricted to one tenant still needs an efficient way to find that tenant's rows.

The example deliberately leaves status validation and money-related rules to a fuller domain schema. Choosing an ID format should not distract from constraints that prevent invalid business data. Similarly, child relationships need foreign keys and an explicit approach to tenant boundaries.

If the application instead chooses tenant-scoped identifiers, its primary key might contain both tenant_id and id. That changes every reference to an order: the pair becomes the identity. A globally generated value can still be useful inside that design, but callers must not accidentally omit the tenant component from lookups.

Notice that the schema does not enforce one UUID version. That can be intentional when old and new generator versions coexist. If a version restriction is required, document why and include migration and import behaviour in the rule. A constraint added for tidiness can otherwise reject legitimate historical references.

The schema makes the decision reviewable. A colleague can see which column establishes identity, which values establish presentation order and which index serves the common listing query without reverse-engineering assumptions from the identifier's bytes.

Follow an Identifier Through a Retried Create Request

Imagine a caller submits an order, the database commits, and the response is lost. The caller retries because it cannot distinguish a failed operation from a failed response. Generating a fresh UUID for every incoming request can now create two valid orders with two different IDs.

Neither UUIDv7 nor ULID fixes that problem. The application needs to recognise that the repeated request represents the same intended operation. An idempotency record can associate the caller's operation key, request fingerprint and resulting order identifier within the appropriate transaction boundary.

On a matching retry, the service returns the original order reference. If the same operation key arrives with materially different content, the service rejects that reuse according to its API contract. The identity of the order remains stable even though several network attempts occurred.

Generating the order ID before starting the transaction can simplify constructing related records and outgoing events. Keep that value with the logical operation while retrying a transient database failure. However, a retry policy still needs to distinguish a transaction known to have rolled back from one whose outcome is uncertain.

When a client supplies the order ID, the server must also decide whether that namespace is trusted and how conflicts are handled. An existing identifier must not allow a new request to overwrite another customer's order. Uniqueness and authorisation checks work together here.

Walking through this failure shows where identifier selection fits. It gives the order a durable name. Transaction handling determines whether its records commit together, and idempotency determines whether an uncertain request can be repeated safely. Each guarantee needs an explicit owner.

Work Through a Practical Choice

Suppose our order service uses PostgreSQL, creates orders in several application instances and exposes UUIDs publicly. Most lookups use equality, while recent-order pages use tenant_id and created_at. The current system uses UUIDv4 successfully, but its primary-key index contributes meaningfully to write I/O at projected scale.

A UUIDv7 trial is reasonable because the native UUID representation can remain unchanged. The team checks its application and database versions, validates cross-language generation and benchmarks the actual schema under concurrent load.

If measurements show a useful improvement, new orders can adopt the new generator while historical IDs remain valid. The explicit created_at column and tenant-scoped pagination index remain because they serve the user-facing query.

Now change the scenario to a SQL Server application using uniqueidentifier as its clustered key. The same reasoning cannot skip the engine's comparison behaviour. The team must test how the chosen values actually sort and whether a different physical-key design is more appropriate.

For an application already built around ULID strings and interoperable libraries, staying with ULID may be simpler than moving to UUIDv7. The existence of a newer standard is not itself a business requirement to replace a working contract.

Record the Decision and Its Limits

A useful decision record names the generator, storage type, textual representation and uniqueness enforcement. It also states what the identifier does not promise, especially global chronology and authorisation.

Include a fixed set of interoperability examples. Teams working in another language should be able to generate, parse, store and compare the same values without discovering undocumented conversion rules through production failures.

Explain the benchmark conditions and the outcome that justified the choice. If the main benefit is interoperability rather than speed, say so. That makes the decision easier to revisit when the workload changes.

Keep application code from depending unnecessarily on the internal bits. A dedicated parser can validate a value, but business rules should use explicit fields for tenant ownership, timestamps and lifecycle state. Otherwise a future generator change becomes a hidden domain-model migration.

Summary

UUIDv4 provides widely supported random identifiers, UUIDv7 combines UUID compatibility with a time-prefixed layout, and ULID offers a compact textual format with its own generation rules. The right choice depends on how the application generates, stores, compares and exposes IDs.

Measure index behaviour in the actual database, preserve uniqueness constraints and keep chronology, authorisation and business references separate. A successful identifier design provides stable identity across the application's lifetime without asking one value to solve every ordering or security problem.