Database Design and Indexing for Scale

Advanced System Design & Software Architecture · Lesson 28 · High-Level Design and Distributed Systems

Lesson Overview

TechLearningPro's learner home page asks one question of PostgreSQL: the twenty most recent orders for this learner, with status and total, placed since a date the client already knows. During the winter enrollment week that query moved from a quiet index lookup to a multi-second sort. The table was normalized. The primary key was a random UUID. Six secondary indexes had been added because six screens filtered on six columns. Writes to commerce.orders had slowed in the same week, and autovacuum was the name everyone used before anyone opened a plan.

The schema was not "wrong" in the textbook sense. It was unfinished as an access-path design. A row model answers what must stay true when money moves. An index answers which predicate, sort, and projected columns a real statement will use. Those are different designs, and they fight each other. Every extra B-tree is more WAL, more page splits, and fewer chances for a heap-only tuple update. A missing B-tree on the foreign-key column is a sequential scan the day a parent row is deleted. A covering index that the visibility map cannot serve still visits the heap and looks, in a slide, like it should have been free.

This lesson treats schema and indexes as one capacity decision for the TechLearningPro commerce write path and the order-history read path. You will shape tables so constraints protect invariants, then attach indexes to measured statements. You will read EXPLAIN (ANALYZE, BUFFERS) as evidence, use INCLUDE columns when the projection is stable, and account for write amplification before you approve another index. PostgreSQL is the engine. Spring's JdbcClient is the application side: parameterized SQL, a row mapper, and a transaction boundary that does not hide the statement.

Replication, sharding, and cross-service transactions come next. Database Replication and Consistency copies this schema. Database Sharding and Partitioning splits it. Index syntax in depth lives in the PostgreSQL course: Index Fundamentals, B-Tree Indexes, Composite Indexes, Covering Indexes, Partial Indexes, and EXPLAIN ANALYZE. Caching in front of this query is Caching Strategies and Cache Invalidation. A cache does not repair a plan that reads the wrong pages.

Learning Objectives

By the end of this lesson you will be able to:

1. Separate the write model (invariants, keys, captured facts) from the read path (predicate, order, projection) and name which one an index serves.
2. Choose B-tree column order for a statement that mixes equality, a range, and ORDER BY.
3. Explain what a PostgreSQL covering index using INCLUDE stores, and what an index-only scan still requires from the visibility map.
4. Estimate write amplification from heap tuples, secondary indexes, TOAST, WAL, and the loss of heap-only updates.
5. Read EXPLAIN (ANALYZE, BUFFERS) for a limit query and distinguish estimated rows, actual rows, and heap fetches.
6. Add a large index with CREATE INDEX CONCURRENTLY and recognize an invalid index left behind by a failed build.
7. Use a partial unique index to enforce a business invariant that a plain UNIQUE on the whole table would reject.
8. Decide when a narrow summary table is a better read model than another join index on the OLTP tables.
9. Place foreign-key indexes, extended statistics, and autovacuum in a design review of a growing orders table.
10. Reject an index proposal that has no statement, no selectivity story, and no write-cost owner.

Prerequisites

• You can read SQL joins, primary keys, and foreign keys. Creating Tables and Constraints are enough if those words are rusty.
• You know a transaction commits or rolls back as one unit on a single PostgreSQL database. Transactions and MVCC explain why an update leaves a new tuple version.
• You can read a Java 21 record and a Spring @Transactional method. This lesson uses org.springframework.jdbc.core.simple.JdbcClient, which has been in Spring Framework since 6.1. It does not use a custom persistence framework.
• Lesson 24, Capacity Estimation and Workload Modeling, is the place you got the numbers this lesson spends: order-history QPS, rows per learner, and write rate during enrollment week.
• Lesson 27 is optional. If a cache sits in front of order history, you still need the index. The cache miss path is this query.

Detailed Core Concepts

The row is a fact; the index is an access path

An order row records a sale that already happened. The line stores unit_price_cents because refunds, tax, and instructor payouts must read the price that was charged after the catalog price changes. That column is a fact of the sale. A learner, an order, a line, and a payment attempt have different lifetimes, so they stay separate tables. A foreign key from order_lines.order_id to orders.id makes an orphan line unrepresentable. A check keeps total_cents non-negative. Those rules run for every writer, including a batch job that never calls the Spring service.

The read path is a statement, not a table. "Show this learner their recent orders" is:

• equality on learner_id
• a range on placed_at
• an order by placed_at descending
• a small projection: id, time, total, status
• a limit

If you design the index from the table diagram, you will index columns that appear in the entity class. If you design it from the statement, the key is (learner_id, placed_at DESC) and the extra projected columns are INCLUDE payload. The first design matches the object. The second matches the pages PostgreSQL will touch.

What a B-tree actually orders

PostgreSQL's default index is a B-tree. Leaves are sorted by the indexed columns. Equality, range, and ordering can all use that sort if the query's needs are a prefix of the index. The planner can use (learner_id, placed_at) for WHERE learner_id = $1 AND placed_at >= $2 ORDER BY placed_at DESC. It can use the same index for WHERE learner_id = $1 alone. It cannot use the second column to satisfy WHERE placed_at >= $2 if learner_id is absent, because those leaf entries for one timestamp are scattered under every learner.

Column order follows the predicate, then the sort:

1. Columns compared with equality, highest selectivity first when you have a choice and the statements agree.
2. The column used as a range or as the ORDER BY that you want to satisfy without a separate sort.
3. Further columns only if a real statement filters them with equality after the prefix.

Two indexes that swap column order are two different products. (placed_at, learner_id) serves "all orders yesterday" and then a filter. (learner_id, placed_at) serves one learner's history. TechLearningPro's home page is the second statement. A nightly finance export is the first, and it may not belong on this table at all. BRIN Indexes fit a physically time-ordered export table better than a second huge B-tree.

Random primary keys change the write shape of the same B-tree. A version-4 UUID spreads inserts across leaf pages, so each insert is more likely to split a full page and to dirty a page that was not already in cache. A bigint identity, or a UUID version 7 generated in the application (PostgreSQL 18 also provides uuidv7()), keeps new keys toward one edge of the index. Internal keys can be bigint. Public ids can still be UUIDs if clients already store them. The choice is locality of inserts, measured in WAL and cache misses, not taste.

Covering indexes and the visibility map

A covering index holds every column the statement needs, so the executor can answer from the index. In PostgreSQL the search key and the payload are different. Columns in the key define order and the predicates you can seek. Columns listed in INCLUDE are stored in the leaf and are not part of comparisons. That split matters: a wide status string in the key widens every comparison and every internal page; the same string in INCLUDE widens the leaf only.

sqlCopy
CREATE INDEX orders_learner_placed_cov
    ON commerce.orders (learner_id, placed_at DESC)
    INCLUDE (total_cents, status);

An index-only scan is still conditional. MVCC visibility lives on the heap tuple. The visibility map, maintained by VACUUM, records pages whose tuples are all visible to every current snapshot. If the page is all-visible, the executor trusts the index tuple. If it is not, the plan still says Index Only Scan and reports heap fetches. On a busy table the covering index becomes a wider leaf plus a heap visit. VACUUM is part of the design.

Write amplification

A single insert writes a heap tuple and a leaf entry in every index that includes that table. Each of those page modifications is WAL. A replica will replay the same WAL, so an index you added for a rare admin screen is paid again on every standby. An update that changes an indexed column does it a second time: a new heap version and new index entries. An update that changes only unindexed columns can be a heap-only tuple (HOT) when the new version fits on the same page. HOT skips new index entries. A line pointer redirect keeps indexes pointed at the chain.

HOT is why "index every column the UI might filter" damages a progress-update workload. If every column is in some index, almost every update changes an indexed column, HOT does not apply, and the table grows index versions as fast as it grows heap versions. fillfactor below 100 leaves free space on the heap page so a later HOT update has room. It is a deliberate density trade: slightly more pages at rest, fewer index writes on the hot update path. Set it on tables you have measured, not on every table in the migration.

TOAST copies large values out of line. A jsonb receipt on the hot order row is rewritten when the row is updated. Put wide documents in a side table when the hot update does not touch them. Autovacuum then has more dead index entries to visit. A unique index also proves absence on every insert. Pay that cost when the invariant is real.

The design rule is a budget. For the order table, name the statements that justify each index, the write rate that will maintain it, and the person who will drop it if pg_stat_user_indexes.idx_scan stays at zero after a full business cycle. An index with no scans is a tax.

Constraints the index is allowed to enforce

Some indexes exist so the database can refuse a bad write. A partial unique index is the usual tool when the invariant covers a subset of rows.

One learner may have many enrollments in a course over the years, and only one of them may be active:

sqlCopy
CREATE UNIQUE INDEX enrollments_one_active
    ON learning.enrollments (learner_id, course_id)
    WHERE status = 'active';

A plain unique constraint on (learner_id, course_id) would forbid a legitimate second enrollment after completion. A check in the Spring service would race: two transactions both see no active row and both insert. The partial unique index is the serialization point. Application code still catches the unique violation and returns a clear conflict to the client. The database remains the last writer of the rule.

Foreign keys need a supporting index on the referencing column. PostgreSQL indexes the referenced side because a primary key or unique constraint is required. It does not create an index on order_lines.order_id for you. Without that index, deleting or updating a parent order scans the child table, and the lock footprint surprises the person who thought a cascade was cheap. Add the child index in the same migration as the foreign key.

When the read model should leave the write table

A join across orders, lines, courses, and payout rates can be indexed into a tolerable plan and still be the wrong owner of a dashboard. If instructor revenue is "sum of captured line prices by course and month," the OLTP indexes that serve learner history do not serve that aggregate, and an aggregate that scans a year of lines will compete with checkout. A summary table, filled by a scheduled statement or by the same transaction that posts the line when the volume is modest, is a second schema with a second access path.

Keep the summary boring and rebuildable. Store course_id, month, gross_cents, refund_cents, and updated_at. The write path remains the line item. The summary is a projection you can truncate and refill from lines if a bug double-counts. That rebuild story is what makes denormalization safe. A summary nobody can recompute is a second source of truth with no owner.

A materialized view is the same projection with REFRESH MATERIALIZED VIEW CONCURRENTLY, which needs a unique index on the view. Prefer an explicit summary table when you must update one course's month as each order posts. Materialized Views covers the refresh command.

Statistics are part of the schema

The planner chooses an index when its row estimate makes that path cheaper than a sequential scan. Estimates come from ANALYZE samples: most-common values, histograms, and null fraction. A column with a default statistics target can mis-estimate a highly skewed learner_id if a few institutional accounts own a huge share of rows. The symptom is stable: estimated rows and actual rows in EXPLAIN ANALYZE disagree by an order of magnitude, and the chosen plan matches the estimate.

Raise statistics on the skewed column (ALTER TABLE ... ALTER COLUMN learner_id SET STATISTICS 1000) and run ANALYZE. When two columns are correlated, single-column stats multiply selectivities and invent a fantasy row count. Extended statistics record that dependence:

sqlCopy
CREATE STATISTICS orders_learner_status (dependencies, ndistinct)
    ON learner_id, status
    FROM commerce.orders;

Then ANALYZE commerce.orders. Fix the statistic and recapture the plan before you reach for a planner hint. Query Planning and ANALYZE are the companion lessons.

Schema evolution without a rewrite you did not budget

Adding a nullable column is a catalog change and does not rewrite the heap. Adding a column with a non-volatile constant default also avoids a full rewrite on PostgreSQL 11 and later. A volatile default, a type change, or a check validated against every row can lock far longer than the migration window. CREATE INDEX blocks writes. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, takes longer, and can leave an invalid index if the build fails. Drop that invalid index and build again.

Expand, then contract. Add the index, switch readers after the plan is proven, and drop the old index only after idx_scan shows the new statement carrying the load. The ADR records the index name, the statement, and the drop as rollback.

Architecture and Design Diagrams

The write path and the history read path share commerce.orders and do not share a reason to touch the same pages.

mermaidCopy
flowchart LR
    subgraph writePath [Checkout write path]
        API[OrderController]
        TX[JdbcClient transaction]
        ORD[orders heap]
        LIN[order_lines heap]
        PAY[payments heap]
        API --> TX
        TX --> ORD
        TX --> LIN
        TX --> PAY
    end
    subgraph indexes [Access structures]
        PK[orders PK]
        HIST[learner_id placed_at INCLUDE]
        LINEFK[order_lines order_id]
        ACT[partial unique on active enrollment]
    end
    ORD --> PK
    ORD --> HIST
    LIN --> LINEFK
    subgraph readPath [Learner home read path]
        HOME[History query LIMIT 20]
        HOME --> HIST
    end

Checkout inserts one order, its lines, and a payment row in one local transaction. The history index is maintained because the order insert touches it, not because the home page is in the request. The home page later seeks that index and returns. Instructor revenue does not sit on this arrow. It reads a summary projection fed from posted lines.

mermaidCopy
sequenceDiagram
    participant Learner
    participant Spring as OrderHistoryService
    participant PG as PostgreSQL
    Learner->>Spring: GET /me/orders?since=
    Spring->>PG: SELECT ... WHERE learner_id AND placed_at ORDER BY placed_at DESC LIMIT 20
    PG->>PG: Index seek on orders_learner_placed_cov
    PG->>PG: Visibility map check for index-only scan
    PG-->>Spring: 20 rows or fewer
    Spring-->>Learner: OrderHistoryRow list

If the visibility map bit is clear, the seek still happens and each qualifying leaf tuple fetches the heap. Look for that hop in Heap Fetches.

Status in the index key rewrites the leaf on every transition. Status in INCLUDE still changes an indexed column, so HOT does not apply, and the history query can skip the heap only when the visibility map allows. If history can read status from the heap, leave it out of the index and keep HOT for fulfillment updates that flip status every few seconds.

Technical Explanation

How the executor uses this index

For the history statement the planner seeks the first leaf for (learner_id, placed_at) at the lower bound of the range and walks backward because the index is declared placed_at DESC. LIMIT 20 stops the walk. The cost model charges a random page read for the index descent and then sequential leaf reads. shared_blks_hit versus shared_blks_read in EXPLAIN (ANALYZE, BUFFERS) tells you whether those pages were already in PostgreSQL's shared buffers. A plan that is "fast" in staging and slow on a cold cache is often this difference, not a different plan shape.

A sort node above the index scan means the index order did not match ORDER BY. You paid for a seek and then sorted. For a limit of twenty, an ordered index walk that stops early is the plan you wanted. A sort of a learner's entire history is the plan you get when the index prefix is only learner_id and placed_at is not the next key, or when the direction and NULLS placement disagree with the index. PostgreSQL can scan a B-tree backward, so ASC versus DESC is usually flexible. A mix of directions across columns is not. Declare the index in the order the important statement needs.

Bitmap scans, sequential scans, and BRIN

If the predicate matches many rows, the planner may bitmap the index and visit heap pages in physical order. When the bitmap exceeds work_mem, it becomes lossy: it remembers pages and rechecks every tuple. See lossy= in the plan before you call the index selective. A sequential scan of a table that fits in cache can beat an index for a non-selective predicate. enable_seqscan = off is a session experiment, not a production setting. BRIN fits a physically time-ordered export table because each entry summarizes many pages with min and max. Random keys and mid-table updates make those ranges overlap. Learner history stays on a B-tree: one equality seek and a short walk.

Locks you take while changing the access path

CREATE INDEX CONCURRENTLY allows inserts, updates, and deletes, then waits until snapshots that predate the build have ended before it marks the index valid. Idle-in-transaction sessions stretch that wait. Set lock_timeout, and check pg_stat_activity before you start. DROP INDEX CONCURRENTLY is how you retire an index idx_scan has shown to be idle. Capture the plan before and after. Two concurrent builds on one table queue behind each other.

Security and tenancy on the same tables

An index does not enforce tenant isolation. A query that forgets learner_id and filters only on placed_at will use a different index, or a sequential scan, and can return another learner's orders if the application allows it. Row Level Security can make learner_id = current_setting('app.learner_id')::uuid a policy the table enforces. The policy needs an index that matches the policy predicate or every query grows a sequential filter. Row-Level Security is the mechanism. The access path in this lesson is what keeps the policy cheap. Application-side tenant filters remain necessary for clear errors. The database policy is the backstop when a new repository method forgets the predicate.

Least privilege sits beside the index. The login the home page uses should not own the table and should not be allowed to CREATE INDEX in production. Migrations run as a role that can. Runtime reads run as a role that can SELECT the columns the DTO needs. A covering index that includes an email address or a payment token copies that value into the index file. Do not INCLUDE a secret to save a heap fetch. Fetch secrets by primary key on the rare path that is allowed to see them.

Practical Implementation or Design Walkthrough

Problem

Learners open order history on every home-page load during enrollment week. Support staff look up a single order by id. Checkout writes orders, lines, and a payment attempt in one transaction. Finance exports are offline and must not dictate the OLTP indexes.

Requirements and constraints

• History: p95 under 30 ms inside the database for a learner with up to five thousand historical orders, returning at most twenty rows, filtered by placed_at >= :since.
• Point read by id: unique, current-snapshot, under a few milliseconds.
• Checkout write: one local transaction. Losing a line without the order is a defect.
• Enrollment week write rate from lesson 24's workbook: design for 200 order inserts per second sustained, with status updates after payment at a similar rate. These are planning numbers for this exercise, not production telemetry from a live company.
• Index builds must not block checkout writes.
• One active enrollment per learner and course.

Schema

sqlCopy
CREATE SCHEMA IF NOT EXISTS commerce;

CREATE TABLE commerce.orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id uuid NOT NULL UNIQUE,
    learner_id uuid NOT NULL,
    placed_at timestamptz NOT NULL,
    status text NOT NULL CHECK (status IN ('pending', 'paid', 'failed', 'refunded')),
    total_cents integer NOT NULL CHECK (total_cents >= 0),
    currency char(3) NOT NULL
);

CREATE INDEX orders_learner_placed_cov
    ON commerce.orders (learner_id, placed_at DESC)
    INCLUDE (total_cents, status);

CREATE TABLE commerce.order_lines (
    order_id bigint NOT NULL REFERENCES commerce.orders (id),
    line_no integer NOT NULL,
    course_id uuid NOT NULL,
    unit_price_cents integer NOT NULL CHECK (unit_price_cents >= 0),
    PRIMARY KEY (order_id, line_no)
);

CREATE INDEX order_lines_course_id ON commerce.order_lines (course_id);

id is the clustering-friendly internal key. public_id is what the API already exposes, with a unique B-tree so a support lookup does not scan. unit_price_cents is the captured price. order_lines carries a primary key that starts with order_id, so the foreign key is indexed by the primary key itself. The extra index on course_id exists only because payout grouping filters by course. If that query moves to a summary table, drop order_lines_course_id and say so in the ADR.

Status is in INCLUDE, not in the key. History can return it without a heap fetch when the visibility map allows. A status update still modifies the covering index. If measurement shows status flips dominating WAL, remove status from INCLUDE and accept a heap fetch on the history path. Record the measurement that forced the change.

The statement and the plan you keep

sqlCopy
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, public_id, placed_at, total_cents, status
FROM commerce.orders
WHERE learner_id = '7d4e3c2b-1a09-4f8e-9c6d-5b4a39281706'
  AND placed_at >= timestamptz '2024-01-01'
ORDER BY placed_at DESC
LIMIT 20;

What you want to see on a realistic data set:

• An index scan or index-only scan on orders_learner_placed_cov.
• Actual rows close to estimated rows at the index condition.
• A limit that stops the scan. No Sort node above a scan of thousands of rows.
• Heap fetches near zero after VACUUM if you are claiming index-only behavior. If heap fetches dominate, either vacuum is behind or the table's update rate clears the visibility map faster than vacuum sets it. In the second case, stop claiming a covering win and size the heap fetch.

BUFFERS is required in the evidence you attach to the change. A plan without buffers cannot tell a cached demo from an I/O-bound production execution.

Application code

The service names the access path in the method and binds every parameter. String concatenation is how a history filter becomes an injection bug and how a plan cache fills with one-off statements.

javaCopy
public record OrderHistoryRow(
        long id,
        UUID publicId,
        OffsetDateTime placedAt,
        int totalCents,
        String status) {}

@Repository
public class OrderHistoryReader {
    private final JdbcClient jdbc;

    public OrderHistoryReader(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    @Transactional(readOnly = true)
    public List<OrderHistoryRow> recent(UUID learnerId, OffsetDateTime since, int limit) {
        int bounded = Math.min(Math.max(limit, 1), 50);
        return jdbc.sql("""
                SELECT id, public_id, placed_at, total_cents, status
                FROM commerce.orders
                WHERE learner_id = :learnerId
                  AND placed_at >= :since
                ORDER BY placed_at DESC
                LIMIT :limit
                """)
                .param("learnerId", learnerId)
                .param("since", since)
                .param("limit", bounded)
                .query((rs, n) -> new OrderHistoryRow(
                        rs.getLong("id"),
                        rs.getObject("public_id", UUID.class),
                        rs.getObject("placed_at", OffsetDateTime.class),
                        rs.getInt("total_cents"),
                        rs.getString("status")))
                .list();
    }
}

limit is clamped in the application so a client cannot request a million-row sort "through the index." The database still applies LIMIT. Read-only transactions on this method let a later lesson route the connection to a replica. This lesson keeps a single primary. Checkout uses a different class and a read-write transaction that inserts the order, the lines, and the payment. They do not share a repository method that sometimes writes.

Build the index on a large table

sqlCopy
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_learner_placed_cov
    ON commerce.orders (learner_id, placed_at DESC)
    INCLUDE (total_cents, status);

IF NOT EXISTS still reports success when an invalid index of that name exists. After a failed concurrent build, check pg_index.indisvalid joined to pg_class. Drop the invalid index with DROP INDEX CONCURRENTLY and build again. Run this statement outside a transaction block. PostgreSQL rejects CREATE INDEX CONCURRENTLY inside one.

Validation

• Load a staging table with skewed learners: one learner owns 5,000 orders, most own fewer than 10. Compare estimates and actuals.
• Capture buffers before and after VACUUM (ANALYZE) commerce.orders.
• Insert at the design write rate in a test and record WAL bytes per order with and without the covering index, using pg_stat_wal around the run. The delta is the amplification you are accepting.
• Attempt two concurrent activations of the same enrollment and show exactly one succeeds. That test belongs to the partial unique index, not to the history index.
• Confirm idx_scan increases when the home page is exercised and does not depend on a sequential scan in pg_stat_statements for the history text.

Failure handling

Unique violations on public_id or on the active-enrollment index become HTTP 409 with a stable error code. They are not retries. A statement timeout on history is a 503 or a degraded empty-with-error the client can distinguish from "this learner has no orders." Returning an empty list on timeout trains the client to cache the wrong answer. Connection exhaustion is a pool and query-latency problem: a slow plan holds connections, and the pool queue is the user-visible outage. Fix the plan, and set a statement timeout so one bad filter cannot occupy a connection for minutes.

Enterprise Real-World Scenarios

Scenario 1: Learner order history during enrollment week

Context. TechLearningPro serves the signed-in home page from a Spring service and PostgreSQL. The page calls order history for every load. Lesson 24's model says 4,000 history reads per second at peak and 200 checkout writes per second. The orders table holds 80 million rows after three years.

What failed. The first index was (placed_at). The predicate started with learner_id, so the planner scanned a time range and filtered learners, or it sequential-scanned when the range was wide. A second index on (learner_id) found the learner and then sorted every order that learner had ever placed. Learners who bought many courses hit the sort. work_mem spilled to disk. Connections stayed busy. Checkout latency rose because the pool was shared.

Design. The covering index in the walkthrough matches equality, order, and projection. Status stays in INCLUDE only while heap fetches in the captured plan stay near zero during peak update hours. Support lookup uses public_id's unique index, not the history index. Finance monthly totals read commerce.course_month_totals, rebuilt nightly from order_lines, so the export never gains a vote on OLTP indexes.

Operations. The enroll-week review artifact is three queries: the EXPLAIN (ANALYZE, BUFFERS) of history, pg_stat_user_indexes for the orders table, and pg_stat_statements sorted by total time for commerce.orders. Someone owns the drop list. An index that served a retired A/B filter is dropped with DROP INDEX CONCURRENTLY after one full peak with zero scans.

Security. The reader role has SELECT on id, public_id, learner_id, placed_at, total_cents, status and nothing else. Payment tokens live on commerce.payments, which this service's login cannot read.

Scenario 2: Progress events and an index on every filter

Context. The learning player writes a progress row several times a minute per active learner: position, completion flag, last error code. Product asks for filters by course, by device, by error code, and by completion. Each filter arrives as a ticket titled "add an index."

Scale. Fifty thousand updates per second at peak is within the planning model for the player. Each update changes position_seconds and updated_at. The table is the hottest write in the platform.

What goes wrong if every filter wins. Five B-trees plus a primary key means six index maintenance actions per update, HOT is impossible because every updated column is indexed somewhere, WAL volume multiplies, replicas fall behind, and autovacuum cannot freeze tuples fast enough. The ticket that made device-type search "snappy" in a demo becomes replication lag on the primary's disk.

Design that holds. The player updates one row keyed by (learner_id, course_id), with a primary key on that pair and no secondary index for optional filters. Ad-hoc investigations go to a warehouse fed by logical replication or by an export, which is a different freshness contract. One partial index serves the operational exception: WHERE error_code IS NOT NULL on (error_code, updated_at) so support can find recent failures without indexing the happy path. The partial predicate keeps the index small because most rows have a null error code and are absent from the index.

Review question. Which statement runs in the request path, and which statement is a human with a SQL client? The second one does not get a B-tree on the hot table. It gets a replica, a sample, or a warehouse. Logical Replication is one way to feed that warehouse without putting its indexes on the player primary.

Best Practices

• Write the statement, then the index. Attach the statement text and a buffers-enabled plan to the migration.
• Order B-tree columns as equality prefix, then the range or ORDER BY column the limiter needs.
• Put projected columns that you do not search in INCLUDE. Re-check heap fetches after vacuum before you call the scan index-only.
• Budget write amplification: heap, each secondary index, TOAST, WAL, and replica replay. Record WAL bytes per transaction when the write rate is high.
• Prefer HOT for hot updates. Leave frequently updated columns out of indexes unless a request-path statement requires them.
• Index foreign-key referencing columns in the migration that adds the foreign key.
• Use partial unique indexes for invariants that apply to a subset of rows. Catch the constraint failure in the service and return a specific conflict.
• Let sequential scans win for non-selective queries. Change statistics before you change planner flags.
• Build and drop large indexes with the concurrent forms, outside a transaction block, with lock_timeout set.
• Keep secrets and wide documents out of hot rows and out of INCLUDE lists.
• Give every index an owner and a retirement check against pg_stat_user_indexes.idx_scan.
• Separate OLTP access paths from analytical aggregates. Summaries must be rebuildable from the write model.
• Set statement_timeout on interactive reads so a missing predicate cannot hold a pooled connection.

Common Mistakes and Anti-Patterns

Indexing the entity instead of the statement. Twelve single-column indexes match an ORM entity and still leave a sort in the history plan. Replace them with the composite index the statement can prefix-match, then drop indexes whose idx_scan stays at zero.

Putting the range column first. (placed_at, learner_id) serves a time-window scan. Learner history needs (learner_id, placed_at DESC). Keep a time-leading index only for a statement that really searches by time across learners.

Treating INCLUDE as a guarantee. A quiet staging database shows Index Only Scan. Production updates clear the visibility map, heap fetches climb, and latency returns. Tune vacuum or put the heap fetch in the capacity model.

Unique in the service, not in the database. A check-then-insert for one active enrollment races. Two transactions both see no row. The partial unique index is the serialization point. The service check can still map the constraint failure to a clear conflict.

A random UUID primary key on a write-heavy table, unexamined. UUID version 4 is a fine external id. As the primary key of a hot insert table it scatters leaf splits. Measure WAL. Use bigint identity for the clustering key and a unique secondary UUID when the API needs one.

CREATE INDEX on the primary during peak. A non-concurrent build blocks writes. Use the concurrent form, clear idle transactions, and set lock_timeout. A failed concurrent build can leave an invalid index the planner ignores while the name still exists.

Denormalizing without a rebuild path. A JSON copy of the order on the learner row drifts when a refund updates a line and skips the JSON. Store a projection you can refill with INSERT ... SELECT, or keep the line as the only writable fact.

Troubleshooting and Design Review

Design review for a proposed index, in order:

1. Paste the statement. If there is no statement, there is no index review.
2. Mark equality, range, order, and projection on that statement.
3. Name the write rate of the table and whether updated columns sit in the index.
4. Show a plan with actuals and buffers on production-shaped skew.
5. Show the lock method for the build and the rollback drop.
6. Name the role that can read the indexed columns. Refuse secrets in INCLUDE.
7. Schedule the idx_scan check that can retire the index.

A review that skips to "B-tree is fast" has not reviewed anything.

Interview Questions and Answers

Q1. How do you order columns in a composite B-tree for a query that filters on equality and a range and then sorts?

Put equality columns first and the range or ORDER BY column immediately after them. Learner history is (learner_id, placed_at DESC): seek one learner, walk in limit order, stop after twenty rows. A leading placed_at serves "all orders in this window," a different statement. PostgreSQL uses the index for predicates that constrain a prefix of the key. One index matches one family of statements.

Q2. What does a PostgreSQL covering index guarantee?

INCLUDE stores payload columns in the leaf without making them part of the search key. It does not guarantee the heap is skipped. An index-only scan still needs the visibility map to show the page is all-visible. Otherwise EXPLAIN reports heap fetches. Quote those fetches from EXPLAIN (ANALYZE, BUFFERS) on a recently updated table before you claim the win. Leave secrets and bulky JSON out of INCLUDE. They bloat every leaf and every replica.

Q3. Why can adding an index slow the system down?

Each insert, and each update of an indexed column, maintains the leaf, writes WAL, and later asks vacuum to clean dead entries. Replicas replay that WAL. If the new index covers a column that HOT updates used to skip, write amplification jumps by more than the one new index. Cache pressure can slow reads too. Justify the index with a request-path plan that improves by more than the write tax, and show idx_scan plus WAL bytes from a load test.

Q4. When should a unique index be partial?

When the rule applies only to rows matching a predicate. Many enrollments per learner and course are valid, and only one may be active. UNIQUE (learner_id, course_id) WHERE status = 'active' rejects a double activation and keeps history. A full unique constraint rejects the history. A check-then-insert in Spring loses the race. A partial index WHERE error_code IS NOT NULL also stays small because quiet rows are absent from it. Queries that do not imply the predicate may not use the index.

Q5. What do you look for in EXPLAIN (ANALYZE, BUFFERS) on a LIMIT 20 history query?

An index scan on the composite history index, estimates close to actuals, no sort of the learner's full history, and a small buffer footprint. ANALYZE runs the query, so use a safe database. A large estimate gap is a statistics problem. Heap fetches near zero, after vacuum, support an index-only claim. High shared_blks_read means a cold cache. Lossy bitmap blocks mean the predicate was less selective than you thought or work_mem was tight. A plan without BUFFERS is incomplete evidence.

Q6. PostgreSQL requires a unique index on the referenced side of a foreign key. Why do you still create an index on the child?

The parent index proves the target exists. The child index lets a parent delete or key update find referencing rows without a sequential scan, and it serves joins from parent to child. If the child primary key already starts with the foreign-key column, that is the index. Add it in the same migration as the foreign key. ON DELETE CASCADE still performs that child lookup.

Q7. How do you add an index to a large orders table without stopping checkout?

Use CREATE INDEX CONCURRENTLY outside a transaction block. It waits for snapshots that started before the build, then marks the index valid. Set lock_timeout and clear idle-in-transaction sessions first. A failed build can leave pg_index.indisvalid = false. IF NOT EXISTS will not repair that. DROP INDEX CONCURRENTLY and rebuild. Plain CREATE INDEX belongs on a small table or in a window where blocking writes is the plan.

Q8. When do you add a summary table instead of another index?

When the statement aggregates a long range on the same machine as checkout and the product can name a staleness bound. Instructor revenue by course and month is that statement. Store course_id, month, and money in a table you can truncate and refill from order_lines. If the dashboard requires the latest committed order, say so and point the read at a replica or the primary, still without a reporting B-tree on the checkout write path.

Q9. What is a heap-only tuple update, and how do index choices defeat it?

MVCC writes a new tuple version. If no indexed column changes and the new tuple fits on the same page, PostgreSQL adds a heap-only tuple and a line-pointer redirect instead of new index entries. That is a HOT update. Any index on a column the update modifies disables HOT for that update. fillfactor below 100 leaves room on the page. Compare n_tup_hot_upd with n_tup_upd. A design that drives the ratio to zero needs a written reason.

Q10. A stakeholder asks for an index per filter on a 50,000-update-per-second table. What do you say in review?

The player updates position by primary key. Each extra B-tree is maintained on every update, shipped to every replica, and likely disables HOT. Offer a partial index for a rare operational predicate such as non-null error codes. Offer a replica or a warehouse for ad-hoc device filters. Ask for the statement, the QPS, and the staleness budget. Bring WAL measurements with and without the index. The outcome is a budget.

Q11. Estimated rows and actual rows disagree by 100x, and the planner refuses your new index. What do you change first?

Change statistics. Check the last ANALYZE, raise the statistics target on a skewed learner_id, and add CREATE STATISTICS when filters are correlated. Analyze again and recapture EXPLAIN (ANALYZE, BUFFERS). If estimates then match and a sequential scan is cheaper, the scan can be the right plan. enable_seqscan = off is a single-session experiment, not a permanent setting.

Hands-On Exercises

Exercise 1: Order-history access path

Problem. You are reviewing the TechLearningPro home page query against commerce.orders (columns as in the walkthrough: id, public_id, learner_id, placed_at, status, total_cents, currency). Peak design load is 4,000 history reads per second and 200 inserts per second. One percent of learners hold more than 2,000 orders.

Tasks.

1. Write the history SELECT with a bound limit and parameters.
2. Propose one B-tree, including column order, direction, and any INCLUDE list. Justify HOT impact if status changes after insert.
3. List the EXPLAIN (ANALYZE, BUFFERS) signals that would make you reject your own index.
4. Describe how you would build it on an 80-million-row table while checkout continues, and how you would detect an invalid index.
5. Name one query you will refuse to index on this table, and where that query goes instead.

Deliverables. SQL for the table's relevant indexes, the statement, a short ADR (decision, alternatives, write-cost, rollback), and a validation checklist.

Acceptance. The index prefix matches the statement. The plan criteria mention estimates, actuals, sort nodes, heap fetches, and buffers. The build method is concurrent and outside a transaction. The refused query has an explicit home.

Exercise 2: Partial unique enrollment

Problem. learning.enrollments has many historical rows per learner and course. At most one row per pair may have status = 'active'. Two checkout requests can race.

Tasks.

1. Write the partial unique index.
2. Explain why a non-partial unique constraint fails the product rule.
3. Show the application behavior on unique violation, using a real constraint-error path rather than a check-then-insert as the only guard.
4. Add the foreign-key index story if enrollments reference courses(id) and you will delete courses.

Rubric.

Lesson Summary

Scale for a relational write path starts with facts the database can refuse to break: keys, foreign keys, checks, and partial unique indexes where the rule covers a subset of rows. Scale for a read path starts with one statement: equality prefix, range or order column, projection, limit. A B-tree matches that shape or it matches a different product. INCLUDE stores payload. The visibility map decides whether the heap stays out of the scan. Every secondary index is write amplification, WAL, and replica work, and it can disable heap-only updates. Measure with EXPLAIN (ANALYZE, BUFFERS), pg_stat_user_indexes, pg_stat_statements, and WAL counters. Build large indexes concurrently, repair invalid ones on purpose, and keep analytical aggregates in a rebuildable summary.

The next lesson copies this database. A bad plan shows up again as lag, and the learner who just paid may read a replica that does not have the order yet. That choice is Database Replication and Consistency.

Further Reading

• PostgreSQL documentation: Indexes, Index-only scans and covering indexes, CREATE INDEX, EXPLAIN, Routine vacuuming, and Extended statistics.
• TechLearningPro PostgreSQL path: B-Tree Indexes, Covering Indexes, Partial Indexes, Composite Indexes, EXPLAIN ANALYZE, MVCC, VACUUM.
• Markus Winand, SQL Indexing and Tuning: composite column order and the limits of "index every WHERE clause."
• Spring Framework reference: JdbcClient and transaction management. The class used here is org.springframework.jdbc.core.simple.JdbcClient.
• Next in this module: Database Replication and Consistency, Database Sharding and Partitioning.
• Related platform lesson: Capacity Estimation and Workload Modeling.