PgDesigner
Engineering

66 Lint Rules for PostgreSQL Schemas — What We Check and Why

vmkteam •
#postgresql#lint#schema-design#best-practices

Most PostgreSQL schemas accumulate design debt silently. A missing index on a foreign key doesn’t break anything — until a JOIN takes 40 seconds in production. A char(n) column works fine — until you discover it pads every value with spaces and breaks equality comparisons with text.

PgDesigner ships 66 lint rules to catch these issues at design time. Here’s the reasoning behind the most impactful ones.

PgDesigner Check Diagram showing lint results with warnings and info rules on Northwind database in light theme

Three severity levels

Errors (32 rules) — structural problems that make your schema invalid. FK pointing to a non-existent table, duplicate column names, unknown data types. These block DDL generation — you can’t ship a broken schema.

Warnings (21 rules) — valid SQL that’s almost certainly wrong. FK type mismatches, missing indexes, circular dependencies, reserved words as identifiers. Your schema will run, but you’ll pay for it later.

Info (13 rules) — antipatterns and style suggestions. serial vs identity, json vs jsonb, timestamp vs timestamptz. Not bugs, but modernizing these saves headaches.

The rules that matter most

W002: FK columns have no matching index

This is the single most common performance issue in PostgreSQL schemas. Every foreign key implies a JOIN. Without an index on the FK columns, PostgreSQL needs to scan the child table on every DELETE from the parent — to check if any rows still reference the deleted parent. On large tables, this means a sequential scan (PostgreSQL docs: Foreign Keys).

-- Table: orders
-- FK: orders.customer_id -> customers.id
-- Missing: CREATE INDEX ON orders (customer_id)

PgDesigner flags this as W002 and offers one-click autofix: it creates a btree index on the FK columns. In the AdventureWorks sample database (68 tables, 69 FKs), 12 missing FK indexes were caught on first lint.

W001: FK column type mismatch

PostgreSQL requires FK columns to have compatible types. A mismatch between bigint and integer causes CREATE TABLE to fail with “Key columns are of incompatible types.” But subtler mismatches slip through — varchar(50) referencing varchar(100), or numeric(10,2) vs numeric(12,2). These create implicit casts on every JOIN, defeating index usage.

PgDesigner checks that FK source and target column types match exactly — catching mismatches before you run any SQL.

E031: Multiple identity columns

The SQL standard allows only one IDENTITY column per table. PostgreSQL relaxes this restriction — you can create multiple identity columns, but INSERT only supports a single OVERRIDING clause, making it impractical to use more than one. Multiple identity columns almost always indicate a design error. PgDesigner flags this so you catch it at design time (PostgreSQL docs: Identity Columns).

I001: Prefer text over char(n)

char(n) in PostgreSQL pads values with spaces to the declared length. This means:

  • char(100) storing “hello” actually stores “hello” + 95 spaces — wasting storage
  • Trailing spaces cause subtle bugs: comparisons between char(n) values ignore padding, but mixing char(n) with text in application code leads to unexpected mismatches
  • char(n) is actually the slowest of the three string types due to padding overhead. varchar(n) and text are identical in performance

The autofix converts char(n) to text (or varchar(n) if you prefer a length constraint).

I004: Prefer identity over serial

serial is a legacy pattern that creates an implicit sequence with OWNED BY. Problems:

  • serial columns aren’t part of the SQL standard — they’re a PostgreSQL-specific shorthand
  • The column is NOT NULL DEFAULT nextval(...) — not the same as GENERATED ALWAYS AS IDENTITY
  • CREATE TABLE ... (LIKE ... INCLUDING ALL) copies the nextval() default but shares the same sequence between both tables — a subtle and dangerous gotcha
  • Historical pg_dump edge cases with sequence ownership, mostly fixed in modern versions

IDENTITY (SQL:2003) is cleaner: the sequence is bound to the column, LIKE INCLUDING IDENTITY creates an independent sequence, and GENERATED ALWAYS restricts manual inserts (bypassable with OVERRIDING SYSTEM VALUE when needed).

I005: Prefer timestamptz over timestamp

timestamp without time zone stores a wall-clock time with no timezone information. This seems simpler until:

  • Your application servers are in different timezones
  • Daylight saving time changes shift stored times by an hour
  • You try to compare timestamps from two systems

timestamptz stores everything as UTC internally and converts on display based on the session timezone. It’s always the right choice unless you’re explicitly modeling timezone-agnostic data (like “every Monday at 9am regardless of location”).

W020: Reserved word as identifier

Using user, order, group, table, or select as a table or column name works — if you always quote it. The moment someone writes SELECT * FROM user without quotes, the query fails. PgDesigner checks identifiers against the PostgreSQL key words list covering hundreds of reserved and non-reserved words.

15 rules with autofix

Not every rule needs manual intervention. PgDesigner can automatically fix:

RuleFix
W002Create btree index on FK columns
W004Add identity integer PK column
W005Drop duplicate index
W010Remove default from identity column
W011Remove default from generated column
W012Set PK column to NOT NULL
W015Change FK NO ACTION to RESTRICT
W017Drop overlapping prefix index
W018Drop duplicate FK
I001Convert char(n) to text
I003Convert money to numeric
I004Convert serial to identity
I005Convert timestamp to timestamptz
I006Convert timetz to time
I009Convert json to jsonb

In the UI, click the fix button next to any issue. In the CLI:

pgdesigner lint -fix schema.pgd

Lint in CI/CD

Add schema validation to your pipeline:

pgdesigner lint schema.pgd

Exit code 1 if any errors are found. Use flags to customize:

pgdesigner lint -s error schema.pgd      # errors only
pgdesigner lint -s warning schema.pgd    # errors + warnings
pgdesigner lint -f json schema.pgd       # machine-readable output

JSON output includes rule code, severity, table, column, and message — easy to parse with jq or feed into your monitoring.

How we picked these 66 rules

Every rule comes from real-world experience across 6 production databases (630+ tables total). We didn’t invent problems to solve — we catalogued the issues we found when importing real schemas from MicroOLAP, DbSchema, and live PostgreSQL instances.

The rule set will grow. If you have a rule suggestion, open an issue on GitHub.

References

← Back to Blog