Database Administration

Database administration is the practice of keeping a database correct, durable, fast enough and recoverable while it is being changed underneath you. The job has shifted — managed services now handle provisioning, patching and failover mechanics — but the part that never moved is the part that decides whether a system survives: schema and index design, transaction behaviour, backup and restore, replication topology, and the operational limits that stop one query from taking the whole instance down.

Physical design: indexes and access paths

An index is a redundant, ordered copy of part of your data that exists so the engine can find rows without reading all of them. Everything about indexing follows from that sentence. Indexes make reads faster and every write slower, because each write must maintain every index on the table. They consume storage and memory, and memory is usually the scarce resource, since an index only pays for itself when its hot portion stays cached.

The workhorse is the B-tree, which supports equality and range predicates and returns rows in key order — which is why it can also satisfy an ORDER BY without a sort. A composite index over several columns is usable for a prefix of those columns, so column order is a design decision, not a detail. A covering index includes every column a query needs, letting the engine answer from the index alone and skip the table entirely. A partial (filtered) index covers only rows matching a predicate, which is the right tool for a status column where one value is rare and heavily queried.

The two opposite failure modes are equally common. Missing indexes show up as sequential scans in the plan and latency that grows with table size. Excess indexes show up as write amplification, bloated storage, and a planner with too many mediocre options. Redundant indexes — one whose columns are a prefix of another's — are pure cost.

The planner and its statistics

A cost-based planner chooses among access paths using statistics about the data: how many distinct values a column has, how they are distributed, how correlated the physical row order is with a column's order. When those statistics are stale or the distribution is skewed, the planner estimates badly and picks a plan that is catastrophically wrong rather than slightly wrong — a nested loop over what it believed were ten rows and were actually ten million. Reading the actual execution plan, with real row counts next to estimates, is the single highest-value diagnostic skill in this discipline. A large gap between estimated and actual rows is the signal; the fix is usually better statistics, a rewritten predicate, or an index that makes the good plan obvious.

Transactions, isolation and concurrency

The SQL standard defines four isolation levels — read uncommitted, read committed, repeatable read and serializable — in terms of the anomalies each one permits: dirty reads, non-repeatable reads and phantoms. Real engines implement these differently, and several provide stronger or differently-named guarantees, so the level name alone does not tell you the behaviour. What you must know for your engine is which anomalies remain at your default level, because application code is usually written as if the level were serializable while the database is running something weaker.

Most modern engines use multi-version concurrency control: a write creates a new version of a row rather than overwriting it, so readers never block writers and writers never block readers. The consequence is that old versions accumulate and must be reclaimed by a background process. If that process cannot keep up — most often because a single long-running transaction holds a snapshot open and makes every version since then still potentially visible — dead rows accumulate, tables and indexes bloat, scans read more pages for the same data, and performance decays in a way that looks like mysterious gradual degradation. A long-running transaction is one of the most destructive things an application can do to a database, and it is almost always accidental: an idle connection left inside a transaction while it waits on a network call.

Locking is the other half. Row locks contend only under genuine conflict; the dangerous locks are the ones taken by schema changes, which can queue behind a long read and then block every subsequent query behind themselves. Deadlocks are normal in a busy system and are resolved by the engine killing a victim; the application's job is to retry, and the design's job is to acquire locks in a consistent order.

Durability, backup and replication

Durability comes from a write-ahead log: changes are written and flushed to the log before the data pages are updated, so a crash can be recovered by replaying the log from the last checkpoint. Every durability knob is about how much of that flushing you are willing to skip in exchange for throughput, and the honest framing is that relaxing it is a decision to lose recent transactions on a crash.

Replication reuses the same log. Physical replication ships the log itself and produces a byte-identical replica; logical replication ships decoded row changes and allows different schemas or versions on each side, which is what makes it the tool for migrations. Replication can be asynchronous (fast, with a window of possible data loss on failover) or synchronous (no loss of acknowledged commits, at the cost of making the primary wait on a replica, so a slow or hung replica becomes a write outage).

Backups are the only real safety net, and the rule is absolute: a backup that has never been restored is not a backup, it is a hypothesis. Restores must be practised, timed and documented, because the number that matters in an incident is how long a restore takes, and that number is only known by measurement. Point-in-time recovery combines a base backup with retained log segments so you can recover to a moment just before a bad migration. A replica is not a backup — it replicates your mistakes faithfully and immediately.

Schema change without downtime

Migrations are where healthy databases get broken. The safe pattern is expand and contract: add the new structure, write to both old and new, backfill in bounded batches, move readers over, verify, and only then drop the old structure — each step independently deployable and reversible. The unsafe pattern is a single migration that rewrites a large table under a blocking lock. Two disciplines make the difference: know which operations your engine can perform without a full rewrite or an exclusive lock, and always set a short lock timeout on migrations so a blocked migration fails fast instead of queueing the entire workload behind it.

Operating limits and what to watch

Databases fail in characteristic ways, and most are preventable with limits set in advance rather than alerts added afterwards. Set a statement timeout so no single query can run unbounded, and an idle-in-transaction timeout so a forgotten transaction cannot block version cleanup. Put a connection pooler in front of anything with many application instances: each connection costs memory and scheduling, and a connection storm during a restart is a classic self-inflicted outage where the database is healthy but has no capacity to accept the herd.

The signals worth watching are the ones that lead rather than follow: replication lag, cache hit ratio, the age of the oldest running transaction, table and index bloat, lock wait time, and per-statement latency percentiles rather than averages. An average hides the tail, and the tail is what users experience.

Failure modes to recognise on sight

• Gradual slowdown with no code change — usually bloat from blocked cleanup, or an index that no longer fits in cache.
• A query that was fast yesterday — a plan flip after data growth or stale statistics, not a change in the query.
• Everything blocked behind one statement — a schema change queued behind a long read, holding a lock that every later query needs.
• Failover that loses data or splits the brain — asynchronous replication promoted without fencing the old primary. Failover must be tested, and the old primary must be provably unable to accept writes.
• The N+1 query — an application loop issuing one query per row. The database is not slow; it is being asked the same question thousands of times.
• Retries without backoff — a struggling database is pushed over by its own clients' retry traffic.

When to invest in this and when not to

A managed service removes provisioning, patching and failover plumbing; it does not remove schema design, index strategy, transaction discipline, restore drills or capacity planning. Those remain yours no matter who runs the instance. Dedicated database expertise earns its keep when a single database is a critical dependency for revenue, when data volume has outgrown the point where any query can be rescued by adding memory, or when compliance requires provable recovery. Below that, the highest-leverage practices are cheap and general: read plans, set timeouts, pool connections, migrate with expand-and-contract, and restore a backup on a schedule.

For how these instances feed analytics, see data pipelines and data warehousing; for the surrounding platform, see big data architecture.