prefer-bigint-over-int

What triggers it

Fires on a CreateStmt column whose ColumnDef carries a CONSTR_PRIMARY or CONSTR_FOREIGN constraint and whose type name is int2, int4, integer, or smallint.

What does not

Only CreateStmt is checked — ALTER TABLE ADD COLUMN or ALTER COLUMN TYPE on an existing table never trigger it. Temp tables are skipped, and a column is only flagged when it carries a PRIMARY KEY or FOREIGN KEY constraint and its type is smallint/integer — a plain INT column with no key constraint passes through untouched.

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 concern for the flagged statement — this is a brand-new CREATE TABLE, so there is no existing data to rewrite yet. The ACCESS EXCLUSIVE table rewrite only happens later, if the table grows past the range of INT and has to be migrated to BIGINT after the fact.

Why it matters

INT (4 bytes) maxes out at 2,147,483,647. Fast-growing tables or high-throughput systems can hit this limit. Changing from INT to BIGINT requires a full table rewrite. Start with BIGINT.

Unsafe, and safe

CREATE TABLE orders (id INT PRIMARY KEY);

CREATE TABLE orders (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

What it assumes

Assumes the table will grow large enough to approach 2.1 billion rows; for genuinely small or bounded tables, such as lookup tables, INT is fine forever and the extra 4 bytes per row from BIGINT is pure waste.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

⚠ [MP038] WARNING (line 1)
  Primary/foreign key column "id" on "orders" uses INT (max ~2.1B). Use BIGINT to avoid expensive future type migration.

  Safe alternative:
  -- Use BIGINT for primary/foreign key columns:
  -- "id" BIGINT PRIMARY KEY

  Why: INT primary keys overflow at ~2.1 billion rows. Migrating from INT to BIGINT on a large table requires a full table rewrite under ACCESS EXCLUSIVE lock, which can take hours. Starting with BIGINT avoids this expensive migration and costs only 4 extra bytes per row.
  Docs: https://migrationpilot.dev/rules/mp038

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 MP038
CREATE TABLE orders (id INT PRIMARY KEY);

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

rules:
  MP038: false

# or keep it, and downgrade it
rules:
  MP038:
    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.

Related rules

• MP039prefer-identity-over-serialuse IDENTITY instead of SERIAL
• MP045require-primary-keyevery table needs a primary key
• MP007no-column-type-changeINT to BIGINT migration is very expensive