Postgres Rules

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

Rule Summary

Code	Name	Aliases	Fix Compatible
PG01	postgres.excessive_locks	-	❌

---

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
• ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY → use NOT VALID

This rule only applies to the postgres dialect.

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;

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

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;

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

---