Deleting one tenant in PostgreSQL: cascade, batch or DROP

A tenant cancels and asks for their data to be removed. In a multi-tenant SaaS where every tenant lives in the same tables, that is one DELETE — and four questions nobody asks until the day it runs: how long does it take, does it hold up the other tenants, does the database get smaller, and is the data actually gone?

We measured all four on PostgreSQL 17.10, in a throwaway container with default settings: 100 tenants, 311,205 invoices and 1,244,820 invoice lines, 235 MB. The largest tenant had 60,000 invoices and 240,000 lines; a middle one 1,200 invoices. Both foreign keys (invoices → tenants, invoice_lines → invoices) are on delete cascade, and both have an index on the referencing column.

How long does deleting one tenant take?

Each figure is two runs from the same starting database, with a checkpoint just before each run (WAL includes the full-page images that follow a checkpoint, so without one the WAL figure is lower). Cascading or deleting table by table makes no difference. What the explicit route does show is where the time goes: removing the 240,000 invoice lines took 140 ms, removing the 60,000 invoices after them took 1,185–1,202 ms. The cost is not in the rows but in the foreign key: PostgreSQL fires a referential-integrity trigger for every deleted invoice, which goes looking for lines that point to it — even when you have just deleted them all yourself. EXPLAIN ANALYZE on that step showed it: Trigger for constraint invoice_lines_invoice_id_fkey: time=1157.535 calls=60000, out of 1,214 ms in total.

That trigger is also why the index on the referencing column matters. Without it every one of those checks is a scan of the child table; in our multi-tenant PostgreSQL guide that made deleting a tenant of 2,000 invoices take 21.8 seconds instead of 0.12.

Does deleting a tenant block the other tenants?

Not directly — not for reads, and not for writes of other tenants. We started the delete of the largest tenant and kept its transaction open. Meanwhile, in a second session:

• inserting an invoice for another tenant took 10 ms;
• counting the deleted tenant’s invoices still returned 60,000 — until commit, everyone else sees the old rows;
• inserting an invoice for the tenant being deleted waited 2.8 seconds, until the delete committed, and then failed with violates foreign key constraint.

A delete only locks the rows it removes, plus the tenant row. So what to plan for is everything that still writes for the departing tenant — their application, but also background jobs: switch those off before you start. One indirect cost remains: as long as the transaction is open, vacuum cannot clean up dead rows anywhere in the database, for any tenant. Another reason to keep it short.

What batch size should you use to delete in batches?

Batching keeps each transaction short, which reduces replication lag and the time anything waits on those rows. We deleted the largest tenant in batches — select a batch of invoice ids, delete their lines and the invoices, commit — at four batch sizes:

Batches of 3,000 and 5,000 took two to three times as long in total as a single delete. EXPLAIN ANALYZE on the first batch showed why: at 1,000 ids the planner used the indexes (index scans, four lines per invoice); at 5,000 it chose a hash join over a sequential scan of both tables — all 1,244,820 lines and all 311,205 invoices. Every batch cost about the same (233–264 ms at 5,000; 27–31 ms at 1,000), so a bigger batch did not make up for it; it just read far more than it deleted. And the curve is not smooth — 3,000 was slower in total than 5,000.

So pick the batch size with EXPLAIN ANALYZE on one batch, not by feel, and check it again when the table grows: the point where the plan flips depends on your data and on how you write the batch query, not on a rule of thumb.

Does the database get smaller after deleting a tenant?

No. After deleting the largest tenant (19% of all rows) the database was still 235 MB, and after VACUUM as well. The PostgreSQL documentation says so plainly: vacuum “will not return the space to the operating system”, except for completely empty pages at the end of a table.

The space is not lost. A new tenant of exactly the same size went into the freed space of the tables without growing them (167 MB before, 167 MB after). The indexes did grow, by 13 MB (60 to 73 MB): the new rows’ keys sort at the end of the indexes, not where the old tenant’s keys were.

VACUUM FULL does give the space back — 235 MB became 191 MB — but it rewrites the table under an exclusive lock: 1,329 ms for the lines and 311 ms for the invoices, during which nobody can read those tables. For one departing tenant that is rarely worth it.

Is the tenant’s data really gone after a DELETE?

Not from the data files. We gave every invoice of the middle tenant a recognisable note, deleted the tenant, ran VACUUM and a checkpoint. A query found 0 rows. The table’s data file still contained the text 1,219 times, for 1,200 invoices: vacuum marks the space as free, but does not erase the old bytes. After VACUUM FULL the new data file contained it 0 times; the old file is removed, which is not the same as its blocks being wiped from the disk.

Other places keep the data after the delete too, among them:

• The write-ahead log: the text was still in the most recent WAL segments. With WAL archiving it lives on for as long as you keep the archive.
• Backups: every base backup taken before the delete contains the tenant until that backup expires.
• Your own copies: an audit log that stores full rows — the default in the triggers in our audit log article — keeps the tenant’s data, as do exports, search indexes and caches. So can the server log: a constraint error prints the key values in its DETAIL line.

So “deleted” means: no longer reachable through the database. How long the other copies may exist is a question for your retention periods, and the honest answer to a tenant names them.

Is dropping the tenant’s partition faster?

Much faster, with two pitfalls. We built the same data with both tables partitioned by list on tenant_id — the largest tenant in its own partition, everyone else in a default partition, primary keys (tenant_id, id) and a foreign key (tenant_id, invoice_id) from lines to invoices.

Removing the largest tenant — drop its lines partition, detach and drop its invoices partition, delete the tenant row — took 2–3 ms per statement and 7.5 ms for the commit, wrote 206 kB of WAL instead of 48 MB, and gave the disk space back straight away (244 MB to 198 MB).

Pitfall 1: the order. The obvious sequence — detach the lines partition, then the invoices partition — fails:

ERROR:  removing partition "p_invoices_t1" violates foreign key constraint "p_lines_t1_tenant_id_invoice_id_fkey"
DETAIL:  Key (tenant_id, invoice_id)=(1, 1) is still referenced from table "p_lines_t1".

A detached partition keeps its foreign key. Drop the referencing partition first (or detach and drop it), then detach the referenced one.

Pitfall 2: the lock. Both detaching and dropping a partition take an ACCESS EXCLUSIVE lock on the whole partitioned table and on its default partition — where all the other tenants are — not just on the tenant’s part; detaching also locks the table its foreign key points to (tenants), as pg_locks showed. While the drop of the lines partition sat in an open transaction, a query for another tenant’s lines waited 2.0 seconds. Worse, the drop has to wait for every running query on that table, and everyone else queues behind it: with a three-second report running, the drop waited 2.5 seconds and a simple query for another tenant 2.0 seconds. With set lock_timeout = '200ms' the drop gave up after 200 ms, and the other tenant’s query took 11 ms — then you retry.

DETACH PARTITION … CONCURRENTLY takes a weaker lock, but the documentation is clear that it “cannot be run in a transaction block and is not allowed if the partitioned table contains a default partition”. Put big tenants in their own partition and small ones in a default partition, and you cannot use it.

A checklist for deleting one tenant

1. Every foreign key column has an index. Without it, deleting is a scan per parent row.
2. Switch off everything that writes for the tenant first: their application and background jobs. Those are what wait.
3. Batch by plan, not by feel. EXPLAIN ANALYZE one batch; if it reads whole tables to delete a few thousand rows, make the batch smaller or rewrite the query.
4. Do not expect the database to shrink. The space goes to the next tenant; VACUUM FULL only if you need the disk back and can afford the lock.
5. Partition drop: referencing partition first, with a lock_timeout, and in a transaction that does nothing else.
6. Tell the tenant where their data still is: WAL archive, backups, audit log, logs and exports, each with its retention period.

If the tenant wants a copy first, how to export exactly their rows with pg_dump — and why the export must not carry your sequence values — is in exporting one tenant with pg_dump and row-level security.

What we did not measure

Deletes on a replica or with logical replication, TRUNCATE of a partition, schema-per-tenant (DROP SCHEMA … CASCADE), and deletes under row-level security as the application role. Timings are from one container on one machine; the ratios are what carries over, not the milliseconds.

Sources: the PostgreSQL 17 documentation on routine vacuuming and ALTER TABLE (DETACH PARTITION CONCURRENTLY), read on 1 October 2026. All measurements are our own, on PostgreSQL 17.10, on 1 October 2026.