Volume tests (pinned query plans)

Most data-layer regressions only show with data volume. A test that seeds a handful of rows stays green when a statement's plan degrades to "decompress every batch" or "one probe per historical row". The volume tests seed a realistic data set into TimescaleDB and pin the plan that production statements get on it.

• Fixture: apps/api/tests/common/volume.rs
• Pinned plans: apps/api/tests/volume_plans.rs (Bazel target //apps/api:integration_volume_plans, tag requires-tsdb)

Assert plan counters, never milliseconds

Every pin runs EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) and asserts structural facts:

• node types (no Sort, no Seq Scan, one ModifyHypertable, …)
• Chunks Visited and the chunks a plan touches
• Batches checked by bloom / Batches pruned by bloom
• Batches decompressed, Batches deleted, Tuples decompressed
• Tuples Inserted, Conflicting Tuples, conflict arbiter indexes
• Actual Loops of scans on a table (per-row probes)
• shared buffers touched or dirtied, with a budget of at least 3× the measured count

Timings are printed for humans only. The test host is shared and CPU-capped, so a millisecond assertion would be a flaky test.

Profiles

	small	large
Used by	every PR (CI job API tsdb Migration Test)	manual, before a data design check on a hot table
Accounts	20	200
Chat rows	200 000 over 21 days	4 000 000 over 14 days
Events	20 000	200 000
Crawler	2 000 live streams × 60 one-minute samples	30 000 live streams × 120 one-minute samples
Crawler streams in total	6 000 (1 live + 2 ended per channel)	90 000
platform_users (main Postgres, P11 only)	10 000	157 000
Database size	~110 MB	~2.3 GB
Seed time on the host test stack	~7 s, once per server and day	~3.5 min, every run

The data has the shape of the capacity model:

• Skewed accounts. Each row picks its account as 1 + floor(N * random()^3). In large the biggest account holds about 17 % of the rows; in small it holds about 37 %. Chatters are skewed with floor(C * random()^2).
• Several chunks, mostly compressed. Every chunk older than its hypertable's compress_after is compressed. Most plan regressions only show on compressed chunks. The newest chunk holds the anchor time and stays uncompressed. The anchor is now(), moved to at least 30 % into its chunk.
• Realistic row widths. Chat rows are about 735 B including indexes and carry JSONB badges and emotes, a 36-character platform_message_id and an avatar URL. Events carry a JSONB payload of about 1 KB.
• Crawler rows. Crawler sessions and streams have account_id set to the nil UUID and source = 'external_crawler'. The fixture leaves the crawler sessions open - the worst case for the first-party work list: a crawler that is down, or rows written before the crawler's close sweep (ZAF-2193).

Running

Small profile (all non-ignored pins, ~10 s on a warm server):

DATABASE_URL="$TEST_TSDB_DATABASE_URL" RUN_TSDB_MIGRATION_TEST=1 \
  cargo test -p lumio-api --test volume_plans

Large profile (every pin plus one report table, ~4 min):

DATABASE_URL="$TEST_TSDB_DATABASE_URL" RUN_TSDB_MIGRATION_TEST=1 \
  cargo test -p lumio-api --test volume_plans -- --ignored large --nocapture

The large run checks free disk space first. It needs at least 20 GiB free on /workspace, or on / if /workspace does not exist; set VOLUME_DISK_GUARD_PATH to check another path. It seeds into its own lumio_vol_<unix>_<uuid> database and drops it at the end, including when it fails. It asserts the pins that are green on small. For pending pins, the report says whether they still fail as expected or now pass, in which case you remove their #[ignore].

Human developers point DATABASE_URL at the Dev Stack's TimescaleDB server (see Installation).

How the fixture works

• Real schema. The fixture applies the real apps/api/tsdb_migrations tree, never a copy. It deschedules every background job right after the migrations, because add_job fires immediately and a policy would race the seed.
• Deterministic seed. Seeding is set-based: setseed(0.42), generate_series, rows written in time order, one day per statement, no round trip per row. Ids are md5('zaf2196-…' || n)::uuid, so a test can name any seeded row. fixture_small_shape pins checksums of the seeded ids and values. If you change the seed SQL, bump SEED_VERSION and re-pin the checksums.
• One template per day. small is seeded once per server, migration set and UTC day into the template lumio_vtmpl_<hash>_<yyyymmdd>. Each test gets a CREATE DATABASE … TEMPLATE clone, which is dropped when the test ends. Clones a killed process left behind, half-built templates and templates older than two days are pruned the next time a template is built. Never touch lumio_v* databases by hand.
• TimescaleDB scheduler. TimescaleDB keeps a background scheduler connected to every database that has the extension, and CREATE DATABASE … TEMPLATE fails while anyone is connected. Renaming a database stops its scheduler, and ALLOW_CONNECTIONS false keeps it from coming back. The fixture therefore builds the template as lumio_vbuild_… and renames it when complete; the rename also marks the template as finished. common::test_db() parks its own template under another name and back for the same reason, so it also works on a TimescaleDB server.

Pinned plans

#	Statement (source)	Pinned property	Status
P1	Chat history first page (lo_chat::queries::list_messages_before, floored at the 30-day walk window)	1 chunk visited, no decompression, buffer budget; ordered per-account index scan without Sort for a typical account; an unknown account visits at most window + 1 chunks, decompresses no tuple and stays within a per-chunk and an absolute buffer budget (planning + execution, warm connection); the window-edge catalog probe opens no data chunk; end to end the unknown account's first page ends its history. p1_aged_small adds 365 compressed one-day chunks (rolled back) and proves the unbounded page would blow the absolute budget. The plan shape production runs is pinned too (ZAF-2254): both keyset reads leave no prepared statement after six runs on one connection (so every request gets a custom plan), the page sent with bound values on an unnamed statement stays bounded on every one of six runs, and the generic plan a cached statement could switch to lists every chunk	green
P2	Plan retention delete (delete_old_messages, one slice of the sweep's walk: lower bound at the oldest chunk), cutoff inside a compressed chunk	batches below the cutoff deleted by metadata, ≤ 2 % of the account's batches decompressed	green; needs the explicit compress_orderby (ZAF-2197)
P3	Single-message soft-delete (soft_delete_message) as a prepared statement, generic plan	≥ 90 % of the checked batches pruned by bloom; hit, repeat and unknown id	green
P4	Ban/timeout soft-delete (soft_delete_user_messages)	Tuples decompressed < 10 000; every message of the chatter in the 24 h window flagged, another chatter untouched	green
P5	Event dedup probe (lo_events::queries::insert_event)	only chunks inside the 7-day dedup window touched	green
P6	Chat flush insert, 1 000 rows (insert_messages_batch)	one ModifyHypertable, 0 conflicts, ≤ 3 buffers dirtied per row; the replay inserts nothing	green
P7	GDPR erasure by (platform, user_id) (delete_messages_for_platform_user)	the user_id bloom keeps every batch without the subject closed: decompressed batches ≤ erased rows + 2 % of the checked batches	green
P8	Public browse COUNT on the direct path (public_stats_browse::browse_channels_from_streams, used until the browse read model is backfilled): generic plan for the default page and the live filter, custom plan for the live filter	only open stream rows probe channel_history_stats, each chunk scanned at most once per open row and at most two scans per open row (the freshness window's chunks); closed rows never probe	green
P9	Sampler work list (channel_history::list_open_sessions)	no crawler (nil-account) session; first-party sessions still listed	green
P10	Crawler UNNEST sample insert (apps/crawler-twitch/src/writer.rs)	one statement, arbiter idx_channel_history_stats_sample, an unchanged re-poll updates nothing	green
P11	Chat-flush platform_users write (lo_chat::upsert_chatter_batch), main Postgres	≥ 50 % HOT updates (n_tup_hot_upd / n_tup_upd)	green
P12	Event list (lo_events::list_events): the dashboard home feed page and its capped count, without from, after 365 days of compressed history are added (rolled back)	page and count visit at most the chunks of the 30-day default window + 2, also for a filter nothing matches; the count's inner LIMIT is LIST_TOTAL_CAP + 1; the same statements over the whole history open more than twice that, proving the window bounds them	green
P13	Crawler close sweep (apps/crawler-twitch/src/writer.rs close_stale_streams), generic plan: steady state and backlog	steady state closes nothing, backlog closes every stale live stream in one batch; no compressed channel_history_stats chunk scanned, no tuple decompressed	green
P14	Crawler stream + session upserts (writer.rs write_stream_rows) for every open crawled stream as one flush	one statement each, arbiter uq_channel_history_streams_public_key; an unchanged re-poll updates nothing; a peak rise updates every row and some updates are HOT (no index covers peak_viewers)	green
P15	Crawler stream-metadata diff (writer.rs write_stream_metadata_history) for the same flush	one statement; the latest value per broadcast and field comes from idx_public_stream_metadata_history_broadcast; no sequential scan of the stream or history table; an unchanged re-observation inserts nothing	green
P16	Public browse on the read model (public_stats_browse::read_model_*_sql): every channel page (6 sorts × all / live / offline), page 200, the COUNTs, search, category page and count, category + search, Games page / search / count — after extra closed streams are seeded (small 100 k / 10 k channels; large 1 M / 50 k) plus one stream in each of 25 000 extra categories and the read model is built with the worker steps, then again at twice the streams	no read of channel_history_streams / channel_history_stats; buffer budgets; the index top-N shapes cost the same at 2× the streams. large (1 M → 2 M streams, 30 k open, 2026-10-14): pages ≤ 0.1 ms, live / offline tab ≤ 3.2 ms (O(live channels) when the planner reads the live table in full), page 200 ≤ 6.4 ms, COUNTs ≤ 3.3 ms, Games over 25 k categories ≤ 40 ms. Category + search walks the category's pairs (O(category), not O(page)): ≤ 10 ms for a ~6 k-pair category; if a real category approaches 100 k pairs, copy the channel identity onto the pair grain. Manual run of this pin alone: -- --ignored p16_large --nocapture	green
P17	Bot-module chat:delete (lo_chat::get_message_text + soft_delete_platform_message) with the 6 h lookback bound as a literal (the statements run unprepared): a fresh message, and a message from the oldest, compressed chunk	every statement plans against ≤ 2 chunks and probes idx_platform_chat_messages_dedup; the fresh message is found and flagged, the old one is out of bound: nothing found, no tuple decompressed	green

Writing a pin

1. Run the production SQL. When the statement is assembled at run time, call the public builder production uses (P16: read_model_channel_sql and its siblings). Otherwise read the statement out of its source file with source_fn_sql(SOURCE, "function", "fragment") or source_const(SOURCE, "CONST"), using include_str! on the file. If the file lives outside apps/api, also add it to the target's compile_data in apps/api/BUILD.bazel and to exports_files in its own package. A rewritten statement then fails the pin loudly instead of silently testing stale SQL. A statement assembled with a QueryBuilder is exposed by its crate as a push_* builder function instead (P12: lo_events::push_list_events_count / push_list_events_page); the pin starts a builder with explain("") and pushes the production statement, binds included.
2. Pick the plan production gets. sqlx runs statements as prepared statements, and after a few executions Postgres may switch to the generic plan. Postgres keeps using custom plans while they are cheaper on average, so production runs both shapes. If a parameter decides the plan (for example $4 IS NULL OR …), pin the generic plan with SET LOCAL plan_cache_mode = force_generic_plan + PREPARE + EXPLAIN … EXECUTE, and pin the custom plan (force_custom_plan) too for every parameter value that needs the part under test. A custom plan with a NULL parameter can legitimately drop the join you meant to test, so that case is not a pin.
3. Keep the data unchanged. Run mutating statements inside a transaction and roll it back.
4. Assert counters with the Plan helpers: executed_kinds(), sum("Batches pruned by bloom"), shared_buffers(), scanned_relations(). TimescaleDB nests a DeferredChunkAppend child under Plan rather than Plans, and a compressed chunk's batches are scanned as <chunk>_compressed. The helpers handle both.
5. Cover the happy path and the failure cases: a hit and a miss, a replay or repeat (idempotency), an unknown id.
6. If the fix is still pending, mark the test #[ignore = "ZAF-…: reason"] and remove the ignore in the PR that ships the fix. Add the pin to the large_profile_report list.