DELETE

DELETE does not erase bytes in place; it walks the rows that match your WHERE clause and, one row at a time, writes each row's pre-image into the transaction log (so the change is durable and reversible) before marking that row's slot as dead — which is why it is transactional, fires triggers, and can be rolled back, but is also slow on millions of rows.

Syntax and the components that matter

DELETE FROM employees
WHERE id = 1;

• FROM employees — the target table. Exactly one base table (some engines allow multi-table delete via joins, but the core form targets one).
• WHERE condition — optional but load-bearing. It is the only thing standing between deleting one row and deleting the whole table. Omit it and every row goes.

The engine evaluates WHERE against the current state of the table, collects the matching rows, then deletes them as a set inside the surrounding transaction. Nothing is visible to other sessions until you COMMIT.

A worked trace with real values

Start with this employees table (an id primary key plus a row-version/transaction id that the engine keeps internally — shown here to make the mechanism concrete):

Now run, inside a transaction:

BEGIN;
DELETE FROM employees
WHERE id = 1;

1. Scan / index lookup. id = 1 on a primary key is an index probe, not a table scan. It locates the row for Asha.
2. Lock the row. A row-level exclusive lock is taken so no concurrent transaction can update or delete it underneath us.
3. Log the pre-image. Before touching the page, the old row (1, Asha, Sales, 52000) is written to the write-ahead/undo log. This is what makes ROLLBACK possible.
4. Mark it dead, do NOT reclaim space. Postgres stamps the row's xmax with this transaction's id (the row is now "deleted by tx 110" but its bytes still sit on the page). InnoDB marks it delete-flagged and links the old version into the undo log. The page does not shrink yet.
5. Fire row triggers. Any BEFORE/AFTER DELETE trigger and ON DELETE referential action (e.g. CASCADE) runs here — per row.
6. COMMIT vs ROLLBACK. COMMIT makes the dead mark permanent; the slot becomes reclaimable garbage. ROLLBACK simply ignores the xmax stamp and the row is alive again — no data was ever physically removed.

After COMMIT the logical table is:

The space Asha occupied is freed only later — by VACUUM (Postgres) or purge of old undo (InnoDB). That is why a big DELETE can leave a table file just as large on disk as before.

DELETE vs TRUNCATE vs DROP — the mechanism, not the marketing

Rule of thumb: need a filter, triggers, or safe rollback → DELETE. Wiping every row of a big table and you own the identity reset → TRUNCATE. Want the table itself gone → DROP.

Pitfalls

The WHERE-less DELETE footgun

-- intends to delete one employee, but the WHERE was lost
DELETE FROM employees;   -- deletes EVERY row

Why the naive version is wrong: WHERE is optional syntax, so the parser happily accepts a clause-less DELETE and treats it as "match all rows." There is no warning. The most common real-world cause is a highlighted query in a SQL client where you select the DELETE FROM employees line but not the WHERE id = 1 line below it, then hit Run. The fix is procedural, not syntactic:

• Write and run the SELECT form first: SELECT * FROM employees WHERE id = 1; — eyeball the row count, then change SELECT * to DELETE.
• Wrap destructive work in an explicit transaction: BEGIN; DELETE ...; check rows affected, then COMMIT; or ROLLBACK;.
• Run with a safety net like MySQL's --safe-updates, which rejects a DELETE that has no WHERE on a key.

Foreign keys block or cascade

Deleting a parent row that is referenced by a child fails with a foreign-key violation unless the constraint is ON DELETE CASCADE — in which case it silently deletes the children too. Know which one you have before you run it in production.

The big-DELETE that bloats and locks

A single DELETE FROM orders WHERE created_at < '2020-01-01' over tens of millions of rows holds locks and undo for the whole statement, can blow up replication lag, and leaves the table physically as large as before (dead tuples await VACUUM). Delete in bounded batches (e.g. ... LIMIT 10000 in a loop, committing each batch), or if you are clearing the whole table use TRUNCATE.

DELETE does not shrink the file

Engineers are surprised that du on the data file is unchanged after a huge delete. The rows are logically gone but the pages are still allocated; you need VACUUM FULL / OPTIMIZE TABLE (which rewrites the table) to actually return disk to the OS.

Takeaways

• DELETE is a row-by-row, fully logged DML operation: it stamps rows dead and keeps their pre-image, which is exactly what makes it rollback-able, trigger-firing, and slow at scale.
• The WHERE clause is optional to the parser but not to your data — a missing one deletes the entire table with no warning. Preview with SELECT and wrap in a transaction.
• Reach for TRUNCATE only to wipe an entire table fast (it deallocates pages, resets identity, skips triggers, and isn't rollback-able on MySQL); use DELETE whenever you need a filter or safety.
• A committed DELETE frees space logically, not physically — disk is reclaimed later by VACUUM/purge or a table rewrite.

Sources: ISO/IEC 9075 SQL standard (Data Manipulation — <delete statement>); PostgreSQL documentation (DELETE, TRUNCATE, and "Routine Vacuuming" on dead tuples and MVCC xmin/xmax); MySQL 8.0 Reference Manual (DELETE, TRUNCATE TABLE, InnoDB undo logs and purge, --safe-updates); Microsoft SQL Server docs (DELETE vs TRUNCATE TABLE, minimal logging). Worked trace, DELETE/TRUNCATE/DROP mechanism contrast, and SVG re-authored/deepened for this guide; image-placeholder before/after tables replaced with inline data.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — DELETE

Why this concept exists (judgment layer)

DELETE is logged, row-by-row, trigger-firing DML — the safe selective remove. WHERE-less DELETE and giant single-statement deletes are classic outage patterns.

Mental model (install this intuition)

Locate rows → lock → log pre-image → mark dead (MVCC xmax / delete flag) → triggers/FK actions → commit. Space reclaimed later (VACUUM/purge). WHERE optional to parser, mandatory to survival.

Worked example with numbers or traced steps

DELETE FROM employees WHERE id=1:
  index probe → row lock → WAL/undo pre-image → xmax set → AFTER DELETE
ROLLBACK undoes xmax; COMMIT leaves dead tuple until VACUUM
Footgun: DELETE FROM employees;  -- all rows
10M-row DELETE: batch LIMIT 10k + commit; else replication lag + bloat

When NOT to use / named alternative

Wipe entire large table with no need for triggers → TRUNCATE. Remove table → DROP. Soft-delete product requirement → UPDATE deleted_at, not hard DELETE.

Failure mode & ops fingerprint

Fingerprint: client runs only DELETE FROM line without WHERE; table empty; disk usage unchanged after massive delete (need VACUUM FULL/OPTIMIZE); FK RESTRICT errors; CASCADE silently deletes children.

Hostile-panel drills (defend the decision)

Q1. Why doesn't DELETE free disk immediately?
Model answer: Rows marked dead; pages stay allocated until VACUUM/purge or table rewrite.

Q2. Safe procedure before production DELETE?
Model answer: SELECT same WHERE first; BEGIN; DELETE; check rowcount; COMMIT or ROLLBACK; consider --safe-updates.

Q3. DELETE vs TRUNCATE for 'empty staging table' on Postgres?
Model answer: TRUNCATE faster, resets identity, skips triggers, still transactional on PG — prefer TRUNCATE if those match intent.