The database cleanup checklist: finding orphaned rows, deleting junk safely, and proving nothing valid went with it

Delete a user in Supabase and your app’s own tables can keep every row that pointed at them when no foreign key ties them: comments, uploads, a half-finished order, all naming an id that no longer exists. A database cleanup checklist finds those orphaned rows, rehearses the delete with a dry run, and removes them so you can undo it.

What an orphaned record is, and the database cleanup checklist

An orphaned record in a database is a child row whose parent row no longer exists, such as a comment whose user was deleted. A database cleanup checklist has 7 lines: back up, find orphans per relationship, classify them, dry-run the delete, delete from a holding table, compare, and add the missing constraint.

Orphan cleanup is one line of the data consistency checklist for SaaS, and this page works through that line from the first query to the last check.

The parent can be anything a row points at. An order line whose order is gone is an orphan; so is an upload whose owner was removed. In SQL terms, orphan records are rows whose reference column holds a value that matches no row in the parent table. In my reading, they exist because no foreign key was there to refuse the delete or to cascade it. The same cleanup covers three other kinds of junk: abandoned rows (carts, drafts and signups nobody finished), duplicates left by a job that retried, and test data that ended up in production.

“Data cleanup” also names a different job: fixing contacts in a CRM, or missing values in a dataset before analysis. That is not this page. Everything below is about rows in a relational database that point at other rows.

This work is deliverable 4.11 of the Production Hardening Sprint, Orphaned-record cleanup: we identify and clean abandoned records and broken relationships with documented safeguards.

What goes wrong without it

Junk rows rarely announce themselves. They show up as a wrong number in a report or an error on one page, long after the delete that caused them.

In my reading, a database full of junk records usually shows all four at once.

Start with the user delete. Supabase’s users live in auth.users, and its docs suggest your own user tables in the public schema reference that table with on delete cascade. As I see it, the tables that store a user’s id with no foreign key to auth.users are where the orphaned rows after deleting a user come from: the auth row goes, and every row that named it stays. Rows left behind after deleting a parent can also hold personal data that a user asked to have erased, and what an erasure request has to reach is covered in handling a GDPR delete request.

Across the third-party apps I audited in June and July 2026, the Data Integrity & Safety pillar averages 51.6 out of 100, scored on 20 of the 21 apps. They are a selected set of audited apps, not a random sample, so the figure is no rate for AI-built apps in general.

Before any row is classed as junk, settle what the law or a contract says you must keep. That is a data retention policy for a small SaaS, and it comes first.

How to do it in Postgres: find, classify, rehearse, compare, measure

The five steps below run in the order the work happens, and every write runs first on a restored copy, never first on production. The Postgres behavior described here is taken from the PostgreSQL manual, and each query is reasoned against the functions it uses; none was run to write this page.

How to find rows with broken references

Rows with broken references are found with an anti-join per relationship: left join the child table to its parent and keep the rows where the parent id is set and no parent matches. Leave out the is-set condition and the list also takes rows that never had a parent. Without foreign keys, list the id columns first.

The reason is in PostgreSQL’s join documentation: for each row of the left table that matches no row of the right, a left outer join adds a joined row with null values in the right table’s columns. A child whose reference is itself null matches no parent either, so it comes back with the same nulls. Those rows are a nullability question, not orphans, and c.user_id IS NOT NULL keeps them out. The same check belongs in the NOT EXISTS form, because a null reference never equals a parent id and would pass that test too. Both forms return the same rows. This is also the general answer to how to find records in one table that are not present in another table: the child table is the first, the parent the second.

-- 1. candidate relationships: every public column ending in _id
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND column_name ~ '_id$';
-- 2. orphans in one relationship: comments whose author is gone
SELECT c.* FROM comments c LEFT JOIN users u ON u.id = c.user_id
WHERE c.user_id IS NOT NULL AND u.id IS NULL;
-- 3. the same rows with NOT EXISTS
SELECT c.* FROM comments c WHERE c.user_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = c.user_id);

Run the orphan query once per relationship. When no foreign keys declare the relationships, the first query lists the columns that probably hold one; pair each with its likely parent. Run it as the role that owns your tables, since information_schema.columns shows “Only those columns … that the current user has access to”. A reference column named something else, such as author or owner, will not match the pattern, so read the Prisma or Drizzle schema file as well: it may declare relations that the database itself does not enforce. Write down the count for each relationship with the date.

On Supabase, start with every table that holds a user id. Supabase’s guide to managing user data has app tables reference auth.users, so for a column that holds a user’s id, the parent in the queries above is auth.users, or a profiles table of your own that references it.

Say you delete a batch of test users, list “comments whose author is gone” with a left join to the users table and a filter on the users table’s id being null, and copy the list into a holding table. The list also holds the comments the app posts itself, whose author column was always empty, because a left join returns a row that matches nothing with nulls in the users columns, and a null author matches no user. Reading the holding table by group before the delete is where those extra rows show, and the finder needs “the author id is set” as well. The lesson I take from it: an orphan is a row whose parent id is set and points at nothing, and a row with no parent id at all is a different question that the finder has to leave out.

Safely delete unused database rows: classify before you touch anything

Unused rows fall into 3 groups before any delete, by my working rule: orphans with no value alone are deleted, financial and legally retained rows are kept with the reference nulled, and content other users can see is the owner’s call. Unused is a written rule, never a feeling.

My working rule is that “unused” gets written down before anyone runs a query. Good rules name something you can check: no login since a date you pick, onboarding never completed, or an email domain that marks a test account. The rule you write here becomes the scheduled job later, so write it as a query condition.

Rows that someone may need back should not be hard-deleted at all; that is what soft delete is for. How each class of data should be deleted from now on (hard, soft or retained) is its own design question, answered in the article on data-loss bugs in AI-built apps, in its section on choosing deletion behavior by data class. This step only sorts rows that already exist.

A dry run before a data cleanup

A dry run before a data cleanup takes 5 steps: work on a restored copy, copy the target rows into a holding table, count every table that references them, run the delete inside a transaction and roll it back, then run for real only if nothing moved that you did not list. The holding tables are the undo.

1. 01 Restore last night's backup into a scratch database and do everything below there first. On Supabase, daily backups cover Pro, Team and Enterprise projects; restore one into a new project, never over the live one, and note that Restore to a New Project is only open to paid plans with physical backups enabled. A Free project takes a dump with the Supabase CLI db dump command, which Supabase recommends for free projects, and restores it into a local or second database.
2. 02 Copy the rows you mean to delete into a holding table, such as cleanup_comments_yyyymmdd. Copy the child rows a cascade will take with them into their own holding tables. Count each one.
3. 03 Count every table that references the target table, and write the numbers down with the date.
4. 04 Inside a transaction, run the delete, read the count it reports, re-count those child tables, then ROLLBACK. A delete count that differs from the holding table stops the run. So does a child count that moved, unless it fell by exactly the rows you held for it in step 2.
5. 05 Only when both match, run it for real on production, in batches, from the holding table.

The finder query from the last section becomes the holding table, and the rehearsal ends in ROLLBACK:

-- step 2: hold the target rows and the children a cascade would take
CREATE TABLE cleanup_comments_yyyymmdd AS SELECT c.* FROM comments c
  LEFT JOIN users u ON u.id = c.user_id WHERE c.user_id IS NOT NULL AND u.id IS NULL;
CREATE TABLE cleanup_reactions_yyyymmdd AS SELECT r.* FROM reactions r
  WHERE r.comment_id IN (SELECT id FROM cleanup_comments_yyyymmdd);
-- step 4: rehearse, count, undo
BEGIN;
DELETE FROM comments WHERE id IN (SELECT id FROM cleanup_comments_yyyymmdd);
SELECT count(*) FROM reactions; -- and every other table that references comments
ROLLBACK;

The query that lists every foreign key and its delete action is in the data-loss article’s section on finding every delete that can reach your data; run it, together with the candidate list from the find step, to build the step 3 list rather than guessing. The transaction itself is the subject of how to wrap multiple writes in a transaction.

PostgreSQL’s DELETE reference says the reported count “is the number of rows deleted”, and adds that it “may be less than the number of rows that matched the condition when deletes were suppressed by a BEFORE DELETE trigger”. Whether rows removed by a cascade are in that count is not stated in PostgreSQL’s docs, so my working rule is to trust the child-table counts, not the delete count, to show what a cascade did.

For the real run, the batching that keeps the app up is in deleting a lot of rows without taking the app down. Keep the holding tables afterward: my working rule is about a month, then drop them. If a real run goes wrong anyway, recovery works the way it does after an accident, starting with how to undo a DELETE in Postgres.

Before you delete: comparing two copies of a table with a checksum in SQL

A checksum in SQL condenses a table to one value so two copies can be compared. In Postgres, hash each row’s text with md5 and aggregate the hashes in a fixed order. A matching count and checksum before and after is strong evidence that a table was untouched.

For every table the cleanup should not change, take a row count and a checksum before the run and again after it, and the pairs must match. On the scratch copy nothing else writes, so compare whole tables. On the live database the app keeps writing, so compare only rows created before the run started; even then a user editing an old row changes the value, so in my reading a live mismatch is a prompt to look and the scratch copy is the proof.

-- run before and after the cleanup; both columns must match
SELECT count(*) AS row_count,
       md5(string_agg(md5(t::text), '' ORDER BY md5(t::text))) AS checksum
FROM invoices t;
-- on the live database, add: WHERE t.created_at < (the run's start time)

The ORDER BY inside the aggregate is what makes the result independent of the order rows come back in: PostgreSQL’s aggregate functions page says string_agg’s result depends on input order, which “is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call”. The alias t stands for the whole row, and t::text is its text form. For a table the cleanup did change, run the same query over the rows that should remain, with the delete rule negated in the WHERE clause.

SQL Server has CHECKSUM and CHECKSUM_AGG built in. Microsoft’s CHECKSUM documentation says that if a value changes, “the list checksum will probably change. However, this is not guaranteed”, and recommends it “only if your application can tolerate an occasional missed change”. My reading for any engine: a match is strong evidence, and a mismatch is proof something changed.

A comparison like this catches rows that vanished. An update that wrote the wrong values is a different accident, and what you can still get back after overwriting data starts from there.

Bloat, vacuum, and what is actually using the space

Postgres reports its own size with pg_database_size for the database and pg_total_relation_size for each table, indexes included. Take both numbers before and after a cleanup. VACUUM takes tables, not a schema, so vacuuming one schema goes through vacuumdb and its schema option.

The PostgreSQL query for database size wraps pg_database_size in pg_size_pretty, and a second query ranks the tables:

-- the whole database
SELECT pg_size_pretty(pg_database_size(current_database()));
-- the ten largest tables, indexes and TOAST data included
SELECT c.oid::regclass AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 10;

In PostgreSQL’s size functions, pg_database_size computes “the total disk space used by the database”, and pg_total_relation_size computes the disk space used by a table “including all indexes and TOAST data”. Run the Postgres query for database size and the table list before the cleanup and again after it, and keep both results in the record.

The cleanup is how you reduce Postgres database size at the source, but the figure may barely move right after a large delete. Why that happens, and what VACUUM and VACUUM FULL do about it, is the data-loss article’s FAQ on a disk that stays full; what vacuum does to any later recovery of deleted rows is covered in the recovery article’s FAQ.

To vacuum one schema, you cannot name it in VACUUM: the command takes a list of tables, optionally with columns, and has no schema form. The vacuumdb reference gives the command-line tool a --schema option that will “Clean or analyze all tables in schema only”, and it can be repeated for several schemas.

On a managed plan, the size that matters is the one your provider bills for, which can differ from what these functions report (my reading; check the provider’s usage page). If queries slowed down as the tables grew, how to analyze a Postgres query is the next stop.

How to verify it

A cleanup is verified by 6 checks: the dry-run counts match, every anti-join now returns zero, untouched tables match on count and checksum, valid parents still load with their children, exports run, and the record names the holding table and backup.

1. 01 The dry-run delete count equals the holding-table count, and no child count moved beyond the child rows held for the cascade. Evidence: the numbers, with the date.
2. 02 After the real run, every anti-join from the find step returns zero rows. Evidence: the query output.
3. 03 Every untouched table matches its before values on count and checksum: in full on the scratch copy, and on the live database over rows created before the run started, since the app's own writes would fail a whole-table comparison on a correct run. Evidence: the before and after pairs.
4. 04 About ten valid parent records, picked before the run, still load in the app with all their children, such as a customer with orders or a workspace with members. Evidence: the list of ids checked.
5. 05 The exports and the main reports run without error. Evidence: the run dates.
6. 06 The record names the holding tables and the backup used, with their dates.

In the Production Hardening Sprint, deliverable 4.11 is verified this way: run a dry-run comparison and confirm the cleanup preserves valid linked records.

Then stop new orphans from forming. Add the missing foreign keys with the delete rule you chose for each relationship; which rule fits which table belongs to the database review checklist. Schedule the written rule for abandoned rows as a job, so the next cleanup is small. If list pages slowed down because they look up each row separately, that is the N+1 query problem, a different fix. Files whose rows you deleted can stay behind in storage, and sweeping them belongs to the object storage security checklist. And a long batch run that exhausts connections is a matter of connection pooling in Postgres.

Where the sprint does this

The cleanup and its evidence are recorded in the production readiness report, deliverable 13.1, which is verified by accounting for all 123 IDs, keeping failures visible until resolved and explaining genuine non-applicable items. Where it stops: we implement and document the technical data-handling controls, and legal advice and certification are separate services. Each deliverable and its verify line is listed in the published scope.

Common questions about cleaning up a database

How can I check the size of all tables in a PostgreSQL database?

Query pg_class for ordinary tables and order them by pg_total_relation_size, which counts each table with all its indexes and TOAST data; the table-list query above does this for ten tables, and dropping its LIMIT returns them all. For the table alone use pg_table_size, which excludes indexes, and for the indexes alone pg_indexes_size.

What is it called if you delete a record and the database goes on and deletes other records associated with that record?

It is called a cascading delete, and it happens because the foreign key was declared with ON DELETE CASCADE. Supabase’s own example for a profiles table uses references auth.users on delete cascade. What the option does in full is covered in the data-loss article’s section on ON DELETE CASCADE, and which tables should cascade is a schema decision for the database review checklist.

Can Postgres handle 100 million rows?

Yes. The PostgreSQL manual lists no limit on database size, a relation size limit of 32 TB with the default block size, and rows per table limited by the number of tuples that can fit onto 4,294,967,295 pages, which I read as far above 100 million rows. The same appendix says practical limits, such as performance limitations or available disk space, may apply before the hard limits are reached; I’d expect those to be indexes and maintenance keeping up with the table.

How do you delete all records from a table without deleting the table?

Use TRUNCATE, or DELETE with no WHERE clause; both empty the table and leave it in place. The PostgreSQL manual says TRUNCATE has the same effect as an unqualified DELETE but is faster because it does not scan the table, refuses a table that other tables reference unless they are truncated in the same command, does not fire ON DELETE triggers, takes a lock that blocks every other operation on the table, and is rolled back if its transaction does not commit. On production, neither runs before the holding-table step.

Is SHA256 a checksum?

Yes, SHA-256 is a hash function, and a hash of the data works as a checksum. In Postgres the built-in sha256() function computes the SHA-256 hash of a binary string, and pgcrypto’s digest() lists sha256 among its standard algorithms. For comparing two copies of a table, I’d use md5; SHA-256 fits when someone might change the data on purpose.