Performance Tuning

pg_ripple performance depends on three interacting subsystems: the query engine, the write path, and the dictionary cache. This page provides diagnostic steps and tuning recipes. Measure changes against your own retained baseline.

The Three Bottleneck Areas

┌──────────────────────────────────────────────────────┐
│                   Performance                         │
│                                                      │
│   ┌──────────┐   ┌──────────┐   ┌──────────────┐    │
│   │  Query    │   │  Write   │   │  Cache       │    │
│   │  Engine   │   │  Path    │   │  Pressure    │    │
│   │          │   │          │   │              │    │
│   │ Slow     │   │ Merge    │   │ Dictionary   │    │
│   │ SPARQL   │   │ worker   │   │ misses →     │    │
│   │ queries  │   │ lag,     │   │ table        │    │
│   │          │   │ delta    │   │ lookups      │    │
│   │          │   │ bloat    │   │              │    │
│   └──────────┘   └──────────┘   └──────────────┘    │
└──────────────────────────────────────────────────────┘

Diagnostic Workflow

Before tuning, identify which subsystem is the bottleneck:

-- Step 1: Overall health
SELECT pg_ripple.canary();

-- Step 2: Cache hit rate
SELECT
    (s->>'encode_cache_hits')::bigint AS hits,
    (s->>'encode_cache_misses')::bigint AS misses,
    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;

-- Step 3: Delta accumulation
SELECT (pg_ripple.stats()->>'unmerged_delta_rows')::int AS delta_rows;

-- Step 4: Slowest queries
SELECT calls, mean_exec_time::numeric(10,2) AS avg_ms, LEFT(query, 100)
FROM pg_stat_statements
WHERE query LIKE '%_pg_ripple.vp_%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Query Performance

Establish a baseline

The repository does not publish current latency ranges for production-scale datasets. Capture EXPLAIN, fixture identity, hardware, software versions, and raw timings before and after each tuning change.

Tuning: Slow Single Queries

Step 1: Get the EXPLAIN output

SELECT pg_ripple.explain_sparql(
    'SELECT ?name WHERE {
        ?person <http://schema.org/knows> ?friend .
        ?friend <http://schema.org/name> ?name
    }',
    'text'
);

Step 2: Check for common issues

Step 3: Enable the plan cache

-- Cache compiled SQL for repeated queries
SET pg_ripple.plan_cache_size = 512;

The plan cache avoids repeated parse, optimization, and SQL-generation work for recurring SPARQL patterns. Size it from the observed working set.

Tuning: Overall Query Throughput

For workloads with many concurrent queries:

# Enable parallel query for complex joins
pg_ripple.parallel_query_min_joins = 2

# PostgreSQL parallel execution
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Larger work_mem for complex joins
work_mem = '128MB'

Write Throughput

Measure write performance

Use the bounded benchmark producer for a smoke measurement, then test the exact parser, batch size, durability settings, and storage used in production. The repository does not publish current production-scale throughput ranges.

Tuning: Merge Worker Lag

If unmerged_delta_rows grows continuously, the merge worker cannot keep up with the write rate.

Diagnosis:

-- Check delta accumulation
SELECT (pg_ripple.stats()->>'unmerged_delta_rows')::int AS delta;
-- Run again 60 seconds later — if delta is growing, merges are lagging

Solutions (in order of impact):

1. Lower merge_threshold — Merge smaller batches more frequently:

   ALTER SYSTEM SET pg_ripple.merge_threshold = 5000;
   SELECT pg_reload_conf();
   

2. Increase merge frequency — Reduce polling interval:

   ALTER SYSTEM SET pg_ripple.merge_interval_secs = 15;
   SELECT pg_reload_conf();
   

3. Manual compaction — Force an immediate merge:

   SELECT pg_ripple.compact();
   

4. Separate write windows — Batch writes during off-peak hours, then compact.

Tuning: Bulk Load Performance

For large initial data loads:

-- Temporarily disable SHACL validation
SET pg_ripple.shacl_mode = 'off';

-- Use file-based loading for best throughput
SELECT pg_ripple.load_turtle_file('/data/large_dataset.ttl');

-- Re-enable validation
SET pg_ripple.shacl_mode = 'async';

-- Force merge to move data to main tables
SELECT pg_ripple.compact();

Cache Pressure

Diagnosis

SELECT
    (s->>'encode_cache_capacity')::int AS capacity,
    (s->>'encode_cache_utilization_pct')::int AS util_pct,
    (s->>'encode_cache_hits')::bigint AS hits,
    (s->>'encode_cache_misses')::bigint AS misses,
    (s->>'encode_cache_evictions')::bigint AS evictions
FROM pg_ripple.stats() s;

Sizing the Dictionary Cache

Rule of thumb: the cache should hold at least 80% of your unique terms.

-- Count unique terms
SELECT count(*) AS unique_terms FROM _pg_ripple.dictionary;

Workload-Specific Recipes

Read-Heavy Analytics

Optimized for complex SPARQL queries with rare writes:

# Large plan cache for diverse query shapes
pg_ripple.plan_cache_size = 2048

# BGP optimization
pg_ripple.bgp_reorder = on
pg_ripple.parallel_query_min_joins = 2

# Large dictionary cache
pg_ripple.dictionary_cache_size = 262144
pg_ripple.cache_budget = 256

# Infrequent merges (writes are rare)
pg_ripple.merge_threshold = 100000
pg_ripple.merge_interval_secs = 300

# PostgreSQL
shared_buffers = '4GB'
effective_cache_size = '12GB'
work_mem = '256MB'
random_page_cost = 1.1

Record the resulting P95 latency and compare it with your service-level objective.

Write-Heavy Ingestion

Optimized for continuous data ingestion with periodic queries:

# Smaller plan cache (fewer distinct queries)
pg_ripple.plan_cache_size = 64

# Aggressive merging to keep delta small
pg_ripple.merge_threshold = 5000
pg_ripple.merge_interval_secs = 10
pg_ripple.latch_trigger_threshold = 2000
pg_ripple.auto_analyze = on

# Large cache to handle encoding pressure
pg_ripple.dictionary_cache_size = 500000
pg_ripple.cache_budget = 512

# Disable SHACL during ingestion
pg_ripple.shacl_mode = 'off'

# PostgreSQL — optimize for writes
shared_buffers = '2GB'
wal_buffers = '64MB'
checkpoint_completion_target = 0.9
max_wal_size = '4GB'

Expected: Sustained ingestion at 50K+ triples/sec with merge lag < 30 seconds.

Mixed HTAP (Read + Write)

Balanced for concurrent queries and writes:

# Moderate plan cache
pg_ripple.plan_cache_size = 512

# Balanced merge — not too frequent, not too lazy
pg_ripple.merge_threshold = 25000
pg_ripple.merge_interval_secs = 30
pg_ripple.latch_trigger_threshold = 10000
pg_ripple.auto_analyze = on

# Good cache coverage
pg_ripple.dictionary_cache_size = 131072
pg_ripple.cache_budget = 128

# Async SHACL so writes are not blocked
pg_ripple.shacl_mode = 'async'

# BGP optimization for read queries
pg_ripple.bgp_reorder = on

# PostgreSQL
shared_buffers = '4GB'
effective_cache_size = '12GB'
work_mem = '128MB'
max_parallel_workers_per_gather = 2

Expected: Read P95 < 30ms, write throughput > 20K triples/sec, merge lag < 60 seconds.

Benchmarking Your Deployment

Use the built-in compact() function and pg_stat_statements to establish baselines:

-- Reset statistics
SELECT pg_stat_statements_reset();

-- Run your workload (queries, inserts, etc.)

-- Collect results
SELECT
    calls,
    mean_exec_time::numeric(10,2) AS avg_ms,
    stddev_exec_time::numeric(10,2) AS stddev_ms,
    min_exec_time::numeric(10,2) AS min_ms,
    max_exec_time::numeric(10,2) AS max_ms,
    rows,
    LEFT(query, 80) AS query_prefix
FROM pg_stat_statements
WHERE query LIKE '%_pg_ripple%'
ORDER BY total_exec_time DESC
LIMIT 20;