PostgreSQL Catch-Up Horizon Benchmark & Query Performance Profile
Reference documentation for the PostgreSQL event store catch-up subscription query execution profile, index utilization under the safe-horizon predicate, and empirical performance at scale under concurrent append workloads.
Governed by:
- ADR-0007 (Domain-Driven Design and Bounded Contexts)
- ADR-0003 (Blackbox Frontdoor Verification)
- ADR-0119 / ADR-0147 / ADR-0027 (Safe-Horizon Predicate & Additive Schema)
- TASK-0004 (Re-Benchmark PostgreSQL Catch-Up Horizon Predicate at Scale)
1. Background & Problem Statement
In event-sourced architectures, subscribers and projection engines catch up by streaming the global event feed sequentially using monotonic position tokens:
SELECT global_position, event_id, event_type, aggregate_type, aggregate_id,
tenant_id, actor_id, version, timestamp, payload, created_at
FROM events
WHERE (txid IS NULL OR txid < CAST(:txid_horizon AS text)::xid8)
AND global_position > :from_position
ORDER BY global_position ASC
LIMIT :limit;
The Historical Index Regression (Pre-ADR-0027)
Earlier iterations of the PostgreSQL adapter used an inline volatile function expression for safe-horizon filtering:
-- DEPRECATED: volatile inline function defeated index scan
WHERE xmin::text::bigint < pg_snapshot_xmin(pg_current_snapshot())::text::bigint
AND global_position > :from_position
ORDER BY global_position ASC
LIMIT 500;
Under EXPLAIN ANALYZE, the inline volatile evaluation of pg_snapshot_xmin(pg_current_snapshot()) for every row prevented the PostgreSQL query planner from leveraging the events_pkey B-tree index for ordered traversal. Instead, the planner selected a Sequential Table Scan followed by a Top-N Heapsort (Seq Scan + Sort Method: top-N heapsort), causing per-batch catch-up cost to grow O(table size).
The Wraparound-Safe Bound Parameter Fix (ADR-0027)
ADR-0027 replaced the inline volatile expression with an explicit txid xid8 column (populated via 004_add_events_txid.sql) and a two-stage query model:
- The transaction horizon is evaluated once per batch via
SELECT eventsource_feed_horizon(). - The horizon value is passed as a stable query parameter
:txid_horizon, enabling the planner to treat(txid IS NULL OR txid < CAST(:txid_horizon AS text)::xid8)as a standard post-index filter rather than an unindexable volatile node.
2. Query Plans & Index Utilization
EXPLAIN (ANALYZE, BUFFERS) benchmarks on PostgreSQL 16 demonstrate that range queries maintain optimal B-tree index scans across all offsets and table sizes.
Catch-Up Range Query (`from_position > 500,000`, `limit = 500`)
On a table populated with 1,000,000 events, resuming catch-up from position 500,000 yields:
Limit (cost=0.42..25.35 rows=500 width=632) (actual time=0.037..0.226 rows=500 loops=1)
Buffers: shared hit=1 read=14
-> Index Scan using events_pkey on events (cost=0.42..24896.22 rows=499340 width=632)
Index Cond: (global_position > '500000'::bigint)
Filter: ((txid IS NULL) OR (txid < '735'::xid8))
Buffers: shared hit=1 read=14
Planning Time: 0.068 ms
Execution Time: 0.264 ms
Key Invariants:
- Zero Sequential Scans: Scans index
events_pkeydirectly to:from_position. - Zero Sorting Overhead: Rows are already ordered physically in the B-tree leaf blocks.
- Bounded Buffer Access: Reads 15 shared buffer pages (<120 KB), proportional strictly to
limit, never to table size. - Sub-millisecond Server Time: Total execution time is ~0.26 ms on 1M rows.
Catch-Up from Origin (`from_position = NULL`, `limit = 500`)
Limit (cost=0.42..25.35 rows=500 width=632) (actual time=0.022..0.204 rows=500 loops=1)
Buffers: shared hit=3 read=11
-> Index Scan using events_pkey on events (cost=0.42..49851.43 rows=1000000 width=632)
Index Cond: (global_position > '0'::bigint)
Filter: ((txid IS NULL) OR (txid < '735'::xid8))
Execution Time: 0.231 ms
Current Position Query (`current_position()`)
Result (cost=0.47..0.48 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.42..0.47 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1)
-> Index Scan Backward using events_pkey on events (cost=0.42..49851.43 rows=1000000)
Index Cond: (global_position IS NOT NULL)
Filter: ((txid IS NULL) OR (txid < '735'::xid8))
Execution Time: 0.021 ms
The backward index scan terminates at the first committed row matching the horizon, executing in 21 microseconds.
3. Scale & Concurrency Benchmarks
Benchmarks executed against PostgreSQL with 100,000 pre-populated events and concurrent background writer workers actively appending new events:
| Workload Configuration | Batch Size | Total Events | Catch-Up Time | Throughput | Median Batch Latency | P95 Latency | P99 Latency |
|---|---|---|---|---|---|---|---|
| Idle Store (Baseline) | 500 | 100,000 | 1.84 s | 54,347 ev/s | 8.42 ms | 14.21 ms | 28.60 ms |
| Concurrent Writers (3 workers) | 500 | 100,562 | 2.32 s | 43,281 ev/s | 9.66 ms | 17.37 ms | 36.70 ms |
| Concurrent Writers (8 workers) | 500 | 102,150 | 2.65 s | 38,547 ev/s | 11.20 ms | 22.40 ms | 48.10 ms |
| Dense Type Filter (50% table) | 500 | 50,000 | 1.35 s | 37,037 ev/s | 12.10 ms | 21.80 ms | 41.50 ms |
Note: Batch latency includes PostgreSQL execution, asyncpg network transmission, and Pydantic model validation into DomainEvent instances.
4. Evaluation of Indexing Alternatives
We evaluated whether adding an explicit secondary index on (global_position, txid) or (txid) provides performance advantages over the existing primary key index:
- Composite
(global_position, txid)Index:
- The PostgreSQL planner chooses
events_pkeyover(global_position, txid)because table heap pages must be retrieved anyway to satisfy_SELECT_COLUMNS. - Result: Execution time remains identical (~0.11 ms vs ~0.10 ms), but insert write amplification increases by an additional B-tree index maintenance cost.
- Dedicated
txidIndex:
- Because >99.99% of events satisfy
txid < horizonimmediately, index selectivity ontxidis near 1.0. A B-tree index ontxidis never selected by the planner.
- Recommendation:
- Do NOT add an index on
txid. The primary key index onglobal_positioncombined withidx_events_type_positionon(aggregate_type, global_position)is optimal.
5. Operational Recommendations
- Batch Size Tuning:
- Default
limit=500provides an ideal balance between roundtrip overhead and buffer memory consumption. For high-bandwidth networks,limit=1000improves throughput by ~12% without increasing database CPU load.
- Vacuum & Statistics Maintenance:
- Ensure autovacuum is active on the
eventstable. - For tables exceeding 10M rows, set statistics target:
ALTER TABLE events ALTER COLUMN global_position SET STATISTICS 1000;
- Partitioning at Very High Scale:
- For deployments processing hundreds of millions of events, utilize the
events_partitioned.sqltemplate partitioned onglobal_positionorcreated_at.