pg_trickle

pg_trickle is a PostgreSQL 18 extension that adds self-maintaining materialized views — stream tables — and keeps them up to date incrementally as the underlying data changes. No external streaming engine, no sidecars, no bespoke refresh pipeline. Just install the extension and write SQL.

SELECT pgtrickle.create_stream_table(
    name     => 'active_orders',
    query    => 'SELECT * FROM orders WHERE status = ''active''',
    schedule => '30s'
);

INSERT INTO orders (id, status) VALUES (42, 'active');
SELECT count(*) FROM active_orders;  -- 1, automatically

New here? Read What is pg_trickle? for the plain-language overview, or jump to the 5-Minute Quickstart to try it. First time installing? See the Installation Guide.

How it works

pg_trickle keeps stream tables current by tracking every change to the source tables — inserts, updates, and deletes — and recomputing only the parts of the view that are affected by those changes. This is called differential (or incremental) view maintenance. Instead of re-running the full query on every refresh cycle, pg_trickle applies a delta computation proportional to the number of changed rows, not the total table size. A stream table over a billion-row orders table refreshes in milliseconds when only a few rows changed.

Change capture works through row-level AFTER triggers (the default) or WAL-based logical decoding (cdc_mode = 'wal' or the automatic 'auto' mode). Trigger-based capture writes changed rows into a per-source change-buffer table within the same transaction, providing full atomicity with no possibility of a committed change being missed. The background scheduler reads from the change buffer, computes the delta SQL, and applies the result to the stream table using MERGE in a separate transaction.

For queries that cannot be maintained incrementally (non-monotonic functions, LATERAL with volatile sub-expressions, etc.), pg_trickle automatically falls back to a full refresh — replacing the entire stream table contents in a single transaction. You can also force full mode explicitly or let the cost-based AUTO strategy choose per-refresh based on the change-to-table-size ratio.

Choose your path

What's new

See What's New for a curated summary of recent releases, or the Changelog for the full history.

Project & licensing

• Written in Rust using pgrx.
• Targets PostgreSQL 18.
• Apache 2.0 licensed.
• Repository: https://github.com/trickle-labs/pg-trickle
• Project history · Roadmap · Contributing · Security policy

What is pg_trickle?

pg_trickle is a PostgreSQL 18 extension that adds stream tables — tables that are defined by a SQL query and stay up to date automatically as the underlying data changes. No external process, no streaming engine, no pipeline to operate. Just install the extension and write SQL.

If you have ever wished CREATE MATERIALIZED VIEW would just keep itself fresh, this is that.

The problem

PostgreSQL's materialized views are powerful but frustrating. REFRESH MATERIALIZED VIEW re-runs the entire query from scratch, even if only one row changed in a million-row table. Your choices are:

• Burn CPU on full recomputation, on a schedule, and hope you refresh often enough.
• Accept stale data, and try to explain that to the dashboard user.
• Build a bespoke refresh pipeline — Debezium, Kafka Connect, a streaming engine, a separate read database. Now you have two systems to operate.

Most teams pick option 3 and end up maintaining infrastructure that is more complex than the application it supports.

What pg_trickle does instead

You declare a stream table with a SQL query and a schedule:

SELECT pgtrickle.create_stream_table(
    name     => 'revenue_by_region',
    query    => $$
        SELECT c.region,
               COUNT(*)                  AS order_count,
               SUM(o.quantity * p.price) AS total_revenue
        FROM orders   o
        JOIN customers c ON c.id = o.customer_id
        JOIN products  p ON p.id = o.product_id
        GROUP BY c.region
    $$,
    schedule => '1s'
);

-- Read it like any table — always fresh.
SELECT * FROM revenue_by_region ORDER BY total_revenue DESC;

Behind the scenes:

1. pg_trickle parses your query into an operator tree (scans, joins, aggregates).
2. It captures every INSERT, UPDATE, and DELETE on the source tables — by default with lightweight row-level triggers, with no replication slots required.
3. On each refresh cycle (every 1s, in the example above) it derives a delta query from the operator tree — the SQL that computes only the change since the last refresh — and merges the result into the stream table.

When you insert one row into a million-row source table, pg_trickle processes one row's worth of computation. Not a million.

Why people care

A few things make this materially different from "just another IVM project":

No external infrastructure. No Kafka, no Flink, no Debezium, no sidecar. The extension lives inside PostgreSQL and uses only its built-in mechanisms.

Stream tables can depend on stream tables. A single write to a base table can ripple through a graph of derived tables, each refreshed in the right order, each doing only the work proportional to what actually changed.

Demand-driven scheduling. With the default CALCULATED schedule mode, you only set a refresh interval on the consumer-facing stream tables — the ones your application actually reads. Upstream stream tables inherit the tightest cadence among their downstream dependents. You declare freshness where it matters; the system propagates it everywhere else.

Hybrid change capture. It bootstraps with row-level triggers, which always work. If wal_level = logical is available, it transitions automatically to WAL-based capture for near-zero write-side overhead. The transition is seamless. If anything goes wrong, it falls back to triggers.

Broad SQL coverage. Joins (inner, left, right, full outer, lateral), GROUP BY with 30+ aggregates, DISTINCT, UNION/INTERSECT/EXCEPT, subqueries (EXISTS, IN, scalar), CTEs including WITH RECURSIVE, window functions, and most of standard SQL. See docs/SQL_REFERENCE.md for the complete matrix.

Inside the same transaction, if you want it. IMMEDIATE refresh mode maintains the stream table inside the same transaction as the source DML, giving read-your-writes consistency without any background worker.

Where the design comes from

The mathematical foundation is the DBSP differential dataflow framework (Budiu et al., 2022). Delta queries are derived automatically from your SQL's operator tree:

• joins produce the classic bilinear expansion,
• aggregates maintain auxiliary counters,
• linear operators like filters and projections pass deltas through unchanged.

You do not need to know any of this to use the extension; the rules are baked in. If you are curious, the DVM Operators reference and the DBSP Comparison explain how pg_trickle relates to the original.

Performance, briefly

Performance is a primary goal. Maximum throughput, low latency, and minimal overhead drive every design decision. Differential refresh is the default; full refresh is a fallback of last resort.

A few headline numbers from the TPC-H validation:

• All 22 standard analytical queries pass in DIFFERENTIAL, IMMEDIATE, and FULL modes, with identical results across modes.
• 5–90× measured speedup over FULL across the suite at 1% change rate.
• The bottleneck is PostgreSQL's own MERGE, not pg_trickle's pipeline.

For workloads with high write volume, hybrid CDC and column-level change tracking keep the write-side overhead low (sub-microsecond per row in WAL mode).

What's it built on

• Language: Rust, using pgrx 0.18.
• Targets: PostgreSQL 18.
• License: Apache 2.0.
• Status: active development, approaching 1.0. APIs may still change between minor versions; see ROADMAP.md.
• Tests: thousands of unit, integration, and end-to-end tests. TPC-H 22/22 in all modes.

Try it

The fastest paths from zero to a working stream table:

# Option 1 — playground (sample data, dashboards)
cd playground && docker compose up -d

# Option 2 — minimal Docker image
docker run --rm -e POSTGRES_PASSWORD=secret -p 5432:5432 \
  ghcr.io/trickle-labs/pg_trickle:latest

Then read the 5-Minute Quickstart.

Where to go next

Source: https://github.com/trickle-labs/pg-trickle

Note on terminology. A few terms are used throughout the docs without further definition: stream table, differential, delta query, DAG, frontier. The Glossary defines all of them.

Use Cases

pg_trickle is for any team that has wanted PostgreSQL to keep a derived table up to date automatically — without writing a refresh cron, an external streaming pipeline, or a custom CDC consumer.

This page is a gallery of the most common things people build with stream tables. Each section tells you what the pattern looks like, gives you a minimal SQL example, and links to a deeper guide.

New to stream tables? Read What is pg_trickle? first, or jump straight to the 5-minute Quickstart.

At a glance

1. Real-time dashboards

You want KPI tiles ("orders today", "revenue per region", "active users this hour") to update within a second or two of the underlying data changing. With pg_trickle you write the SQL once and any number of dashboards (Grafana, Metabase, Looker, a custom React app) just read from the stream table.

SELECT pgtrickle.create_stream_table(
    'kpi_revenue_today',
    $$SELECT region, SUM(amount) AS revenue
      FROM orders
      WHERE created_at >= date_trunc('day', now())
      GROUP BY region$$,
    schedule => '2s'
);

Why it's a fit: aggregates over high-cardinality groups are exactly where DIFFERENTIAL refresh wins biggest.

2. Operational read models / CQRS

A microservice writes to a normalised event/order/customer table; a read API needs the denormalised projection (one row per order with customer name, current status, latest payment). Most teams build this with a separate read database and a CDC pipeline. With pg_trickle the projection is just a stream table sitting next to the write tables.

SELECT pgtrickle.create_stream_table(
    'order_view',
    $$SELECT o.id, o.placed_at, c.name AS customer,
             p.status AS payment_status,
             COALESCE(s.shipped_at, NULL) AS shipped_at
      FROM orders o
      JOIN customers c ON c.id = o.customer_id
      LEFT JOIN payments p ON p.order_id = o.id
      LEFT JOIN shipments s ON s.order_id = o.id$$,
    refresh_mode => 'IMMEDIATE'
);

Why it's a fit: IMMEDIATE mode gives you read-your-writes consistency without a second database.

3. Fraud detection, alerting, anomaly

Define a stream table that flags suspicious activity (large transactions, velocity rules, unusual geographies). Subscribe an alerter to that stream table — either via PostgreSQL LISTEN/NOTIFY, a downstream publication, or by polling.

SELECT pgtrickle.create_stream_table(
    'high_velocity_accounts',
    $$SELECT account_id, COUNT(*) AS txn_count, SUM(amount) AS total
      FROM transactions
      WHERE occurred_at >= now() - interval '5 minutes'
      GROUP BY account_id
      HAVING COUNT(*) > 20$$,
    schedule => '1s'
);

The demo ships a full 9-node fraud-detection DAG you can run locally with docker compose up.

4. Leaderboards & TopK

ORDER BY ... LIMIT N stream tables are a special case where pg_trickle stores only the top N rows and updates them with scoped recomputation when the changes affect the leaderboard.

SELECT pgtrickle.create_stream_table(
    'top_10_customers',
    $$SELECT customer_id, SUM(amount) AS lifetime_spend
      FROM orders
      GROUP BY customer_id
      ORDER BY lifetime_spend DESC
      LIMIT 10$$,
    schedule => '5s'
);

5. Bronze / Silver / Gold

A medallion architecture in PostgreSQL alone:

• Bronze – raw ingest table (a regular table you write to).
• Silver – cleaned and deduplicated stream table.
• Gold – business-level aggregates stream table that depends on Silver.

Set the schedule only on Gold; pg_trickle propagates the cadence upstream automatically (CALCULATED scheduling).

See Patterns §1.

6. Event-driven services

The transactional outbox/inbox pattern, native to PostgreSQL:

• Outbox — write events in the same transaction as your business data; an external system drains them to Kafka / NATS / SQS / a webhook.
• Inbox — receive events idempotently from an external system; stream tables give you live views of pending work, retries, and a dead-letter queue.

See Transactional Outbox and Transactional Inbox.

7. Cross-system replication

Once a stream table exists, you can expose it as a standard PostgreSQL logical-replication publication. Anything that speaks logical replication — Debezium, Kafka Connect, a downstream PostgreSQL replica, a Spark Structured Streaming job, a custom WAL consumer — can subscribe to live changes.

SELECT pgtrickle.stream_table_to_publication('order_view');
-- Subscribers can now subscribe to publication pgt_pub_order_view

See Downstream Publications.

8. Slowly-changing dimensions (SCD)

Type 2 SCDs (one row per version, with valid_from / valid_to) fall out naturally from a stream table over an event log. See Patterns §3.

9. Multi-tenant analytics

If your application multi-tenants by tenant_id, you can build per-tenant aggregates as a single stream table grouped by tenant_id and protect access with PostgreSQL Row-Level Security. See Multi-tenant integration and the Row-Level Security tutorial.

10. Citus distributed analytics

pg_trickle works on Citus-distributed source tables. The scheduler polls per-worker WAL slots via dblink, merges changes on the coordinator, and applies the delta — automatically and idempotently. See Citus.

11. dbt-managed warehouse models

Use the stream_table materialization in dbt. No custom adapter needed — works with the standard dbt-postgres adapter.

{{ config(materialized='stream_table', schedule='5m', refresh_mode='AUTO') }}
SELECT customer_id, SUM(amount) AS total
FROM {{ source('raw', 'orders') }}
GROUP BY customer_id

See dbt integration.

When pg_trickle is not the right fit

A short, honest list — knowing when to look elsewhere is a feature.

• Pure OLTP with no derived state. If you don't have anything to materialize, you don't need a materializer.
• Sub-millisecond latency derived state. IMMEDIATE mode is fast, but it pays the differential cost inside your transaction. If you need every transaction to commit in < 1 ms, benchmark first.
• Stateless event transformation only. If you want "transform each Kafka event" with no stored state, a stream processor (Flink, Bytewax) is closer to that shape.
• Cross-database joins at scale. pg_trickle reads from local PostgreSQL tables (or Citus distributions). For federated joins across many heterogeneous databases, consider a streaming engine.
• Workloads where you cannot install extensions. Some managed PostgreSQL services don't allow third-party extensions. Check Installation for the support matrix.

See also: Patterns · Performance Cookbook · SQL Reference · Comparisons

Comparisons

This page compares pg_trickle to adjacent tools so you can decide whether it's the right fit. Each comparison is a short, honest summary — strengths, weaknesses, and "use this instead if…".

If you are evaluating pg_trickle from a specific tool you already run, jump to the relevant section. If you want a deeper academic comparison, see also DBSP Comparison, pg_ivm Comparison, and Prior Art.

At a glance

vs. PostgreSQL REFRESH MATERIALIZED VIEW

The question this answers: "I'm already using materialized views — what would I gain?"

Use vanilla materialized views if: you only refresh occasionally, your data is small, and you do not have a chain of dependent views.

Switch to pg_trickle if: any of those things stop being true.

vs. pg_ivm

The question this answers: "There's another PostgreSQL extension in this space — how do they relate?"

pg_ivm is an open-source IVM extension that pioneered much of the relevant work in PostgreSQL land. The two projects have different scopes.

There is a more thorough side-by-side at research/PG_IVM_COMPARISON.md.

If your queries are simple aggregates and you want the smallest possible install footprint, pg_ivm is a perfectly good choice. If you want broader SQL, multi-layer DAGs, or operational tooling, pg_trickle is closer to that shape.

vs. Materialize

Materialize is a cloud-native database built specifically for incremental view maintenance. It is the inspiration for much of this space.

Use Materialize if: you want one engine to materialise across many heterogeneous sources, you want true streaming semantics, and you are happy operating a separate database.

Use pg_trickle if: your data lives in PostgreSQL and you want the materialisation to live there too.

vs. RisingWave

RisingWave is a PostgreSQL-wire-compatible streaming database in Rust. Like Materialize, it is its own engine that you deploy alongside (or instead of) PostgreSQL.

The same trade-off applies: RisingWave is a richer streaming engine; pg_trickle is the answer if you do not want to operate a second database.

vs. Apache Flink (or Spark Structured Streaming)

Flink is a general stateful stream processor. It can do everything pg_trickle can and a lot more — including state-machine workflows, event-time semantics, and complex windowing.

The trade-off is operational. Flink wants a JVM cluster, a state backend (RocksDB / S3), checkpointing, savepoint management, a schema registry, and so on. For "I want my materialized views to update themselves", that is overkill.

Use Flink if: you have stateful event processing that goes beyond derived tables — state machines, complex CEP, multi-source joins at high throughput.

Use pg_trickle if: you want stream-table semantics and you are already running PostgreSQL.

vs. Debezium + sink (Kafka Connect, etc.)

Debezium captures changes from PostgreSQL and emits them onto Kafka (or another stream). It is only the change-capture half of the problem — you still need a downstream consumer that turns those changes into a derived table.

Use Debezium if: you need to fan changes out to many heterogeneous downstream systems (Elasticsearch, S3, Snowflake, a data lake).

Use pg_trickle if: you want the derived table to live in PostgreSQL itself. You can still expose stream-table changes via downstream publications — and even use Debezium to read those.

vs. ksqlDB

ksqlDB gives you streaming SQL on top of Kafka. Same trade-off as Materialize/RisingWave: another engine, another set of operational concerns.

If your data already lives in Kafka and you want SQL on it, ksqlDB is a fine choice. If your data lives in PostgreSQL, pg_trickle is closer to where it already is.

vs. Snowflake Dynamic Tables

Snowflake Dynamic Tables are auto-refreshing tables inside Snowflake. They occupy almost exactly the same conceptual slot as pg_trickle — but in a different database.

Use whichever matches the database you have.

vs. "cron + REFRESH MATERIALIZED VIEW"

This is what most teams build before they find a real IVM tool. It works, until:

• Refreshes start to overlap.
• A long refresh blocks readers.
• The refresh becomes too expensive to run as often as you'd like.
• A second view depends on the first and you start writing ordering logic.
• A failure leaves stale data and nobody notices.

When that happens, pg_trickle's quick start is ~5 minutes of setup.

See also: Use Cases · Migrating from materialized views · Migrating from pg_ivm · Research and prior art

Glossary

A plain-language reference for the terms used throughout the pg_trickle documentation. If a term isn't here, check the FAQ — and please open an issue so we can add it.

How to use this page. Most pg_trickle pages link the first use of a jargon term back to the matching entry below. You can also search this page directly (the entries are alphabetised within each section).

Core concepts

Stream table

A table whose contents are defined by a SQL query, and that pg_trickle keeps up to date automatically as the underlying data changes. Think of it as a materialized view that maintains itself — without you ever calling REFRESH MATERIALIZED VIEW.

Defining query

The SQL SELECT statement you give to pgtrickle.create_stream_table(). It can use joins, aggregates, CTEs, window functions, and most of standard SQL. The defining query is what pg_trickle differentiates to compute deltas.

Source table

A regular PostgreSQL table that a stream table reads from. Source tables are written to in the normal way (INSERT, UPDATE, DELETE); pg_trickle captures those writes and propagates them downstream.

Base table

Synonym for source table. Used interchangeably in older docs.

Schedule

How often a stream table refreshes. May be a duration ('5s', '10m'), a cron expression ('@hourly', '0 * * * *'), the special value 'CALCULATED' (derived from downstream consumers), or NULL (only refresh when called manually, or for IMMEDIATE mode).

Refresh

A single round of bringing a stream table up to date with its sources. Each refresh either rewrites the whole result (FULL) or applies only the incremental change (DIFFERENTIAL).

Refresh mode

Tells pg_trickle how to refresh. The four modes:

• AUTO — pick the cheapest mode each cycle (the default).
• DIFFERENTIAL — incremental: only changed rows are processed.
• FULL — re-run the entire defining query.
• IMMEDIATE — refresh inside the same transaction as the source DML (no scheduler involved).

Incremental View Maintenance (IVM)

The technique of updating a materialized view by computing only the change induced by recent edits, rather than re-running the whole query. pg_trickle is an IVM engine for PostgreSQL.

Differential

Synonym for "incremental" in the IVM sense. Also the name of the refresh mode that uses incremental computation. Inspired by differential dataflow and the DBSP framework — see also delta query below.

Delta query (ΔQ)

The SQL pg_trickle generates internally to compute the change in a stream table given a change in its inputs. ΔQ is derived automatically from the defining query's operator tree. You can inspect it with pgtrickle.explain_st(name).

Operator tree

The internal representation of your defining query — a tree of nodes like scan, filter, join, aggregate. pg_trickle differentiates this tree operator by operator to derive the delta query.

DAG (directed acyclic graph)

The shape of your stream-table dependencies. If stream table B reads from stream table A, there is an edge A → B. pg_trickle refreshes the DAG in topological order so that downstream tables always see consistent upstream state.

Diamond (in a DAG)

A pattern where two parallel branches both depend on a common ancestor and both feed into a common descendant (A → B → D and A → C → D). pg_trickle refreshes diamonds atomically to prevent the descendant from seeing one branch updated and the other not.

SCC (strongly connected component)

A cycle in the dependency graph — a group of stream tables that all transitively depend on each other. pg_trickle supports cycles only for monotone queries and only when explicitly enabled (pg_trickle.allow_circular). See Circular Dependencies.

Change capture

CDC (Change Data Capture)

The mechanism that records every INSERT, UPDATE, and DELETE on a source table so pg_trickle knows what changed since the last refresh. pg_trickle has two CDC backends — see CDC Modes.

Trigger-based CDC

The default backend. Lightweight AFTER row-level triggers on each source table write a single row to a change buffer per data change. Cost is roughly 2–15 µs per row, paid by the writing transaction.

WAL-based CDC

The optional backend that uses PostgreSQL's logical replication to read changes from the write-ahead log instead of via triggers. Requires wal_level = logical. Adds near-zero write-side cost.

Hybrid CDC

The default behaviour: pg_trickle starts with triggers (which always work), and if wal_level = logical is available, transitions automatically to WAL once the first refresh succeeds. If anything goes wrong, it falls back to triggers.

Change buffer

A small per-source table in the pgtrickle_changes.* schema that holds captured changes between refreshes. Each refresh drains the relevant rows.

Compaction

Collapsing redundant entries in a change buffer — for example an INSERT followed by a matching DELETE cancels out. pg_trickle compacts buffers automatically when refreshes are batched.

Watermark

A timestamp or position published by an external loader that tells pg_trickle "you can safely consider data through here as complete". Downstream refreshes wait until all relevant watermarks are aligned. Used in ETL bootstrap patterns.

Frontier

A set of per-source positions (LSN or logical timestamps) that records where each input was up to at the moment of the last refresh. The next refresh reads from frontier→now. Frontiers are how pg_trickle guarantees correctness across multiple sources.

LSN (Log Sequence Number)

A PostgreSQL identifier for a position in the write-ahead log. Looks like 16/B374D8A0. pg_trickle uses LSNs to record frontiers in WAL CDC mode.

Refresh & scheduling

CALCULATED schedule

The default. Set an explicit refresh interval only on the consumer-facing stream tables (the ones your application queries). Upstream stream tables inherit the tightest cadence among their downstream dependents automatically.

Tier (Hot / Warm / Cold / Frozen)

A coarse refresh cadence bucket used for very large deployments (pg_trickle.tiered_scheduling = on). Hot tables refresh as scheduled; Frozen tables never refresh until manually thawed.

Adaptive fallback

The engine's automatic switch from DIFFERENTIAL to FULL when the change ratio exceeds a threshold (default 50%). It switches back when the rate drops.

Fuse (circuit breaker)

A safety mechanism: a stream table that fails repeatedly is automatically suspended so it cannot block the scheduler. You re-enable it with pgtrickle.reset_fuse(). See Fuse Circuit Breaker.

MERGE

PostgreSQL's MERGE statement, which lets pg_trickle apply a delta (insert / update / delete in one go) to the stream table's storage. Most of a refresh's wall-clock time is spent in MERGE — i.e., in PostgreSQL itself, not in pg_trickle.

Scoped recomputation

A delta-application strategy used for MIN, MAX, and TopK aggregates: re-aggregate just the affected groups (rather than the whole result) by reading only the rows that match the changed keys.

Group-rescan

Similar to scoped recomputation, used for "holistic" aggregates like STRING_AGG, ARRAY_AGG, MODE, PERCENTILE_*.

Predicate pushdown

The optimisation that injects WHERE clauses from the defining query directly into change-buffer scans, so irrelevant changes are filtered out at read time.

Columnar tracking

A capture-side optimisation: CDC records only the columns referenced by the defining query, encoded as a bitmask. Updates that touch only unreferenced columns are skipped entirely.

Background workers

Launcher

The single per-server background worker that scans pg_database every few seconds and spawns a scheduler in every database where pg_trickle is installed.

Scheduler

The per-database background worker that wakes periodically, decides which stream tables are due for refresh, and (in parallel mode) dispatches refresh jobs to a worker pool.

BGW (BackGround Worker)

A PostgreSQL concept — a long-running process spawned by the postmaster. pg_trickle uses BGWs for the launcher, schedulers, and parallel refresh workers.

Parallel refresh

An execution mode (pg_trickle.parallel_refresh_mode = 'on') where independent stream tables in the DAG are refreshed concurrently across a pool of dynamic background workers.

Aggregates

Algebraic aggregate

An aggregate that can be maintained from previous state plus a delta (SUM, COUNT, AVG by tracking sum + count). Cheapest possible IVM.

Semi-algebraic aggregate

An aggregate that can be maintained on inserts cheaply, but on a delete may need to rescan the affected group (MIN, MAX). pg_trickle handles this with scoped recomputation.

Holistic aggregate

An aggregate that has no incremental form (PERCENTILE_*, MODE, STRING_AGG, ARRAY_AGG). pg_trickle re-aggregates the affected groups from source.

Stream-table features

Snapshot

A point-in-time copy of a stream table's contents (v0.27+). Useful for backups, replica bootstrap, or test fixtures. See Snapshots.

Outbox

A stream-table-backed implementation of the transactional outbox pattern: write events in the same transaction as your business data; external systems consume them with at-least-once delivery. See Transactional Outbox.

Inbox

The mirror of the outbox: receive events idempotently from an external system, with stream tables giving you live views of pending work and a dead-letter queue. See Transactional Inbox.

Publication (downstream)

A regular PostgreSQL logical-replication publication automatically created over a stream table's storage. Lets Debezium, Kafka Connect, Spark, etc. subscribe to stream-table changes without an extra pipeline. See Downstream Publications.

Relay

A standalone Rust binary (pg-tide-relay) that bridges outbox/inbox tables with external messaging systems (NATS, Kafka, Redis Streams, SQS, RabbitMQ, webhooks). Extracted to the pg_tide project in v0.46.0.

TopK

Stream tables of the form SELECT … ORDER BY x LIMIT N (optionally with OFFSET M). pg_trickle stores only the top N rows and recomputes them incrementally when the changes affect the leaderboard.

IMMEDIATE mode

A refresh mode that maintains the stream table inside the same transaction as the source DML — no scheduler, no change buffers. Gives read-your-writes consistency at the cost of slightly heavier writes.

Engine internals

DVM (Differential View Maintenance)

The engine inside pg_trickle that turns operator trees into delta queries. The name is used informally; the academic name for the underlying technique is differential dataflow.

DBSP

The academic framework that pg_trickle's differentiation rules are based on. See the DBSP Comparison for the relationship between pg_trickle and the original DBSP runtime.

Bilinear expansion

The expansion of Δ(A ⋈ B) = ΔA ⋈ B + A ⋈ ΔB + ΔA ⋈ ΔB for joins. This is the formal recipe for incrementally maintaining a join. You don't need to know it to use pg_trickle.

Semi-naive evaluation

A classical algorithm for incremental recursive queries (WITH RECURSIVE). pg_trickle uses it in IMMEDIATE and DIFFERENTIAL modes for insert-only recursion.

DRed (Delete-and-Rederive)

A companion algorithm to semi-naive evaluation that handles deletions inside recursive queries. Also used in IMMEDIATE mode.

__pgt_row_id

A hidden column pg_trickle adds to every stream table to give each row a stable identity, even if the defining query has no primary key. You can ignore it in your queries; it is replicated correctly to logical-replication subscribers.

Auto-rewrite

A pipeline of small rewrites pg_trickle applies to your defining query before differentiation — for example expanding DISTINCT ON to ROW_NUMBER(), inlining views, or splitting GROUPING SETS into UNION ALL. See Auto-Rewrite Pipeline.

Operations & infrastructure

GUC (Grand Unified Configuration)

A PostgreSQL configuration variable, set in postgresql.conf, by ALTER SYSTEM, or per-session with SET. All pg_trickle GUCs start with pg_trickle.*. The full list is in Configuration.

Advisory lock

A lightweight lock you ask PostgreSQL to keep on your behalf. pg_trickle uses advisory locks to coordinate refreshes across processes — including across PgBouncer-pooled sessions, which is why pg_trickle is pooler-friendly.

Pooler

Connection pooler such as PgBouncer or pgcat, often deployed in front of PostgreSQL. pg_trickle's background workers connect directly (not through the pooler), so your app's pooler does not interact with refresh activity.

CNPG

CloudNativePG, a Kubernetes operator for PostgreSQL. pg_trickle ships a minimal scratch-based OCI image suitable for CNPG's Image Volume Extensions feature.

pgrx

The Rust framework pg_trickle is built with. Provides safe wrappers around PostgreSQL internals.

wal_level

The PostgreSQL setting that controls how much information the write-ahead log contains. Values: minimal, replica (default), logical. WAL-based CDC requires logical.

Replication slot

A PostgreSQL object that retains WAL until a consumer has read it. WAL CDC mode creates one slot per source table; trigger CDC mode does not.

Acronym key

See also: FAQ · SQL Reference · Architecture · Configuration

Playground

The quickest way to explore pg_trickle is the playground — a pre-configured Docker environment with sample data and stream tables ready to query. No installation, no configuration. One command and you're running.

Quick Start

git clone https://github.com/trickle-labs/pg-trickle.git
cd pg-trickle/playground
docker compose up -d

Then connect:

psql postgresql://postgres:playground@localhost:5432/playground

PostgreSQL 18+ note: The Docker image stores data in a versioned subdirectory (/var/lib/postgresql/18/main). The compose file mounts /var/lib/postgresql (not .../data) — this is intentional.

What's Pre-Loaded

The seed script creates three base tables and five stream tables that cover the most common pg_trickle patterns.

Base Tables

Stream Tables

Exercises

1. Watch an INSERT propagate

-- Current state
SELECT * FROM sales_by_region ORDER BY region;

-- Insert a new order
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (1, 1, 10, CURRENT_DATE);

-- After ~1 s the stream table refreshes
SELECT * FROM sales_by_region ORDER BY region;

2. Inspect pg_trickle internals

-- Overall health
SELECT * FROM pgtrickle.health_check();

-- Status of all stream tables
SELECT name, status, refresh_mode, staleness
FROM pgtrickle.pgt_status()
ORDER BY name;

-- Recent refresh activity
SELECT start_time, stream_table, action, status, duration_ms
FROM pgtrickle.refresh_timeline(10);

-- Delta SQL for a stream table
SELECT pgtrickle.explain_st('sales_by_region');

-- Change buffer sizes
SELECT * FROM pgtrickle.change_buffer_sizes();

3. Update and Delete

-- Update a product price
UPDATE products SET price = 99.99 WHERE name = 'Widget';

-- customer_lifetime_value re-calculates
SELECT * FROM customer_lifetime_value ORDER BY total_revenue DESC LIMIT 5;

-- Delete a customer's orders
DELETE FROM orders WHERE customer_id = 3;

-- Stream tables reflect the removal
SELECT * FROM sales_by_region ORDER BY region;

4. Create your own stream table

SELECT pgtrickle.create_stream_table(
    name     => 'my_experiment',
    query    => $$
        SELECT p.category,
               COUNT(DISTINCT o.customer_id) AS unique_buyers,
               SUM(o.quantity)               AS total_units
        FROM orders o
        JOIN products p ON p.id = o.product_id
        GROUP BY p.category
        HAVING SUM(o.quantity) > 5
    $$,
    schedule => '2s'
);

SELECT * FROM my_experiment;

Tear Down

docker compose down -v

The -v flag removes the data volume. Omit it if you want to keep your changes.

Next Steps

• Getting Started Guide — full tutorial with an org-chart example
• SQL Reference — all functions and parameters
• Best-Practice Patterns — production-ready patterns

Real-time Demo

This demo shows pg_trickle doing real work: a continuous stream of events flows into PostgreSQL, and a DAG of stream tables keeps a live view of that data up to date — automatically, incrementally, and within seconds.

Three scenarios are available via the DEMO_SCENARIO environment variable:

Each scenario includes two purpose-built differential efficiency showcases: stream tables with sub-1.0 change ratios that demonstrate when DIFFERENTIAL mode is clearly the right choice.

It is the fastest way to see how stream tables, differential refresh, and DAG-aware scheduling work together on data you can watch moving.

Quick Start

cd demo

# E-commerce analytics (default)
docker compose up --build

# Fraud detection
DEMO_SCENARIO=fraud docker compose up --build

# Financial risk analytics (10-level DAG with deep calculated dependencies)
DEMO_SCENARIO=finance docker compose up --build

Open http://localhost:8080 — the dashboard refreshes every 2 seconds.

To stop and remove all data:

docker compose down -v

Switching scenarios requires removing the old data volume:

docker compose down -v
DEMO_SCENARIO=fraud docker compose up

Or use any of the three: fraud, ecommerce, finance.

What the Demo Does

Three Docker services start together:

The DEMO_SCENARIO variable controls which SQL files are loaded on startup and which generator/dashboard module is activated. Both scenarios share the same Docker Compose services.

Scenario: fraud

The demo models the data pipeline a financial institution might build to spot suspicious activity as it happens, not hours later in a batch job.

Source Data

Four regular PostgreSQL tables hold the reference data:

transactions is the only table that grows continuously. merchant_risk_tier changes occasionally (about one row per minute). Everything else is static.

Normal vs. Suspicious Traffic

The generator creates two kinds of transactions:

Normal traffic — a random user buys something from a random merchant at a plausible amount for that merchant's category. Inserted at roughly one per second.

Suspicious burst — every ~45 seconds, the generator picks one user and fires 6–14 rapid transactions (0.15–0.45 s apart) at Crypto or Gambling merchants, with amounts that escalate with each successive transaction. This pattern is designed to cross the risk thresholds and light up the HIGH-risk column on the dashboard.

The DAG of Stream Tables (fraud)

All nine stream tables are defined in demo/postgres/fraud/02_stream_tables.sql.

  Base tables             Layer 1 — Silver           Layer 2 — Gold              Layer 3 — Platinum
  ────────────            ──────────────────────     ─────────────────────       ──────────────────────

  ┌──────────┐            ┌──────────────────┐
  │  users   │───────────►│  user_velocity   │──────────────────────────────────►┌──────────────┐
  └──────────┘            │  DIFFERENTIAL 1s │                                   │ country_risk │
                          └──────┬───────────┘                                   │  DIFF, calc  │
                                 │                                                └──────────────┘
  ┌──────────────┐               │  ┌──────────────────┐
  │ transactions │───────────────┼─►│  merchant_stats  │
  │  (stream)    │               │  │  DIFFERENTIAL 1s │
  └──────────────┘               │  └──────┬───────────┘
          │                      │         │
          │          ┌───────────┴─────────┘ ← DIAMOND DEPENDENCY
          │          │
          │          ▼                                                             ┌─────────────────┐
          │     ┌────────────────────┐                                             │  alert_summary  │
          │     │    risk_scores     │────────────────────────────────────────────►│   DIFF, calc    │
          │     │   FULL, calc       │                                             └─────────────────┘
          │     └────────────────────┘
          │                                                                         ┌───────────────────────┐
          │                                                                         │  top_risky_merchants  │
          └─────────────────────────────────────────────────────────────────────►  │   DIFF, calc          │
                                                                                    └───────────┬───────────┘
  ┌──────────┐            ┌──────────────────┐                                                 │
  │merchants │───────────►│ category_volume  │          ┌──────────────────────────────────────▼──────┐
  └──────────┘            │  DIFFERENTIAL 1s │          │       top_10_risky_merchants                │
                          └──────────────────┘          │  DIFFERENTIAL 5s  ← SHOWCASE #2             │
                                                         │  change ratio ≈ 0.25 (LIMIT 10)             │
  ┌───────────────────┐   ┌──────────────────────┐      └─────────────────────────────────────────────┘
  │ merchant_risk_tier│──►│ merchant_tier_stats  │  ← SHOWCASE #1
  │ (slowly-changing) │   │   DIFFERENTIAL 5s    │     change ratio ≈ 0.07
  └───────────────────┘   │                      │
  ┌──────────┐            │                      │
  │merchants │───────────►│                      │
  └──────────┘            └──────────────────────┘

Layer 1 — Silver: Direct Aggregates

These three stream tables each read directly from the base tables and refresh every second using DIFFERENTIAL mode. pg_trickle calculates only what changed since the last refresh — if five new transactions arrived, it adjusts exactly the five affected aggregate buckets rather than recomputing the full table.

user_velocity — per-user transaction statistics

For each of the 30 users, keeps a running count of transactions, total spend, average transaction amount, and how many distinct merchants they have visited. This is the core input for detecting users who are suddenly transacting far more than usual.

SELECT u.id, u.name, u.country,
       COUNT(t.id)               AS txn_count,
       SUM(t.amount)             AS total_spent,
       ROUND(AVG(t.amount), 2)   AS avg_txn_amount,
       COUNT(DISTINCT t.merchant_id) AS unique_merchants
FROM users u
LEFT JOIN transactions t ON t.user_id = u.id
GROUP BY u.id, u.name, u.country

merchant_stats — per-merchant baseline

Tracks how many transactions each merchant typically sees and at what amounts. A transaction that is 3× the merchant's own average is more suspicious than one that is 3× the user's average — this table supplies that context.

category_volume — industry-level view

Groups by merchant category (Crypto, Gambling, Retail, etc.) so the dashboard can show which sectors are hot at any moment. Refreshes every second and uses DIFFERENTIAL: a new transaction in Electronics updates only the Electronics row.

Layer 2 — Gold: Derived Metrics

These stream tables read from the Layer 1 stream tables (stream tables reading stream tables). pg_trickle's scheduler refreshes Layer 1 first, then triggers Layer 2 automatically — you never schedule this yourself.

risk_scores — the diamond convergence node

This is the most interesting table in the DAG. It joins:

• transactions — the raw event
• user_velocity — that user's accumulated behaviour (Layer 1)
• merchant_stats — that merchant's baseline (Layer 1)

Because transactions feeds both user_velocity and merchant_stats independently, and risk_scores depends on both, this creates a classic diamond dependency:

transactions ──→ user_velocity  ──┐
                                   ├──→ risk_scores
transactions ──→ merchant_stats ──┘

pg_trickle detects this diamond and schedules both Layer 1 nodes before triggering the Layer 2 refresh, so risk_scores always sees fresh context.

The risk scoring logic lives entirely in SQL:

CASE
    WHEN uv.txn_count > 20
         AND t.amount > 3 * uv.avg_txn_amount
        THEN 'HIGH'
    WHEN uv.txn_count > 10
         OR  t.amount > 2 * uv.avg_txn_amount
        THEN 'MEDIUM'
    ELSE 'LOW'
END AS risk_level

risk_scores uses refresh_mode => 'FULL' because it joins three sources (including two stream tables) in a way that requires re-evaluating all rows to maintain correctness. FULL mode is the right choice here — it is still fast because it is triggered only when an upstream actually changes.

country_risk — geographic rollup

Reads user_velocity (Layer 1) and aggregates by country. Pure DIFFERENTIAL because it is a simple GROUP BY country over the Silver layer.

Layer 3 — Platinum: Executive Roll-Ups

These stream tables read from risk_scores (Layer 2) and are defined with schedule => 'calculated', meaning pg_trickle fires them automatically whenever their upstream changes.

alert_summary — the primary KPI

Counts and totals by risk level (LOW / MEDIUM / HIGH). This is what drives the four big counters at the top of the dashboard. Because it is a simple aggregate over risk_scores, it uses DIFFERENTIAL mode and updates only the affected risk-level row on each cycle.

top_risky_merchants — merchant triage list

Groups risk_scores by merchant name and category, counting how many HIGH and MEDIUM transactions each merchant has seen, plus a risk-rate percentage. Operationally this is where a fraud team would start when deciding which merchants to review or block.

Differential Efficiency Showcases

Two additional stream tables sit outside the main fraud pipeline. Their purpose is to demonstrate that DIFFERENTIAL mode can achieve a meaningfully sub-1.0 change ratio when the output cardinality is constrained.

merchant_tier_stats — Showcase #1: slowly-changing lookup source

Joins merchants (static) with merchant_risk_tier (a 15-row lookup that the generator updates one row per ~30 cycles). Because no fast-growing table is in the query, only the one rotated merchant's row changes each cycle:

• Change ratio ≈ 1/15 ≈ 0.07
• Refresh Mode Advisor recommendation: ✓ KEEP DIFFERENTIAL
• Schedule: 5 s (independent of the main DAG)

This is the counterpoint to risk_scores. risk_scores correctly uses FULL because its change ratio is ~1.0; merchant_tier_stats correctly uses DIFFERENTIAL because its change ratio is ~0.07. Seeing both on the same dashboard makes the advisor's logic concrete.

top_10_risky_merchants — Showcase #2: fixed-cardinality output

Reads top_risky_merchants (Layer 3) and applies LIMIT 10. Even though the upstream changes heavily every cycle, only the merchants whose rank crosses the top-10 boundary produce a net change in the output. Typically 2–3 merchants enter or leave the top 10 per refresh cycle:

• Change ratio ≈ 0.2–0.3
• Refresh Mode Advisor recommendation: ✓ KEEP DIFFERENTIAL
• Schedule: 5 s

The Dashboard

Open http://localhost:8080. Panels update every 2 seconds.

KPI Row

Four counters: total transactions, LOW / MEDIUM / HIGH counts. Below them, a proportional colour bar that makes the risk mix visible at a glance.

Recent Alerts

A live table of the most recent HIGH and MEDIUM transactions — transaction ID, user name, merchant, category, amount, and risk badge. Rows turn red for HIGH and amber for MEDIUM. During a burst you will see this fill with HIGH rows as the generator fires rapid transactions.

Merchant Risk Leaderboard

Sorted by HIGH-risk count descending. Crypto and Gambling merchants will typically appear at the top because the generator targets them during bursts.

User Velocity, Country Overview, Category Volume

Three side-by-side tables driven by the Layer 1 stream tables. These are the most-refreshed tables in the DAG (every second); watching user velocity change in real time illustrates why DIFFERENTIAL mode matters — only the affected rows move.

Merchant Tier Stats and Tiers

Two panels driven by merchant_tier_stats. The left panel shows the full 15-row output (merchant ID, name, category, current tier, risk score, and when the tier last changed). The right panel shows a compact tier-only view. Tiers are colour-coded: HIGH = red, ELEVATED = amber, STANDARD = green. Tiers rotate visibly every ~30 generator cycles (roughly once per minute).

Top 10 Risky Merchants Leaderboard

A live leaderboard driven by top_10_risky_merchants. Shows rank, merchant name, category, total transactions, HIGH and MEDIUM risk counts, and a risk-rate percentage. The percentage column is coloured green (<25%), amber (25–49%), or red (≥50%). Watch the rankings shift as the generator's burst patterns accumulate.

Stream Table Status

A compact status panel showing each stream table's refresh mode, schedule, and whether it is populated. This reads from pgtrickle.pgt_status().

DAG Topology

A collapsible ASCII diagram showing the full dependency graph. Useful as a reference while exploring the database directly.

Exploring the Database (fraud)

Connect directly to inspect the stream tables and pg_trickle internals:

docker compose exec postgres psql -U demo -d fraud_demo

Check all stream table status:

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

Inspect the DAG dependency graph (full tree):

SELECT tree_line FROM pgtrickle.dependency_tree()
ORDER BY tree_line;

See the most recent refresh history:

SELECT st.pgt_name, rh.action, rh.status, 
       (rh.end_time - rh.start_time) AS duration, rh.start_time
FROM pgtrickle.pgt_refresh_history rh
JOIN pgtrickle.pgt_stream_tables st ON st.pgt_id = rh.pgt_id
ORDER BY rh.start_time DESC
LIMIT 20;

Spot the diamond groups (stream tables with shared sources):

SELECT group_id, member_name, is_convergence, schedule_policy
FROM pgtrickle.diamond_groups()
ORDER BY group_id, is_convergence DESC;

Watch a HIGH-risk alert appear:

-- In one terminal: watch for new HIGH rows
SELECT txn_id, user_name, merchant_name, amount
FROM risk_scores
WHERE risk_level = 'HIGH'
ORDER BY txn_id DESC
LIMIT 5;

-- Wait ~45 seconds for the next burst, then run the query again.

Force a manual refresh:

SELECT pgtrickle.refresh_stream_table('risk_scores');

How the Files Are Organised

demo/
├── docker-compose.yml          # Service definitions; DEMO_SCENARIO selects the scenario
├── README.md                   # Quick start + scenario descriptions
│
├── postgres/
│   ├── fraud/
│   │   ├── 01_schema.sql       # Base tables + seed data (30 users, 15 merchants,
│   │   │                       # 40 initial transactions, merchant_risk_tier)
│   │   └── 02_stream_tables.sql# All 9 stream table definitions (Layers 1–3 + showcases)
│   ├── ecommerce/
│   │   ├── 01_schema.sql       # Base tables + seed data (customers, products,
│   │   │                       # categories, orders, product_catalog)
│   │   └── 02_stream_tables.sql# All 6 stream table definitions (Layers 1–3 + showcases)
│   └── finance/
│       ├── 01_schema.sql       # Base tables + seed data (30 instruments, 50 accounts,
│       │                       # 5 portfolios, 8 sectors, seed trades/prices)
│       └── 02_stream_tables.sql# All 10 stream table definitions (L1–L10 cascade)
│
├── generator/
│   ├── Dockerfile
│   ├── requirements.txt        # psycopg2-binary only
│   ├── generate.py             # Scenario dispatcher; reads DEMO_SCENARIO
│   └── scenarios/
│       ├── fraud.py            # Transaction generator (normal + burst mode)
│       ├── ecommerce.py        # Order generator (normal + flash sale mode)
│       └── finance.py          # Trade + price tick generator (normal + algo burst mode)
│
└── dashboard/
    ├── Dockerfile
    ├── requirements.txt        # flask + psycopg2-binary
    ├── app.py                  # Scenario dispatcher: / → HTML, /api/data → JSON,
    │                           #   /api/internals → stream table metadata (shared)
    └── scenarios/
        ├── fraud.py            # Fraud HTML, DAG diagram, and data queries
        ├── ecommerce.py        # E-commerce HTML, DAG diagram, and data queries
        └── finance.py          # Finance HTML, DAG diagram, and data queries

Why These Design Choices

Why seven stream tables instead of one big query?

Splitting the computation across three layers means each layer does a smaller, cheaper differential computation. A single-query approach that joins users, transactions, and merchants directly into risk_scores would force a FULL refresh every cycle because no single source table captures all changes. With the layered approach:

• L1 tables catch source changes differentially (they are cheap to maintain)
• L2/L3 tables read from already-aggregated L1 data (smaller join inputs)
• The scheduler only re-runs a layer when its inputs actually changed

Why is risk_scores FULL and not DIFFERENTIAL?

risk_scores is a 1:1 projection of transactions — one output row per input transaction, with enrichment from the L1 stream tables. Since transactions is append-only, the change ratio is ~1.0: every refresh adds roughly as many new rows as existed before, making DIFFERENTIAL no more efficient than FULL. Additionally, FULL mode simplifies the refresh logic (avoiding complex multi-source delta algebra) while remaining fast because the table is small and updates are infrequent (only triggered when L1 feeds new data).

pg_trickle's diagnostic system (recommend_refresh_mode()) confirms this choice: it observed an avg change ratio of 1.0 and recommended to KEEP FULL.

The L3 tables (alert_summary, top_risky_merchants) are purely single-upstream aggregates over risk_scores and therefore support DIFFERENTIAL efficiently — the change ratio there is much lower.

Why schedule => 'calculated' for L2 and L3?

calculated means "refresh whenever an upstream stream table has new data." This is the right choice for derived layers: there is no point refreshing risk_scores if neither user_velocity nor merchant_stats has changed, and there is no point waiting for a clock interval when they have.

Why triggers and not logical replication?

The demo uses the default CDC mode (row-level AFTER triggers). This works with any PostgreSQL 18 installation out of the box — no replication slot configuration, no wal_level = logical requirement. For production deployments with very high write throughput, WAL-based CDC is more efficient. pg_trickle can switch modes transparently; see CONFIGURATION.md for details.

Empirical optimization: FULL vs DIFFERENTIAL by change ratio

The demo illustrates a practical rule of thumb: when a stream table's change ratio (fraction of output rows that are inserted or deleted per refresh cycle) is high (>0.5), FULL mode is often faster than DIFFERENTIAL because the delta overhead dominates the benefit. Use pgtrickle.recommend_refresh_mode(table_name) to check — it analyzes actual refresh history and recommends the best mode with confidence scores.

The Refresh Mode Advisor computes change ratio as:

change_ratio = (rows_inserted + rows_deleted) / max(reltuples, 1)

where reltuples is the stream table's current row count from pg_class. This gives a meaningful fraction: 0.07 means 7% of output rows changed last cycle; 1.0 means the entire output turned over.

The two showcase tables make this concrete:

Scenario: ecommerce (default)

The e-commerce scenario models a real-time online store analytics pipeline with orders streaming in continuously.

cd demo
DEMO_SCENARIO=ecommerce docker compose up --build

Source Data

orders is the only table that grows continuously. product_catalog changes occasionally (about one row per minute). Everything else is static.

Normal vs. Flash Sale Traffic

Normal orders — a random customer orders a random product in quantity 1–2 at roughly the catalog price (±15%). Inserted at roughly one per second.

Flash sale burst — every ~45 seconds, the generator picks one category and fires 8–18 rapid orders (0.10–0.35 s apart) at a 70–90% discount. This creates a visible revenue spike in the Category Revenue panel.

The DAG of Stream Tables (ecommerce)

All six stream tables are defined in demo/postgres/ecommerce/02_stream_tables.sql.

  Base tables          Layer 1 — Silver           Layer 2 — Gold         Layer 3 — Platinum
  ────────────         ──────────────────────      ─────────────────────  ──────────────────────

  ┌────────────┐       ┌──────────────────┐
  │ customers  │──────►│ customer_stats   │──────────────────────────────►┌──────────────────┐
  └────────────┘       │  DIFFERENTIAL 1s │                               │ country_revenue  │
                       └──────────────────┘                               │  DIFF, calc      │
                                │                                          └──────────────────┘
  ┌────────────┐                │
  │  orders    │────────────────┘
  │ (stream)   │────────────────────────────────►┌────────────────┐
  └────────────┘       ┌────────────────┐         │ product_sales  │
  ┌────────────┐       │category_revenue│         │ DIFFERENTIAL   │
  │  products  │──────►│ DIFFERENTIAL   │         │ 1s             │
  │ categories │──────►│ 1s             │         └────────────────┘
  └─────┬──────┘       └────────────────┘
        │
  ┌─────▼──────────────┐   ┌──────────────────────┐
  │  product_catalog   │──►│ catalog_price_impact │  ← DIFFERENTIAL SHOWCASE #1
  │  (slowly-changing) │   │   DIFFERENTIAL 5s    │    change ratio ≈ 0.07
  └────────────────────┘   └──────────────────────┘

  ┌──────────────────┐   ┌──────────────────┐
  │  customer_stats  │──►│  top_10_customers│  ← DIFFERENTIAL SHOWCASE #2
  │  DIFFERENTIAL 1s │   │  DIFFERENTIAL    │    change ratio ≈ 0.1–0.2
  └──────────────────┘   │  calc, LIMIT 10  │
                         └──────────────────┘

Stream Tables (ecommerce)

Differential efficiency showcases (ecommerce)

catalog_price_impact — Showcase #1: slowly-changing price catalog

Joins products (static) with product_catalog (15 rows, one repriced per ~30 cycles). Only the repriced product's row changes each cycle:

• Change ratio ≈ 1/15 ≈ 0.07
• Refresh Mode Advisor recommendation: ✓ KEEP DIFFERENTIAL
• The price_delta and pct_change columns highlight repriced products in real time.

top_10_customers — Showcase #2: fixed-cardinality leaderboard

Reads customer_stats (all 30 customers) and applies LIMIT 10. Only rank boundary crossings produce net output changes:

• Change ratio ≈ 0.1–0.2
• Refresh Mode Advisor recommendation: ✓ KEEP DIFFERENTIAL

Exploring the Database (ecommerce)

docker compose exec postgres psql -U demo -d ecommerce_demo

-- See category revenue live
SELECT category, order_count, units_sold, revenue
FROM   category_revenue ORDER BY revenue DESC;

-- Top 10 customers leaderboard
SELECT * FROM top_10_customers;

-- Price changes vs. base price
SELECT product_name, base_price, current_price, pct_change
FROM   catalog_price_impact ORDER BY ABS(pct_change) DESC;

-- Refresh efficiency comparison across all stream tables
SELECT pgt_name, avg_diff_ms, diff_speedup, avg_change_ratio
FROM   pgtrickle.refresh_efficiency() ORDER BY pgt_name;

Scenario: finance

The finance scenario models a real-time financial risk analytics pipeline demonstrating a 10-level deep DAG where only the leaf tables have fixed schedules, and all downstream layers cascade with schedule => 'calculated'. This showcases pg_trickle's strength in maintaining deep computation graphs where derived data automatically flows upward through the layers.

cd demo
DEMO_SCENARIO=finance docker compose up --build

Source Data

trades is append-only; market_prices updates continuously (one instrument per generator cycle). Everything else is static reference data.

Real-Time Market Activity

Normal trading — the generator randomly selects accounts and instruments, inserting buy/sell trades at current market prices (~1 trade per second). Buy quantities are positive; sell quantities are negative. This creates a natural, continuous flow of position changes.

Algo bursts — every ~60 seconds, the generator triggers an algorithmic burst (0.05–0.20s between trades, 8–20 trades) concentrated on a few instruments, simulating realistic high-frequency trading patterns that stress-test the differential calculation pipeline.

The DAG of Stream Tables (finance)

All 10 stream tables are defined in demo/postgres/finance/02_stream_tables.sql.

This is the deepest DAG in all three scenarios — a 10-level cascade where each level reads only from the previous level (plus static reference data), creating a linear dependency chain. The two leaf tables (price_snapshot at 2s and net_positions at 1s) are the only ones with fixed schedules; all nine downstream layers use schedule => 'calculated' to propagate changes automatically.

  Base tables          Layer 1          Layer 2          ...         Layer 10
  ────────────         ────────           ────────                   ─────────

  ┌──────────────┐     ┌──────────────────────┐
  │market_prices │────►│  price_snapshot      │
  │(stream, 2s)  │     │  DIFFERENTIAL 2s     │
  └──────────────┘     └─────────┬────────────┘
                                 │
  ┌──────────────┐               │     ┌────────────────┐
  │   trades     │──────────┐    └────►│position_values │  L2 (calculated)
  │(stream, 1s)  │          │          │ DIFFERENTIAL   │
  └──────────────┘          │          └─────────┬──────┘
         │                  │                    │
         │                  ▼                    ▼
         │          ┌────────────────┐   ┌───────────────┐
         │          │net_positions   │   │ account_pnl   │  L3 (calculated)
         │          │DIFFERENTIAL 1s │   │ DIFFERENTIAL  │
         │          └────────────────┘   └───────┬───────┘
         │                                       │
         │                  ┌──────────────────────┤
         │                  │                      │
         │                  ▼                      ▼
         └─────────────────►┌────────────────────────────────┐
                            │ portfolio_pnl, sector_exposure │  L4–L5 (calculated)
                            │ DIFFERENTIAL                   │
                            └────────────────┬───────────────┘
                                             │
                                             ▼
                            ┌────────────────────────────┐
                            │  var_contributions         │  L6 (calculated)
                            │  account_var, portfolio_var│  L7–L8 (calculated)
                            │  DIFFERENTIAL throughout   │
                            └────────────────┬───────────┘
                                             │
                                             ▼
                            ┌────────────────────────────┐
                            │ regulatory_capital         │  L9 (calculated)
                            │ DIFFERENTIAL               │
                            └────────────────┬───────────┘
                                             │
                                             ▼
                            ┌────────────────────────────┐
                            │ breach_dashboard           │  L10 (calculated)
                            │ DIFFERENTIAL, LIMIT 10     │  Fixed cardinality
                            └────────────────────────────┘

Stream Tables (finance)

Why This DAG Demonstrates Differential Efficiency

The finance scenario is built specifically to show how DIFFERENTIAL mode excels in deep DAGs:

1. High cardinality at L1 — The 30 × 50 instrument-account combinations yield up to 1,500 potential positions. A price tick affects ~30 positions (change ratio ≈ 0.02).

2. Compressing cardinality downstream — As data aggregates up the layers (L2 → L3 → L4 ...), the output cardinality shrinks. By L5 (sector exposure), there are only 8 rows max. By L10 (top 10 portfolios), there are ≤10 rows.

3. Sub-1.0 change ratios throughout — Because each layer reads only from the previous layer and applies aggregation or filtering:

   • L2 (position_values): change ratio ≈ 0.02 (only changed positions)
   • L5 (sector_exposure): change ratio ~1 row per tick (typically only 1–2 sectors affected)
   • L10 (breach_dashboard): change ratio ≈ 0–0.1 (typically 0–1 row changes per cycle; fixed cardinality means rank shifts are rare)

4. All-DIFFERENTIAL cascade — Unlike the fraud scenario (which uses one FULL table), finance uses DIFFERENTIAL throughout. This is valid because each layer is a simple aggregate or filtered view of the previous layer (no complex diamond joins with multiple independent sources).

The Dashboard

Open http://localhost:8080. Panels update every 2 seconds.

Key metrics:

• Top KPIs — Total positions tracked, total book exposure (USD), portfolio P&L, aggregate portfolio VaR
• Breach Dashboard — Top 10 portfolios by capital utilization ratio (regulatory capital used / capital limit). Highlights portfolios approaching regulatory constraints.
• Sector Exposure — Per-sector breakdown of position count and net exposure.
• Portfolio Metrics — Per-portfolio P&L and 95% VaR.
• Market Snapshot — Current bid/ask/mid prices for all 30 instruments with sector labels.
• Stream Table Status — Refresh mode, schedule, and population status for all 10 tables.
• DAG Topology — ASCII diagram showing the 10-level cascade and dependency flow.

Exploring the Database (finance)

docker compose exec postgres psql -U demo -d finance_demo

-- See all stream table status
SELECT pgt_name, refresh_mode, schedule, is_populated
FROM   pgtrickle.pgt_status()
ORDER BY pgt_id;

-- View the full 10-level dependency graph
SELECT tree_line FROM pgtrickle.dependency_tree()
ORDER BY tree_line;

-- Current market snapshot (L1)
SELECT symbol, sector, bid, ask, mid
FROM   price_snapshot
ORDER BY symbol;

-- Net positions per account-instrument (L1)
SELECT account_id, instrument_symbol, net_quantity, position_value
FROM   net_positions
WHERE  net_quantity != 0
ORDER BY account_id, instrument_symbol;

-- Top-10 portfolio risk ranking (L10)
SELECT portfolio_name, capital_required, capital_limit, utilization_ratio
FROM   breach_dashboard
ORDER BY utilization_ratio DESC;

-- Watch differential efficiency in action
-- This shows very low change ratios at the top levels (L1) and even lower at L10
SELECT pgt_name, avg_change_ratio, avg_diff_ms, diff_speedup
FROM   pgtrickle.refresh_efficiency()
ORDER BY pgt_id;

Differential Efficiency at Work

Open a terminal and watch the refresh history:

docker compose exec postgres psql -U demo -d finance_demo -c "
SELECT 
  st.pgt_name,
  rh.action,
  (rh.end_time - rh.start_time)::int8 AS duration_ms,
  rh.start_time
FROM pgtrickle.pgt_refresh_history rh
JOIN pgtrickle.pgt_stream_tables st ON st.pgt_id = rh.pgt_id
WHERE rh.start_time > now() - interval '10 seconds'
ORDER BY rh.start_time DESC
LIMIT 50;
" --watch

5-Minute Quickstart

The shortest possible introduction to pg_trickle. By the end of this page you will have created a self-maintaining table, watched it update in real time, and dropped it again — without leaving psql.

Prefer to see it first? Run the playground (cd playground && docker compose up -d) for a pre-loaded environment, or pull the prebuilt image:

docker run --rm -e POSTGRES_PASSWORD=secret -p 5432:5432 \
  ghcr.io/trickle-labs/pg_trickle:latest

Then connect with psql postgres://postgres:secret@localhost:5432/postgres and skip to Step 2 below.

Step 1 — Install the extension

If you already have a PostgreSQL 18 server with pg_trickle installed (via the playground, the Docker image, or a manual install — see Installation for full options), skip this step.

Otherwise, the shortest path on a developer machine is the prebuilt Docker image — one command, no configuration:

docker run --rm -e POSTGRES_PASSWORD=secret -p 5432:5432 \
  ghcr.io/trickle-labs/pg_trickle:latest

Connect with psql:

psql postgres://postgres:secret@localhost:5432/postgres

Step 2 — Enable the extension

CREATE EXTENSION IF NOT EXISTS pg_trickle;

That's all the configuration you need. The extension auto-discovers every database where it's installed and starts a per-database scheduler.

Step 3 — Create a source table

CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    region  TEXT     NOT NULL,
    amount  NUMERIC  NOT NULL
);

INSERT INTO orders (region, amount) VALUES
    ('US',   100),
    ('EU',   200),
    ('US',   300),
    ('APAC',  50);

This is a perfectly ordinary table. You will write to it the normal way.

Step 4 — Create a stream table

SELECT pgtrickle.create_stream_table(
    name     => 'revenue_by_region',
    query    => $$
        SELECT region,
               SUM(amount) AS total,
               COUNT(*)    AS order_count
        FROM orders
        GROUP BY region
    $$,
    schedule => '1s'
);

What just happened:

1. pg_trickle parsed your query and built an internal operator tree.
2. It created a new table revenue_by_region with the right columns.
3. It installed lightweight AFTER triggers on orders to capture changes.
4. It ran an initial full refresh, populating the new table.
5. It registered a 1-second refresh schedule.

Query the stream table — it's already populated:

SELECT * FROM revenue_by_region ORDER BY region;

 region | total | order_count
--------+-------+-------------
 APAC   |    50 |           1
 EU     |   200 |           1
 US     |   400 |           2

Step 5 — Watch it update

Insert a new order:

INSERT INTO orders (region, amount) VALUES ('US', 999);

Wait one second (or call SELECT pgtrickle.refresh_stream_table('revenue_by_region') to refresh immediately):

SELECT * FROM revenue_by_region WHERE region = 'US';

 region | total | order_count
--------+-------+-------------
 US     |  1399 |           3

Only the US group was recomputed — the other regions were not touched at all. That is differential refresh in action.

Step 6 — Look around

A few useful built-ins worth knowing about right away:

-- Status of all stream tables in this database
SELECT * FROM pgtrickle.pgt_status();

-- A one-shot health triage (returns rows only when something is wrong)
SELECT * FROM pgtrickle.health_check() WHERE severity != 'OK';

-- See what delta SQL pg_trickle would run on the next refresh
SELECT pgtrickle.explain_st('revenue_by_region');

Step 7 — Clean up

SELECT pgtrickle.drop_stream_table('revenue_by_region');
DROP TABLE orders;

This removes the stream table, its catalog entries, and the CDC triggers on orders.

Where to go next

Getting Started with pg_trickle

What is pg_trickle?

pg_trickle adds stream tables to PostgreSQL — tables that are defined by a SQL query and kept automatically up to date as the underlying data changes. Think of them as materialized views that refresh themselves, but smarter: instead of re-running the entire query on every refresh, pg_trickle uses Incremental View Maintenance (IVM) to process only the rows that changed.

Traditional materialized views force a choice: either re-run the full query (expensive) or accept stale data. pg_trickle eliminates this trade-off. When you insert a single row into a million-row table, pg_trickle computes the effect of that one row on the query result — it doesn't touch the other 999,999.

How data flows

The key concept is that data flows downstream automatically — from your base tables through any chain of stream tables, without you writing a single line of orchestration code:

  You write to base tables
         │
         ▼
  ┌─────────────┐   triggers (or WAL)   ┌─────────────────────┐
  │ Base Tables │ ─────────────────────▶ │   Change Buffers    │
  │ (you write) │                        │ (pgtrickle_changes.*) │
  └─────────────┘                        └──────────┬──────────┘
                                                     │
                                           delta query (ΔQ) on refresh
                                                     │
                                                     ▼
  ┌──────────────────────────────────────────────────────────────┐
  │  Stream Table A  ◀── depends on base tables                  │
  └──────────────────────────┬───────────────────────────────────┘
                             │  change captured, buffer written
                             ▼
  ┌──────────────────────────────────────────────────────────────┐
  │  Stream Table B  ◀── depends on Stream Table A               │
  └──────────────────────────────────────────────────────────────┘

One write to a base table can ripple through an entire DAG of stream tables — each layer refreshed in the correct topological order, each doing only the work proportional to what actually changed.

1. You write to your base tables normally — INSERT, UPDATE, DELETE
2. Lightweight AFTER row-level triggers capture each change into a buffer, atomically in the same transaction. No polling, no logical replication slots required by default.
3. On each refresh cycle, pg_trickle derives a delta query (ΔQ) that reads only the buffered changes since the last refresh frontier
4. The delta is merged into the stream table — only the affected rows are written
5. If other stream tables depend on this one, they are scheduled next (topological order)
6. Optionally: once wal_level = logical is available and the first refresh succeeds, pg_trickle automatically transitions from triggers to WAL-based CDC (near-zero write-path overhead compared to ~2–15 μs for triggers). The transition is seamless and transparent.

This tutorial walks through a concrete org-chart example so you can see this flow end to end, including a chain of stream tables that propagates changes automatically.

Prerequisites

• PostgreSQL 18.x with pg_trickle installed (see Installation)
• shared_preload_libraries = 'pg_trickle' in postgresql.conf
• max_worker_processes raised to at least 32 (see Installation); the PostgreSQL default of 8 is often exhausted if you have several databases, causing stream tables to silently stop refreshing
• psql or any SQL client

Deploying to production? See the Pre-Deployment Checklist for a complete list of requirements, pooler compatibility, and recommended GUC values.

Playground: The fastest way to experiment is the playground — a Docker Compose environment with sample tables and stream tables pre-loaded. cd playground && docker compose up -d and you're running.

Quick start with Docker: Pull the pre-built GHCR image — PostgreSQL 18.3 + pg_trickle ready to run, no configuration needed:

docker run --rm -e POSTGRES_PASSWORD=secret -p 5432:5432 ghcr.io/trickle-labs/pg_trickle:latest

All GUC defaults (wal_level, shared_preload_libraries, scheduler settings) are pre-configured. See Installation for tag details and volume mounting.

Security — RLS bypass: The pg_trickle background worker executes refresh queries with SET LOCAL row_security = off. This mirrors the behaviour of PostgreSQL's own REFRESH MATERIALIZED VIEW, which also bypasses Row-Level Security. As a result, stream table output is always the full, unfiltered result set regardless of any RLS policies defined on source tables. Applications must not rely on RLS to restrict what data a stream table exposes — use view filters, column masking, or a separate per-role view on top of the stream table instead. See PRE_DEPLOYMENT.md for the full security checklist.

Connect to the database you want to use and enable the extension:

CREATE EXTENSION pg_trickle;

No additional configuration is needed. pg_trickle automatically discovers all databases on the server and starts a scheduler for each one where the extension is installed.

Chapter 1: Hello World — Your First Stream Table

Before diving into multi-table joins and recursive CTEs, start with the simplest possible stream table: a single-source aggregate with no joins.

1.1 Setup

Create one table and enable the extension:

CREATE EXTENSION IF NOT EXISTS pg_trickle;

CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    category TEXT           NOT NULL,
    price    NUMERIC(10,2)  NOT NULL,
    in_stock BOOLEAN        NOT NULL DEFAULT true
);

INSERT INTO products (category, price) VALUES
    ('Electronics', 299.99),
    ('Electronics', 49.99),
    ('Books',       14.99),
    ('Books',       24.99),
    ('Books',        9.99);

1.2 Create the stream table

SELECT pgtrickle.create_stream_table(
    name     => 'category_summary',
    query    => $$
        SELECT
            category,
            COUNT(*)                    AS product_count,
            ROUND(AVG(price), 2)        AS avg_price,
            MIN(price)                  AS min_price,
            MAX(price)                  AS max_price,
            COUNT(*) FILTER (WHERE in_stock) AS in_stock_count
        FROM products
        GROUP BY category
    $$,
    schedule => '1s'
);

Query it immediately — it was populated by the initial full refresh:

SELECT category, product_count, avg_price, min_price, max_price, in_stock_count
FROM category_summary ORDER BY category;

  category   | product_count | avg_price | min_price | max_price | in_stock_count
-------------+---------------+-----------+-----------+-----------+----------------
 Books       |             3 |     16.66 |      9.99 |     24.99 |              3
 Electronics |             2 |    174.99 |     49.99 |    299.99 |              2
(2 rows)

1.3 Watch an INSERT update one group

INSERT INTO products (category, price) VALUES ('Books', 39.99);

Within ~1 second (or call SELECT pgtrickle.refresh_stream_table('category_summary') to force it):

SELECT category, product_count, avg_price, min_price, max_price, in_stock_count
FROM category_summary WHERE category = 'Books';

 category | product_count | avg_price | min_price | max_price | in_stock_count
----------+---------------+-----------+-----------+-----------+----------------
 Books    |             4 |     22.49 |      9.99 |     39.99 |              4
(1 row)

The Electronics row was not touched at all — pg_trickle read exactly 1 row from the change buffer, adjusted only the Books group.

1.4 Watch an UPDATE propagate

UPDATE products SET price = 19.99 WHERE price = 299.99;

After the next refresh:

SELECT category, product_count, avg_price, min_price, max_price, in_stock_count
FROM category_summary WHERE category = 'Electronics';

  category   | product_count | avg_price | min_price | max_price | in_stock_count
-------------+---------------+-----------+-----------+-----------+----------------
 Electronics |             2 |     34.99 |     19.99 |     49.99 |              2
(1 row)

For AVG, pg_trickle maintains running sum and count columns internally, so re-aggregating a group is O(1) regardless of group size.

1.5 What you just saw

• A single function call created the storage table, installed CDC triggers, ran the initial full refresh, and registered a 1-second schedule.
• Every subsequent DML on products was captured in an AFTER trigger — no polling, no logical replication.
• Each refresh touched only the rows and groups that changed.
• The stream table is a real PostgreSQL table — you can SELECT, index, and join against category_summary like any other table.

Clean up: SELECT pgtrickle.drop_stream_table('category_summary'); DROP TABLE products;

Chapter 2: Joins, Aggregates & Chains

What you'll build

An employee org-chart system with two stream tables:

• department_tree — a recursive CTE that flattens a department hierarchy into paths like Company > Engineering > Backend
• department_stats — a join + aggregation over department_tree (a stream table!) that computes headcount and salary budget, with the full path included
• department_report — a further aggregation that rolls up stats to top-level departments

The chain departments → department_tree → department_stats → department_report demonstrates automatic downstream propagation: modify a department name in the base table and all three stream tables update automatically, in the right order, without any manual orchestration.

By the end you will have:

• Seen how stream tables are created, queried, and refreshed
• Watched a single UPDATE in a base table cascade through three layers of stream tables automatically
• Understood the four refresh modes and IVM strategies

Prefer dbt? A runnable dbt companion project mirrors every step below. Clone the repo and run:

./examples/dbt_getting_started/scripts/run_example.sh

See examples/dbt_getting_started/ for full details.

2.1 Create the Base Tables

These are ordinary PostgreSQL tables — pg_trickle doesn't require any special column types, annotations, or schema conventions.

Tables without a primary key work, but pg_trickle will emit a WARNING at stream table creation time: change detection falls back to a content-based hash across all columns, which is slower for wide tables and cannot distinguish between identical duplicate rows. Adding a primary key gives the best performance and most reliable change detection. A primary key is also required for automatic transition to WAL-based CDC (cdc_mode = 'auto'); without one the source table stays on trigger-based CDC.

-- Department hierarchy (self-referencing tree)
CREATE TABLE departments (
    id         SERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    parent_id  INT REFERENCES departments(id)
);

-- Employees belong to a department
CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          TEXT NOT NULL,
    department_id INT NOT NULL REFERENCES departments(id),
    salary        NUMERIC(10,2) NOT NULL
);

Now insert some data — a three-level department tree and a handful of employees:

-- Top-level
INSERT INTO departments (id, name, parent_id) VALUES
    (1, 'Company',     NULL);

-- Second level
INSERT INTO departments (id, name, parent_id) VALUES
    (2, 'Engineering', 1),
    (3, 'Sales',       1),
    (4, 'Operations',  1);

-- Third level (under Engineering)
INSERT INTO departments (id, name, parent_id) VALUES
    (5, 'Backend',     2),
    (6, 'Frontend',    2),
    (7, 'Platform',    2);

-- Employees
INSERT INTO employees (name, department_id, salary) VALUES
    ('Alice',   5, 120000),   -- Backend
    ('Bob',     5, 115000),   -- Backend
    ('Charlie', 6, 110000),   -- Frontend
    ('Diana',   7, 130000),   -- Platform
    ('Eve',     3, 95000),    -- Sales
    ('Frank',   3, 90000),    -- Sales
    ('Grace',   4, 100000);   -- Operations

At this point these are plain tables with no triggers, no change tracking, nothing special. The department tree looks like this:

Company (1)
├── Engineering (2)
│   ├── Backend (5)     — Alice, Bob
│   ├── Frontend (6)    — Charlie
│   └── Platform (7)    — Diana
├── Sales (3)           — Eve, Frank
└── Operations (4)      — Grace

2.2 Create the First Stream Table — Recursive Hierarchy

Our first stream table flattens the department tree. For every department, it computes the full path from the root and the depth level. This uses WITH RECURSIVE — a SQL construct that can't be differentiated with simple algebraic rules (the recursion depends on itself), but pg_trickle handles it using incremental strategies (semi-naive evaluation for inserts, Delete-and-Rederive for mixed changes) that we'll explain later.

SELECT pgtrickle.create_stream_table(
    name         => 'department_tree',
    query        => $$
    WITH RECURSIVE tree AS (
        -- Base case: root departments (no parent)
        SELECT id, name, parent_id, name AS path, 0 AS depth
        FROM departments
        WHERE parent_id IS NULL

        UNION ALL

        -- Recursive step: children join back to the tree
        SELECT d.id, d.name, d.parent_id,
               tree.path || ' > ' || d.name AS path,
               tree.depth + 1
        FROM departments d
        JOIN tree ON d.parent_id = tree.id
    )
    SELECT id, name, parent_id, path, depth FROM tree
    $$,
    schedule     => '1s'
);

Note on short schedules: A 1-second schedule is safe for development and production thanks to auto_backoff (on by default since v0.10.0). If a refresh takes more than 95% of the schedule window, the scheduler automatically stretches the effective interval (up to 8× the configured schedule) to prevent CPU runaway, then resets to 1× as soon as a refresh completes on time. You will see a WARNING message when backoff activates.

v0.2.0+: create_stream_table also accepts diamond_consistency ('none' or 'atomic') and diamond_schedule_policy ('fastest' or 'slowest') for diamond-shaped dependency graphs. Schedules can be cron expressions (e.g., '*/5 * * * *', '@hourly'). Set pooler_compatibility_mode => true if you're connecting through PgBouncer or another transaction-mode connection pooler. See SQL_REFERENCE.md for the full parameter list.

What just happened?

That single function call did a lot of work atomically (all in one transaction):

1. Parsed the defining query into an operator tree — identifying the recursive CTE, the scan on departments, the join, the union
2. Created a storage table called department_tree in the public schema — a real PostgreSQL heap table with columns matching the SELECT output, plus internal columns __pgt_row_id (a hash used to track individual rows)
3. Installed CDC triggers on the departments table — lightweight AFTER INSERT OR UPDATE OR DELETE row-level triggers that will capture every future change
4. Created a change buffer table in the pgtrickle_changes schema — this is where the triggers write captured changes
5. Ran an initial full refresh — executed the recursive query against the current data and populated the storage table
6. Registered the stream table in pg_trickle's catalog with a 1-second refresh schedule

TRUNCATE caveat: Row-level triggers do not fire on TRUNCATE. If you TRUNCATE a base table, the change is not captured incrementally — the stream table will become stale. Use DELETE FROM table instead, or call pgtrickle.refresh_stream_table('department_tree') after a TRUNCATE. If the stream table uses DIFFERENTIAL mode, temporarily switch to FULL for a full recompute: pgtrickle.alter_stream_table('department_tree', refresh_mode => 'FULL'), refresh, then switch back. Query it immediately — it's already populated:

SELECT id, name, parent_id, path, depth FROM department_tree ORDER BY path;

Expected output:

 id |    name     | parent_id |               path               | depth
----+-------------+-----------+----------------------------------+-------
  1 | Company     |           | Company                          |     0
  2 | Engineering |         1 | Company > Engineering            |     1
  5 | Backend     |         2 | Company > Engineering > Backend  |     2
  6 | Frontend    |         2 | Company > Engineering > Frontend |     2
  7 | Platform    |         2 | Company > Engineering > Platform |     2
  4 | Operations  |         1 | Company > Operations             |     1
  3 | Sales       |         1 | Company > Sales                  |     1
(7 rows)

This is a real PostgreSQL table — you can create indexes on it, join it in other queries, reference it in views, or even use it as a source for other stream tables. pg_trickle keeps it in sync automatically.

Key insight: The recursive query that computes paths and depths would normally need to be re-run manually (or via REFRESH MATERIALIZED VIEW). With pg_trickle, it stays fresh — any change to the departments table is automatically reflected within the schedule bound (1 second here).

2.3 Chain Stream Tables — Build the Downstream Layers

Now create department_stats. The twist: instead of joining directly against departments, it joins against department_tree — the stream table we just created. This creates a chain: changes to departments update department_tree, whose changes then trigger department_stats to update.

This demonstrates how pg_trickle builds a DAG — a directed acyclic graph of stream tables — and automatically schedules refreshes in topological order.

SELECT pgtrickle.create_stream_table(
    name         => 'department_stats',
    query        => $$
    SELECT
        t.id          AS department_id,
        t.name        AS department_name,
        t.path        AS full_path,
        t.depth,
        COUNT(e.id)                    AS headcount,
        COALESCE(SUM(e.salary), 0)     AS total_salary,
        COALESCE(AVG(e.salary), 0)     AS avg_salary
    FROM department_tree t
    LEFT JOIN employees e ON e.department_id = t.id
    GROUP BY t.id, t.name, t.path, t.depth
    $$,
    schedule     => 'calculated'      -- CALCULATED: inherit schedule from downstream; see explanation below
);

What just happened — and why this one is different?

Like before, pg_trickle parsed the query, created a storage table, and set up CDC. But department_stats depends on department_tree, not a base table — so no new triggers were installed. Instead, pg_trickle registered department_tree as an upstream dependency in the DAG.

The schedule is 'calculated' (CALCULATED mode), which means: "don't give this table its own schedule — inherit the tightest schedule of any downstream table that queries it". Internally this stores NULL in the catalog, but you must pass the string 'calculated' — passing SQL NULL is an error. Since no other stream table has been created yet, it will be refreshed on demand or when a downstream dependent triggers it.

The query has no recursive CTE, so pg_trickle uses algebraic differentiation:

1. Decomposed into operators: Scan(department_tree) → LEFT JOIN → Scan(employees) → Aggregate(GROUP BY + COUNT/SUM/AVG) → Project
2. Derived a differentiation rule for each:
   • Δ(Scan) = read only change buffer rows (not the full table)
   • Δ(LEFT JOIN) = join change rows from one side against the full other side
   • Δ(Aggregate) = for COUNT/SUM/AVG, add or subtract per group — no rescan needed
3. Composed these into a single delta query (ΔQ) that never touches unchanged rows

When one employee is inserted, the refresh reads one change buffer row, joins to find the department, and adjusts only that group's count and sum.

Query it:

SELECT department_name, full_path, headcount, total_salary
FROM department_stats
ORDER BY full_path;

Expected output:

 department_name |            full_path             | headcount | total_salary
-----------------+----------------------------------+-----------+--------------
 Company         | Company                          |         0 |            0
 Engineering     | Company > Engineering            |         0 |            0
 Backend         | Company > Engineering > Backend  |         2 |    235000.00
 Frontend        | Company > Engineering > Frontend |         1 |    110000.00
 Platform        | Company > Engineering > Platform |         1 |    130000.00
 Operations      | Company > Operations             |         1 |    100000.00
 Sales           | Company > Sales                  |         2 |    185000.00
(7 rows)

Notice that the full_path column comes from department_tree — this data already went through one layer of incremental maintenance before landing here.

Add a third layer: department_report

Now add a rollup that aggregates department_stats by top-level group (depth = 1):

SELECT pgtrickle.create_stream_table(
    name         => 'department_report',
    query        => $$
    SELECT
        split_part(full_path, ' > ', 2) AS division,
        SUM(headcount)                  AS total_headcount,
        SUM(total_salary)               AS total_payroll
    FROM department_stats
    WHERE depth >= 1
    GROUP BY 1
    $$,
    schedule     => '1s'              -- this is the only explicit schedule; CALCULATED tables above inherit it
);

The DAG is now:

departments (base)  employees (base)
      │                   │
      ▼                   │
department_tree ──────────┤
   (DIFF, CALCULATED)     │
      │                   ▼
      └──────▶ department_stats
                 (DIFF, CALCULATED)
                      │
                      ▼
               department_report
                  (DIFF, 1s)   ◀── only explicit schedule

department_report drives the whole pipeline. Because it has a 1-second schedule, pg_trickle automatically propagates that cadence upstream: department_stats and department_tree will also be refreshed within 1 second of a base table change, in topological order, with no manual configuration.

Query the report:

SELECT division, total_headcount, total_payroll FROM department_report ORDER BY division;

  division   | total_headcount | total_payroll
-------------+-----------------+---------------
 Engineering |               4 |    475000.00
 Operations  |               1 |    100000.00
 Sales       |               2 |    185000.00
(3 rows)

2.4 Watch a Change Cascade Through All Three Layers

This is the heart of pg_trickle. We'll make four changes to the base tables and watch changes propagate automatically through the three-layer DAG — each layer doing only the minimum work.

The data flow pipeline (three layers)

  Your SQL statement
       │
       ▼
  CDC trigger fires (same transaction)
  Change buffer receives one row
       │
       ▼
  Background scheduler fires (within ~1 second)
       │
       ├──▶ [Layer 1] Refresh department_tree
       │         delta query reads change buffer
       │         MERGE touches only affected rows in department_tree
       │         department_tree's own change buffer is updated
       │
       ├──▶ [Layer 2] Refresh department_stats
       │         delta query reads department_tree's change buffer
       │         MERGE touches only affected department groups
       │
       └──▶ [Layer 3] Refresh department_report
                 delta query reads department_stats' change buffer
                 MERGE touches only affected division rows
                 All change buffers cleaned up ✓

All three layers run in a single scheduled pass, in topological order.

2.4a: INSERT ripples through all three layers

INSERT INTO employees (name, department_id, salary) VALUES
    ('Heidi', 6, 105000);  -- New Frontend engineer

What happened immediately (in your transaction): The AFTER INSERT trigger on employees fired and wrote one row to pgtrickle_changes.changes_<employees_oid>. The row contains the new values, action type I, and the LSN at the time of insert. Your transaction committed normally — no blocking.

The stream tables don't know about Heidi yet. The change is in the buffer, waiting for the next refresh.

The background scheduler handles this automatically. With a 1-second schedule, department_stats and department_report refresh within about a second.

To confirm a refresh has happened, check data_timestamp in the monitoring view:

SELECT name, data_timestamp, staleness FROM pgtrickle.pgt_status();

To force an immediate synchronous refresh, wait a moment first (so the scheduler can finish its current tick), then call in topological order. Note that refresh_stream_table only refreshes the named table — it does not cascade upstream:

SELECT pg_sleep(2);  -- let the scheduler finish any in-progress tick
SELECT pgtrickle.refresh_stream_table('department_stats');
SELECT pgtrickle.refresh_stream_table('department_report');

What happened across the three layers:

Check the result:

SELECT department_name, headcount, total_salary FROM department_stats
WHERE department_name = 'Frontend';

 department_name | headcount | total_salary
-----------------+-----------+--------------
 Frontend        |         2 |    215000.00

The 6 other groups in department_stats were not touched at all.

Contrast with a standard materialized view: REFRESH MATERIALIZED VIEW would re-scan all 8 employees, re-join with all 7 departments, re-aggregate, and update all 7 rows. With pg_trickle, the work was proportional to the 1 changed row — across all three layers.

2.4b: A department change cascades through the whole DAG

Now change the departments table — the root of the entire chain:

INSERT INTO departments (id, name, parent_id) VALUES
    (8, 'DevOps', 2);  -- New team under Engineering

What happened: The CDC trigger on departments fired. The change buffer for departments has one new row. None of the stream tables know about it yet.

The scheduler handles this automatically — all three tables will refresh within a second in the correct dependency order (upstream first). To force it synchronously, wait a moment first then refresh each table in topological order (refresh_stream_table does not cascade upstream):

SELECT pg_sleep(2);
SELECT pgtrickle.refresh_stream_table('department_tree');
SELECT pgtrickle.refresh_stream_table('department_stats');
SELECT pgtrickle.refresh_stream_table('department_report');

What happened across all three layers:

How the recursive CTE refresh works — unlike department_stats, recursive CTEs can't be algebraically differentiated (the recursion references itself). pg_trickle uses incremental fixpoint strategies:

• INSERT → semi-naive evaluation: differentiate the base case, propagate the delta through the recursive term, stopping when no new rows are produced. Only new rows inserted.
• DELETE or UPDATE → Delete-and-Rederive (DRed): remove rows derived from deleted facts, re-derive rows that may have alternative derivation paths, handle cascades cleanly.

SELECT id, name, depth, path FROM department_tree WHERE name = 'DevOps';

 id |  name  | depth |              path
----+--------+-------+--------------------------------
  8 | DevOps |     2 | Company > Engineering > DevOps
(1 row)

The recursive CTE automatically expanded to include the new department at the correct depth and path. One inserted row in departments produced one new row in the stream table.

2.4c: UPDATE — A single rename that cascades everywhere

Rename "Engineering" to "R&D":

UPDATE departments SET name = 'R&D' WHERE id = 2;

What happened in the change buffer: The CDC trigger captured the old row (name='Engineering') and the new row (name='R&D'). Both old and new values are stored so the delta can compute what to remove and what to add.

Wait a moment for the scheduler to propagate the rename through all layers. To force it synchronously, wait then refresh each table in topological order (refresh_stream_table does not cascade upstream):

SELECT pg_sleep(2);
SELECT pgtrickle.refresh_stream_table('department_tree');
SELECT pgtrickle.refresh_stream_table('department_stats');
SELECT pgtrickle.refresh_stream_table('department_report');

What happened across all three layers:

Query to verify the cascade:

SELECT name, path FROM department_tree WHERE path LIKE '%R&D%' ORDER BY depth, name;

   name   |           path           
----------+--------------------------
 R&D      | Company > R&D
 Backend  | Company > R&D > Backend
 DevOps   | Company > R&D > DevOps
 Frontend | Company > R&D > Frontend
 Platform | Company > R&D > Platform
(5 rows)

One UPDATE to a department name flowed through all three layers automatically — updating 5 + 5 + 2 rows across the chain.

2.4d: DELETE — Remove an employee

DELETE FROM employees WHERE name = 'Bob';

What happened: The AFTER DELETE trigger on employees fired, writing a change buffer row with action type D and Bob's old values (department_id=5, salary=115000). The delta query will use these old values to compute the correct aggregate adjustment — it knows to subtract 115000 from Backend's salary sum and decrement the count.

Important — refresh before querying: The background scheduler refreshes all three tables within ~1 second, in topological order. To see the result immediately, wait a moment then explicitly refresh in upstream-first order:

SELECT pg_sleep(2);
SELECT pgtrickle.refresh_stream_table('department_stats');
SELECT pgtrickle.refresh_stream_table('department_report');

Why call department_stats first? department_stats sources from both employees and department_tree. Refreshing in topological order ensures each layer processes its upstream changes before computing its own deltas. Even when department_tree has unprocessed changes from step 4c and a new employee change arrives simultaneously, pg_trickle's differential engine handles both correctly — using the pre-change left snapshot (L₀) to avoid double-counting.

Then verify the result:

SELECT department_name, headcount, total_salary, avg_salary
FROM department_stats WHERE department_name = 'Backend';

 department_name | headcount | total_salary |     avg_salary
-----------------+-----------+--------------+---------------------
 Backend         |         1 |    120000.00 | 120000.000000000000
(1 row)

Headcount dropped from 2 → 1 and the salary aggregates updated. Again, only the Backend group was touched — the other 6 department rows were untouched.

Chapter 3: Scheduling & Backpressure

Automatic Scheduling — Let the DAG Drive Itself

pg_trickle runs a background scheduler that automatically refreshes stale tables in topological order. In the Step 4 examples above, the scheduler handled every change within about a second. You can also call refresh_stream_table() directly when needed (e.g. in scripts or tests), but in normal operation the scheduler takes care of everything.

How schedules propagate

We gave department_report a '1s' schedule and the two upstream tables a NULL schedule (CALCULATED mode). This is the recommended pattern:

 department_tree    (CALCULATED → inherits 1s from downstream)
       │
 department_stats   (CALCULATED → inherits 1s from downstream)
       │
 department_report  (1s — the only explicit schedule)

CALCULATED mode (pass schedule => 'calculated') means: compute the tightest schedule across all downstream dependents. You declare freshness requirements at the tables your application queries — the system figures out how often each upstream table needs to refresh.

What the scheduler does every second

1. Queries the catalog for stream tables past their freshness bound
2. Sorts them topologically (upstream first) — department_tree refreshes before department_stats, which refreshes before department_report
3. Runs each refresh (respecting pg_trickle.max_concurrent_refreshes)
4. Updates the last-refresh frontier

Monitoring

-- Current status of all stream tables
SELECT name, status, refresh_mode, schedule, data_timestamp, staleness
FROM pgtrickle.pgt_status();

        name                 | status |  refresh_mode | schedule |       data_timestamp        |    staleness
-----------------------------+--------+---------------+----------+-----------------------------+-----------------
 public.department_tree      | ACTIVE | DIFFERENTIAL  |          | 2026-02-26 10:30:00.123+01 | 00:00:00.877
 public.department_stats     | ACTIVE | DIFFERENTIAL  |          | 2026-02-26 10:30:00.456+01 | 00:00:00.544
 public.department_report    | ACTIVE | DIFFERENTIAL  | 1s       | 2026-02-26 10:30:00.789+01 | 00:00:00.211

-- Detailed performance stats
SELECT pgt_name, total_refreshes, avg_duration_ms, successful_refreshes
FROM pgtrickle.pg_stat_stream_tables;

-- Health check: quick triage of common issues
SELECT check_name, severity, detail FROM pgtrickle.health_check();

-- Visualize the dependency DAG
SELECT * FROM pgtrickle.dependency_tree();

-- Recent refresh timeline across all stream tables
SELECT * FROM pgtrickle.refresh_timeline(10);

-- Check CDC change buffer sizes (spotting buffer build-up)
SELECT * FROM pgtrickle.change_buffer_sizes();

See SQL_REFERENCE.md for the full list of monitoring functions including list_sources(), trigger_inventory(), and diamond_groups().

Chapter 4: Monitoring In Depth

All the monitoring capabilities from the monitoring quick reference above, expanded. For the five most important day-to-day introspection queries see the Monitoring Quick Reference at the end of this guide.

Optional: WAL-based CDC

By default pg_trickle uses triggers. If wal_level = logical is configured, set:

ALTER SYSTEM SET pg_trickle.cdc_mode = 'auto';
SELECT pg_reload_conf();

pg_trickle will automatically transition each stream table from trigger-based to WAL-based capture after the first successful refresh — reducing per-write overhead from ~2–15 μs (triggers) to near-zero (WAL-based capture adds no synchronous overhead to your DML). The transition is transparent; your queries and the refresh schedule are unaffected.

Optional: Parallel Refresh (v0.4.0+)

By default the scheduler refreshes stream tables sequentially in topological order within a single background worker. This is correct and efficient for most workloads.

For deployments with many independent stream tables, enable parallel refresh:

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

Independent stream tables at the same DAG level will then refresh concurrently in separate dynamic background workers. Refresh pairs with IMMEDIATE-trigger connections and atomic consistency groups still run in a single worker for correctness.

Before enabling, ensure max_worker_processes has enough room:

max_worker_processes >= 1 (launcher)
                      + number of databases with stream tables
                      + max_dynamic_refresh_workers (default 4)
                      + autovacuum and other extension workers

Monitor parallel refresh:

SELECT * FROM pgtrickle.worker_pool_status();        -- live worker budget
SELECT * FROM pgtrickle.parallel_job_status(60);     -- recent jobs

See CONFIGURATION.md — Parallel Refresh for the complete tuning reference.

Optional: PgBouncer / Connection Pooler Compatibility (v0.10.0+)

If you're connecting through PgBouncer or another connection pooler in transaction mode (the default on Supabase, Railway, Neon, and most managed PostgreSQL platforms), set pooler_compatibility_mode when creating or altering a stream table:

SELECT pgtrickle.create_stream_table(
    name                    => 'live_headcount',
    query                   => 'SELECT department_id, COUNT(*) FROM employees GROUP BY 1',
    schedule                => '1s',
    pooler_compatibility_mode => true
);

This disables prepared statements and NOTIFY emissions for that table — the two features that break in transaction-pool mode. Leave it off (the default) if you connect directly to PostgreSQL.

Optional: Change Buffer Compaction (v0.10.0+)

For high-churn tables, pg_trickle automatically compacts the pending change buffer before each refresh cycle when it exceeds pg_trickle.compact_threshold (default 100,000 rows). INSERT→DELETE pairs that cancel each other out are eliminated, and multiple changes to the same row are collapsed to a single net change, reducing delta scan overhead by 50–90%.

Chapter 5: Advanced Topics

Refresh Modes and IVM Strategies

You've now seen the IVM strategies pg_trickle uses for incremental view maintenance. Understanding the four refresh modes and when each strategy applies helps you write efficient stream table queries.

The Four Refresh Modes

When you omit refresh_mode, the default is 'AUTO' — it uses differential (delta-only) maintenance when the query supports it, and automatically falls back to full recomputation when it doesn't. You only need to specify a mode explicitly for advanced cases.

IMMEDIATE mode (new in v0.2.0) maintains stream tables synchronously within the same transaction as the base table DML. It uses statement-level AFTER triggers with transition tables — no change buffers, no scheduler. The stream table is always consistent with the current transaction.

-- Create a stream table that updates in real-time
SELECT pgtrickle.create_stream_table(
    name         => 'live_headcount',
    query        => $$
    SELECT department_id, COUNT(*) AS headcount
    FROM employees
    GROUP BY department_id
    $$,
    refresh_mode => 'IMMEDIATE'
);

-- After any INSERT/UPDATE/DELETE on employees,
-- live_headcount is already up-to-date — no refresh needed!

IMMEDIATE mode supports joins, aggregates, window functions, LATERAL subqueries, and cascading IMMEDIATE stream tables. Recursive CTEs are not supported in IMMEDIATE mode (use DIFFERENTIAL instead).

You can switch between modes at any time:

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

-- Switch back to DIFFERENTIAL with a schedule
SELECT pgtrickle.alter_stream_table('department_stats', refresh_mode => 'DIFFERENTIAL', schedule => '1s');

Algebraic Differentiation (used by department_stats)

For queries composed of scans, filters, joins, and algebraic aggregates (COUNT, SUM, AVG), pg_trickle can derive the IVM delta mathematically. The rules come from the theory of DBSP (Database Stream Processing):

The total cost is proportional to the number of changes, not the table size. For a million-row table with 10 changes, the delta query touches ~10 rows.

Incremental Strategies for Recursive CTEs (used by department_tree)

For recursive CTEs, pg_trickle can't derive an algebraic delta because the recursion references itself. Instead it uses two complementary strategies, chosen automatically based on what changed:

Semi-naive evaluation (for INSERT-only changes):

1. Differentiate the base case — find the new seed rows
2. Propagate the delta through the recursive term, iterating until no new rows are produced
3. The result is only the new rows created by the change — not the whole tree

Delete-and-Rederive (DRed) (for DELETE or UPDATE):

1. Remove all rows derived from the old fact
2. Re-derive rows that had the old fact as one of their derivation paths (they may still be reachable via other paths)
3. Insert the newly derived rows under the new fact

Both strategies are more efficient than full recomputation — they work on the affected portion of the result set, not the entire recursive query. The MERGE only modifies rows that actually changed.

When to use which strategy?

You don't choose — pg_trickle detects the strategy automatically based on the query structure:

FUSE Circuit Breaker (v0.11.0+)

The fuse is a circuit breaker that stops a stream table from processing an unexpectedly large batch of changes — for example from a runaway script or mass-delete migration — without operator review.

-- Arm a fuse: blow when pending changes exceed 50 000 rows
SELECT pgtrickle.alter_stream_table(
    'category_summary',
    fuse           => 'on',
    fuse_ceiling   => 50000
);

-- Check fuse status across all stream tables
SELECT name, fuse_mode, fuse_state, fuse_ceiling, blown_at
FROM pgtrickle.fuse_status();

-- After investigating and deciding to apply the batch:
SELECT pgtrickle.reset_fuse('category_summary', action => 'apply');

-- Or skip the oversized batch entirely and resume from current state:
SELECT pgtrickle.reset_fuse('category_summary', action => 'skip_changes');

reset_fuse supports three actions:

• 'apply' — process all pending changes and resume normal scheduling.
• 'reinitialize' — drop and repopulate the stream table from scratch.
• 'skip_changes' — discard pending changes and resume from the current frontier.

A pgtrickle_alert NOTIFY is emitted when the fuse blows, making it easy to hook into alerting pipelines or LISTEN from application code.

Partitioned Stream Tables (v0.11.0+)

For large stream tables, declare a partition key at creation time so MERGE operations are scoped to only the relevant partitions:

SELECT pgtrickle.create_stream_table(
    name         => 'sales_by_month',
    query        => $$
        SELECT
            DATE_TRUNC('month', sale_date) AS month,
            product_id,
            SUM(amount) AS total_sales
        FROM sales
        GROUP BY 1, 2
    $$,
    schedule     => '1m',
    partition_by => 'month'    -- partition key must be in the SELECT output
);

pg_trickle creates the storage table as PARTITION BY RANGE (month) with a catch-all partition, then on each refresh:

1. Inspects the delta to find the MIN and MAX of the partition key.
2. Injects AND st.month BETWEEN min AND max into the MERGE ON clause.
3. PostgreSQL prunes all partitions outside the range — giving ~100× I/O reduction for a 0.1% change rate on a 10M-row table.

See SQL_REFERENCE.md for full partitioning options.

IMMEDIATE Mode — Real-Time In-Transaction IVM

-- Create a stream table that updates in the same transaction as its source
SELECT pgtrickle.create_stream_table(
    name         => 'live_headcount',
    query        => $$
    SELECT department_id, COUNT(*) AS headcount
    FROM employees
    GROUP BY department_id
    $$,
    refresh_mode => 'IMMEDIATE'
);

-- After any INSERT/UPDATE/DELETE on employees, live_headcount is already up-to-date:
INSERT INTO employees (name, department_id, salary) VALUES ('Zara', 2, 95000);
SELECT * FROM live_headcount WHERE department_id = 2;  -- 4 rows, immediately

IMMEDIATE mode uses statement-level AFTER triggers with transition tables — no change buffers, no scheduler, no background workers. The stream table is always consistent with the current transaction. Ideal for audit tables, real-time dashboards, and applications that need zero-latency reads.

Multi-Tenant Worker Quotas (v0.11.0+)

In deployments with multiple databases, one busy database can starve others if all dynamic refresh workers are claimed. The per_database_worker_quota GUC prevents this:

-- Limit one performance-critical database to 4 workers (with burst to 6)
ALTER DATABASE analytics  SET pg_trickle.per_database_worker_quota = 4;
-- Allow a reporting database only 2 base workers
ALTER DATABASE reporting  SET pg_trickle.per_database_worker_quota = 2;
-- Apply changes
SELECT pg_reload_conf();

When the cluster has spare capacity (active workers < 80% of max_dynamic_refresh_workers), a database may temporarily burst to 150% of its quota. Burst is reclaimed within 1 scheduler cycle once load rises. Within each dispatch tick, IMMEDIATE-trigger closures are always dispatched first, followed by atomic groups, singletons, and cyclic SCCs.

See CONFIGURATION.md for full quota tuning options.

Clean Up

When you're done experimenting, drop the stream tables. Drop dependents before their sources:

SELECT pgtrickle.drop_stream_table('department_report');
SELECT pgtrickle.drop_stream_table('department_stats');
SELECT pgtrickle.drop_stream_table('department_tree');

DROP TABLE employees;
DROP TABLE departments;

drop_stream_table atomically removes in a single transaction:

• The storage table (e.g., public.department_stats)
• CDC triggers on source tables (removed only if no other stream table references the same source)
• Change buffer tables in pgtrickle_changes
• Catalog entries in pgtrickle.pgt_stream_tables

Monitoring Quick Reference

pg_trickle ships several built-in monitoring functions and a ready-made Prometheus/Grafana stack. Here are the five most useful functions for day-to-day operations.

Stream Table Status

-- Overview of all stream tables: status, staleness, last refresh time, errors
SELECT name, status, staleness, last_refresh_at, last_error
FROM pgtrickle.pgt_status();

Health Check

-- Run all built-in health checks; returns severity (OK/WARNING/CRITICAL) per check
SELECT check_name, severity, detail FROM pgtrickle.health_check();

Change Buffer Sizes

-- Show CDC buffer row counts per source table — useful for spotting backlogs
SELECT * FROM pgtrickle.change_buffer_sizes();

Dependency Tree

-- Visualize the DAG: which stream tables depend on what
SELECT * FROM pgtrickle.dependency_tree();

Fuse Status

-- Check circuit breaker state for all stream tables (v0.11.0+)
SELECT * FROM pgtrickle.fuse_status();

Prometheus & Grafana

For production monitoring, pg_trickle ships a ready-made observability stack in the monitoring/ directory:

cd monitoring && docker compose up

This starts PostgreSQL + postgres_exporter + Prometheus + Grafana with pre-configured dashboards and alerting rules. Grafana is available at http://localhost:3000 (admin/admin). See the monitoring README for the full list of exported metrics and alert conditions.

Key Prometheus metrics:

Pre-configured alerts: staleness > 5 min, ≥3 consecutive failures, table SUSPENDED, CDC buffer > 1 GB, scheduler down, high refresh duration.

Summary: What You Learned

The key takeaway: you write to base tables — pg_trickle does the rest. Data flows downstream automatically, each layer doing the minimum work proportional to what changed, in dependency order.

Troubleshooting

Stream table is stale / not refreshing

Check the status view first:

SELECT name, status, last_error, last_refresh_at, staleness FROM pgtrickle.pgt_status();

A status of ERROR means the last refresh failed. last_error contains the message. Fix the underlying issue (e.g., a dropped column referenced in the query) then call:

SELECT pgtrickle.refresh_stream_table('your_table');

For a broader health check:

SELECT check_name, severity, detail FROM pgtrickle.health_check();

Change buffer growing large

If a stream table has status = 'PAUSED' or refreshes are falling behind:

SELECT * FROM pgtrickle.change_buffer_sizes();  -- find large buffers

Large buffers are normal under heavy load — auto_backoff slows the schedule to avoid CPU runaway and will self-correct once throughput stabilises. If a buffer stays large indefinitely, check last_error in pgt_status() for a blocked refresh.

CDC triggers missing after restore / point-in-time recovery

PITR restores the heap table but not the triggers if the extension was installed after the base backup. Verify:

SELECT * FROM pgtrickle.trigger_inventory();  -- expected vs installed triggers

Any missing trigger can be reinstalled with:

SELECT pgtrickle.repair_stream_table('your_table');

Deployment Best Practices

Once you've built your stream tables interactively, you'll want to deploy them reliably — via SQL migration scripts, dbt, or GitOps pipelines.

Kubernetes Deployment (CloudNativePG)

pg_trickle integrates natively with CloudNativePG using Image Volume Extensions (Kubernetes 1.33+). The extension is packaged as a scratch-based OCI image containing only the .so, .control, and .sql files — no custom PostgreSQL image required.

Prerequisites

• Kubernetes 1.33+ with the ImageVolume feature gate enabled
• CloudNativePG operator 1.28+
• pg_trickle extension image pushed to your cluster registry

Quick Start

1. Deploy the Cluster with the extension mounted as an Image Volume:

# cnpg/cluster-example.yaml (abridged)
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-trickle-demo
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:18
  postgresql:
    shared_preload_libraries:
      - pg_trickle
    extensions:
      - name: pg-trickle
        image:
          reference: ghcr.io/<owner>/pg_trickle-ext:<version>
    parameters:
      max_worker_processes: "8"

2. Create the extension declaratively with a CNPG Database resource:

# cnpg/database-example.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: pg-trickle-app
spec:
  name: app
  owner: app
  cluster:
    name: pg-trickle-demo
  extensions:
    - name: pg_trickle

3. Apply both resources:

kubectl apply -f cnpg/cluster-example.yaml
kubectl apply -f cnpg/database-example.yaml

Full example manifests are in the cnpg/ directory.

Health Monitoring

CNPG manages PostgreSQL liveness/readiness probes via its instance manager. For pg_trickle-specific health, use the built-in health check function:

-- Run against the primary or any replica:
SELECT * FROM pgtrickle.health_check();

This returns rows for scheduler status, error/suspended tables, stale tables, CDC buffer growth, WAL slot lag, and worker pool utilization. Integrate it into your monitoring stack:

• Prometheus: Use the CNPG monitoring integration to expose pgtrickle.health_check() results as custom metrics
• Kubernetes CronJob: Schedule periodic health checks and alert via your existing alerting pipeline

Probe Configuration

The example manifests include probe settings tuned for pg_trickle workloads:

probes:
  startup:
    periodSeconds: 10
    failureThreshold: 60     # 10 min for shared_preload_libraries init
  liveness:
    periodSeconds: 10
    failureThreshold: 6      # 60s before restart
  readiness:
    type: streaming
    maximumLag: 64Mi         # replicas must be streaming before serving reads

Why readiness: streaming? Stream tables are readable on replicas, but a lagging replica serves stale stream table data. The maximumLag setting ensures replicas are caught up before receiving traffic.

Failover Behavior

When the primary pod fails and CNPG promotes a replica:

• Scheduler: The new primary starts the pg_trickle scheduler background worker automatically (registered via shared_preload_libraries)
• Stream tables: All stream table definitions are stored in the pgtrickle.pgt_stream_tables catalog table, which is replicated to all replicas. The promoted replica has the complete catalog.
• CDC triggers: Trigger definitions are replicated as part of the WAL stream. The new primary's triggers fire normally on new writes.
• Change buffers: Uncommitted change buffer rows from in-flight transactions on the old primary are lost (standard PostgreSQL behavior). The next refresh cycle detects the gap and performs a FULL refresh to resynchronize.
• Refresh frontiers: Each stream table's last-refresh frontier is stored in the catalog. If the frontier is ahead of the available change buffer data (due to WAL replay lag), the scheduler falls back to FULL refresh once and then resumes DIFFERENTIAL.

No manual intervention is required after failover.

Idempotent SQL Migrations

Use create_or_replace_stream_table() in your migration scripts. It's safe to run on every deploy:

-- migrations/V003__stream_tables.sql
-- Creates if absent, updates if definition changed, no-op if identical.

SELECT pgtrickle.create_or_replace_stream_table(
    name         => 'employee_salaries',
    query        => 'SELECT e.id, e.name, d.name AS department, e.salary
                     FROM employees e JOIN departments d ON e.department_id = d.id',
    schedule     => '30s',
    refresh_mode => 'DIFFERENTIAL'
);

SELECT pgtrickle.create_or_replace_stream_table(
    name         => 'department_stats',
    query        => 'SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
                     FROM employee_salaries GROUP BY department',
    schedule     => '30s',
    refresh_mode => 'DIFFERENTIAL'
);

If someone changes the query in a later migration, create_or_replace detects the difference and migrates the storage table in place — no need to drop and recreate.

dbt Integration

With the dbt-pgtrickle package, stream tables are just dbt models with materialized='stream_table':

-- models/department_stats.sql
{{ config(
    materialized='stream_table',
    schedule='30s',
    refresh_mode='DIFFERENTIAL'
) }}

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM {{ ref('employee_salaries') }}
GROUP BY department

Every dbt run calls create_or_replace_stream_table() under the hood, so deployments are always idempotent.

Day 2 Operations

Added in v0.20.0 (UX-4).

Once your stream tables are running in production, pg_trickle can monitor itself using its own stream tables — a technique called self-monitoring.

Enabling Self-Monitoring

-- Create all five monitoring stream tables (idempotent, safe to repeat).
SELECT pgtrickle.setup_self_monitoring();

-- Check what was created.
SELECT * FROM pgtrickle.self_monitoring_status();

This creates five stream tables in the pgtrickle schema:

Checking Recommendations

After at least 10–20 refresh cycles have accumulated:

-- Which stream tables have poorly calibrated thresholds?
SELECT pgt_name, current_threshold, recommended_threshold, confidence, reason
FROM pgtrickle.df_threshold_advice
WHERE confidence IN ('HIGH', 'MEDIUM')
  AND abs(recommended_threshold - current_threshold) > 0.05;

-- Are any stream tables experiencing anomalies?
SELECT pgt_name, duration_anomaly, recent_failures
FROM pgtrickle.df_anomaly_signals
WHERE duration_anomaly IS NOT NULL OR recent_failures >= 2;

Automatic Threshold Tuning

To let pg_trickle automatically apply threshold recommendations:

SET pg_trickle.self_monitoring_auto_apply = 'threshold_only';

This applies changes only when confidence is HIGH and the recommended threshold differs by more than 5%. Changes are rate-limited to once per 10 minutes per stream table and logged with initiated_by = 'SELF_MONITOR'.

Visualizing the DAG

-- See the full refresh graph (Mermaid format, paste into any Mermaid renderer).
SELECT pgtrickle.explain_dag();

Dog-feeding STs appear in green, user STs in blue, suspended in red.

Disabling Self-Monitoring

SELECT pgtrickle.teardown_self_monitoring();

This drops all monitoring stream tables. User stream tables are never affected. The control plane continues operating identically without self-monitoring.

What's Next?

• SQL_REFERENCE.md — Full API reference for all functions, views, and configuration
• ARCHITECTURE.md — Deep dive into the system architecture and data flow
• DVM_OPERATORS.md — How each SQL operator is differentiated for incremental maintenance
• CONFIGURATION.md — GUC variables for tuning schedule, concurrency, and cleanup behavior
• Flyway & Liquibase Integration — Migration patterns for Flyway and Liquibase
• ORM Integration — SQLAlchemy and Django ORM patterns for stream tables
• What Happens on INSERT — Detailed trace of a single INSERT through the entire pipeline
• What Happens on UPDATE — How UPDATEs are split into D+I, group key changes, and net-effect computation
• What Happens on DELETE — Reference counting, group deletion, and INSERT+DELETE cancellation
• What Happens on TRUNCATE — Why TRUNCATE bypasses triggers and how to recover
• dbt Getting Started example — Everything above, expressed as dbt models and seeds with a one-command Docker runner

Viewing on GitHub? The installation guide lives in INSTALL.md. This stub is served by the pg_trickle docs site — the include below renders there.

Installation Guide

Choose your installation path

Prerequisites

Building from source additionally requires Rust 1.85+ (edition 2024) and pgrx 0.18.x. Pre-built release artifacts only need a running PostgreSQL 18.x instance.

Installing from a Pre-built Release

1. Download the release archive

Download the archive for your platform from the GitHub Releases page:

Optionally verify the checksum against SHA256SUMS.txt from the same release:

sha256sum -c SHA256SUMS.txt

2. Extract and install

Linux / macOS:

tar xzf pg_trickle-<ver>-pg18-linux-amd64.tar.gz
cd pg_trickle-<ver>-pg18-linux-amd64

sudo cp lib/*.so  "$(pg_config --pkglibdir)/"
sudo cp extension/*.control extension/*.sql "$(pg_config --sharedir)/extension/"

Windows (PowerShell):

Expand-Archive pg_trickle-<ver>-pg18-windows-amd64.zip -DestinationPath .
cd pg_trickle-<ver>-pg18-windows-amd64

Copy-Item lib\*.dll  "$(pg_config --pkglibdir)\"
Copy-Item extension\* "$(pg_config --sharedir)\extension\"

3. Using with CloudNativePG (Kubernetes)

pg_trickle is distributed as an OCI extension image for use with CloudNativePG Image Volume Extensions.

Requirements: Kubernetes 1.33+, CNPG 1.28+, PostgreSQL 18.

# Pull the extension image
docker pull ghcr.io/trickle-labs/pg_trickle-ext:<ver>

See cnpg/cluster-example.yaml and cnpg/database-example.yaml for complete Cluster and Database deployment examples.

4. GHCR Docker image (recommended for local dev)

pg_trickle is published as a ready-to-run Docker image on the GitHub Container Registry. PostgreSQL 18.3 and pg_trickle are pre-installed and all sensible GUC defaults (wal_level, shared_preload_libraries, memory, scheduler settings) are baked in — no configuration file editing needed.

docker pull ghcr.io/trickle-labs/pg_trickle:latest

docker run --rm \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  ghcr.io/trickle-labs/pg_trickle:latest

CREATE EXTENSION pg_trickle; runs automatically on the default postgres database at first startup.

Available tags:

Override any GUC at runtime without rebuilding:

docker run --rm \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  ghcr.io/trickle-labs/pg_trickle:latest \
  -c shared_buffers=2GB -c work_mem=64MB -c effective_cache_size=6GB

For persistent data, mount a volume:

docker run -d \
  --name pg_trickle \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  -v pg_trickle_data:/var/lib/postgresql/data \
  ghcr.io/trickle-labs/pg_trickle:latest

Alternative — manual mount from a release archive: If you prefer to use the stock postgres:18.3 image rather than the pre-built image, extract the extension files from a release archive and mount them:

tar xzf pg_trickle-<ver>-pg18-linux-amd64.tar.gz
cd pg_trickle-<ver>-pg18-linux-amd64

docker run --rm \
  -v $PWD/lib/pg_trickle.so:/usr/lib/postgresql/18/lib/pg_trickle.so:ro \
  -v $PWD/extension/:/tmp/ext/:ro \
  -e POSTGRES_PASSWORD=postgres \
  postgres:18.3 \
  sh -c 'cp /tmp/ext/* /usr/share/postgresql/18/extension/ && \
         exec postgres -c shared_preload_libraries=pg_trickle'

Installing from PGXN

pg_trickle is published on the PostgreSQL Extension Network (PGXN). Installing via PGXN compiles the extension from source, so the Rust toolchain and pgrx are required.

1. Install prerequisites

# Rust toolchain
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
source "$HOME/.cargo/env"

# pgrx build tool
cargo install --locked cargo-pgrx --version 0.18.0
cargo pgrx init --pg18 "$(pg_config --bindir)/pg_config"

2. Install the pgxn client

pip install pgxnclient

3. Install pg_trickle

pgxn install pg_trickle

To install a specific version:

pgxn install pg_trickle=0.10.0

Note: After installation, follow the PostgreSQL Configuration and Extension Installation steps below.

Building from Source

1. Install Rust

curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

2. Install pgrx

cargo install --locked cargo-pgrx --version 0.18.0
cargo pgrx init --pg18 $(pg_config --bindir)/pg_config

3. Build the Extension

# Development build (faster compilation)
cargo pgrx install --pg-config $(pg_config --bindir)/pg_config

# Release build (optimized, for production)
cargo pgrx install --release --pg-config $(pg_config --bindir)/pg_config

# Package for deployment (creates installable artifacts)
cargo pgrx package --pg-config $(pg_config --bindir)/pg_config

PostgreSQL Configuration

Add the following to postgresql.conf before starting PostgreSQL:

# Required — loads the extension shared library at server start
shared_preload_libraries = 'pg_trickle'

# Must accommodate the pg_trickle launcher + one scheduler per database
# with pg_trickle installed + optional parallel refresh workers.
#
# WARNING: when this limit is reached, the launcher silently skips
# databases it cannot spawn a scheduler for and retries every 5 minutes.
# Those databases stop refreshing without any visible error.
# Check PostgreSQL logs for:
#   WARNING:  pg_trickle launcher: could not spawn scheduler for database '...'
#
# Formula:
#   1 (launcher) + N (one scheduler per DB) + max_dynamic_refresh_workers
#   + autovacuum_max_workers + parallel query workers + other extensions
#
# 32 is a safe starting point for most clusters:
max_worker_processes = 32

Note: wal_level = logical and max_replication_slots are not required. The extension uses lightweight row-level triggers for CDC, not logical replication.

Restart PostgreSQL after modifying these settings:

pg_ctl restart -D /path/to/data
# or
systemctl restart postgresql

Extension Installation

Connect to the target database and run:

CREATE EXTENSION pg_trickle;

This creates:

• The pgtrickle schema with catalog tables and SQL functions
• The pgtrickle_changes schema for change buffer tables
• Event triggers for DDL tracking
• The pgtrickle.pg_stat_stream_tables monitoring view

Verification

After installation, verify everything is working:

-- Check the extension version
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_trickle';

-- Or get a full status overview (includes version, scheduler state, stream table count)
SELECT * FROM pgtrickle.pgt_status();

Quick functional test

CREATE TABLE test_source (id INT PRIMARY KEY, val TEXT);
INSERT INTO test_source VALUES (1, 'hello');

SELECT pgtrickle.create_stream_table(
    'test_st',
    'SELECT id, val FROM test_source',
    '1m',
    'FULL'
);

SELECT * FROM test_st;
-- Should return: 1 | hello

-- Clean up
SELECT pgtrickle.drop_stream_table('test_st');
DROP TABLE test_source;

Upgrading

To upgrade pg_trickle to a newer version without losing data:

For comprehensive upgrade instructions, version-specific notes, troubleshooting, and rollback procedures, see docs/UPGRADING.md.

1. Install the new extension files

Follow the same steps as Installing from a Pre-built Release to overwrite the shared library and SQL files with the new version. You do not need to drop the extension from your databases first.

Linux / macOS:

tar xzf pg_trickle-<new-ver>-pg18-linux-amd64.tar.gz
cd pg_trickle-<new-ver>-pg18-linux-amd64

sudo cp lib/*.so  "$(pg_config --pkglibdir)/"
sudo cp extension/*.control extension/*.sql "$(pg_config --sharedir)/extension/"

2. Restart PostgreSQL (when required)

If the shared library ABI has changed, restart PostgreSQL before proceeding so the new .so/.dll is loaded. The release notes for each version will call this out explicitly when a restart is required.

pg_ctl restart -D /path/to/data
# or
systemctl restart postgresql

3. Apply the schema migration in each database

Connect to every database where pg_trickle is installed and run:

-- Upgrade to the latest bundled version
ALTER EXTENSION pg_trickle UPDATE;

-- Or upgrade to a specific version
ALTER EXTENSION pg_trickle UPDATE TO '<new-version>';

PostgreSQL uses the versioned SQL migration scripts bundled with the release (e.g. pg_trickle--0.2.3--0.3.0.sql, pg_trickle--0.3.0--0.4.0.sql) to apply catalog and SQL-surface changes. PostgreSQL automatically chains these scripts when you run ALTER EXTENSION pg_trickle UPDATE. The command is a no-op when no migration script is needed for a given release.

You can confirm the active version afterwards:

SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';

Coming soon: A future release will include a helper function (pgtrickle.upgrade()) that automates steps 2–3 across all databases in the cluster and validates catalog integrity after the migration. Until then, the manual steps above are the supported upgrade path.

Uninstallation

-- Drop all stream tables first
SELECT pgtrickle.drop_stream_table(pgt_schema || '.' || pgt_name)
FROM pgtrickle.pgt_stream_tables;

-- Drop the extension
DROP EXTENSION pg_trickle CASCADE;

Remove pg_trickle from shared_preload_libraries in postgresql.conf and restart PostgreSQL.

Troubleshooting

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

macOS 26 (Tahoe) changed dyld to eagerly resolve all flat-namespace symbols at binary load time. pgrx extensions reference PostgreSQL server-internal symbols (e.g. CacheMemoryContext, SPI_connect) via the -Wl,-undefined,dynamic_lookup linker flag. These symbols are normally provided by the postgres executable when the extension is loaded as a shared library — but for cargo test --lib there is no postgres process, so the test binary aborts immediately:

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

This affects local development only — integration tests, E2E tests, and the extension itself running inside PostgreSQL are unaffected.

The fix is built into the just test-unit recipe. It automatically:

1. Compiles a tiny C stub library (scripts/pg_stub.c → target/libpg_stub.dylib) that provides NULL/no-op definitions for the ~28 PostgreSQL symbols.
2. Compiles the test binary with --no-run.
3. Runs the binary with DYLD_INSERT_LIBRARIES pointing to the stub.

The stub is only built on macOS 26+. On Linux or older macOS, just test-unit runs cargo test --lib directly with no changes.

Note: The stub symbols are never called — unit tests exercise pure Rust logic only. If a test accidentally calls a PostgreSQL function it will crash with a NULL dereference (the desired fail-fast behavior).

If you run unit tests without just (e.g. directly via cargo test --lib), you can use the wrapper script instead:

./scripts/run_unit_tests.sh pg18

# With test name filter:
./scripts/run_unit_tests.sh pg18 -- test_parse_basic

Extension fails to load

Ensure shared_preload_libraries = 'pg_trickle' is set and PostgreSQL has been restarted (not just reloaded). The extension requires shared memory initialization at startup.

Background worker not starting

Check that max_worker_processes is high enough. In sequential mode (default) pg_trickle needs one slot per database with stream tables. With parallel refresh enabled (pg_trickle.parallel_refresh_mode = 'on') it additionally needs max_dynamic_refresh_workers slots (default 4) shared across all databases.

See the worker-budget formula in CONFIGURATION.md for sizing guidance.

Check logs for details

The extension logs at various levels. Enable debug logging for more detail:

SET client_min_messages TO debug1;

Backup & Restore

Backing Up a pg_trickle-Enabled Database

pg_trickle uses two schemas that must be included in any backup alongside your application schema:

• pgtrickle — stream table catalog, dependency graph, and refresh history
• pgtrickle_changes — change-buffer tables (one per CDC-enabled source)

Include both schemas explicitly when using pg_dump:

pg_dump \
  --schema=public \
  --schema=pgtrickle \
  --schema=pgtrickle_changes \
  mydb > mydb_backup.sql

If your application data lives in schemas other than public, include those schemas too. Omitting pgtrickle_changes means any unprocessed CDC rows are lost on restore, forcing the next differential refresh to fall back to FULL mode.

Restoring

Restore normally:

psql -d mydb_restored -f mydb_backup.sql

After restore, run the health check to validate catalog integrity:

SELECT * FROM pgtrickle.health_check();

OID Re-assignment After Restore

Change-buffer tables in pgtrickle_changes are named by storage-table OID (e.g. changes_12345). OIDs may differ after restore if tables were created in a different order. Run pg_trickle_repair_stream_table() on each stream table immediately after restore to reconcile any OID mismatches:

SELECT pgtrickle.repair_stream_table(pgt_schema || '.' || pgt_name)
FROM pgtrickle.pgt_stream_tables;

Next Steps

• Getting Started — Create your first stream table in 5 minutes
• Pre-Deployment Checklist — Complete checklist for production deployments
• Best-Practice Patterns — Common data modeling patterns
• Configuration Reference — All GUC variables and tuning

Best-Practice Patterns for pg_trickle

This guide covers common data modeling patterns and recommended configurations for pg_trickle stream tables. Each pattern includes worked SQL examples, anti-patterns to avoid, and refresh mode recommendations.

Version: v0.14.0+. Some features require recent versions — check SQL_REFERENCE.md for per-feature availability.

Table of Contents

• Pattern 1: Bronze / Silver / Gold Materialization
• Pattern 2: Event Sourcing with Stream Tables
• Pattern 3: Slowly Changing Dimensions (SCD)
• Pattern 4: High-Fan-Out Topology
• Pattern 5: Real-Time Dashboards
• Pattern 6: Tiered Refresh Strategy
• General Guidelines
• Replica Bootstrap & PITR Alignment (v0.27.0)
• Pattern 7: Transactional Outbox (v0.28.0)
• Pattern 8: Transactional Inbox (v0.28.0)

Pattern 1: Bronze / Silver / Gold Materialization

A multi-layer approach where raw data flows through progressively refined stream tables, similar to a medallion architecture.

Architecture

  [raw_events]          ← Bronze: raw ingest table (regular table)
       ↓
  [events_cleaned]      ← Silver: filtered, deduplicated, typed
       ↓
  [events_aggregated]   ← Gold: business-level aggregates

SQL Example

-- Bronze: regular PostgreSQL table (source of truth)
CREATE TABLE raw_events (
    event_id    BIGSERIAL PRIMARY KEY,
    user_id     INT NOT NULL,
    event_type  TEXT NOT NULL,
    payload     JSONB,
    received_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Silver: cleaned and deduplicated events
SELECT pgtrickle.create_stream_table(
    'events_cleaned',
    $$SELECT DISTINCT ON (event_id)
        event_id,
        user_id,
        event_type,
        (payload->>'amount')::numeric AS amount,
        received_at
      FROM raw_events
      WHERE event_type IN ('purchase', 'refund', 'subscription')$$,
    schedule => '5s',
    refresh_mode => 'DIFFERENTIAL'
);

-- Gold: per-user purchase summary
SELECT pgtrickle.create_stream_table(
    'user_purchase_summary',
    $$SELECT user_id,
             COUNT(*) AS total_purchases,
             SUM(amount) AS total_spent,
             AVG(amount) AS avg_order
      FROM events_cleaned
      WHERE event_type = 'purchase'
      GROUP BY user_id$$,
    schedule => 'calculated',
    refresh_mode => 'DIFFERENTIAL'
);

Recommended Configuration

Anti-Patterns

• Don't use FULL refresh for Silver. With frequent small inserts, DIFFERENTIAL is 10–100x faster.
• Don't skip the Silver layer. Joining raw tables directly in Gold queries produces wider joins and slower deltas.
• Don't use IMMEDIATE mode for Gold. Aggregate maintenance on every DML row is expensive — batched DIFFERENTIAL is more efficient.

When NOT to use this pattern

• Your data never changes after insert — a single stream table is simpler.
• The Bronze source is external (foreign table, dblink) — CDC triggers cannot be attached to foreign tables; use WAL CDC mode or FULL refresh.
• You have fewer than ~10,000 rows in Silver — the overhead of three layers is not justified; use one or two tables instead.

Pattern 2: Event Sourcing with Stream Tables

Use stream tables as projections of an append-only event log. The source table is the event store; stream tables materialize different read models.

SQL Example

-- Event store (append-only source)
CREATE TABLE events (
    event_id    BIGSERIAL PRIMARY KEY,
    aggregate_id UUID NOT NULL,
    event_type   TEXT NOT NULL,
    payload      JSONB NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Projection 1: Current state per aggregate
SELECT pgtrickle.create_stream_table(
    'aggregate_state',
    $$SELECT DISTINCT ON (aggregate_id)
        aggregate_id,
        event_type AS last_event,
        payload AS current_state,
        created_at AS last_updated
      FROM events
      ORDER BY aggregate_id, created_at DESC$$,
    schedule => '2s',
    refresh_mode => 'DIFFERENTIAL'
);

-- Projection 2: Event counts by type per hour
SELECT pgtrickle.create_stream_table(
    'hourly_event_counts',
    $$SELECT date_trunc('hour', created_at) AS hour,
             event_type,
             COUNT(*) AS event_count
      FROM events
      GROUP BY 1, 2$$,
    schedule => '10s',
    refresh_mode => 'DIFFERENTIAL'
);

Recommended Configuration

Anti-Patterns

• Don't DELETE from the event store. pg_trickle tracks changes via triggers; mixing append and delete on the source creates unnecessary delta complexity. Archive old events to a separate table.
• Don't use append_only => true with UPDATE/DELETE patterns. The append_only flag skips DELETE tracking in the change buffer — only use it when the source truly never updates or deletes.

When NOT to use this pattern

• Your event log is consumed and processed in real time by application code — a stream table adds latency without benefit.
• You need strict per-event ordering guarantees within a transaction — use IMMEDIATE mode with a single-row projection instead.
• Your events are multi-gigabyte payloads — stream tables replicate the whole row; store only metadata in the event log, not the payload.

Pattern 3: Slowly Changing Dimensions (SCD)

SCD Type 1: Overwrite

The stream table always reflects the current state. Source updates overwrite previous values.

-- Source: customer dimension table (updated in place)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT,
    tier        TEXT DEFAULT 'standard',
    updated_at  TIMESTAMPTZ DEFAULT now()
);

-- SCD-1: current customer state enriched with order stats
SELECT pgtrickle.create_stream_table(
    'customer_360',
    $$SELECT c.customer_id,
             c.name,
             c.email,
             c.tier,
             COUNT(o.id) AS total_orders,
             COALESCE(SUM(o.amount), 0) AS lifetime_value
      FROM customers c
      LEFT JOIN orders o ON o.customer_id = c.customer_id
      GROUP BY c.customer_id, c.name, c.email, c.tier$$,
    schedule => '30s',
    refresh_mode => 'DIFFERENTIAL'
);

SCD Type 2: History Tracking

For SCD-2, maintain a history table with valid-from/valid-to ranges. The stream table provides the current snapshot.

-- Source: customer history with validity ranges
CREATE TABLE customer_history (
    customer_id INT NOT NULL,
    name        TEXT NOT NULL,
    tier        TEXT NOT NULL,
    valid_from  TIMESTAMPTZ NOT NULL,
    valid_to    TIMESTAMPTZ,  -- NULL = current
    PRIMARY KEY (customer_id, valid_from)
);

-- Current active records only
SELECT pgtrickle.create_stream_table(
    'customers_current',
    $$SELECT customer_id, name, tier, valid_from
      FROM customer_history
      WHERE valid_to IS NULL$$,
    schedule => '10s',
    refresh_mode => 'DIFFERENTIAL'
);

Anti-Patterns

• Don't use FULL refresh for SCD-1 with large dimension tables. Customer tables with millions of rows but few changes per cycle are ideal for DIFFERENTIAL.
• Don't forget to index valid_to IS NULL for SCD-2 sources. Without it, the delta scan touches all historical rows.

When NOT to use this pattern

• You already have a purpose-built slowly-changing-dimension ETL tool (e.g. dbt snapshots) — pg_trickle's SCD support is complementary, not a replacement, and duplicate ownership creates confusion.
• Your dimension table changes every row on every load — DIFFERENTIAL offers no benefit; use FULL refresh or rethink the source update pattern.
• You need Type 3 (add-a-column) or Type 6 SCD — those require schema evolution that pg_trickle does not automate today.

Pattern 4: High-Fan-Out Topology

When a single source table feeds many downstream stream tables.

Architecture

                    [orders]
                   ↙  ↓  ↓  ↘
  [daily_totals] [by_region] [by_product] [top_customers]

SQL Example

-- Single source feeding multiple views
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    region      TEXT NOT NULL,
    product_id  INT NOT NULL,
    amount      NUMERIC(10,2) NOT NULL,
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE
);

-- Fan-out: 4 stream tables on 1 source
SELECT pgtrickle.create_stream_table('daily_totals',
    'SELECT order_date, SUM(amount) AS daily_total, COUNT(*) AS order_count
     FROM orders GROUP BY order_date',
    schedule => '5s', refresh_mode => 'DIFFERENTIAL');

SELECT pgtrickle.create_stream_table('by_region',
    'SELECT region, SUM(amount) AS total, COUNT(*) AS cnt
     FROM orders GROUP BY region',
    schedule => '5s', refresh_mode => 'DIFFERENTIAL');

SELECT pgtrickle.create_stream_table('by_product',
    'SELECT product_id, SUM(amount) AS total, COUNT(*) AS cnt
     FROM orders GROUP BY product_id',
    schedule => '5s', refresh_mode => 'DIFFERENTIAL');

SELECT pgtrickle.create_stream_table('top_customers',
    'SELECT customer_id, SUM(amount) AS lifetime_value, COUNT(*) AS order_count
     FROM orders GROUP BY customer_id',
    schedule => '10s', refresh_mode => 'DIFFERENTIAL');

Recommended Configuration

• All fan-out targets share the same source change buffer — CDC overhead is paid once regardless of how many stream tables read from orders.
• Use schedule => 'calculated' on downstream STs when they chain from other stream tables.
• Consider pg_trickle.max_concurrent_refreshes if fan-out exceeds 8 (default: 4 concurrent refreshes).

Anti-Patterns

• Don't use IMMEDIATE mode on high-fan-out sources. Each DML row triggers N refreshes (one per downstream ST). Use DIFFERENTIAL with a batched schedule instead.
• Don't set different schedules on STs that should be consistent. If daily_totals and by_region must agree, give them the same schedule or use diamond_consistency => 'atomic'.

When NOT to use this pattern

• You only have one or two downstream stream tables — the fan-out pattern adds planning overhead that isn't justified below ~4 targets.
• Downstream queries have incompatible refresh modes (e.g. one needs IMMEDIATE, another needs FULL) — prefer separate source tables.
• All downstream STs will always be queried together — a single wider stream table may be simpler and faster.

Pattern 5: Real-Time Dashboards

For dashboards that need sub-second refresh latency.

SQL Example

-- Live order monitor (sub-second freshness)
SELECT pgtrickle.create_stream_table(
    'order_monitor',
    $$SELECT
        date_trunc('minute', order_date) AS minute,
        region,
        COUNT(*) AS orders,
        SUM(amount) AS revenue
      FROM orders
      WHERE order_date >= CURRENT_DATE
      GROUP BY 1, 2$$,
    schedule => '1s',
    refresh_mode => 'DIFFERENTIAL'
);

-- For truly real-time needs, use IMMEDIATE mode (triggers on each DML)
SELECT pgtrickle.create_stream_table(
    'live_counter',
    $$SELECT region, COUNT(*) AS cnt, SUM(amount) AS total
      FROM orders GROUP BY region$$,
    schedule => 'IMMEDIATE',
    refresh_mode => 'DIFFERENTIAL'
);

When to Use IMMEDIATE vs Scheduled DIFFERENTIAL

Anti-Patterns

• Don't use IMMEDIATE for complex joins. Each INSERT/UPDATE/DELETE fires the full DVM delta SQL synchronously — multi-table joins in IMMEDIATE mode add significant latency to writes.
• Don't forget pooler_compatibility_mode with PgBouncer. Transaction pooling drops temp tables between transactions; enable this flag to avoid stale PREPARE statements.

When NOT to use this pattern

• The data source itself is the bottleneck (slow sensors, infrequent API polling) — a sub-second schedule on a stream table that changes once a minute burns CPU for nothing.
• You need consistency across several related tiles — schedule them together or use a single wider query rather than sub-second individual refreshes that can transiently disagree.
• Write throughput exceeds ~5,000 rows/s — IMMEDIATE mode adds latency to every write; profile with pg_trickle.latency_percentiles() first.

Pattern 6: Tiered Refresh Strategy

Assign refresh importance tiers to control scheduling priority.

-- Hot: real-time operational dashboard
SELECT pgtrickle.create_stream_table('live_metrics', ...);
SELECT pgtrickle.alter_stream_table('live_metrics', tier => 'hot');

-- Warm: hourly business reports (2x interval multiplier)
SELECT pgtrickle.create_stream_table('hourly_report', ...,
    schedule => '1m');
SELECT pgtrickle.alter_stream_table('hourly_report', tier => 'warm');

-- Cold: daily analytics (10x interval multiplier)
SELECT pgtrickle.create_stream_table('daily_analytics', ...,
    schedule => '5m');
SELECT pgtrickle.alter_stream_table('daily_analytics', tier => 'cold');

-- Frozen: archive/audit (skip refresh entirely)
SELECT pgtrickle.alter_stream_table('audit_log_summary', tier => 'frozen');

Tier Multipliers

When NOT to use this pattern

• All your stream tables are equally critical — don't introduce tier complexity just to have tiers; use a flat schedule instead.
• Your scheduler runs with a single worker — tiering helps multi-worker scheduling; it has no effect when max_concurrent_refreshes = 1.
• Tier multipliers change too frequently — tier is a static property; if freshness requirements change continuously, use SLA scheduling (pg_trickle.sla_scheduling) instead.

General Guidelines

Choosing a Refresh Mode

Use pgtrickle.recommend_refresh_mode() (v0.14.0+) for automated analysis:

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

Monitoring Checklist

-- Check refresh efficiency across all stream tables
SELECT pgt_name, refresh_mode, diff_speedup, avg_change_ratio
FROM pgtrickle.refresh_efficiency()
ORDER BY total_refreshes DESC;

-- Find stream tables that might benefit from mode change
SELECT pgt_name, current_mode, recommended_mode, reason
FROM pgtrickle.recommend_refresh_mode()
WHERE recommended_mode != 'KEEP';

-- Check for error states
SELECT pgt_name, status, last_error_message
FROM pgtrickle.stream_tables_info
WHERE status IN ('ERROR', 'SUSPENDED');

-- Export definitions for backup
SELECT pgtrickle.export_definition(pgt_schema || '.' || pgt_name)
FROM pgtrickle.pgt_stream_tables;

Common Mistakes

1. Using FULL refresh by default. Start with DIFFERENTIAL — it's correct for 80%+ of workloads. Switch to FULL only when recommend_refresh_mode() suggests it.

2. Over-scheduling. A 1-second schedule on a table with 1-hour change cycles wastes CPU. Match the schedule to actual data arrival rate.

3. Ignoring append_only. If the source table is truly append-only (no UPDATEs, no DELETEs), set append_only => true to halve change buffer writes.

4. Not using calculated schedule for chained STs. When ST-B reads from ST-A, use schedule => 'calculated' on ST-B to avoid unnecessary refreshes. The scheduler automatically propagates ST-A changes downstream.

5. Mixing IMMEDIATE and complex joins. IMMEDIATE mode fires delta SQL on every DML — an 8-table join in IMMEDIATE mode adds 50–200ms to each INSERT. Use scheduled DIFFERENTIAL for complex queries.

Replica Bootstrap & PITR Alignment (v0.27.0)

When bootstrapping a new replica or performing point-in-time recovery, stream tables need special handling because their state is derived from source data at a specific frontier (LSN + timestamp).

The problem

After a pg_basebackup or logical restore, stream table rows are present but their frontiers may be stale. The next refresh would trigger a FULL re-scan of all source data, which is expensive for large stream tables.

Solution: use snapshots for replica bootstrap

-- On the primary: export the stream table state
SELECT pgtrickle.snapshot_stream_table(
    'public.orders_agg',
    'pgtrickle.orders_agg_replica_init'
);

-- Dump only the snapshot table to the replica
pg_dump -t 'pgtrickle.orders_agg_replica_init' mydb | psql replica_db

-- On the replica: restore and align the frontier
SELECT pgtrickle.restore_from_snapshot(
    'public.orders_agg',
    'pgtrickle.orders_agg_replica_init'
);

-- Clean up the bootstrap snapshot
SELECT pgtrickle.drop_snapshot('pgtrickle.orders_agg_replica_init');

After restore_from_snapshot(), the frontier is set to the snapshot's frontier and the next refresh is DIFFERENTIAL — only changes after the snapshot creation time are fetched.

PITR alignment workflow

When performing PITR to a specific LSN:

1. Take a snapshot immediately before the target LSN
2. Restore the database to the target LSN using pg_basebackup + WAL replay
3. Run restore_from_snapshot() on each stream table to align frontiers

-- Step 1: snapshot all stream tables (before PITR)
SELECT pgtrickle.snapshot_stream_table(
    pgt_schema || '.' || pgt_name,
    'pgtrickle.pitr_snapshot_' || pgt_name || '_' || extract(epoch from now())::bigint
)
FROM pgtrickle.pgt_stream_tables
WHERE status = 'ACTIVE';

-- Step 3 (after PITR): restore all snapshots
SELECT pgtrickle.restore_from_snapshot(
    pgt_schema || '.' || pgt_name,
    'pgtrickle.pitr_snapshot_' || pgt_name || '_<epoch>'
)
FROM pgtrickle.pgt_stream_tables;

Performance: Restoring a 1M-row stream table from a snapshot completes in < 5 seconds (bulk INSERT from local table). The frontier alignment ensures the first differential refresh fetches only new changes, not all rows.

Pattern 7: Transactional Outbox (v0.28.0)

Requires: v0.28.0+

The transactional outbox pattern reliably publishes stream table deltas to external consumers — even if the consumer is temporarily offline. Each time the stream table refreshes, pg_trickle writes a header row to a dedicated outbox table. Consumers read from the outbox via poll_outbox(), process the delta, then commit their offset.

Use this pattern when:

• You need to publish stream table changes to a message queue, webhook, or another service
• Consumers need at-least-once delivery guarantees
• Multiple independent consumers need to read the same stream independently
• You want replay / seek-to-offset for recovery

Architecture

orders (base table)
  └─→ orders_agg (stream table)
        └─→ pgt_outbox_orders_agg (outbox table)
              ├─→ Consumer group A: analytics pipeline
              └─→ Consumer group B: notification service

SQL Example

-- 1. Create the stream table
SELECT pgtrickle.create_stream_table(
    'public.orders_agg',
    'SELECT customer_id, SUM(amount) AS total, COUNT(*) AS cnt FROM orders GROUP BY customer_id',
    schedule_seconds => 5
);

-- 2. Enable the outbox
SELECT pgtrickle.enable_outbox('public.orders_agg', retention_hours => 48);

-- 3. Create consumer groups
SELECT pgtrickle.create_consumer_group('analytics', 'public.orders_agg', auto_offset_reset => 'latest');
SELECT pgtrickle.create_consumer_group('notifications', 'public.orders_agg', auto_offset_reset => 'latest');

-- 4. Consumer A polls and processes
DO $$
DECLARE
    r RECORD;
    last_id BIGINT := 0;
BEGIN
    FOR r IN
        SELECT * FROM pgtrickle.poll_outbox('analytics', 'worker-1', batch_size => 50)
    LOOP
        -- process r.payload (JSONB with inserted/deleted row arrays)
        last_id := r.outbox_id;
    END LOOP;

    IF last_id > 0 THEN
        PERFORM pgtrickle.commit_offset('analytics', 'worker-1', last_id);
    END IF;
END;
$$;

-- 5. Check consumer lag
SELECT * FROM pgtrickle.consumer_lag('analytics');

Consumer Group Tips

Recommended Configuration

pg_trickle.outbox_enabled = true
pg_trickle.outbox_retention_hours = 48        # keep 2 days of history
pg_trickle.outbox_skip_empty_delta = true     # don't write rows for no-op refreshes
pg_trickle.outbox_force_retention = true      # keep rows until all groups commit
pg_trickle.consumer_dead_threshold_hours = 24 # mark workers dead after 24h silence
pg_trickle.consumer_cleanup_enabled = true

Anti-Patterns

• Polling without committing: If commit_offset() is never called, the lease expires and the rows are re-delivered. Always commit after successful processing.
• One group per worker: Use one group and multiple named consumers within it for competing-consumer parallelism. Use multiple groups only when pipelines are truly independent.
• Long processing without heartbeats: Call consumer_heartbeat() every 10–15 seconds for long-running processing to avoid being marked dead.

When NOT to use this pattern

• You only need to expose stream table changes to a single application that reads directly from PostgreSQL — a NOTIFY/LISTEN trigger or change table is simpler than a full outbox.
• Delivery guarantees are not required (analytics, dashboards) — the overhead of consumer groups and offset tracking is not justified.
• Your stream table refreshes every few seconds and consumers can tolerate a few seconds of lag — just poll the stream table directly.

Pattern 8: Transactional Inbox (v0.28.0)

Requires: v0.28.0+

The transactional inbox pattern provides a reliable, idempotent message receiver inside PostgreSQL. External producers write events to the inbox table; pg_trickle maintains stream tables that give you live views of pending, failed, and processed messages — all updated incrementally.

Use this pattern when:

• You receive events from external systems and need to process them exactly-once
• You want automatic dead-letter handling for failed messages
• Multiple workers need to process different aggregates without stepping on each other
• You need per-aggregate ordering guarantees

Architecture

external producer (Kafka / webhook / custom application)
  └─→ pgtrickle.orders_inbox (raw event table)
        ├─→ orders_inbox_pending  (stream table: awaiting processing)
        ├─→ orders_inbox_dlq      (stream table: failed messages)
        └─→ orders_inbox_stats    (stream table: event counts by type)

SQL Example

-- 1. Create the inbox
SELECT pgtrickle.create_inbox(
    'orders_inbox',
    schema           => 'pgtrickle',
    max_retries      => 3,
    with_dead_letter => true,
    with_stats       => true,
    schedule_seconds => 5
);

-- 2. External system inserts a message
INSERT INTO pgtrickle.orders_inbox (event_id, event_type, aggregate_id, payload)
VALUES (
    gen_random_uuid()::text,
    'order.placed',
    'customer-123',
    '{"order_id": 42, "amount": 99.50}'::jsonb
);

-- 3. Worker polls pending messages and processes
UPDATE pgtrickle.orders_inbox
SET processed_at = now()
WHERE event_id = '<event_id>'
  AND processed_at IS NULL;

-- 4. Check inbox health
SELECT pgtrickle.inbox_health('orders_inbox');

-- 5. Replay failed messages
SELECT pgtrickle.replay_inbox_messages(
    'orders_inbox',
    ARRAY['event-id-1', 'event-id-2']
);

Per-Aggregate Ordering

When messages for the same customer / entity must be processed in sequence:

-- Enable ordering: only surface the next unprocessed message per aggregate
SELECT pgtrickle.enable_inbox_ordering(
    'orders_inbox',
    aggregate_id_col => 'aggregate_id',
    seq_col          => 'event_sequence'
);

-- Workers now read from next_orders_inbox (one row per aggregate)
SELECT * FROM pgtrickle.next_orders_inbox;

Multi-Worker Partitioning

Scale horizontally without external coordination:

-- Worker 0 of 4 handles its share of aggregates
SELECT * FROM pgtrickle.orders_inbox_pending
WHERE pgtrickle.inbox_is_my_partition(aggregate_id, 0, 4);

Recommended Configuration

pg_trickle.inbox_enabled = true
pg_trickle.inbox_processed_retention_hours = 72   # keep 3 days of processed msgs
pg_trickle.inbox_dlq_retention_hours = 0          # keep DLQ forever for forensics
pg_trickle.inbox_dlq_alert_max_per_refresh = 10   # alert on DLQ growth

Anti-Patterns

• Not marking messages as processed: The _pending stream table will keep growing. Always set processed_at = now() after successful processing.
• Ignoring the DLQ: Monitor orders_inbox_dlq and replay or investigate failed messages regularly. Use inbox_health() in your alerting pipeline.
• Skipping idempotency: The inbox uses event_id for deduplication. Producers must supply stable, unique event_id values — typically a UUID derived from the source event.

When NOT to use this pattern

• Message volume is very high (>10,000/s) — the inbox table becomes a hot bottleneck; consider a dedicated message queue (Kafka, NATS) fronting a batch INSERT into the inbox.
• Processing is purely stateless and idempotency is guaranteed by the producer — writing to an inbox and querying _pending adds latency without benefit over direct INSERT + trigger.
• The event source already provides at-least-once with dedup — a second layer of dedup in the inbox wastes storage.

See also: Use Cases · Performance Cookbook · SQL Reference · Tutorials: What Happens on INSERT · Outbox Pattern · Inbox Pattern

Mental Model: How pg_trickle Works

This document explains the core concepts behind pg_trickle's differential view maintenance engine for developers who know SQL but have not studied incremental view maintenance (IVM) theory. Analogies come before formulas.

1. The Problem: Expensive Full Recomputation

A standard PostgreSQL materialized view is a snapshot. When the source data changes, you call REFRESH MATERIALIZED VIEW and PostgreSQL re-runs the entire defining query — scanning every row in every source table, applying all the joins, filtering, and aggregating — every time.

For a billion-row orders table with 100 new orders since the last refresh, this is the equivalent of re-reading an entire library to update one paragraph.

pg_trickle solves this with differential maintenance: compute only the change in the view output caused by the change in the inputs.

2. The Key Insight: Deltas Are Just Rows

Think of a change to a table as a signed multiset of rows:

• +1 for an inserted row
• -1 for a deleted row
• -1 for the old version of an updated row, +1 for the new version

If the source table T changes by a delta ΔT, and the view V = f(T), then the view output changes by ΔV = f(T + ΔT) - f(T).

For many SQL operators, ΔV can be computed without reading T at all — only ΔT is needed. For a simple SELECT * FROM orders WHERE status = 'active':

• ΔV = { new rows in ΔT where status = 'active' } - { deleted rows in ΔT where status = 'active' }

For a COUNT(*) aggregate, the delta is even simpler:

• Δcount = (inserted active rows) - (deleted active rows)

This is why pg_trickle can refresh a stream table in milliseconds even when the source table has billions of rows.

3. Change Capture: The Change Buffer

Before pg_trickle can compute ΔV, it needs to know ΔT. It captures changes using row-level AFTER triggers (the default) or WAL decoding.

Each source table gets a dedicated change buffer table: pgtrickle_changes.changes_<source_table_oid>. The trigger writes every inserted, updated, or deleted row into this buffer as part of the same transaction as the DML. This gives you:

• Atomicity: A committed change is guaranteed to be in the buffer.
• No missed changes: There is no window between commit and capture.
• Snapshot isolation: The buffer holds the before/after images of each row.

The change buffer accumulates rows between refresh cycles. On each refresh, the DVM engine reads the buffer, computes the delta SQL, applies it to the stream table, and truncates the buffer.

4. The Delta SQL

For each stream table, pg_trickle pre-generates a delta SQL template at creation time. This template is parameterized by the change buffer contents and produces the ΔV rows to apply.

For a simple aggregation like:

SELECT customer_id, COUNT(*) AS order_count
FROM orders GROUP BY customer_id

The delta SQL looks roughly like:

-- Compute which customers changed in this refresh window
WITH changed_customers AS (
    SELECT DISTINCT customer_id FROM pgtrickle_changes.changes_<oid>
),
-- Recompute count for only the affected customers
new_counts AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    WHERE customer_id IN (SELECT customer_id FROM changed_customers)
    GROUP BY customer_id
)
-- Apply: delete old rows, insert new rows for changed customers
MERGE INTO stream_table AS t
USING new_counts AS s ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET order_count = s.order_count
WHEN NOT MATCHED THEN INSERT VALUES (s.customer_id, s.order_count)
WHEN NOT MATCHED BY SOURCE AND t.customer_id IN (SELECT customer_id FROM changed_customers)
    THEN DELETE;

The key property: the FROM orders scan is filtered to only the affected customer IDs, not the full table. When 10 customers out of 10 million changed, only 10 customer IDs are scanned.

5. Algebraic Operators: What Can Be Maintained Incrementally?

Not all SQL operators can be maintained in O(Δ). pg_trickle classifies them into categories:

✅ Fully Incremental (O(Δ))

• SELECT with filters, projections, casts
• INNER JOIN, LEFT JOIN (equi-join with indexed keys)
• GROUP BY with algebraic aggregates: COUNT, SUM, MIN, MAX, AVG
• DISTINCT (with reference counting)
• UNION ALL
• WHERE EXISTS / WHERE NOT EXISTS (converted to semi/anti-join)
• HAVING (filter on aggregate result)

⚠️ Conditionally Incremental

• COUNT(DISTINCT x) — incremental with algebraic Z-set counting
• STDDEV, VARIANCE — incremental using sum-of-squares decomposition
• TOP-N (ORDER BY ... LIMIT) — incremental within the top-N window
• Multi-table joins — incremental, but delta SQL becomes larger with more tables
• CUBE / ROLLUP — expanded into UNION ALL branches, each incremental

❌ Not Incremental (falls back to FULL refresh)

• TABLESAMPLE — non-deterministic, cannot be differentiated
• VOLATILE functions (random(), now(), nextval()) in the SELECT list
• ORDER BY without LIMIT — full sort on every refresh
• FETCH FIRST without ORDER BY — non-deterministic
• Window functions in the output — planned for future support
• Recursive CTEs with CYCLE — non-terminating delta

When a query contains a non-incremental operator, pg_trickle automatically uses FULL refresh — replacing the stream table contents entirely. This is transparent to the application.

6. The Row Identity Problem

MERGE needs to know which rows in the stream table correspond to which rows in the delta. This is the row identity problem.

For stream tables with a natural primary key in the output (e.g., customer_id), the MERGE key is obvious. For aggregations without a natural key, or for queries with complex output structures, pg_trickle computes a row identity hash (__pgt_row_id) from the grouping keys or the query structure. This column is maintained automatically and is invisible in normal SELECT * queries.

7. The Refresh Cycle

Source DML → CDC trigger → change buffer (same txn)
                                 ↓
                     Scheduler background worker (async)
                                 ↓
                     delta SQL → MERGE into stream table
                                 ↓
                     Truncate change buffer

The scheduler wakes every pg_trickle.scheduler_interval_ms (default 1s), checks which stream tables are ready to refresh based on their schedule, and runs the refresh in dependency (topological) order.

Key property: The application sees a consistent read of the stream table at all times. The MERGE either has fully committed or not. There is no partial update visible to readers.

8. DAG Chaining: Stream Tables as Sources

Stream tables can themselves be sources for other stream tables, forming a directed acyclic graph (DAG) of dependencies:

orders → orders_by_customer → customer_top10
              ↑
         order_items

When orders changes, pg_trickle refreshes orders_by_customer first, then uses its delta to refresh customer_top10. Each step is O(Δ), so the full chain completes in time proportional to the number of changed rows — not the total data size.

pg_trickle detects cycles and rejects stream table definitions that would create them (unless pg_trickle.allow_circular = true, which enables fixpoint iteration for convergent circular queries).

The scheduler runs refreshes in topological order and supports parallel refresh (pg_trickle.parallel_refresh_mode = 'on', the default) to execute independent branches of the DAG concurrently.

See Also

• LIMITATIONS.md — What pg_trickle cannot do and why
• ARCHITECTURE.md — Internal module structure
• DVM_REWRITE_RULES.md — SQL rewrite passes
• DVM_OPERATORS.md — Per-operator delta rules
• PERFORMANCE_CHEATSHEET.md — Quick performance guide

pg_trickle Limitations

This document covers what pg_trickle cannot do, the constraints of DIFFERENTIAL mode, source table restrictions, and operational anti-patterns. Use the decision tree at the end to quickly determine if your use case is supported.

Unsupported SQL Constructs

The following SQL features are not supported in the defining query of a stream table. Attempting to use them will produce an UnsupportedOperator error at creation time, or at the first refresh attempt.

In DIFFERENTIAL mode only

These constructs force a fallback to FULL refresh. The stream table is created successfully, but every refresh performs a full table recomputation:

Not supported at all (any mode)

DIFFERENTIAL Mode Constraints

Source table requirements

For DIFFERENTIAL mode to work correctly, each source table must:

1. Have a primary key or unique index on the columns used as join keys. Without a reliable row identity, the MERGE step cannot match old and new versions of a row. pg_trickle can fall back to a hash-based row ID (__pgt_row_id) for sources without primary keys, but this adds overhead.

2. Not use UNLOGGED or TEMPORARY storage for the stream table output. The stream table must survive a crash-recovery cycle. Source tables can be UNLOGGED (changes are still captured by triggers).

3. Not be altered concurrently in ways that change column structure while a refresh is running. pg_trickle blocks source DDL by default (pg_trickle.block_source_ddl = true). Disabling this risks schema inconsistency between the change buffer and the stream table.

Multi-source join constraints

When a defining query joins multiple source tables:

• All join keys must be equi-joins (e.g., t1.id = t2.id). Range joins (t1.ts BETWEEN t2.start AND t2.end) force FULL mode.
• The number of delta CTEs grows with the number of sources. Queries joining 5+ large tables may hit the pg_trickle.max_diff_ctes limit. The default limit is 200; raise it or simplify the query.
• Left outer joins with nullable right-side keys add correctness complexity. pg_trickle handles them correctly, but the delta SQL is larger.

Aggregate constraints

Source Table Restrictions

Supported source types

Column type constraints

• text-typed columns named as join keys work, but are less efficient than integer or UUID keys. Use an index on the join key columns.
• jsonb columns in GROUP BY are supported but hash joins on JSONB are expensive. Consider extracting the key sub-field.
• bytea columns work in the output but cannot be used as GROUP BY keys in DIFFERENTIAL mode.

Operational Anti-Patterns

Anti-pattern 1: Very high write rates with low schedules

Problem: If a source table receives 100K inserts/second and the stream table schedule is 1 second, the change buffer accumulates 100K rows per cycle. The DIFFERENTIAL delta SQL must process all 100K rows on every refresh, which may take longer than 1 second — causing the scheduler to fall behind.

Fix:

• Increase the schedule to allow batching: schedule => '10s'
• Enable the adaptive fallback: pg_trickle.differential_max_change_ratio = 0.15 (default: fall back to FULL when > 15% of the source table changed)
• Use pg_trickle.max_delta_estimate_rows to cap delta size

Anti-pattern 2: Unbounded DAG depth

Problem: A chain of 20+ stream tables where each depends on the previous creates O(depth) sequential refresh latency on every cycle.

Fix: Flatten the DAG where possible. Use parallel refresh (pg_trickle.parallel_refresh_mode = 'on') for independent branches. Consider whether intermediate stream tables are necessary.

Anti-pattern 3: Schema changes on live sources

Problem: ALTER TABLE ... DROP COLUMN on a source table while pg_trickle is running will break the change buffer schema and cause refresh errors.

Fix: Keep pg_trickle.block_source_ddl = true (the default). This causes schema changes to fail with a descriptive error; you can then update the stream table query explicitly before re-applying the schema change.

Anti-pattern 4: Treating stream tables as application write targets

Problem: Inserting or updating rows directly in a stream table bypasses pg_trickle's refresh logic. On the next refresh, the direct writes will be overwritten.

Fix: Stream tables are read-only from the application's perspective. All writes must go through the source tables. Use the pgtrickle.repair_stream_table() function if a stream table gets into an inconsistent state.

Anti-pattern 5: Using pg_trickle.enabled = false in production

Problem: Setting pg_trickle.enabled = false globally stops all refreshes. Change buffers accumulate indefinitely. Re-enabling causes a burst refresh of all stream tables simultaneously.

Fix: Use pgtrickle.suspend_stream_table() to pause individual stream tables, or pg_trickle.drain_mode = true to stop new work while completing in-flight refreshes.

"Will This Work?" Decision Tree

Does your query use window functions in the output?
  YES → Use FULL mode (refresh_mode => 'FULL')
  NO  ↓

Does your query use volatile functions (random(), now(), nextval())?
  YES → Use FULL mode, or pre-compute the volatile value in the source
  NO  ↓

Does your query use ORDER BY without LIMIT?
  YES → Remove ORDER BY, or use LIMIT N for a Top-N stream table
  NO  ↓

Does your query use WITH RECURSIVE?
  YES → Not supported. Materialize the recursive query into a regular table first.
  NO  ↓

Do all join keys use equi-join conditions (= not BETWEEN / >=)?
  NO  → Use FULL mode, or rewrite the join condition
  YES ↓

Does every source table have a primary key or unique index on the join key?
  NO  → pg_trickle will use hash-based row IDs (slightly less efficient, but works)
  YES ↓

✅ Your query is a good candidate for DIFFERENTIAL mode.
   Use: refresh_mode => 'DIFFERENTIAL' or 'AUTO' (default).

Multi-Column NOT IN with Nullable Elements (COR-1, v0.58.0)

When a defining query contains a multi-column NOT IN subquery such as:

SELECT a, b FROM t
WHERE (a, b) NOT IN (SELECT x, y FROM s)

pg_trickle v0.55.0 introduced an optimisation that rewrites (a, b) IN (SELECT x, y …) as a SemiJoin and NOT IN as an AntiJoin. However, SQL semantics for NOT IN differ from AntiJoin semantics when either side of the comparison can be NULL: SQL propagates UNKNOWN (which excludes the outer row), whereas an AntiJoin keeps the outer row.

Behaviour: When any element on the left-hand side of the row constructor is a NULL constant, or when any column in the subquery's SELECT list is a NULL literal, pg_trickle v0.58.0 detects this condition and falls back to the subquery-based (FULL refresh) execution path, emitting a NOTICE:

NOTICE: pg_trickle: multi-column NOT IN with nullable elements cannot be
rewritten to an anti-join; falling back to subquery-based delta computation.

Workaround: Rewrite using NOT EXISTS or add explicit IS NOT NULL guards to avoid NULL-producing expressions in the row constructor.

Known Future Improvements

See Also

• MENTAL_MODEL.md — Conceptual overview of how IVM works
• CONFIGURATION.md — GUC options to tune limits
• TROUBLESHOOTING.md — Diagnosing refresh problems
• ERRORS.md — Error reference

Performance Tuning Cookbook

This document is a practical, recipe-oriented guide to squeezing the best throughput and latency out of pg_trickle stream tables. Each recipe describes why a problem occurs, when to apply it, and how to implement the fix.

Table of Contents

1. Choosing the Right Refresh Mode
2. Tuning the Scheduler Interval
3. Controlling Change-Buffer Growth
4. Accelerating Wide-Join Queries
5. Reducing Lock Contention
6. Managing Spill-to-Disk in Large Deltas
7. Speeding Up FULL Refresh with Parallelism
8. Monitoring with Prometheus
9. Partition-Aware Stream Tables
10. Adaptive Threshold Tuning
11. Canary Testing Query Changes
12. Recovering from Stale Stream Tables

1. Choosing the Right Refresh Mode

Problem: DIFFERENTIAL refresh is slower than expected, or FULL refresh keeps being chosen by the adaptive engine when you expect DIFFERENTIAL.

Diagnosis: Run the diagnostics helper:

SELECT * FROM pgtrickle.diagnose_stream_table('public.orders_mv');

Look at recommended_mode, composite_score, and change_ratio_current.

Recipe — Force DIFFERENTIAL for low-churn tables:

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

Use this when:

• change_ratio_current < 0.05 (less than 5% of rows change per tick)
• The query has no DISTINCT, EXCEPT, or INTERSECT at the top level
• The table has a suitable covering index on the join/group-by columns

Recipe — Force FULL for high-churn or complex queries:

SELECT pgtrickle.alter_stream_table(
    'public.summary_mv',
    refresh_mode => 'FULL'
);

Use this when:

• change_ratio_current > 0.30
• The query contains WITH RECURSIVE, complex GROUPING SETS, or multiple correlated subqueries

Recipe — Use AUTO (recommended default):

SELECT pgtrickle.alter_stream_table(
    'public.orders_mv',
    refresh_mode => 'AUTO'
);

AUTO switches between FULL and DIFFERENTIAL each cycle based on the adaptive cost model (pg_trickle.cost_model_safety_margin).

2. Tuning the Scheduler Interval

Problem: Stream tables are falling behind the source; refreshes are not running often enough. Or conversely, the scheduler is running too frequently, creating unnecessary load.

Diagnosis:

-- Check average staleness across all active stream tables
SELECT pgt_name, staleness_seconds
FROM pgtrickle.st_refresh_stats()
ORDER BY staleness_seconds DESC NULLS LAST;

Recipe — Reduce the poll interval for fresher data:

-- In postgresql.conf or via ALTER SYSTEM:
ALTER SYSTEM SET pg_trickle.scheduler_interval_ms = 250;
SELECT pg_reload_conf();

Minimum safe value: 250 ms. Below this, CPU overhead from the scheduler loop becomes noticeable.

Recipe — Set a per-table schedule:

-- Refresh every 30 seconds
SELECT pgtrickle.alter_stream_table('public.orders_mv', schedule => '30s');

-- Refresh using a cron expression (every 5 minutes)
SELECT pgtrickle.alter_stream_table('public.daily_agg', schedule => '*/5 * * * *');

Per-table schedules override the global poll interval for that stream table.

3. Controlling Change-Buffer Growth

Problem: The change buffer schema (pgtrickle_changes.*) keeps growing and consuming disk space.

Diagnosis:

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = current_setting('pg_trickle.change_buffer_schema', true)
                   ::text
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

Recipe — Reduce the WAL-to-buffer retention window:

-- Advance the frontier faster by refreshing more frequently
SELECT pgtrickle.alter_stream_table('public.orders_mv', schedule => '5s');

pg_trickle deletes change-buffer rows once every stream table that references the source has consumed them. Slow stream tables block cleanup.

Recipe — Enable truncate-based cleanup (faster for large buffers):

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

Uses TRUNCATE instead of DELETE when cleaning up entire partitioned change-buffer tables. Avoids bloat from frequent deletes.

4. Accelerating Wide-Join Queries

Problem: DIFFERENTIAL refresh on a query with 5+ table joins is slow.

Diagnosis:

-- Check the join scan count
SELECT pgtrickle.validate_query($$ SELECT … FROM a JOIN b JOIN c … $$);

Recipe — Enable planner hints for wide joins:

ALTER SYSTEM SET pg_trickle.planner_aggressive = on;
ALTER SYSTEM SET pg_trickle.merge_planner_hints = on;
SELECT pg_reload_conf();

This sets SET LOCAL enable_seqscan = off and SET LOCAL join_collapse_limit = 1 before the MERGE execution, forcing the planner to use indexes.

Recipe — Limit differential join depth:

SELECT pgtrickle.alter_stream_table(
    'public.complex_mv',
    max_differential_joins => 4
);

When join count exceeds max_differential_joins, pg_trickle falls back to FULL refresh instead of failing with a planning error.

Recipe — Add covering indexes on join keys:

-- The differential engine joins on __pgt_row_id; ensure the join keys
-- are indexed in both the storage table and source tables.
CREATE INDEX CONCURRENTLY ON orders (customer_id, order_date);
CREATE INDEX CONCURRENTLY ON customers (id) INCLUDE (name, region);

5. Reducing Lock Contention

Problem: lock timeout errors appear in pgt_refresh_history, or queries against the stream table are blocked during refresh.

Diagnosis:

SELECT * FROM pgtrickle.diagnose_errors('public.orders_mv') LIMIT 10;

Look for error_type = 'performance' with lock timeout in error_message.

Recipe — Increase lock timeout:

ALTER SYSTEM SET pg_trickle.lock_timeout = '5s';
SELECT pg_reload_conf();

Recipe — Use APPEND_ONLY mode for insert-only pipelines:

SELECT pgtrickle.alter_stream_table(
    'public.events_mv',
    append_only => true
);

APPEND_ONLY skips the MERGE and uses a fast INSERT … SELECT which holds locks for a much shorter time.

Recipe — Use pooler compatibility mode:

SELECT pgtrickle.alter_stream_table(
    'public.orders_mv',
    pooler_compatibility_mode => true
);

Disables prepared-statement reuse, which can cause issues with PgBouncer in transaction-pool mode.

6. Managing Spill-to-Disk in Large Deltas

Problem: Differential refresh writes large amounts of temp data, causing performance degradation.

Diagnosis:

SELECT pgt_name, last_temp_blks_written
FROM pgtrickle.st_refresh_stats();

Recipe — Increase work_mem for MERGE operations:

ALTER SYSTEM SET pg_trickle.merge_work_mem_mb = 256;
SELECT pg_reload_conf();

Recipe — Set a spill threshold to auto-switch to FULL:

-- Force FULL refresh after 3 consecutive spilling differentials
ALTER SYSTEM SET pg_trickle.spill_threshold_blocks = 10000;
ALTER SYSTEM SET pg_trickle.spill_consecutive_limit = 3;
SELECT pg_reload_conf();

After spill_consecutive_limit consecutive differential refreshes that write more than spill_threshold_blocks temp blocks, pg_trickle switches to FULL refresh for that stream table.

7. Speeding Up FULL Refresh with Parallelism

Problem: FULL refresh is slow due to large source tables.

Recipe — Enable parallel query for FULL refresh:

-- Allow more parallel workers
ALTER SYSTEM SET max_parallel_workers_per_gather = 8;
ALTER SYSTEM SET parallel_tuple_cost = 0.01;
SELECT pg_reload_conf();

pg_trickle uses INSERT INTO … SELECT … which respects the standard PostgreSQL parallel query settings.

Recipe — Enable partition-parallel refresh:

SELECT pgtrickle.alter_stream_table(
    'public.orders_mv',
    partition_by => 'region'
);

With partition_by, pg_trickle dispatches one refresh worker per partition, running them in parallel.

8. Monitoring with Prometheus

Problem: You want to monitor pg_trickle metrics with Prometheus.

Recipe — Enable the built-in metrics endpoint (v0.21.0+):

ALTER SYSTEM SET pg_trickle.metrics_port = 9188;
SELECT pg_reload_conf();

Then configure Prometheus to scrape:

scrape_configs:
  - job_name: pg_trickle
    static_configs:
      - targets: ['localhost:9188']
    metrics_path: /metrics

Available metrics:

Recipe — Check staleness via SQL (for custom alerting):

SELECT pgt_name, staleness_seconds, stale
FROM pgtrickle.st_refresh_stats()
WHERE stale = true;

9. Partition-Aware Stream Tables

Problem: A stream table over a large partitioned source is slow to refresh.

Recipe — Mirror source partitioning:

-- If the source is RANGE partitioned by order_date:
SELECT pgtrickle.create_stream_table(
    'public.orders_by_region',
    'SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id',
    partition_by => 'customer_id'
);

Recipe — Per-partition MERGE for HASH-partitioned targets:

pg_trickle automatically uses per-partition MERGE when the stream table is HASH-partitioned. No additional configuration is needed; the optimizer routes each row to the correct partition.

10. Adaptive Threshold Tuning

Problem: The adaptive engine keeps switching between FULL and DIFFERENTIAL unexpectedly.

Recipe — Widen the dead zone (less switching):

-- Require a 30% score difference before switching (default: 20%)
ALTER SYSTEM SET pg_trickle.cost_model_safety_margin = 0.30;
SELECT pg_reload_conf();

Recipe — Use self-monitoring analytics to auto-tune:

-- Let pg_trickle automatically apply threshold recommendations
ALTER SYSTEM SET pg_trickle.self_monitoring_auto_apply = 'threshold_only';
SELECT pg_reload_conf();

With threshold_only, pg_trickle applies max_delta_fraction changes from pgtrickle.df_threshold_advice when confidence is HIGH.

11. Canary Testing Query Changes

Problem: You want to change a stream table's defining query safely without impacting production.

Recipe — Use canary/shadow mode (v0.21.0+):

-- 1. Create a canary table with the new query
SELECT pgtrickle.canary_begin(
    'public.orders_mv',
    'SELECT customer_id, COUNT(*), SUM(total) FROM orders GROUP BY customer_id'
);

-- 2. Wait for the canary to populate (check status)
SELECT status FROM pgtrickle.pgt_stream_tables
WHERE pgt_name = '__pgt_canary_orders_mv';

-- 3. Compare live vs canary output
SELECT * FROM pgtrickle.canary_diff('public.orders_mv');

-- 4. If diff is empty (or acceptable), promote the canary
SELECT pgtrickle.canary_promote('public.orders_mv');

The canary_diff result will be empty when both the old and new queries produce identical output for the current source data.

12. Recovering from Stale Stream Tables

Problem: A stream table is SUSPENDED or has a large backlog of changes.

Recipe — Pause all tables, catch up, then resume:

SELECT pgtrickle.pause_all();

-- Investigate
SELECT pgt_name, status, consecutive_errors
FROM pgtrickle.pgt_stream_tables
ORDER BY consecutive_errors DESC;

-- Fix the root cause, then resume
SELECT pgtrickle.resume_all();

Recipe — Force immediate refresh on stale tables:

-- Refresh only if older than 10 minutes
SELECT pgtrickle.refresh_if_stale('public.orders_mv', '10 minutes');

Recipe — Full reinitialization after schema change:

-- If source schema changed, reinitialize to rebuild column metadata
SELECT pgtrickle.reinitialize_stream_table('public.orders_mv');

13. DVM Query Complexity Limits

Problem: Differential refresh is slower than full refresh for complex queries, especially at scale. Understanding when DIFFERENTIAL mode breaks down helps you choose the right strategy.

Three Failure Mode Categories

Which SQL Patterns Trigger Each Category

Threshold Collapse (queries like TPC-H Q05, Q07, Q08, Q09):

• Multi-table joins (4+ tables) using the cascading EXCEPT ALL delta strategy
• Queries with many intermediate join nodes generate exponential intermediate rows
• Diagnosis: pgtrickle.log_delta_sql = on + EXPLAIN (ANALYZE, BUFFERS)

Early Collapse (queries like TPC-H Q04):

• WHERE EXISTS (SELECT 1 FROM t WHERE t.key = outer.key AND t.col < t.col2)
• The non-equi predicates in the EXISTS clause can prevent key-filter extraction
• Diagnosis: Check if R_old CTE scans the full right table

Structural Bug (queries like TPC-H Q20):

• WHERE EXISTS (SELECT 1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE ...))
• Inner snapshot CTEs are re-evaluated per outer row instead of shared
• Diagnosis: Look for repeated CTE evaluations in EXPLAIN ANALYZE

Recommended Scale Factors

Diagnosing Your Query

-- 1. Enable delta SQL logging
SET pg_trickle.log_delta_sql = on;

-- 2. Trigger a manual refresh
SELECT pgtrickle.refresh_stream_table('my_stream_table');

-- 3. Check the PostgreSQL log for the generated delta SQL
-- 4. Run EXPLAIN (ANALYZE, BUFFERS) on the captured SQL
-- 5. Look for:
--    - Nested Loop joins on large tables (threshold collapse)
--    - Sequential scans on R_old CTEs (early collapse)
--    - Repeated CTE evaluations (structural bug)

-- Use explain_diff_sql() to inspect without executing:
SELECT pgtrickle.explain_diff_sql('my_stream_table');

Mitigation GUCs

-- Increase work_mem for delta execution
SET pg_trickle.delta_work_mem = 256;  -- MB

-- Disable nested loops for delta execution
SET pg_trickle.delta_enable_nestloop = off;

-- Run ANALYZE on change buffers (enabled by default)
SET pg_trickle.analyze_before_delta = on;

Worked Example A — max_diff_ctes Hit and Recovery

Symptom: EXPLAIN ANALYZE shows more than pg_trickle.max_diff_ctes (default 64) CTEs in the generated delta SQL, and the refresh falls back to FULL mode with a warning in pgt_refresh_history.

Diagnosis:

-- Check the warning in the refresh history
SELECT pgt_name, refresh_mode, rows_in_last_refresh, warning_message
FROM pgtrickle.pgt_refresh_history
WHERE pgt_name = 'my_complex_view'
ORDER BY started_at DESC
LIMIT 5;

-- Inspect the generated delta SQL
SELECT pgtrickle.explain_diff_sql('my_complex_view');
-- Count the CTE blocks in the output

Recovery steps:

-- Option 1: Raise the limit (accept higher delta execution cost)
ALTER SYSTEM SET pg_trickle.max_diff_ctes = 128;
SELECT pg_reload_conf();

-- Option 2: Simplify the query — split a complex view into two stream tables
-- First level: join + filter
SELECT pgtrickle.create_stream_table(
    'orders_with_products',
    'SELECT o.*, p.name AS product_name FROM orders o JOIN products p ON p.id = o.product_id',
    '10s', 'DIFFERENTIAL'
);
-- Second level: aggregate over the first
SELECT pgtrickle.create_stream_table(
    'revenue_summary',
    'SELECT product_name, SUM(amount) AS total FROM orders_with_products GROUP BY product_name',
    '15s', 'DIFFERENTIAL'
);

-- Option 3: Force FULL mode for queries that genuinely exceed complexity budget
SELECT pgtrickle.alter_stream_table('my_complex_view', refresh_mode => 'FULL');

Worked Example B — Detecting When FULL Beats DIFFERENTIAL

Symptom: The AUTO cost model keeps switching between FULL and DIFFERENTIAL every few cycles, or diff_speedup from refresh_efficiency() is below 1.5×.

Diagnosis using recommend_refresh_mode():

-- Get the weighted signal breakdown for the table
SELECT
    pgt_name,
    current_mode,
    recommended_mode,
    confidence,
    reason,
    jsonb_pretty(signals) AS signals
FROM pgtrickle.recommend_refresh_mode('my_table');

Examine the signals output. Key indicators that FULL is better:

Apply the recommendation:

-- Switch to FULL when composite_score < -0.15
SELECT pgtrickle.alter_stream_table('my_table', refresh_mode => 'FULL');

-- Switch to AUTO and let the cost model decide going forward
SELECT pgtrickle.alter_stream_table('my_table', refresh_mode => 'AUTO');

-- Set the switching dead-zone wider to reduce oscillation
ALTER SYSTEM SET pg_trickle.cost_model_safety_margin = 0.25;
SELECT pg_reload_conf();

Worked Example C — Deep-Join Chain and max_differential_joins

Symptom: A stream table with a 6-way JOIN is slow in DIFFERENTIAL mode, even though individual tables are small.

Diagnosis:

-- Enable delta SQL logging and trigger a refresh
SET pg_trickle.log_delta_sql = on;
SELECT pgtrickle.refresh_stream_table('deep_join_view');

-- Check effective join count reported by the engine
SELECT pgt_name, query_join_depth, last_refresh_mode_reason
FROM pgtrickle.pgt_stream_tables
WHERE pgt_name = 'deep_join_view';

Understanding the GUC:

pg_trickle.max_differential_joins (default: 4) sets the maximum number of right-side scan expansions the delta engine will attempt before falling back to FULL mode. Each additional join roughly doubles the number of delta CTE branches generated.

Tuning steps:

-- Option 1: Allow deeper join differentiation (accept higher delta cost)
ALTER SYSTEM SET pg_trickle.max_differential_joins = 6;
SELECT pg_reload_conf();

-- Option 2: Intermediate stream table to break the join chain
-- Split a 6-way join into two 3-way steps:
SELECT pgtrickle.create_stream_table(
    'join_layer_1',
    $$SELECT a.*, b.val AS b_val, c.val AS c_val
      FROM table_a a
      JOIN table_b b ON b.id = a.b_id
      JOIN table_c c ON c.id = a.c_id$$,
    '5s', 'DIFFERENTIAL'
);
SELECT pgtrickle.create_stream_table(
    'join_layer_2',
    $$SELECT l1.*, d.val AS d_val, e.val AS e_val, f.val AS f_val
      FROM join_layer_1 l1
      JOIN table_d d ON d.id = l1.d_id
      JOIN table_e e ON e.id = l1.e_id
      JOIN table_f f ON f.id = l1.f_id$$,
    '10s', 'DIFFERENTIAL'
);

-- Option 3: Verify the deep-join fast-path is eligible for a given query
SELECT pgtrickle.validate_query(
    $$<your deep-join query>$$
);

Breaking the join chain into two stream tables reduces each step to ≤3 right-side expansions, well within the default max_differential_joins = 4 limit.

See Also

• docs/CONFIGURATION.md — full GUC reference
• docs/SQL_REFERENCE.md — SQL function reference
• docs/TROUBLESHOOTING.md — common error messages and fixes
• docs/BENCHMARK.md — benchmark results and methodology
• docs/SCALING.md — guidance for large deployments

Performance Cheat Sheet

Quick reference for pg_trickle performance tuning. For in-depth explanations, see CONFIGURATION.md and PERFORMANCE_COOKBOOK.md.

Three Golden Rules

1. Measure before tuning. Use pgtrickle.explain_st('my_table') and pgtrickle.refresh_history('my_table') to understand where time is spent before adjusting any GUC.

2. DIFFERENTIAL is almost always faster for Δ-small changes. Only switch to FULL mode when the change-to-table ratio is high (> 15%) or the delta SQL is genuinely slower than a full scan on your data distribution.

3. The bottleneck is usually the change buffer or work_mem. If refreshes are slow, check pg_stat_statements for temp block writes before adjusting schedule frequency.

Top-10 GUC Quick Wins

5 FULL-Fallback Patterns and How to Fix Them

These patterns cause pg_trickle to fall back to FULL refresh automatically. Each can often be rewritten for DIFFERENTIAL support.

Pattern 1: Volatile function in SELECT

-- ❌ Forces FULL: now() is volatile
SELECT id, created_at, now() - created_at AS age FROM orders;

-- ✅ DIFFERENTIAL: compute age in the source table or exclude it
SELECT id, created_at FROM orders;
-- Then compute age in the application or in a wrapper view

Pattern 2: ORDER BY without LIMIT

-- ❌ Forces FULL: full sort on every refresh
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
ORDER BY total DESC;

-- ✅ DIFFERENTIAL: remove ORDER BY (sort in the query layer)
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id;
-- Sort in the SELECT: SELECT * FROM my_stream ORDER BY total DESC

Pattern 3: Non-equi join

-- ❌ Forces FULL: range join cannot be differentiated
SELECT e.*, s.salary_band
FROM employees e
JOIN salary_bands s ON e.salary BETWEEN s.min AND s.max;

-- ✅ DIFFERENTIAL: pre-classify salary_band in the source table
ALTER TABLE employees ADD COLUMN salary_band TEXT;
-- Update via trigger or background job
SELECT e.*, e.salary_band FROM employees e;

Pattern 4: ARRAY_AGG / STRING_AGG

-- ❌ Forces FULL: order-dependent aggregate
SELECT customer_id, STRING_AGG(product, ', ') AS products
FROM order_lines GROUP BY customer_id;

-- ✅ DIFFERENTIAL: use COUNT or a separate denormalized column
SELECT customer_id, COUNT(*) AS product_count
FROM order_lines GROUP BY customer_id;
-- If you need the array: maintain it in the source table

Pattern 5: Window function in output

-- ❌ Forces FULL: window function requires global ordering
SELECT customer_id, amount,
       RANK() OVER (ORDER BY amount DESC) AS rank
FROM orders;

-- ✅ Use a Top-N stream table instead
SELECT pgtrickle.create_stream_table(
    name    => 'top_orders',
    query   => 'SELECT customer_id, amount FROM orders ORDER BY amount DESC LIMIT 100',
    refresh_mode => 'DIFFERENTIAL'  -- pg_trickle supports ORDER BY LIMIT
);

Refresh Latency Quick Diagnostics

-- See last N refresh durations for a stream table
SELECT started_at, duration_ms, mode, rows_changed
FROM pgtrickle.refresh_history('my_stream_table', limit => 20)
ORDER BY started_at DESC;

-- Check if delta is spilling to disk
SELECT query, temp_blks_written
FROM pg_stat_statements
WHERE query LIKE '%pgtrickle_changes%'
ORDER BY temp_blks_written DESC
LIMIT 10;

-- See the generated delta SQL
SELECT pgtrickle.explain_diff_sql('my_stream_table');

-- Check change buffer size
SELECT schemaname, tablename, n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = 'pgtrickle_changes'
ORDER BY n_live_tup DESC
LIMIT 10;

See Also

• CONFIGURATION.md — Full GUC reference
• PERFORMANCE_COOKBOOK.md — In-depth recipes
• MENTAL_MODEL.md — Why DIFFERENTIAL is fast
• LIMITATIONS.md — What forces FULL mode

SQL Reference

Complete reference for all SQL functions, views, and catalog tables provided by pgtrickle.

Table of Contents

• Functions
  • Core Lifecycle
    • pgtrickle.create_stream_table
    • pgtrickle.create_stream_table_if_not_exists
    • pgtrickle.create_or_replace_stream_table
    • pgtrickle.bulk_create
    • pgtrickle.alter_stream_table
    • pgtrickle.drop_stream_table
    • pgtrickle.resume_stream_table
    • pgtrickle.refresh_stream_table
    • pgtrickle.repair_stream_table
  • Status & Monitoring
    • pgtrickle.pgt_status
    • pgtrickle.health_check
    • pgtrickle.health_summary
    • pgtrickle.refresh_timeline
    • pgtrickle.st_refresh_stats
    • pgtrickle.get_refresh_history
    • pgtrickle.get_staleness
    • pgtrickle.explain_refresh_mode
    • pgtrickle.cache_stats
  • CDC Diagnostics
    • pgtrickle.slot_health
    • pgtrickle.check_cdc_health
    • pgtrickle.change_buffer_sizes
    • pgtrickle.trigger_inventory
    • pgtrickle.worker_pool_status
    • pgtrickle.parallel_job_status
    • pgtrickle.fuse_status
    • pgtrickle.reset_fuse
  • Dependency & Inspection
    • pgtrickle.dependency_tree
    • pgtrickle.diamond_groups
    • pgtrickle.pgt_scc_status
    • pgtrickle.explain_st
    • pgtrickle.list_sources
  • Utilities
    • pgtrickle.rebuild_cdc_triggers
    • pgtrickle.convert_buffers_to_unlogged
    • pgtrickle.pg_trickle_hash
    • pgtrickle.pg_trickle_hash_multi
  • Diagnostics
    • pgtrickle.recommend_refresh_mode
    • pgtrickle.refresh_efficiency
    • pgtrickle.export_definition
• Expression Support
  • Conditional Expressions
  • Comparison Operators
  • Boolean Tests
  • SQL Value Functions
  • Array and Row Expressions
  • Subquery Expressions
    • ALL (subquery) — Worked Example
  • Auto-Rewrite Pipeline
    • Window Functions in Expressions (Auto-Rewrite)
  • HAVING Clause
  • Tables Without Primary Keys (Keyless Tables)
  • Volatile Function Detection
  • COLLATE Expressions
  • IS JSON Predicate (PostgreSQL 16+)
  • SQL/JSON Constructors (PostgreSQL 16+)
  • JSON_TABLE (PostgreSQL 17+)
  • Unsupported Expression Types
• Restrictions & Interoperability
  • Referencing Other Stream Tables
  • Views as Sources in Defining Queries
  • Partitioned Tables as Sources
  • Foreign Tables as Sources
  • IMMEDIATE Mode Query Restrictions
  • Logical Replication Targets
  • Views on Stream Tables
  • Materialized Views on Stream Tables
  • Logical Replication of Stream Tables
  • Known Delta Computation Limitations
  • What Is NOT Allowed
  • Row-Level Security (RLS)
• Views
  • pgtrickle.stream_tables_info
  • pgtrickle.pg_stat_stream_tables
  • pgtrickle.quick_health
  • pgtrickle.pgt_cdc_status
• Catalog Tables
  • pgtrickle.pgt_stream_tables
  • pgtrickle.pgt_dependencies
  • pgtrickle.pgt_refresh_history
  • pgtrickle.pgt_change_tracking
  • pgtrickle.pgt_source_gates
  • pgtrickle.pgt_refresh_groups
• Delta SQL Profiling (v0.13.0)
  • pgtrickle.explain_delta
  • pgtrickle.dedup_stats
  • pgtrickle.shared_buffer_stats
• dbt Integration (v0.13.0)
  • partition_by config
  • fuse config
• Stream Table Snapshots (v0.27.0)
  • snapshot_stream_table
  • restore_from_snapshot
  • list_snapshots
  • drop_snapshot
• Transactional Outbox & Consumer Groups (v0.28.0)
  • enable_outbox
  • disable_outbox
  • outbox_status
  • create_consumer_group
  • poll_outbox
  • commit_offset
  • consumer_lag
• Transactional Inbox (v0.28.0)
  • create_inbox
  • drop_inbox
  • inbox_status
  • enable_inbox_ordering
  • inbox_is_my_partition

Functions

Core Lifecycle

Create, modify, and manage the lifecycle of stream tables.

pgtrickle.create_stream_table

Create a new stream table.

pgtrickle.create_stream_table(
    name                  text,
    query                 text,
    schedule              text      DEFAULT 'calculated',
    refresh_mode          text      DEFAULT 'AUTO',
    initialize            bool      DEFAULT true,
    diamond_consistency   text      DEFAULT NULL,
    diamond_schedule_policy text    DEFAULT NULL,
    cdc_mode              text      DEFAULT NULL,
    append_only           bool      DEFAULT false,
    pooler_compatibility_mode bool  DEFAULT false
) → void

Parameters:

When refresh_mode => 'IMMEDIATE', the cluster-wide pg_trickle.cdc_mode setting is ignored. IMMEDIATE mode always uses statement-level IVM triggers instead of CDC triggers or WAL replication slots. If you explicitly pass cdc_mode => 'wal' together with refresh_mode => 'IMMEDIATE', pg_trickle rejects the call because WAL CDC is asynchronous and incompatible with in-transaction maintenance.

Duration format:

Cron expression format:

schedule also accepts standard cron expressions for time-based scheduling. The scheduler refreshes the stream table when the cron schedule fires, rather than checking staleness.

Note: Cron-scheduled stream tables do not participate in CALCULATED schedule resolution. The stale column in monitoring views returns NULL for cron-scheduled tables.

Example:

-- Duration-based: refresh when data is staler than 2 minutes (refresh_mode defaults to 'AUTO')
SELECT pgtrickle.create_stream_table(
    name     => 'order_totals',
    query    => 'SELECT region, SUM(amount) AS total FROM orders GROUP BY region',
    schedule => '2m'
);

-- Cron-based: refresh every hour
SELECT pgtrickle.create_stream_table(
    name         => 'hourly_summary',
    query        => 'SELECT date_trunc(''hour'', ts), COUNT(*) FROM events GROUP BY 1',
    schedule     => '@hourly',
    refresh_mode => 'FULL'
);

-- Cron-based: refresh at 6 AM on weekdays
SELECT pgtrickle.create_stream_table(
    name         => 'daily_report',
    query        => 'SELECT region, SUM(revenue) AS total FROM sales GROUP BY region',
    schedule     => '0 6 * * 1-5',
    refresh_mode => 'FULL'
);

-- Immediate mode: maintained synchronously within the same transaction
-- No schedule needed — updates happen automatically when base table changes
SELECT pgtrickle.create_stream_table(
    name         => 'live_totals',
    query        => 'SELECT region, SUM(amount) AS total FROM orders GROUP BY region',
    refresh_mode => 'IMMEDIATE'
);

-- Force WAL CDC for this stream table even if the global GUC is 'trigger'
SELECT pgtrickle.create_stream_table(
    name         => 'wal_orders',
    query        => 'SELECT id, amount FROM orders',
    schedule     => '1s',
    refresh_mode => 'DIFFERENTIAL',
    cdc_mode     => 'wal'
);

Aggregate Examples:

All supported aggregate functions work in AUTO mode (and all other modes). Examples below omit refresh_mode — the default 'AUTO' selects DIFFERENTIAL automatically. Explicit modes are shown only when the mode itself is being demonstrated.

-- Algebraic aggregates (fully differential — no rescan needed)
SELECT pgtrickle.create_stream_table(
    name     => 'sales_summary',
    query    => 'SELECT region, COUNT(*) AS cnt, SUM(amount) AS total, AVG(amount) AS avg_amount
     FROM orders GROUP BY region',
    schedule => '1m'
);

-- Semi-algebraic aggregates (MIN/MAX)
SELECT pgtrickle.create_stream_table(
    name     => 'salary_ranges',
    query    => 'SELECT department, MIN(salary) AS min_sal, MAX(salary) AS max_sal
     FROM employees GROUP BY department',
    schedule => '2m'
);

-- Group-rescan aggregates (BOOL_AND/OR, STRING_AGG, ARRAY_AGG, JSON_AGG, JSONB_AGG,
--                          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, ANY_VALUE)
SELECT pgtrickle.create_stream_table(
    name     => 'team_members',
    query    => 'SELECT department,
            STRING_AGG(name, '', '' ORDER BY name) AS members,
            ARRAY_AGG(employee_id) AS member_ids,
            BOOL_AND(active) AS all_active,
            JSON_AGG(name) AS members_json
     FROM employees
     GROUP BY department',
    schedule => '1m'
);

-- Bitwise aggregates
SELECT pgtrickle.create_stream_table(
    name     => 'permission_summary',
    query    => 'SELECT department,
            BIT_OR(permissions) AS combined_perms,
            BIT_AND(permissions) AS common_perms,
            BIT_XOR(flags) AS xor_flags
     FROM employees
     GROUP BY department',
    schedule => '1m'
);

-- JSON object aggregates
SELECT pgtrickle.create_stream_table(
    name     => 'config_map',
    query    => 'SELECT department,
            JSON_OBJECT_AGG(setting_name, setting_value) AS settings,
            JSONB_OBJECT_AGG(key, value) AS metadata
     FROM config
     GROUP BY department',
    schedule => '1m'
);

-- Statistical aggregates
SELECT pgtrickle.create_stream_table(
    name     => 'salary_stats',
    query    => 'SELECT department,
            STDDEV_POP(salary) AS sd_pop,
            STDDEV_SAMP(salary) AS sd_samp,
            VAR_POP(salary) AS var_pop,
            VAR_SAMP(salary) AS var_samp
     FROM employees
     GROUP BY department',
    schedule => '1m'
);

-- Ordered-set aggregates (MODE, PERCENTILE_CONT, PERCENTILE_DISC)
SELECT pgtrickle.create_stream_table(
    name     => 'salary_percentiles',
    query    => 'SELECT department,
            MODE() WITHIN GROUP (ORDER BY grade) AS most_common_grade,
            PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
            PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
     FROM employees
     GROUP BY department',
    schedule => '1m'
);

-- Regression / correlation aggregates (CORR, COVAR_*, REGR_*)
SELECT pgtrickle.create_stream_table(
    name     => 'regression_stats',
    query    => 'SELECT department,
            CORR(salary, experience) AS sal_exp_corr,
            COVAR_POP(salary, experience) AS covar_pop,
            COVAR_SAMP(salary, experience) AS covar_samp,
            REGR_SLOPE(salary, experience) AS slope,
            REGR_INTERCEPT(salary, experience) AS intercept,
            REGR_R2(salary, experience) AS r_squared,
            REGR_COUNT(salary, experience) AS regr_n
     FROM employees
     GROUP BY department',
    schedule => '1m'
);

-- ANY_VALUE aggregate (PostgreSQL 16+)
SELECT pgtrickle.create_stream_table(
    name     => 'dept_sample',
    query    => 'SELECT department, ANY_VALUE(office_location) AS sample_office
     FROM employees GROUP BY department',
    schedule => '1m'
);

-- FILTER clause on aggregates
SELECT pgtrickle.create_stream_table(
    name     => 'order_metrics',
    query    => 'SELECT region,
            COUNT(*) AS total,
            COUNT(*) FILTER (WHERE status = ''active'') AS active_count,
            SUM(amount) FILTER (WHERE status = ''shipped'') AS shipped_total
     FROM orders
     GROUP BY region',
    schedule => '1m'
);

-- PgBouncer compatibility (transaction-mode pooler)
SELECT pgtrickle.create_stream_table(
    name                      => 'pooled_orders',
    query                     => 'SELECT id, amount FROM orders',
    schedule                  => '5m',
    pooler_compatibility_mode => true
);

CTE Examples:

Non-recursive CTEs are fully supported in both FULL and DIFFERENTIAL modes:

-- Simple CTE
SELECT pgtrickle.create_stream_table(
    name     => 'active_order_totals',
    query    => 'WITH active_users AS (
        SELECT id, name FROM users WHERE active = true
    )
    SELECT a.id, a.name, SUM(o.amount) AS total
    FROM active_users a
    JOIN orders o ON o.user_id = a.id
    GROUP BY a.id, a.name',
    schedule => '1m'
);

-- Chained CTEs (CTE referencing another CTE)
SELECT pgtrickle.create_stream_table(
    name     => 'top_regions',
    query    => 'WITH regional AS (
        SELECT region, SUM(amount) AS total FROM orders GROUP BY region
    ),
    ranked AS (
        SELECT region, total FROM regional WHERE total > 1000
    )
    SELECT * FROM ranked',
    schedule => '2m'
);

-- Multi-reference CTE (referenced twice in FROM — shared delta optimization)
SELECT pgtrickle.create_stream_table(
    name     => 'self_compare',
    query    => 'WITH totals AS (
        SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
    )
    SELECT t1.user_id, t1.total, t2.total AS next_total
    FROM totals t1
    JOIN totals t2 ON t1.user_id = t2.user_id + 1',
    schedule => '1m'
);

-- Append-only stream table (INSERT-only fast path)
SELECT pgtrickle.create_stream_table(
    name        => 'event_log_st',
    query       => 'SELECT id, event_type, payload, created_at FROM events',
    schedule    => '30s',
    append_only => true
);

Recursive CTEs work with FULL, DIFFERENTIAL, and IMMEDIATE modes:

-- Recursive CTE (hierarchy traversal)
SELECT pgtrickle.create_stream_table(
    name         => 'category_tree',
    query        => 'WITH RECURSIVE cat_tree AS (
        SELECT id, name, parent_id, 0 AS depth
        FROM categories WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id, c.name, c.parent_id, ct.depth + 1
        FROM categories c
        JOIN cat_tree ct ON c.parent_id = ct.id
    )
    SELECT * FROM cat_tree',
    schedule     => '5m',
    refresh_mode => 'FULL'  -- FULL mode: standard re-execution
);

-- Recursive CTE with DIFFERENTIAL mode (incremental semi-naive / DRed)
SELECT pgtrickle.create_stream_table(
    name         => 'org_chart',
    query        => 'WITH RECURSIVE reports AS (
        SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
        UNION ALL
        SELECT e.id, e.name, e.manager_id
        FROM employees e JOIN reports r ON e.manager_id = r.id
    )
    SELECT * FROM reports',
    schedule     => '2m',
    refresh_mode => 'DIFFERENTIAL'  -- Uses semi-naive, DRed, or recomputation (auto-selected)
);

-- Recursive CTE with IMMEDIATE mode (same-transaction maintenance)
SELECT pgtrickle.create_stream_table(
    name         => 'org_chart_live',
    query        => 'WITH RECURSIVE reports AS (
        SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
        UNION ALL
        SELECT e.id, e.name, e.manager_id
        FROM employees e JOIN reports r ON e.manager_id = r.id
    )
    SELECT * FROM reports',
    refresh_mode => 'IMMEDIATE'  -- Uses transition tables with semi-naive / DRed maintenance
);

Non-monotone recursive terms: If the recursive term contains operators like EXCEPT, aggregate functions, window functions, DISTINCT, INTERSECT (set), or anti-joins, the system automatically falls back to recomputation to guarantee correctness. Semi-naive and DRed strategies require monotone recursive terms (JOIN, UNION ALL, filter/project only).

Set Operation Examples:

INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL, UNION, and UNION ALL are supported:

-- INTERSECT: customers who placed orders in BOTH regions
SELECT pgtrickle.create_stream_table(
    name     => 'bi_region_customers',
    query    => 'SELECT customer_id FROM orders_east
     INTERSECT
     SELECT customer_id FROM orders_west',
    schedule => '2m'
);

-- INTERSECT ALL: preserves duplicates (bag semantics)
SELECT pgtrickle.create_stream_table(
    name     => 'common_items',
    query    => 'SELECT item_name FROM warehouse_a
     INTERSECT ALL
     SELECT item_name FROM warehouse_b',
    schedule => '1m'
);

-- EXCEPT: orders not yet shipped
SELECT pgtrickle.create_stream_table(
    name     => 'unshipped_orders',
    query    => 'SELECT order_id FROM orders
     EXCEPT
     SELECT order_id FROM shipments',
    schedule => '1m'
);

-- EXCEPT ALL: preserves duplicate counts (bag subtraction)
SELECT pgtrickle.create_stream_table(
    name     => 'excess_inventory',
    query    => 'SELECT sku FROM stock_received
     EXCEPT ALL
     SELECT sku FROM stock_shipped',
    schedule => '5m'
);

-- UNION: deduplicated merge of two sources
SELECT pgtrickle.create_stream_table(
    name     => 'all_contacts',
    query    => 'SELECT email FROM customers
     UNION
     SELECT email FROM newsletter_subscribers',
    schedule => '5m'
);

LATERAL Set-Returning Function Examples:

Set-returning functions (SRFs) in the FROM clause are supported in both FULL and DIFFERENTIAL modes. Common SRFs include jsonb_array_elements, jsonb_each, jsonb_each_text, and unnest:

-- Flatten JSONB arrays into rows
SELECT pgtrickle.create_stream_table(
    name     => 'flat_children',
    query    => 'SELECT p.id, child.value AS val
     FROM parent_data p,
     jsonb_array_elements(p.data->''children'') AS child',
    schedule => '1m'
);

-- Expand JSONB key-value pairs (multi-column SRF)
SELECT pgtrickle.create_stream_table(
    name     => 'flat_properties',
    query    => 'SELECT d.id, kv.key, kv.value
     FROM documents d,
     jsonb_each(d.metadata) AS kv',
    schedule => '2m'
);

-- Unnest arrays
SELECT pgtrickle.create_stream_table(
    name     => 'flat_tags',
    query    => 'SELECT t.id, tag.tag
     FROM tagged_items t,
     unnest(t.tags) AS tag(tag)',
    schedule => '1m'
);

-- SRF with WHERE filter
SELECT pgtrickle.create_stream_table(
    name     => 'high_value_items',
    query    => 'SELECT p.id, (e.value)::int AS amount
     FROM products p,
     jsonb_array_elements(p.prices) AS e
     WHERE (e.value)::int > 100',
    schedule => '5m'
);

-- SRF combined with aggregation
SELECT pgtrickle.create_stream_table(
    name         => 'element_counts',
    query        => 'SELECT a.id, count(*) AS cnt
     FROM arrays a,
     jsonb_array_elements(a.data) AS e
     GROUP BY a.id',
    schedule     => '1m',
    refresh_mode => 'FULL'
);

LATERAL Subquery Examples:

LATERAL subqueries in the FROM clause are supported in both FULL and DIFFERENTIAL modes. Use them for top-N per group, correlated aggregation, and conditional expansion:

-- Top-N per group: latest item per order
SELECT pgtrickle.create_stream_table(
    name     => 'latest_items',
    query    => 'SELECT o.id, o.customer, latest.amount
     FROM orders o,
     LATERAL (
         SELECT li.amount
         FROM line_items li
         WHERE li.order_id = o.id
         ORDER BY li.created_at DESC
         LIMIT 1
     ) AS latest',
    schedule => '1m'
);

-- Correlated aggregate
SELECT pgtrickle.create_stream_table(
    name     => 'dept_summaries',
    query    => 'SELECT d.id, d.name, stats.total, stats.cnt
     FROM departments d,
     LATERAL (
         SELECT SUM(e.salary) AS total, COUNT(*) AS cnt
         FROM employees e
         WHERE e.dept_id = d.id
     ) AS stats',
    schedule => '1m'
);

-- LEFT JOIN LATERAL: preserve outer rows with NULLs when subquery returns no rows
SELECT pgtrickle.create_stream_table(
    name     => 'dept_stats_all',
    query    => 'SELECT d.id, d.name, stats.total
     FROM departments d
     LEFT JOIN LATERAL (
         SELECT SUM(e.salary) AS total
         FROM employees e
         WHERE e.dept_id = d.id
     ) AS stats ON true',
    schedule => '1m'
);

WHERE Subquery Examples:

Subqueries in the WHERE clause are automatically transformed into semi-join, anti-join, or scalar subquery operators in the DVM operator tree:

-- EXISTS subquery: customers who have placed orders
SELECT pgtrickle.create_stream_table(
    name     => 'active_customers',
    query    => 'SELECT c.id, c.name
     FROM customers c
     WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)',
    schedule => '1m'
);

-- NOT EXISTS: customers with no orders
SELECT pgtrickle.create_stream_table(
    name     => 'inactive_customers',
    query    => 'SELECT c.id, c.name
     FROM customers c
     WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)',
    schedule => '1m'
);

-- IN subquery: products that have been ordered
SELECT pgtrickle.create_stream_table(
    name     => 'ordered_products',
    query    => 'SELECT p.id, p.name
     FROM products p
     WHERE p.id IN (SELECT product_id FROM order_items)',
    schedule => '1m'
);

-- NOT IN subquery: products never ordered
SELECT pgtrickle.create_stream_table(
    name     => 'unordered_products',
    query    => 'SELECT p.id, p.name
     FROM products p
     WHERE p.id NOT IN (SELECT product_id FROM order_items)',
    schedule => '1m'
);

-- Scalar subquery in SELECT list
SELECT pgtrickle.create_stream_table(
    name     => 'products_with_max_price',
    query    => 'SELECT p.id, p.name, (SELECT max(price) FROM products) AS max_price
     FROM products p',
    schedule => '1m'
);

Notes:

• The defining query is parsed into an operator tree and validated for DVM support.
• Views as sources — views referenced in the defining query are automatically inlined as subqueries (auto-rewrite pass #0). CDC triggers are created on the underlying base tables. Nested views (view → view → table) are fully expanded. The user's original query is preserved in original_query for reinit and introspection. Materialized views are rejected in DIFFERENTIAL mode (use FULL mode or the underlying query directly). Foreign tables are also rejected in DIFFERENTIAL mode.
• CDC triggers and change buffer tables are created automatically for each source table.
• TRUNCATE on source tables — when a source table is TRUNCATEd, a CDC trigger writes a marker row (action='T') into the change buffer. On the next refresh cycle, pg_trickle detects the marker and automatically falls back to a FULL refresh. For single-source stream tables where no subsequent DML occurred after the TRUNCATE, an optimized fast path deletes all ST rows directly without re-running the full defining query.
• The ST is registered in the dependency DAG; cycles are rejected.
• Non-recursive CTEs are inlined as subqueries during parsing (Tier 1). Multi-reference CTEs share delta computation (Tier 2).
• Recursive CTEs in DIFFERENTIAL mode use three strategies, auto-selected per refresh: semi-naive evaluation for INSERT-only changes, DRed (Delete-and-Rederive) for mixed DELETE/UPDATE changes, and recomputation fallback when CTE columns do not match ST storage columns. Non-monotone recursive terms (containing EXCEPT, Aggregate, Window, DISTINCT, AntiJoin, or INTERSECT SET) automatically fall back to recomputation to ensure correctness.

Recursive CTE DIFFERENTIAL mode -- DRed algorithm (P2-1) In DIFFERENTIAL mode, mixed DELETE/UPDATE changes now use the DRed (Delete-and-Rederive) algorithm: (1) semi-naive INSERT propagation; (2) over-deletion cascade from ST storage; (3) rederivation from current source tables; (4) combine net deletions. DRed correctly handles derived-column changes such as path rebuilds under a renamed ancestor node. When CTE output columns differ from ST storage columns (mismatch), recomputation is used. Implemented in v0.10.0 (P2-1).

• LATERAL SRFs in DIFFERENTIAL mode use row-scoped recomputation: when a source row changes, only the SRF expansions for that row are re-evaluated.
• LATERAL subqueries in DIFFERENTIAL mode also use row-scoped recomputation: when an outer row changes, the correlated subquery is re-executed only for that row.
• WHERE subqueries (EXISTS, IN, scalar) are parsed into dedicated semi-join, anti-join, and scalar subquery operators with specialized delta computation.
• ALL (subquery) is the only subquery form that is currently rejected.
• ORDER BY is accepted but silently discarded — row order in the storage table is undefined (consistent with PostgreSQL's CREATE MATERIALIZED VIEW behavior). Apply ORDER BY when querying the stream table.
• TopK (ORDER BY + LIMIT) — When a top-level ORDER BY … LIMIT N is present (with a constant integer limit, optionally with OFFSET M), the query is recognized as a "TopK" pattern and accepted. TopK stream tables store exactly N rows (starting from position M+1 if OFFSET is specified) and are refreshed via a scoped-recomputation MERGE strategy. The DVM delta pipeline is bypassed; instead, each refresh re-evaluates the full ORDER BY + LIMIT [+ OFFSET] query and merges the result into the storage table. The catalog records topk_limit, topk_order_by, and optionally topk_offset for the stream table. TopK is not supported with set operations (UNION/INTERSECT/EXCEPT) or GROUP BY ROLLUP/CUBE/GROUPING SETS.
• LIMIT / OFFSET without ORDER BY are rejected — stream tables materialize the full result set. Apply LIMIT when querying the stream table.

pgtrickle.create_stream_table_if_not_exists

Create a stream table if it does not already exist. If a stream table with the given name already exists, this is a silent no-op (an INFO message is logged). The existing definition is never modified.

pgtrickle.create_stream_table_if_not_exists(
    name                    text,
    query                   text,
    schedule                text      DEFAULT 'calculated',
    refresh_mode            text      DEFAULT 'AUTO',
    initialize              bool      DEFAULT true,
    diamond_consistency     text      DEFAULT NULL,
    diamond_schedule_policy text      DEFAULT NULL,
    cdc_mode                text      DEFAULT NULL,
    append_only             bool      DEFAULT false,
    pooler_compatibility_mode bool    DEFAULT false
) → void

Parameters: Same as create_stream_table.

Example:

-- Safe to re-run in migrations:
SELECT pgtrickle.create_stream_table_if_not_exists(
    'order_totals',
    'SELECT customer_id, sum(amount) AS total FROM orders GROUP BY customer_id',
    '1m',
    'DIFFERENTIAL'
);

Notes:

• Useful for deployment / migration scripts that should be safe to re-run.
• If the stream table already exists, the provided query, schedule, and other parameters are ignored — the existing definition is preserved.

pgtrickle.create_or_replace_stream_table

Create a stream table if it does not exist, or replace the existing one if the definition changed. This is the declarative, idempotent API for deployment workflows (dbt, SQL migrations, GitOps).

pgtrickle.create_or_replace_stream_table(
    name                    text,
    query                   text,
    schedule                text      DEFAULT 'calculated',
    refresh_mode            text      DEFAULT 'AUTO',
    initialize              bool      DEFAULT true,
    diamond_consistency     text      DEFAULT NULL,
    diamond_schedule_policy text      DEFAULT NULL,
    cdc_mode                text      DEFAULT NULL,
    append_only             bool      DEFAULT false,
    pooler_compatibility_mode bool    DEFAULT false
) → void

Parameters: Same as create_stream_table.

Behavior:

The initialize parameter is honoured on create only. On replace, the stream table is always repopulated via a full refresh.

Query comparison uses the post-rewrite (normalized) form of the SQL. Cosmetic differences such as whitespace, casing, and extra parentheses are ignored.

Example:

-- Idempotent deployment — safe to run on every deploy:
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'
);

-- If the query changed since last deploy, the stream table is
-- migrated in place (no data gap). If nothing changed, it's a no-op.

Notes:

• Mirrors PostgreSQL's CREATE OR REPLACE convention (CREATE OR REPLACE VIEW, CREATE OR REPLACE FUNCTION).
• Never drops the stream table — even for incompatible schema changes, the ALTER QUERY path rebuilds storage in place while preserving the catalog entry (pgt_id).
• For migration scripts that should not modify an existing definition, use create_stream_table_if_not_exists instead.

pgtrickle.bulk_create

Create multiple stream tables in a single transaction.

pgtrickle.bulk_create(
    definitions  jsonb     -- Array of stream table definitions
) → jsonb                  -- Array of result objects

Each element in the definitions array must be a JSON object with at least name and query keys. All other keys match the parameters of create_stream_table (snake_case):

Returns a JSONB array of result objects:

[
  {"name": "st1", "status": "created", "pgt_id": 42},
  {"name": "st2", "status": "created", "pgt_id": 43}
]

On any error, the entire transaction is rolled back (standard PostgreSQL transactional semantics). The error message includes the index and name of the failing definition.

Example:

SELECT pgtrickle.bulk_create('[
  {"name": "order_totals", "query": "SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id", "schedule": "30s"},
  {"name": "product_stats", "query": "SELECT product_id, COUNT(*) AS cnt FROM order_items GROUP BY product_id", "schedule": "1m"}
]'::jsonb);

pgtrickle.alter_stream_table

Alter properties of an existing stream table.

pgtrickle.alter_stream_table(
    name                  text,
    query                 text      DEFAULT NULL,
    schedule              text      DEFAULT NULL,
    refresh_mode          text      DEFAULT NULL,
    status                text      DEFAULT NULL,
    diamond_consistency   text      DEFAULT NULL,
    diamond_schedule_policy text    DEFAULT NULL,
    cdc_mode              text      DEFAULT NULL,
    append_only           bool      DEFAULT NULL,
    pooler_compatibility_mode bool  DEFAULT NULL,
    tier                  text      DEFAULT NULL
) → void

Parameters:

If you switch a stream table to refresh_mode => 'IMMEDIATE' while the cluster-wide pg_trickle.cdc_mode GUC is set to 'wal', pg_trickle logs an INFO and proceeds with IVM triggers. WAL CDC does not apply to IMMEDIATE mode. If the stream table has an explicit cdc_mode => 'wal' override, switching to IMMEDIATE is rejected until you change the requested CDC mode back to 'auto' or 'trigger'.

Examples:

-- Change the defining query (same output schema — fast path)
SELECT pgtrickle.alter_stream_table('order_totals',
    query => 'SELECT customer_id, SUM(amount) AS total FROM orders WHERE status = ''active'' GROUP BY customer_id');

-- Change query and add a column (compatible schema migration)
SELECT pgtrickle.alter_stream_table('order_totals',
    query => 'SELECT customer_id, SUM(amount) AS total, COUNT(*) AS cnt FROM orders GROUP BY customer_id');

-- Change query and mode 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');

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

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

-- Switch to immediate (transactional) mode — installs IVM triggers, clears schedule
SELECT pgtrickle.alter_stream_table('order_totals', refresh_mode => 'IMMEDIATE');

-- Switch from immediate back to differential — re-creates CDC triggers, restores schedule
SELECT pgtrickle.alter_stream_table('order_totals',
    refresh_mode => 'DIFFERENTIAL', schedule => '5m');

-- Pin a deferred stream table to trigger CDC even when the global GUC is 'auto'
SELECT pgtrickle.alter_stream_table('order_totals', cdc_mode => 'trigger');

-- Enable append-only INSERT fast path
SELECT pgtrickle.alter_stream_table('event_log_st', append_only => true);

-- Enable pooler compatibility mode (for PgBouncer transaction mode)
SELECT pgtrickle.alter_stream_table('order_totals', pooler_compatibility_mode => true);

-- Set refresh tier (requires pg_trickle.tiered_scheduling = on)
SELECT pgtrickle.alter_stream_table('order_totals', tier => 'warm');
SELECT pgtrickle.alter_stream_table('archive_stats', tier => 'frozen');

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

-- Resume a suspended stream table
SELECT pgtrickle.resume_stream_table('order_totals');
-- Or via alter_stream_table
SELECT pgtrickle.alter_stream_table('order_totals', status => 'ACTIVE');

Notes:

• When query is provided, the function runs the full query rewrite pipeline (view inlining, DISTINCT ON, GROUPING SETS, etc.) and validates the new query before applying changes.
• The entire ALTER QUERY operation runs within a single transaction. If any step fails, the stream table is left unchanged.
• For same-schema and compatible-schema changes, the storage table OID is preserved — views, policies, and publications referencing the stream table remain valid.
• For incompatible schema changes (e.g., changing a column from integer to text), the storage table is rebuilt and the OID changes. A WARNING is emitted.
• The stream table is temporarily suspended during query migration to prevent concurrent scheduler refreshes.

pgtrickle.drop_stream_table

Drop a stream table, removing the storage table and all catalog entries.

pgtrickle.drop_stream_table(name text) → void

Parameters:

Example:

SELECT pgtrickle.drop_stream_table('order_totals');

Notes:

• Drops the underlying storage table with CASCADE.
• Removes all catalog entries (metadata, dependencies, refresh history).
• Cleans up CDC triggers and change buffer tables for source tables that are no longer tracked by any ST.
• Automatically drops any downstream publication created by stream_table_to_publication().

pgtrickle.stream_table_to_publication

Create a PostgreSQL logical replication publication for a stream table, enabling downstream consumers (Debezium, Kafka Connect, standby replicas) to subscribe to changes.

pgtrickle.stream_table_to_publication(name text) → void

Parameters:

Example:

SELECT pgtrickle.stream_table_to_publication('order_totals');
-- Creates publication 'pgt_pub_order_totals'

Notes:

• The publication is named pgt_pub_<table_name>.
• Only one publication per stream table is allowed.
• The publication is automatically dropped when the stream table is dropped.

pgtrickle.drop_stream_table_publication

Drop the logical replication publication for a stream table.

pgtrickle.drop_stream_table_publication(name text) → void

Parameters:

Example:

SELECT pgtrickle.drop_stream_table_publication('order_totals');

pgtrickle.set_stream_table_sla

Assign a freshness deadline SLA to a stream table. The extension automatically assigns the appropriate refresh tier based on the SLA.

pgtrickle.set_stream_table_sla(name text, sla interval) → void

Parameters:

Tier assignment:

• SLA ≤ 5 seconds → Hot tier
• SLA ≤ 30 seconds → Warm tier
• SLA > 30 seconds → Cold tier

Example:

SELECT pgtrickle.set_stream_table_sla('order_totals', interval '10 seconds');
-- Assigns Warm tier

Notes:

• The scheduler periodically checks actual refresh performance and dynamically re-assigns tiers if the SLA is consistently breached or over-served.

pgtrickle.resume_stream_table

Resume a suspended stream table, clearing its consecutive error count and re-enabling automated and manual refreshes.

pgtrickle.resume_stream_table(name text) → void

Parameters:

Example:

-- Resume a stream table that was auto-suspended due to repeated errors
SELECT pgtrickle.resume_stream_table('order_totals');

Notes:

• Errors if the ST is not in SUSPENDED state.
• Resets consecutive_errors to 0 and sets status = 'ACTIVE'.
• Emits a resumed event on the pg_trickle_alert NOTIFY channel.
• After resuming, the scheduler will include the ST in its next cycle.

pgtrickle.refresh_stream_table

Manually trigger a synchronous refresh of a stream table.

pgtrickle.refresh_stream_table(name text) → void

Parameters:

Example:

SELECT pgtrickle.refresh_stream_table('order_totals');

Notes:

• Blocked if the ST is SUSPENDED — use pgtrickle.resume_stream_table(name) first.
• Uses an advisory lock to prevent concurrent refreshes of the same ST.
• For DIFFERENTIAL mode, generates and applies a delta query. For FULL mode, truncates and reloads.
• Records the refresh in pgtrickle.pgt_refresh_history with initiated_by = 'MANUAL'.

pgtrickle.repair_stream_table

Repair a stream table by reinstalling any missing CDC triggers, validating catalog entries, and reconciling change buffer state.

pgtrickle.repair_stream_table(name text) → text

Parameters:

Example:

-- Reinstall missing CDC triggers after a point-in-time recovery
SELECT pgtrickle.repair_stream_table('order_totals');

Notes:

• Inspects all source tables in the stream table's dependency graph and reinstalls any missing or disabled CDC triggers.
• Validates that the stream table's catalog entry, storage table, and change buffer tables are consistent.
• Useful after pg_basebackup or PITR restores where triggers may not have been captured in the backup.
• Use pgtrickle.trigger_inventory() first to identify which triggers are missing.
• Safe to call on a healthy stream table — it is a no-op if everything is intact.

Status & Monitoring

Query the state of stream tables, view refresh statistics, and diagnose problems.

pgtrickle.pgt_status

Get the status of all stream tables.

pgtrickle.pgt_status() → SETOF record(
    name                text,
    status              text,
    refresh_mode        text,
    is_populated        bool,
    consecutive_errors  int,
    schedule            text,
    data_timestamp      timestamptz,
    staleness           interval
)

Example:

SELECT * FROM pgtrickle.pgt_status();

pgtrickle.health_check

Run a set of health checks against the pg_trickle installation and return one row per check.

pgtrickle.health_check() → SETOF record(
    check_name  text,   -- identifier for the check
    severity    text,   -- 'OK', 'WARN', or 'ERROR'
    detail      text    -- human-readable explanation
)

Filter to problems only:

SELECT check_name, severity, detail
FROM pgtrickle.health_check()
WHERE severity != 'OK';

Checks: scheduler_running, error_tables, stale_tables, needs_reinit, consecutive_errors, buffer_growth (> 10 000 pending rows), slot_lag (retained WAL above pg_trickle.slot_lag_warning_threshold_mb, default 100 MB), worker_pool (all worker tokens in use — parallel mode only), job_queue (> 10 jobs queued — parallel mode only).

pgtrickle.health_summary

Single-row summary of the entire pg_trickle deployment's health. Designed for monitoring dashboards that want one endpoint to poll instead of joining multiple views.

pgtrickle.health_summary() → SETOF record(
    total_stream_tables   int,
    active_count          int,
    error_count           int,
    suspended_count       int,
    stale_count           int,
    reinit_pending        int,
    max_staleness_seconds float8,    -- NULL if no stream tables
    scheduler_status      text,      -- 'ACTIVE', 'STOPPED', or 'NOT_LOADED'
    cache_hit_rate        float8     -- NULL if no cache lookups yet
)

Example:

SELECT * FROM pgtrickle.health_summary();

Tip: Use this in a Grafana single-stat panel or a Prometheus exporter to surface fleet-level health at a glance.

pgtrickle.refresh_timeline

Return recent refresh records across all stream tables in a single chronological view.

pgtrickle.refresh_timeline(
    max_rows int  DEFAULT 50
) → SETOF record(
    start_time      timestamptz,
    stream_table    text,
    action          text,
    status          text,
    rows_inserted   bigint,
    rows_deleted    bigint,
    duration_ms     float8,
    error_message   text
)

Example:

-- Most recent 20 events across all stream tables:
SELECT start_time, stream_table, action, status, round(duration_ms::numeric,1) AS ms
FROM pgtrickle.refresh_timeline(20);

-- Just failures in the last 100 events:
SELECT * FROM pgtrickle.refresh_timeline(100) WHERE status = 'ERROR';

pgtrickle.st_refresh_stats

Return per-ST refresh statistics aggregated from the refresh history.

pgtrickle.st_refresh_stats() → SETOF record(
    pgt_name                text,
    pgt_schema              text,
    status                 text,
    refresh_mode           text,
    is_populated           bool,
    total_refreshes        bigint,
    successful_refreshes   bigint,
    failed_refreshes       bigint,
    total_rows_inserted    bigint,
    total_rows_deleted     bigint,
    avg_duration_ms        float8,
    last_refresh_action    text,
    last_refresh_status    text,
    last_refresh_at        timestamptz,
    staleness_secs       float8,
    stale           bool
)

Example:

SELECT pgt_name, status, total_refreshes, avg_duration_ms, stale
FROM pgtrickle.st_refresh_stats();

pgtrickle.get_refresh_history

Return refresh history for a specific stream table.

pgtrickle.get_refresh_history(
    name      text,
    max_rows  int  DEFAULT 20
) → SETOF record(
    refresh_id       bigint,
    data_timestamp   timestamptz,
    start_time       timestamptz,
    end_time         timestamptz,
    action           text,
    status           text,
    rows_inserted    bigint,
    rows_deleted     bigint,
    duration_ms      float8,
    error_message    text
)

Example:

SELECT action, status, rows_inserted, duration_ms
FROM pgtrickle.get_refresh_history('order_totals', 5);

pgtrickle.get_staleness

Get the current staleness in seconds for a specific stream table.

pgtrickle.get_staleness(name text) → float8

Returns NULL if the ST has never been refreshed.

Example:

SELECT pgtrickle.get_staleness('order_totals');
-- Returns: 12.345  (seconds since last refresh)

pgtrickle.explain_refresh_mode

Added in v0.11.0

Explain the configured vs. effective refresh mode for a stream table, including the reason for any downgrade (e.g., AUTO choosing FULL).

pgtrickle.explain_refresh_mode(name text) → TABLE(
    configured_mode  text,
    effective_mode   text,
    downgrade_reason text
)

Columns:

Example:

SELECT * FROM pgtrickle.explain_refresh_mode('public.orders_summary');

pgtrickle.cache_stats

Return template cache statistics from shared memory.

Reports L1 (thread-local) hits, L2 (catalog table) hits, full misses (DVM re-parse), evictions (generation flushes), and the current L1 cache size for this backend.

pgtrickle.cache_stats() → SETOF record(
    l1_hits    bigint,
    l2_hits    bigint,
    misses     bigint,
    evictions  bigint,
    l1_size    integer
)

Example:

SELECT * FROM pgtrickle.cache_stats();

Note: Counters are cluster-wide (shared memory) except l1_size which is per-backend. Requires shared_preload_libraries = 'pg_trickle'; returns zeros when loaded dynamically.

CDC Diagnostics

Inspect CDC pipeline health, replication slots, change buffers, and trigger coverage.

pgtrickle.slot_health

Check replication slot health for all tracked CDC slots.

pgtrickle.slot_health() → SETOF record(
    slot_name          text,
    source_relid       bigint,
    active             bool,
    retained_wal_bytes bigint,
    wal_status         text
)

Example:

SELECT * FROM pgtrickle.slot_health();

pgtrickle.check_cdc_health

Check CDC health for all tracked source tables. Returns per-source health status including the current CDC mode, replication slot details, estimated lag, and any alerts.

The alert column uses the critical threshold configured by pg_trickle.slot_lag_critical_threshold_mb (default 1024 MB).

pgtrickle.check_cdc_health() → SETOF record(
    source_relid   bigint,
    source_table   text,
    cdc_mode       text,
    slot_name      text,
    lag_bytes      bigint,
    confirmed_lsn  text,
    alert          text
)

Columns:

Example:

SELECT * FROM pgtrickle.check_cdc_health();

pgtrickle.change_buffer_sizes

Show pending change counts and estimated on-disk sizes for all CDC-tracked source tables.

Returns one row per (stream_table, source_table) pair.

pgtrickle.change_buffer_sizes() → SETOF record(
    stream_table  text,     -- qualified stream table name
    source_table  text,     -- qualified source table name
    source_oid    bigint,
    cdc_mode      text,     -- 'trigger', 'wal', or 'transitioning'
    pending_rows  bigint,   -- rows in buffer not yet consumed
    buffer_bytes  bigint    -- estimated buffer table size in bytes
)

Example:

SELECT * FROM pgtrickle.change_buffer_sizes()
ORDER BY pending_rows DESC;

Useful for spotting a source table whose CDC buffer is growing unexpectedly (which may indicate a stalled differential refresh or a high-write source that has outpaced the schedule).

pgtrickle.worker_pool_status

Snapshot of the parallel refresh worker pool. Returns a single row.

pgtrickle.worker_pool_status() → SETOF record(
    active_workers  int,   -- workers currently executing refresh jobs
    max_workers     int,   -- cluster-wide worker budget (GUC)
    per_db_cap      int,   -- per-database dispatch cap (GUC)
    parallel_mode   text   -- current parallel_refresh_mode value
)

Example:

SELECT * FROM pgtrickle.worker_pool_status();

Returns 0 active workers when parallel_refresh_mode = 'off'.

pgtrickle.parallel_job_status

Active and recently completed scheduler jobs from the pgt_scheduler_jobs table. Shows jobs that are currently queued or running, plus jobs that finished within the last max_age_seconds (default 300).

pgtrickle.parallel_job_status(
    max_age_seconds int  DEFAULT 300
) → SETOF record(
    job_id         bigint,
    unit_key       text,        -- stable unit identifier (s:42, a:1,2, etc.)
    unit_kind      text,        -- 'singleton', 'atomic_group', 'immediate_closure'
    status         text,        -- 'QUEUED', 'RUNNING', 'SUCCEEDED', etc.
    member_count   int,
    attempt_no     int,
    scheduler_pid  int,
    worker_pid     int,         -- NULL if not yet claimed
    enqueued_at    timestamptz,
    started_at     timestamptz, -- NULL if still queued
    finished_at    timestamptz, -- NULL if not finished
    duration_ms    float8       -- NULL if not finished
)

Example — show running and recently failed jobs:

SELECT job_id, unit_key, status, duration_ms
FROM pgtrickle.parallel_job_status(60)
WHERE status NOT IN ('SUCCEEDED');

pgtrickle.trigger_inventory

List all CDC triggers that pg_trickle should have installed, and verify each one exists and is enabled in pg_catalog.

pgtrickle.trigger_inventory() → SETOF record(
    source_table  text,    -- qualified source table name
    source_oid    bigint,
    trigger_name  text,    -- expected trigger name
    trigger_type  text,    -- 'DML' or 'TRUNCATE'
    present       bool,    -- trigger exists in pg_catalog
    enabled       bool     -- trigger is not disabled
)

A present = false row means change capture is broken for that source.

Example:

-- Show only missing or disabled triggers:
SELECT source_table, trigger_type, trigger_name
FROM pgtrickle.trigger_inventory()
WHERE NOT present OR NOT enabled;

pgtrickle.fuse_status

Return the circuit-breaker (fuse) state for every stream table that has a fuse configured.

pgtrickle.fuse_status() → SETOF record(
    name           text,         -- stream table name
    fuse_mode      text,         -- 'off', 'on', or 'auto'
    fuse_state     text,         -- 'armed' or 'blown'
    fuse_ceiling   bigint,       -- change-count threshold
    fuse_sensitivity int,        -- consecutive over-ceiling cycles before blow
    blown_at       timestamptz,  -- when the fuse last blew (NULL if armed)
    blow_reason    text          -- reason the fuse blew (NULL if armed)
)

Example:

-- Check all fuse-enabled stream tables
SELECT name, fuse_mode, fuse_state, fuse_ceiling, blown_at
FROM pgtrickle.fuse_status();

-- Find blown fuses
SELECT name, blow_reason, blown_at
FROM pgtrickle.fuse_status()
WHERE fuse_state = 'blown';

Notes:

• Returns one row per stream table where fuse_mode != 'off'.
• A blown fuse suspends differential refreshes until cleared with pgtrickle.reset_fuse().
• A pgtrickle_alert NOTIFY with event fuse_blown is emitted when the fuse trips.
• See Configuration — fuse_default_ceiling for global defaults.

pgtrickle.reset_fuse

Clear a blown circuit-breaker fuse and resume scheduling for the stream table.

pgtrickle.reset_fuse(name text, action text DEFAULT 'apply') → void

Parameters:

Actions:

Example:

-- After investigating a bulk load, apply the changes:
SELECT pgtrickle.reset_fuse('category_summary', action => 'apply');

-- Or skip the oversized batch entirely:
SELECT pgtrickle.reset_fuse('category_summary', action => 'skip_changes');

-- Or rebuild from scratch:
SELECT pgtrickle.reset_fuse('category_summary', action => 'reinitialize');

Notes:

• Errors if the stream table's fuse is not in 'blown' state.
• After reset, the fuse returns to 'armed' state and the scheduler resumes normal operation.
• Use pgtrickle.fuse_status() to inspect the fuse state before resetting.
• The 'skip_changes' action advances the frontier past the pending changes without applying them — use only when you are certain the changes should be discarded.

Dependency & Inspection

Visualize dependencies, understand query plans, and audit source table relationships.

pgtrickle.dependency_tree

Render all stream table dependencies as an indented ASCII tree.

pgtrickle.dependency_tree() → SETOF record(
    tree_line    text,    -- indented visual line (├──, └──, │ characters)
    node         text,    -- qualified name (schema.table)
    node_type    text,    -- 'stream_table' or 'source_table'
    depth        int,
    status       text,    -- NULL for source_table nodes
    refresh_mode text     -- NULL for source_table nodes
)

Roots (stream tables with no stream-table parents) appear at depth 0. Each dependent is indented beneath its parent. Plain source tables are rendered as leaf nodes tagged [src].

Example:

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

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]

pgtrickle.diamond_groups

List all detected diamond dependency groups and their members.

When stream tables form diamond-shaped dependency graphs (multiple paths converge at a single fan-in node), the scheduler groups them for coordinated refresh. This function exposes those groups for monitoring and debugging.

pgtrickle.diamond_groups() → SETOF record(
    group_id        int4,
    member_name     text,
    member_schema   text,
    is_convergence  bool,
    epoch           int8,
    schedule_policy text
)

Return columns:

Example:

SELECT * FROM pgtrickle.diamond_groups();

Notes:

• Singleton stream tables (not part of any diamond) are omitted.
• The DAG is rebuilt on each call from the catalog — results reflect the current dependency graph.
• Groups are only relevant when diamond_consistency = 'atomic' is set on the convergence node or globally via the pg_trickle.diamond_consistency GUC.

pgtrickle.pgt_scc_status

List all cyclic strongly connected components (SCCs) and their convergence status.

When stream tables form circular dependencies (with pg_trickle.allow_circular = true), they are grouped into SCCs and iterated to a fixed point. This function exposes those groups for monitoring and debugging.

pgtrickle.pgt_scc_status() → SETOF record(
    scc_id              int4,
    member_count        int4,
    members             text[],
    last_iterations     int4,
    last_converged_at   timestamptz
)

Return columns:

Example:

SELECT * FROM pgtrickle.pgt_scc_status();

Notes:

• Only cyclic SCCs (with scc_id IS NOT NULL) are returned. Acyclic stream tables are omitted.
• last_iterations reflects the maximum last_fixpoint_iterations across SCC members.
• Results are queried from the catalog on each call.

pgtrickle.explain_st

Explain the DVM plan for a stream table's defining query.

pgtrickle.explain_st(name text) → SETOF record(
    property  text,
    value     text
)

Example:

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

Output Fields

Note: Properties are only included when data is available. For example, source_partitions only appears when at least one source table is partitioned, and refresh_timing_stats only appears after at least one completed refresh.

pgtrickle.list_sources

List the source tables that a stream table depends on.

pgtrickle.list_sources(name text) → SETOF record(
    source_table   text,         -- qualified source table name
    source_oid     bigint,
    source_type    text,         -- 'table', 'stream_table', etc.
    cdc_mode       text,         -- 'trigger', 'wal', or 'transitioning'
    columns_used   text          -- column-level dependency info (if available)
)

Example:

SELECT * FROM pgtrickle.list_sources('order_totals');

Returns the tables tracked by CDC for the given stream table, along with how they are being tracked. Useful when diagnosing why a stream table is not refreshing or to audit which source tables are being trigger-tracked.

Utilities

Utility functions for CDC management and row identity hashing.

pgtrickle.rebuild_cdc_triggers

Rebuild all CDC triggers (function body + trigger DDL) for every source table tracked by pg_trickle. This recreates trigger functions and re-attaches the trigger to each source table.

pgtrickle.rebuild_cdc_triggers() → text

Returns 'done' on success. Emits a WARNING per table on error and continues processing remaining sources.

When to use:

• After changing pg_trickle.cdc_trigger_mode from row to statement (or vice versa).
• After ALTER EXTENSION pg_trickle UPDATE when the CDC trigger function body has changed.
• After restoring from a backup where triggers may have been lost.

Example:

-- Switch to statement-level triggers and rebuild
SET pg_trickle.cdc_trigger_mode = 'statement';
SELECT pgtrickle.rebuild_cdc_triggers();

Notes:

• Called automatically during ALTER EXTENSION pg_trickle UPDATE (0.3.0 → 0.4.0) migration.
• Safe to call at any time — existing triggers are dropped and recreated.
• On error for a specific table, a WARNING is logged and processing continues with remaining sources.

pgtrickle.pg_trickle_hash

Compute a 64-bit xxHash row ID from a text value.

pgtrickle.pg_trickle_hash(input text) → bigint

Marked IMMUTABLE, PARALLEL SAFE.

Example:

SELECT pgtrickle.pg_trickle_hash('some_key');
-- Returns: 1234567890123456789

pgtrickle.pg_trickle_hash_multi

Compute a row ID by hashing multiple text values (composite keys).

pgtrickle.pg_trickle_hash_multi(inputs text[]) → bigint

Marked IMMUTABLE, PARALLEL SAFE. Uses \x1E (record separator) between values and \x00NULL\x00 for NULL entries.

Example:

SELECT pgtrickle.pg_trickle_hash_multi(ARRAY['key1', 'key2']);

Operator Support Matrix — Summary

pg_trickle supports 60+ SQL constructs across three refresh modes. The table below summarises broad categories. For the complete per-operator matrix (including notes on caveats, auxiliary columns and strategies), see DVM_OPERATORS.md.

Legend: ✅ fully supported — ⚠️ supported with caveats — ❌ not supported

For details on each operator's delta strategy, auxiliary columns, and known limitations, see the full Operator Support Matrix.

Expression Support

pgtrickle's DVM parser supports a wide range of SQL expressions in defining queries. All expressions work in both FULL and DIFFERENTIAL modes.

Conditional Expressions

Comparison Operators

Boolean Tests