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:


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:

  1. The transaction horizon is evaluated once per batch via SELECT eventsource_feed_horizon().
  2. 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:

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:

  1. Composite (global_position, txid) Index:
  1. Dedicated txid Index:
  1. Recommendation:

5. Operational Recommendations

  1. Batch Size Tuning:
  1. Vacuum & Statistics Maintenance:
  1. Partitioning at Very High Scale: