Tutorial: Tuning Refresh Mode

This tutorial walks you through using pg_trickle's built-in diagnostics to determine whether your stream tables are running in the most efficient refresh mode (FULL vs DIFFERENTIAL), and how to act on the recommendations.

Prerequisites

• pg_trickle v0.14.0 or later
• At least one stream table with several completed refresh cycles (the diagnostics become more accurate with more history)

Step 1: Check Current Refresh Efficiency

Start by reviewing how your stream tables are performing with their current refresh mode:

SELECT pgt_name, refresh_mode, diff_count, full_count,
       avg_diff_ms, avg_full_ms, diff_speedup
FROM pgtrickle.refresh_efficiency();

Example output:

Key observations:

• order_totals: DIFFERENTIAL is 68× faster — this is a great fit.
• user_stats: Running in FULL mode with no DIFFERENTIAL history — worth checking if DIFFERENTIAL would be faster.
• daily_metrics: DIFFERENTIAL and FULL take about the same time (1.0× speedup). FULL might actually be simpler and more predictable here.

Step 2: Get Recommendations

Use recommend_refresh_mode() to get AI-weighted recommendations:

SELECT pgt_name, current_mode, recommended_mode, confidence, reason
FROM pgtrickle.recommend_refresh_mode();

Example output:

For a single table with full signal details:

SELECT recommended_mode, confidence, reason,
       jsonb_pretty(signals) AS signal_details
FROM pgtrickle.recommend_refresh_mode('daily_metrics');

Step 3: Understand the Signals

The signals JSONB column contains the detailed breakdown of all seven weighted signals that contributed to the recommendation:

{
  "composite_score": -0.22,
  "signals": [
    { "name": "change_ratio_avg", "score": -0.1, "weight": 0.30 },
    { "name": "empirical_timing", "score": -0.3, "weight": 0.35 },
    { "name": "change_ratio_current", "score": -0.2, "weight": 0.25 },
    { "name": "query_complexity", "score": 0.0, "weight": 0.10 },
    { "name": "target_size", "score": 0.1, "weight": 0.10 },
    { "name": "index_coverage", "score": 0.0, "weight": 0.05 },
    { "name": "latency_variance", "score": -0.4, "weight": 0.05 }
  ]
}

Positive scores favour DIFFERENTIAL; negative scores favour FULL. A composite score above +0.15 recommends DIFFERENTIAL; below −0.15 recommends FULL; in between, the current mode is near-optimal (KEEP).

Confidence levels:

Step 4: Apply the Recommendation

If you decide to follow a recommendation, use ALTER STREAM TABLE:

-- Switch daily_metrics from DIFFERENTIAL to FULL
SELECT pgtrickle.alter_stream_table('daily_metrics',
    refresh_mode => 'FULL'
);

Or switch a table to DIFFERENTIAL:

-- Switch user_stats to DIFFERENTIAL mode
SELECT pgtrickle.alter_stream_table('user_stats',
    refresh_mode => 'DIFFERENTIAL'
);

The change takes effect on the next refresh cycle. No data is lost during the transition.

Step 5: Monitor After the Change

After switching modes, wait for several refresh cycles and re-check:

-- Wait a few minutes, then re-check efficiency
SELECT pgt_name, refresh_mode, diff_count, full_count,
       avg_diff_ms, avg_full_ms, diff_speedup
FROM pgtrickle.refresh_efficiency()
WHERE pgt_name = 'daily_metrics';

Run the recommendation function again to verify the change was beneficial:

SELECT recommended_mode, confidence, reason
FROM pgtrickle.recommend_refresh_mode('daily_metrics');

If the recommendation now says KEEP, the new mode is working well.

Common Scenarios

High-cardinality aggregates

Stream tables with SUM/COUNT/AVG over high-cardinality GROUP BY keys (1000+ groups) are almost always better in DIFFERENTIAL mode. pg_trickle warns about low-cardinality groups at creation time (DIAG-2).

Small tables with frequent full rewrites

If the source table is small (< 10,000 rows) and changes affect > 30% of rows per cycle, FULL refresh is often faster because it avoids the overhead of change tracking and delta application.

Complex multi-join queries

Queries with 4+ JOINs may have high DIFFERENTIAL overhead due to the delta propagation rules. If diff_speedup is below 2×, consider FULL mode.

Tables with volatile functions

Stream tables using volatile functions (e.g., now(), random()) must use FULL mode. pg_trickle rejects volatile functions in DIFFERENTIAL mode at creation time.

Using the TUI

The pgtrickle TUI provides a visual diagnostics panel. Press 5 or d in the interactive dashboard to open the diagnostics view, which shows recommendations with confidence levels for all stream tables at a glance.

From the CLI:

# Show recommendations for all tables
pgtrickle diag

# Show recommendations in JSON format (for automation)
pgtrickle diag --format json

See Also

• SQL Reference: recommend_refresh_mode() — Full function documentation
• SQL Reference: refresh_efficiency() — Efficiency metrics documentation
• Configuration: agg_diff_cardinality_threshold — Cardinality warning threshold
• DVM Operators — Full operator support matrix