Chapter 12 · Reps

Persistence II — PostgreSQL and Scaling Up — Reps

← Back to Chapter 12

Chapter 12 — Reps

Conditioning, not grading. PostgreSQL + psycopg reps this week.

Ground rules:

  1. Type every line yourself. No copy-paste of SQL or driver code. The placeholder is %s; you will get it wrong from muscle memory the first few times. That’s the point — burn in the new reflex by hand.
  2. Run everything against a real local Postgres. Appendix B walks you through installing PostgreSQL, creating the verses database and a user, and connecting. Do that setup before Rep 1. These reps assume psql works and pip install "psycopg[binary]" psycopg_pool succeeded.
  3. AI/agents are ON — but the reps are hand-built. Phase 2. You may use an agent in Project 12, but every rep here goes through your own fingers first. You cannot direct an agent to write SQL well if you have never felt the SQL yourself. Write the reps cold; save the agent for the project.
  4. Parameterize every query that takes a value. %s and a tuple, every time. No f-strings in SQL. Ever. Make it automatic now.

Throughout, you are working with the memory-verse schema from Project 11 (verses and collections). You are porting it to Postgres and building on it.

Reps 1–3: Connect and Migrate

Rep 1 — Hello, Postgres

With your local server running, connect from Python and prove it:

import os, psycopg

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

with psycopg.connect(conninfo) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT version();")
        print(cur.fetchone()[0])

Set PG_PASSWORD in your shell first (export PG_PASSWORD=...). Run it. You should see a line like PostgreSQL 16.2 on .... If you get a connection error, the problem is your setup (Appendix B), not your code — fix the setup before continuing.

Then deliberately break it: change port=5432 to port=5433. Run again. Read the error carefully — “connection refused” tells you nobody is listening on that port. That is the client/server boundary announcing itself. Restore.

Rep 2 — A Connection String Two Ways

The same connection, expressed both ways psycopg accepts. Write a script that connects using each form and runs SELECT 1; to confirm:

  1. Keyword-style: psycopg.connect("host=localhost port=5432 dbname=verses user=verses_app password=...").
  2. URL-style: psycopg.connect("postgresql://verses_app:PASSWORD@localhost:5432/verses").

Confirm both work. Note in a comment: in real code, neither string is hard-coded — the password comes from os.environ. Prove you understand by reading it from the environment in both.

Rep 3 — Port the Schema as a Migration

Take the Project 11 SQLite schema.sql and write it as a Postgres migration file, 0001_initial_schema.sql. Make the three Postgres-specific changes:

  • INTEGER PRIMARY KEY (SQLite’s auto-rowid) becomes SERIAL PRIMARY KEY.
  • Keep TEXT, NOT NULL, DEFAULT 'ESV', REFERENCES collections(id), and the UNIQUE (reference, translation) constraint — those port unchanged.
  • Wrap the whole thing in BEGIN; ... COMMIT; and end with INSERT INTO schema_migrations (version) VALUES (1); (create the schema_migrations table first if it doesn’t exist).

Run it with psql -d verses -f 0001_initial_schema.sql. Then run \d verses in psql and confirm the table shape. Then run the migration again and watch it fail on the duplicate version — that failure is the migration system protecting you from running the same change twice.

Reps 4–5: CRUD Against Postgres

Rep 4 — Insert and Read, Parameterized

Write functions add_verse(reference, text) and get_verse_by_reference(reference). Both must parameterize with %s:

def add_verse(conn, reference, text):
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO verses (reference, text) VALUES (%s, %s) RETURNING id;",
            (reference, text),
        )
        return cur.fetchone()[0]   # RETURNING gives you the new SERIAL id

Insert three real ESV verses (e.g., John 3:16, Romans 6:23, Psalm 23:1). Read each back by reference. Note the RETURNING id clause — Postgres hands you the auto-generated id in the same round trip. Confirm the data survives by reconnecting in a fresh script and reading it again.

Rep 5 — Update, Delete, and rowcount

Add update_verse_text(conn, verse_id, new_text) and delete_verse(conn, verse_id). After each execute, check cur.rowcount:

  • An update that matched a row returns rowcount == 1.
  • An update for a nonexistent id returns rowcount == 0.
  • A delete of a real id returns rowcount == 1.

Write a tiny check that prints “updated” vs “no such verse” based on rowcount. This is how the API layer (your Project 10/11 code) knows whether to return 200 or 404 — the database tells you how many rows it touched.

Reps 6–7: Indexes and EXPLAIN (the Week 6 Callback)

Rep 6 — Make a Table Big Enough to Matter

An index makes no measurable difference on ten rows. You need scale before the tradeoff is visible. Generate it with Postgres’s generate_series:

INSERT INTO verses (reference, text)
SELECT
    'Gen ' || g || ':1',
    'Generated verse number ' || g
FROM generate_series(1, 300000) AS g;

Confirm the count: SELECT count(*) FROM verses; should report 300,000-plus rows. Now you have a table where the structure matters — the same realization you had in Project 6 when small inputs hid the difference between a tree and a list.

Rep 7 — EXPLAIN Before, Index, EXPLAIN After

On the big table from Rep 6, run:

EXPLAIN ANALYZE SELECT id, text FROM verses WHERE reference = 'Gen 250000:1';

Record the plan. You should see Seq Scan and a large Rows Removed by Filter and an Execution Time in the tens of milliseconds. Now build the index and ask again:

CREATE INDEX idx_verses_reference ON verses (reference);
EXPLAIN ANALYZE SELECT id, text FROM verses WHERE reference = 'Gen 250000:1';

Record the second plan. You should now see Index Scan and an Execution Time in fractions of a millisecond.

Write down, in two sentences: (1) the before-and-after execution times, and (2) what changed in the plan (Seq Scan → Index Scan, no more “Rows Removed”). This is O(n) → O(log n), measured. Keep these numbers; Project 12’s Medium tier asks for exactly this.

Reps 8–9: Transactions

Rep 8 — A Transaction That Commits

Write a function that, in one transaction, creates a collection and assigns two verses to it:

def create_collection_with_verses(conn, name, verse_ids):
    with conn:                          # the transaction
        with conn.cursor() as cur:
            cur.execute(
                "INSERT INTO collections (name) VALUES (%s) RETURNING id;",
                (name,),
            )
            cid = cur.fetchone()[0]
            for vid in verse_ids:
                cur.execute(
                    "UPDATE verses SET collection_id = %s WHERE id = %s;",
                    (cid, vid),
                )
    # Exiting the `with conn:` block with no exception commits all of it.

Run it. Confirm with a query that the collection exists and both verses point at it. All three writes (one insert, two updates) committed together.

Rep 9 — Force a Rollback Mid-Transaction

Now prove atomicity by breaking it on purpose. Copy Rep 8’s function and inject a failure after the first verse update but before the second:

def create_collection_with_verses_BROKEN(conn, name, verse_ids):
    with conn:
        with conn.cursor() as cur:
            cur.execute("INSERT INTO collections (name) VALUES (%s) RETURNING id;", (name,))
            cid = cur.fetchone()[0]
            cur.execute("UPDATE verses SET collection_id = %s WHERE id = %s;", (cid, verse_ids[0]))
            raise RuntimeError("simulated crash mid-transaction")  # boom
            cur.execute("UPDATE verses SET collection_id = %s WHERE id = %s;", (cid, verse_ids[1]))

Call it inside a try/except so the error doesn’t kill your script. Then query the database: the collection should not exist, and neither verse should point at it. The INSERT and the first UPDATE were rolled back when the exception escaped the with conn: block. Nothing half-happened.

This is atomicity you can see. Write one sentence: what state would the database be in right now if there had been no transaction?

Reps 10–11: Pooling and the Decision

Rep 10 — A Connection Pool

Replace per-call connections with a pool:

from psycopg_pool import ConnectionPool
pool = ConnectionPool(conninfo, min_size=2, max_size=10, open=True)

def get_verse(verse_id):
    with pool.connection() as conn:        # BORROW, don't open
        with conn.cursor() as cur:
            cur.execute("SELECT reference, text FROM verses WHERE id = %s;", (verse_id,))
            return cur.fetchone()

Call get_verse in a loop a few hundred times. It should be noticeably faster than a version that calls psycopg.connect() each iteration — write both, time both with time.perf_counter(), and record the difference. The gap is the connection-handshake cost you amortized away. Call pool.close() at the end.

Rep 11 — The Decision Table

No code. For each scenario below, decide SQLite or Postgres and name the single constraint that decides it:

  1. A command-line tool that indexes your personal sermon notes on your laptop. One user, one machine.
  2. A church directory web app where ten staff members add and edit member records concurrently all day, from different offices.
  3. A read-only Bible-verse lookup embedded in a mobile app, shipped on the device, never updated after install.
  4. A prototype of a small-group scheduler you’re building this weekend to show your pastor on Monday, expecting maybe five test users.

Fill in a three-column table: scenario, choice, the constraint. For at least one, note the choice that would be a mistake and why (e.g., “Postgres here would be premature complexity”). This is the exact reasoning Project 12’s Hard-tier memo wants.

Done? One Last Thing.

From scratch, no looking — write a single Python script that:

  1. Connects to your verses database (password from os.environ).
  2. In one transaction, inserts a new collection named “Comfort” and inserts two new verses already assigned to it (use RETURNING id to get the collection id, then insert the verses with that collection_id).
  3. Commits, then queries and prints the collection and its two verses.
  4. Then runs EXPLAIN ANALYZE on a lookup of one of those verses by reference and prints the plan.

If you can write that cold — connect, parameterize, transact, commit, and explain — you have the week’s muscles in your hands. The judgment about whether to be on Postgres at all is what the project will test next.

Up next: Project 12 — Project 12: Migrate to Postgres, Justify It.