Databases designed so your reports still run in year five

Design decisions that show up years later

Almost every serious performance problem we are called in to fix has its origin in a decision made during the first fortnight of a project. A relationship modelled as one to one that later needed to be one to many. A status stored as free text so that reporting requires guessing. A field that should have kept history but only stores the current value, so nobody can answer what the price was in March.

None of those cause trouble at first. They surface in year two or three, when the business asks a reasonable question and the honest answer is that the data was never captured, or when a table reaches a size where a missing index turns a fast page into a thirty second wait. By then the fix means restructuring tables, migrating history and adjusting every part of the application that touched them.

We spend real time on the data model before development starts, and we ask about the future deliberately. What might this later belong to as well? Will you need to know what this used to be? What will you want to report on? How many of these will exist in five years? Those questions cost an afternoon and routinely save months.

Integrity enforced by the database

Applications have bugs, imports go wrong and somebody eventually writes a script that seemed safe at the time. The database is the last line of defence, and we use it properly. Foreign keys so a record cannot point at something that no longer exists. Unique constraints so the same reference cannot be created twice. Not null where a value is genuinely required. Sensible types rather than storing everything as text.

This matters more than it sounds. Data that has been allowed to become inconsistent is extremely difficult to repair afterwards, because by the time anyone notices, other records and reports have already been built on top of it. Preventing bad data is enormously cheaper than cleaning it.

Indexes chosen from real queries

Indexing is where most performance work begins and ends. An index makes reads faster and writes slightly slower, so the goal is the smallest set that covers the queries your system genuinely runs. We look at the actual query patterns rather than adding an index to every column, which is a surprisingly common approach that wastes storage and slows down every insert.

We also watch for the query patterns that scale badly, particularly code that issues one database call per row in a list. It looks harmless with twenty records and becomes catastrophic with twenty thousand, and it is by far the most frequent cause of an application that was fast on launch day and unusable a year later.

Migrations, imports and moving data safely

Changing a live database is a controlled operation. We use the Django migration system so that schema changes are versioned, repeatable and applied in the same order everywhere, rather than being typed by hand on a server and forgotten. Before anything runs against production, it has run against a copy.

Data imports from spreadsheets or older systems are treated as their own small project, because real data always contains duplicates, inconsistent spellings, dates in several formats and notes in the wrong column. We run the import repeatedly against copies, produce a report of everything that could not be matched, and agree with you how those cases should be handled before the live migration happens.

Backups that have actually been restored

A backup nobody has restored is a hope rather than a plan. We configure automated backups, keep copies away from the server they came from, and test restoration so we know how long it takes and that it works. We also think about retention, because the most common data loss we see is not a failed disk, it is somebody deleting something in error and nobody noticing for three weeks.