Postgres Rules

This section contains 2 rule(s) related to postgres.

Rule Summary

Code	Name	Aliases	Fix Compatible
PG01	postgres.excessive_locks	-	❌
PG02	postgres.not_valid_foreign_key	-	❌

---

PG01: postgres.excessive_locks

Avoid excessive locks in PostgreSQL DDL statements.

Property	Value
Name	postgres.excessive_locks
Groups	all, postgres
Auto-fixable	No ❌

Several PostgreSQL DDL operations acquire locks that block reads or writes for the duration of the operation. On large tables this can cause significant downtime. This rule flags statements that should use safer alternatives:

• CREATE INDEX → use CONCURRENTLY (CREATE UNIQUE INDEX excluded)
• DROP INDEX → use CONCURRENTLY
• REINDEX → use CONCURRENTLY
• REFRESH MATERIALIZED VIEW → use CONCURRENTLY

This rule only applies to the postgres dialect and is disabled by default. Enable it with the force_enable = True flag.

Anti-pattern

DDL that acquires excessive locks.

CREATE INDEX idx_foo ON bar (tenant_id);

DROP INDEX idx_foo;

REINDEX INDEX idx_foo;

REFRESH MATERIALIZED VIEW my_view;

Best practice

Use non-blocking alternatives.

CREATE INDEX CONCURRENTLY idx_foo ON bar (tenant_id);

DROP INDEX CONCURRENTLY idx_foo;

REINDEX INDEX CONCURRENTLY idx_foo;

REFRESH MATERIALIZED VIEW CONCURRENTLY my_view;

Configuration

Option	Description
force_enable	Run this rule even for dialects where this rule is disabled by default. Must be one of [True, False].

---

PG02: postgres.not_valid_foreign_key

Create PostgreSQL foreign keys as NOT VALID before validating.

Property	Value
Name	postgres.not_valid_foreign_key
Groups	all, postgres
Auto-fixable	No ❌

Adding a foreign key constraint normally validates existing rows as part of the ALTER TABLE statement. On large tables, this can hold locks for longer than necessary. Creating the constraint as NOT VALID defers that scan so it can be run later with VALIDATE CONSTRAINT.

This rule only applies to the postgres dialect and is disabled by default. Enable it with the force_enable = True flag.

Anti-pattern

A foreign key constraint that validates existing rows during creation.

ALTER TABLE foo ADD CONSTRAINT fk_bar
    FOREIGN KEY (bar_id) REFERENCES bar (id);

Best practice

Create the foreign key as NOT VALID, then validate it in a separate statement.

ALTER TABLE foo ADD CONSTRAINT fk_bar
    FOREIGN KEY (bar_id) REFERENCES bar (id) NOT VALID;

ALTER TABLE foo VALIDATE CONSTRAINT fk_bar;

Configuration

Option	Description
force_enable	Run this rule even for dialects where this rule is disabled by default. Must be one of [True, False].

---