Chapter 12 — Persistence II — PostgreSQL and Scaling Up

“The database is the heart of most applications. The schema is its DNA. Choose both deliberately, because everything else is downstream of them.” — Joe Celko, SQL for Smarties

“For no one can lay a foundation other than that which is laid, which is Jesus Christ.” — 1 Corinthians 3:11

Why This Matters

Last week you gave your API a memory. SQLite turned a server that forgot everything on restart into one that faithfully kept a record. You wrote a schema, you wrote parameterized SQL, you survived a restart with your data intact. That was a real win, and SQLite was exactly the right tool for it.

This week you are going to learn when it stops being the right tool — and, just as important, when reaching past it is a mistake.

Here is the trap that catches good engineers. SQLite worked. The next thing the internet told you about — Postgres — is “more serious,” “production-grade,” “what real companies use.” So the temptation is to reach for it reflexively, the way a beginner reaches for the most powerful tool in the room because power feels like safety. That instinct is wrong, and this whole book has been training you to know why it is wrong. Reaching for Postgres when SQLite would do is its own architectural mistake. You will pay for it — in setup, in a server process you now have to run and secure and back up, in a network hop on every query — and you will get nothing back if your problem never had the constraints that justify the cost.

So the question this chapter answers is not “how do I use Postgres” (though you will learn that). The question is: what foundation can bear the weight this particular building will carry? A backyard shed and a cathedral both need a foundation. They do not need the same foundation. The engineer who pours a cathedral footing under a shed has not been careful. He has been wasteful, and waste is a form of carelessness.

PostgreSQL is a client/server database. That single architectural difference from SQLite — a separate server process that many clients connect to over a network — is the whole story of when you need it. Multiple machines writing at once. Connections from across a network. Concurrency the single-file model cannot give you. When your constraints include those words, the cathedral footing is exactly right, and pouring the shed slab would be the mistake instead.

And here is the through-line back to Phase 1, the one I want you to feel in your hands this week: a database index is a data structure. It is the B-tree from Week 6 or the hash table from Week 5 — the very structures you built by hand and measured — except the database builds it and maintains it for you. When you create an index, you are making the exact space-versus-time decision you made in Project 6 when you chose a tree over a hash. You are spending disk and write-time to buy read-speed. The fine print you read by hand in Phase 1 is the fine print the database is quietly managing now. You will read EXPLAIN, watch a query go from a full table scan to an index seek, and recognize an old friend.

The cornerstone passage frames it. A foundation gets laid once, and everything stands or falls on whether it can bear the load. You do not get to re-pour it cheaply later — migrating a live database is one of the harder things in this profession precisely because the foundation is load-bearing while you work on it. So you choose deliberately, up front, from the constraints. That is the architect’s job, and it is the one job the agent cannot do for you.

12.1 — SQLite’s Model vs the Client/Server Model

Start with a clear picture of what you had, because the contrast is the lesson.

SQLite is a library. When your Python program calls sqlite3.connect("verses.db"), no separate program starts. Your process opens a file on disk and reads and writes it directly, through code that is linked into your own program. There is no server. There is no port. There is no network. The “database” is a file, and the “database engine” is a function library running inside your process.

That model has enormous virtues. Zero configuration — there is nothing to install, start, or administer. One file you can copy, email, or check into a backup. No network latency, because there is no network. For an embedded app, a desktop tool, a phone, a single-process API serving modest traffic, an MVP you want running in ten minutes — SQLite is not the “lite” choice. It is the correct choice, and the most-deployed database in the world precisely because so many real problems live inside its constraints.

Now the cost. Because the database is a file edited by whoever opens it, concurrent writing is the hard limit. SQLite serializes writers: at the moment of a write, one connection holds the write lock and everyone else waits. For one process this is invisible. For many processes — say, several copies of your API behind a load balancer, all trying to write — it becomes a bottleneck, then an error (database is locked), then a production incident. That is the wall you measured at the end of Project 11.

PostgreSQL is a server. It is a separate, long-running program — a process (really a family of processes) that owns the data files and is the only thing that touches them. Your program never opens the data files. Instead it opens a connection over the network to the Postgres server and speaks a protocol: “here is some SQL, run it, send me the rows.” The server arbitrates everyone. Ten clients, a hundred clients, clients on other machines — they all talk to the one server, and the server is built from the ground up to handle their reads and writes happening at the same time without corrupting the data.

Coach’s Note — The phrase “client/server” is the entire chapter compressed into two words. Server: one program owns the data and runs forever. Client: many programs connect to it, from anywhere, at once. Every capability Postgres has over SQLite, and every cost, falls out of that one architectural fact. When you can explain a feature by pointing at “because there’s a server,” you understand it.

The network boundary is not free, and you should respect it. A SQLite query is a function call — nanoseconds of overhead. A Postgres query travels over a socket to another process (often another machine), gets parsed and planned and executed there, and the rows travel back. That round trip is measured in milliseconds even on the same machine, more across a network. You are buying concurrency and scale, and you are paying latency and complexity. That is a trade, and a trade only makes sense when you actually want what you are buying.

12.2 — Connecting to Postgres from Python with psycopg

You talk to Postgres from Python through a driver — a library that knows the Postgres wire protocol and presents it to you as Python. The modern one is psycopg (version 3; the older, still-everywhere version 2 is psycopg2). Install it with pip install "psycopg[binary]". The API follows Python’s standard DB-API, so it will feel familiar after SQLite.

Everything in this section runs against a real Postgres server. Appendix B walks you through installing Postgres locally and creating a database. Until you have that, read the code as a careful author would — every line below is correct and idiomatic — and run it once Appendix B is done.

First, what a connection needs. Where SQLite took a filename, Postgres takes the coordinates of a server:

You can pass these as a connection string (a single URL-shaped string) or as keyword arguments. Both are below.

# connect_demo.py — opening one connection to Postgres and running one query.
import os
import psycopg

# A connection string. Note the password comes from the environment, NOT
# the source code. Hard-coding a database password into a file you commit to
# git is one of the most common — and most expensive — security mistakes there
# is. Treat it like a key to the building, because it is.
CONNINFO = (
    f"host=localhost port=5432 dbname=verses "
    f"user=verses_app password={os.environ['PG_PASSWORD']}"
)

# The same thing as a URL, if you prefer (psycopg accepts either form):
#   CONNINFO = os.environ["DATABASE_URL"]
#   # e.g. "postgresql://verses_app:secret@localhost:5432/verses"

with psycopg.connect(CONNINFO) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT version();")
        (version,) = cur.fetchone()
        print(version)

A few things to read carefully here, because they are the same disciplines you learned with SQLite, in a new key:

• The with blocks matter. with psycopg.connect(...) as conn gives you a connection that is committed (if all went well) and closed when the block ends. The inner with conn.cursor() gives you a cursor that is closed at the end of its block. Letting these clean up automatically is how you avoid leaking connections — and a leaked connection in a client/server world is not a minor sloppiness; it is a finite resource you failed to return.
• A cursor is the thing you run SQL on. cur.execute(sql) sends the statement; cur.fetchone() / cur.fetchall() pull rows back. Same shape as SQLite.
• The password is read from os.environ. Burn this in now. Connection credentials live in environment variables (or a secrets manager), never in the file.

Now a real query, parameterized — exactly the SQL-injection discipline from Week 11, and it does not change one bit because the database changed:

# Find a verse by reference. The %s is a PLACEHOLDER, not string formatting.
# psycopg sends the SQL and the values SEPARATELY to the server, so a value
# can never be parsed as SQL. This is the ONLY safe way to put user input
# into a query. Never, ever build SQL with f-strings or + concatenation.
ref = "John 3:16"   # imagine this came from an untrusted HTTP request

with psycopg.connect(CONNINFO) as conn:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, reference, text FROM verses WHERE reference = %s;",
            (ref,),                      # a TUPLE of parameters, always
        )
        row = cur.fetchone()
        print(row)   # (12, 'John 3:16', 'For God so loved the world...')

Coach’s Note — Two driver gotchas that bite everyone once. First: psycopg’s placeholder is %s, not SQLite’s ?. It looks like printf/%-formatting but it is not — you pass values as the second argument, you never %-format them in yourself. Second: %s is the placeholder regardless of the column’s type — there is no %d for integers. If you write %d, you get an error. The placeholder marks “a value goes here”; the driver figures out the type.

12.3 — Connection Pooling: Why Opening a Connection Is Expensive

Here is a fact that does not exist in the SQLite world at all: opening a Postgres connection is expensive.

Think about what psycopg.connect() actually does. It opens a TCP socket to the server. It performs an authentication handshake. The server forks or assigns a backend process to serve this connection and allocates memory for it. All of that — the network round trips, the auth, the server-side setup — happens before your first query runs. On a local machine it might be a handful of milliseconds; across a network, more. It sounds small. It is not, when it happens on every single HTTP request your API serves.

Picture your memory-verse API under load. A hundred requests a second arrive. If each request opens a fresh connection, runs one fast query, and closes the connection, you are paying that whole connect-and-teardown cost a hundred times a second — and your actual query, the thing you care about, might be a tenth of that cost. You have spent most of your database budget on saying hello and goodbye.

The fix is a connection pool. A pool opens a set of connections once, at startup, and keeps them alive. When a request needs the database, it borrows a ready connection from the pool, uses it, and returns it — it does not close it. The expensive handshake happened once; every request after that reuses an already-open connection. The pool also caps the total number of connections, which protects the server: an unbounded API under a traffic spike could otherwise open thousands of connections and exhaust the server’s limited supply.

This is, if you squint, the exact same idea as the dynamic array’s pre-allocated buffer from Week 2: pay a setup cost once, amortize it across many cheap operations. Different layer, same accounting.

# pool_demo.py — a connection pool with psycopg_pool.
# pip install "psycopg[binary]" psycopg_pool
import os
from psycopg_pool import ConnectionPool

CONNINFO = (
    f"host=localhost port=5432 dbname=verses "
    f"user=verses_app password={os.environ['PG_PASSWORD']}"
)

# Create the pool ONCE, when the program starts. min_size connections are
# opened eagerly; the pool will grow to max_size under load and no further.
# Capping max_size is how you protect the server from being swamped.
pool = ConnectionPool(CONNINFO, min_size=2, max_size=10, open=True)

def get_verse(verse_id: int):
    # BORROW a connection from the pool. At the end of the `with`, it is
    # RETURNED to the pool — not closed. The next caller reuses it.
    with pool.connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT id, reference, text FROM verses WHERE id = %s;",
                (verse_id,),
            )
            return cur.fetchone()

# ... your API handlers call get_verse() on every request, and the
# connect cost is paid once, not per request.

# On clean shutdown of the whole application:
#   pool.close()

Coach’s Note — “Open a connection per request” is the single most common performance bug in junior database code, and it is invisible in testing — one developer, one request at a time, the connect cost is unnoticeable. It only shows up under real concurrent load, which is exactly the situation you moved to Postgres for. Pool from the start. The pool is not premature optimization here; it is the correct shape for a client/server database serving an API.

If you used FastAPI in Project 10, the natural home for the pool is a startup/shutdown lifespan: create it when the app boots, close it when the app stops, and hand out connections per request in between.

12.4 — Migrations: Schema Changes That Are Reproducible

In Week 11 you wrote a schema.sql and ran it once against a fresh file. That works the first time. But a real database is not created once and frozen — it evolves. Next month you add a created_at column. The month after, a new table. The month after that, a NOT NULL constraint on a column that used to allow nulls. Each of these is a schema change, and the question that separates an amateur from a professional is: how do you apply the same change, in the same order, to your laptop, your teammate’s laptop, the test server, and production — and know it was applied?

The amateur’s answer is ad-hoc: open a SQL prompt on production, type ALTER TABLE, hope you remember to do the same thing everywhere else. This is how databases drift. Your laptop’s schema and production’s schema diverge, silently, until a query that works on your machine fails in production because the column you added locally never made it there. This is the database equivalent of “works on my machine,” and it is just as bad.

The professional’s answer is migrations: schema changes captured as versioned, ordered, append-only files. Each migration is a small SQL script with a number. You apply them in order. You record which ones have been applied. The schema becomes reproducible — anyone can build the exact current schema by running every migration in order against an empty database, and everyone’s database is identical because they all ran the same scripts.

The simplest possible scheme — and the one Project 12 asks for — is plain numbered SQL files plus a tiny tracking table:

-- migrations/0001_initial_schema.sql
-- The first migration: the schema, ported from SQLite to Postgres.
-- See 0001_initial_schema.sql in this chapter's code/ for the full, runnable version.
BEGIN;

CREATE TABLE collections (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE verses (
    id            SERIAL PRIMARY KEY,
    reference     TEXT NOT NULL,
    text          TEXT NOT NULL,
    translation   TEXT NOT NULL DEFAULT 'ESV',
    collection_id INTEGER REFERENCES collections(id),
    UNIQUE (reference, translation)
);

-- Record that this migration ran, so we never run it twice.
INSERT INTO schema_migrations (version) VALUES (1);

COMMIT;

-- migrations/0002_add_created_at.sql
-- A LATER change: every migration is append-only. You never edit 0001 after
-- it has run somewhere. You add 0002. The history is the truth.
BEGIN;
ALTER TABLE verses ADD COLUMN created_at TIMESTAMPTZ NOT NULL DEFAULT now();
INSERT INTO schema_migrations (version) VALUES (2);
COMMIT;

The tracking table is just:

CREATE TABLE IF NOT EXISTS schema_migrations (
    version    INTEGER PRIMARY KEY,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

A migration runner reads which versions are already in schema_migrations, then runs every numbered file higher than the max, in order, each wrapped in its own transaction. (Real teams use a tool — alembic for Python, or Flyway, or sqitch — but the tool is doing exactly this. Build the small version once, by hand, so you know what the tool does.)

Coach’s Note — Notice the Postgres-isms in the migration above. SERIAL is Postgres’s auto-incrementing integer (SQLite used INTEGER PRIMARY KEY for the same job). TIMESTAMPTZ is a timestamp with time zone — store time in UTC, always. And the whole migration is wrapped in BEGIN ... COMMIT so it is atomic: either the table is created and the version is recorded, or neither happens. A half-applied migration is a foundation poured halfway. The transaction prevents it.

Coach’s Note — The migration files are append-only for the same reason a ledger is append-only and the church’s witness is handed down rather than rewritten: the history is the record, and a record you can quietly edit is no record at all. Once 0001 has run on a real server, you do not change it — you write 0003. This is stewardship of the schema (Week 11’s word) extended through time.

12.5 — Indexes as a Space/Time Tradeoff (the Big Callback)

This is the section I most want you to feel, because it is Phase 1 and Phase 2 shaking hands.

In Week 6 you built a binary search tree and learned what it bought you: ordered data, O(log n) lookup, range queries. In Week 5 you built a hash table and learned what it bought you: O(1) average membership, no ordering. In Project 6 you wrote a memo choosing between them for a given workload, and the choice was a space/time tradeoff — you spent memory to buy speed, and you chose the structure whose strengths matched the queries you actually ran.

A database index is exactly that choice, made one layer down, with the database doing the building.

When a table has no index on the column you are filtering by, the database has no choice but to read every row and check each one. That is a sequential scan (Postgres calls it Seq Scan), and it is O(n) — the table-scan you’d recognize from a for loop over an unsorted array. For ten rows, fine. For ten million rows on a query you run a thousand times a second, a catastrophe.

When you create an index on that column, the database builds and maintains a B-tree (the default; Postgres also offers hash and other types) keyed on that column’s values. Now a lookup walks the tree in O(log n) instead of scanning in O(n) — the identical improvement you measured by hand in Week 6, on the identical structure.

And the cost is the identical cost: the index takes space (it is a whole second data structure stored on disk, duplicating the key column), and it slows writes (every INSERT, UPDATE, or DELETE must now update the table and every index on it, keeping the B-tree balanced — the maintenance you did by hand in Week 6, the database now does on every write). You are spending disk and write-time to buy read-speed. That is the trade. It was the trade in Project 6. It is the trade here.

You read the truth of it with EXPLAIN, which asks Postgres to show its plan without running it (EXPLAIN ANALYZE runs it and reports real timings).

-- Before any index, on a verses table with a few hundred thousand rows:
EXPLAIN ANALYZE
SELECT id, text FROM verses WHERE reference = 'John 3:16';

Seq Scan on verses  (cost=0.00..4621.00 rows=1 width=58)
                    (actual time=0.041..38.902 rows=1 loops=1)
  Filter: (reference = 'John 3:16'::text)
  Rows Removed by Filter: 299999
Planning Time: 0.094 ms
Execution Time: 38.961 ms

Read that output like the comprehension brief from Coding 2. Seq Scan — it read the whole table. Rows Removed by Filter: 299999 — it looked at three hundred thousand rows to find the one you wanted. Execution Time: 38.961 ms — and it took a while. This is O(n) caught in the act.

Now create the index and ask again:

CREATE INDEX idx_verses_reference ON verses (reference);

EXPLAIN ANALYZE
SELECT id, text FROM verses WHERE reference = 'John 3:16';

Index Scan using idx_verses_reference on verses
                    (cost=0.42..8.44 rows=1 width=58)
                    (actual time=0.038..0.041 rows=1 loops=1)
  Index Cond: (reference = 'John 3:16'::text)
Planning Time: 0.121 ms
Execution Time: 0.063 ms

Index Scan — it walked the B-tree. No “Rows Removed,” because it went straight to the row instead of filtering the whole table. Execution Time: 0.063 ms — roughly 600× faster on this data. That is O(log n) versus O(n) made visible, in milliseconds, on a real table.

Coach’s Note — Here is the part the autocomplete-brained engineer gets wrong: more indexes is not better. Indexing every column “to be safe” floods your disk and makes every write slow, to speed up reads you never run. An index you don’t query is pure cost — space and write-time spent buying nothing. You index the columns you actually filter and join on, the hot ones, and you leave the rest unindexed on purpose. That is the same discipline as choosing one structure in Project 6 instead of hedging with all of them. The architect indexes deliberately.

So the table-scan-vs-index decision is the tree-vs-hash decision wearing a database’s clothes. When you decide whether a column deserves an index, run the Project 6 reasoning: what queries do I actually run against this column, how big is the table, and is the read-speed worth the space and the write-slowdown? Same question. New layer.

12.6 — Transactions and ACID

A connection to Postgres gives you something SQLite has too, but that matters far more once many clients are writing at once: transactions.

A transaction is a group of statements that the database treats as a single, indivisible unit. Either all of them take effect, or none of them do. You open one with BEGIN, you make it permanent with COMMIT, and you throw it all away with ROLLBACK. (psycopg manages this for you: by default each with conn: block is one transaction that commits on success and rolls back on exception.)

The reason transactions exist is the classic example: move a verse from one collection to another. That is two writes — remove it from collection A, add it to collection B. Imagine the server crashes, or the network drops, or another error fires between those two writes. Without a transaction, the verse is now in neither collection (or both). The data is corrupt — it represents a state that should never exist. With a transaction, the two writes are one unit: the crash rolls the whole thing back, and the verse stays safely in collection A as if nothing happened. You try again. No corruption.

# transaction_demo.py — a two-step operation made atomic.
import os
import psycopg

CONNINFO = (
    f"host=localhost port=5432 dbname=verses "
    f"user=verses_app password={os.environ['PG_PASSWORD']}"
)

def move_verse(verse_id: int, from_collection: int, to_collection: int):
    with psycopg.connect(CONNINFO) as conn:
        # This `with conn:` block IS the transaction. Everything inside it
        # either all commits at the end, or — if any line raises — all rolls
        # back. There is no halfway state visible to anyone else, ever.
        with conn.cursor() as cur:
            # Step 1: remove from the old collection.
            cur.execute(
                "UPDATE verses SET collection_id = NULL "
                "WHERE id = %s AND collection_id = %s;",
                (verse_id, from_collection),
            )
            if cur.rowcount != 1:
                # The verse wasn't where we expected. Bail out — and because
                # we raise inside the transaction, Step 1 is rolled back too.
                raise ValueError(f"verse {verse_id} not in collection {from_collection}")

            # Step 2: add to the new collection.
            cur.execute(
                "UPDATE verses SET collection_id = %s WHERE id = %s;",
                (to_collection, verse_id),
            )
        # Reaching here with no exception -> COMMIT. Both updates are now
        # permanent, together. A crash before this point -> ROLLBACK, and
        # it is as if move_verse was never called.

The four guarantees a transactional database makes go by the acronym ACID:

Isolation is the letter that earns its keep specifically in the client/server world, and it is worth dwelling on. With many clients writing at once, isolation is the promise that each transaction runs as if it had the database to itself — it never sees another transaction’s uncommitted, half-finished work. Postgres delivers this with MVCC (Multi-Version Concurrency Control): readers see a consistent snapshot and never block writers, writers never block readers. This is a deep reason Postgres handles concurrent load gracefully where SQLite’s single write-lock cannot. It is, again, the client/server fact paying off: the server is the one place that can coordinate everyone, so it can offer guarantees no single-file library can.

Coach’s Note — “Make it atomic” is a phrase you will use for the rest of your career, and it always means the same thing: this group of steps must be all-or-nothing, because a halfway state is a corrupt state. The instant you see a multi-step operation where the in-between state would be wrong — money moved out of one account but not yet into the other, a verse in neither collection — you reach for a transaction. Recognizing that situation is an architect’s reflex. Project 12’s Hard tier makes you build it and break it on purpose.

12.7 — The Decision: SQLite or Postgres

Now put the whole chapter to work, because the deliverable that matters most this week is not the migration — it is the judgment about whether to migrate.

You have two real databases in your hands and a clear-eyed view of what each costs. The decision between them is driven, as always in this book, by the constraints of the actual problem. Run the table:

The rule is simple to state and requires discipline to follow: start with SQLite, and move to Postgres when — and only when — a specific constraint forces you to. Concurrent writers from multiple processes is the classic forcing constraint; you measured exactly that wall at the end of Project 11. Needing clients on other machines is another. Needing the things only a server can give — robust concurrency, a sophisticated query planner, fine-grained access control — is another.

And the rule cuts both ways, which is the part that takes maturity:

• Building an MVP on Postgres “because we’ll need it eventually” is a mistake. You pay full freight today — a server to run and secure, a connection pool, more moving parts, slower local setup for every teammate — for a scale you do not yet have and may never reach. You can migrate later (it is real work, but it is known work, and this chapter just taught you how). The cost of starting simple is small and recoverable. The cost of premature complexity is paid every single day until you remove it.
• Shipping a multi-machine, concurrent-write production system on SQLite “because it was already there” is the other mistake. You will hit the write-lock wall under real load, and you will hit it at the worst possible time — in production, under traffic, which is the situation you least want to be re-pouring a foundation in.

Coach’s Note — “We might need it later” is the most expensive sentence in software architecture. It justifies every premature complexity ever shipped. The architect’s answer is: then we’ll add it later, and here is roughly what that will cost and what signal will tell us it’s time. You earn the right to that answer by knowing how the migration actually works — which is why this chapter taught you to do it before it asked you to decide whether to. Knowing how is what makes “later” a real plan instead of a fear.

The cornerstone passage is exactly this. A foundation gets laid once and bears the weight of everything above it. You do not get to be careless about it, in either direction — neither over-building it for a load that will never come, nor under-building it for the load you know is coming. You count the cost (Week 1’s verse) and you build to the actual weight. That is the whole job.

12.8 — Common Bugs

Bug: Building SQL with an f-string or + because the placeholder syntax changed and you got impatient. Example: cur.execute(f"SELECT * FROM verses WHERE reference = '{ref}'") — and now a ref of '; DROP TABLE verses; -- is a catastrophe. Fix: Always parameterize: cur.execute("... WHERE reference = %s", (ref,)). The driver sends SQL and values separately so a value can never become SQL. This rule did not change from SQLite; only the placeholder did (? → %s).

Bug: Using %d or %f as a placeholder, copying habits from printf. Example: cur.execute("... WHERE id = %d", (vid,)) raises an error. Fix: psycopg uses %s for every type. The driver infers the type from the value you pass. There is no %d.

Bug: Opening a fresh connection on every request instead of using a pool. Example: with psycopg.connect(CONNINFO) as conn: inside an HTTP handler called hundreds of times a second. Fix: Create one ConnectionPool at startup and with pool.connection() as conn: per request. The handshake cost is paid once, not per request. This bug is invisible in single-user testing and brutal under load.

Bug: Forgetting to commit, then wondering why the data vanished. Example: Running an INSERT with psycopg.connect() but never exiting the with conn: block cleanly (e.g., you held the connection open in a REPL). Fix: Let the with conn: block close — it commits on clean exit, rolls back on exception. If you manage transactions manually, call conn.commit() explicitly. An uncommitted write is invisible to everyone else and disappears on rollback.

Bug: Indexing everything “to be safe,” then watching writes crawl. Example: Adding an index to every column of a write-heavy table. Fix: Index the columns you actually filter and join on. Each index costs disk and slows every write (it must be kept up to date). Use EXPLAIN ANALYZE to confirm an index is actually used before you keep it. An unused index is pure cost.

Bug: Hard-coding the database password into the source file. Example: password='hunter2' committed to a public GitHub repo. Fix: Read credentials from os.environ (or a secrets manager). Never commit them. A password in git history is a password you must now rotate everywhere, even after you delete the line — git remembers.

12.9 — Reps

Open the exercises for the full set. This week the reps run against a real local Postgres (Appendix B), and — Phase 2 — agentic AI is on, but the reps are still hand-built so the SQL and the driver code go through your fingers before you delegate anything in the project.

A preview:

• Rep 1 — Connect to Postgres and run SELECT version();.
• Rep 3 — Port the P11 schema to Postgres SQL (SERIAL, TIMESTAMPTZ) and run it as a migration.
• Rep 6 — Load enough rows to matter, then EXPLAIN ANALYZE a query before and after an index. Record both plans.
• Rep 9 — Write a two-step transaction, then force a failure mid-transaction and prove the rollback.
• Rep 11 — Fill in a SQLite-vs-Postgres decision table for three given scenarios.

Do every one. The reps put the SQL and the driver in your hands; the project puts the judgment in your hands.

12.10 — This Week’s Project

You’re ready for Project 12 — Migrate to Postgres, Justify It, in Project 12.

You will take your Project 11 SQLite database and migrate it to PostgreSQL: a reproducible migration script, a connection pool, your CRUD re-run against the new foundation. The Medium tier makes you add an index to a hot column and measure the query before and after on enough rows to matter — the Week 6 tradeoff, live. The Hard tier makes you build a transaction, break it on purpose to demonstrate a clean rollback, and write the decision memo: for three concrete scenarios, SQLite or Postgres, with the specific constraint that decides each.

This is a Phase 2 project, so agentic AI is on and an agent-log.txt is required. But read the thesis again before you start: the agent can write the migration SQL and the pool boilerplate. The agent cannot decide whether the migration is the right call. The migrate-or-don’t judgment is yours, every time. That decision — and the memo defending it — is the soul of the assignment, and the one part an agent cannot finish for you.

12.11 — Coach’s Final Word for Week 12

Last week you taught your program to remember. This week you learned what it costs to remember at scale, and — harder — when that cost is worth paying.

The temptation all week was to treat Postgres as an upgrade, a strictly-better SQLite you graduate to. It is not. It is a different tool for a different constraint, more powerful and more expensive, and the engineer who reaches for it reflexively has made the same mistake as the one who refuses to reach for it when the load is real. Both failed to read the constraints. Both poured the wrong foundation.

You also met an old friend wearing new clothes. The index is the tree from Week 6, the space/time tradeoff from Project 6, built and maintained by the database instead of by your hands. When you ran EXPLAIN ANALYZE and watched a query go from a 39-millisecond scan to a 0.06-millisecond seek, you were watching O(n) become O(log n) on a real table — the exact thing you measured by hand in Phase 1, paying off in Phase 2. That is the whole architecture of this book in one query. The cost intuition you built is the thing that makes you good now.

If you find the decision harder than the code: that’s the gap, and it’s the right gap. The code is learnable in an afternoon. The judgment about which foundation bears this building’s weight is the work of a career. Close it deliberately.

See you on Monday.

Up next: Read the exercises and complete every rep against your local Postgres. Then open Project 12 and migrate — and justify it. After that, Chapter 13 — when the right shape isn’t a table at all, but a document.