Quick summary
Summarize this blog with AI
A targeted DELETE needs a precise target and recovery boundary
DELETE removes rows, and a correct predicate is only part of using it safely. You also need to know which rows were approved, what concurrent changes could occur, which foreign keys or triggers apply, and whether the transaction is still open. This PostgreSQL 16 guide uses temporary fixtures with explicit expected IDs. Run the examples in a disposable learning database; each script ends with ROLLBACK.
Describe the target population in plain language
Our example asks for inactive employees whose termination dates fall during the preceding one-year period: October 17, 2025 inclusive through October 17, 2026 exclusive. That is different from “terminated more than a year ago.” The first rule uses a lower and an upper bound; the second would select dates before the lower bound. A future termination date and a NULL date do not qualify for this example.
The WHERE clause must match the approved scope
Without a WHERE clause, DELETE targets every row in its table, subject to constraints and other database behavior. A broad status predicate can still target far more rows than intended. Before modifying data, inspect the exact primary keys and relevant values, compare the count with expectations, and abort when the reviewed population has changed. A LIMIT alone does not make an incorrect predicate correct.
Preview, delete, inspect, and undo in one fixture
BEGIN;
CREATE TEMP TABLE delete_demo (
id integer PRIMARY KEY,status text,termination_date date
) ON COMMIT DROP;
INSERT INTO delete_demo VALUES
(1,'inactive','2025-01-01'),(2,'inactive','2026-04-01'),
(3,'active',NULL),(4,'inactive','2026-11-01'),
(5,'inactive','2025-10-17');
CREATE TEMP TABLE delete_candidates ON COMMIT DROP AS
SELECT id FROM pg_temp.delete_demo
WHERE status='inactive' AND termination_date>=DATE '2025-10-17'
AND termination_date<DATE '2026-10-17';
SELECT id AS proposed_delete FROM pg_temp.delete_candidates ORDER BY id;
DO $$ BEGIN
IF (SELECT ARRAY_AGG(id ORDER BY id) FROM pg_temp.delete_candidates)
IS DISTINCT FROM ARRAY[2,5] THEN
RAISE EXCEPTION 'Candidate IDs do not match the reviewed fixture';
END IF;
END $$;
SAVEPOINT before_delete;
WITH deleted AS (
DELETE FROM pg_temp.delete_demo d USING pg_temp.delete_candidates c
WHERE d.id=c.id RETURNING d.id
)
SELECT id AS deleted_id FROM deleted ORDER BY id;
SELECT COUNT(*) AS rows_after_delete FROM pg_temp.delete_demo;
ROLLBACK TO SAVEPOINT before_delete;
SELECT COUNT(*) AS rows_after_undo FROM pg_temp.delete_demo;
ROLLBACK;proposed_delete: 2, 5
deleted_id: 2, 5
rows_after_delete: 3
rows_after_undo: 5ID 1 is too old for the stated period, ID 4 is in the future, and ID 3 is active with no termination date. ID 5 is exactly on the included start boundary. The assertion compares the exact candidate IDs rather than merely accepting any two rows. The temporary candidate table freezes this fixture’s reviewed selection; no other session can write these temporary rows.
Undo happens before commit
ROLLBACK TO SAVEPOINT reverses the deletion while keeping the earlier fixture setup. The final ROLLBACK removes the entire transaction’s work. After a successful COMMIT, a later ROLLBACK cannot undo that committed deletion. Recovery then requires an appropriate restore or compensating operation, supported by retained evidence. Do not describe “commit, then roll back if needed” as a recovery plan. See PostgreSQL’s transaction tutorial.
Review real-table dependencies and concurrent changes
For a real cleanup, a preview and later delete can observe different data. Use an approved maintenance window or a deliberately designed locking and version-check strategy. A stored ID list preserves identities, but does not prove their eligibility remained unchanged. Abort on an unexpected row version, candidate count, or dependency; do not broaden the predicate to make a failing operation succeed. Keep access permissions limited to the authorized data.
Make recovery evidence usable
Confirm the relevant backup or point-in-time recovery process and its restore procedure before a consequential cleanup. A backup that has never been checked is weaker evidence than a demonstrated restore. Capture the necessary reviewed row data and job identity under the system’s retention and access rules. DELETE RETURNING can provide affected rows to the application, but returning a row does not itself persist an audit log.
Account for triggers and auditing
PostgreSQL DELETE can invoke delete triggers and foreign-key actions. Their effects may change the work performed, reject a deletion, or alter returned rows. Inspect the actual schema and trigger definitions. A cleanup’s audit record should identify the operation, approved scope, execution result, and transaction outcome; do not log a proposed deletion as committed before the commit succeeds. Consult the DELETE reference for USING and RETURNING semantics.
Choose a deterministic batch
PostgreSQL DELETE has no direct LIMIT clause. One approach selects a bounded set of primary keys, then deletes those keys in the same statement. This separate fixture deletes only the oldest eligible row, ID 5, before rolling back:
BEGIN;
CREATE TEMP TABLE batch_demo(id integer PRIMARY KEY,status text,
termination_date date) ON COMMIT DROP;
INSERT INTO batch_demo VALUES
(1,'inactive','2025-01-01'),(2,'inactive','2026-04-01'),
(3,'active',NULL),(4,'inactive','2026-11-01'),
(5,'inactive','2025-10-17');
WITH candidates AS (
SELECT id FROM pg_temp.batch_demo
WHERE status='inactive' AND termination_date>=DATE '2025-10-17'
AND termination_date<DATE '2026-10-17'
ORDER BY termination_date,id LIMIT 1 FOR UPDATE
), deleted AS (
DELETE FROM pg_temp.batch_demo d USING candidates c
WHERE d.id=c.id RETURNING d.id
)
SELECT id AS deleted_id FROM deleted ORDER BY id;
ROLLBACK;deleted_id
5A production batch needs an explicit stop condition, stable cutoff, concurrency strategy, and confirmation of affected rows. Committing each batch can release locks sooner, but sacrifices whole-job atomicity. Running all batches in one transaction retains the overall transaction’s locks and recovery scope. Batching does not magically eliminate logging or all blocking. SKIP LOCKED, if deliberately used for competing workers, can omit currently locked rows and therefore needs retry/completeness handling.
Measure the plan without blindly disabling indexes
An index matching the selective predicate or join can reduce lookup work, while every maintained index still has deletion costs. Large deletions also create maintenance and replication considerations. Inspect the actual plan and workload; do not disable or rebuild every index as a generic recipe. EXPLAIN ANALYZE executes its statement, so using it on a real DELETE performs the deletion unless enclosed in an appropriate rollback workflow—and external side effects may still require care.
A foreign key is a deletion policy, not an obstacle to remove
BEGIN;
CREATE TEMP TABLE parent_demo(id integer PRIMARY KEY) ON COMMIT DROP;
CREATE TEMP TABLE child_demo(id integer PRIMARY KEY,
parent_id integer REFERENCES parent_demo(id) ON DELETE RESTRICT
) ON COMMIT DROP;
INSERT INTO parent_demo VALUES (1);
INSERT INTO child_demo VALUES (10,1);
DO $$ BEGIN
BEGIN
DELETE FROM pg_temp.parent_demo WHERE id=1;
RAISE EXCEPTION 'Expected the foreign key to reject deletion';
EXCEPTION WHEN foreign_key_violation THEN
RAISE NOTICE 'Expected: child row prevents parent deletion';
END;
END $$;
SELECT (SELECT COUNT(*) FROM pg_temp.parent_demo) AS parents,
(SELECT COUNT(*) FROM pg_temp.child_demo) AS children;
ROLLBACK;NOTICE: Expected: child row prevents parent deletion
parents | children
1 | 1The inner block catches only the expected foreign-key violation; an unexpected successful deletion causes a different exception and fails the script. ON DELETE CASCADE would intentionally delete dependent rows instead, which can greatly expand the operation’s scope. Do not change a relationship to CASCADE just to make an ad hoc cleanup pass. Review its business meaning and all downstream effects. See PostgreSQL foreign-key actions.
Join on identities you have reviewed
The USING clause in our examples joins candidates to target rows on the primary key. A join against a non-unique descriptive field can expand the eligible target set. When related data is used only as a condition, an EXISTS predicate often expresses that intent clearly. Independently count and inspect the resulting target IDs before applying a real change.
Explain the operation and its limits
A clear interview answer names the population and cutoff, shows candidate IDs, explains transaction boundaries, and discusses referential actions and concurrency. For this fixture, the proof is exact: IDs 2 and 5 are removed, three rows remain, and rolling back to the savepoint restores five. That proves the teaching example; it does not substitute for inspecting a different application schema.
Delete only the intended rows
Use a predicate that matches the stated time window, verify the candidate identities, and retain a real recovery path. Keep commit and rollback semantics explicit. The safest useful explanation is a complete rehearsal with observable before-and-after results.
FAQ
Can I roll back after committing? No; rollback operates on the current uncommitted transaction. Should I use TRUNCATE for a subset? No; it has no row predicate and its locking, trigger, and foreign-key behavior differs. Does a preview lock the candidates? An ordinary SELECT does not provide the required protection by itself.
