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 for each bottleneck area, with realistic numbers from BSBM benchmarks and internal testing.

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

Typical Performance Numbers

Based on BSBM benchmarks and internal testing with 10M triples on a 4-core/16GB instance:

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 eliminates parse/optimize/generate overhead for repeated SPARQL patterns. With BSBM's mix of 12 query templates, a cache size of 256 achieves ~98% hit rate.

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

Typical Write Performance

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

Expected: P95 query latency < 50ms for 5-pattern BGPs on 10M triples.

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;