Capstone: Design and Operate a Production Database

This capstone turns the earlier PostgreSQL lessons into one production design exercise. The outcome is not a large schema; it is a defensible database that preserves business facts under concurrent writes, supports predictable reads, can be migrated without unnecessary downtime, and can be recovered from a tested backup.

The example domain is a small course platform because it has realistic relationships: accounts enroll in courses, lessons are published in order, learners submit attempts, and operators need progress reports. By the end, you should be able to explain why each table, constraint, index, role, and operational check exists.

Purpose and Outcome

A production PostgreSQL database has two jobs. First, it is the authority for durable state. Second, it is a concurrency control system that decides which changes are valid when many sessions act at once. Application code can help, but invariants that must never be violated belong as close to the data as possible.

For this capstone, the main invariants are concrete: email addresses are unique case-insensitively, a learner can enroll in a course only once, lesson positions are unique inside a course, an attempt references an existing enrollment and lesson, and reports can find recent activity without scanning every row. Those rules drive the design more than table names do.

How PostgreSQL Enforces the Design

PostgreSQL stores rows in heap tables and uses MVCC, multi-version concurrency control, to let readers see a consistent snapshot while writers create newer row versions. A transaction sees rows according to its isolation level, and commits make its successful changes visible to later snapshots. This is why a database design must consider both the logical model and the timing of concurrent statements.

Constraints are checked by PostgreSQL itself. A primary key creates a unique B-tree index and gives each row a stable identity. A foreign key checks that referenced rows exist and prevents orphaned data according to its action. A unique constraint or unique index rejects duplicate keys even when two sessions race. A check constraint rejects invalid values on insert and update.

Indexes are separate access structures. They speed up specific predicates, joins, and ordering operations, but every insert, update, and delete must maintain them. The planner chooses between sequential scans, index scans, bitmap scans, nested loops, hash joins, and other plan nodes by estimating row counts and costs from table statistics. Production design therefore includes representative data and plan inspection, not only schema syntax.

Operations matter because PostgreSQL is a living system. WAL, the write-ahead log, records changes before data pages are flushed. Backups and point-in-time recovery depend on base backups plus WAL. Autovacuum removes dead row versions and updates visibility information. Connection limits, lock waits, long transactions, and missing statistics can all turn a correct schema into a slow or unavailable service.

Schema Anatomy

The first example creates the core model. It uses generated identity columns for narrow surrogate keys, natural uniqueness where the business requires it, foreign keys for relationships, and partial indexing for a common production query: recent completed attempts.

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE app_user (
    user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email citext NOT NULL UNIQUE,
    display_name text NOT NULL CHECK (length(display_name) BETWEEN 2 AND 120),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE course (
    course_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug text NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$'),
    title text NOT NULL CHECK (length(title) BETWEEN 3 AND 200),
    published_at timestamptz
);

CREATE TABLE lesson (
    lesson_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_id bigint NOT NULL REFERENCES course(course_id) ON DELETE CASCADE,
    position integer NOT NULL CHECK (position > 0),
    title text NOT NULL CHECK (length(title) BETWEEN 3 AND 200),
    UNIQUE (course_id, position)
);

CREATE TABLE enrollment (
    enrollment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES app_user(user_id) ON DELETE CASCADE,
    course_id bigint NOT NULL REFERENCES course(course_id) ON DELETE CASCADE,
    enrolled_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (user_id, course_id)
);

CREATE TABLE lesson_attempt (
    attempt_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    enrollment_id bigint NOT NULL REFERENCES enrollment(enrollment_id) ON DELETE CASCADE,
    lesson_id bigint NOT NULL REFERENCES lesson(lesson_id) ON DELETE RESTRICT,
    score numeric(5,2) NOT NULL CHECK (score BETWEEN 0 AND 100),
    completed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX lesson_attempt_recent_completed_idx
    ON lesson_attempt (completed_at DESC, enrollment_id)
    WHERE completed_at IS NOT NULL;

The citext extension stores email comparisons case-insensitively while preserving the submitted spelling. The unique constraints are not documentation; they are race-safe enforcement points. The partial index is smaller than a full index because incomplete attempts are excluded, which helps the report query that only cares about completions.

Progressive Example 1: Load Valid Data

This example inserts one user, one course, two lessons, one enrollment, and one completed attempt. The expected behavior is a single returned progress row for the learner.

INSERT INTO app_user (email, display_name)
VALUES ('Ada@example.com', 'Ada Lovelace');

INSERT INTO course (slug, title, published_at)
VALUES ('postgresql-in-depth', 'PostgreSQL in Depth', now());

INSERT INTO lesson (course_id, position, title)
SELECT course_id, 1, 'Relational modeling'
FROM course
WHERE slug = 'postgresql-in-depth';

INSERT INTO lesson (course_id, position, title)
SELECT course_id, 2, 'Indexes and plans'
FROM course
WHERE slug = 'postgresql-in-depth';

INSERT INTO enrollment (user_id, course_id)
SELECT u.user_id, c.course_id
FROM app_user AS u
CROSS JOIN course AS c
WHERE u.email = 'ada@example.com'
  AND c.slug = 'postgresql-in-depth';

INSERT INTO lesson_attempt (enrollment_id, lesson_id, score, completed_at)
SELECT e.enrollment_id, l.lesson_id, 96.50, now()
FROM enrollment AS e
JOIN app_user AS u ON u.user_id = e.user_id
JOIN course AS c ON c.course_id = e.course_id
JOIN lesson AS l ON l.course_id = c.course_id AND l.position = 1
WHERE u.email = 'ADA@example.com'
  AND c.slug = 'postgresql-in-depth';

SELECT u.email, c.slug, count(a.attempt_id) AS completed_lessons
FROM app_user AS u
JOIN enrollment AS e ON e.user_id = u.user_id
JOIN course AS c ON c.course_id = e.course_id
LEFT JOIN lesson_attempt AS a
    ON a.enrollment_id = e.enrollment_id
   AND a.completed_at IS NOT NULL
WHERE u.email = 'ada@example.com'
GROUP BY u.email, c.slug;

The deterministic part of the output is completed_lessons = 1. The email predicate matches regardless of case because citext controls comparison semantics. The joins follow foreign-key relationships, so the report cannot count an attempt for a nonexistent enrollment.

Progressive Example 2: Prove Invariants Reject Bad State

Production design is incomplete until refusal behavior is tested. These statements should fail: the duplicate email conflicts with the case-insensitive unique key, and the duplicate lesson position conflicts with UNIQUE (course_id, position).

INSERT INTO app_user (email, display_name)
VALUES ('ada@EXAMPLE.com', 'Another Ada');

INSERT INTO lesson (course_id, position, title)
SELECT course_id, 1, 'Duplicate first lesson'
FROM course
WHERE slug = 'postgresql-in-depth';

The expected symptoms are unique-violation errors. The important production lesson is that the application does not need to win a timing race before inserting. PostgreSQL arbitrates the conflict at the index level. The application should catch the unique violation and return a domain-specific message such as email already registered or lesson position already used.

Progressive Example 3: Inspect the Reporting Plan

A schema that is correct but slow still fails the capstone. This query asks for recent completions and should be able to use the partial index once the table has enough rows for the planner to prefer it.

EXPLAIN (ANALYZE, BUFFERS)
SELECT a.completed_at, u.email, c.slug, l.position, a.score
FROM lesson_attempt AS a
JOIN enrollment AS e ON e.enrollment_id = a.enrollment_id
JOIN app_user AS u ON u.user_id = e.user_id
JOIN course AS c ON c.course_id = e.course_id
JOIN lesson AS l ON l.lesson_id = a.lesson_id
WHERE a.completed_at IS NOT NULL
ORDER BY a.completed_at DESC
LIMIT 20;

On a tiny development database, a sequential scan may be cheaper and is not automatically a bug. With representative volume, the expected shape is an index scan or backward scan using lesson_attempt_recent_completed_idx, followed by primary-key lookups for joined rows. If the plan scans all attempts at production scale, check row estimates, run ANALYZE, and confirm the predicate exactly matches the partial index condition.

Design Choices and Trade-offs

Surrogate keys make joins compact and stable when titles or slugs change. Natural keys still matter: course.slug and app_user.email have business uniqueness and need database enforcement. A design that uses only surrogate keys but omits natural unique constraints permits duplicate real-world entities.

ON DELETE CASCADE is appropriate from course to lesson in this training platform only if deleting a course is an intentional administrative operation that should remove its lesson structure. ON DELETE RESTRICT on lesson_attempt.lesson_id protects historical attempt records from accidental lesson deletion. These actions should match product policy, audit requirements, and recovery expectations.

The partial index improves the recent-completion report and reduces write overhead compared with indexing every attempt by completion time. The trade-off is specificity. Queries that omit completed_at IS NOT NULL or search incomplete attempts cannot use it. Additional indexes should be justified by observed query plans and write cost, not added because a column appears in a where clause once.

One transaction should wrap a user-visible operation that must be atomic, such as creating a course with its first lessons. Long transactions, however, keep old row versions visible and can delay cleanup. Keep transactions short, avoid waiting for network calls while holding locks, and use retry logic for serialization failures or deadlocks when higher isolation is chosen.

Failure Modes and Troubleshooting

Symptom: registration intermittently returns duplicate accounts for the same email spelling with different case. Cause: the database used text without a functional unique index or citext. Diagnose: group by lower(email) and count duplicates. Correct: merge duplicates, install citext or add a unique index on lower(email), then update the application to handle unique violations.

Symptom: the recent progress page becomes slow as attempts grow. Cause: missing, unused, or bloated index; stale statistics; or a predicate that does not match the partial index. Diagnose: run EXPLAIN (ANALYZE, BUFFERS), inspect row estimates, and compare shared-buffer reads. Correct: run ANALYZE, rewrite the predicate to match the index, or create a better index after measuring write impact.

Symptom: deployments block writes or time out. Cause: a migration took a strong lock on a large table during peak traffic. Diagnose: inspect pg_stat_activity and lock waits, then identify the migration statement. Correct: break the migration into phases, backfill in batches, validate constraints separately where possible, and schedule high-lock steps deliberately.

Symptom: disk usage grows while delete and update traffic is normal. Cause: dead tuples are not being reclaimed, often because autovacuum is behind or a long transaction prevents cleanup. Diagnose: check table dead-tuple estimates, long-running transactions, and autovacuum activity. Correct: end stale transactions, tune autovacuum for hot tables, and avoid application sessions that stay idle in transaction.

Security, Performance, and Reliability

Use separate roles for migrations, application traffic, and read-only reporting. The application role should not own tables and should not have broad schema privileges. Grant only the statements it needs. This limits damage from SQL injection, application bugs, or leaked credentials.

Performance work should start from the workload: the top read queries, write rate, latency target, and growth model. Indexes speed reads at the cost of storage, WAL volume, and write latency. Constraints add validation cost but prevent corruption that is much more expensive to repair later. Connection pools protect PostgreSQL from too many backends, but pools must still respect transaction boundaries.

Reliability is proven by restore tests. A backup that has never been restored is only an artifact. For this capstone, define a recovery point objective based on acceptable data loss and a recovery time objective based on how long the course platform can be unavailable. Then test a restore into an isolated database and run integrity queries against it.

Hands-on Lab

Prerequisites: a local PostgreSQL database where you can create tables and extensions, a SQL client such as psql, and permission to drop the lab objects afterward.

  1. Create a dedicated schema with CREATE SCHEMA capstone_lab; and set your search path to it.
  2. Run the schema script from the first example.
  3. Run the valid data example and confirm the report returns one completed lesson.
  4. Run each invalid insert from the second example separately and confirm PostgreSQL rejects it.
  5. Run the EXPLAIN query. Record whether it uses a sequential scan or the partial index, and explain whether that is reasonable for your row count.
  6. Create a read-only role and verify it can select progress but cannot insert attempts.
  7. Clean up by dropping the schema.
CREATE ROLE capstone_reader LOGIN PASSWORD 'replace_this_password';
GRANT USAGE ON SCHEMA capstone_lab TO capstone_reader;
GRANT SELECT ON app_user, course, lesson, enrollment, lesson_attempt TO capstone_reader;

SET ROLE capstone_reader;
SELECT count(*) FROM lesson_attempt;
INSERT INTO lesson_attempt (enrollment_id, lesson_id, score)
VALUES (1, 1, 80.00);
RESET ROLE;

DROP SCHEMA capstone_lab CASCADE;
DROP ROLE capstone_reader;

The verification is specific. The SELECT should succeed for the read-only role. The INSERT should fail with a permission error. Cleanup should remove the lab schema and role; if the role still owns or depends on objects, inspect grants and revoke them before dropping it.

Assessment Exercises

  1. A product manager asks to allow unpublished lessons to be reordered freely while preserving published lesson order. What constraint or schema change would you propose, and what migration risk would you test?
  2. The report query is slow, but EXPLAIN shows the partial index is unused on a table with millions of attempts. List three possible causes and the diagnostic query or observation you would use for each.
  3. Two sessions try to enroll the same user in the same course at the same time. Explain which database object prevents duplicate enrollment and how the application should respond.
  4. You need to delete a user for privacy reasons but retain aggregate course completion statistics. Which foreign-key actions in this design conflict with that requirement, and what alternative model could preserve aggregates without personal data?
  5. Design a restore test for this database. What data would you insert before backup, what command or process would restore it, and which SQL checks would prove the restored database is usable?

Summary

A production PostgreSQL design is a set of enforceable decisions. Tables describe durable facts, constraints protect invariants, indexes serve measured access paths, roles define permitted behavior, and backup tests prove recoverability. The capstone skill is connecting those pieces: start from the business rule, encode it in PostgreSQL where possible, inspect how the engine will execute the workload, and rehearse the failures before users experience them.