Database development is the part of a system nobody discusses until something is slow or wrong. It is also the part that is most expensive to change, because everything else is built on top of it. These are the choices that decide whether the database becomes a foundation or a bottleneck.
The things to get right
- The data model is harder to change than the code. Spend the time there first.
- Slow systems are almost always missing an index or making too many calls, not short of hardware.
- Measure before optimising. Guessing costs both time and performance.
- Migrations must be repeatable and reversible, or nobody will dare deploy.
- A backup without a tested restore is not a backup.
- Personal data must be findable and deletable. That is a design requirement, not a task for the end.
The model is the decision that binds you
Code can be rewritten in a week. A data model with five years of data in it cannot. So it is worth spending longer understanding what a customer, an order or an agreement actually is in your business before the tables are fixed.
The classic question that surfaces problems early: can the same thing appear twice with different statuses? If so, it probably belongs in its own table.
Performance is measurable, not a feeling
When something is slow, the cause is almost always one of three. An index is missing on the field being searched. The application fetches one record at a time in a loop instead of all at once. Or a query pulls an entire table to display ten rows.
Find the genuinely slow query with the database’s own tooling before anyone proposes bigger hardware. More CPU hides the problem for six months and makes it dearer to find afterwards.
Indexes cost something too
Indexes make reads faster and writes slower. On a system with heavy updating, too many indexes can be the reason it is slow. Add indexes for the queries you actually run, not for every field somebody might search one day.
Migrations and deployment
Database changes belong in version control alongside the code, applied automatically, and reversible. Without that, every deployment becomes a manual act that one person remembers how to perform.
For larger restructuring: add the new field, write to both, move the data, switch reads, remove the old one. Four small steps are far safer than one big one, and each can be rolled back on its own.
Backup, restore and personal data
Test the restore. It sounds obvious and it is the most common unpleasant surprise we meet: backups have run for two years, nobody has tried to bring data back, and the format turns out to be unusable.
Personal data also requires that you can say where someone’s information sits and delete it. If it is spread across fifteen tables with no shared key, that becomes manual work every time. Designing for it is cheaper.
When the database is inherited
Most of the work we are asked to do is not building new, but understanding something that has run for ten years. Start by measuring rather than guessing: which queries are slow, which tables are growing, where is data with no owner.
If the codebase above it also needs assessing that falls under code audit, and keeping the system running meanwhile under software maintenance.
How we work
We handle data modelling, migration, tuning and operations as part of our database development service, usually alongside API development where the data also has to reach other systems. Where the work continues over time we put a dedicated development team on it.
Frequently asked questions
SQL, unless you have a concrete reason otherwise. Most business data is relational, and relational databases now handle both JSON and large volumes well. Choose NoSQL when the data genuinely is documents or events.
Size is rarely the problem in itself. The problem appears when queries are not written for the volume. We see tables with a hundred million rows performing well and tables with twenty thousand performing badly.
Technically, often yes. But if the system is slow today it will be slow in the cloud, just with a monthly invoice attached. Clean up first, move afterwards.
At least twice a year, and always after major changes. Write down the date so somebody can answer when it was last done.
Where to begin
Measure before optimising, index for the queries you actually run, version-control the migrations, and test the restore. It is not exciting work, but it decides whether the system is still workable in five years.
Want us to look at your database? Get in touch.
