Skip to main content

PG.L6.PK_UUID_V4 — Random UUID primary key where PostgreSQL 18 offers a time-ordered one

  • Category: idiom
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none — nothing here is a schema change
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18 and newer

Why a random key costs more than it looks​

A v4 UUID is random by construction. Two rows inserted a millisecond apart get keys with nothing in common, so they belong on unrelated pages of the primary key's B-tree.

Every insert therefore dirties a different page. The set of pages the index is actively writing to is as large as the table rather than as small as its tail, so it stops fitting in cache; the server reads a page in to write one row and writes it back out again. Pages fill unevenly and split more often than a sequential key would make them.

None of that is a correctness problem, and on a small table none of it is measurable. That is why this rule is level 6 and an idiom: it is a recommendation about write behavior on a table that grows, not a defect to gate a deploy on.

PostgreSQL 18 added uuidv7(). The values carry a millisecond timestamp in their leading bits, so they sort by creation time while keeping the uniqueness a UUID is chosen for — new rows land at the end of the index, the way a sequence would put them.

Flagged​

Schema::create('orders', function (Blueprint $table) {
    $table->uuid('id')->primary()->default(DB::raw('gen_random_uuid()'));
});

uuid_generate_v4() from the uuid-ossp extension is judged the same way.

Preferred​

Schema::create('orders', function (Blueprint $table) {
    $table->uuid('id')->primary()->default(DB::raw('uuidv7()'));
});

The trade-off, which is real​

A v7 UUID encodes when it was created. Anybody holding one — in a URL, in an API response, in a support ticket — can read the row's creation time out of it to the millisecond. A v4 tells them nothing.

Where the identifier is public and the creation time is not, that is a reason to keep v4, and this rule is not the authority on your threat model. Ignore it deliberately in that case:

'audit' => [
    'ignore' => [
        'pairs' => [
            ['rule' => 'PG.L6.PK_UUID_V4', 'objects' => ['public.orders']],
        ],
    ],
],

The finding stays counted and listed under what hid it, so the decision remains visible rather than disappearing from the report.

When the application generates the value​

A UUID column with no server-side default tells this rule nothing on its own. Laravel generates UUIDs in the application, so an absent default is the ordinary shape rather than a signal, and whether those values are v4 or v7 is a fact about code an audit does not read.

The rule answers undetermined there, with the reason uuid_generation_unknown — not pass, because "nobody looked" and "this is fine" are different statements and only one of them is true.

Say which side generates them and the question becomes answerable:

'audit' => [
    // 'app', 'server', or null when the project has not decided.
    'uuid_generated_by' => 'app',
],

With app the recommendation applies to your application's generator instead of a server default. With server — and no server default in the catalog — the answer stays undetermined: the declaration and the schema disagree, and resolving that by believing the declaration would be picking the side that was easier to state.

Why the version window is declared, not checked​

uuidv7() does not exist before PostgreSQL 18. A recommendation to use it on 17 is not merely premature, it is advice that cannot be followed — and advice that cannot be followed is how a tool teaches people to skim its output.

So the rule declares a version window rather than mentioning the version in its message. Below 18 it is not applied at all, and the report says it was withheld, with the version that withheld it.