Database work we take on, from schema to recovery

Our core work is software that manages databases. That can mean a component sitting alongside an existing engine, a tool that solves a specific administrative problem, or an extension to something already running. We build for PostgreSQL, MySQL, SQL Server and IBM Db2 environments, and the choice of stack follows the estate rather than our preferences.

Development is database-first, which means we design the data model before the interface. It shows in the result. Systems built this way tend to survive their first year of real use without a redesign, because the assumptions about what the data can and cannot do were made early and on purpose.

A schema is a set of decisions about what your organisation will find easy for the next several years. Normalisation, index strategy, naming conventions, foreign key constraints, the use of nullable columns: each one is cheap to change at design time and expensive later.

We work on schemas at both ends. New designs get built with the workload in mind, whether it is a transactional service or an analytical store. Existing schemas get reviewed against how they are actually used, which frequently differs from how they were imagined. The output is a concrete change plan, not a lecture on third normal form.

Slow queries usually have one of four causes: a missing or unused index, a plan that the optimiser chose badly, a query written in a way that prevents a good plan, or a data volume the design was never meant to hold. Finding out which one applies requires reading the execution plan, not guessing.

We optimise queries across PostgreSQL, MySQL and SQL Server, and we look at the surrounding causes as well. A query that is slow because the table has grown by a factor of fifty is a different problem from one that is slow because of a bad join order, and the fixes are different too.

Monitoring answers a question that query optimisation does not: what is happening over time. Connection counts, cache hit ratios, replication lag, lock contention, the growth rate of the largest tables. Without this, a database is managed reactively, and reactive management means incidents are discovered by users.

We set up monitoring that produces something a person can act on. Alerts that fire constantly get ignored. Dashboards that show forty metrics and no trend get ignored too. The goal is a short list of signals that indicate a problem early enough to do something about it.

A backup that has never been restored is a hypothesis, not a safeguard. We treat recovery as the thing worth testing, with backups as the means to it. That means documented restore procedures, periodic restore drills, and clarity about the recovery point and recovery time a business can actually tolerate.

The practical work includes choosing between logical and physical backups, deciding where copies live and how long they are kept, and making sure the person who needs to restore at 3am knows where the instructions are. This is unglamorous work and it is the difference between an incident and a catastrophe.

Migrations between engines, between hosting models, or between major versions of the same engine. Each has its own failure modes. Version upgrades are usually the least dramatic and the most often deferred, which is why they tend to hurt when they finally happen.

We approach migration in phases with a rollback point at each one. Data type mapping, SQL dialect differences, and the behaviour of client tools all get checked before the cutover rather than after. A migration plan that assumes everything works on the first attempt is a plan that has not been tested.