Database Management: The Schema Outlives Everything

Database advice tends to stay abstract. Choose the right system, design a good schema, optimise your queries, and index appropriately. Nobody disagrees with any of it, and none of it tells you what to type.

It stays abstract because the concrete parts require reading a query plan, and query plans are specific and unglamorous. So this goes there instead, and it starts from one observation: the schema is the decision you can't cheaply reverse.

Frameworks get replaced over a weekend. Caching layers get swapped out. A data model that made the common query expensive will still be causing problems in five years, because by then a hundred things depend on its shape.

The choice, briefly

Postgres unless you have a reason. It handles relational data, JSON documents, full-text search, geospatial, and vector search, so one system covers what used to need three. MySQL and MariaDB are fine, especially if you already know them. SQLite is genuinely viable for single-server applications and is underrated.

One correction worth making, because it circulates: Redis is not a database choice in the sense MongoDB is. It's an in-memory data structure store, used almost always as a cache or a queue alongside a primary database rather than instead of one. Treating it as a NoSQL alternative to Postgres sends people somewhere they don't want to go. I covered where it actually fits, and the licence change that produced Valkey, in the piece on server-side tools.

Schema

Model the domain, not the screen. Screens change every release. The relationships between orders, customers, and line items don't. A schema shaped around your current interface will be wrong the first time the interface changes.

Put constraints in the database. NOT NULL, foreign keys, UNIQUE, and CHECK are not redundant with application validation, because the application isn't the only thing that writes. Migrations write, scripts write, an admin console writes, and someone will eventually write directly. Application validation stops one path; a constraint stops all of them.

Choose types deliberately, because three mistakes here are close to universal:

Use timestamptz rather than timestamp. Storing a timestamp without a time zone means storing an ambiguous moment, and you find out during the clock change.

Never store money as a float. numeric or an integer count of minor units. Floating-point arithmetic produces amounts that don't reconcile, and the errors compound.

Prefer text over varchar(n) in Postgres unless the length limit is a genuine business rule. There's no performance difference, and changing a limit later requires a migration.

IDs. Sequential integers are compact, index well, and leak information: an ID in a URL tells anyone how many records you have and lets them guess neighbours. UUIDs avoid that, and random v4 UUIDs fragment your index badly because inserts land everywhere. UUIDv7 is the right answer: it's time-ordered, so inserts stay sequential while remaining unguessable.

Normalise first, denormalise with evidence. Normalisation is the correct default because it makes inconsistency impossible, not just unlikely. Denormalise when you have a measured problem, and know that you've taken on the job of keeping two copies in step.

Indexes

An index makes reads faster and every write slower, because each insert, update, and delete has to maintain it. So an unused index is pure cost, and databases are full of them.

Composite index column order matters, and it's the thing people get wrong most. Equality conditions first, range conditions last:

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at);

That serves WHERE customer_id = ? AND created_at > ? well. Reverse the columns, and it doesn't, because once the index is scanning a range on the first column, it can't use the second to narrow further.

A composite index also serves queries on its leading columns alone, so this one covers WHERE customer_id = ? too. Which means a separate index on customer_id is redundant and should be dropped.

Cardinality decides whether an index helps. Indexing a boolean column with an even split is useless: the database still reads half the table, and it's cheaper to do that sequentially. Indexes earn their place on selective columns.

Partial indexes cover the common case cheaply:

CREATE INDEX idx_orders_pending
  ON orders (created_at) WHERE status = 'pending';

If most rows are complete and you only ever query pending ones, this index is a fraction of the size and correspondingly faster.

Find your unused indexes. Postgres tracks scan counts in pg_stat_user_indexes. Any index with zero scans after a representative period is costing you write throughput and storage for nothing.

Reading the query plan

This is the central skill, and everything above is guesswork without it.

EXPLAIN ANALYZE SELECT ...;

EXPLAIN shows the plan. ANALYZE actually runs the query and shows what happened. Three things to look for:

Sequential scan on a large table usually means a missing index. Usually, but not always: if a query returns most of the table, a sequential scan is genuinely the faster choice and the planner is right.

A large gap between estimated and actual rows means the planner's statistics are stale, so it's choosing badly on bad information. Run ANALYZE on the table.

Nested loops over large row counts, which is often what an N+1 pattern looks like from the database side.

The habit worth forming: when something is slow, read the plan before changing anything. Adding indexes speculatively is how you end up with fifteen of them and slower writes.

The N+1 problem

The most common performance bug in web applications, and it comes free with every ORM.

You fetch fifty orders, then loop over them accessing order.customer, and your ORM issues fifty additional queries. Each is fast. Together they're a disaster, because the cost is fifty round trips rather than fifty lookups.

The fix is eager loading, which every ORM supports under some name: includes in Rails, select_related and prefetch_related in Django, with in Laravel, Include in Entity Framework Core.

It persists because it's invisible in code review. The loop looks fine. You only see it in the query log, which is why logging query counts per request in development is worth setting up once and keeping forever.

And worth repeating a point from the caching piece: caching an N+1 pattern hides it rather than fixing it. Fix the query first.

Transactions

A transaction guarantees that a group of statements either all take effect or none do. That's it, and it's more limited than people assume.

Isolation levels decide what you see while others are writing. Most databases default to read committed, which prevents dirty reads and permits this:

Request A reads balance: 100
Request B reads balance: 100
Request A writes 100 - 30 = 70
Request B writes 100 - 50 = 50

Thirty pounds vanished. Both transactions were valid; neither saw the other. The fixes are row-level locking with SELECT ... FOR UPDATE, an atomic update expressed in SQL rather than in application code, or a higher isolation level with retry logic. Choose deliberately, since the default will not save you.

Keep transactions short. A transaction holds locks until it commits, so every other request touching those rows is waiting. Never make a network call inside one: an HTTP request to a payment provider inside an open transaction turns their latency into your lock contention.

Connection pooling

Database connections are expensive to establish, and each one consumes memory on the server, so Postgres in particular has a fairly low practical ceiling.

Your application should use a pool sized to what the database can support, not to what your application would like. Then, if you're running serverless functions or many instances, put a dedicated pooler such as PgBouncer in front, because otherwise each instance holds its own pool and the total blows past the limit. This surprises people right when traffic grows.

Pagination

OFFSET 10000 LIMIT 20 makes the database read and discard 10,000 rows to return 20. It gets slower the deeper the user goes, which is why the last page of a long list crawls.

Keyset pagination uses the last seen value instead:

SELECT * FROM orders
WHERE created_at < :last_seen
ORDER BY created_at DESC
LIMIT 20;

Constant time at any depth. The trade-off is no jumping to page fifty, which for an infinite-scroll interface is no trade-off at all.

Migrations

Schema changes are code and belong in version control, applied by a tool, tested against a copy of production data. Manual changes on a live database are how teams end up unable to recreate their own environment.

Two things worth knowing beyond that.

Expand and contract. To rename a column without downtime: add the new one, write to both, backfill, switch reads, then drop the old one, across several deploys. A rename in one migration breaks every running instance of the previous version as soon as it lands.

Some migrations lock. Adding a column with a non-null default, adding an index without CONCURRENTLY, or changing a type can lock a large table for long enough to take the site down. Know which operations lock in your database, and use the concurrent variants.

Backups

An untested backup is not a backup. It's a file you're hoping about.

Restore it. On a schedule, into a real environment, and time it, because that number is your actual recovery time and it's usually longer than anyone guessed.

Two numbers worth agreeing on explicitly: how much data you can afford to lose, which sets backup frequency, and how long you can afford to be down, which sets your restore approach. Point-in-time recovery, where you replay the write-ahead log to any moment, is what you want when the incident is a bad migration at 14:32 rather than a disk failure.

Keep at least one copy somewhere your production credentials can't reach, since ransomware and a mistaken script both delete whatever they have access to.

What to learn

SQL properly, ahead of any ORM. Joins, aggregates, window functions, and how to read a plan. It transfers across every database and outlasts every framework, which is why it's first on the ordering I set out in the server-side tools piece.

Your database's specifics, because the general knowledge only goes so far. Postgres's EXPLAIN output, its index types, and its locking behaviour are worth real study if that's what you run.

Data modelling has no shortcuts and the highest cost of error.

The short version

The schema is the decision that outlives your framework, your caching layer, and probably your job. Model the domain rather than the screen, put constraints in the database, and get the types right the first time.

Index selectively, order composite columns equality-first, and drop what isn't used.

Read the query plan before changing anything, because otherwise you're guessing.

Keep transactions short, never do network calls inside them, and know that read committed will not prevent a lost update.

And restore your backups on a schedule, because the alternative is discovering their state on the worst day you'll have.