How to clean up a database nobody has maintained

Sooner or later somebody inherits a database that has been running for eight years without anyone responsible for it. Duplicate customers, columns named status2, tables nobody can explain, dates stored as text, and a general suspicion that some of the numbers on the reports are wrong.

The instinct is to rebuild. That is almost always the wrong first move, because the mess encodes years of real business behaviour that nobody wrote down. Here is a safer order.

Do not change anything for the first week

Resist. Every strange thing in a database is either a bug or a business rule, and you cannot tell which by looking. The status2 column probably exists because someone needed a distinction the original design did not allow, and somewhere a report depends on it.

Spend the first pass reading and measuring instead:

• Row counts per table, and how they have grown.
• Which tables have been written to in the last ninety days.
• Which columns are entirely null, or entirely one value.
• Which foreign keys are declared, and which relationships merely exist by convention.

That last one matters more than it sounds. A relationship enforced by the database is a rule. A relationship maintained by application code is a hope, and it is where the orphaned records come from.

Find out what is actually used

Half the tables in an old database are dead, and knowing which half changes the size of the job dramatically.

Postgres tracks reads and writes per table in its statistics views, and it is worth simply looking. A table with zero sequential scans, zero index scans and zero writes over a month is not part of the system, whatever the schema diagram suggests.

Do the same for the application side: search the codebase for each table name. Anything that appears in neither the statistics nor the code is a strong candidate for the archive pile.

Archive, do not drop. Rename it with a prefix, or copy it to a separate schema, and wait a quarter. Nothing costs less than a table sitting untouched, and nothing costs more than dropping the one table the annual report needed.

Fix data types before fixing data

Dates stored as text, numbers stored as text, booleans stored as 'Y' and 'N' and occasionally 'y'. These are the root cause of a large share of the duplicate and mismatch problems downstream, because text comparison is not the comparison anyone intended.

Correct the type first, then clean the values, because the type change surfaces every bad value at once. Do it as a new column, backfilled, verified, then swapped, rather than an in-place alter on a live table. Slower and boring, and it does not take production down at lunchtime.

Money deserves a specific mention: store it as an exact numeric type or as integer cents, never as a floating point number. If the database currently uses floats for money, that is not a preference, it is a defect, and the reports have been slightly wrong the entire time.

Then deduplicate, carefully

Duplicates are the reason most of these projects start. The customer exists four times, with three spellings and two phone numbers.

The order that works:

1. Define what “same” means, in writing, with the business. Same email? Same phone and surname? There is no technically correct answer, only a business one, and getting this agreed is most of the work.
2. Produce the candidate list and have a human review a sample. Every automatic matching rule has false positives, and merging two real customers is much worse than leaving two records for one.
3. Merge into a surviving record, keeping the merged IDs. Never delete the losing record outright. Keep a mapping table so any historical reference can still be resolved.
4. Add the constraint that prevents recurrence. This is the step that gets skipped, and it is why the same cleanup gets done again in three years. A unique index on the agreed key means the problem cannot come back.

Add the constraints last, and expect them to fail

Once the data is clean, declare the rules the database should have had: foreign keys, not-null, unique, check constraints on things like status values.

Expect the first attempt to fail on real rows. That failure is the point, and it is the cheapest audit you will ever run. Each rejection is a genuine data problem that has been sitting there silently, and fixing them individually is far easier than discovering them one at a time through user complaints over the next decade.

Only then, performance

With dead tables archived, types correct and constraints in place, performance problems look completely different. A large fraction of them simply disappear, because text comparisons became integer comparisons and the planner can finally use an index. Whatever remains is worth handling properly with indexes chosen deliberately rather than added hopefully.

Doing performance work first is the common mistake. You end up carefully optimising queries against tables you are about to delete.

Before any of it

Take a backup, prove it restores, and keep it. Every step above is reversible if you have a known-good copy and irreversible if you do not, and cleanup work is exactly when people are moving fast on production. That is worth its own attention, which is why it has its own article.

If you have inherited something like this and want an assessment before touching anything, tell us roughly how old it is and what it runs.

Related: signs your database is the bottleneck, when a spreadsheet becomes a liability, and our database work.

Further reading

• PostgreSQL statistics collector documentation
• PostgreSQL constraints documentation