Database schemas degrade when review focuses only on application code. Missing primary keys, unindexed foreign keys, redundant indexes, unsafe public privileges, mismatched foreign-key types, and unused objects can slip into production because they are valid SQL even though they create performance and maintenance risk.
PostgreSQL database linting brings static-analysis discipline to the live catalog. A linter extension such as pglinter runs inside PostgreSQL, queries system catalogs and statistics views, applies configurable rules, and emits violations that can be reviewed locally, in migrations, or in CI pipelines.
The transferable pattern is rule-based database quality automation. Instead of relying on a DBA to manually inspect every migration, teams encode baseline rules, thresholds, and output formats so schema quality becomes part of the delivery path.
Database linting inspects PostgreSQL’s own metadata: constraints, indexes, schemas, privileges, configuration, and statistics. That makes it different from SQL text linting. A migration may look reasonable as text but still create a redundant index or a foreign key without a supporting index when evaluated against the actual catalog.
pglinter groups checks into database-wide base rules, cluster configuration rules, and schema-level rules. Examples include tables without primary keys, redundant indexes, missing indexes on foreign keys, unsecured public schema settings, insecure host-based authentication entries, and schemas with broad public privileges.
Many rules are threshold based. A team may warn when a percentage of tables violates a guideline and fail when the percentage crosses a stricter threshold. This allows gradual adoption in legacy databases without blocking every change on day one.
The useful signal comes from the post-migration database state. Run migrations against an isolated database, install the linter, execute the rule set, and fail the pipeline if new violations exceed policy.
A PostgreSQL extension can expose functions for listing rules, enabling or disabling checks, explaining a rule, and returning current violations. Because it runs close to the catalog, checks can join metadata and statistics without exporting the whole schema to an external scanner.
CREATE EXTENSION IF NOT EXISTS pglinter;
-- Inspect available rules and their current settings.
SELECT pglinter.show_rules();
-- Find all current violations.
SELECT *
FROM pglinter.get_violations()
ORDER BY severity, rule_code;
-- Focus on one rule during a migration review.
SELECT *
FROM pglinter.get_violations()
WHERE rule_code = 'B003';Rules are most useful when they explain why a violation matters and how to fix it. For example, a missing index on a foreign key can slow joins and cause lock contention during parent-table modifications; the remediation is usually an index on the referencing column or composite key.
-- Example remediation for a foreign key lookup path.
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);
-- Composite foreign keys need a matching composite index.
CREATE INDEX idx_order_items_order_product
ON order_items (order_id, product_id);CI systems need machine-readable output. SARIF is a common format because code-scanning tools can display findings inline with pull requests and maintain baselines across runs.
Run every migration branch against a fresh PostgreSQL instance, execute the linter, and block changes that introduce high-severity schema violations. This catches issues before they become production repair work.
For older databases, start with report-only mode. Track redundant indexes, unused tables, privilege problems, and missing constraints over time, then ratchet thresholds as teams fix categories of debt.
Cluster-level rules can identify configuration hazards such as insecure authentication patterns or memory settings that do not match available RAM. These checks complement application migration review.
A hard zero-violation policy is unrealistic for many databases. Store an accepted baseline and fail only when a branch adds new violations or worsens a threshold.
#!/usr/bin/env bash
set -euo pipefail
createdb app_lint
psql app_lint -f schema.sql
psql app_lint -f migrations/next.sql
psql app_lint -c "CREATE EXTENSION IF NOT EXISTS pglinter"
psql app_lint -Atc "SELECT rule_code, object_name, severity FROM pglinter.get_violations()" > current-findings.tsv
diff -u accepted-baseline.tsv current-findings.tsvUse warning rules for cleanup candidates such as unused indexes, and blocking rules for correctness or security hazards such as missing primary keys on core tables or broad public privileges.
Rule enablement, severity thresholds, and suppressions are policy. Keep them under source control, require review, and include a reason when a team suppresses a finding.