Tune Queries, Autovacuum, Memory, WAL, I/O, and Connection Architecture from Evidence

Learning outcomes

The schema and security model are correct, but correctness does not guarantee an SLO. The performance workflow is now evidence-first: generate a declared workload, capture plans and cumulative-counter baselines, identify the dominant constraint, change one thing, rerun the same work, and keep the before/after artifact. PostgreSQL planner costs are estimates rather than milliseconds, cumulative statistics have reset boundaries, and wait events describe what a backend is waiting on—not necessarily the root cause.

1. Generate a repeatable ServiceHub workload

SET ROLE servicehub_cap_owner;INSERT INTO app.customers(tenant_id,external_ref,display_name)SELECT t.tenant_id,       'C-' || g,       'Customer ' || gFROM app.tenants AS tCROSS JOIN generate_series(1,4000) AS gON CONFLICT (tenant_id,external_ref) DO NOTHING;INSERT INTO app.work_orders(tenant_id,customer_id,external_ref,status,scheduled_at,amount,note,priority)SELECT c.tenant_id,       c.customer_id,       'WO-' || c.customer_id || '-' || x.n,       (ARRAY['queued','assigned','in_progress','completed','cancelled'])         [1 + ((c.customer_id + x.n) % 5)],       TIMESTAMPTZ '2026-08-01 00:00+00'         + ((c.customer_id + x.n) % 2678400) * INTERVAL '1 second',       (((c.customer_id * 17 + x.n) % 80000) / 100.0)::numeric(12,2),       repeat('capstone-',8),       1 + ((c.customer_id + x.n) % 5)FROM app.customers AS cCROSS JOIN generate_series(1,12) AS x(n)ON CONFLICT (tenant_id,external_ref) DO NOTHING;RESET ROLE;ANALYZE app.customers;ANALYZE app.work_orders;

The exact row count depends on whether Lesson 2 seed data already exists, but the insert is idempotent on its declared business keys. Record actual counts before interpreting any plan.

SELECT count(*) AS customers FROM app.customers;SELECT count(*) AS work_orders FROM app.work_orders;SELECT relname,       n_live_tup,       n_dead_tup,       last_analyze,       last_autoanalyze,       last_autovacuumFROM pg_stat_user_tablesWHERE schemaname='app'  AND relname IN ('customers','work_orders');SELECT pg_size_pretty(pg_total_relation_size('app.work_orders'))       AS work_orders_total;

2. Establish the SLO query and preserve its plan

The application asks for one customer's active queue. Lesson 2 deliberately did not pre-create every possible index. This query is the evidence gate for the candidate partial index.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT work_order_id,status,scheduled_at,amount,priorityFROM app.work_ordersWHERE tenant_id = (  SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a')AND customer_id = 1500AND status IN ('queued','assigned','in_progress')ORDER BY scheduled_at DESCLIMIT 50;

Record scan type, estimated versus actual rows, buffers, and execution time. If the existing queue index already makes this fast for your data distribution, that is a valid result: do not add another index simply because the lesson expected one. Scale/choose a customer that demonstrates the real access pattern, then justify the index with evidence.

3. Add one evidence-backed access path and rerun exactly

SET ROLE servicehub_cap_owner;CREATE INDEX CONCURRENTLY IF NOT EXISTSwork_orders_active_customer_idxON app.work_orders(tenant_id, customer_id, scheduled_at DESC)WHERE status IN ('queued','assigned','in_progress');RESET ROLE;ANALYZE app.work_orders;

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT work_order_id,status,scheduled_at,amount,priorityFROM app.work_ordersWHERE tenant_id = (  SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a')AND customer_id = 1500AND status IN ('queued','assigned','in_progress')ORDER BY scheduled_at DESCLIMIT 50;

The expected improvement is fewer table pages/rows visited for this access path, often through the partial index. If it does not improve representative work, remove it; every index adds write/WAL/maintenance/storage cost.

4. Create controlled churn and inspect MVCC maintenance evidence

Multi-Version Concurrency Control (MVCC) updates normally create new tuple versions. Dead versions become reusable only after VACUUM can safely reclaim them. Autovacuum triggers are based on table activity thresholds and statistics, so a heavily updated table can need different per-table settings from a mostly static table.

UPDATE app.work_ordersSET note = note || 'x',    updated_at = clock_timestamp()WHERE (work_order_id % 5) = 0;UPDATE app.work_ordersSET note = left(note, GREATEST(length(note)-1,0)),    updated_at = clock_timestamp()WHERE (work_order_id % 5) = 0;SELECT relname,n_live_tup,n_dead_tup,       autovacuum_count,autoanalyze_count,       total_autovacuum_time,total_autoanalyze_timeFROM pg_stat_user_tablesWHERE schemaname='app' AND relname='work_orders';

5. Derive—not copy—an autovacuum threshold

Suppose the measured table has about 96,000 live rows and the team wants vacuum eligibility around 2,500 updates/deletes instead of waiting for a much larger fraction. One transparent scenario is threshold 500 plus scale factor 0.02: 500 + 0.02×96,000 ≈ 2,420. That is a training derivation, not a universal recommendation.

SELECT reltuples::bigint AS estimated_rowsFROM pg_classWHERE oid='app.work_orders'::regclass;ALTER TABLE app.work_orders SET (  autovacuum_vacuum_threshold = 500,  autovacuum_vacuum_scale_factor = 0.02,  autovacuum_analyze_threshold = 500,  autovacuum_analyze_scale_factor = 0.02);SELECT relname,       reloptionsFROM pg_classWHERE oid='app.work_orders'::regclass;

Then observe whether autovacuum keeps up during the actual write profile. Threshold math only controls eligibility; worker availability, cost delay, locks, I/O, long transactions, and replicas/slots can still delay cleanup.

6. Verify vacuum/analyze behavior

VACUUM (ANALYZE, VERBOSE) app.work_orders;SELECT relname,n_live_tup,n_dead_tup,       last_vacuum,last_autovacuum,       last_analyze,last_autoanalyzeFROM pg_stat_user_tablesWHERE schemaname='app' AND relname='work_orders';

Ordinary VACUUM normally makes dead space reusable inside the relation; it does not promise to shrink the file. A production incident that reaches for VACUUM FULL first would trade bloat for an ACCESS EXCLUSIVE rewrite/lock and extra disk space.

7. Prove a work_mem spill before changing memory

BEGIN;SET LOCAL work_mem='64kB';EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT tenant_id,status,scheduled_at,amount,noteFROM app.work_ordersORDER BY note,scheduled_at,work_order_id;ROLLBACK;

Look for an external/disk-backed sort and its disk usage. work_mem is a base allowance per eligible operation, not a fixed reservation per connection; one plan and its parallel workers can contain multiple memory consumers.

BEGIN;SET LOCAL work_mem='32MB';EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT tenant_id,status,scheduled_at,amount,noteFROM app.work_ordersORDER BY note,scheduled_at,work_order_id;ROLLBACK;

If the sort moves in memory and the local latency improves, that proves this operation benefits from more memory. It does not justify setting 32 MB globally for every node across every active session.

8. Measure WAL generated by a representative write

BEGIN;EXPLAIN (ANALYZE, BUFFERS, WAL, TIMING OFF, SUMMARY ON)UPDATE app.work_ordersSET amount = amount + 1WHERE tenant_id = (  SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a')AND status='queued';ROLLBACK;

EXPLAIN ANALYZE executes the UPDATE, so the transaction deliberately rolls back. WAL generated by the work can still exist even though the logical changes are rolled back; rollback itself is represented by transaction state, not by erasing already-written WAL bytes.

9. Capture PostgreSQL 18 I/O and WAL counters with reset context

SELECT wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_resetFROM pg_stat_wal;SELECT backend_type,object,context,       reads,read_bytes,read_time,       writes,write_bytes,write_time,       writebacks,extends,fsyncs,fsync_time,       stats_resetFROM pg_stat_ioWHERE object IN ('relation','wal')ORDER BY object,backend_type,context;

These are cumulative counters. Snapshot before/after the same workload and subtract; never compare lifetime totals with different stats_reset times. PostgreSQL I/O counters also do not replace operating-system device latency/queue evidence.

10. Connection architecture: measure active work, not socket count

SELECT name,setting,context,sourceFROM pg_settingsWHERE name IN (  'max_connections',  'reserved_connections',  'superuser_reserved_connections',  'work_mem')ORDER BY name;SELECT state,count(*) AS sessionsFROM pg_stat_activityWHERE backend_type='client backend'GROUP BY stateORDER BY sessions DESC;

If hundreds of client sockets produce only a few dozen useful active transactions, an external pooler can bound server backends. Transaction pooling changes session semantics; LISTEN, session advisory locks, temp-table lifetime, session SET state, and prepared-statement behavior must be audited before choosing it. The core lab does not require a pooler.

11. Deliberately wrong tuning bundle

The repair is the method used above: one hypothesis, one bounded change, identical replay, preserved evidence, and rollback if the SLO does not improve without unacceptable side effects.

12. Capstone performance acceptance record

WITH evidence(area,before_state,change,after_state,residual_risk) AS (  VALUES    ('customer_active_orders','record EXPLAIN','partial composite index','record EXPLAIN','write/WAL/index maintenance'),    ('vacuum','dead-tuple/churn counters','table-local thresholds','repeat counters','workers/locks/I/O can still delay'),    ('sort','external spill','SET LOCAL work_mem only','repeat EXPLAIN','global concurrency not tested'),    ('connections','active/idle distribution','pooling remains architecture option','load test required','session semantics'))SELECT * FROM evidence;

Authoritative references

Use current upstream PostgreSQL documentation and release/support pages as the source of truth for version-, security-, topology-, and recovery-sensitive behavior.

• PostgreSQL 18 — EXPLAIN
• PostgreSQL 18 — Routine Vacuuming
• PostgreSQL 18 — Vacuum Configuration
• PostgreSQL 18 — Resource Consumption
• PostgreSQL 18 — Monitoring Database Activity
• PostgreSQL 18 — WAL Configuration