DROP vs DISABLE in Postgres

14 Aug 2026

Both remove a trigger from the path of a write. They are not interchangeable, and the difference that usually matters is not reversibility — it is the lock.

On Postgres 18:

BEGIN;
ALTER TABLE t DISABLE TRIGGER trg;
SELECT mode FROM pg_locks WHERE relation = 't'::regclass;
-- ShareRowExclusiveLock

BEGIN;
DROP TRIGGER trg ON t;
-- AccessExclusiveLock

DISABLE takes SHARE ROW EXCLUSIVE. It blocks writes and other schema changes, but readers keep going. DROP takes ACCESS EXCLUSIVE, which blocks everything, including a plain SELECT.

That is the opposite of the intuition that the destructive operation is the cheaper one, and on a busy table it is the whole decision.

The lock queue is the real hazard

Neither statement is slow. What hurts is that Postgres queues lock requests in order. If a long SELECT is running and you ask for ACCESS EXCLUSIVE, your DROP waits — and every query that arrives after it waits behind you, even though those queries would not have conflicted with the SELECT.

One DROP TRIGGER behind one slow report is enough to stall a table completely. Always:

SET lock_timeout = '3s';

Failing fast and retrying is nearly always better than joining the back of a queue you cannot see.

Disabling for a bulk load

The usual reason to disable a trigger is a large load where you know the trigger's work is either unnecessary or will be redone afterwards. ALTER TABLE ... DISABLE TRIGGER works, but it is DDL: it takes a lock, it changes the catalog, and it is visible to every other session.

For a load in one session, this is better:

SET session_replication_role = replica;
-- load
SET session_replication_role = origin;

No lock, no DDL, no effect on anyone else. It suppresses triggers whose firing state is O (origin) — the default — while leaving A (always) triggers running. That is the mechanism logical replication uses on the subscriber side, which is exactly the "apply rows without re-running local side effects" problem a bulk load has.

The state lives in pg_trigger.tgenabled:

SELECT tgname, tgenabled FROM pg_trigger
 WHERE tgrelid = 't'::regclass AND NOT tgisinternal;

O origin (default), D disabled, R replica only, A always.

Foreign keys are triggers

DISABLE TRIGGER ALL includes the internal triggers that enforce foreign keys. Postgres will let you do it, load rows that violate a constraint, and re-enable — and it does not revalidate on the way back. You are left with a constraint the catalog believes is satisfied and data that does not satisfy it. Nothing will tell you until something else breaks.

If you need that, ALTER TABLE ... DROP CONSTRAINT and re-add with NOT VALID, then VALIDATE CONSTRAINT later. VALIDATE takes only SHARE UPDATE EXCLUSIVE and scans in the background, which is the entire point of splitting it in two.

Indexes have no disable

There is no ALTER INDEX ... DISABLE. The two things people reach for are not equivalent to it:

• SET enable_indexscan = off is a planner setting for your session. The index is still there and still maintained on every write. You have paid the whole cost and lost the benefit.
• Editing pg_index.indisvalid by hand does stop the planner using it, and will eventually cost you a table.

If an index is not earning its write amplification, DROP INDEX CONCURRENTLY and recreate it the same way if you were wrong. CONCURRENTLY avoids the ACCESS EXCLUSIVE lock, cannot run inside a transaction block, and can leave an invalid index behind if it fails — check indisvalid afterwards rather than assuming.

The rule

Disable when you want it back and you own the maintenance window. Drop when it is genuinely gone. And when the table is live, remember that the reversible option is also the one that lets readers through.