Contents

Frequently Asked Questions

This FAQ covers everything from core concepts and getting started, through SQL support details, to operational topics like deployment, monitoring, and troubleshooting. Use the table of contents below to jump to a specific topic.

Table of Contents

Getting started - General — What pg_trickle is, how IVM works, key concepts - Installation & Setup — Installing, configuring, uninstalling - Creating & Managing Stream Tables — Create, alter, drop, schedules

Consistency & refresh modes - Data Freshness & Consistency — Staleness, read-your-writes, DVS - IMMEDIATE Mode (Transactional IVM) — Same-transaction refresh

SQL features - SQL Support — Supported and unsupported SQL constructs - Aggregates & Group-By — Incremental aggregates, HAVING, auxiliary columns - Joins — Multi-table delta computation, FULL OUTER JOIN - CTEs & Recursive Queries — Semi-naive, DRed, recomputation strategies - Window Functions & LATERAL — Partition-based recomputation, SRFs - TopK (ORDER BY … LIMIT) — Bounded result sets - Tables Without Primary Keys — Content-based row identity

Internals & architecture - Change Data Capture (CDC) — Triggers, WAL transition, why auto is the default, change buffers - Diamond Dependencies & DAG Scheduling — Topological ordering, atomic groups - Schema Changes & DDL Events — Reinitialize, event triggers

Operations - Performance & Tuning — Scheduler tuning, min schedule risks, disk space, adaptive fallback - Interoperability — Views, replication, connection poolers, triggers, pgvector - dbt Integration — Materialization, commands, freshness checks - Row-Level Security (RLS) — Source vs stream table policies, SECURITY DEFINER triggers - Deployment & Operations — Workers, upgrades, replicas, Kubernetes - Monitoring & Alerting — Views, NOTIFY alerts, failure handling - Configuration Reference — All GUC parameters

Troubleshooting & reference - Troubleshooting — Common problems and debugging - Why Are These SQL Features Not Supported? — Technical explanations for each limitation - Why Are These Stream Table Operations Restricted? — Why direct DML, ALTER TABLE, and TRUNCATE are disallowed

General

These questions cover fundamental concepts — what pg_trickle is, how incremental view maintenance works, and the key building blocks (frontiers, row IDs, the auto-rewrite pipeline) that power the extension.

What is pg_trickle?

pg_trickle is a PostgreSQL 18 extension that implements stream tables — declarative, automatically-refreshing materialized views with Differential View Maintenance (DVM). You define a SQL query and a refresh schedule; the extension handles change capture, delta computation, and incremental refresh automatically.

It is inspired by the DBSP differential dataflow framework. See DBSP_COMPARISON.md for a detailed comparison.

What is incremental view maintenance (IVM) and why does it matter?

Incremental View Maintenance means updating a materialized view by processing only the changes (deltas) to the source data, rather than re-executing the entire defining query from scratch.

Consider a stream table defined as SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id over a 10-million-row orders table. When you insert 5 new rows:

  • Without IVM (FULL refresh): Re-scans all 10 million rows and recomputes every group. Cost: O(total rows).
  • With IVM (DIFFERENTIAL refresh): Reads only the 5 new rows from the change buffer, identifies the affected groups, and updates just those groups. Cost: O(changed rows × affected groups).

pg_trickle’s DVM engine implements IVM using differentiation rules for each SQL operator (Scan, Filter, Join, Aggregate, etc.), generating a delta query that computes the exact changes to the stream table from the exact changes to the source.

What is the difference between a stream table and a regular materialized view, in practice?

Feature Materialized Views Stream Tables
Refresh Manual (REFRESH MATERIALIZED VIEW) Automatic (scheduler) or manual
Incremental refresh Not supported natively Built-in differential mode
Change detection None — always full recompute CDC triggers track row-level changes
Dependency ordering None DAG-aware topological refresh
Monitoring None Built-in views, stats, NOTIFY alerts
Schedule None Duration strings (5m) or cron (*/5 * * * *)
Transactional IVM No Yes (IMMEDIATE mode)

In practice, stream tables are regular PostgreSQL heap tables under the hood — you can query them, create indexes on them, join them with other tables, and reference them from views. The key difference is that pg_trickle manages their contents automatically.

What happens behind the scenes when I INSERT a row into a table tracked by a stream table?

The full data flow for a DIFFERENTIAL-mode stream table:

  1. Your INSERT completes normally. The row is written to the source table.
  2. A CDC trigger fires (row-level AFTER INSERT). It writes a change record (action=I, the new row data as JSONB, the current WAL LSN) into the source’s change buffer table (pgtrickle_changes.changes_<oid>). This happens within your transaction — if you roll back, the change record is also rolled back.
  3. You commit. Both the source row and the change record become visible.
  4. The scheduler wakes up (every pg_trickle.scheduler_interval_ms, default 1 second). It checks whether the stream table’s schedule says a refresh is due.
  5. If due, the refresh engine runs. It reads the change buffer for rows with LSN > the stream table’s current frontier, generates a delta query from the DVM operator tree, and applies the result via MERGE.
  6. Frontier advances. The stream table’s frontier is updated to the new LSN, and the consumed change buffer rows are cleaned up.

For IMMEDIATE-mode stream tables, steps 2–6 are replaced: a statement-level AFTER trigger computes and applies the delta within your transaction, so the stream table is updated before your transaction commits.

What does “differential” mean in the context of pg_trickle?

“Differential” refers to the mathematical approach of computing differences (deltas) rather than absolute values. Given a query Q and a set of changes ΔR to source table R, the DVM engine computes ΔQ(R, ΔR) — the change to the query result caused by the change to the source. This delta is then applied (merged) into the stream table.

Each SQL operator has its own differentiation rule. For example: - Filter: ΔFilter(R, ΔR) = Filter(ΔR) — just apply the filter to the changes. - Join: ΔJoin(R, S, ΔR) = Join(ΔR, S) — join the changes against the other side’s current state. - Aggregate: Recompute only the groups whose keys appear in the changes.

See DVM_OPERATORS.md for the complete set of differentiation rules.

What is a frontier, and why does pg_trickle track LSNs?

A frontier is a per-source map of {source_oid → LSN} that records exactly how far each stream table has consumed changes from each of its source tables. It is stored as JSONB in the pgtrickle.pgt_stream_tables catalog.

Why LSNs? PostgreSQL’s Write-Ahead Log Sequence Number (LSN) provides a globally ordered, monotonically increasing position in the change stream. By recording the LSN at which each source was last consumed, the frontier ensures:

  • No missed changes. The next refresh reads changes with LSN > frontier, ensuring contiguous, non-overlapping windows.
  • No duplicate processing. Changes at or below the frontier are never re-read.
  • Consistent snapshots. When a stream table depends on multiple source tables, the frontier tracks each source independently, enabling consistent multi-source delta computation.

Lifecycle: Created on first full refresh → Advanced on each differential refresh → Reset on reinitialize.

What is the __pgt_row_id column and why does it appear in my stream tables?

Every stream table has a __pgt_row_id BIGINT PRIMARY KEY column. It stores a 64-bit xxHash of the row’s group-by key (for aggregate queries) or all output columns (for non-aggregate queries). The refresh engine uses it to match incoming deltas against existing rows during the MERGE operation.

You should ignore this column in your queries. It is an implementation detail. If it bothers you, exclude it explicitly:

SELECT customer_id, total FROM order_totals;  -- omit __pgt_row_id

What is the auto-rewrite pipeline and how does it affect my queries?

Before parsing a defining query into the DVM operator tree, pg_trickle runs six automatic rewrite passes:

# Pass What it does
0 View inlining Replaces view references with (view_definition) AS alias subqueries (fixpoint, max depth 10)
1 DISTINCT ON Converts to ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) = 1 subquery
2 GROUPING SETS / CUBE / ROLLUP Decomposes into UNION ALL of separate GROUP BY queries
3 Scalar subquery in WHERE Rewrites WHERE col > (SELECT …) to CROSS JOIN
4 Correlated scalar subquery in SELECT Rewrites to LEFT JOIN with grouped inline view
5 SubLinks in OR Splits WHERE a OR EXISTS (…) into UNION branches

The rewrites are transparent — your original query is preserved in the catalog (original_query column) while the rewritten version is stored in defining_query. The DVM engine only sees standard SQL operators after rewriting.

See ARCHITECTURE.md for details on each pass.

How does pg_trickle compare to DBSP (the academic framework)?

pg_trickle is inspired by DBSP but is not a direct implementation. Key differences:

  • DBSP is a general-purpose differential dataflow framework with a Rust runtime (Feldera). It models computation as circuits over Z-sets (multisets with integer weights).
  • pg_trickle implements the same mathematical principles (delta queries, frontier tracking) but embedded inside PostgreSQL as an extension. It generates SQL delta queries rather than running a separate computation engine.
  • Trade-off: pg_trickle leverages PostgreSQL’s optimizer, indexes, and storage engine but is limited to what can be expressed as SQL queries. DBSP can implement arbitrary dataflow computations.

See DBSP_COMPARISON.md for a detailed comparison.

How does pg_trickle compare to pg_ivm?

Feature pg_ivm pg_trickle
Refresh timing Immediate (same transaction) only Immediate, Deferred (scheduled), or Manual
Incremental strategy Transition tables + query rewriting DVM operator tree + delta SQL generation
Supported SQL Inner joins, simple outer joins, COUNT/SUM/AVG/MIN/MAX, EXISTS, DISTINCT All of the above + window functions, recursive CTEs, LATERAL, UNION/INTERSECT/EXCEPT, 37 aggregates, TopK, GROUPING SETS
Cascading (view-on-view) No Yes (DAG-aware topological refresh)
Scheduling None (always immediate) Duration, cron, CALCULATED, or NULL
Monitoring None Built-in views, stats, NOTIFY alerts
PostgreSQL version 14–17 18 only (until v0.4.0)

pg_trickle’s IMMEDIATE mode is designed as a migration path for pg_ivm users — it uses the same statement-level trigger approach with transition tables.

What PostgreSQL versions are supported?

PostgreSQL 18.x exclusively. pg_trickle uses PostgreSQL 18 features such as enhanced MERGE syntax with NOT MATCHED BY SOURCE and improved event trigger payloads. These features are not available in earlier versions.

Backward compatibility with PostgreSQL 16–17 is planned for the v0.4.0 release.

Does pg_trickle require wal_level = logical?

No. By default, pg_trickle uses lightweight row-level triggers for change data capture instead of logical replication. This means you do not need to set wal_level = logical, configure max_replication_slots, or create publications.

If you later enable the hybrid CDC mode (pg_trickle.cdc_mode = 'auto'), WAL-based capture becomes an option — but this is opt-in and not required for normal operation.

Is pg_trickle production-ready?

pg_trickle is under active development and approaching production readiness. It has a comprehensive test suite with 700+ unit tests and 290+ end-to-end tests covering correctness, failure recovery, and concurrency scenarios.

That said, as with any new extension, you should evaluate it against your specific workloads before deploying to production. Start with non-critical dashboards or reporting tables, monitor refresh performance and data correctness, and gradually expand usage as confidence grows.

Installation & Setup

How do I install pg_trickle?

  1. Add pg_trickle to shared_preload_libraries in postgresql.conf: ini shared_preload_libraries = 'pg_trickle'
  2. Restart PostgreSQL.
  3. Run: sql CREATE EXTENSION pg_trickle;

See INSTALL.md for platform-specific instructions and pre-built release artifacts.

What are the minimum configuration requirements?

The only mandatory setting is adding pg_trickle to shared_preload_libraries in postgresql.conf (this requires a PostgreSQL restart):

shared_preload_libraries = 'pg_trickle'

All other GUC parameters have sensible defaults and can be tuned later. The PostgreSQL default of max_worker_processes = 8 is sufficient for most deployments — pg_trickle uses one background worker per database.

Can I install pg_trickle on a managed PostgreSQL service (RDS, Cloud SQL, etc.)?

It depends on whether the service allows custom extensions and shared_preload_libraries modifications. Many managed services restrict these. However, pg_trickle has one advantage over replication-based extensions: it does not require wal_level = logical, which avoids one of the most common restrictions on managed PostgreSQL services.

Check your provider’s documentation for custom extension support. Services that support custom extensions (e.g., some tiers of Azure Flexible Server, Supabase, Neon) are more likely to work.

How do I uninstall pg_trickle?

  1. Drop all stream tables first (or they will be cascade-dropped): sql SELECT pgtrickle.drop_stream_table(pgt_name) FROM pgtrickle.pgt_stream_tables;
  2. Drop the extension: sql DROP EXTENSION pg_trickle CASCADE;
  3. Remove pg_trickle from shared_preload_libraries and restart PostgreSQL.

Creating & Managing Stream Tables

Do I need to choose a refresh mode?

No. The default mode ('AUTO') is adaptive: it uses differential (delta-only) maintenance when efficient, and automatically falls back to full recomputation when the change volume is high or the query cannot be differentiated. This works well for the vast majority of queries.

You only need to specify a mode explicitly when: - You want FULL mode to force recomputation every time (rare). - You want IMMEDIATE mode for sub-second, in-transaction updates (adds overhead to every write on source tables). - You want strict DIFFERENTIAL mode and prefer an error over silent fallback when the query isn’t differentiable.

How do I create a stream table?

-- Minimal: just name and query. Refreshes on a calculated schedule
-- using adaptive differential maintenance.
SELECT pgtrickle.create_stream_table(
    'order_totals',
    'SELECT customer_id, SUM(amount) AS total
     FROM orders GROUP BY customer_id'
);

-- With custom schedule:
SELECT pgtrickle.create_stream_table(
    name     => 'order_totals',
    query    => 'SELECT customer_id, SUM(amount) AS total
     FROM orders GROUP BY customer_id',
    schedule => '5m'
);

What is the difference between FULL and DIFFERENTIAL refresh mode?

  • FULL — Truncates the stream table and re-runs the entire defining query every refresh cycle. Simple but expensive for large result sets.
  • DIFFERENTIAL — Computes only the delta (changes since the last refresh) using the DVM engine and applies it via a MERGE statement. Much faster when only a small fraction of source data changes between refreshes. When the change ratio exceeds pg_trickle.differential_max_change_ratio (default 15%), DIFFERENTIAL automatically falls back to FULL for that cycle.
  • IMMEDIATE — Maintains the stream table synchronously within the same transaction as the base table DML. Uses statement-level triggers with transition tables — no change buffers, no scheduler. The stream table is always up-to-date.

Why does FULL mode exist if DIFFERENTIAL can fall back to it automatically?

DIFFERENTIAL mode with adaptive fallback covers most user needs — it uses incremental deltas when changes are small and automatically switches to a full recompute when the change ratio is high. However, explicit FULL mode still has its place:

  1. No CDC overhead. FULL mode installs CDC triggers on source tables (for DAG tracking), but the refresh itself ignores the change buffers entirely. If your workload has very high write throughput and you know you’ll always do a full recompute, FULL mode avoids the per-row trigger overhead of writing change records that will never be consumed incrementally.

  2. Simpler debugging. When investigating data correctness issues, FULL mode is a clean baseline — it re-runs the defining query with no delta computation, no frontier tracking, and no MERGE logic. If FULL produces correct results but DIFFERENTIAL doesn’t, the bug is in the delta pipeline.

  3. Predictable performance. DIFFERENTIAL refresh time varies with the number of changes, which can be unpredictable. FULL refresh time is proportional to the total result set size, which is stable. For SLA-sensitive workloads where you’d rather have consistent 500ms refreshes than variable 5ms–500ms refreshes, FULL provides that predictability.

  4. Unsupported-but-planned constructs. Some queries may parse correctly in DIFFERENTIAL mode but produce suboptimal deltas. Using FULL mode explicitly is a safe fallback while the DVM engine matures.

For most users, DIFFERENTIAL is the right default. Use FULL when you have a specific reason.

When should I use FULL vs. DIFFERENTIAL vs. IMMEDIATE?

Use DIFFERENTIAL (default) when: - Source tables are large and changes between refreshes are small - The defining query uses supported operators (most common SQL is supported) - Some staleness (seconds to minutes) is acceptable

Use FULL when: - The defining query uses unsupported aggregates (CORR, COVAR_*, REGR_*) - Source tables are small and a full recompute is cheap - You see frequent adaptive fallbacks to FULL (check refresh history)

Use IMMEDIATE when: - The stream table must always reflect the latest committed data - You need transactional consistency (reads within the same transaction see updated data) - Write-side overhead per DML statement is acceptable - The defining query is relatively simple (no TopK, no materialized view sources)

What are the advantages and disadvantages of IMMEDIATE vs. deferred (FULL/DIFFERENTIAL) refresh modes?

IMMEDIATE mode

Detail
✅ Read-your-writes consistency The stream table is updated within the same transaction as the base table DML — always current from the writer’s perspective.
✅ No lag No background worker, no schedule interval. The view is never stale.
✅ No change buffers pgtrickle_changes.* tables are not used, reducing write overhead on source tables.
✅ pg_ivm compatibility Drop-in migration path for existing pg_ivm / IMMV users.
❌ Write amplification Every DML statement on a base table also executes IVM trigger logic, adding latency to the original transaction.
❌ Serialized concurrent writes An ExclusiveLock is taken on the stream table during maintenance, serializing writers.
❌ Limited SQL support Window functions, recursive CTEs, LATERAL joins, scalar subqueries, and TopK (ORDER BY … LIMIT) are not supported — use DIFFERENTIAL instead.
❌ Cascading limitations Cascading IMMEDIATE stream tables work but may require manual refresh for deep chains.
❌ No throttling The refresh cannot be delayed or rate-limited.

Deferred mode (FULL / DIFFERENTIAL)

Detail
✅ Decoupled write path Base table writes are fast; view maintenance runs later via the scheduler or manual refresh.
✅ Broadest SQL support Window functions, recursive CTEs, LATERAL, UNION, user-defined aggregates, TopK, cascading stream tables, and more.
✅ Adaptive cost control DIFFERENTIAL automatically falls back to FULL when the change ratio exceeds pg_trickle.differential_max_change_ratio.
✅ Concurrency-friendly Writers never block on view maintenance.
❌ Staleness The stream table lags by up to one schedule interval (e.g. 1m).
❌ No read-your-writes A writer querying the stream table immediately after a write may see the pre-change data.
❌ Infrastructure overhead Requires change buffer tables, a background worker, and frontier tracking.

Rule of thumb: use IMMEDIATE when the query is simple and freshness within the transaction matters. Use DIFFERENTIAL (or FULL) for complex queries, high concurrency, or when you want to decouple write latency from view maintenance.

What happens if I have an IMMEDIATE stream table between two DIFFERENTIAL stream tables in a dependency chain?

Consider the chain: source → ST_A (DIFFERENTIAL) → ST_B (IMMEDIATE) → ST_C (DIFFERENTIAL). This is a valid but unusual configuration with important behavioral consequences:

  • ST_A refreshes on its schedule (e.g., every 1 minute) via the background scheduler.
  • ST_B is IMMEDIATE, so it has no CDC triggers on ST_A — it uses statement-level IVM triggers. But ST_A is updated by the scheduler (not by user DML), and the scheduler’s MERGE operation does fire statement-level triggers on ST_A’s dependents. So ST_B updates within the scheduler’s transaction when ST_A refreshes.
  • ST_C is DIFFERENTIAL and depends on ST_B. Since ST_B is a stream table, ST_C’s CDC triggers fire when ST_B is modified. The scheduler refreshes ST_C on its own schedule.

The practical concern: write latency stacking. When the scheduler refreshes ST_A, ST_B’s IVM triggers fire synchronously within that same transaction, adding IVM overhead to ST_A’s refresh. If ST_B’s delta computation is expensive, it slows down the entire scheduler cycle.

Recommendation: Avoid mixing IMMEDIATE into the middle of a deferred chain. Either make the entire chain IMMEDIATE (for small, simple queries) or keep it entirely DIFFERENTIAL. If you need read-your-writes for one specific step, consider making that the terminal (leaf) stream table in the chain.

What schedule formats are supported?

Duration strings:

Unit Suffix Example
Seconds s 30s
Minutes m 5m
Hours h 2h
Days d 1d
Weeks w 1w
Compound — 1h30m

Cron expressions:

Format Example Description
5-field */5 * * * * Every 5 minutes
Aliases @hourly, @daily Built-in shortcuts

CALCULATED mode: Pass NULL as the schedule to inherit the schedule from downstream dependents.

How do cron schedules handle timezones? What does @daily really mean?

pg_trickle evaluates cron expressions in UTC. The underlying croner crate computes the next occurrence from a UTC timestamp, and the scheduler compares this against chrono::Utc::now(). There is no per-stream-table timezone setting.

This means: - @daily (equivalent to 0 0 * * *) fires at midnight UTC, not midnight in your local timezone. - @hourly (equivalent to 0 * * * *) fires at the top of each UTC hour. - 0 9 * * 1-5 fires at 09:00 UTC on weekdays — if your server is in America/New_York, that’s 04:00 or 05:00 local time depending on DST.

If you need a schedule aligned to a local timezone, convert the desired local time to UTC and write the cron expression accordingly. For example, to refresh at 08:00 Europe/Oslo (UTC+1 in winter, UTC+2 in summer), use 0 6 * * * in summer and 0 7 * * * in winter — or accept the 1-hour seasonal shift and pick one.

Tip: For most analytics workloads, UTC-based schedules are preferable because they don’t shift with daylight saving transitions.

What is the minimum allowed schedule?

The pg_trickle.min_schedule_seconds GUC (default: 60 seconds) sets the shortest allowed refresh schedule. Any create_stream_table or alter_stream_table call with a schedule shorter than this floor is rejected with a clear error message.

This guard exists to prevent accidentally creating stream tables that refresh too frequently, which could overload the scheduler or the source tables. During development and testing, you can lower it:

ALTER SYSTEM SET pg_trickle.min_schedule_seconds = 1;
SELECT pg_reload_conf();

What happens if all stream tables in the DAG have a CALCULATED schedule?

When every stream table uses a CALCULATED schedule (schedule => 'calculated'), there are no explicit schedules for the resolution algorithm to derive from. The CALCULATED logic works by propagating MIN(effective_schedule) from downstream dependents upward through the DAG. If no node has an explicit duration:

  1. Leaf nodes (no downstream dependents) have no schedules to take the minimum of, so they fall back to the pg_trickle.min_schedule_seconds GUC (default: 60 seconds).
  2. Upstream nodes then resolve to MIN(fallback) = fallback.
  3. The result: every stream table in the DAG gets the fallback schedule (60 s by default).

This is safe but usually not what you want — the whole DAG refreshes at the same generic interval. Best practice is to set an explicit schedule on at least the leaf (most-downstream) stream tables so that upstream CALCULATED schedules resolve to something meaningful:

-- Leaf ST with an explicit schedule
SELECT pgtrickle.create_stream_table(
    name     => 'daily_summary',
    query    => 'SELECT region, SUM(total) FROM pgtrickle.order_totals GROUP BY region',
    schedule => '10m'
);

-- Upstream ST inherits that 10 m schedule via CALCULATED
SELECT pgtrickle.create_stream_table(
    name     => 'order_totals',
    query    => 'SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id',
    schedule => 'calculated'
);

You can inspect the resolved effective schedules with:

SELECT pgt_name, schedule, effective_schedule
FROM pgtrickle.pgt_stream_tables;

Can a stream table reference another stream table?

Yes. Stream tables can depend on other stream tables. The scheduler automatically refreshes them in topological order (upstream first). Circular dependencies are detected and rejected at creation time.

-- ST1: aggregates orders
SELECT pgtrickle.create_stream_table(
    name         => 'order_totals',
    query        => 'SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id',
    schedule     => '1m',
    refresh_mode => 'DIFFERENTIAL'
);

-- ST2: filters ST1
SELECT pgtrickle.create_stream_table(
    name         => 'big_customers',
    query        => 'SELECT customer_id, total FROM pgtrickle.order_totals WHERE total > 1000',
    schedule     => '1m',
    refresh_mode => 'DIFFERENTIAL'
);

How do I change a stream table’s schedule or mode?

-- Change schedule
SELECT pgtrickle.alter_stream_table('order_totals', schedule => '10m');

-- Switch refresh mode
SELECT pgtrickle.alter_stream_table('order_totals', refresh_mode => 'FULL');

-- Suspend
SELECT pgtrickle.alter_stream_table('order_totals', status => 'SUSPENDED');

-- Resume
SELECT pgtrickle.alter_stream_table('order_totals', status => 'ACTIVE');

Can I change the defining query of a stream table?

Yes — use the query parameter of alter_stream_table():

SELECT pgtrickle.alter_stream_table('order_totals',
    query => 'SELECT customer_id, SUM(amount) AS total, COUNT(*) AS order_count
              FROM orders GROUP BY customer_id');

The ALTER QUERY operation validates the new query, migrates the storage table schema if needed, updates catalog entries and source dependencies, and runs a full refresh — all within a single transaction. Concurrent readers see either the old data or the new data, never an empty table.

Schema migration behavior:

Schema change Behavior
Same columns Fast path — no storage DDL, just catalog update + full refresh
Columns added or removed Compatible migration via ALTER TABLE ADD/DROP COLUMN — storage table OID preserved
Column type incompatible Full rebuild — storage table dropped and recreated (OID changes, WARNING emitted)

You can also change the query and other parameters simultaneously:

SELECT pgtrickle.alter_stream_table('order_totals',
    query => 'SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id',
    refresh_mode => 'FULL');

How do I deploy stream tables idempotently?

Use create_or_replace_stream_table() — one function call that does the right thing automatically:

-- Safe to run on every deploy — creates, updates, or no-ops as needed:
SELECT pgtrickle.create_or_replace_stream_table(
    name         => 'order_totals',
    query        => 'SELECT region, SUM(amount) AS total FROM orders GROUP BY region',
    schedule     => '2m',
    refresh_mode => 'DIFFERENTIAL'
);

What happens on each deploy:

Situation Action
First deploy (stream table doesn’t exist) Creates it, populates data
Nothing changed since last deploy No-op — logs INFO, returns instantly
You changed the schedule or mode Updates config in place (no data loss)
You changed the query Migrates storage schema + runs a full refresh

This mirrors PostgreSQL’s CREATE OR REPLACE VIEW / CREATE OR REPLACE FUNCTION pattern.

When to use which function:

Function Use case
create_or_replace_stream_table() Recommended for most deployments. Declarative, idempotent — handles all cases automatically.
create_stream_table_if_not_exists() Safe re-run, but never modifies an existing definition. Good for one-time seed migrations.
create_stream_table() Strict mode — errors if the stream table already exists. Use when you want an explicit failure on duplicates.

How do I trigger a manual refresh?

Call refresh_stream_table() to immediately refresh a stream table without waiting for the next scheduled cycle:

SELECT pgtrickle.refresh_stream_table('order_totals');

This runs a synchronous refresh in your current session and returns when complete. It works even when the background scheduler is disabled (pg_trickle.enabled = false), making it useful for testing, debugging, or one-off data refreshes.

To force a full refresh regardless of the stream table’s configured mode:

SELECT pgtrickle.refresh_stream_table('order_totals', force_full => true);

Data Freshness & Consistency

Understanding when and how stream tables become current is the #1 conceptual hurdle for users coming from synchronous materialized views. This section explains staleness guarantees, read-your-writes behavior, and Delayed View Semantics (DVS).

How stale can a stream table be?

For deferred modes (FULL / DIFFERENTIAL): A stream table can be at most one schedule interval behind the source data, plus the time it takes to execute the refresh itself. For example, with schedule => '1m', the maximum staleness is approximately 1 minute + refresh duration.

In practice, staleness is often less than the schedule interval because the scheduler continuously checks for due refreshes at pg_trickle.scheduler_interval_ms (default: 1 second).

For IMMEDIATE mode: The stream table is always current within the transaction that modified the source data. There is zero staleness.

Check current staleness:

SELECT pgtrickle.get_staleness('order_totals');  -- returns seconds, NULL if never refreshed

-- Or check all stream tables:
SELECT pgt_name, staleness, stale FROM pgtrickle.stream_tables_info;

Can I read my own writes immediately after an INSERT?

It depends on the refresh mode:

  • IMMEDIATE mode: Yes. The stream table is updated within the same transaction as your INSERT. You can query it immediately and see the updated data.
  • DIFFERENTIAL / FULL mode: No. The stream table is updated by the background scheduler in a separate transaction. Your INSERT is captured by the CDC trigger, but the stream table won’t reflect it until the next scheduled refresh (or a manual refresh_stream_table() call).

If read-your-writes consistency is a requirement, use refresh_mode => 'IMMEDIATE'.

What consistency guarantees does pg_trickle provide?

pg_trickle provides Delayed View Semantics (DVS): the contents of every stream table are logically equivalent to evaluating its defining query at some past point in time — the data_timestamp. This means:

  • The data is always internally consistent — it corresponds to a valid snapshot of the source data.
  • The data may be stale — it reflects the source state at data_timestamp, not necessarily the current state.
  • For cascading stream tables, the scheduler refreshes in topological order so that when ST B references upstream ST A, A has already been refreshed before B runs its delta query against A’s contents.

For IMMEDIATE mode, the guarantee is stronger: the stream table always reflects the state of the source data as of the current transaction.

What are “Delayed View Semantics” (DVS)?

DVS is the formal consistency guarantee: a stream table’s contents are equivalent to evaluating its defining query at a specific past time (the data_timestamp). This is analogous to how a materialized view captured at a point in time is always internally consistent, even if the source data has since changed.

The data_timestamp is recorded in the catalog and advanced after each successful refresh:

SELECT pgt_name, data_timestamp FROM pgtrickle.pgt_stream_tables;

What happens if the scheduler is behind — does data get lost?

No. Change data is never lost, even if the scheduler falls behind. Changes accumulate in the change buffer tables (pgtrickle_changes.changes_<oid>) until consumed by a refresh. The frontier ensures that each refresh picks up exactly where the last one left off.

However, a growing change buffer increases: - Disk usage (change buffer tables grow) - Refresh time (more changes to process per cycle) - Risk of adaptive fallback to FULL (if the change ratio exceeds pg_trickle.differential_max_change_ratio)

The monitoring system emits a buffer_growth_warning NOTIFY alert if buffers grow unexpectedly.

How does pg_trickle ensure deltas are applied in the right order across cascading stream tables?

The scheduler uses topological ordering from the dependency DAG. When ST B depends on ST A:

  1. ST A is refreshed first — its data is brought up to date and its frontier advances.
  2. ST A’s refresh writes are captured by CDC triggers (since ST A is a source for ST B).
  3. ST B is refreshed next — its delta query reads ST A’s current (just-refreshed) data and the change buffer.

This ensures that downstream stream tables always see consistent upstream data. Circular dependencies are rejected at creation time.

IMMEDIATE Mode (Transactional IVM)

IMMEDIATE mode maintains the stream table synchronously — within the same transaction as the source DML. This section covers when to use it, what SQL it supports, locking behavior, and how to switch between modes.

When should I use IMMEDIATE mode instead of DIFFERENTIAL?

Use IMMEDIATE when: - Your application requires read-your-writes consistency — e.g., a user inserts an order and immediately queries a dashboard that must include that order. - The defining query is relatively simple (single-table aggregation, joins, filters). - The source table write rate is moderate (IMMEDIATE adds latency to every DML statement).

Stick with DIFFERENTIAL when: - Staleness of a few seconds to minutes is acceptable. - The defining query uses unsupported IMMEDIATE constructs (materialized-view sources, foreign-table sources). - Write-side performance is critical (high-throughput OLTP). - You need to decouple write latency from view maintenance.

What SQL features are NOT supported in IMMEDIATE mode?

IMMEDIATE mode supports all constructs that DIFFERENTIAL supports, with two source-type exceptions:

Feature Status Notes
WITH RECURSIVE ✅ Supported (IM1) Semi-naive evaluation inside the trigger. A depth counter guards against infinite loops (pg_trickle.ivm_recursive_max_depth, default 100). A warning is emitted at create time for very deep hierarchies.
TopK (ORDER BY … LIMIT N [OFFSET M]) ✅ Supported (IM2) Micro-refresh: recomputes the top-N rows on every DML statement. Gated by pg_trickle.ivm_topk_max_limit to prevent unbounded scans.
Materialized views as sources ❌ Rejected Stale-snapshot prevents trigger-based capture — use the underlying query instead.
Foreign tables as sources ❌ Rejected No triggers on foreign tables — use FULL mode instead.

Attempting to create or switch to IMMEDIATE mode with an unsupported construct produces a clear error message.

What happens when I TRUNCATE a source table in IMMEDIATE mode?

A statement-level AFTER TRUNCATE trigger fires and truncates the stream table, then re-populates it by executing a full refresh from the defining query — all within the same transaction. The stream table remains consistent.

Can I have cascading IMMEDIATE stream tables (ST A → ST B)?

Yes. When ST A is IMMEDIATE and ST B depends on ST A and is also IMMEDIATE, changes propagate through the chain within the same transaction. The IVM triggers on the base table update ST A, and since that write is visible within the transaction, ST B’s triggers fire and update ST B.

What locking does IMMEDIATE mode use?

IMMEDIATE mode acquires statement-level locks on the stream table during delta application:

  • Simple queries (single-table scan/filter without aggregates or DISTINCT): RowExclusiveLock — allows concurrent readers, blocks other writers.
  • Complex queries (joins, aggregates, DISTINCT, window functions): ExclusiveLock — blocks both readers and writers to ensure delta consistency.

This means concurrent writes to the same base table are serialized through the stream table lock. For high-concurrency write workloads, DIFFERENTIAL mode avoids this bottleneck.

How do I switch an existing DIFFERENTIAL stream table to IMMEDIATE?

SELECT pgtrickle.alter_stream_table('order_totals', refresh_mode => 'IMMEDIATE');

This: 1. Validates the defining query against IMMEDIATE mode restrictions. 2. Removes the row-level CDC triggers from source tables. 3. Installs statement-level IVM triggers (BEFORE + AFTER with transition tables). 4. Clears the schedule (IMMEDIATE mode has no schedule). 5. Performs a full refresh to establish a consistent baseline.

To switch back:

SELECT pgtrickle.alter_stream_table('order_totals', refresh_mode => 'DIFFERENTIAL');

This reverses the process: removes IVM triggers, installs CDC triggers, restores the schedule (default 1m), and performs a full refresh.

What happens to IMMEDIATE mode during a manual refresh_stream_table() call?

For IMMEDIATE mode stream tables, refresh_stream_table() performs a FULL refresh — truncates and re-populates from the defining query. This is useful for recovering from edge cases or forcing a clean baseline. It is equivalent to pg_ivm’s refresh_immv(name, true).

How much write-side overhead does IMMEDIATE mode add?

Each DML statement on a base table tracked by an IMMEDIATE stream table incurs:

  • BEFORE trigger: Advisory lock acquisition + pre-state setup (~0.1–0.5 ms).
  • AFTER trigger: Transition table copy to temp tables + delta SQL generation + delta application (~1–50 ms depending on query complexity and delta size).

For a simple single-table aggregate, expect 2–10 ms overhead per statement. For multi-table joins or window functions, overhead is higher. The overhead scales with the number of IMMEDIATE stream tables that depend on the same source table.

SQL Support

pg_trickle supports a broad range of SQL in defining queries. This section covers what’s supported, what’s rejected (with rewrites), and how specific constructs like aggregates and ORDER BY are handled. The subsections that follow dive deeper into aggregates, joins, CTEs, window functions, and TopK.

What SQL features are supported in defining queries?

Most common SQL is supported in both FULL and DIFFERENTIAL modes:

  • Table scans, projections, WHERE/HAVING filters
  • INNER, LEFT, RIGHT, FULL OUTER JOIN (including multi-table joins)
  • GROUP BY with 25+ aggregate functions (COUNT, SUM, AVG, MIN, MAX, BOOL_AND/OR, STRING_AGG, ARRAY_AGG, JSON_AGG, JSONB_AGG, BIT_AND/OR/XOR, STDDEV, VARIANCE, MODE, PERCENTILE_CONT/DISC, and more)
  • FILTER (WHERE ...) on aggregates
  • DISTINCT
  • Set operations: UNION ALL, UNION, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL
  • Subqueries: EXISTS, NOT EXISTS, IN (subquery), NOT IN (subquery), scalar subqueries
  • Non-recursive and recursive CTEs
  • Window functions (ROW_NUMBER, RANK, SUM OVER, etc.)
  • LATERAL joins with set-returning functions and correlated subqueries
  • CASE, COALESCE, NULLIF, GREATEST, LEAST, BETWEEN, IS DISTINCT FROM

See DVM Operators for the complete list.

What SQL features are NOT supported?

The following are rejected with clear error messages and suggested rewrites:

Feature Reason Suggested Rewrite
TABLESAMPLE Stream tables materialize the full result set Use WHERE random() < fraction in consuming query
Window functions in expressions Cannot be differentially maintained Move window function to a separate column
LIMIT / OFFSET (without ORDER BY) Stream tables materialize the full result set; ORDER BY … LIMIT N [OFFSET M] is supported as TopK Apply when querying the stream table, or add ORDER BY + LIMIT to use the TopK pattern
FOR UPDATE / FOR SHARE Row-level locking not applicable Remove the locking clause
RANGE_AGG / RANGE_INTERSECT_AGG No incremental delta decomposition exists for range aggregates Use FULL mode, or compute range unions in the consuming query

Each rejected feature is explained in detail in the Why Are These SQL Features Not Supported? section below.

What happens to ORDER BY in defining queries?

ORDER BY in the defining query is accepted but silently discarded. This is consistent with how PostgreSQL handles CREATE MATERIALIZED VIEW AS SELECT ... ORDER BY ... — the ordering only affects the initial INSERT, not the stored data.

Stream tables are heap tables with no guaranteed row order. Apply ORDER BY when querying the stream table instead:

-- Don't rely on ORDER BY in the defining query:
-- 'SELECT region, SUM(amount) AS total FROM orders GROUP BY region ORDER BY total DESC'

-- Instead, order when reading:
SELECT * FROM regional_totals ORDER BY total DESC;

Exception: When ORDER BY is paired with LIMIT N (with or without OFFSET M), pg_trickle recognizes the TopK pattern and preserves the ordering, limit, and offset.

Which aggregates support DIFFERENTIAL mode?

Algebraic (O(changes), fully incremental): COUNT, SUM, AVG

Semi-algebraic (incremental with occasional group rescan): MIN, MAX

Group-rescan (affected groups re-aggregated from source): STRING_AGG, ARRAY_AGG, JSON_AGG, JSONB_AGG, BOOL_AND, BOOL_OR, BIT_AND, BIT_OR, BIT_XOR, JSON_OBJECT_AGG, JSONB_OBJECT_AGG, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, MODE, PERCENTILE_CONT, PERCENTILE_DISC, CORR, COVAR_POP, COVAR_SAMP, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY

37 aggregate function variants are supported in total.

Aggregates & Group-By

Aggregate handling is one of the most complex parts of incremental view maintenance. This section explains how pg_trickle categorizes aggregates by their incremental cost, how hidden auxiliary columns work, and what happens when groups are created or destroyed.

Which aggregates are fully incremental (O(1) per change) vs. group-rescan?

pg_trickle categorizes aggregates into three tiers:

Tier Cost per change Aggregates Mechanism
Algebraic O(1) COUNT, SUM, AVG Hidden auxiliary columns (__pgt_count, __pgt_sum_x) track running totals. Delta updates these columns arithmetically.
Semi-algebraic O(1) normally, O(group) on extremum deletion MIN, MAX Maintained via LEAST/GREATEST. If the current MIN/MAX is deleted, the group is rescanned to find the new extremum.
Group-rescan O(group size) per affected group All others (35 functions) Affected groups are re-aggregated from source data. A NULL sentinel marks stale groups for rescan.

For most workloads, the algebraic tier (COUNT/SUM/AVG) covers the majority of aggregations and is the fastest.

Why do some aggregates have hidden auxiliary columns?

For algebraic aggregates (COUNT, SUM, AVG), the DVM engine adds hidden __pgt_count and __pgt_sum_x columns to the stream table’s storage. These store running totals that can be updated with O(1) arithmetic per change instead of rescanning the entire group.

For example, a stream table defined as SELECT dept, AVG(salary) FROM employees GROUP BY dept internally stores: - dept — the group-by key - avg — the user-visible average (computed as __pgt_sum_x / __pgt_count) - __pgt_count — running count of rows in the group - __pgt_sum_x — running sum of salary values - __pgt_row_id — row identity hash

When a new employee is inserted, the refresh updates __pgt_count += 1, __pgt_sum_x += new_salary, and recomputes avg. No rescan of the source table is needed.

How does HAVING work with incremental refresh?

HAVING is fully supported in DIFFERENTIAL mode. The DVM engine tracks threshold transitions — groups entering or exiting the HAVING condition:

  • Group crosses threshold upward: A previously excluded group (e.g., HAVING COUNT(*) > 5) gains enough members → the group is inserted into the stream table.
  • Group crosses threshold downward: A group that was included drops below the threshold → the group is deleted from the stream table.
  • Group stays above threshold: Normal delta update (adjust aggregate values).

This means the stream table always reflects only the groups that satisfy the HAVING clause, even as group membership changes.

What happens to a group when all its rows are deleted?

When the last row of a group is deleted from the source table, the DVM engine detects that __pgt_count drops to zero and deletes the group row from the stream table. The hidden auxiliary columns are cleaned up along with it.

If a new row for the same group-by key is later inserted, a fresh group row is created from scratch.

Why are CORR, COVAR_*, and REGR_* limited to FULL mode?

Regression aggregates like CORR, COVAR_POP, COVAR_SAMP, and the REGR_* family require maintaining running sums of products and squares across the entire group. Unlike COUNT/SUM/AVG (where deltas can be computed from the change alone), regression aggregates:

  1. Lack algebraic delta rules. There is no closed-form way to update a correlation coefficient from a single row change without access to the full group’s data.
  2. Would degrade to group-rescan anyway. Even if supported, the implementation would need to rescan the full group from source — identical to FULL mode for most practical group sizes.

These aggregates work fine in FULL refresh mode, which re-runs the entire query from scratch each cycle.

Joins

Join delta computation can produce surprising results when both sides change simultaneously. This section covers the standard IVM join rule, FULL OUTER JOIN support, and known edge cases.

How does a DIFFERENTIAL refresh handle a join when both sides changed?

When both tables in a join have changes since the last refresh, the DVM engine computes the join delta using the standard IVM join rule:

$$\Delta(R \bowtie S) = (\Delta R \bowtie S) \cup (R \bowtie \Delta S) \cup (\Delta R \bowtie \Delta S)$$

In practice, this means: 1. Join the changes from the left against the current state of the right. 2. Join the current state of the left against the changes from the right. 3. Join the changes from both sides (handles simultaneous changes to matching keys).

All three parts are combined into a single CTE-based delta query that PostgreSQL executes in one pass.

Does pg_trickle support FULL OUTER JOIN incrementally?

Yes. FULL OUTER JOIN is supported in DIFFERENTIAL mode with an 8-part delta computation. This handles all four cases: matched rows on both sides, left-only rows, right-only rows, and rows that transition between matched and unmatched states as data changes.

The 8 parts cover: new left matches, removed left matches, new right matches, removed right matches, newly matched from left-only, newly matched from right-only, newly unmatched to left-only, and newly unmatched to right-only.

What happens when a join key is updated and the joined row is simultaneously deleted?

This is a known edge case. When a join key column is updated in the same refresh cycle as the joined-side row is deleted, the delta may miss the required DELETE, potentially leaving a stale row in the stream table.

Mitigations: - The adaptive FULL fallback (triggered when the change ratio exceeds pg_trickle.differential_max_change_ratio) catches most high-change-rate scenarios where this is likely. - You can stagger changes across refresh cycles. - Use FULL mode for tables where this pattern is common.

How does NATURAL JOIN work?

NATURAL JOIN is fully supported. At parse time, pg_trickle resolves the common columns between the two tables and synthesizes explicit equi-join conditions. The internal __pgt_row_id column is excluded from common column resolution, so NATURAL JOINs between stream tables also work correctly.

CTEs & Recursive Queries

Recursive CTE support is a key differentiator for pg_trickle. This section explains the three maintenance strategies (semi-naive, DRed, recomputation) and when each is used.

Do recursive CTEs work in DIFFERENTIAL mode?

Yes. pg_trickle supports WITH RECURSIVE in DIFFERENTIAL mode with three auto-selected strategies:

Strategy When used How it works
Semi-naive evaluation INSERT-only changes to the base case Iteratively evaluates new derivations from the inserted rows without touching existing rows. Fastest path.
Delete-and-Rederive (DRed) Mixed changes (INSERT + DELETE/UPDATE) Deletes potentially affected derived rows, then rederives them from scratch to determine the true delta.
Recomputation fallback Column mismatch or non-monotone recursive terms Falls back to full recomputation of the recursive CTE. Used when the recursive term contains EXCEPT, Aggregate, Window, DISTINCT, AntiJoin, or INTERSECT SET operators.

The strategy is selected automatically based on the type of changes and the recursive term’s structure.

What are the three strategies for recursive CTE maintenance?

See the table above. In brief:

  • Semi-naive is the fast path for append-only workloads (e.g., adding nodes to a tree). It’s O(new derivations) — much cheaper than a full re-evaluation.
  • DRed handles deletions and updates correctly by first removing potentially invalidated rows and then rederiving them. More expensive than semi-naive, but still incremental.
  • Recomputation is the safe fallback that re-executes the entire recursive CTE. Used when the recursive term’s structure is too complex for incremental processing.

What triggers a fallback from semi-naive to recomputation?

A recomputation fallback is triggered when:

  1. The recursive term contains non-monotone operators — EXCEPT, Aggregate, Window, DISTINCT, AntiJoin, or INTERSECT SET. These operators can “un-derive” rows when inputs change, which semi-naive evaluation cannot handle.
  2. Column mismatch — the CTE’s output columns don’t match the stream table’s storage schema (e.g., after a schema change).
  3. Mixed DML with non-monotone terms — DELETE or UPDATE changes combined with non-monotone recursive terms always trigger recomputation.

Check which strategy was used in the refresh history:

SELECT action, rows_inserted, rows_deleted
FROM pgtrickle.get_refresh_history('my_recursive_st', 5);

What happens when a CTE is referenced multiple times in the same query?

When a non-recursive CTE is referenced more than once, pg_trickle uses shared delta computation — the CTE’s delta is computed once and cached, then reused by each reference. This is tracked via CteScan operator nodes that look up the shared delta from an internal CTE registry.

For single-reference CTEs, pg_trickle simply inlines them as subqueries (no overhead).

Window Functions & LATERAL

Window functions are maintained via partition-based recomputation rather than row-level deltas. This section covers what’s supported, the expression restriction, and LATERAL constructs.

How are window functions maintained incrementally?

pg_trickle uses partition-based recomputation for window functions. When source data changes, the DVM engine:

  1. Identifies which partitions are affected by the changes (based on the PARTITION BY key).
  2. Recomputes the window function for only the affected partitions.
  3. Replaces the old partition results with the new ones in the stream table.

This is more efficient than a full recomputation when changes affect a small number of partitions.

Why can’t I use a window function inside a CASE or COALESCE expression?

Window functions like ROW_NUMBER() OVER (…) are supported as standalone columns but cannot be embedded in expressions (e.g., CASE WHEN ROW_NUMBER() OVER (...) = 1 THEN ...).

This restriction exists because the DVM engine handles window functions by recomputing entire partitions. When a window function is buried inside an expression, the engine cannot isolate the window computation from the surrounding expression.

Rewrite: Move the window function to a separate column in one stream table, then reference it in a second stream table:

-- ST1: compute the window function
SELECT id, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees

-- ST2: use it in an expression (references ST1)
SELECT id, CASE WHEN rn = 1 THEN 'top' ELSE 'other' END AS rank_label
FROM st1

What LATERAL constructs are supported?

pg_trickle supports three kinds of LATERAL constructs:

Construct Example Delta strategy
Set-returning functions LATERAL jsonb_array_elements(data) Row-scoped recomputation — only affected parent rows are re-expanded
Correlated subqueries LATERAL (SELECT ... WHERE t.id = s.id) Row-scoped recomputation
JSON_TABLE (PG 17+) JSON_TABLE(data, '$.items[*]' ...) Modeled as LateralFunction

Additional supported SRFs: jsonb_each, jsonb_each_text, unnest, generate_series, and others.

What happens when a row moves between window partitions during a refresh?

When a row’s PARTITION BY key changes (e.g., an employee moves departments), the DVM engine recomputes both the old partition (to remove the row) and the new partition (to add it). Both partitions are re-evaluated from the source data, ensuring window function results are correct.

TopK (ORDER BY … LIMIT)

TopK queries (ORDER BY ... LIMIT N, optionally with OFFSET M) are handled via a specialized MERGE-based strategy that re-executes the bounded query each cycle. This section explains how it works and its limitations.

How does ORDER BY … LIMIT N work in a stream table?

When a defining query has a top-level ORDER BY … LIMIT N (with a constant integer N), pg_trickle recognizes it as a TopK pattern. An optional OFFSET M (constant integer) selects a “page” within the ranked result. The stream table stores exactly N rows and is refreshed via a MERGE-based scoped-recomputation strategy:

  1. On each refresh, the full query (with ORDER BY + LIMIT, and OFFSET if present) is re-executed against the source tables.
  2. The result is merged into the stream table using MERGE with NOT MATCHED BY SOURCE for deletes.
  3. The catalog records topk_limit, topk_order_by, and optionally topk_offset for the stream table.

TopK bypasses the DVM delta pipeline — it always re-executes the bounded query. This is efficient because the result set is bounded by N.

SELECT pgtrickle.create_stream_table(
    name         => 'top_customers',
    query        => 'SELECT customer_id, total FROM order_totals ORDER BY total DESC LIMIT 100',
    schedule     => '1m',
    refresh_mode => 'DIFFERENTIAL'
);

-- With OFFSET — "page 2" of the leaderboard (rows 101–200):
SELECT pgtrickle.create_stream_table(
    name         => 'next_customers',
    query        => 'SELECT customer_id, total FROM order_totals ORDER BY total DESC LIMIT 100 OFFSET 100',
    schedule     => '1m',
    refresh_mode => 'DIFFERENTIAL'
);

Does OFFSET work with TopK?

Yes. ORDER BY … LIMIT N OFFSET M is fully supported. The stream table stores exactly N rows starting from position M+1 in the ranked result. This is useful for:

  • Paginated dashboards: Each page is a separate stream table with a different OFFSET.
  • Excluding outliers: OFFSET 5 LIMIT 50 skips the top 5 and shows the next 50.
  • Windowed leaderboards: OFFSET 10 LIMIT 10 shows the “second tier.”

Caveat: When source data changes, the “page” can shift — a row on page 3 may move to page 2 or 4. The stream table always reflects the current state of the page at the time of the last refresh.

OFFSET 0 is treated as no offset.

What happens when a row below the top-N cutoff rises above it?

On the next refresh, the full ORDER BY … LIMIT N query is re-executed. The newly qualifying row appears in the result, and the row that fell out of the top-N is removed. The MERGE operation handles this by:

  • INSERT the newly qualifying row
  • DELETE the row that fell below the cutoff
  • UPDATE any rows whose values changed but remained in the top-N

Since TopK always re-executes the bounded query, it correctly detects all ranking changes.

Can I use TopK with aggregates or joins?

Yes. The defining query can contain any SQL that pg_trickle supports, plus ORDER BY … LIMIT N:

-- TopK over an aggregate
SELECT dept, SUM(salary) AS total_salary
FROM employees GROUP BY dept
ORDER BY total_salary DESC LIMIT 10

-- TopK over a join
SELECT e.name, d.name AS dept, e.salary
FROM employees e JOIN departments d ON e.dept_id = d.id
ORDER BY e.salary DESC LIMIT 20

The only restriction is that TopK cannot be combined with set operations (UNION/INTERSECT/EXCEPT) or GROUPING SETS/CUBE/ROLLUP.

Tables Without Primary Keys

While primary keys are not required, their absence changes how pg_trickle identifies rows. This section explains the content-based hashing fallback and its limitations with duplicate rows.

Do source tables need a primary key?

No, but it is strongly recommended. When a source table has a primary key, pg_trickle uses it to generate a deterministic __pgt_row_id for each row — this is the most reliable way to track row identity across refreshes.

Without a primary key, pg_trickle falls back to content-based hashing — an xxHash of all column values. This works correctly for tables where every row is unique, but has known issues with exact duplicate rows. See What are the risks of using tables without primary keys? for details.

What are the risks of using tables without primary keys?

Content-based row identity has known limitations with exact duplicate rows (rows where every column value is identical):

  1. INSERT as no-op: If a row identical to an existing one is inserted, both have the same __pgt_row_id hash, so the MERGE treats it as a no-op (the row already exists).
  2. DELETE removes all copies: Deleting one of N identical rows generates a DELETE delta, but the MERGE removes all rows with that __pgt_row_id.
  3. Aggregate drift: Over time, these mismatches can cause aggregate values to drift from the true result.

Recommendation: Add a primary key or unique constraint to source tables, or use FULL mode for tables with frequent exact-duplicate rows.

How does content-based row identity work for duplicate rows?

For tables without a primary key, __pgt_row_id is computed as pg_trickle_hash_multi(ARRAY[col1::text, col2::text, ...]) — an xxHash of all column values. Rows with identical content produce identical hashes.

The hash uses \x1E (record separator) between values and \x00NULL\x00 for NULL values, minimizing collision risk for rows with different content. However, truly identical rows (same values in every column) will always hash to the same value — this is inherent to content-based identity.

Change Data Capture (CDC)

This section explains how pg_trickle captures changes to your source tables, the trade-offs between trigger-based and WAL-based CDC, and operational topics like backup/restore and buffer inspection.

How does pg_trickle capture changes to source tables?

pg_trickle installs AFTER INSERT/UPDATE/DELETE row-level PL/pgSQL triggers on each source table referenced by a stream table. Whenever a row in the source table is modified, the trigger writes a change record into a per-source buffer table in the pgtrickle_changes schema.

Each change record contains: - Action — I (insert), U (update), D (delete), or T (truncate marker) - Row data — old and/or new row values serialized as JSONB - LSN — the current WAL log sequence number, used for frontier tracking - Transaction ID — links the change to its originating transaction

The trigger fires within your transaction, so if you roll back, the change record is also rolled back. This guarantees that only committed changes appear in the buffer.

What is the overhead of CDC triggers?

The per-row overhead is approximately 20–55 μs, which covers the PL/pgSQL function dispatch, row_to_json() serialization, and the buffer table INSERT.

At typical write rates (fewer than 1,000 writes per second per source table), this adds less than 5% additional DML latency. For most OLTP workloads, the overhead is negligible — a single network round-trip to the database is usually 10–100× more expensive.

If you have very high-throughput source tables (>10K writes/sec), consider enabling the hybrid CDC mode (pg_trickle.cdc_mode = 'auto') which can automatically transition to WAL-based capture for lower per-row overhead (~5–15 μs).

What happens when I TRUNCATE a source table?

TRUNCATE is captured via a statement-level AFTER TRUNCATE trigger that writes a T marker row to the change buffer. When the differential refresh engine detects this marker, it automatically falls back to a full refresh for that cycle, ensuring the stream table stays consistent. Both FULL and DIFFERENTIAL mode stream tables handle TRUNCATE correctly.

Are CDC triggers automatically cleaned up?

Yes. pg_trickle tracks which source tables are referenced by which stream tables in the pgt_dependencies catalog. When the last stream table referencing a particular source table is dropped, pg_trickle automatically:

  1. Removes the CDC triggers from the source table.
  2. Drops the associated change buffer table (pgtrickle_changes.changes_<oid>).

You do not need to manually clean up triggers or buffer tables.

What happens if a source table is dropped or altered?

pg_trickle has DDL event triggers that listen for ALTER TABLE and DROP TABLE on source tables. When a change is detected, pg_trickle responds automatically:

  1. All stream tables that depend on the altered source are marked with needs_reinit = true in the catalog.
  2. On the next scheduler cycle, each affected stream table is reinitialized — the existing storage table is dropped, recreated from the current defining query schema, and re-populated with a full refresh.
  3. A reinitialize_needed NOTIFY alert is sent so your monitoring can detect the event.

If the DDL change breaks the defining query (e.g., a column referenced in the query was dropped), the reinitialization will fail and the stream table will enter ERROR status. In that case, you need to drop and recreate the stream table with an updated query.

How do I check if a source table has switched from trigger-based CDC to WAL-based CDC?

When you enable hybrid CDC (pg_trickle.cdc_mode = 'auto'), pg_trickle starts capturing changes with triggers and can automatically transition to WAL-based logical replication once conditions are met. There are several ways to check the current CDC mode for each source table:

1. Query the dependency catalog directly:

SELECT d.source_relid, c.relname AS source_table, d.cdc_mode,
       d.slot_name, d.decoder_confirmed_lsn, d.transition_started_at
FROM pgtrickle.pgt_dependencies d
JOIN pg_class c ON c.oid = d.source_relid;

The cdc_mode column shows one of three values: - TRIGGER — changes are captured via row-level triggers (the default) - TRANSITIONING — the system is in the process of switching from triggers to WAL - WAL — changes are captured via logical replication

2. Use the built-in health check function:

SELECT source_table, cdc_mode, slot_name, lag_bytes, alert
FROM pgtrickle.check_cdc_health();

This returns a row per source table with the current mode, replication slot lag (for WAL-mode sources), and any alert conditions such as slot_lag_exceeds_threshold or replication_slot_missing.

3. Listen for real-time transition notifications:

LISTEN pg_trickle_cdc_transition;

pg_trickle sends a NOTIFY with a JSON payload whenever a transition starts, completes, or is rolled back. Example payload:

{
  "event": "transition_complete",
  "source_table": "public.orders",
  "old_mode": "TRANSITIONING",
  "new_mode": "WAL",
  "slot_name": "pg_trickle_slot_16384"
}

This lets you integrate CDC mode changes into your monitoring stack without polling.

4. Check the global GUC setting:

SHOW pg_trickle.cdc_mode;

This shows the desired global behavior (trigger, auto, or wal), not the per-table actual state. The per-table state lives in pgt_dependencies.cdc_mode as described above.

See CONFIGURATION.md for details on the pg_trickle.cdc_mode, pg_trickle.wal_transition_timeout, pg_trickle.slot_lag_warning_threshold_mb, and pg_trickle.slot_lag_critical_threshold_mb GUCs.

Is it safe to add triggers to a stream table while the source table is switching CDC modes?

Yes, this is completely safe. CDC mode transitions and user-defined triggers operate on different tables and do not interfere with each other:

  • CDC transitions affect how changes are captured from source tables (e.g., orders). The transition switches the capture mechanism from row-level triggers on the source table to WAL-based logical replication.
  • User-defined triggers live on stream tables (e.g., order_totals) and control how the refresh engine applies changes to the materialized output.

Because these are independent concerns, you can freely add, modify, or remove triggers on a stream table at any point — including during an active CDC transition on its source tables.

How it works in practice:

  1. The refresh engine checks for user-defined triggers on the stream table at the start of each refresh cycle (via a fast pg_trigger lookup, <0.1 ms).
  2. If user triggers are detected, the engine uses explicit DELETE / UPDATE / INSERT statements instead of MERGE, so your triggers fire with correct TG_OP, OLD, and NEW values.
  3. The change data consumed by the refresh engine has the same format regardless of whether it came from CDC triggers or WAL decoding — so the trigger detection and the CDC mode are fully decoupled.

A trigger added between two refresh cycles will simply be picked up on the next cycle. The only (theoretical) edge case is adding a trigger in the tiny window during a single refresh transaction, between the trigger-detection check and the MERGE execution — but since both happen within the same transaction, this is virtually impossible in practice.

Why does pg_trickle use triggers instead of logical replication for initial CDC?

pg_trickle always bootstraps CDC with row-level AFTER triggers because they provide single-transaction atomicity — the change record is written in the same transaction as the source DML, so:

  1. No commit-order ambiguity. The change buffer always reflects committed data; rolled-back transactions never produce partial change records.
  2. No replication slot management at creation time. Logical replication requires creating and monitoring replication slots, which can bloat WAL if the subscriber falls behind. Trigger-based bootstrap avoids this complexity.
  3. Works on all hosting providers. Some managed PostgreSQL services restrict wal_level = logical or limit the number of replication slots. Trigger bootstrap works everywhere, with no configuration changes.
  4. Simpler initial deployment. No need for wal_level = logical, no publication/subscription setup, and no extra connections for WAL senders.

With pg_trickle.cdc_mode = 'auto' (the default since v0.3.0), pg_trickle uses triggers initially and then transparently transitions to WAL-based CDC if wal_level = logical is available. If WAL is not available, triggers are kept permanently — no degradation, no errors. Set pg_trickle.cdc_mode = 'trigger' if you want to disable WAL transitions entirely. See ADR-001 and ADR-002 in the architecture documentation for the full rationale.

Why is auto the default pg_trickle.cdc_mode?

As of v0.3.0, auto is the default CDC mode. This was changed from trigger based on the following considerations:

1. Safe no-op on standard installs. PostgreSQL ships with wal_level = replica by default. In this configuration, auto simply stays on trigger-based CDC permanently — it does not create replication slots, publications, or any WAL infrastructure. There is no error, warning, or user-visible difference from the old trigger default. auto only activates the WAL transition path when wal_level = logical is explicitly configured by the operator.

2. Automatic fallback hardening. The WAL transition and steady-state polling now include robust automatic fallback: - Consecutive poll errors (5 failures) trigger automatic revert to triggers. - check_decoder_health() validates slot existence, WAL lag, and wal_level on every tick. - The TRANSITIONING phase has a progressive timeout with informative warnings. - Post-restart health checks (check_cdc_transition_health()) automatically clean up stale transitions.

3. Zero overhead for trigger-only deployments. When wal_level != logical, the auto scheduler branch takes a fast-path exit after a single GUC check and pg_replication_slots query. The overhead compared to trigger mode is negligible (<1 ms per scheduler tick).

4. Progressive optimisation without config changes. When an operator later enables wal_level = logical (e.g., for other replication needs), pg_trickle automatically benefits from lower per-row CDC overhead (~5–15 μs vs ~20–55 μs) without any configuration change. This aligns with the principle of least surprise.

When to use trigger instead: Set pg_trickle.cdc_mode = 'trigger' if you want fully deterministic trigger-only behaviour, need to minimize any replication slot management, or are on a restricted managed PostgreSQL that caps replication slots. This reverts to the pre-v0.3.0 default.

Caveats to be aware of in auto mode: - Keyless tables (no PRIMARY KEY) stay on triggers permanently — WAL mode requires a PK for pk_hash computation. - Replication slots prevent WAL recycling: if the decoder falls behind, WAL accumulates. pg_trickle now warns at pg_trickle.slot_lag_warning_threshold_mb (default 100 MB) and marks per-source CDC health unhealthy at pg_trickle.slot_lag_critical_threshold_mb (default 1024 MB). - The TRANSITIONING phase runs both trigger and WAL decoder simultaneously; LSN-based deduplication handles correctness. If anything goes wrong, the system rolls back to triggers.

How does the trigger-to-WAL automatic transition work?

When pg_trickle.cdc_mode = 'auto', pg_trickle monitors each source table’s write rate. When the rate exceeds an internal threshold, the transition proceeds in three phases:

  1. Slot creation. A logical replication slot is created for the source table’s OID (e.g., pg_trickle_slot_16384).
  2. Dual capture. For a brief period, both triggers and WAL decoding capture changes. The system uses LSN comparison to deduplicate, ensuring no changes are lost or double-counted.
  3. Trigger removal. Once the WAL decoder has confirmed it is caught up (its confirmed LSN ≥ the frontier LSN), the row-level triggers are dropped and the source transitions fully to WAL mode.

The transition is tracked in pgt_dependencies.cdc_mode (values: TRIGGER → TRANSITIONING → WAL). If the transition times out (pg_trickle.wal_transition_timeout, default 5 minutes), it is rolled back and triggers are kept.

What happens to CDC if I restore a database backup?

After restoring a backup (pg_dump, pg_basebackup, or PITR), the CDC state depends on the backup type:

Backup type Triggers Change buffers Frontier Action needed
pg_dump (logical) Preserved (in DDL) Buffer rows included Catalog restored Usually none — next refresh detects stale frontier and does a full refresh
pg_basebackup (physical) Preserved Buffer rows preserved (committed at backup time) Catalog restored Replication slots may be invalid — WAL-mode sources may need manual transition back to TRIGGER mode
PITR (point-in-time) Preserved Only committed buffer rows at the recovery target Catalog restored Similar to pg_basebackup; frontier may point ahead of actual buffer content → first refresh does a full refresh to reconcile

In all cases, the pg_trickle scheduler automatically detects frontier inconsistencies and falls back to a full refresh for the first cycle after restore. No manual intervention is required for trigger-mode sources.

For full guidelines on disaster recovery strategies, see our dedicated Backup and Restore chapter.

For WAL-mode sources, replication slots created after the backup point will not exist in the restored state. Set pg_trickle.cdc_mode = 'trigger' temporarily, or let the auto transition recreate slots.

Do CDC triggers fire for rows inserted via logical replication (subscribers)?

Yes. PostgreSQL fires row-level triggers on the subscriber side for rows applied via logical replication. This means if you have a subscriber database with pg_trickle installed, the CDC triggers will capture replicated changes into the local change buffers.

Implication: You can run stream tables on a subscriber database that tracks replicated tables — the change capture works transparently. However, be careful about:

  • Double-counting. If the same table is tracked by pg_trickle on both the publisher and subscriber, changes are captured twice (once on each side). This is fine if the stream tables are independent, but confusing if you expect them to be identical.
  • Replication lag. The stream table on the subscriber will be delayed by both the replication lag and the pg_trickle refresh schedule.

Can I inspect the change buffer tables directly?

Yes. Change buffers are ordinary tables in the pgtrickle_changes schema, named changes_<source_oid>:

-- List all change buffer tables
SELECT tablename FROM pg_tables WHERE schemaname = 'pgtrickle_changes';

-- Inspect recent changes for a source table (find OID first)
SELECT c.oid FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'orders' AND n.nspname = 'public';

-- Then query the buffer
SELECT action, lsn, txid, old_data, new_data
FROM pgtrickle_changes.changes_16384
ORDER BY lsn DESC LIMIT 10;

The action column contains: I (insert), U (update), D (delete), or T (truncate).

Warning: Do not modify buffer tables directly. The refresh engine manages buffer cleanup (truncation) after each successful refresh. Manual changes will corrupt the frontier tracking.

How does pg_trickle prevent its own refresh writes from re-triggering CDC?

When the refresh engine writes to a stream table (via MERGE or explicit DML), it does not trigger CDC capture on that stream table, even if the stream table is itself a source for a downstream stream table. This is because:

  1. CDC triggers are only installed on source tables, not on stream tables. The refresh engine writes directly to the stream table’s storage without going through any change-capture mechanism.
  2. Downstream change propagation uses a different path. When stream table A is a source for stream table B, changes to A are detected at B’s refresh time by re-reading A’s data (not via triggers on A). The topological ordering ensures A is refreshed before B.

This design prevents infinite loops (A triggers B triggers A) and avoids the overhead of capturing changes to materialized output that will be recomputed anyway.

Diamond Dependencies & DAG Scheduling

When multiple stream tables form a diamond-shaped dependency graph, careful coordination is needed to avoid inconsistent snapshots. This section covers atomic consistency, schedule policies, and topological ordering.

What is a diamond dependency and why does it matter?

A diamond dependency occurs when two (or more) intermediate stream tables both depend on the same source, and a downstream stream table depends on both of them:

       Source: orders
       /             \
  ST: totals      ST: counts
       \             /
    ST: combined_report

Without coordination, combined_report might be refreshed after totals is updated but before counts is updated (or vice versa), producing a temporarily inconsistent snapshot — totals reflects the latest data but counts is stale.

What does diamond_consistency = 'atomic' do?

When diamond_consistency = 'atomic' is set on the downstream stream table (e.g., combined_report), pg_trickle ensures that all upstream stream tables in the diamond are refreshed within the same scheduler cycle before the downstream table is refreshed. This guarantees a consistent point-in-time snapshot.

If any upstream refresh in the atomic group fails, the downstream refresh is skipped for that cycle to avoid inconsistency. The failed upstream will be retried on the next cycle.

SELECT pgtrickle.alter_stream_table('combined_report',
    diamond_consistency => 'atomic');

What is the difference between 'fastest' and 'slowest' schedule policy?

When a stream table has multiple upstream dependencies with different schedules, pg_trickle needs a policy for when to refresh the downstream table:

Policy Behavior Best for
fastest Refresh downstream whenever any upstream refreshes Low-latency dashboards where partial freshness is acceptable
slowest Refresh downstream only after all upstreams have refreshed Reports requiring all-or-nothing consistency

The default is fastest. Use slowest with diamond_consistency = 'atomic' for the strongest consistency guarantees.

What happens when an atomic diamond group partially fails?

When diamond_consistency = 'atomic' is set and one upstream stream table in the diamond fails to refresh:

  1. The downstream refresh is skipped for that cycle (it reads stale-but-consistent data from the previous successful cycle).
  2. The failed upstream follows the normal retry logic (exponential backoff, up to max_consecutive_errors).
  3. Other non-failing upstreams in the diamond are still refreshed normally — their data is fresh, but the downstream won’t consume it until all upstreams succeed.
  4. A NOTIFY pg_trickle_alert with event diamond_partial_failure is sent so your monitoring can detect the situation.

How does pg_trickle determine topological refresh order?

The scheduler builds a directed acyclic graph (DAG) of stream table dependencies at startup and after any create_stream_table / drop_stream_table call. The algorithm:

  1. Edge discovery. For each stream table, the defining query’s source tables are extracted. If a source table is itself a stream table, a dependency edge is added.
  2. Cycle detection. The DAG is checked for cycles. If a cycle is detected, the offending create_stream_table call is rejected with a clear error message listing the cycle path.
  3. Topological sort. A Kahn’s algorithm topological sort produces the refresh order — leaf nodes (no stream table dependencies) are refreshed first, then their dependents, and so on.
  4. Level assignment. Each stream table is assigned a “level” (0 for leaves, max(parent levels) + 1 for dependents). Stream tables at the same level are refreshed concurrently when pg_trickle.parallel_refresh_mode = 'on'.

The topological order is recalculated whenever the DAG changes. You can inspect it with:

SELECT pgt_name, depends_on, topo_level
FROM pgtrickle.stream_tables_info
ORDER BY topo_level, pgt_name;

Schema Changes & DDL Events

pg_trickle detects source table schema changes via PostgreSQL’s DDL event trigger system and reacts automatically. This section explains what happens for various DDL operations and how to handle them.

What happens when I add a column to a source table?

Adding a column to a source table is safe and non-disruptive if the stream table’s defining query does not use SELECT *:

  • Named columns: If the defining query explicitly lists columns (e.g., SELECT id, name, amount FROM orders), the new column is simply not captured by CDC and has no effect on the stream table.
  • SELECT *: If the defining query uses SELECT *, pg_trickle detects the schema mismatch at the next refresh and marks the stream table with needs_reinit = true. The next scheduler cycle performs a full reinitialization — drops the storage table, recreates it with the new column set, and does a full refresh.

CDC triggers capture the full row as JSONB regardless of which columns the stream table uses, so no trigger changes are needed.

What happens when I drop a column used in a stream table’s query?

Dropping a column that is referenced in a stream table’s defining query will cause the next refresh to fail because the column no longer exists in the source table. pg_trickle handles this via:

  1. DDL event trigger detects the ALTER TABLE ... DROP COLUMN and marks all affected stream tables with needs_reinit = true.
  2. On the next refresh cycle, the scheduler attempts reinitialization — but the defining query will fail with a PostgreSQL error (e.g., column "amount" does not exist).
  3. The stream table moves to ERROR status after max_consecutive_errors failures.
  4. A reinitialize_needed NOTIFY alert is sent.

Resolution: Drop and recreate the stream table with an updated defining query:

SELECT pgtrickle.drop_stream_table('order_totals');
SELECT pgtrickle.create_stream_table(
    name         => 'order_totals',
    query        => 'SELECT id, name FROM orders',  -- updated query without dropped column
    schedule     => '1m',
    refresh_mode => 'DIFFERENTIAL'
);

What happens when I CREATE OR REPLACE a view used by a stream table?

PostgreSQL event triggers fire on CREATE OR REPLACE VIEW, so pg_trickle detects the change and marks dependent stream tables with needs_reinit = true. On the next refresh:

  • If the new view definition is compatible (same output columns, same types), reinitialization succeeds transparently — the stream table is repopulated with the new query logic.
  • If the new view definition changes the output schema (different columns or types), the delta query will fail and the stream table enters ERROR status.

Tip: To avoid disruption, use pgtrickle.alter_stream_table() to pause the stream table before replacing the view, then resume after verifying compatibility.

What happens when I alter or drop a function used in a stream table’s query?

If a stream table’s defining query calls a user-defined function (e.g., SELECT my_func(amount) FROM orders) and that function is altered or dropped:

  • ALTER FUNCTION (changing the body): pg_trickle does not detect this automatically — PostgreSQL does not fire DDL event triggers for function body changes. The stream table continues refreshing with the new function behavior. If this is intentional, no action is needed. If you want a full rebase to the new logic, run a manual full refresh: sql SELECT pgtrickle.refresh_stream_table('my_st', force_full => true);
  • DROP FUNCTION: The next refresh fails because the function no longer exists. The stream table enters ERROR status. Recreate the function or drop and recreate the stream table.

What is reinitialize and when does it trigger?

Reinitialize is pg_trickle’s mechanism for handling structural changes to source tables. When a stream table is marked with needs_reinit = true, the next scheduler cycle performs:

  1. Drop the existing storage table (the physical heap table backing the stream table).
  2. Recreate the storage table from the defining query’s current output schema.
  3. Full refresh — run the defining query against current source data and populate the new storage table.
  4. Reset the frontier to the current LSN.
  5. Clear the needs_reinit flag.

Reinitialize triggers automatically when: - DDL event triggers detect ALTER TABLE, DROP TABLE, or CREATE OR REPLACE VIEW on source tables or intermediate views. - A needs_reinit NOTIFY alert is sent. - You can also trigger it manually: sql UPDATE pgtrickle.pgt_stream_tables SET needs_reinit = true WHERE pgt_name = 'my_st';

Can I block DDL on tracked source tables?

pg_trickle does not currently block DDL on source tables — it only reacts to DDL changes via event triggers. If you want to prevent accidental schema changes on critical source tables, use PostgreSQL’s built-in mechanisms:

-- Revoke ALTER/DROP from application roles
REVOKE ALL ON TABLE orders FROM app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO app_user;
-- Only the table owner (or superuser) can now ALTER/DROP

Alternatively, create a custom event trigger that raises an exception when DDL targets tracked source tables:

CREATE OR REPLACE FUNCTION prevent_source_ddl() RETURNS event_trigger AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM pg_event_trigger_ddl_commands() cmd
        JOIN pgtrickle.pgt_dependencies d ON d.source_relid = cmd.objid
    ) THEN
        RAISE EXCEPTION 'Cannot ALTER/DROP a table tracked by pg_trickle';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER guard_source_ddl ON ddl_command_end
EXECUTE FUNCTION prevent_source_ddl();

What happens if I run DDL on a source table during an active refresh? {#ec-17}

PostgreSQL’s locking mechanism prevents most conflicts. The refresh transaction acquires a ShareLock on source tables before reading them. Since ALTER TABLE (including ADD COLUMN, DROP COLUMN, ALTER TYPE) requires an AccessExclusiveLock, the DDL statement blocks until the refresh transaction completes.

In practice: - During a refresh: The ALTER TABLE waits for the refresh to finish, then proceeds. pg_trickle’s DDL event trigger then detects the change and marks the stream table for reinitialization. - Between refreshes: DDL proceeds immediately. The next refresh picks up the reinitialization flag.

There is a tiny theoretical window between lock acquisition and the first read where DDL could sneak in, but this is prevented by PostgreSQL’s MVCC — the refresh’s snapshot was taken before the DDL committed, so it reads the old schema regardless.

If pg_trickle.block_source_ddl = true: Column-affecting DDL on tracked source tables is rejected entirely with an ERROR, regardless of whether a refresh is running.

Do stream tables work with logical replication? {#ec-21-22-23}

Stream tables are replicated to standbys via physical (streaming) replication like any other heap table. However, they are not automatically maintained by pg_trickle on the subscriber:

Aspect Primary Physical standby Logical subscriber
Scheduler runs Yes No (read-only) No (no pg_trickle catalog)
Stream tables readable Yes Yes (replicated) Only if published
Refreshes occur Yes No (standby is read-only) No
Change buffers Managed by pg_trickle Replicated but not consumed Not available

Key limitations: - Change buffer tables (pgtrickle_changes.*) are not published through logical replication — they are internal transient data. - The pg_trickle catalog (pgtrickle.pgt_stream_tables) is not replicated through logical replication. - On a physical standby, stream tables receive updates through streaming replication with the usual replication lag.

Recommended pattern: Run pg_trickle on the primary only. Read stream tables from any physical standby.

Performance & Tuning

This section covers scheduler tuning, the adaptive FULL fallback, disk space management, and guidance on when to use DIFFERENTIAL vs. FULL mode.

How do I tune the scheduler interval?

The pg_trickle.scheduler_interval_ms GUC controls how often the scheduler checks for stale stream tables (default: 1000 ms).

Workload Recommended Value
Low-latency (near real-time) 100–500
Standard 1000 (default)
Low-overhead (many STs, long schedules) 5000–10000

Is there any risk in setting min_schedule_seconds very low?

Yes. pg_trickle.min_schedule_seconds (default: 60) is a safety guardrail, not an arbitrary limit. Setting it very low — especially in production — can cause several problems:

WAL amplification. Every differential refresh writes a MERGE to the WAL. At 1-second intervals across many stream tables, WAL generation rises sharply, increasing replication lag and storage costs.

Lock contention. Each refresh acquires locks on the change buffer table. With cleanup_use_truncate = true (the default), this is an AccessExclusiveLock. Sub-second schedules can starve concurrent INSERT/UPDATE/DELETE statements on the source tables.

Cascading refresh load. If a refresh takes longer than the schedule interval (e.g., an 800 ms refresh on a 1-second schedule), the next refresh fires almost immediately upon completion. With chained or diamond-shaped ST graphs, the entire topological chain must complete within the interval to avoid falling behind.

Autovacuum pressure. Rapid MERGE operations produce dead tuples in the stream table faster than autovacuum can clean them up, bloating the table and degrading query performance over time.

Adaptive fallback triggering. At high change rates, pg_trickle.differential_max_change_ratio may trigger a FULL refresh instead of DIFFERENTIAL. A FULL refresh at 1-second intervals is very expensive and defeats the purpose of differential maintenance.

Practical guidance:

Environment Recommended minimum
Development / testing 1 s — fine for fast iteration
Lightly loaded production 10–30 s
Standard production 60 s (default)
High-throughput OLTP 120+ s — let change buffers accumulate for efficient batch merging

If you need near-real-time results, consider IMMEDIATE mode (refresh_mode => 'DIFFERENTIAL' with same-transaction refresh) instead of a very short schedule — it avoids the scheduler overhead entirely and updates the stream table within your transaction.

What is the adaptive fallback to FULL?

When the number of pending changes exceeds pg_trickle.differential_max_change_ratio (default: 15%) of the source table size, DIFFERENTIAL mode automatically falls back to FULL for that refresh cycle. This prevents pathological delta queries on bulk changes.

  • Set to 0.0 to always use DIFFERENTIAL (even on large change sets)
  • Set to 1.0 to effectively always use FULL
  • Default 0.15 (15%) is a good balance

How many concurrent refreshes can run?

By default (parallel_refresh_mode = 'off') refreshes are processed sequentially within the scheduler’s single background worker. This is safe and efficient for most deployments.

Starting in v0.4.0, true parallel refresh is available via:

ALTER SYSTEM SET pg_trickle.parallel_refresh_mode = 'on';
ALTER SYSTEM SET pg_trickle.max_dynamic_refresh_workers = 4;  -- cluster-wide cap
ALTER SYSTEM SET pg_trickle.max_concurrent_refreshes = 4;    -- per-database cap
SELECT pg_reload_conf();

When enabled, independent stream tables at the same DAG level are refreshed concurrently in separate dynamic background workers. Each worker uses one max_worker_processes slot — see the worker-budget formula before enabling.

Monitor parallel refresh with:

SELECT * FROM pgtrickle.worker_pool_status();
SELECT * FROM pgtrickle.parallel_job_status(60);

For most deployments with fewer than 100 stream tables, sequential processing is still efficient (each differential refresh typically takes 5–50 ms).

How do I check if my stream tables are keeping up?

-- Quick overview
SELECT pgt_name, status, staleness, stale
FROM pgtrickle.stream_tables_info;

-- Detailed statistics
SELECT pgt_name, total_refreshes, avg_duration_ms, consecutive_errors, stale
FROM pgtrickle.pg_stat_stream_tables;

-- Recent refresh history for a specific ST
SELECT * FROM pgtrickle.get_refresh_history('order_totals', 10);

What is __pgt_row_id?

Every stream table has a __pgt_row_id BIGINT PRIMARY KEY column that stores a 64-bit xxHash of the row’s identity key. The refresh engine uses it to match incoming deltas against existing rows during MERGE operations.

For a detailed explanation of how this column is computed and why it exists, see What is the __pgt_row_id column and why does it appear in my stream tables? in the General section.

You should ignore this column in your queries. It is an implementation detail.

How much disk space do change buffer tables consume?

Each change buffer table stores one row per source-table change (INSERT, UPDATE, DELETE, or TRUNCATE marker). The row size depends on the source table’s column count and data types:

Component Approximate size
action column (char) 1 byte
old_data / new_data (JSONB) 1–10 KB per row (depends on source columns)
lsn (pg_lsn) 8 bytes
txid (xid8) 8 bytes
Index (on lsn) ~40 bytes per row

Rule of thumb: Buffer tables consume roughly 2–3× the raw row size of the source change, because both OLD and NEW values are stored as JSONB.

Buffer tables are cleaned up (truncated or deleted) after each successful refresh. If you suspect buffer bloat, check:

SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'pgtrickle_changes')
ORDER BY pg_total_relation_size(oid) DESC;

What determines whether DIFFERENTIAL or FULL is faster for a given workload?

The breakeven point depends on the change ratio — the number of changed rows relative to the total source table size:

Change ratio Recommended mode Why
< 5% DIFFERENTIAL Delta query touches few rows; much cheaper than re-reading everything
5–15% DIFFERENTIAL (usually) Still faster, but approaching the crossover
15–50% FULL The delta query scans a large fraction of the source anyway; FULL avoids the overhead of delta computation
> 50% FULL Bulk load scenario — TRUNCATE + INSERT is simpler and faster

Additional factors: - Query complexity: Queries with many joins or window functions have more expensive delta computation. The crossover shifts lower. - Source table size: For small tables (<10K rows), FULL is nearly always faster because the overhead is negligible. - Index presence: DIFFERENTIAL uses indexes to look up changed rows. Missing indexes on join keys or GROUP BY columns can make delta queries slow.

The adaptive fallback (pg_trickle.differential_max_change_ratio, default 0.15) automates this decision per-cycle.

What are the planner hints and when should I disable them?

Before executing a delta query, pg_trickle sets several session-level planner parameters to guide PostgreSQL toward efficient delta plans:

SET LOCAL enable_seqscan = off;     -- Prefer index scans for small deltas
SET LOCAL enable_nestloop = on;     -- Nested loops are good for small delta × large table joins
SET LOCAL enable_mergejoin = off;   -- Merge joins are worse for skewed delta sizes

These hints are active only during the refresh transaction and are reset afterward.

When to disable hints: If you notice that a particular stream table’s refresh is slow (check avg_duration_ms in pg_stat_stream_tables), the planner hints may be suboptimal for that specific query. You can disable them by setting:

SET pg_trickle.planner_hints = off;

This allows PostgreSQL’s planner to choose its own strategy. Test both settings and compare avg_duration_ms.

How do prepared statements help refresh performance?

The refresh engine uses PostgreSQL prepared statements (PREPARE / EXECUTE) for the delta and MERGE queries. On the first refresh, the statement is prepared; subsequent refreshes reuse the cached plan. Benefits:

  • Reduced planning overhead. For complex delta queries with many joins and CTEs, planning can take 5–50 ms. Prepared statements skip this on subsequent refreshes.
  • Stable plans. The planner uses generic plans after the 5th execution (PostgreSQL default), avoiding plan instability from statistic fluctuations.

Prepared statements are stored per-session and are invalidated when: - The stream table is reinitialized (schema change) - The shared cache generation advances after DDL or stream-table metadata changes - The PostgreSQL connection is recycled - The session ends

How does the adaptive FULL fallback threshold work in practice?

The pg_trickle.differential_max_change_ratio GUC (default: 0.15) is evaluated per source table, per refresh cycle:

  1. Before each differential refresh, the engine counts pending changes in the buffer table: pending_changes = COUNT(*) FROM pgtrickle_changes.changes_<oid>.
  2. It estimates the source table size from pg_class.reltuples.
  3. If pending_changes / reltuples > differential_max_change_ratio, the engine falls back to FULL for that cycle.

Edge cases: - If the source table has reltuples = 0 (freshly created, no ANALYZE yet), the engine always uses FULL until statistics are available. - For multi-source stream tables (joins), each source is evaluated independently. If any source exceeds the threshold, the entire refresh falls back to FULL. - The threshold applies to the current cycle only — the next cycle re-evaluates.

How many stream tables can a single PostgreSQL instance handle?

There is no hard limit. Practical limits depend on:

Factor Guideline
Scheduler overhead Each cycle iterates all STs; at 1000 STs with 1ms overhead per check, the cycle takes ~1s
Background connections 1 per database (the scheduler) + 1 per manual refresh call
Change buffer bloat Each source table gets its own buffer table — many sources = many tables in pgtrickle_changes
Catalog size pgt_stream_tables and pgt_dependencies grow linearly
Refresh throughput Sequential processing means total cycle time = sum of individual refresh times

Tested benchmarks: Up to 500 stream tables on a single instance with <2s total cycle time for DIFFERENTIAL refreshes averaging 3ms each.

What is the TRUNCATE vs DELETE cleanup trade-off for change buffers?

After each successful refresh, the engine cleans up processed change records from the buffer table. The pg_trickle.cleanup_use_truncate GUC (default: true) controls the method:

Method Pros Cons
TRUNCATE (default) Instant — O(1) regardless of row count. Reclaims disk space immediately. Takes an ACCESS EXCLUSIVE lock on the buffer table, briefly blocking concurrent INSERTs from CDC triggers (~0.1 ms typical).
DELETE Row-level lock only — no blocking of concurrent CDC writes. O(N) — proportional to the number of processed rows. Dead tuples require VACUUM to reclaim space.

When to switch to DELETE: If your source table has extremely high write throughput (>10K writes/sec) and you observe brief stalls in DML latency during refresh cleanup, switch to DELETE:

ALTER SYSTEM SET pg_trickle.cleanup_use_truncate = false;
SELECT pg_reload_conf();

For most workloads, TRUNCATE is the better choice because buffer tables are typically emptied completely after each refresh.

Interoperability

Stream tables are standard PostgreSQL heap tables, which means they work with most PostgreSQL features. This section clarifies what’s compatible (views, replication, triggers) and what’s not (direct DML, foreign keys).

Can PostgreSQL views reference stream tables?

Yes. Since stream tables are standard PostgreSQL heap tables, you can create views on top of them just like any other table. The view will return whatever data is currently in the stream table, reflecting the most recent refresh:

CREATE VIEW high_value_customers AS
SELECT customer_id, total FROM pgtrickle.order_totals WHERE total > 1000;

This is a common pattern for adding per-user filters or formatting on top of a shared stream table.

Can materialized views reference stream tables?

Yes, though this is usually redundant — both materialized views and stream tables are physical snapshots of query results. The key difference is that the materialized view requires its own manual REFRESH MATERIALIZED VIEW call; it does not auto-refresh when the underlying stream table refreshes.

A more idiomatic approach is to create a second stream table that references the first one. This way, pg_trickle handles the dependency ordering and refresh scheduling for both automatically.

Can I replicate stream tables with logical replication?

Yes. Stream tables can be published like any ordinary table:

CREATE PUBLICATION my_pub FOR TABLE pgtrickle.order_totals;

Important caveats: - The __pgt_row_id column is replicated (it is the primary key) - Subscribers receive materialized data, not the defining query - Do not install pg_trickle on the subscriber and attempt to refresh the replicated table — it will have no CDC triggers or catalog entries - Internal change buffer tables are not published by default

Can I INSERT, UPDATE, or DELETE rows in a stream table directly?

No. Stream table contents are managed exclusively by the refresh engine, and direct DML will corrupt the internal state (row IDs, frontier tracking, and change buffer consistency). See Why can’t I INSERT, UPDATE, or DELETE rows in a stream table? for a detailed explanation of what goes wrong.

If you need to post-process stream table data, create a view or a second stream table that references the first one.

Can I add foreign keys to or from stream tables?

No. Foreign key constraints are incompatible with how the refresh engine operates. The engine uses bulk MERGE operations that apply inserts and deletes atomically, without guaranteeing the row-by-row ordering that foreign key checks require. Full refreshes also use TRUNCATE + INSERT, which bypasses cascade logic entirely.

See Why can’t I add foreign keys? for details. If you need referential integrity, enforce it in your application or in a view that joins the stream tables.

Can I add my own triggers to stream tables?

Yes, for DIFFERENTIAL mode stream tables. When user-defined row-level triggers are detected, the refresh engine automatically switches from MERGE to explicit DELETE + UPDATE + INSERT statements. This ensures triggers fire with the correct TG_OP, OLD, and NEW values. Legacy configs that still set pg_trickle.user_triggers = 'on' are treated the same as auto.

Limitations: - Row-level triggers do not fire during FULL refresh (they are automatically suppressed via DISABLE TRIGGER USER). Use REFRESH MODE DIFFERENTIAL for stream tables with triggers. - The IS DISTINCT FROM guard prevents no-op UPDATE triggers when the aggregate result is unchanged. - BEFORE triggers that modify NEW will affect the stored value — the next refresh may “correct” it back, causing oscillation.

See the pg_trickle.user_triggers GUC in CONFIGURATION.md for control options.

Can I ALTER TABLE a stream table directly?

No. Direct ALTER TABLE would change the physical table without updating pg_trickle’s catalog, causing column mismatches and __pgt_row_id invalidation on the next refresh. See Why can’t I ALTER TABLE a stream table directly? for details.

Instead, use the pg_trickle API:

-- Change schedule, mode, or status:
SELECT pgtrickle.alter_stream_table('order_totals', schedule => '10m');

-- To change the defining query or column structure, drop and recreate:
SELECT pgtrickle.drop_stream_table('order_totals');
SELECT pgtrickle.create_stream_table(
    name         => 'order_totals',
    query        => '...',
    schedule     => '5m',
    refresh_mode => 'DIFFERENTIAL'
);

Does pg_trickle work with PgBouncer or other connection poolers?

It depends on the pooling mode. pg_trickle’s background scheduler uses session-level features that are incompatible with transaction-mode connection pooling:

Feature Issue with Transaction-Mode Pooling
pg_advisory_lock() Session-level lock released when connection returns to pool — concurrent refreshes possible
PREPARE / EXECUTE Prepared statements are session-scoped — “does not exist” errors on different connections
LISTEN / NOTIFY Notifications lost when listeners change connections

Recommended configurations:

  • Session-mode pooling (pool_mode = session): Fully compatible. The scheduler holds a dedicated connection.
  • Direct connection (no pooler for the scheduler): Fully compatible. Application queries can still go through a pooler.
  • Transaction-mode pooling (pool_mode = transaction): Not supported. The scheduler requires a persistent session.

Tip: If your infrastructure requires transaction-mode pooling (e.g., AWS RDS Proxy, Supabase), route the pg_trickle background worker through a direct connection while keeping application traffic on the pooler. Most connection poolers support per-database or per-user routing rules.

Does pg_trickle work with pgvector?

Partially — it depends on the refresh mode and what the defining query does.

What works:

  • Source tables with vector columns. CDC triggers are generated using PostgreSQL’s format_type(), which returns the full type name (e.g. vector(1536)). Change buffer tables mirror the source schema correctly, so inserts, updates, and deletes on pgvector tables are captured and replayed without issue.
  • Passing vector columns through in DIFFERENTIAL mode. Stream tables that select, filter (on non-vector columns), or join sources that happen to contain vector columns work correctly — the vector data is treated as an opaque value and copied through unchanged.
  • FULL mode with any pgvector expression. Because FULL mode re-executes the entire defining query, all pgvector operators (<->, <=>, <#>) and functions (cosine_distance, l2_normalize, etc.) work exactly as they do in a regular query.

What does not work:

  • DIFFERENTIAL mode with pgvector distance operators in the query. The DVM engine needs a differentiation rule for every SQL operator it encounters. Custom operators like <-> (L2 distance) or <=> (cosine distance) are not in the built-in rule set. The engine will fall back automatically to FULL mode if such operators appear in the delta query path. Set refresh_mode => 'FULL' explicitly to make this intent clear.
  • Incremental aggregation over vector columns. There is no meaningful incremental form for aggregates over vector values (e.g. averaging embeddings). Use FULL mode for any aggregate that involves vector arithmetic.

Recommended pattern for a nearest-neighbour cache or semantic search result set:

CREATE EXTENSION IF NOT EXISTS vector;

SELECT pgtrickle.create_stream_table(
    name         => 'top_similar_docs',
    query        => $$
        SELECT d.id, d.title, d.embedding,
               d.embedding <=> '[0.1, 0.2, 0.3]'::vector AS distance
        FROM documents d
        ORDER BY distance
        LIMIT 100
    $$,
    schedule     => '5m',
    refresh_mode => 'FULL'
);

For use-cases that only carry vector columns through without computing on them, DIFFERENTIAL mode works fine:

-- Vectors are not used in the delta computation — DIFFERENTIAL is safe here
SELECT pgtrickle.create_stream_table(
    name         => 'active_doc_embeddings',
    query        => $$
        SELECT id, embedding
        FROM documents
        WHERE status = 'published'
    $$,
    schedule     => '1m',
    refresh_mode => 'DIFFERENTIAL'
);

dbt Integration

The dbt-pgtrickle package provides a stream_table materialization that lets you manage stream tables through dbt’s standard workflow. This section covers setup, commands, freshness checks, and query change handling.

How do I use pg_trickle with dbt?

Install the dbt-pgtrickle package (a pure Jinja SQL macro package — no Python dependencies):

# packages.yml
packages:
  - package: pg_trickle/dbt_pgtrickle
    version: ">=0.2.0"

Then define a stream table model using the stream_table materialization:

-- models/order_totals.sql
{{ config(
    materialized='stream_table',
    schedule='1m',
    refresh_mode='DIFFERENTIAL'
) }}

SELECT customer_id, SUM(amount) AS total
FROM {{ source('public', 'orders') }}
GROUP BY customer_id

The stream_table materialization calls pgtrickle.create_stream_table() on the first run and pgtrickle.alter_stream_table() on subsequent runs (if the schedule or mode changes).

What dbt commands work with stream tables?

Command Behavior
dbt run Creates stream tables that don’t exist; updates schedule/mode if changed; does not alter the defining query of existing STs
dbt run --full-refresh Drops and recreates all stream tables from scratch (new defining query, fresh data)
dbt test Works normally — tests query the stream table as a regular table
dbt source freshness Works if you configure a freshness block on the stream table source
dbt docs generate Documents stream tables like any other model

How does dbt run --full-refresh work with stream tables?

When --full-refresh is passed, the stream_table materialization:

  1. Calls pgtrickle.drop_stream_table('model_name') to remove the existing stream table, CDC triggers, and change buffers.
  2. Calls pgtrickle.create_stream_table(...) with the current defining query from the model file.
  3. The new stream table starts in INITIALIZING status and performs its first full refresh.

This is the correct way to update a stream table’s defining query in dbt. Without --full-refresh, dbt will not detect query changes (it only compares schedule and mode).

How do I check stream table freshness in dbt?

Use dbt’s built-in source freshness feature by adding a freshness block to your source definition:

# models/sources.yml
sources:
  - name: pgtrickle
    schema: pgtrickle
    tables:
      - name: order_totals
        loaded_at_field: "last_refreshed_at"  # from stream_tables_info
        freshness:
          warn_after: {count: 5, period: minute}
          error_after: {count: 15, period: minute}

Then run dbt source freshness to check.

Alternatively, query the pg_trickle monitoring views directly in a dbt test:

-- tests/check_freshness.sql
SELECT pgt_name FROM pgtrickle.stream_tables_info WHERE stale = true

What happens when the defining query changes in dbt?

If you modify the SQL in a stream table model file and run dbt run without --full-refresh:

  • The stream_table materialization detects that the stream table already exists.
  • It compares the schedule and refresh mode — if either changed, it calls alter_stream_table() to update them.
  • It does not compare the defining query text. The existing defining query remains in effect.

To apply a new defining query, you must run dbt run --full-refresh. This drops and recreates the stream table with the new query.

Recommendation: After changing a model’s SQL, always run dbt run --full-refresh -s model_name to apply the change.

Can I use dbt snapshot with stream tables?

Yes, with caveats. dbt snapshots work by tracking changes to a source table over time using updated_at or check strategies. You can snapshot a stream table like any other table.

However, keep in mind: - Stream tables are refreshed periodically, not on every write. The snapshot will only capture changes at refresh boundaries, not at the granularity of individual source-table writes. - The __pgt_row_id column will appear in the snapshot. You may want to exclude it with check_cols or a select in the snapshot configuration. - FULL refresh mode replaces all rows each cycle, which will appear as “updates” to the snapshot strategy even if the data hasn’t changed. Use DIFFERENTIAL mode for stream tables that are snapshotted.

What dbt versions are supported?

dbt-pgtrickle is a pure Jinja SQL macro package that works with:

  • dbt-core 1.7+ (the stream_table materialization uses standard Jinja patterns)
  • dbt-postgres adapter (required for PostgreSQL connection)

There are no Python dependencies beyond dbt-core and dbt-postgres. The package is tested against dbt 1.7.x and 1.8.x in CI.

Row-Level Security (RLS)

Does RLS on source tables affect stream table content?

No. Stream tables always materialize the full, unfiltered result set, regardless of any RLS policies on source tables. This matches the behavior of PostgreSQL’s built-in REFRESH MATERIALIZED VIEW.

The scheduled refresh runs as a superuser background worker. Manual calls to refresh_stream_table() and IMMEDIATE-mode IVM triggers also bypass RLS internally (SET LOCAL row_security = off / SECURITY DEFINER trigger functions), ensuring the stream table content is always complete and deterministic.

Can I use RLS on a stream table to filter reads per role?

Yes. Stream tables are regular PostgreSQL tables, so ALTER TABLE … ENABLE ROW LEVEL SECURITY and CREATE POLICY work exactly as expected. This is the recommended pattern for multi-tenant filtering:

ALTER TABLE pgtrickle.order_totals ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON pgtrickle.order_totals
    USING (tenant_id = current_setting('app.tenant_id')::INT);

One stream table serves all tenants. Per-tenant filtering happens at query time with zero storage duplication.

What happens when I ENABLE or DISABLE RLS on a source table?

pg_trickle’s DDL event trigger detects ALTER TABLE … ENABLE ROW LEVEL SECURITY, DISABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY, and NO FORCE ROW LEVEL SECURITY on source tables and marks all dependent stream tables for reinitialisation. The same applies to CREATE POLICY, ALTER POLICY, and DROP POLICY.

Why are IVM trigger functions SECURITY DEFINER?

In IMMEDIATE mode, the IVM trigger fires in the DML-issuing user’s context. If that user has restricted RLS visibility, the delta query could see only a subset of the base table rows, producing a corrupt stream table. Making the trigger function SECURITY DEFINER (owned by the extension installer, typically a superuser) ensures the delta query always has full visibility. The DML itself is still subject to the user’s own RLS policies — only the stream table maintenance runs with elevated privileges.

The trigger functions also set search_path = pg_catalog, pgtrickle, pgtrickle_changes, public to prevent search_path hijacking — a security best practice for all SECURITY DEFINER functions. The public schema is included because the delta SQL references user tables that typically reside there.

Deployment & Operations

This section covers the operational aspects of running pg_trickle in production: background workers, upgrades, restarts, replicas, Kubernetes, partitioned tables, and multi-database deployments.

How many background workers does pg_trickle use?

pg_trickle registers 1 background worker per database that contains stream tables. The worker is registered during _PG_init() (extension load) and starts automatically when PostgreSQL starts.

Component Workers Notes
Scheduler 1 per database Persistent; checks for stale STs every scheduler_interval_ms
WAL decoder 0 (shared) Shares the scheduler’s SPI connection
Manual refresh 0 Runs in the caller’s session

Ensure max_worker_processes (default 8) has room for the pg_trickle worker plus any other extensions.

How do I upgrade pg_trickle to a new version?

  1. Install the new shared library (replace the .so/.dylib file in PostgreSQL’s lib directory).
  2. Run the upgrade SQL: sql ALTER EXTENSION pg_trickle UPDATE; This applies migration scripts (e.g., pg_trickle--0.2.1--0.2.2.sql) that update catalog tables, add new functions, and migrate data as needed.
  3. Restart PostgreSQL if the shared library changed (required for shared_preload_libraries changes).
  4. Verify: sql SELECT pgtrickle.version();

Zero-downtime upgrades are possible for minor versions (patch releases) that don’t change the shared library. Just run ALTER EXTENSION pg_trickle UPDATE — no restart needed.

For detailed instructions, version-specific notes, rollback procedures, and troubleshooting, see the full Upgrading Guide.

How do I know if my shared library and SQL extension versions match?

The background worker checks for version mismatches at startup and logs a WARNING if the compiled .so version differs from the installed SQL extension version. You can also check manually:

-- Compiled .so version:
SELECT pgtrickle.version();

-- Installed SQL extension version:
SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';

If these differ, run ALTER EXTENSION pg_trickle UPDATE; and restart PostgreSQL if prompted.

Are stream tables preserved during an upgrade?

Yes. ALTER EXTENSION pg_trickle UPDATE applies only additive schema migrations (new columns, updated function signatures). Existing stream tables, their data, refresh history, and CDC infrastructure are preserved. The scheduler resumes normal operation after the upgrade completes.

For version-specific migration notes, see the Upgrading Guide — Version-Specific Notes.

What happens to stream tables during a PostgreSQL restart?

During a restart: 1. The scheduler stops. No refreshes occur while PostgreSQL is down. 2. CDC triggers are inactive. Source table writes during the restart window are captured when PostgreSQL comes back up (triggers are persistent DDL objects). 3. On startup, the scheduler background worker starts, reads the catalog, rebuilds the DAG, and resumes refresh cycles from where it left off. 4. Frontier reconciliation. The scheduler detects any gap between the stored frontier LSN and the current WAL position. Source changes that occurred between the last successful refresh and the restart are in the change buffers (for trigger-mode CDC) and will be processed in the first refresh cycle.

Net effect: Stream tables may be stale for the duration of the downtime, but no data is lost. The first refresh cycle after restart catches up automatically.

Can I use pg_trickle on a read replica / standby?

The scheduler does not run on standby servers. When pg_trickle detects it is running in recovery mode (pg_is_in_recovery() = true), the background worker enters a sleep loop and does not attempt any refreshes.

However, stream tables replicated from the primary are readable on the standby — they are regular heap tables and are replicated via physical (streaming) replication like any other table.

Pattern for read-heavy workloads: - Run pg_trickle on the primary — it performs all refreshes. - Query stream tables on the standby — read replicas get the latest refreshed data via streaming replication, with replication lag as the only additional delay.

How does pg_trickle work with CloudNativePG / Kubernetes?

pg_trickle is compatible with CloudNativePG. The cnpg/ directory in the repository contains example manifests:

  • Dockerfile.ext — builds a PostgreSQL image with pg_trickle pre-installed
  • cluster-example.yaml — CloudNativePG Cluster manifest with shared_preload_libraries = 'pg_trickle'

Key considerations: - Include pg_trickle in shared_preload_libraries in the Cluster’s postgresql configuration. - The scheduler runs on the primary pod only. Replica pods detect recovery mode and sleep. - Pod restarts are handled the same way as PostgreSQL restarts (see above). - Persistent volume claims preserve catalog and change buffers across pod rescheduling.

Does pg_trickle work with partitioned source tables?

Yes. pg_trickle installs CDC triggers on the partitioned parent table, which PostgreSQL automatically propagates to all existing and future partitions. When a row is inserted into any partition, the trigger fires and writes the change to the buffer table.

Caveats: - TRUNCATE on individual partitions fires the partition-level trigger, which is also captured. - Attaching or detaching partitions (ALTER TABLE ... ATTACH/DETACH PARTITION) fires DDL event triggers, which may mark the stream table for reinitialization. - Row movement between partitions (when the partition key is updated) is captured as a DELETE from the old partition and an INSERT into the new partition.

Can I run pg_trickle in multiple databases on the same cluster?

Yes. Each database gets its own independent scheduler background worker, its own catalog tables, and its own change buffers. Stream tables in different databases do not interact.

Resource planning: Each database with stream tables requires 1 background worker slot in max_worker_processes. If you have 3 databases using pg_trickle, you need at least 3 worker slots.

-- On each database where you want pg_trickle:
CREATE EXTENSION pg_trickle;

The extension must be created separately in each database — shared_preload_libraries loads the shared library cluster-wide, but the SQL objects (catalog tables, functions) are per-database.

Monitoring & Alerting

pg_trickle provides built-in monitoring views and NOTIFY-based alerting. This section explains the available views, alert events, and failure handling.

How do I list all stream tables in my database?

Several options depending on how much detail you need:

-- Quickest: name + status + mode + staleness
SELECT name, status, refresh_mode, is_populated, staleness
FROM pgtrickle.stream_tables_info;

-- Full stats: refresh counts, rows inserted/deleted, avg duration, error streaks
SELECT * FROM pgtrickle.pg_stat_stream_tables;

-- Live status including consecutive_errors and data_timestamp
SELECT * FROM pgtrickle.pgt_status();

-- Raw catalog (all persisted properties, no computed fields)
SELECT * FROM pgtrickle.pgt_stream_tables;

How do I inspect what pg_trickle is doing right now?

Quick status snapshot:

SELECT name, status, refresh_mode, consecutive_errors, staleness
FROM pgtrickle.pgt_status();

Deep dive into a specific stream table — shows the defining query, DVM operator tree, source tables, generated delta SQL, and current WAL frontier:

SELECT * FROM pgtrickle.explain_st('my_table');

Key properties returned:

Property Description
dvm_supported Whether differential maintenance is possible for this query
operator_tree How the DVM engine has decomposed the query
delta_query The actual SQL executed during a differential refresh
frontier Per-source LSN positions flushed at last refresh

Recent refresh activity:

-- Last 10 refreshes for a stream table (action, status, rows, duration):
SELECT * FROM pgtrickle.get_refresh_history('my_table', 10);

-- Aggregate refresh stats for all stream tables:
SELECT * FROM pgtrickle.st_refresh_stats();

CDC and slot health:

-- Per-source CDC mode, WAL lag, and alerts:
SELECT * FROM pgtrickle.check_cdc_health();

-- Replication slot health (slot_name, active, lag_bytes):
SELECT * FROM pgtrickle.slot_health();

Real-time event stream:

LISTEN pg_trickle_alert;
-- Receives JSON payloads for: stale_data, auto_suspended, resumed,
-- reinitialize_needed, buffer_growth_warning, refresh_completed, refresh_failed

Pending change buffers (rows not yet consumed by a differential refresh):

SELECT stream_table, source_table, cdc_mode, pending_rows, buffer_bytes
FROM pgtrickle.change_buffer_sizes()
ORDER BY pending_rows DESC;

Are there convenience functions for inspecting source tables and CDC buffers?

Yes. pg_trickle provides two functions added to complement the existing monitoring suite:

pgtrickle.list_sources(name) — shows every source table a stream table depends on, the CDC mode each uses, and any column-level usage metadata:

SELECT * FROM pgtrickle.list_sources('order_totals');
-- Returns: source_table, source_oid, source_type, cdc_mode, columns_used

pgtrickle.change_buffer_sizes() — shows, for every tracked source table, how many CDC rows are pending (not yet consumed by a differential refresh) and the estimated on-disk size of the change buffer:

SELECT * FROM pgtrickle.change_buffer_sizes()
ORDER BY pending_rows DESC;
-- Returns: stream_table, source_table, source_oid, cdc_mode, pending_rows, buffer_bytes

A large pending_rows value for a source table means a differential refresh is overdue or stalled — use pgtrickle.get_refresh_history() to investigate.

Can I see a tree view of all stream table dependencies?

Yes. pgtrickle.dependency_tree() walks the dependency DAG and renders it as an indented ASCII tree:

SELECT tree_line, status, refresh_mode
FROM pgtrickle.dependency_tree();

Example output:

tree_line                                 | status | refresh_mode
------------------------------------------+--------+--------------
report_summary                            | ACTIVE | DIFFERENTIAL
├── orders_by_region                      | ACTIVE | DIFFERENTIAL
│   ├── public.orders [src]              |        |
│   └── public.customers [src]           |        |
└── revenue_totals                        | ACTIVE | DIFFERENTIAL
    └── public.orders [src]              |        |

Each row has node (qualified name), node_type (stream_table or source_table), depth, status, and refresh_mode. Source tables are shown as leaves tagged with [src].

What monitoring views are available?

View Description
pgtrickle.stream_tables_info Status overview with computed staleness
pgtrickle.pg_stat_stream_tables Comprehensive stats (refresh counts, avg duration, error streaks)

How do I get alerted when something goes wrong?

pg_trickle sends PostgreSQL NOTIFY messages on the pg_trickle_alert channel with JSON payloads:

Event When
stale_data Staleness exceeds 2× the schedule
auto_suspended Stream table suspended after max consecutive errors
reinitialize_needed Upstream DDL change detected
buffer_growth_warning Change buffer growing unexpectedly
refresh_completed Refresh completed successfully
refresh_failed Refresh failed

Listen with: sql LISTEN pg_trickle_alert;

What happens when a stream table keeps failing?

After pg_trickle.max_consecutive_errors (default: 3) consecutive failures, the stream table moves to ERROR status and automatic refreshes stop. An auto_suspended NOTIFY alert is sent.

To recover: sql -- Fix the underlying issue (e.g., restore a dropped source table), then: SELECT pgtrickle.alter_stream_table('my_table', status => 'ACTIVE');

Retries use exponential backoff (base 1s, max 60s, ±25% jitter, up to 5 retries before counting as a real failure).

Configuration Reference

All pg_trickle settings are configured via PostgreSQL GUC parameters. The table below lists every available parameter with its type, default, and description.

GUC Type Default Description
pg_trickle.enabled bool true Enable/disable the scheduler. Manual refreshes still work when false.
pg_trickle.scheduler_interval_ms int 1000 Scheduler wake interval in milliseconds (100–60000)
pg_trickle.min_schedule_seconds int 60 Minimum allowed schedule duration (1–86400)
pg_trickle.max_consecutive_errors int 3 Failures before auto-suspending (1–100)
pg_trickle.change_buffer_schema text pgtrickle_changes Schema for CDC buffer tables
pg_trickle.max_concurrent_refreshes int 4 Max parallel refresh workers (1–32)
pg_trickle.user_triggers text auto User trigger handling: auto (detect), off (suppress), on (deprecated alias for auto)
pg_trickle.differential_max_change_ratio float 0.15 Change ratio threshold for adaptive FULL fallback (0.0–1.0)
pg_trickle.cleanup_use_truncate bool true Use TRUNCATE instead of DELETE for buffer cleanup

All GUCs are SUSET context (superuser SET) and take effect without restart, except shared_preload_libraries which requires a PostgreSQL restart.

Troubleshooting

This section covers common problems and how to debug them. If your issue isn’t listed here, check the refresh history for error messages and the monitoring views for status information.

Unit tests crash with symbol not found in flat namespace on macOS 26+

macOS 26 (Tahoe) changed the dynamic linker (dyld) to eagerly resolve all flat-namespace symbols at binary load time. pgrx extensions link PostgreSQL server symbols (e.g. CacheMemoryContext, SPI_connect) with -Wl,-undefined,dynamic_lookup, which previously resolved lazily. Since cargo test --lib runs outside the postgres process, those symbols are missing and the test binary aborts:

dyld[66617]: symbol not found in flat namespace '_CacheMemoryContext'

Use just test-unit — it automatically detects macOS 26+ and injects a stub library (libpg_stub.dylib) via DYLD_INSERT_LIBRARIES. The stub provides NULL/no-op definitions for the ~28 PostgreSQL symbols; they are never called during unit tests (pure Rust logic only).

This does not affect integration tests, E2E tests, just lint, just build, or the extension running inside PostgreSQL.

See the Installation Guide for details and manual usage.

My stream table is stuck in INITIALIZING status

The initial full refresh may have failed. Check: sql SELECT * FROM pgtrickle.get_refresh_history('my_table', 5); If the error is transient, retry with: sql SELECT pgtrickle.refresh_stream_table('my_table');

My stream table shows stale data but the scheduler is running

Common causes: 1. TRUNCATE on source table — bypasses CDC triggers. Manual refresh needed. 2. Too many errors — check consecutive_errors in pgtrickle.pg_stat_stream_tables. Resume with ALTER ... status => 'ACTIVE'. 3. Long-running refresh — check for lock contention or slow defining queries. 4. Scheduler disabled — verify SHOW pg_trickle.enabled; returns on.

I get “cycle detected” when creating a stream table

Stream tables cannot have circular dependencies. If stream table A depends on stream table B and B depends on A (either directly or through a chain of intermediate stream tables), pg_trickle rejects the creation with a clear error message listing the cycle path.

To resolve this, restructure your queries to eliminate the circular reference. Common patterns: - Extract the shared logic into a single base stream table that both A and B reference. - Use a regular view instead of a stream table for one side of the dependency. - Merge the two queries into a single stream table if possible.

A source table was altered and my stream table stopped refreshing

pg_trickle detects DDL changes (column additions, drops, type changes) via event triggers and marks affected stream tables with needs_reinit = true. The next scheduler cycle will attempt to reinitialize the stream table — drop the storage table, recreate it from the current defining query schema, and perform a full refresh.

If the schema change breaks the defining query (e.g., a column referenced in the query was dropped or renamed), the reinitialization will fail repeatedly until the stream table hits max_consecutive_errors and enters ERROR status.

To fix it: Update the defining query and recreate the stream table:

SELECT pgtrickle.drop_stream_table('order_totals');
SELECT pgtrickle.create_stream_table(
    name         => 'order_totals',
    query        => 'SELECT id, name FROM orders',  -- updated query reflecting new schema
    schedule     => '1m',
    refresh_mode => 'DIFFERENTIAL'
);

Check the refresh history for the specific error message:

SELECT * FROM pgtrickle.get_refresh_history('order_totals', 5);

How do I see the delta query generated for a stream table?

SELECT pgtrickle.explain_st('order_totals');

This shows the DVM operator tree, source tables, and the generated delta SQL.

How do I interpret the refresh history?

The pgtrickle.get_refresh_history() function returns the most recent refresh records for a stream table:

SELECT * FROM pgtrickle.get_refresh_history('order_totals', 10);

Key columns:

Column Meaning
action Refresh type: FULL, DIFFERENTIAL, TOPK, IMMEDIATE, or REINITIALIZE
rows_inserted Rows added to the stream table in this cycle
rows_deleted Rows removed from the stream table in this cycle
rows_updated Rows modified in the stream table (for explicit DML path)
duration_ms Wall-clock time for the refresh
error_message NULL for success; error text for failures
source_changes Number of pending change records processed
fallback_reason If DIFFERENTIAL fell back to FULL: change_ratio_exceeded, truncate_detected, or reinitialize

Patterns to look for: - High rows_inserted + rows_deleted with low source_changes → possible duplicate rows (keyless source tables) - fallback_reason = 'change_ratio_exceeded' frequently → consider lowering the threshold or switching to FULL mode - Increasing duration_ms over time → index maintenance or buffer bloat; consider VACUUM or checking for missing indexes

How can I tell if the scheduler is running?

Several ways to verify:

1. Check the background worker: sql SELECT pid, datname, backend_type, state, query FROM pg_stat_activity WHERE backend_type = 'pg_trickle scheduler';

If no rows are returned, the scheduler is not running. Common causes: pg_trickle.enabled = false, extension not in shared_preload_libraries, or max_worker_processes exhausted.

2. Check recent refresh activity: sql SELECT MAX(refreshed_at) AS last_refresh FROM pgtrickle.pgt_stream_tables WHERE status = 'ACTIVE';

If the last refresh was long ago relative to the shortest schedule, the scheduler may be stuck.

3. Check PostgreSQL logs: The scheduler logs startup and shutdown messages at LOG level: LOG: pg_trickle scheduler started for database "mydb" LOG: pg_trickle scheduler shutting down (SIGTERM)

How do I debug a stream table that shows stale data?

Follow this diagnostic checklist:

  1. Is the scheduler running? (See above)
  2. Is the stream table active? sql SELECT pgt_name, status, consecutive_errors FROM pgtrickle.pg_stat_stream_tables WHERE pgt_name = 'my_st'; If status is ERROR or SUSPENDED, the stream table has been auto-suspended after repeated failures.
  3. Are there pending changes? sql SELECT COUNT(*) FROM pgtrickle_changes.changes_<source_oid>; If zero, the source table may not have CDC triggers (check SELECT tgname FROM pg_trigger WHERE tgrelid = '<source_oid>').
  4. Is the refresh failing silently? sql SELECT * FROM pgtrickle.get_refresh_history('my_st', 5); Check for error messages.
  5. Is there lock contention? Long-running transactions holding locks on the source or stream table can block refreshes. Check pg_locks and pg_stat_activity.

What does the needs_reinit flag mean and how do I clear it?

The needs_reinit flag in pgtrickle.pgt_stream_tables indicates that the stream table’s physical storage needs to be rebuilt — typically because a source table’s schema changed.

When needs_reinit = true: - The scheduler skips normal differential/full refresh. - Instead, it performs a reinitialize: drop the storage table, recreate it from the current defining query schema, and populate with a full refresh. - If reinitialization succeeds, needs_reinit is cleared automatically.

If reinitialization keeps failing (e.g., the defining query references a dropped column): sql -- Fix the underlying issue first, then clear manually: UPDATE pgtrickle.pgt_stream_tables SET needs_reinit = false WHERE pgt_name = 'my_st'; -- Or drop and recreate: SELECT pgtrickle.drop_stream_table('my_st'); SELECT pgtrickle.create_stream_table( name => 'my_st', query => 'SELECT ...', schedule => '1m', refresh_mode => 'DIFFERENTIAL' );

Why Are These SQL Features Not Supported?

This section gives detailed technical explanations for each SQL limitation. pg_trickle follows the principle of “fail loudly rather than produce wrong data” — every unsupported feature is detected at stream-table creation time and rejected with a clear error message and a suggested rewrite.

For all of these, returning an explicit error is a deliberate design choice: the alternative would be silently producing incorrect results after a refresh, which is far harder to diagnose.

How does NATURAL JOIN work?

NATURAL JOIN is fully supported. At parse time, pg_trickle resolves the common columns between the two tables (using OpTree::output_columns()) and synthesizes explicit equi-join conditions. This supports INNER, LEFT, RIGHT, and FULL NATURAL JOIN variants.

Internally, NATURAL JOIN is converted to an explicit JOIN ... ON before the DVM engine builds its operator tree, so delta computation works identically to a manually specified equi-join.

Note: The internal __pgt_row_id column is excluded from common column resolution, so NATURAL JOINs between stream tables work correctly.

How do GROUPING SETS, CUBE, and ROLLUP work?

GROUPING SETS, CUBE, and ROLLUP are fully supported via an automatic parse-time rewrite. pg_trickle decomposes these constructs into a UNION ALL of separate GROUP BY queries before the DVM engine processes the query.

Explosion guard: CUBE(N) generates $2N$ branches. pg_trickle rejects CUBE/ROLLUP combinations that would produce more than 64 branches to prevent runaway memory usage. Use explicit GROUPING SETS(...) instead.

For example: ```sql – This defining query: SELECT dept, region, SUM(amount) FROM sales GROUP BY CUBE(dept, region)

– Is automatically rewritten to: SELECT dept, region, SUM(amount) FROM sales GROUP BY dept, region UNION ALL SELECT dept, NULL::text, SUM(amount) FROM sales GROUP BY dept UNION ALL SELECT NULL::text, region, SUM(amount) FROM sales GROUP BY region UNION ALL SELECT NULL::text, NULL::text, SUM(amount) FROM sales ```

GROUPING() function calls are replaced with integer literal constants corresponding to the grouping level. The rewrite is transparent — the DVM engine sees only standard GROUP BY + UNION ALL operators and can apply incremental delta computation to each branch independently.

How does DISTINCT ON (…) work?

DISTINCT ON is fully supported via an automatic parse-time rewrite. pg_trickle transparently transforms DISTINCT ON into a ROW_NUMBER() window function subquery:

-- This defining query:
SELECT DISTINCT ON (dept) dept, employee, salary
FROM employees ORDER BY dept, salary DESC

-- Is automatically rewritten to:
SELECT dept, employee, salary FROM (
    SELECT dept, employee, salary,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) sub WHERE rn = 1

The rewrite happens before DVM parsing, so the operator tree sees a standard window function query and can apply partition-based recomputation for incremental delta maintenance.

Why is TABLESAMPLE rejected?

TABLESAMPLE returns a random subset of rows from a table (e.g., FROM orders TABLESAMPLE BERNOULLI(10) gives ~10% of rows).

Stream tables materialize the complete result set of the defining query and keep it up-to-date across refreshes. Baking a random sample into the defining query is not meaningful because:

  1. Non-determinism. Each refresh would sample different rows, making the stream table contents unstable and unpredictable. The delta between refreshes would be dominated by sampling noise, not actual data changes.

  2. CDC incompatibility. The trigger-based change-capture system tracks specific row-level changes (inserts, updates, deletes). A TABLESAMPLE defining query has no stable row identity — the “changed rows” concept doesn’t apply when the entire sample shifts each cycle.

Rewrite: ```sql – Instead of sampling in the defining query: SELECT * FROM orders TABLESAMPLE BERNOULLI(10)

– Materialize the full result and sample when querying: SELECT * FROM order_stream_table WHERE random() < 0.1 ```

Why is LIMIT / OFFSET rejected?

Stream tables materialize the complete result set and keep it synchronized with source data. Bare LIMIT/OFFSET (without a recognized pattern) would truncate the result:

  1. Undefined ordering. LIMIT without ORDER BY returns an arbitrary subset.

  2. Delta instability. When source rows change, the boundary between “in the LIMIT” and “out of the LIMIT” shifts. A single INSERT could evict one row and admit another, requiring the refresh to track the full ordered position of every row.

  3. Semantic mismatch. Users who write LIMIT 100 typically want to limit what they read, not what is stored.

Exception — TopK pattern: When the defining query has a top-level ORDER BY … LIMIT N (constant integer, optionally with OFFSET M), pg_trickle recognizes this as a TopK query and accepts it. The ORDER BY clause is required — bare LIMIT without ORDER BY is always rejected because it selects an arbitrary subset. With ORDER BY, the top-N boundary is well-defined and the stream table stores exactly those N rows (starting from position M+1 if OFFSET is specified). See the TopK section for details.

Rewrite (when TopK doesn’t apply): ```sql – Instead of: ‘SELECT * FROM orders ORDER BY created_at DESC LIMIT 100’

– Omit LIMIT from the defining query, apply when reading: SELECT * FROM orders_stream_table ORDER BY created_at DESC LIMIT 100 ```

Why are window functions in expressions rejected?

Window functions like ROW_NUMBER() OVER (…) are supported as standalone columns in stream tables. However, embedding a window function inside an expression — such as CASE WHEN ROW_NUMBER() OVER (...) = 1 THEN ... or SUM(x) OVER (...) + 1 — is rejected.

This restriction exists because:

  1. Partition-based recomputation. pg_trickle’s differential mode handles window functions by recomputing entire partitions that were affected by changes. When a window function is buried inside an expression, the DVM engine cannot isolate the window computation from the surrounding expression, making it impossible to correctly identify which partitions to recompute.

  2. Expression tree ambiguity. The DVM parser would need to differentiate the outer expression (arithmetic, CASE, etc.) while treating the inner window function specially. This creates a combinatorial explosion of differentiation rules for every possible expression type × window function combination.

Rewrite: ```sql – Instead of: SELECT id, CASE WHEN ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1 THEN ‘top’ ELSE ‘other’ END AS rank_label FROM employees

– Move window function to a separate column, then use a wrapping stream table: – ST1: SELECT id, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees

– ST2 (references ST1): SELECT id, CASE WHEN rn = 1 THEN ‘top’ ELSE ‘other’ END AS rank_label FROM pgtrickle.employees_ranked ```

Why is FOR UPDATE / FOR SHARE rejected?

FOR UPDATE and related locking clauses (FOR SHARE, FOR NO KEY UPDATE, FOR KEY SHARE) acquire row-level locks on selected rows. This is incompatible with stream tables because:

  1. Refresh semantics. Stream table contents are managed by the refresh engine using bulk MERGE operations. Row-level locks taken during the defining query would conflict with the refresh engine’s own locking strategy.

  2. No direct DML. Since users cannot directly modify stream table rows, there is no use case for locking rows inside the defining query. The locks would be held for the duration of the refresh transaction and then released, serving no purpose.

How does ALL (subquery) work?

ALL (subquery) comparisons (e.g., WHERE x > ALL (SELECT y FROM t)) are supported via an automatic rewrite to NOT EXISTS. For example, x > ALL (SELECT y FROM t) is rewritten to NOT EXISTS (SELECT 1 FROM t WHERE y >= x), which pg_trickle handles via its anti-join operator.

Why is ORDER BY silently discarded?

ORDER BY in the defining query is accepted but ignored. This is consistent with how PostgreSQL treats CREATE MATERIALIZED VIEW AS SELECT ... ORDER BY ... — the ordering is not preserved in the stored data.

Stream tables are heap tables with no guaranteed row order. The ORDER BY in the defining query would only affect the order of the initial INSERT, which has no lasting effect. Apply ordering when querying the stream table:

-- This ORDER BY is meaningless in the defining query:
'SELECT region, SUM(amount) FROM orders GROUP BY region ORDER BY total DESC'

-- Instead, order when reading:
SELECT * FROM regional_totals ORDER BY total DESC

Why are unsupported aggregates (CORR, COVAR_*, REGR_*) limited to FULL mode?

Regression aggregates like CORR, COVAR_POP, COVAR_SAMP, and the REGR_* family require maintaining running sums of products and squares across the entire group. Unlike COUNT/SUM/AVG (where deltas can be computed from the change alone) or group-rescan aggregates (where only affected groups are re-read), regression aggregates:

  1. Lack algebraic delta rules. There is no closed-form way to update a correlation coefficient from a single row change without access to the full group’s data.

  2. Would degrade to group-rescan anyway. Even if supported, the implementation would need to rescan the full group from source — identical to FULL mode for most practical group sizes.

These aggregates work fine in FULL refresh mode, which re-runs the entire query from scratch each cycle.

Why Are These Stream Table Operations Restricted?

Stream tables are regular PostgreSQL heap tables under the hood, but their contents are managed exclusively by the refresh engine. This section explains why certain operations that work on ordinary tables are disallowed or unsupported on stream tables, and what to do instead.

Why can’t I INSERT, UPDATE, or DELETE rows in a stream table?

Stream table contents are the output of the refresh engine — they represent the materialized result of the defining query at a specific point in time. Direct DML would corrupt this contract in several ways:

  1. Row ID integrity. Every row has a __pgt_row_id (a 64-bit xxHash of the group-by key or all columns). The refresh engine uses this for delta MERGE — matching incoming deltas against existing rows. A manually inserted row with an incorrect or duplicate __pgt_row_id would cause the next differential refresh to produce wrong results (double-counting, missed deletes, or merge conflicts).

  2. Frontier inconsistency. Each refresh records a frontier — a set of per-source LSN positions that represent “data up to this point has been materialized.” A manual DML change is not tracked by any frontier. The next differential refresh would either overwrite the change (if the delta touches the same row) or leave the stream table in a state that doesn’t match any consistent point-in-time snapshot of the source data.

  3. Change buffer desync. The CDC triggers on source tables write changes to buffer tables. The refresh engine reads these buffers and advances the frontier. Manual DML on the stream table bypasses this pipeline entirely — the buffer and frontier have no record of the change, so future refreshes cannot account for it.

If you need to post-process stream table data, create a view or a second stream table that references the first one.

Why can’t I add foreign keys to or from a stream table?

Foreign key constraints require that referenced/referencing rows exist at the time of each DML statement. The refresh engine violates this assumption:

  1. Bulk MERGE ordering. A differential refresh executes a single MERGE INTO statement that applies all deltas (inserts and deletes) atomically. PostgreSQL evaluates FK constraints row-by-row within this MERGE. If a parent row is deleted and a new parent inserted in the same delta batch, the child FK check may fail because it sees the delete before the insert — even though the final state would be consistent.

  2. Full refresh uses TRUNCATE + INSERT. In FULL mode, the refresh engine truncates the stream table and re-inserts all rows. TRUNCATE does not fire individual DELETE triggers and bypasses FK cascade logic, which would leave referencing tables with dangling references.

  3. Cross-table refresh ordering. If stream table A has an FK referencing stream table B, both tables refresh independently (in topological order, but in separate transactions). There is no guarantee that A’s refresh sees B’s latest data — the FK constraint could transiently fail between refreshes.

Workaround: Enforce referential integrity in the consuming application or use a view that joins the stream tables and validates the relationship.

How do user-defined triggers work on stream tables?

When a DIFFERENTIAL mode stream table has user-defined row-level triggers, the refresh engine uses explicit DML decomposition instead of MERGE:

  1. Delta materialized once. The delta query result is stored in a temporary table (__pgt_delta_<id>) to avoid evaluating it three times.

  2. DELETE removed rows. Rows in the stream table whose __pgt_row_id is absent from the delta are deleted. AFTER DELETE triggers fire with correct OLD values.

  3. UPDATE changed rows. Rows whose __pgt_row_id exists in both the stream table and delta but whose values differ (checked via IS DISTINCT FROM) are updated. AFTER UPDATE triggers fire with correct OLD and NEW. No-op updates (where values are identical) are skipped, preventing spurious triggers.

  4. INSERT new rows. Rows in the delta whose __pgt_row_id is absent from the stream table are inserted. AFTER INSERT triggers fire with correct NEW values.

FULL refresh behavior: Row-level user triggers are automatically suppressed during FULL refresh via DISABLE TRIGGER USER / ENABLE TRIGGER USER. A NOTIFY pgtrickle_refresh is emitted so listeners know a FULL refresh occurred. Use REFRESH MODE DIFFERENTIAL for stream tables that need per-row trigger semantics.

Performance: The explicit DML path adds ~25–60% overhead compared to MERGE for triggered stream tables. Stream tables without user triggers have zero overhead (only a fast pg_trigger check, <0.1 ms).

Control: The pg_trickle.user_triggers GUC controls this behavior: - auto (default): detect user triggers automatically - off: always use MERGE, suppressing triggers - on: deprecated compatibility alias for auto

Why can’t I ALTER TABLE a stream table directly?

Stream table metadata (defining query, schedule, refresh mode) is stored in the pg_trickle catalog (pgtrickle.pgt_stream_tables). A direct ALTER TABLE would change the physical table without updating the catalog, causing:

  1. Column mismatch. If you add or remove columns, the refresh engine’s cached delta query and MERGE statement would reference columns that no longer exist (or miss new ones), causing runtime errors.

  2. __pgt_row_id invalidation. The row ID hash is computed from the defining query’s output columns. Altering the table schema without updating the defining query would make existing row IDs inconsistent with the new column set.

Use pgtrickle.alter_stream_table() to change schedule, refresh mode, or status. To change the defining query or column structure, drop and recreate the stream table.

Why can’t I TRUNCATE a stream table?

TRUNCATE removes all rows instantly but does not update the pg_trickle frontier or change buffers. After a TRUNCATE:

  1. Differential refresh sees no changes. The frontier still records the last-processed LSN. No new source changes may have occurred, so the next differential refresh produces an empty delta — leaving the stream table empty even though the source still has data.

  2. No recovery path for differential mode. The refresh engine has no way to detect that the stream table was externally truncated. It assumes the current contents match the frontier.

Use pgtrickle.refresh_stream_table('my_table') to force a full re-materialization, or drop and recreate the stream table if you need a clean slate.

What are the memory limits for delta processing?

The differential refresh path executes the delta query as a single SQL statement. For large batches (e.g., a bulk UPDATE of 10M rows), PostgreSQL may attempt to materialize the entire delta result set in memory. If the delta exceeds work_mem, PostgreSQL will spill to temporary files on disk, which is slower but safe. In extreme cases, OOM (out of memory) can occur if work_mem is set very high and the delta is enormous.

Mitigations:

  1. Adaptive fallback. The pg_trickle.differential_max_change_ratio GUC (default 0.15) automatically triggers a FULL refresh when the ratio of pending changes to total rows exceeds the threshold. This prevents large deltas from consuming excessive memory.

  2. work_mem tuning. PostgreSQL’s work_mem setting controls how much memory each sort/hash operation uses before spilling to disk. For pg_trickle workloads, 64–256 MB is typical. Monitor temp_blks_written in pg_stat_statements to detect spilling.

  3. pg_trickle.merge_work_mem_mb GUC. Sets a session-level work_mem override during the MERGE execution (default: 0 = use global work_mem). This allows higher memory for refresh without affecting other queries.

  4. Monitoring. If pg_stat_statements is installed, pg_trickle logs a warning when the MERGE query writes temporary blocks to disk.

Why are refreshes processed sequentially by default?

The default (parallel_refresh_mode = 'off') is sequential because it is simple, correct, and efficient for most workloads. Topological ordering guarantees upstream stream tables refresh before downstream ones with no coordination overhead.

When to consider enabling parallel refresh:

  • Your database has many independent stream tables (no shared dependencies).
  • Total cycle time = sum of all refresh durations and some refreshes are visibly blocking unrelated ones.
  • You have enough max_worker_processes headroom (each parallel worker uses one slot).

Enabling parallel refresh (v0.4.0+):

ALTER SYSTEM SET pg_trickle.parallel_refresh_mode = 'on';
SELECT pg_reload_conf();

With parallel_refresh_mode = 'on', the scheduler builds an execution-unit DAG and dispatches independent units to dynamic background workers. Atomic consistency groups and IMMEDIATE-trigger closures remain single-worker for correctness.

See CONFIGURATION.md — Parallel Refresh for tuning guidance including the worker-budget formula.

How many connections does pg_trickle use?

pg_trickle uses the following PostgreSQL connections:

Component Connections When
Background scheduler 1 Always (per database with STs)
WAL decoder polling 0 (shared) Uses the scheduler’s SPI connection
Manual refresh 1 Per-call (uses caller’s session)

Total: 1 persistent connection per database. WAL decoder polling shares the scheduler’s SPI connection rather than opening separate connections.

max_worker_processes: pg_trickle registers 1 background worker per database during _PG_init(). Ensure max_worker_processes (default 8) has room for the pg_trickle worker plus any other extensions.

Advisory locks: The scheduler holds a session-level advisory lock per actively-refreshing ST. These are released immediately after each refresh completes.