Relationships, Indexes, and the N+1 Problem

This lesson models Storefront relationships in PostgreSQL, adds the indexes that queries need, reads EXPLAIN output, removes an N+1 query pattern from Rust code, and builds a read model. It builds on PostgreSQL with SQLx. Background is in How indexes work and Query plans.

Project setup

Terminal

docker run --name storefront-pg -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=storefront -p 5432:5432 -d postgres:16
export DATABASE_URL=postgres://postgres:postgres@localhost:5432/storefront
cargo install sqlx-cli --no-default-features --features rustls,postgres

Migrations are applied with sqlx migrate run. Queries are run with psql inside the container: docker exec -i storefront-pg psql -U postgres storefront.

Cargo.toml

TOML

[package]
name = "storefront-relations"
version = "0.1.0"
edition = "2021"

[dependencies]
axum = "0.8.4"
serde = { version = "1.0", features = ["derive"] }
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "derive"] }
tokio = { version = "1.44", features = ["full"] }

migrations/20260929120001_products.sql

SQL

CREATE TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         TEXT        NOT NULL UNIQUE,
    name        TEXT        NOT NULL CHECK (length(name) BETWEEN 1 AND 100),
    price_cents INTEGER     NOT NULL CHECK (price_cents > 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Example 1: Keys and constraints

Orders belong to customers. Order lines belong to orders and refer to products. A foreign key makes the database reject a row that points to nothing.

migrations/20260929120002_orders.sql

SQL

CREATE TABLE customers (
    id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT      NOT NULL REFERENCES customers (id),
    status      TEXT        NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_lines (
    order_id                BIGINT  NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    line_no                 INTEGER NOT NULL,
    product_id              BIGINT  NOT NULL REFERENCES products (id),
    quantity                INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase_cents INTEGER NOT NULL CHECK (price_at_purchase_cents > 0),
    PRIMARY KEY (order_id, line_no)
);

PRIMARY KEY is unique and not null, and creates an index.
UNIQUE rejects duplicates and creates an index.
REFERENCES rejects a value with no matching parent row.
CHECK rejects rows that fail a condition.
order_lines has a composite primary key, so a line cannot exist without its order and line numbers are unique within an order.
price_at_purchase_cents is a snapshot of the price when the order was placed, so later price changes do not alter old orders.

ON DELETE sets what happens to child rows when a parent is deleted. NO ACTION (the default) and RESTRICT make the delete fail while children exist. CASCADE deletes the children. SET NULL nulls the child column. Order lines cascade because a line has no meaning alone. Orders do not cascade from customers, because deleting a customer must not silently remove financial records.

BIGINT identity keys are compact and sort by insertion. UUID keys can be generated by clients and hide counts, but random UUIDs spread inserts across the index and take 16 bytes instead of 8.

Example 2: Many-to-many with a junction table

A one-to-many relationship puts the foreign key on the "many" side (orders.customer_id). A many-to-many relationship needs a junction table: a product has many categories, and a category has many products.

migrations/20260929120003_categories.sql

SQL

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

CREATE TABLE product_categories (
    product_id  BIGINT NOT NULL REFERENCES products (id)   ON DELETE CASCADE,
    category_id BIGINT NOT NULL REFERENCES categories (id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, category_id)
);

The composite primary key prevents the same pair twice and serves lookups by product_id. Lookups by category_id need their own index (Example 4). A junction table can carry its own columns, such as position.

PostgreSQL does not index foreign key columns automatically. A foreign key column that appears in joins, in WHERE, or in deletes of the parent table usually needs an index. Without one, deleting a product scans order_lines to check for references.

Example 3: Read a query plan

EXPLAIN shows the plan PostgreSQL chooses. EXPLAIN (ANALYZE, BUFFERS) also runs the statement and reports actual times, row counts, and pages read, so it must not be pointed at a write statement on real data. Plans depend on data volume, so this seed script loads 100,000 orders spread over 1,000 customers.

seed.sql

SQL

INSERT INTO products (sku, name, price_cents)
SELECT 'SKU-' || g, 'Product ' || g, 100 + g
FROM generate_series(1, 50) AS g;

INSERT INTO customers (email)
SELECT 'user' || g || '@example.com'
FROM generate_series(1, 1000) AS g;

INSERT INTO orders (customer_id, created_at)
SELECT 1 + (g % 1000), now() - (g || ' minutes')::interval
FROM generate_series(1, 100000) AS g;

INSERT INTO order_lines (order_id, line_no, product_id, quantity, price_at_purchase_cents)
SELECT o.id, n, 1 + ((o.id + n) % 50), 1 + (n % 3), 101 + ((o.id + n) % 50)
FROM orders o
CROSS JOIN generate_series(1, 3) AS n;

ANALYZE;

Apply the migrations, load the seed, and explain the customer order list.

Terminal

sqlx migrate run
docker exec -i storefront-pg psql -U postgres storefront < seed.sql
docker exec -i storefront-pg psql -U postgres storefront -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id, status FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;"

With no index on customer_id, the plan reads every order: expect a Seq Scan on orders with a filter on customer_id, followed by a Sort node. Timings differ per machine, so read the node names and the rows= and actual ... rows= values.

Seq Scan reads every row. It is correct on small tables and signals a missing index under a selective WHERE on large ones.
Index Scan walks an index and fetches matching rows. Index Only Scan answers from the index alone.
Bitmap Heap Scan collects row locations from an index, then reads pages in order.
Sort above a scan for ORDER BY ... LIMIT can mean the index does not match the sort order.
Estimated rows= far from actual rows means stale statistics. ANALYZE refreshes them.
Joins appear as Nested Loop, Hash Join, or Merge Join.

Example 4: Add an index for the query

An index is a sorted structure (a B-tree by default) that maps column values to row locations. It costs storage and slows writes, because every insert and update also maintains each index on the table.

The query filters on customer_id and sorts by created_at, so one composite index serves both. Column order matters: the index is sorted by customer_id first, then by created_at within each customer. It helps queries that filter on customer_id, or on customer_id and created_at. It does not help a query that filters only on created_at.

migrations/20260929120004_indexes.sql

SQL

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);
CREATE INDEX order_lines_product_idx ON order_lines (product_id);
CREATE INDEX product_categories_category_idx ON product_categories (category_id);

Terminal

sqlx migrate run
docker exec -i storefront-pg psql -U postgres storefront -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id, status FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;"

The plan now starts from Index Scan using orders_customer_created_idx on orders, and the Sort node is gone because the index already returns rows in created_at DESC order. The file also indexes the two other foreign key lookups from Example 2.

Other index forms use the same CREATE INDEX statement:

Unique: CREATE UNIQUE INDEX enforces uniqueness on an expression or subset of rows.
Partial: ... ON orders (created_at) WHERE status = 'pending' indexes only rows a hot query reads.
Covering: ... ON orders (customer_id) INCLUDE (status) answers from the index alone.
Expression: ... ON customers (lower(email)) supports WHERE lower(email) = $1.
On a large live table, CREATE INDEX CONCURRENTLY builds without blocking writes. It cannot run in a transaction, so it needs its own migration file that starts with the line -- no-transaction.

Example 5: The N+1 problem

N+1 is one query that loads a list, followed by one query for each item. Twenty orders cost 21 queries, and each one pays a network round trip and a pool checkout. The count grows with the page size and stays hidden in tests with two rows.

The fix loads the lines of the whole page in one query. = ANY($1) matches any value in a bound array. The lines are grouped by order in memory. Both functions return the number of queries they ran, so the program can print it.

src/orders.rs

Rust

use std::collections::HashMap;

use serde::Serialize;
use sqlx::PgPool;

#[derive(Debug, Clone, PartialEq, Serialize, sqlx::FromRow)]
pub struct LineRow {
    pub order_id: i64,
    pub line_no: i32,
    pub product_id: i64,
    pub quantity: i32,
    pub price_at_purchase_cents: i32,
}

#[derive(Debug, sqlx::FromRow)]
struct OrderRow {
    id: i64,
    status: String,
}

#[derive(Debug, PartialEq, Serialize)]
pub struct OrderDto {
    pub id: i64,
    pub status: String,
    pub lines: Vec<LineRow>,
}

const ORDERS_SQL: &str =
    "SELECT id, status FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20";

/// One query for the orders, then one per order. Returns the result and the number of queries run.
pub async fn orders_naive(pool: &PgPool, customer_id: i64) -> Result<(Vec<OrderDto>, usize), sqlx::Error> {
    let orders = sqlx::query_as::<_, OrderRow>(ORDERS_SQL)
        .bind(customer_id)
        .fetch_all(pool)
        .await?;
    let mut queries = 1;

    let mut result = Vec::new();
    for order in orders {
        let lines = sqlx::query_as::<_, LineRow>(
            "SELECT order_id, line_no, product_id, quantity, price_at_purchase_cents
             FROM order_lines WHERE order_id = $1 ORDER BY line_no",
        )
        .bind(order.id)
        .fetch_all(pool)
        .await?;
        queries += 1;
        result.push(OrderDto { id: order.id, status: order.status, lines });
    }
    Ok((result, queries))
}

/// Two queries in total: the orders, then every line of those orders at once.
pub async fn orders_batched(pool: &PgPool, customer_id: i64) -> Result<(Vec<OrderDto>, usize), sqlx::Error> {
    let orders = sqlx::query_as::<_, OrderRow>(ORDERS_SQL)
        .bind(customer_id)
        .fetch_all(pool)
        .await?;

    let ids: Vec<i64> = orders.iter().map(|o| o.id).collect();
    let lines = sqlx::query_as::<_, LineRow>(
        "SELECT order_id, line_no, product_id, quantity, price_at_purchase_cents
         FROM order_lines WHERE order_id = ANY($1) ORDER BY order_id, line_no",
    )
    .bind(&ids)
    .fetch_all(pool)
    .await?;

    let mut by_order: HashMap<i64, Vec<LineRow>> = HashMap::new();
    for line in lines {
        by_order.entry(line.order_id).or_default().push(line);
    }

    let result = orders
        .into_iter()
        .map(|o| OrderDto {
            lines: by_order.remove(&o.id).unwrap_or_default(),
            id: o.id,
            status: o.status,
        })
        .collect();
    Ok((result, 2))
}

src/main.rs

Rust

mod orders;

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&std::env::var("DATABASE_URL")?)
        .await?;

    let (naive, naive_queries) = orders::orders_naive(&pool, 42).await?;
    let (batched, batched_queries) = orders::orders_batched(&pool, 42).await?;

    let count_lines = |list: &[orders::OrderDto]| list.iter().map(|o| o.lines.len()).sum::<usize>();
    println!("naive:   orders={} lines={} queries={}", naive.len(), count_lines(&naive), naive_queries);
    println!("batched: orders={} lines={} queries={}", batched.len(), count_lines(&batched), batched_queries);
    println!("same result: {}", naive == batched);
    Ok(())
}

Output

naive:   orders=20 lines=60 queries=21
batched: orders=20 lines=60 queries=2
same result: true

Customer 42 has 100 orders. The query returns the newest 20, and each order has 3 lines.
The batched version runs 2 queries for any page size. The primary key (order_id, line_no) serves the ANY lookup.
A single LEFT JOIN also works, but LIMIT 20 would then limit line rows instead of orders. Selecting the page of orders first avoids this.
In a running service, N+1 shows up as many similar statements per request in the statement log or in traces. The observability lesson covers tracing.

Example 6: Read models

An order total can be computed on every request:

SQL

SELECT o.id, SUM(l.quantity::bigint * l.price_at_purchase_cents)::bigint AS total_cents
FROM orders o
JOIN order_lines l ON l.order_id = o.id
WHERE o.customer_id = 42
GROUP BY o.id
ORDER BY o.created_at DESC
LIMIT 20;

This is correct and needs no extra storage, and it reads every line of the customer's recent orders on each request. A read model is a structure shaped for one read:

A view is computed on every query. It gives no speed gain.
A materialized view stores the result and is refreshed with REFRESH MATERIALIZED VIEW. It is stale between refreshes.
A summary table is a normal table that the write path or an event consumer keeps current. Reads are fast, and every write path that affects it must maintain it.

This lesson uses a summary table. The migration creates it, and the backfill fills it from existing orders. New orders must insert their summary row in the same transaction as the order, which the transactions lesson covers. A periodic job that recomputes summaries and compares them detects drift. The same idea across services is a local copy built from events, as in Decomposition and data ownership and CQRS.

migrations/20260929120005_order_summaries.sql

SQL

CREATE TABLE order_summaries (
    order_id    BIGINT      PRIMARY KEY REFERENCES orders (id) ON DELETE CASCADE,
    customer_id BIGINT      NOT NULL,
    status      TEXT        NOT NULL,
    total_cents BIGINT      NOT NULL,
    line_count  INTEGER     NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL
);

CREATE INDEX order_summaries_customer_idx ON order_summaries (customer_id, created_at DESC);

backfill.sql

SQL

INSERT INTO order_summaries (order_id, customer_id, status, total_cents, line_count, created_at)
SELECT o.id,
       o.customer_id,
       o.status,
       COALESCE(SUM(l.quantity::bigint * l.price_at_purchase_cents), 0)::bigint,
       COUNT(l.line_no)::int,
       o.created_at
FROM orders o
LEFT JOIN order_lines l ON l.order_id = o.id
GROUP BY o.id;

src/summaries.rs

Rust

use serde::Serialize;
use sqlx::PgPool;

#[derive(Debug, Serialize, sqlx::FromRow)]
pub struct Summary {
    pub order_id: i64,
    pub status: String,
    pub total_cents: i64,
    pub line_count: i32,
}

pub async fn recent(pool: &PgPool, customer_id: i64) -> Result<Vec<Summary>, sqlx::Error> {
    sqlx::query_as::<_, Summary>(
        "SELECT order_id, status, total_cents, line_count
         FROM order_summaries
         WHERE customer_id = $1
         ORDER BY created_at DESC
         LIMIT 20",
    )
    .bind(customer_id)
    .fetch_all(pool)
    .await
}

Complete example

The service exposes the batched order list and the summary list. Everything below is needed to run it from an empty directory.

Text

storefront-relations/
  Cargo.toml
  seed.sql
  backfill.sql
  migrations/
    20260929120001_products.sql
    20260929120002_orders.sql
    20260929120003_categories.sql
    20260929120004_indexes.sql
    20260929120005_order_summaries.sql
  src/
    main.rs
    orders.rs
    summaries.rs

Cargo.toml

TOML

[package]
name = "storefront-relations"
version = "0.1.0"
edition = "2021"

[dependencies]
axum = "0.8.4"
serde = { version = "1.0", features = ["derive"] }
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "derive"] }
tokio = { version = "1.44", features = ["full"] }

migrations/20260929120001_products.sql

SQL

CREATE TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         TEXT        NOT NULL UNIQUE,
    name        TEXT        NOT NULL CHECK (length(name) BETWEEN 1 AND 100),
    price_cents INTEGER     NOT NULL CHECK (price_cents > 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

migrations/20260929120002_orders.sql

SQL

CREATE TABLE customers (
    id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT      NOT NULL REFERENCES customers (id),
    status      TEXT        NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_lines (
    order_id                BIGINT  NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    line_no                 INTEGER NOT NULL,
    product_id              BIGINT  NOT NULL REFERENCES products (id),
    quantity                INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase_cents INTEGER NOT NULL CHECK (price_at_purchase_cents > 0),
    PRIMARY KEY (order_id, line_no)
);

migrations/20260929120003_categories.sql

SQL

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

CREATE TABLE product_categories (
    product_id  BIGINT NOT NULL REFERENCES products (id)   ON DELETE CASCADE,
    category_id BIGINT NOT NULL REFERENCES categories (id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, category_id)
);

migrations/20260929120004_indexes.sql

SQL

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);
CREATE INDEX order_lines_product_idx ON order_lines (product_id);
CREATE INDEX product_categories_category_idx ON product_categories (category_id);

migrations/20260929120005_order_summaries.sql

SQL

CREATE TABLE order_summaries (
    order_id    BIGINT      PRIMARY KEY REFERENCES orders (id) ON DELETE CASCADE,
    customer_id BIGINT      NOT NULL,
    status      TEXT        NOT NULL,
    total_cents BIGINT      NOT NULL,
    line_count  INTEGER     NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL
);

CREATE INDEX order_summaries_customer_idx ON order_summaries (customer_id, created_at DESC);

seed.sql

SQL

INSERT INTO products (sku, name, price_cents)
SELECT 'SKU-' || g, 'Product ' || g, 100 + g
FROM generate_series(1, 50) AS g;

INSERT INTO customers (email)
SELECT 'user' || g || '@example.com'
FROM generate_series(1, 1000) AS g;

INSERT INTO orders (customer_id, created_at)
SELECT 1 + (g % 1000), now() - (g || ' minutes')::interval
FROM generate_series(1, 100000) AS g;

INSERT INTO order_lines (order_id, line_no, product_id, quantity, price_at_purchase_cents)
SELECT o.id, n, 1 + ((o.id + n) % 50), 1 + (n % 3), 101 + ((o.id + n) % 50)
FROM orders o
CROSS JOIN generate_series(1, 3) AS n;

ANALYZE;

backfill.sql

SQL

INSERT INTO order_summaries (order_id, customer_id, status, total_cents, line_count, created_at)
SELECT o.id,
       o.customer_id,
       o.status,
       COALESCE(SUM(l.quantity::bigint * l.price_at_purchase_cents), 0)::bigint,
       COUNT(l.line_no)::int,
       o.created_at
FROM orders o
LEFT JOIN order_lines l ON l.order_id = o.id
GROUP BY o.id;

src/orders.rs

Rust

use std::collections::HashMap;

use serde::Serialize;
use sqlx::PgPool;

#[derive(Debug, Clone, PartialEq, Serialize, sqlx::FromRow)]
pub struct LineRow {
    pub order_id: i64,
    pub line_no: i32,
    pub product_id: i64,
    pub quantity: i32,
    pub price_at_purchase_cents: i32,
}

#[derive(Debug, sqlx::FromRow)]
struct OrderRow {
    id: i64,
    status: String,
}

#[derive(Debug, PartialEq, Serialize)]
pub struct OrderDto {
    pub id: i64,
    pub status: String,
    pub lines: Vec<LineRow>,
}

const ORDERS_SQL: &str =
    "SELECT id, status FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20";

/// One query for the orders, then one per order. Returns the result and the number of queries run.
pub async fn orders_naive(pool: &PgPool, customer_id: i64) -> Result<(Vec<OrderDto>, usize), sqlx::Error> {
    let orders = sqlx::query_as::<_, OrderRow>(ORDERS_SQL)
        .bind(customer_id)
        .fetch_all(pool)
        .await?;
    let mut queries = 1;

    let mut result = Vec::new();
    for order in orders {
        let lines = sqlx::query_as::<_, LineRow>(
            "SELECT order_id, line_no, product_id, quantity, price_at_purchase_cents
             FROM order_lines WHERE order_id = $1 ORDER BY line_no",
        )
        .bind(order.id)
        .fetch_all(pool)
        .await?;
        queries += 1;
        result.push(OrderDto { id: order.id, status: order.status, lines });
    }
    Ok((result, queries))
}

/// Two queries in total: the orders, then every line of those orders at once.
pub async fn orders_batched(pool: &PgPool, customer_id: i64) -> Result<(Vec<OrderDto>, usize), sqlx::Error> {
    let orders = sqlx::query_as::<_, OrderRow>(ORDERS_SQL)
        .bind(customer_id)
        .fetch_all(pool)
        .await?;

    let ids: Vec<i64> = orders.iter().map(|o| o.id).collect();
    let lines = sqlx::query_as::<_, LineRow>(
        "SELECT order_id, line_no, product_id, quantity, price_at_purchase_cents
         FROM order_lines WHERE order_id = ANY($1) ORDER BY order_id, line_no",
    )
    .bind(&ids)
    .fetch_all(pool)
    .await?;

    let mut by_order: HashMap<i64, Vec<LineRow>> = HashMap::new();
    for line in lines {
        by_order.entry(line.order_id).or_default().push(line);
    }

    let result = orders
        .into_iter()
        .map(|o| OrderDto {
            lines: by_order.remove(&o.id).unwrap_or_default(),
            id: o.id,
            status: o.status,
        })
        .collect();
    Ok((result, 2))
}

src/summaries.rs

Rust

use serde::Serialize;
use sqlx::PgPool;

#[derive(Debug, Serialize, sqlx::FromRow)]
pub struct Summary {
    pub order_id: i64,
    pub status: String,
    pub total_cents: i64,
    pub line_count: i32,
}

pub async fn recent(pool: &PgPool, customer_id: i64) -> Result<Vec<Summary>, sqlx::Error> {
    sqlx::query_as::<_, Summary>(
        "SELECT order_id, status, total_cents, line_count
         FROM order_summaries
         WHERE customer_id = $1
         ORDER BY created_at DESC
         LIMIT 20",
    )
    .bind(customer_id)
    .fetch_all(pool)
    .await
}

src/main.rs

Rust

mod orders;
mod summaries;

use axum::{
    extract::{Path, State},
    http::StatusCode,
    routing::get,
    Json, Router,
};
use sqlx::{postgres::PgPoolOptions, PgPool};

#[derive(Clone)]
struct AppState {
    pool: PgPool,
}

fn internal(err: sqlx::Error) -> StatusCode {
    eprintln!("query failed: {err}");
    StatusCode::INTERNAL_SERVER_ERROR
}

async fn list_orders(
    State(state): State<AppState>,
    Path(customer_id): Path<i64>,
) -> Result<Json<Vec<orders::OrderDto>>, StatusCode> {
    let (list, _queries) = orders::orders_batched(&state.pool, customer_id)
        .await
        .map_err(internal)?;
    Ok(Json(list))
}

async fn list_summaries(
    State(state): State<AppState>,
    Path(customer_id): Path<i64>,
) -> Result<Json<Vec<summaries::Summary>>, StatusCode> {
    let list = summaries::recent(&state.pool, customer_id).await.map_err(internal)?;
    Ok(Json(list))
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .connect(&std::env::var("DATABASE_URL")?)
        .await?;

    let app = Router::new()
        .route("/customers/{id}/orders", get(list_orders))
        .route("/customers/{id}/order-summaries", get(list_summaries))
        .with_state(AppState { pool });

    let listener = tokio::net::TcpListener::bind("127.0.0.1:3000").await?;
    axum::serve(listener, app).await?;
    Ok(())
}

Start a fresh database, wait a few seconds until PostgreSQL accepts connections, then migrate, load the data, and run the service. The service does not call orders_naive. Example 5 shows its comparison program.

Terminal

docker rm -f storefront-pg
docker run --name storefront-pg -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=storefront -p 5432:5432 -d postgres:16
export DATABASE_URL=postgres://postgres:postgres@localhost:5432/storefront
sqlx migrate run
docker exec -i storefront-pg psql -U postgres storefront < seed.sql
docker exec -i storefront-pg psql -U postgres storefront < backfill.sql
cargo run

Request the orders and the summaries for customer 42. The commands need jq and use it to print counts and one field set, so the output does not depend on ids or dates.

Terminal

curl -s localhost:3000/customers/42/orders | jq 'length, (.[0].lines | length)'
curl -s localhost:3000/customers/42/order-summaries | jq 'length'
curl -s localhost:3000/customers/42/order-summaries | jq -c '.[0] | {status, line_count}'

Output

20
3
20
{"status":"pending","line_count":3}

Summary

A foreign key rejects rows that point to nothing. PostgreSQL does not index foreign key columns, so add the index when queries join or filter on them.
ON DELETE CASCADE fits child rows that have no meaning without the parent. The default restrictive action protects records that must be kept.
Many-to-many relationships need a junction table with a composite primary key.
A composite index serves queries that filter on a leftmost prefix of its columns and can also return rows in its sort order.
EXPLAIN (ANALYZE, BUFFERS) shows scan types, estimates, and timings. Compare estimated and actual rows, and look for Seq Scan on large tables and unexpected Sort nodes.
N+1 is one query for a list plus one per item. One query with = ANY($1) and in-memory grouping replaces it.
LIMIT on a join limits joined rows, so page the parent rows first.
A read model trades write-path work and possible staleness for cheap reads.