7.2.1 Three things files don’t give you🔗

Every TerpTasks feature so far has lived in a Python dict or a JSON file next to the code. That collapses the moment three things happen at once: concurrent access (two requests write at the same time—who wins?), crash safety (the process dies mid-write—is the file now half-written garbage?), and querying (“which users have more than five open documents?”—in a flat file, that’s a program you write and re-write every time the question changes).

A database is a program whose entire job is answering those three problems: keep data correct and durable while many clients read and write it concurrently, and let you ask new questions of old data without writing a new program each time. A DBMS (database management system) is that program; a relational DBMS (RDBMS) is one built on the relational model—data as tables connected by shared key values, queried with a declarative language (SQL) instead of a loop you hand-write. PostgreSQL is the RDBMS this course uses: open source, standards-following, and aggressively protective of your data’s integrity—the property both lectures below care most about.

7.2.2 The stack, and where these two lectures fit🔗

Lecture 1 is engine-agnostic: tables, keys, constraints, and SQL are the same idea on PostgreSQL, MySQL, or SQLite. Lecture 2 is where PostgreSQL specifically earns its keep—and where the schema becomes a real, running, concurrently-accessed system.

7.2.3 Running example: TerpTasks gets a document library🔗

TerpTasks is adding a feature that will matter again the moment this course reaches retrieval—students can upload reference material (a syllabus, lecture notes, a reading) and later ask questions about it. Before any of that is possible, the material has to be stored, split into retrievable pieces, and organized:

• Users upload documents. → a table, one-to-many

• Each document belongs to exactly one user, and is split into ordered chunks for later retrieval. → one-to-many, twice

• Documents can carry tags (“syllabus”, “midterm”); a tag applies to many documents. → many-to-many, junction table

• A document starts unprocessed and becomes processed once it has been chunked. → NULL as “not yet known”

This is the schema both lectures build, piece by piece, and the one your later RAG work will literally reuse—chunks is exactly the table an embedding pipeline chunks into.

7.3.1 The Relational Model🔗

Formal definition. A table is a named set of rows; every row has the same columns, and every column has a data type the database enforces on write. A schema (the design sense, not PostgreSQL’s namespace sense—more on that in Lecture 2) is the complete set of tables, columns, types, and constraints that together describe what your data is allowed to look like.

In plain English: a table is a spreadsheet the database refuses to let you fill in wrong. A spreadsheet suggests; a database, given the right schema, enforces—the write is rejected before it happens. Schema design is deciding, up front, which wrong states can never exist.

Choosing types. The PostgreSQL types you’ll reach for constantly in Lecture 1 (Lecture 2 adds the PostgreSQL-specific ones):

Misconception. “I’ll just make everything TEXT and sort it out in the app.” A column’s type is the first and cheapest integrity check you get: chunk_index typed as INTEGER makes ’first’ unrepresentable before any application code runs. Push checks as early as the type system allows.

-- Looks reasonable. Returns zero rows, always, on every database.
SELECT * FROM documents WHERE processed_at = NULL;

-- Correct: NULL is a state you test for, not a value you compare to.
SELECT * FROM documents WHERE processed_at IS NULL;

CREATE TABLE users (
    id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  TEXT NOT NULL UNIQUE,     -- natural uniqueness: constraint, not PK
    name   TEXT NOT NULL
);

CREATE TABLE documents (
    id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title        TEXT NOT NULL CHECK (length(title) > 0),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    processed_at TIMESTAMPTZ                          -- NULL until chunked
);

7.3.2 Schema Design in Practice🔗

CREATE TABLE chunks (
    id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    document_id  INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    chunk_index  INTEGER NOT NULL,
    content      TEXT NOT NULL CHECK (length(content) > 0),
    UNIQUE (document_id, chunk_index)    -- one chunk 0, one chunk 1, ... per document
);

CREATE TABLE tags (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE
);

CREATE TABLE document_tags (
    document_id  INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    tag_id       INTEGER NOT NULL REFERENCES tags(id)      ON DELETE CASCADE,
    PRIMARY KEY (document_id, tag_id)      -- composite PK: each pairing once
);

7.3.3 Defining Tables: DDL🔗

Formal definition. DDL (data definition language) is the subset of SQL that creates and changes the shape of the database—tables, columns, constraints—as opposed to DML (data manipulation language, next section), which reads and writes the rows inside that shape.

The full running schema, DDL only:

CREATE TABLE users (
    id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  TEXT NOT NULL UNIQUE,
    name   TEXT NOT NULL
);

CREATE TABLE documents (
    id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title        TEXT NOT NULL CHECK (length(title) > 0),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    processed_at TIMESTAMPTZ
);

CREATE TABLE chunks (
    id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    document_id  INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    chunk_index  INTEGER NOT NULL,
    content      TEXT NOT NULL CHECK (length(content) > 0),
    UNIQUE (document_id, chunk_index)
);

CREATE TABLE tags (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE
);

CREATE TABLE document_tags (
    document_id  INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    tag_id       INTEGER NOT NULL REFERENCES tags(id)      ON DELETE CASCADE,
    PRIMARY KEY (document_id, tag_id)
);

GENERATED ALWAYS AS IDENTITY is PostgreSQL’s modern auto-incrementing primary key: every row gets the next integer automatically, and (unlike the older SERIAL) you cannot accidentally INSERT an explicit value into it and desynchronize the counter.

Schemas change after they ship, which is exactly what ALTER and DROP are for:

ALTER TABLE documents ADD COLUMN page_count INTEGER;   -- new, nullable column
ALTER TABLE documents DROP COLUMN page_count;           -- reverse it

DROP TABLE document_tags;   -- careful: gone, with the table's data

Misconception. “DROP TABLE and recreate whenever the schema needs to change.” Fine on a laptop with no real data; the instant a table holds rows someone cares about, dropping it destroys them. Evolving a live schema safely is a big enough problem to get its own subsection in Lecture 2 (Operating a Real Database)—the short version now: ALTER, don’t DROP and recreate.

-- INSERT: one row, or many in one statement
INSERT INTO users (email, name) VALUES ('alice@umd.edu', 'Alice Wu');
INSERT INTO users (email, name) VALUES
    ('bob@umd.edu', 'Bob Lee'),
    ('carol@umd.edu', 'Carol Diaz');

-- UPDATE and DELETE: the WHERE clause is not optional
UPDATE documents SET processed_at = now() WHERE id = 1;
DELETE FROM documents WHERE id = 1;

SELECT title, created_at
FROM documents
WHERE owner_id = 1 AND processed_at IS NOT NULL
ORDER BY created_at DESC
LIMIT 5 OFFSET 0;

SELECT DISTINCT owner_id FROM documents;              -- unique values only

-- comparison / logical operators, IN, BETWEEN, LIKE, IS NULL
SELECT * FROM documents WHERE owner_id IN (1, 2);
SELECT * FROM chunks    WHERE chunk_index BETWEEN 0 AND 3;
SELECT * FROM documents WHERE title LIKE 'Lecture%';   -- % = any characters
SELECT * FROM documents WHERE processed_at IS NULL;    -- the NULL trap, correctly

-- aliases and expressions
SELECT title AS document_title, length(title) AS title_len
FROM documents;

-- CASE: a conditional expression, usable anywhere a value is usable
SELECT title,
       CASE WHEN processed_at IS NULL THEN 'pending'
            ELSE 'processed' END AS status
FROM documents;

7.3.4 Joins and Aggregation🔗

Why joins exist. Normalization split one fat table into five narrow ones specifically so each fact lives once. A join is the payoff: it stitches those tables back together for one query, without ever storing the redundant copy. You get the integrity benefits of normalization and the convenience of the flat view, on demand.

Formal definition. INNER JOIN returns rows that have a match in both tables; unmatched rows on either side are dropped. LEFT JOIN returns every row from the left table, with NULLs filled in where the right table has no match—these two cover the overwhelming majority of real queries. (RIGHT JOIN is LEFT JOIN with the tables swapped; FULL JOIN keeps unmatched rows from both sides; you’ll recognize them when you need them.)

-- INNER JOIN: only documents that have an owner (all of them, by our FK,
-- but the FK is exactly why this is always safe)
SELECT documents.title, users.name AS owner
FROM documents
INNER JOIN users ON documents.owner_id = users.id;

-- LEFT JOIN: every document, even ones with zero chunks so far
SELECT documents.title, chunks.chunk_index
FROM documents
LEFT JOIN chunks ON chunks.document_id = documents.id
ORDER BY documents.title, chunks.chunk_index;

Worked example—why the join direction matters. Document 3 ("Notes") has zero chunks yet. With INNER JOIN, document 3 disappears from the result entirely—there’s no chunk row to match. With LEFT JOIN, document 3 appears once, with chunk_index = NULL. “Show me every document and how many chunks each has, including zero” is the textbook LEFT JOIN signal: keep the left side no matter what.

Joining three tables—documents, tags, and the junction between them:

SELECT documents.title, tags.name AS tag
FROM documents
JOIN document_tags ON document_tags.document_id = documents.id
JOIN tags           ON tags.id = document_tags.tag_id
ORDER BY documents.title;

Aggregation: COUNT, SUM, AVG, MIN, MAX collapse many rows into one number, and GROUP BY does that per group instead of over the whole table.

-- chunk count and average chunk length, per document
SELECT documents.title,
       COUNT(chunks.id)               AS chunk_count,
       AVG(length(chunks.content))    AS avg_chunk_len
FROM documents
LEFT JOIN chunks ON chunks.document_id = documents.id
GROUP BY documents.id, documents.title;

-- HAVING filters *groups*, after aggregation (WHERE filters rows, before)
SELECT owner_id, COUNT(*) AS doc_count
FROM documents
GROUP BY owner_id
HAVING COUNT(*) > 1;

-- CASE inside an aggregate: a conditional count, per user
SELECT owner_id,
       COUNT(*)                                            AS total_docs,
       COUNT(*) FILTER (WHERE processed_at IS NOT NULL)    AS processed_docs
FROM documents
GROUP BY owner_id;

Misconception. “WHERE COUNT(*) > 1 should work—it’s just a condition.” WHERE runs before grouping even happens, so it has no aggregate value yet to compare; HAVING runs after, once each group’s COUNT exists. The rule: filter rows with WHERE, filter groups with HAVING.

Subqueries and a first CTE. A query can nest inside another query’s WHERE, or be named up front with WITH (a common table expression) and reused as if it were a table:

-- subquery: users who own at least one unprocessed document
SELECT name FROM users
WHERE id IN (SELECT owner_id FROM documents WHERE processed_at IS NULL);

-- the same idea as a CTE: named, and easier to read as the query grows
WITH unprocessed AS (
    SELECT owner_id, COUNT(*) AS pending_count
    FROM documents
    WHERE processed_at IS NULL
    GROUP BY owner_id
)
SELECT users.name, unprocessed.pending_count
FROM unprocessed
JOIN users ON users.id = unprocessed.owner_id
WHERE unprocessed.pending_count > 0;

A CTE and its equivalent subquery return identical results here; the CTE just gives the intermediate result a name, which pays off the moment a query needs that intermediate result more than once, or needs to be read by someone other than the person who wrote it.

Checkpoint—by the end of Lecture 1 you can: sketch an ER diagram for a small domain, create it as a constrained schema, load and edit data safely, and answer a real multi-table question with a join and an aggregate. Lecture 2 takes this exact schema and makes it PostgreSQL: running, fast, safe from concurrent writers, and reachable from Python.

7.4.1 Getting PostgreSQL Running🔗

Formal definition. A running PostgreSQL server (one process, one port) hosts one or more databases; each database has one or more schemas (a namespace for grouping tables—unrelated to the design sense of “schema” from Lecture 1; every database starts with one schema, public); each schema holds tables. So: server → database → schema → table—four nested containers, and 95% of a course project never leaves the default public schema of one database.

Two ways to get that server: install PostgreSQL natively, or run it in Docker without installing anything on the host:

docker run --name terptasks-db \
    -e POSTGRES_PASSWORD=devpassword \
    -e POSTGRES_DB=terptasks \
    -p 5432:5432 \
    -d postgres:17

psql essentials—the terminal client, and the fastest way to look inside a database:

psql -h localhost -U postgres -d terptasks

\l           -- list databases
\c terptasks -- connect to a database
\dt          -- list tables in the current schema
\d documents -- describe one table: columns, types, constraints, indexes
\q           -- quit

Roles and privileges, briefly. PostgreSQL calls both users and groups roles; a role can log in (or not), and holds privileges (SELECT, INSERT, ...) on specific objects. For this course: one role per application (never share the postgres superuser’s credentials with your API server), created with CREATE ROLE app_user LOGIN PASSWORD ’...’; and granted only what it needs (GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;). Fine-grained access control beyond this is a topic of its own, out of scope here.

7.4.2 PostgreSQL Specifics Worth Knowing🔗

text vs.\ varchar. In PostgreSQL they perform identically; varchar(n) adds a length cap PostgreSQL enforces on write, text has none. Default to text, and only cap length with varchar(n) (or a CHECK (length(...) <= n)) when the cap is a real business rule, not a guess.

Numeric types and money. INTEGER/BIGINT for counts; never store money in FLOAT—binary floating-point can’t represent most decimal fractions exactly, so cents silently drift. NUMERIC(precision, scale) is exact and is what any column holding currency must use.

timestamptz, and why not timestamp. timestamp stores a date and time with no timezone information—“2:00 PM” in whose timezone? timestamptz stores the moment unambiguously (internally as UTC) and converts on display to whatever timezone the connection asks for. Always use timestamptz; it is the storage-layer version of the UTC-everywhere discipline this course has enforced all semester.

uuid, arrays, and enums—evolving the running schema to show each one in a realistic spot:

-- uuid: an alternative surrogate key, useful when an id must be
-- guessable-resistant (e.g. exposed in a public share link) rather
-- than a small sequential integer. Not a schema change here---just
-- know it exists:
--   id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- array: for a genuinely atomic multi-valued fact with no shared
-- identity of its own (contrast with tags, which needed a junction
-- table because tags ARE shared, queryable entities)
ALTER TABLE documents ADD COLUMN authors TEXT[] NOT NULL DEFAULT '{}';
UPDATE documents SET authors = ARRAY['A. Wu', 'B. Lee'] WHERE id = 2;
SELECT title FROM documents WHERE 'A. Wu' = ANY(authors);

-- enum: a small, fixed, closed set of categories -- safer than a
-- free-text column because an invalid value is a write-time error,
-- not a typo discovered three months later
CREATE TYPE source_kind_t AS ENUM ('upload', 'url', 'pasted_text');
ALTER TABLE documents ADD COLUMN source_kind source_kind_t
    NOT NULL DEFAULT 'upload';

Misconception. “Arrays solve the tags problem without a junction table.” A tags TEXT[] column still can’t enforce UNIQUE on tag names, can’t be efficiently joined to “every document with this tag,” and needs an app-level rename fixup everywhere the tag name is copied. Reach for an array only when the values have no identity beyond being members of that one row’s list—an author list, not a set of shared, queryable entities.

JSONB—for genuinely semi-structured data, where different rows legitimately have different fields:

ALTER TABLE documents ADD COLUMN metadata JSONB NOT NULL DEFAULT '{}';

UPDATE documents
SET metadata = '{"course": "CMSC389A", "week": 7, "pages": 12}'
WHERE id = 2;

-- ->> extracts a JSON field as text; index it like any other column
SELECT title FROM documents WHERE metadata ->> 'course' = 'CMSC389A';
CREATE INDEX ON documents USING GIN (metadata);

JSONB (the binary, indexable form—prefer it over plain JSON for anything you’ll query) is the escape hatch for data that doesn’t fit a fixed set of columns; it is not a reason to skip modeling the columns you know you have. A PDF upload’s page count and a pasted-text source’s original word count are both “metadata,” but they’re different shapes—exactly the case JSONB is for. If every row does share the same fields, those are ordinary columns, typed and constrained like everything else in this lecture.

Type casting. :: converts between types inline: ’42’::INTEGER, now()::DATE (drops the time-of-day), metadata ->> ’week’::INTEGER (a JSONB text field, cast back to a number for comparison).

RETURNING. Skip a round trip: get the row (or just the generated id) back from the same statement that wrote it.

INSERT INTO tags (name) VALUES ('syllabus') RETURNING id;

Upsert: INSERT ... ON CONFLICT. “Get or create” in one statement instead of a select-then-maybe-insert race:

-- get-or-create a tag by name; do nothing if it already exists
INSERT INTO tags (name) VALUES ('syllabus')
ON CONFLICT (name) DO NOTHING
RETURNING id;

-- upsert that updates on conflict instead: bump a use counter
ALTER TABLE tags ADD COLUMN use_count INTEGER NOT NULL DEFAULT 0;

INSERT INTO tags (name, use_count) VALUES ('syllabus', 1)
ON CONFLICT (name) DO UPDATE SET use_count = tags.use_count + 1
RETURNING id, use_count;

Without ON CONFLICT, “check if the tag exists, insert if not” is two statements with a race condition between them—two concurrent requests can both see “doesn’t exist yet” and both insert, and the UNIQUE constraint saves you from duplicate rows only by throwing an error one of them has to handle. ON CONFLICT makes the whole get-or-create atomic and error-free.

7.4.3 Transactions🔗

Motivation. Move $100 from account A to account B: debit A, credit B—two separate statements. If the process crashes between them, does the money exist twice, or vanish? A transaction is the database’s answer: a group of statements that succeed or fail together.

Formal definition (ACID). Atomicity—all statements in the transaction happen, or none do. Consistency—every committed state satisfies the schema’s constraints. Isolation—concurrent transactions don’t see each other’s uncommitted changes. Durability—once committed, a transaction survives a crash.

BEGIN;
    INSERT INTO documents (owner_id, title) VALUES (1, 'New Upload')
        RETURNING id;                          -- suppose this returns id = 4
    INSERT INTO chunks (document_id, chunk_index, content) VALUES
        (4, 0, 'first chunk of text...'),
        (4, 1, 'second chunk of text...');
COMMIT;     -- or ROLLBACK to undo everything above, as if it never ran

Worked example—force a failure mid-transaction. Start the same block, but before COMMIT, try inserting a chunk with a duplicate (document_id, chunk_index)—the UNIQUE constraint rejects it, the transaction is now aborted, and every statement since BEGIN is undone, including the document insert that itself looked fine. Either the whole upload exists—document and all its chunks—or none of it does. There is no state where the document exists with half its chunks missing.

What goes wrong without isolation—the concurrency anomalies isolation levels exist to prevent:

MVCC, at a high level. PostgreSQL prevents most of these not by locking readers out, but with MVCC (multiversion concurrency control): every transaction sees a consistent snapshot of the data as of when it started, while writers create new row versions rather than overwriting in place. Readers never block writers and writers never block readers—only two writers touching the same row can conflict. PostgreSQL’s default isolation level, Read Committed, gives you this snapshot per-statement (not per-transaction) and rules out dirty reads; it still permits non-repeatable reads, which is the right trade-off for the overwhelming majority of application code and the one you’ll use unless you have a specific reason not to.

Deadlocks. Transaction A locks row 1 then waits for row 2; transaction B has already locked row 2 and is waiting for row 1—neither can proceed, and after a timeout PostgreSQL kills one of them with a deadlock detected error so the other can continue. The fix is almost always process, not code: have every transaction that touches multiple rows lock them in the same order (e.g., always by ascending id), so two transactions converge on the same row instead of crossing paths.

7.4.4 Indexes and Query Performance🔗

Formal definition. An index is a separate, ordered data structure PostgreSQL maintains alongside a table, letting it find matching rows without scanning every one. The default and by far most common kind is a B-tree: a balanced tree keyed on one or more columns, giving O(log n) lookups instead of O(n).

Intuition. A phone book sorted by last name lets you find “Wu” by bisecting; an unsorted pile makes you read every card. An index is the sorted structure; without one, PostgreSQL reads every row (a sequential scan)—correct, but slow as the table grows.

CREATE INDEX ON documents (owner_id);                    -- single column
CREATE INDEX ON chunks (document_id, chunk_index);        -- composite
CREATE UNIQUE INDEX ON users (email);                     -- UNIQUE already
                                                            -- creates one of these

The trade-off. An index makes matching SELECTs faster by making every INSERT/UPDATE/DELETE slightly slower (the index must be kept in sync) and uses extra disk. Index the columns your WHERE, JOIN, and ORDER BY clauses actually use; indexing everything is not free.

EXPLAIN and EXPLAIN ANALYZE. EXPLAIN shows the plan PostgreSQL would use, without running the query; EXPLAIN ANALYZE actually runs it and reports real timing alongside the plan.

Worked example—live, before and after. Seed documents with 200,000 rows across many owners, then:

EXPLAIN ANALYZE SELECT * FROM documents WHERE owner_id = 42;

Every one of 200,000 rows was checked to find 8 matches. Now:

CREATE INDEX ON documents (owner_id);
EXPLAIN ANALYZE SELECT * FROM documents WHERE owner_id = 42;

Same query, same result, Seq Scan → Index Scan, roughly 450× faster on this shape of data—read the plan’s node type first; it tells you which of the two things PostgreSQL is doing before you even look at the timings.

The N+1 query problem. A different performance bug, with no index fix: fetch every document, then loop in application code fetching that document’s chunks one query at a time. Ten documents means 1 query plus 10 more—N+1 queries where one would do:

-- the fix: one query, not N+1
SELECT * FROM chunks WHERE document_id = ANY(ARRAY[1,2,3,4,5,6,7,8,9,10]);
-- or the LEFT JOIN from Lecture 1, which returns everything in one round trip

N+1 doesn’t show up in EXPLAIN—each individual query looks fine—it shows up as “why does this page make 200 database calls,” visible only by counting queries per request (turning on query logging, or watching the count in a profiler) rather than reading any single plan.

7.4.5 Talking to PostgreSQL from Python🔗

The driver. psycopg (v3) is PostgreSQL’s standard Python driver: connect, open a cursor, execute, fetch.

import psycopg

with psycopg.connect("dbname=terptasks user=app_user") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT id, title FROM documents WHERE owner_id = %s", (1,))
        for row in cur.fetchall():
            print(row)

Parameterized queries—never build SQL with f-strings. The %s placeholder above is not string formatting; psycopg sends the query text and the value separately, so the database never interprets user input as SQL syntax, no matter what it contains.

Worked example—the exploit, then the fix. A search endpoint built the “obviously simpler” way:

# VULNERABLE: never do this
def search_documents_unsafe(cur, title_query: str):
    cur.execute(f"SELECT * FROM documents WHERE title ILIKE '%{title_query}%'")
    return cur.fetchall()

A normal search for Lecture produces the expected SQL. But title_query = "x’ OR ’1’=’1" produces:

SELECT * FROM documents WHERE title ILIKE '%x' OR '1'='1'%'

’1’=’1’ is always true, so this returns every document in the database, regardless of who owns it—a single unauthenticated search box just leaked every user’s private uploads. A more aggressive payload (title_query containing ’; DROP TABLE documents; –) can delete data outright. This is SQL injection: user input escaping the data channel and being executed as code, because the query string was built by concatenation instead of kept structurally separate from its inputs.

# SAFE: the value is a parameter, never SQL text
def search_documents(cur, title_query: str):
    cur.execute("SELECT * FROM documents WHERE title ILIKE %s", (f"%{title_query}%",))
    return cur.fetchall()

Note precisely what changed: the % wildcards are still built into the Python string, but the user-controlled value is never spliced into the SQL text—it travels to PostgreSQL as data, full stop, and x’ OR ’1’=’1 is searched for literally as a title substring (matching nothing) instead of parsed as SQL. This is the single most important habit in this lecture: if you ever write f"...{user_input}..." next to the word SQL, stop.

Transactions from application code mirror BEGIN/COMMIT directly—psycopg’s connection starts a transaction automatically on first use, and closing the with block commits on success or rolls back on an unhandled exception:

with psycopg.connect("dbname=terptasks user=app_user") as conn:
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO documents (owner_id, title) VALUES (%s, %s) RETURNING id",
            (1, "New Upload"),
        )
        doc_id = cur.fetchone()[0]
        cur.execute(
            "INSERT INTO chunks (document_id, chunk_index, content) VALUES (%s, %s, %s)",
            (doc_id, 0, "first chunk..."),
        )
    # both INSERTs commit together here; an exception above rolls both back

ORM vs.\ raw SQL, in one table. An ORM (object-relational mapper, e.g.\ SQLAlchemy) maps rows to Python objects and writes the SQL for you; this course teaches raw SQL first because the ORM’s SQL is everything above, one layer down, and every mistake in this lecture (missing index, N+1, an unparameterized filter) still happens through an ORM if you don’t understand what it’s generating.

Connection pooling. Opening a PostgreSQL connection is relatively expensive (a new process on the server, authentication, setup); a web app handling many short-lived requests should not open one per request. A connection pool (e.g.\ psycopg_pool, or PgBouncer in front of the database) keeps a set of connections open and hands them out and back, so a request borrows one instead of paying setup cost every time.

7.4.6 Operating a Real Database🔗

Schema migrations. Section Defining Tables: DDL warned against DROP-and-recreate; the tool that makes evolving a live schema safe is a migration: a small, version-controlled script with an upgrade and a downgrade, applied in order, with the database remembering which one it’s on—Git for your schema, in one sentence. (Python projects typically reach for Alembic, alongside SQLAlchemy; the underlying discipline—review every autogenerated diff before running it, because a rename can autogenerate as a destructive drop-and-add—matters regardless of tool, and is worth internalizing even at the level this course covers it.)

Seed data and test databases. Tests should run against a real, disposable PostgreSQL—a fresh schema per test run (or per test), loaded with known seed rows, so a test failure means the code is wrong, not that yesterday’s leftover data drifted. A throwaway Docker container (Section Getting PostgreSQL Running) is the natural fit: spin it up in CI, run migrations, run tests, discard it.

pg_dump / pg_restore. A full backup and its inverse:

pg_dump -h localhost -U postgres terptasks > backup.sql
psql    -h localhost -U postgres terptasks < backup.sql     # restore (plain SQL dump)

VACUUM, ANALYZE, and autovacuum, one sentence each. VACUUM reclaims space left behind by MVCC’s old row versions once nothing needs them anymore; ANALYZE refreshes the statistics the query planner uses to decide seq-scan-vs-index-scan; autovacuum runs both automatically in the background, and for a course project you will essentially never touch either command directly.

7.4.7 Bridge to the Rest of the Course🔗

chunks was named that from the very first diagram in this lecture for a reason: it is precisely the table an embedding pipeline chunks documents into. Add one column and this schema is a vector store:

CREATE EXTENSION vector;
ALTER TABLE chunks ADD COLUMN embedding vector(384);

-- nearest-neighbor search: <=> is cosine distance, ascending = most similar
SELECT content FROM chunks ORDER BY embedding <=> $query_vector LIMIT 5;

That’s pgvector—and the reason “just use Postgres” is usually the right call before reaching for a dedicated vector database: your embeddings live next to the rows they came from, in the same transactions, the same backups, the same JOINs you already know from Lecture 1. The full mechanics—how the ANN index trades exactness for speed, and where the schema plugs into retrieval—belong to the RAG and vector-search material later in the course; the point to leave with today is narrower: it’s the same database, wearing one new column type.

And the whole schema built across these two lectures is exactly what sits under the REST API layer: a POST /documents endpoint runs the INSERT from Section Defining Tables: DDL, a GET /documents/{id}/chunks runs the LEFT JOIN from Section Joins and Aggregation, each wrapped in exactly the parameterized, transactional psycopg calls from Section Talking to PostgreSQL from Python. Nothing about swapping in a real database changes what the API layer promises—see the REST/FastAPI lecture.

Checkpoint—by the end of Lecture 2 you can: run PostgreSQL, query it safely from Python, explain why a query is slow (and fix it), and wrap a multi-step write in a transaction.

7.5.1 Basic🔗

1. B1. ER diagram to schema. Sketch the ER diagram for the running example (users, documents, chunks, tags, document_tags), labeling cardinality on every line. Then write the CREATE TABLE statements from scratch, with every constraint from Lecture 1, and load 3 users, 5 documents, and a handful of chunks and tags with INSERT.

2. B2. Name the anomalies. Given the intern’s flat table (document/title/owner_email/owner_name/tags), give one concrete sequence of operations for each anomaly—update, insert, delete—using Alice, Bob, and a new user Dana.

3. B3. Break it on purpose. Against your schema from B1, deliberately violate a NOT NULL, a UNIQUE, a foreign key, and a CHECK constraint (one INSERT/UPDATE each). Paste each PostgreSQL error message and explain, in one sentence per error, exactly which rule stopped the write.

4. B4. Five queries. Using your seeded data, write five queries: one JOIN, one GROUP BY, one query using CASE, one using LIKE or IN, and one that correctly tests a nullable column with IS NULL.

5. B5. The WHERE you forgot. On a scratch copy of your database, run an UPDATE and a DELETE with no WHERE clause and observe the damage. Then restore from a pg_dump backup you took beforehand. State, in one sentence, the habit that would have prevented needing the backup at all.

7.5.2 Intermediate🔗

1. I1. Three-table report. Write one query that returns, per user: name, total documents, and documents still unprocessed (processed_at IS NULL)—joining users and documents and using GROUP BY with a conditional aggregate.

2. I2. A CTE, and the NULL trap named. Write a WITH query that finds every user with more than two unprocessed documents. In a comment above it, explain in one sentence why WHERE processed_at = NULL would silently return nothing even if such users exist.

3. I3. Index a slow query. Seed documents with at least 100,000 rows (a small script generating fake titles/owners is fine). Run EXPLAIN ANALYZE on a query filtering by owner_id, then create the appropriate index and run it again. Report both plans and the before/after timing.

4. I4. psycopg, RETURNING, and upsert. Write a Python script that inserts a new tag using a parameterized query and RETURNING to capture the generated id, then re-runs the same insert as an ON CONFLICT upsert and shows it does not create a duplicate row.

5. I5. Transaction, forced rollback. Write a psycopg script that inserts a document and its first two chunks inside one transaction, then deliberately raises an exception before the block exits. Query the database afterward and show that neither the document nor the chunks exist—the transaction rolled back as a unit.

7.5.3 Advanced🔗

1. A1. Exploit it, then fix it. Against a scratch database (never one with real data), implement the vulnerable f-string search from Section Talking to PostgreSQL from Python, demonstrate a payload that returns rows it shouldn’t (or drops a table on a disposable copy), then replace it with a parameterized version and show the same payload is now treated as a harmless literal string. Deliverable: both versions of the function, the exploit payload, and the query PostgreSQL actually received in each case (via cur.query or query logging).

2. A2. Reproduce a deadlock. Open two psql sessions. In session A, BEGIN and update document 1, then (without committing) try to update document 2. In session B, BEGIN and update document 2, then try to update document 1. Capture the deadlock detected error, explain which session PostgreSQL killed and why, then fix the ordering so it can’t recur.

3. A3. JSONB in anger. Add the metadata JSONB column from Lecture 2, populate it with differently-shaped metadata across at least three documents (a PDF upload’s page count, a URL source’s fetch timestamp, a pasted-text source’s word count), write a query filtering on one nested field with ->>, create a GIN index on the column, and use EXPLAIN ANALYZE to confirm the index is used.

7.6.1 Key takeaways🔗

1. A schema makes wrong states unrepresentable. Types, PKs, FKs, UNIQUE, CHECK, and cascade rules bind every writer, not just the one you remembered to check in application code.

2. Three relationship shapes, three FK placements: 1-N puts the FK on the many side; M-N requires a junction table whose rows are facts and can carry columns of their own. Normalize until every fact has one home.

3. NULL is unknown, not empty. Test it with IS [NOT] NULL, never =.

4. Joins are the payoff for normalization; INNER drops unmatched rows, LEFT keeps them with NULL filled in. WHERE filters rows, HAVING filters groups.

5. A transaction is all-or-nothing. ACID is what that buys you; MVCC is how PostgreSQL gives most of it away for free, without readers and writers blocking each other.

6. An index trades write speed and storage for read speed. EXPLAIN ANALYZE is how you find out whether that trade is even happening—read the node type (Seq Scan vs.\ Index Scan) first.

7. Parameterize every query built from user input. SQL injection is not a rare edge case; it is the default outcome of string-formatting SQL, and the fix is one API call away.

8. The database is one more thing you version, seed, back up, and maintain—migrations, test databases, pg_dump, and autovacuum are what keep a schema evolving safely instead of by accident.

7.6.2 Terminology🔗

7.6.3 Common mistakes🔗

• Comparing to NULL with = instead of IS NULL.

• Running UPDATE/DELETE without checking the WHERE clause first.

• Comma-separated values in a column instead of a junction table—and reaching for an array type to do the same thing with a different syntax.

• Skipping FK constraints “because the app checks”—the app is one writer among several.

• FLOAT for money; timestamp without a timezone.

• Building SQL with an f-string instead of a parameterized query.

• Adding an index without ever running EXPLAIN ANALYZE to confirm it’s used—or never adding one and blaming “the database” for a slow, un-indexed WHERE clause.

• Looping in application code to fetch related rows one at a time (N+1) instead of one join or one WHERE ... = ANY(...).

• DROP TABLE and recreate as a migration strategy, once real data exists.

7.6.4 Connections🔗

• Backward: this is the persistence layer under the REST APIs and FastAPI: Designing and Building Web Interfaces lecture; constraint-thinking continues the make-invalid-unrepresentable philosophy from Pydantic; the migration/backup discipline echoes the Git lecture’s version-control habits applied to a schema instead of code.

• Forward: pgvector and the chunks table are the literal data layer under this course’s retrieval-augmented-generation material; parameterized queries are the first concrete defense against the OWASP injection class of vulnerabilities; and when an AI agent maintains your schema or writes your queries, the review habits from the (part "Elephant-Goldfish") lecture—read the diff, read the generated SQL, don’t ship an autogenerated migration or a string-built query unread—are exactly where the human judgment concentrates.