Skip to main content

PG.L5.PK_NEAR_CEILING — A primary key whose sequence is closing in on its ceiling

  • Category: safety
  • Level: 5
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

What it checks​

A single-column integer primary key whose sequence has handed out at least half of the values the key can hold. The share is a setting:

'audit' => [
    'key_ceiling' => [
        'min_percent_used' => 50,
    ],
],

Reported:

Schema::create('events', function (Blueprint $table) {
    $table->increments('id');   // integer: 2,147,483,647 values
});

// Years later, events_id_seq has handed out 1,932,735,283 of them: 90.0% of the ceiling.

Not reported, because the key was chosen wide enough when the table was created:

Schema::create('events', function (Blueprint $table) {
    $table->id();               // bigint
});

Not reported: the same table while its sequence is below the share, a bigint key on a bigint sequence, and a key no sequence fills.

A type choice, and then a date​

PG.L6.PK_NOT_BIGINT reports every key that can run out, on its type alone, from the day the table exists. That finding reads the same on a key that has handed out four values and on one that has handed out two billion, and a project can reasonably leave it for later at level 6.

This rule reads how far the sequence has got. Once a key has used half of its ceiling, it has handed out as many values as it has left: at the rate that brought it there, the room that remains lasts as long as the table has existed so far, and the insert rate of a growing table rarely stays where it was. From then on the failing INSERT is a date, and widening the key gets more expensive with every row.

The ceiling is the lower of two​

The column's type is one limit and the sequence's own maximum is the other. Measured on PostgreSQL 18:

CREATE TABLE w (id serial PRIMARY KEY);
ALTER TABLE w ALTER COLUMN id TYPE bigint;   -- the column is a bigint now
-- pg_sequences: w_id_seq  integer  max_value 2147483647
SELECT setval('w_id_seq', 2147483647);
INSERT INTO w DEFAULT VALUES;
-- ERROR:  nextval: reached maximum value of sequence "w_id_seq" (2147483647)

A serial column widened to bigint keeps its sequence AS integer, while an identity column widens its sequence with it. The finding names whichever limit binds, because the two cures differ. Widening a column rewrites the table. ALTER SEQUENCE w_id_seq AS bigint raises a maximum that was the default of the narrower type and leaves the table alone: measured, the table kept its file node, and the next insert took 2,147,483,648. A maximum set by hand needs NO MAXVALUE in the same statement.

Where the position comes from, and the privilege it needs​

The position is read beside the catalog with pg_sequence_last_value(). The sequence is found through pg_depend, by object id: the dependency serial and OWNED BY record, the one an identity column records, and failing both the one a DEFAULT nextval(...) records.

PostgreSQL answers the position with NULL for a role that holds neither SELECT nor USAGE on the sequence, and with the same NULL for a sequence that has never handed out a value. pg_monitor, the recommended audit role, is such a role on every sequence it was not granted. The reading asks has_sequence_privilege() beside it, so the two NULLs stay apart, and a withheld position is reported as undetermined, with the grant that answers it:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO sqlens_audit;
-- sequences created later, by the role that runs your migrations:
ALTER DEFAULT PRIVILEGES FOR ROLE your_migration_role IN SCHEMA public
    GRANT SELECT ON SEQUENCES TO sqlens_audit;

SELECT on a sequence reads its position and nothing else; drawing a value needs USAGE or UPDATE.

Reading the position takes ROW EXCLUSIVE on the sequence until the reading ends, measured on PostgreSQL 18. That is the lock every nextval() takes, so it waits behind nothing an INSERT does not wait behind, and a sequence the role may not read is left untouched. A lock that is not granted within the session's lock timeout makes the reading unavailable, and the rule reports undetermined rather than the audit failing.

What it cannot read​

A key filled by the application, without a sequence, has no counter. Its fill could only be read from the table's rows, and an audit does not read rows.

With CACHE above 1, the position read is the last value the sequence reserved, which can stand ahead of the last value an insert used.

Sources​

The fix material this rule carries​

A finding from this rule carries machine-readable fix material. Which sequence depends on the statement:

  • none — Looked at, and there is no safe standard sequence — a statement, not an absence.
  • rewrite_avoidance — Reach the same end state without the operation that rewrites the whole table.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.