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

Sequence Exhaustion (statement_id_seq)

(L15-11, v0.97.0)

Every triple stored by pg_ripple receives a globally-unique statement identifier (SID) from the _pg_ripple.statement_id_seq sequence. The sequence is a standard PostgreSQL BIGINT sequence, which means it can count from 1 to 9,223,372,036,854,775,807 (approximately 9.2 × 10¹⁸).

How Long Until Exhaustion?

At realistic workloads the sequence will never exhaust. However, if you ever hit this limit, PostgreSQL raises:

ERROR:  nextval: reached maximum value of sequence "_pg_ripple.statement_id_seq" (9223372036854775807)

Checking Remaining Capacity

Use pg_ripple.sid_exhaustion_years() to check how many years remain:

SELECT pg_ripple.sid_exhaustion_years();
-- Returns: 291723.6 (years remaining at current insert rate)

The function returns NULL if fewer than 100,000 SIDs have been assigned (the rate estimate is unreliable at low counts).

You can also query the sequence directly:

SELECT
    last_value                                  AS current_sid,
    9223372036854775807 - last_value            AS sids_remaining,
    (9223372036854775807 - last_value)::numeric
        / NULLIF((SELECT count(*) FROM _pg_ripple.vp_rare), 0) AS approx_years_at_current_rate
FROM _pg_ripple.statement_id_seq;

Recovery Procedure (if exhaustion occurs)

1. Dump all data using pg_ripple.export_turtle() or pg_dump.
2. Truncate all VP tables:

   SELECT pg_ripple.truncate_all_triples();  -- development only!
   

3. Reset the sequence:

   ALTER SEQUENCE _pg_ripple.statement_id_seq RESTART WITH 1;
   

4. Reload data from the dump.

In production, the correct long-term solution is to use a CYCLE sequence, which pg_ripple does not currently support (adding CYCLE would allow SID reuse which could cause correctness issues with time-travel queries). Track this in a support ticket if you project you will hit the limit within your deployment horizon.