Databases#

Day 5 · Persistence

The hardest bugs in your career will be data bugs. The bug isn't in your code - the bug is that two services disagree about what's true, a migration ran half-way, a JSON column has three shapes, or a vector index returned plausible-but-wrong neighbours. Databases are where state lives, and state is where production gets weird.

This chapter teaches you enough to not be afraid of the database - to run a query, design a schema, reason about transactions, and pick the right store for the job.

Outline#

1. Why databases exist - durability, consistency, concurrency
2. Relational basics - tables, keys, normalisation, joins
3. SQL fluency - SELECT, INSERT, UPDATE, DELETE, aggregates, window functions
4. Indexes - what they are, when they help, when they hurt
5. Transactions and isolation - ACID, read committed vs repeatable read
6. PostgreSQL specifics - JSON columns, EXPLAIN ANALYZE, extensions
7. Migrations - schema as code, forwards-only, zero-downtime patterns
8. NoSQL - when MongoDB makes sense, when it doesn't
9. Vector databases - embeddings, ANN search, Qdrant basics
10. Operational reality - backups, replicas, connection pooling, the n+1 problem

101 Primer#

Pick the right tool#

Default to Postgres. It now has JSON columns, vector search via pgvector, full-text search, and time-series via Timescale. You can grow into one database for a long time. Adding a second store doubles your operational complexity, not your power.

SQL - the 80% that matters#

-- Read
SELECT u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.email
HAVING COUNT(o.id) > 0
ORDER BY order_count DESC
LIMIT 20;

-- Write
INSERT INTO users (email, name) VALUES ('a@welzin.ai', 'Aman')
RETURNING id;

UPDATE users SET name = 'Aman M.' WHERE id = 42;

DELETE FROM sessions WHERE expires_at < NOW();

Things worth internalising:

• LEFT JOIN keeps the left table's rows even when no match. INNER JOIN drops them.
• WHERE filters rows; HAVING filters groups (post-aggregation).
• RETURNING (Postgres) lets INSERT/UPDATE/DELETE return rows - no extra SELECT round-trip.
• NULL is not equal to anything, including itself. Use IS NULL, not = NULL.

Indexes - the speed-up that also slows you down#

An index is a pre-sorted lookup structure that makes reads on the indexed column fast. It also makes writes slower (the index has to be updated). It also costs disk.

CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at DESC);

Rule of thumb: index the columns you filter or join on, in the order you filter. Multi-column indexes are leftmost-prefix-matched - the above index helps WHERE user_id = ? and WHERE user_id = ? AND created_at > ?, but not WHERE created_at > ? alone.

Always read EXPLAIN ANALYZE before adding an index. If you see Seq Scan on a large table where you expected an Index Scan, your index isn't being used.

Transactions, briefly#

A transaction is an all-or-nothing change. ACID: Atomic, Consistent, Isolated, Durable.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If anything fails between BEGIN and COMMIT, both updates roll back. Use them for anything that touches more than one row across more than one table.

Isolation levels matter under concurrency. Postgres defaults to READ COMMITTED, which is fine for most app code. Upgrade to REPEATABLE READ or SERIALIZABLE if you're doing read-modify-write on the same row from multiple workers.

Schema as code: migrations#

Never edit production schema by hand. Use a migration tool - prisma migrate, alembic, knex, sqlx, your framework's flavour - and commit migrations to Git. Migrations should be:

• Forward-only. Don't write down migrations you'll never run.
• Backwards-compatible with the previous app version. Deploy schema change before code change.
• Reviewed. A bad migration is the #1 way to take down production.

Common safe pattern for adding a NOT NULL column to a big table:

1. Add the column nullable.
2. Backfill in batches.
3. Add NOT NULL constraint.
4. Ship the code that requires it.

The n+1 problem#

The single most common performance bug in web apps:

users = User.objects.all()           # 1 query
for u in users:
    print(u.profile.name)            # n queries, one per user

Fix it by eager-loading the relation (select_related / joinedload / include). If your endpoint takes seconds and the DB looks bored, you have n+1s.

NoSQL - use sparingly#

MongoDB is genuinely good for: append-only logs of varying shape, prototype-velocity schemas, document-per-request workloads. It is genuinely bad for: anything with joins, anything where you'll later wish you had transactions, anything where strong consistency matters.

If you're reaching for Mongo, ask: would a Postgres JSONB column do the same thing with fewer foot-guns? Usually yes.

Vectors and RAG, briefly#

A vector database stores high-dimensional embeddings (e.g. 1536-dim from text-embedding-3-small) and finds the nearest neighbours of a query vector. This is how retrieval-augmented generation finds "relevant" passages.

# Conceptual flow
doc_chunks = chunk(document)
embeddings = embed_model(doc_chunks)        # list of 1536-dim vectors
qdrant.upsert(collection="docs", points=zip(ids, embeddings, metadata))

query_vec = embed_model(user_question)
hits = qdrant.search("docs", query_vec, limit=5)
# Pass hits as context to the LLM

What can go wrong:

• Chunks too large → bad recall. Too small → bad coherence. Tune.
• Mixing embedding models in one collection - vectors aren't compatible.
• Forgetting metadata filters → returning chunks from the wrong tenant.
• Trusting cosine similarity blindly. Always sanity-check top-k by eye.

Operational checklist#

• Backups: automated daily, restored to a scratch instance monthly. Untested backups are wishful thinking.
• Replicas: read replicas for analytics, never as a write fallback (consistency).
• Connection pooling: PgBouncer in front of Postgres if you have > 50 concurrent connections.
• Slow query log: enable it. Review weekly.
• Secrets: connection strings live in a secrets manager, never in source.

Hands-on Checkpoints#

• Spin up Postgres locally via Docker; create a users and orders schema with proper foreign keys.
• Insert 100k rows of synthetic data; write a query that joins them.
• Run EXPLAIN ANALYZE, observe the plan, add an index, re-run, see the change.
• Wrap a two-step update in a transaction, force the second step to fail, confirm rollback.
• Set up pgvector, embed 50 short docs with any sentence-transformers model, query by similarity.
• Write a migration that adds a NOT NULL column safely (the 4-step pattern).

Further reading#

• Designing Data-Intensive Applications - Martin Kleppmann (the book on this topic)
• Use The Index, Luke - short, free, on indexing
• PostgreSQL docs - actually well-written
• Qdrant quickstart

Welzin opinion: Reach for Postgres first. Add a second datastore only when you can name the specific Postgres limitation you're hitting. Half of "we need a graph DB" turns out to be "we need a recursive CTE."