Found by the third AWS benchmark run (2026-08-17, db.m7g.4xlarge, 12,000 events/s at batch 10, pg_stat_statements + a 5-second pg_stat_wal/pg_stat_io sampler; evidence in the benchmark write-up § third run, "Product side").
Measured, steady state (44 min, 28.8M events):
- 1,228 bytes of WAL and 7.68 WAL records per event; full-page images are not the driver (0.05 FPI/event with RDS's
wal_compression=zstd).
- The event insert executes as one statement per event:
ingestOneTx (internal/usage/postgres.go) — WITH rate AS (SELECT … provider_cost_rates …) INSERT … RETURNING — 36.1M calls for 36.1M events; IngestBatch composes N of them per transaction, i.e. N round trips per batch. That statement alone wrote 42 GB of WAL over the series (1,164 B/event).
- Three foreign-key
FOR KEY SHARE lookups per event (customers, meters, tenants — 56M calls each incl. the seed): the tenants check locks the same row 12,000×/s and shows up in Performance Insights as LWLock:MultiXactGen; the RI locks themselves generate WAL (0.2–0.5 GB each over the series).
- Index growth per event:
idx_usage_events_customer_meter 121 B, (tenant_id, livemode, idempotency_key) 117 B, pkey (random hex) 88 B, idx_usage_events_tenant_time 37 B, GIN 9 B; heap 247 B.
Why it matters: every storage-side limit the benchmark hit (checkpoint bursts at the volume's IOPS ceiling at 15–25k ev/s, the WAL-segment-pool stalls at 12k, the WAL rate that sizes max_wal_size) scales with WAL bytes per event. Halving WAL/event moves all of them.
Levers, in order of leverage vs risk (not built yet — this is the numbered lead):
- Multi-row
INSERT … SELECT FROM unnest($1::text[], …) per batch (one statement, one RETURNING) — fewer WAL records/round trips; the cost-rate CTE can be applied per row in the same statement. Keep ON CONFLICT DO NOTHING semantics per row.
- The tenants FK on
usage_events: tenant is already established by auth on every ingest path; a hot-row KEY SHARE per event buys nothing. Options: drop that FK (tenant_id stays NOT NULL + RLS), or keep it NOT VALID. Customers/meters FKs are the real integrity checks — measure their cost separately.
- Index set / key shape: two random-key btrees (pkey, idempotency) vs time-ordered ids — a later ADR question, and the largest but most invasive lever.
Follows the money-path playbook (ingest is on it): enumerate the site-set before touching ingestOneTx.
Found by the third AWS benchmark run (2026-08-17,
db.m7g.4xlarge, 12,000 events/s at batch 10,pg_stat_statements+ a 5-secondpg_stat_wal/pg_stat_iosampler; evidence in the benchmark write-up § third run, "Product side").Measured, steady state (44 min, 28.8M events):
wal_compression=zstd).ingestOneTx(internal/usage/postgres.go) —WITH rate AS (SELECT … provider_cost_rates …) INSERT … RETURNING— 36.1M calls for 36.1M events;IngestBatchcomposes N of them per transaction, i.e. N round trips per batch. That statement alone wrote 42 GB of WAL over the series (1,164 B/event).FOR KEY SHARElookups per event (customers, meters, tenants — 56M calls each incl. the seed): the tenants check locks the same row 12,000×/s and shows up in Performance Insights asLWLock:MultiXactGen; the RI locks themselves generate WAL (0.2–0.5 GB each over the series).idx_usage_events_customer_meter121 B,(tenant_id, livemode, idempotency_key)117 B, pkey (random hex) 88 B,idx_usage_events_tenant_time37 B, GIN 9 B; heap 247 B.Why it matters: every storage-side limit the benchmark hit (checkpoint bursts at the volume's IOPS ceiling at 15–25k ev/s, the WAL-segment-pool stalls at 12k, the WAL rate that sizes
max_wal_size) scales with WAL bytes per event. Halving WAL/event moves all of them.Levers, in order of leverage vs risk (not built yet — this is the numbered lead):
INSERT … SELECT FROM unnest($1::text[], …)per batch (one statement, one RETURNING) — fewer WAL records/round trips; the cost-rate CTE can be applied per row in the same statement. KeepON CONFLICT DO NOTHINGsemantics per row.usage_events: tenant is already established by auth on every ingest path; a hot-rowKEY SHAREper event buys nothing. Options: drop that FK (tenant_id stays NOT NULL + RLS), or keep itNOT VALID. Customers/meters FKs are the real integrity checks — measure their cost separately.Follows the money-path playbook (ingest is on it): enumerate the site-set before touching
ingestOneTx.