System Design: Databases Basics

What an index costs

Indexes are not free. They make writes slower (every insert, update and delete also updates each affected index), they take storage, and they compete with table data for cache memory.

So index to serve your actual queries, not every column. Good candidates are columns used in WHERE filters, JOIN conditions and ORDER BY clauses on frequent queries. Poor candidates are columns with very few distinct values, or tables with heavy writes and rare reads.

Composite indexes cover several columns, and column order matters. An index on (clinician_id, starts_at) serves "this clinician's appointments for this day, in order" very well, but it usually does not help a query that filters only on starts_at, because the index is sorted by clinician_id first.

CREATE INDEX idx_appt_clinician_start
  ON appointments (clinician_id, starts_at);

Use your database's explain tool (EXPLAIN in PostgreSQL and MySQL) to see whether a query uses an index rather than guessing.

Normalisation versus denormalisation

Normalisation means organising tables so each fact is stored once. An appointment row stores patient_id, not the patient's name and phone. If a patient changes their phone number, you update one row and every appointment reflects it. Benefits: no duplicated data, fewer update bugs. Cost: reading often requires joins.

Denormalisation deliberately duplicates data to make reads cheaper, for example a booked_count on each clinician-day row instead of counting appointments on every page load. Benefits: faster reads. Cost: you must keep copies in sync, and a missed update means drift.

A sensible path: normalise first, measure, then denormalise only the specific hot paths that need it.

Transactions in practice

A transaction protects multi-step changes. Cancelling an appointment may need to mark the slot free and queue a notification; either both happen or neither does.

BEGIN;
UPDATE appointments SET status = 'cancelled' WHERE id = :appt_id;
INSERT INTO notifications (appointment_id, kind) VALUES (:appt_id, 'cancelled');
COMMIT;

If any statement fails, you issue ROLLBACK and the database undoes the earlier steps. Three practical rules:

1. Keep transactions short. A long transaction holds locks or old row versions, blocking others and bloating storage.
2. Do not call slow external services inside a transaction. Waiting on a network call while holding locks is a classic cause of stalls.
3. Know your concurrency problems. Two requests that both read a value and then write back can overwrite each other (a "lost update"), and two requests that both check "is the slot free?" can both say yes. Use atomic updates, row locks, constraints or version columns.

Transactions that span multiple separate databases or services are much harder; see distributed transactions later.

Connection pooling

Opening a database connection is expensive: network setup, authentication and, in some databases, a whole server process per connection. Databases also limit concurrent connections. A connection pool keeps a set of open connections that requests borrow and return, like a taxi rank instead of building a new car for each trip.

Points to know: a bigger pool is not better, because a database can only do so much in parallel and excess connections just queue inside it. The total across servers matters (20 servers with a pool of 50 means up to 1,000 connections). An external pooler such as PgBouncer for PostgreSQL multiplexes many client connections onto fewer server connections. And release connections promptly: a leak eventually freezes the app.

The NoSQL families

NoSQL is an umbrella term for databases that are not relational tables. They typically relax something (joins, strict schema, or full transactions) in return for flexibility, scale or speed on a specific pattern.

Many modern systems are polyglot: each part uses the store that suits it, for example patient records in PostgreSQL, sessions in Redis and scanned documents in object storage. The price is operating several systems, so add each one only when it earns its place.

Answer from the top. Also weigh how much data and traffic you really have (see estimation) and what your team already runs well: an unfamiliar database in production is a risk of its own.

In practice: double-booking prevention for a clinic booking system

The situation

A group of 40 clinics with about 300 clinicians takes bookings through a patient web app, a mobile app and a reception desktop tool. Around 15,000 appointments are created per day, and the appointments table holds roughly 20 million rows. A small team of five engineers runs a TypeScript API on a managed PostgreSQL instance, with Redis for sessions, Prometheus and Grafana for metrics, and Terraform for infrastructure. Health data rules apply: access must be auditable and backups encrypted.

Two things hurt. First, a few times a week two patients end up with the same clinician at the same time, found only when both arrive. Second, the clinician's day view takes 1.5 to 3 seconds because the query scans most of the table.

Starting architecture

The check-then-insert logic lives in application code. Two requests interleave, both see an empty slot, and both insert. No amount of careful coding in the API fixes a race that only the database can see.

Options considered

The decision

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE appointments
  ADD CONSTRAINT no_double_booking
  EXCLUDE USING gist (
    clinician_id WITH =,
    tstzrange(starts_at, ends_at) WITH &&
  ) WHERE (status <> 'cancelled');

By default tstzrange includes the start and excludes the end, so an appointment ending at 10:30 does not conflict with one starting at 10:30.

Rollout and verification

Order matters. First run a self-join query to list existing overlaps and resolve them with the clinics, since the constraint cannot be added while violations exist. Deploy the API change that maps the exclusion violation (SQLSTATE 23P01) to an HTTP 409 first, so it is ready before the constraint is. Adding the constraint builds an index while holding a strong table lock (writes are blocked, and in PostgreSQL reads are too), so rehearse on a production-sized copy to learn how long it takes, then run it in a quiet window.

Verify with the duplicates query returning zero rows, EXPLAIN showing an index scan for the day view, day-view p95 under a target such as 200 ms, and a Grafana panel of 409 responses per hour (some are expected: they are the races now being caught).

The 2 a.m. failure

At 07:00 on a Monday, clinics open and the page reads "reschedule errors above 20 percent". The reschedule feature was implemented as insert the new slot, then cancel the old one in a second step. Moving an appointment by 15 minutes overlaps the original, which is still active, so the constraint rejects the new row.

Detection: the 409 rate for the reschedule endpoint spikes, and the PostgreSQL log shows conflicting key value violates exclusion constraint "no_double_booking". The first three checks: grep the database log for that message and note the endpoint, run SELECT id, starts_at, ends_at, status FROM appointments WHERE clinician_id = :id AND starts_at::date = :day; for a failing case, and check git log for the reschedule change. Mitigation: disable the reschedule button behind its flag and have reception cancel and rebook. Follow-up: rewrite rescheduling as a single UPDATE of the existing row's times inside one transaction, which the constraint checks against other rows but not itself, and add an integration test that moves an appointment into its own old time window.

Architect's notes

• Put the invariant where it cannot be bypassed. If a rule must always hold ("no two appointments overlap"), enforce it in the database. Every other client, script and future service benefits automatically.
• Look at the query log before indexing. Collect the top queries by total time (for PostgreSQL, the pg_stat_statements extension) and index for those, not for the ones developers remember.
• Ask who can write to production data. Direct access by support or analytics tools bypasses application checks, which is another reason for constraints. Also decide who reviews migrations.
• Rehearse migrations at production size. Lock duration scales with table size. A migration that is instant on a laptop can block writes for minutes in production.
• Plan for the unglamorous requirements. Audit logs, backups you have restored at least once, and retention rules matter more than the choice of index in a health setting.
• Defer the polyglot stack. Resist adding a document store or search engine until PostgreSQL measurably cannot serve a specific, named access pattern.

Common mistakes

• Choosing a database for fashion. A trendy store may not fit your queries. Start from access patterns and the team's skills.
• Check-then-insert in application code. Two concurrent requests both pass the check. Use a constraint, a lock or an atomic statement.
• Missing indexes. Queries scan whole tables as data grows. Use EXPLAIN and index real filters and joins.
• Too many indexes. Each one slows writes and consumes storage. Remove unused indexes.
• N+1 queries. Fetching a list, then one extra query per item, multiplies round trips. Use joins or batch lookups.
• Long or chatty transactions. They hold locks and slow everyone. Keep them short and free of external calls.
• Unbounded connections. Autoscaled apps can overwhelm the database. Pool, cap and monitor connections.
• Denormalising too early. Duplicated data drifts. Normalise first and denormalise only measured hot paths.

Key takeaways

• Start relational. A relational database with good indexes serves most systems well and gives strong guarantees.
• ACID protects correctness. Transactions give all-or-nothing changes, constraints, isolation and durability.
• Let the database enforce invariants. Constraints close races that application code cannot.
• Indexes speed reads and slow writes. Build them for real queries, check with EXPLAIN, and prune the rest.
• Normalise, then denormalise on purpose. Duplicate data only for measured hot read paths and keep it in sync.
• Keep transactions short. Long transactions and external calls inside them cause stalls.
• Pool connections. Pools protect the database from connection storms; size them modestly.
• NoSQL is a set of trade-offs. Key-value, document, wide-column and graph each fit particular access patterns.

Practice exercises

Exercise 1 — Starter (30 min): Write SQL CREATE TABLE statements (on paper or in SQLite or PostgreSQL) for patients, clinicians and appointments, with primary keys, foreign keys and a unique constraint on clinician and start time. Insert a few rows and try to break each rule.

Exercise 2 — Intermediate (2 hours): Generate a table with one million rows of fake appointments in a local database. Time a query for "clinician X's appointments on one day" without an index, then with a composite index on (clinician_id, starts_at). Compare EXPLAIN output and timings, then measure insert speed with zero, two and five extra indexes. Write down the trade-off you observe.

Exercise 3 — Advanced (half day): In PostgreSQL, reproduce the double-booking race with two concurrent sessions or a small script that checks and then inserts. Then add the exclusion constraint shown above and show that the second insert fails. Finally design the reschedule operation so it succeeds when the new time overlaps the old one, and explain how you would roll the constraint out on a live table.

Related reading

• System Design: Caching Basics — the usual first step when reads overload the database.
• System Design: CDN and Object Storage — where large files should live instead of the database.
• System Design: Replication — how read replicas and failover build on a single primary.
• System Design: Storage Engines — B-trees versus LSM-trees, for a deeper look at how data is stored.
• PostgreSQL documentation — the official reference for SQL, indexes and EXPLAIN.