Configuration

Complete reference for all pg_stream GUC (Grand Unified Configuration) variables.

Overview

pg_stream exposes sixteen configuration variables in the pg_stream namespace. All can be set in postgresql.conf or at runtime via SET / ALTER SYSTEM.

Required postgresql.conf settings:

shared_preload_libraries = 'pg_stream'

The extension must be loaded via shared_preload_libraries because it registers GUC variables and a background worker at startup.

Note: wal_level = logical and max_replication_slots are not required by default. The default CDC mode (trigger) uses lightweight row-level triggers. If you set pg_stream.cdc_mode = 'auto' or 'wal', then wal_level = logical is needed for WAL-based capture (see pg_stream.cdc_mode).

GUC Variables

pg_stream.enabled

Enable or disable the pg_stream extension.

When set to false, the background scheduler stops processing refreshes. Existing stream tables remain in the catalog but are not refreshed. Manual pgstream.refresh_stream_table() calls still work.

-- Disable automatic refreshes
SET pg_stream.enabled = false;

-- Re-enable
SET pg_stream.enabled = true;

pg_stream.scheduler_interval_ms

How often the background scheduler checks for stream tables that need refreshing.

Tuning Guidance:

• Low-latency workloads (sub-second schedule): Set to 100–500.
• Standard workloads (minutes of schedule): Default 1000 is appropriate.
• Low-overhead workloads (many STs with long schedules): Increase to 5000–10000 to reduce scheduler overhead.

The scheduler interval does not determine refresh frequency — it determines how often the scheduler checks whether any ST's staleness exceeds its schedule (or whether a cron expression has fired). The actual refresh frequency is governed by schedule (duration or cron) and canonical period alignment.

SET pg_stream.scheduler_interval_ms = 500;

pg_stream.min_schedule_seconds

Minimum allowed schedule value (in seconds) when creating or altering a stream table with a duration-based schedule. This limit does not apply to cron expressions.

This acts as a safety guardrail to prevent users from setting impractically small schedules that would cause excessive refresh overhead.

Tuning Guidance:

• Development/testing: Set to 1 for fast iteration.
• Production: Keep at 60 or higher to prevent excessive WAL consumption and CPU usage.

-- Allow 10-second schedules (for testing)
SET pg_stream.min_schedule_seconds = 10;

pg_stream.max_consecutive_errors

Maximum consecutive refresh failures before a stream table is moved to ERROR status.

When a ST's consecutive_errors reaches this threshold:

1. The ST status changes to ERROR.
2. Automatic refreshes stop for this ST.
3. Manual intervention is required: SELECT pgstream.alter_stream_table('...', status => 'ACTIVE').

Tuning Guidance:

• Strict (production): 3 — fail fast to surface issues.
• Lenient (development): 10–20 — tolerate transient errors.

SET pg_stream.max_consecutive_errors = 5;

pg_stream.change_buffer_schema

Schema where CDC change buffer tables are created.

Change buffer tables are named <schema>.changes_<oid> where <oid> is the source table's OID. Placing them in a dedicated schema keeps them out of the public namespace.

Tuning Guidance:

• Generally leave at the default. Change only if pgstream_changes conflicts with an existing schema in your database.

SET pg_stream.change_buffer_schema = 'my_change_buffers';

pg_stream.max_concurrent_refreshes

Maximum number of stream tables that can be refreshed simultaneously.

Controls concurrency in the scheduler. Each refresh acquires an advisory lock, and the scheduler skips STs that exceed this limit.

Tuning Guidance:

• Small databases (few STs): 1–4 is sufficient.
• Large deployments (50+ STs): Increase to 8–16 if the server has spare CPU and I/O capacity.
• Resource-constrained: Set to 1 for fully sequential refresh processing.

The optimal setting depends on:

• Number of CPU cores available
• I/O throughput (SSD vs HDD)
• Complexity of the defining queries
• Amount of concurrent OLTP workload

SET pg_stream.max_concurrent_refreshes = 8;

pg_stream.differential_max_change_ratio

Maximum change-to-table ratio before DIFFERENTIAL refresh falls back to FULL refresh.

When the number of pending change buffer rows exceeds this fraction of the source table's estimated row count, the refresh engine switches from DIFFERENTIAL (which uses JSONB parsing and window functions) to FULL refresh. At high change rates FULL refresh is cheaper because it avoids the per-row JSONB overhead.

Special Values:

• 0.0: Disable adaptive fallback — always use DIFFERENTIAL.
• 1.0: Always fall back to FULL (effectively forces FULL mode).

Tuning Guidance:

• OLTP with low change rates (< 5%): Default 0.15 is appropriate.
• Batch-load workloads (bulk inserts): Lower to 0.05–0.10 so large batches trigger FULL refresh sooner.
• Latency-sensitive (want deterministic refresh time): Set to 0.0 to always use DIFFERENTIAL.

-- Lower threshold for batch-heavy workloads
SET pg_stream.differential_max_change_ratio = 0.10;

-- Disable adaptive fallback
SET pg_stream.differential_max_change_ratio = 0.0;

pg_stream.cleanup_use_truncate

Use TRUNCATE instead of per-row DELETE for change buffer cleanup when the entire buffer is consumed by a refresh.

After a differential refresh consumes all rows from the change buffer, the engine must clean up the buffer table. TRUNCATE is O(1) regardless of row count, versus DELETE which must update indexes row-by-row. This saves 3–5 ms per refresh at 10%+ change rates.

Trade-off: TRUNCATE acquires an AccessExclusiveLock on the change buffer table. If concurrent DML on the source table is actively inserting into the same change buffer via triggers, this lock can cause brief contention.

Tuning Guidance:

• Most workloads: Leave at true — the performance benefit outweighs the brief lock.
• High-concurrency OLTP with continuous writes during refresh: Set to false if you observe lock-wait timeouts on the change buffer.

-- Use per-row DELETE for change buffer cleanup
SET pg_stream.cleanup_use_truncate = false;

pg_stream.merge_planner_hints

Inject SET LOCAL planner hints before MERGE execution during differential refresh.

When enabled, the refresh executor estimates the delta size and applies optimizer hints within the transaction:

• Delta ≥ 100 rows: SET LOCAL enable_nestloop = off — forces hash joins instead of nested-loop joins.
• Delta ≥ 10,000 rows: additionally SET LOCAL work_mem = '<N>MB' (see pg_stream.merge_work_mem_mb).

This reduces P95 latency spikes caused by PostgreSQL choosing nested-loop plans for medium/large delta sizes.

Tuning Guidance:

• Most workloads: Leave at true — the hints improve tail latency without affecting small deltas.
• Custom plan overrides: Set to false if you manage planner settings yourself or if the hints conflict with your pg_hint_plan configuration.

-- Disable planner hints
SET pg_stream.merge_planner_hints = false;

pg_stream.merge_work_mem_mb

work_mem value (in MB) applied via SET LOCAL when the delta exceeds 10,000 rows and planner hints are enabled.

A higher value lets PostgreSQL use larger in-memory hash tables for the MERGE join, avoiding disk-spilling sort/merge strategies on large deltas. This setting is only applied when both merge_planner_hints = true and the delta exceeds 10,000 rows.

Tuning Guidance:

• Servers with ample RAM (32+ GB): Increase to 128–256 for faster large-delta refreshes.
• Memory-constrained: Lower to 16–32 or disable planner hints entirely.
• Very large deltas (100K+ rows): Consider 256–512 if refresh latency matters.

SET pg_stream.merge_work_mem_mb = 128;

pg_stream.merge_strategy

Strategy for applying delta changes to the stream table during differential refresh.

Values:

• auto (default): Use MERGE for small deltas; switch to DELETE + INSERT when the delta exceeds 25% of the stream table row count.
• merge: Always use a single MERGE statement. Best for correctness and simplicity.
• delete_insert: Always use DELETE + INSERT. May be faster for very large deltas but has known limitations with aggregate/DISTINCT queries.

Tuning Guidance:

• Most workloads: Leave at 'auto' or 'merge'.
• Batch-heavy ETL: Try 'delete_insert' if MERGE performance degrades on large deltas, but test thoroughly with your specific queries.

-- Always use MERGE
SET pg_stream.merge_strategy = 'merge';

-- Always use DELETE + INSERT
SET pg_stream.merge_strategy = 'delete_insert';

pg_stream.use_prepared_statements

Use SQL PREPARE / EXECUTE for MERGE statements during differential refresh.

When enabled, the refresh executor issues PREPARE __pgs_merge_{id} on the first cache-hit cycle, then uses EXECUTE on subsequent cycles. After approximately 5 executions, PostgreSQL switches from a custom plan to a generic plan, saving 1–2 ms of parse/plan overhead per refresh.

Tuning Guidance:

• Most workloads: Leave at true — the cumulative parse/plan savings are significant for frequently-refreshed stream tables.
• Highly skewed data: Set to false if prepared-statement parameter sniffing produces poor plans (e.g., highly skewed LSN distributions causing bad join estimates).

-- Disable prepared statements
SET pg_stream.use_prepared_statements = false;

pg_stream.user_triggers

Control how user-defined triggers on stream tables are handled during refresh.

When a stream table has user-defined row-level triggers, the refresh engine can decompose the MERGE into explicit DELETE + UPDATE + INSERT statements so triggers fire with correct TG_OP, OLD, and NEW values.

Values:

• auto (default): Automatically detect user triggers on the stream table. If present, use the explicit DML path; otherwise use MERGE.
• on: Always use the explicit DML path, even without user triggers. Useful for testing.
• off: Always use MERGE. User triggers are suppressed during refresh. This is the escape hatch if explicit DML causes issues.

Notes:

• Row-level triggers do not fire during FULL refresh regardless of this setting. FULL refresh uses DISABLE TRIGGER USER / ENABLE TRIGGER USER to suppress them.
• The explicit DML path adds ~25–60% overhead compared to MERGE for affected stream tables.
• Stream tables without user triggers have zero overhead when using auto (only a fast pg_trigger check).

-- Auto-detect (default)
SET pg_stream.user_triggers = 'auto';

-- Always use explicit DML (for testing)
SET pg_stream.user_triggers = 'on';

-- Suppress triggers, use MERGE
SET pg_stream.user_triggers = 'off';

pg_stream.block_source_ddl

When enabled, column-affecting DDL (e.g., ALTER TABLE ... DROP COLUMN, ALTER TABLE ... ALTER COLUMN ... TYPE) on source tables tracked by stream tables is blocked with an ERROR instead of silently marking stream tables for reinitialization.

This is useful in production environments where you want to prevent accidental schema changes that would trigger expensive full recomputation of downstream stream tables.

Default: false
Context: Superuser

-- Block column-affecting DDL on tracked source tables
SET pg_stream.block_source_ddl = true;

-- Allow DDL (stream tables will be marked for reinit instead)
SET pg_stream.block_source_ddl = false;

Note: Only column-affecting changes are blocked. Benign DDL (adding indexes, comments, constraints) is always allowed regardless of this setting.

pg_stream.cdc_mode

CDC (Change Data Capture) mechanism selection.

Default: 'trigger'

-- Always use triggers (default, zero-config)
SET pg_stream.cdc_mode = 'trigger';

-- Enable automatic trigger → WAL transition
SET pg_stream.cdc_mode = 'auto';

-- Require WAL-based CDC (error if wal_level != logical)
SET pg_stream.cdc_mode = 'wal';

pg_stream.wal_transition_timeout

Maximum time (seconds) to wait for the WAL decoder to catch up during the transition from trigger-based to WAL-based CDC. If the decoder has not caught up within this timeout, the system falls back to triggers.

Default: 300 (5 minutes)
Range: 10 – 3600

SET pg_stream.wal_transition_timeout = 300;

Complete postgresql.conf Example

# Required
shared_preload_libraries = 'pg_stream'

# Optional tuning
pg_stream.enabled = true
pg_stream.scheduler_interval_ms = 1000
pg_stream.min_schedule_seconds = 60
pg_stream.max_consecutive_errors = 3
pg_stream.change_buffer_schema = 'pgstream_changes'
pg_stream.max_concurrent_refreshes = 4
pg_stream.differential_max_change_ratio = 0.15
pg_stream.cleanup_use_truncate = true
pg_stream.merge_planner_hints = true
pg_stream.merge_work_mem_mb = 64
pg_stream.merge_strategy = 'auto'
pg_stream.use_prepared_statements = true
pg_stream.user_triggers = 'auto'
pg_stream.block_source_ddl = false
pg_stream.cdc_mode = 'trigger'
pg_stream.wal_transition_timeout = 300

Runtime Configuration

All GUC variables can be changed at runtime by a superuser:

-- View current settings
SHOW pg_stream.enabled;
SHOW pg_stream.scheduler_interval_ms;

-- Change for current session
SET pg_stream.max_concurrent_refreshes = 8;

-- Change persistently (requires reload)
ALTER SYSTEM SET pg_stream.scheduler_interval_ms = 500;
SELECT pg_reload_conf();

Further Reading

• INSTALL.md — Installation and initial configuration
• ARCHITECTURE.md — System architecture overview
• SQL_REFERENCE.md — Complete function reference