Skip to main content

PG.L6.PK_NOT_BIGINT — An integer primary key too narrow to grow into

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

The timing is the whole problem​

A signed integer runs out at 2,147,483,647 and a smallint at 32,767. Nothing warns on the way there. The first symptom is an INSERT failing on a table that has been working for years.

And the moment the fix becomes necessary is precisely the moment it is most expensive: widening the key rewrites the table and every index over it, under a lock, on the largest table you have.

Chosen early, the same decision costs four bytes a row. That asymmetry is the entire argument, and it is why this sits at level 6 — an appetite for being told now, not a defect to fail a build on.

Flagged​

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

Preferred​

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

Widen the referencing columns in the same change​

A key widened on its own is half a fix: every foreign-key column pointing at it has to widen too, or the constraint stops matching.

Those columns live on other tables, and an audit judges one object at a time — so this finding says they must be widened without naming them. Claiming to enumerate them from here would be a promise the rule cannot keep.

A bigint key on an integer sequence​

The column's type is not the whole width of a key. 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 with ALTER COLUMN ... TYPE bigint keeps its sequence AS integer, so the key runs out at 2,147,483,647 as before, while an identity column widens its sequence with it. The rule reads the sequence behind the key and its maximum from the catalog, and reports a bigint key whose sequence stops at or below 2,147,483,647, with the cure: 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.

A narrow serial key is told the same in its finding, because widening its column alone would leave the sequence behind. A maximum above the integer range and below the bigint one is a cap somebody chose, and is not reported.

How full the key is, and which rule reads that​

This rule judges the type and stays blind to how far the key has got, so one schema reads the same on every run. How far the sequence has got is read beside the catalog by PG.L5.PK_NEAR_CEILING, which reports the same key one level earlier once it has used half of its ceiling, or the share set in sqlens.audit.key_ceiling.min_percent_used.

That reading needs SELECT on the sequence, which the recommended audit role does not hold. Without it PostgreSQL answers the position with NULL, the same NULL a sequence that has never handed out a value returns, and the level-5 rule reports the key as undetermined rather than as fine.

The framework's own migrations ledger is exempt​

Laravel creates migrations itself, from DatabaseMigrationRepository, with increments('id') — on every migrate:install and regardless of what the application would prefer. The advice this rule gives cannot be taken there: the table belongs to the framework, and rewriting it would mean writing against the framework's own default on a table the application does not own.

So the rule skips it, and it is the only table it treats that way. The name comes from database.migrations.table rather than from a constant here — rename the ledger and an ordinary table called migrations is judged like any other.

What is not flagged​

A bigint key whose sequence reaches past the integer range, or that no sequence fills, a UUID or text key, and any composite key. A composite key's range is the product of its parts, so "too narrow" stops being a property of one column, and a rule reporting one anyway would be doing arithmetic nobody asked it to do.

A small lookup table will never approach the ceiling and gains nothing from widening. Ignore those deliberately rather than converting the whole schema:

'audit' => [
'ignore' => [
'pairs' => [
['rule' => 'PG.L6.PK_NOT_BIGINT', 'objects' => ['public.currencies']],
],
],
],