warn-integer-pk-capacity

What triggers it

CREATE SEQUENCE ... AS <type> where the as option's type name is int4, int2, integer, or smallint, read from the option list's DefElem with defname === 'as'.

What does not

CREATE SEQUENCE with no explicit AS type (new sequences default to bigint), or an explicit AS bigint/AS int8. The rule only inspects CreateSeqStmt, so a SERIAL column — which PostgreSQL expands into an int4 sequence during semantic analysis, after the raw parse tree this rule sees — isn't caught by this check.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

No lock applies to the flagged statement itself — creating a new sequence takes no meaningful lock. The lock concern the rule warns about is deferred: converting an already-in-use int4 sequence, and the column it feeds, to bigint later requires ACCESS EXCLUSIVE and a full table rewrite.

Why it matters

Integer sequences overflow at ~2.1 billion (int4) or ~32,000 (int2). When a sequence overflows, all INSERTs fail. Migrating from integer to bigint on a live sequence requires rewriting the dependent column under ACCESS EXCLUSIVE lock.

Unsafe, and safe

CREATE SEQUENCE user_id_seq AS integer;

CREATE SEQUENCE user_id_seq AS bigint;

What it assumes

The rule assumes any explicit small-integer sequence will eventually see enough inserts to overflow, which may not hold for a low-traffic table. It has no way to measure current insert rate, so it flags every explicit AS integer/AS smallint sequence the same whether overflow is decades away or imminent.

What the CLI prints

⚠ [MP068] WARNING (line 1)
  Sequence "user_id_seq" uses integer type (max ~2.1B). Use BIGINT to prevent overflow and avoid expensive future migration.

  Safe alternative:
  -- Use bigint for sequences to prevent overflow:
  CREATE SEQUENCE user_id_seq AS bigint;

  Why: Integer sequences overflow at ~2.1 billion (int4) or ~32,000 (int2). When a sequence overflows, all INSERTs fail with a "nextval: reached maximum value" error. Migrating from integer to bigint on a live sequence requires rewriting the dependent column under ACCESS EXCLUSIVE lock. Buildkite, Mailchimp, and others have reported major outages from this.
  Docs: https://migrationpilot.dev/rules/mp068

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP068
CREATE SEQUENCE user_id_seq AS integer;

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP068: false

# or keep it, and downgrade it
rules:
  MP068:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.