Scaling

pg_ripple scales vertically within a single PostgreSQL instance and horizontally for read traffic via streaming replication. This page covers how to allocate resources, tune the merge worker, set up read replicas, and understand current limitations.

Vertical Scaling

The most impactful scaling lever is giving your single PostgreSQL instance more resources.

Memory

Memory affects three key areas:

Dictionary Cache Sizing

The dictionary cache is allocated in shared memory at server startup. Each entry consumes approximately 200 bytes.

-- Check current utilization
SELECT
  s->>'encode_cache_capacity' AS capacity,
  s->>'encode_cache_utilization_pct' AS utilization_pct,
  ROUND(
    (s->>'encode_cache_hits')::numeric /
    NULLIF((s->>'encode_cache_hits')::numeric + (s->>'encode_cache_misses')::numeric, 0),
    4
  ) AS hit_rate
FROM pg_ripple.stats() s;

PostgreSQL Memory Settings

# postgresql.conf — for a 64 GB server with pg_ripple as the primary workload
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 256MB
maintenance_work_mem = 2GB

CPU

Set max_parallel_workers_per_gather to allow PostgreSQL to parallelize large VP table scans:

max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 100
parallel_tuple_cost = 0.001

pg_ripple's parallel_query_min_joins GUC controls when the SPARQL engine enables parallel hints in generated SQL (default: 3 joins).

Storage

Merge Worker Tuning

The HTAP merge worker is the most important pg_ripple-specific scaling knob. It controls how quickly delta rows (recent writes) are consolidated into the main BRIN-indexed partition.

How the Merge Worker Operates

1. The worker polls every merge_interval_secs (default: 60s)
2. For each predicate, it checks if delta row count >= merge_threshold
3. If yes, it creates a new main table: (old main − tombstones) UNION ALL delta
4. It swaps the view to point at the new main, then drops the old main after merge_retention_seconds
5. If auto_analyze is on, it runs ANALYZE on the new main

Tuning for Write-Heavy Workloads

Lower the merge threshold and interval to keep the delta tables small:

pg_ripple.merge_threshold = 5000
pg_ripple.merge_interval_secs = 30
pg_ripple.latch_trigger_threshold = 5000

This gives fresher reads but increases I/O from more frequent merges.

Tuning for Read-Heavy Workloads

Raise the threshold to batch more writes before merging:

pg_ripple.merge_threshold = 50000
pg_ripple.merge_interval_secs = 120

This reduces merge I/O overhead but means queries scan larger delta tables.

Monitoring Merge Activity

-- Is the merge worker running?
SELECT (pg_ripple.stats()->>'merge_worker_pid')::int AS pid;

-- How many unmerged delta rows?
SELECT (pg_ripple.stats()->>'unmerged_delta_rows')::int AS delta_rows;

Read Replicas

PostgreSQL streaming replication provides horizontal read scaling for SPARQL queries.

Architecture

┌────────────┐     WAL stream     ┌────────────┐
│  Primary   │ ──────────────────→ │  Replica 1 │ ← SPARQL reads
│  (writes)  │                     └────────────┘
│            │     WAL stream     ┌────────────┐
│            │ ──────────────────→ │  Replica 2 │ ← SPARQL reads
└────────────┘                     └────────────┘

Setting Up a Read Replica

On the primary:

# postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB

Create a replication slot:

SELECT pg_create_physical_replication_slot('replica1');

On the replica:

pg_basebackup -h primary-host -D /var/lib/postgresql/18/main -R -S replica1 -P

Start the replica — it will begin streaming WAL and replaying changes, including all VP table mutations.

Replica Considerations

• SPARQL queries work identically on replicas — the query engine reads VP tables the same way
• Dictionary cache is independent per instance — each replica maintains its own LRU cache
• Replication lag: monitor with pg_stat_replication on the primary. Under normal load, lag should be sub-second
• Hot standby conflicts: long-running SPARQL queries on replicas may conflict with WAL replay. Set max_standby_streaming_delay appropriately:

# On the replica
max_standby_streaming_delay = 30s
hot_standby_feedback = on

Connection Pooling

For workloads with many concurrent SPARQL clients, use a connection pooler:

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = session
max_client_conn = 200
default_pool_size = 20

Scaling Limits and Honest Boundaries

Capacity Planning

Storage Estimates

Example: 100M triples with 20M unique terms ≈ 12 GB (VP) + 2.4 GB (dictionary) + overhead ≈ ~20 GB total.

Memory Estimates