pg_trickle

pg_trickle is a PostgreSQL 18 extension that turns ordinary SQL views into self-maintaining stream tables — no external processes, no sidecars, no bespoke refresh pipelines. Just CREATE EXTENSION pg_trickle and your views stay fresh.

-- Declare a stream table — a view that maintains itself
SELECT pgtrickle.create_stream_table(
    name     => 'active_orders',
    query    => 'SELECT * FROM orders WHERE status = ''active''',
    schedule => '30s'
);

-- Insert a row — the stream table updates automatically on the next refresh
INSERT INTO orders (id, status) VALUES (42, 'active');
SELECT count(*) FROM active_orders;  -- 1

The problem with materialized views

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, or accept stale data. Most teams end up building bespoke refresh pipelines just to keep summary tables current.

What pg_trickle does differently

pg_trickle captures changes to your source tables and — on each refresh cycle — derives a delta query that processes only the changed rows and merges the result into the materialized table. One insert into a million-row source table? pg_trickle touches exactly one row's worth of computation.

The approach is grounded in 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, and linear operators like filters pass deltas through unchanged.

Key capabilities

Demand-driven scheduling

With the default CALCULATED schedule mode, you only set an explicit refresh interval on the stream tables your application actually queries. The system propagates that cadence upward through the dependency graph: each upstream stream table inherits the tightest schedule among its downstream dependents. You declare freshness requirements where they matter — at the consumer — and the entire pipeline adjusts without manual coordination.

Hybrid change capture

pg_trickle bootstraps with lightweight row-level triggers — no configuration needed, works out of the box. Once the first refresh succeeds and wal_level = logical is available, the system automatically transitions to WAL-based logical replication for lower write-side overhead. The transition is seamless: trigger → transitioning → WAL-only. If anything goes wrong, it falls back to triggers.

Explore this documentation

• Getting Started — build a three-layer DAG from scratch in minutes
• SQL Reference — every function and option
• Architecture — how the engine works internally
• Configuration — GUC variables and tuning

Tutorials

• Fuse Circuit Breaker — protect stream tables from bulk-change storms
• Tiered Scheduling — configure multi-tier refresh cadences
• Migrating from Materialized Views — step-by-step migration guide
• Circular Dependencies — handle SCCs in your DAG
• Monitoring & Alerting — set up observability for stream tables
• ETL Bulk Load Patterns — safely load large batches without overwhelming CDC

Integrations

• CloudNativePG — deploy pg_trickle on Kubernetes
• Prometheus & Grafana — metrics and dashboards
• PgBouncer — connection pooling configuration

Source & releases

Written in Rust using pgrx. Targets PostgreSQL 18. Apache 2.0 licensed.

• Repository: github.com/grove/pg-trickle
• Install instructions: Installation
• Changelog: Changelog
• Roadmap: Roadmap

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 INSTALL.md)
• shared_preload_libraries = 'pg_trickle' in postgresql.conf
• max_worker_processes raised to at least 32 (see INSTALL.md); 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/grove/pg_trickle:latest

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

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 monitoring/README.md 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
• pgtrickle-tui: The TUI tool has a dedicated Health view that polls health_check() continuously

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 dog-feeding.

Enabling Dog-Feeding

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

-- Check what was created.
SELECT * FROM pgtrickle.dog_feeding_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.dog_feeding_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 = 'DOG_FEED'.

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 Dog-Feeding

SELECT pgtrickle.teardown_dog_feeding();

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

What's Next?

• TUI.md — Terminal UI & CLI tool for managing and monitoring stream tables from outside SQL
• 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

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/grove/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

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

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.

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.

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.

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_workers if fan-out exceeds 8 (default: 4 workers).

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'.

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.

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

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.

Pre-Deployment Checklist

Complete this checklist before deploying pg_trickle to a new environment. Each item links to the relevant documentation for details.

Version: v0.14.0+. Earlier versions may have different requirements.

1. PostgreSQL Version

• PostgreSQL 18.x is required (pg_trickle is compiled against PG 18)
• Extension binary matches your exact PostgreSQL major version

SELECT version();  -- Must show PostgreSQL 18.x

2. shared_preload_libraries

pg_trickle must be loaded at server startup via shared_preload_libraries. Without this, GUC variables and the background scheduler are not available.

# postgresql.conf
shared_preload_libraries = 'pg_trickle'

• shared_preload_libraries includes pg_trickle
• PostgreSQL has been restarted after changing this setting (reload is not sufficient)

SHOW shared_preload_libraries;  -- Must include pg_trickle

Managed PostgreSQL: Some providers (Supabase, Neon) do not support custom shared_preload_libraries. Check your provider's extension compatibility list. AWS RDS and Google Cloud SQL support custom shared libraries via parameter groups.

3. WAL Configuration (Optional but Recommended)

pg_trickle works without wal_level = logical — it uses trigger-based CDC by default. However, WAL-based CDC provides lower overhead on write-heavy workloads.

# postgresql.conf (optional — for WAL-based CDC)
wal_level = logical
max_replication_slots = 10   # At least 1 per tracked source table

• Decide: trigger-based CDC (default) or WAL-based CDC
• If WAL: wal_level = logical and server restarted
• If WAL: max_replication_slots is sufficient for your source table count

Note: CDC mode is configurable per stream table. The default cdc_mode = 'auto' starts with triggers and transitions to WAL automatically when wal_level = logical is detected. See CONFIGURATION.md for details.

4. Extension Installation

CREATE EXTENSION pg_trickle;

-- Verify installation
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_trickle';

• Extension created successfully
• Version matches expected release

5. Background Scheduler

The scheduler runs as a background worker and manages automatic refresh. Verify it's running:

SELECT pid, backend_type, state
FROM pg_stat_activity
WHERE backend_type = 'pg_trickle scheduler';

• Scheduler process is visible in pg_stat_activity
• pg_trickle.enabled = true (default; set to false to disable)

6. Connection Pooler Compatibility

PgBouncer (Transaction Mode)

PgBouncer in transaction pooling mode drops session state between transactions. pg_trickle needs special handling:

• Enable pooler_compatibility_mode on affected stream tables:

SELECT pgtrickle.alter_stream_table('my_st',
    pooler_compatibility_mode => true);

• Or set globally via GUC:

pg_trickle.pooler_compatibility_mode = true

PgBouncer (Session Mode)

Session mode preserves session state — no special configuration needed.

Supavisor / Other Poolers

Some poolers (Supavisor, pgcat) have their own compatibility characteristics. Test with pgtrickle.validate_query() before deploying.

7. Recommended GUC Starting Values

These are sensible defaults for most workloads. Adjust based on monitoring data.

# Core settings (usually fine as defaults)
pg_trickle.enabled = true                    # Enable scheduler
pg_trickle.schedule_interval = '5s'          # Global default refresh interval
pg_trickle.max_workers = 4                   # Parallel refresh workers

# Performance tuning
pg_trickle.planner_aggressive = true         # Enable MERGE planner hints
pg_trickle.tiered_scheduling = true          # Tier-aware scheduling

# CDC mode
pg_trickle.cdc_mode = 'auto'                # auto | trigger | wal

# Safety
pg_trickle.unlogged_buffers = false          # true = faster but not crash-safe
pg_trickle.fuse_default_ceiling = 10000      # Auto-fuse change threshold

• Review GUC values for your workload
• See CONFIGURATION.md for the full reference

8. Resource Planning

Memory

• Each background worker uses a separate PostgreSQL backend
• work_mem applies to each worker's delta SQL execution
• Monitor RSS growth via pg_stat_activity or OS-level tools

Storage

• Change buffer tables (pgtrickle_changes.changes_*) grow between refreshes
• Buffer size depends on DML rate × refresh interval
• Monitor via pgtrickle.shared_buffer_stats()

Connections

• The scheduler uses pg_trickle.max_workers backend connections

• Ensure max_connections has headroom for workers + application

• max_connections is at least application connections + pg_trickle.max_workers + 5

9. Monitoring Setup

Essential Queries

-- Stream table health overview
SELECT pgt_name, status, staleness, refresh_mode
FROM pgtrickle.stream_tables_info
ORDER BY staleness DESC NULLS LAST;

-- Refresh efficiency
SELECT pgt_name, diff_speedup, avg_change_ratio
FROM pgtrickle.refresh_efficiency();

-- Error states
SELECT pgt_name, status, last_error_message, last_error_at
FROM pgtrickle.pgt_stream_tables
WHERE status IN ('ERROR', 'SUSPENDED');

Grafana / Prometheus

See the monitoring/ directory for ready-to-use Grafana dashboards and Prometheus configuration.

• Monitoring configured for stream table health
• Alerting on ERROR/SUSPENDED status

10. Backup & Restore

pg_trickle stream tables are standard PostgreSQL tables and are included in pg_dump / pg_restore. See BACKUP_AND_RESTORE.md for details.

• Backup strategy accounts for both source tables and stream tables
• Restore procedure tested (stream tables may need re-initialization)

Quick Validation Script

Run this after deployment to verify everything is working:

-- 1. Extension loaded
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_trickle';

-- 2. Scheduler running
SELECT COUNT(*) > 0 AS scheduler_alive
FROM pg_stat_activity
WHERE backend_type = 'pg_trickle scheduler';

-- 3. Create a test stream table
CREATE TABLE _deploy_test_src (id INT PRIMARY KEY, val INT);
INSERT INTO _deploy_test_src VALUES (1, 100), (2, 200);

SELECT pgtrickle.create_stream_table(
    '_deploy_test_st',
    'SELECT id, val FROM _deploy_test_src',
    refresh_mode => 'FULL'
);

SELECT pgtrickle.refresh_stream_table('_deploy_test_st');

-- 4. Verify data
SELECT * FROM _deploy_test_st ORDER BY id;
-- Expected: (1, 100), (2, 200)

-- 5. Cleanup
SELECT pgtrickle.drop_stream_table('_deploy_test_st');
DROP TABLE _deploy_test_src;

Connection Pooler Compatibility

Added in v0.19.0 (UX-4 / STAB-1).

pg_trickle uses prepared statements and NOTIFY internally. These features require special handling when a connection pooler sits between the application and PostgreSQL.

PgBouncer Transaction Mode

In PgBouncer transaction pooling mode, each transaction may land on a different server-side connection. Prepared statements and LISTEN/NOTIFY do not survive across transactions.

Recommended configuration:

# postgresql.conf
pg_trickle.connection_pooler_mode = 'transaction'

This cluster-wide GUC:

• Disables prepared-statement reuse for all stream tables.
• Suppresses NOTIFY pg_trickle_refresh emissions (listeners on other connections will not receive them anyway in transaction mode).

Alternatively, enable pooler compatibility per stream table:

SELECT pgtrickle.alter_stream_table('my_stream_table',
    pooler_compatibility_mode => true);

PgBouncer Session Mode

Session pooling is fully compatible — no special configuration needed.

pgcat / Supavisor

These poolers generally support prepared statements and NOTIFY. Set pg_trickle.connection_pooler_mode = 'off' (the default).

Kubernetes / CNPG

See Scaling — CNPG for connection pooler configuration in Kubernetes environments.

Related Documentation

• Getting Started — First stream table in 5 minutes
• Configuration Reference — All GUC variables
• SQL Reference — Complete function reference
• Best-Practice Patterns — Common data modeling patterns
• Architecture — How pg_trickle works internally
• Backup & Restore — Backup considerations

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

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.

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) → void

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

SQL Value Functions

Array and Row Expressions

Subquery Expressions

Subqueries are supported in the WHERE clause and SELECT list. They are parsed into dedicated DVM operators with specialized delta computation for incremental maintenance.

Notes:

• EXISTS and IN (subquery) in the WHERE clause are transformed into semi-join operators. NOT EXISTS and NOT IN (subquery) become anti-join operators.
• Multi-column IN (subquery) is not supported (e.g., WHERE (a, b) IN (SELECT x, y FROM ...)). Rewrite as WHERE EXISTS (SELECT 1 FROM ... WHERE a = x AND b = y) for equivalent semantics.
• Multiple subqueries in the same WHERE clause are supported when combined with AND. Subqueries combined with OR are also supported — they are automatically rewritten into UNION of separate filtered queries.
• Scalar subqueries in the SELECT list are supported as long as they return exactly one row and one column.
• ALL (subquery) is supported — see the worked example below.

ALL (subquery) — Worked Example

ALL (subquery) tests whether a comparison holds against every row returned by the subquery. pg_trickle rewrites it to a NULL-safe anti-join so it can be maintained incrementally.

Comparison operators supported: >, >=, <, <=, =, <>

Example — products cheaper than all competitors:

-- Source tables
CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  TEXT,
    price NUMERIC
);
CREATE TABLE competitor_prices (
    id          INT PRIMARY KEY,
    product_id  INT,
    price       NUMERIC
);

-- Sample data
INSERT INTO products VALUES (1, 'Widget', 9.99), (2, 'Gadget', 24.99), (3, 'Gizmo', 14.99);
INSERT INTO competitor_prices VALUES (1, 1, 12.99), (2, 1, 11.50), (3, 2, 19.99), (4, 3, 14.99);

-- Stream table: find products priced below ALL competitor prices
SELECT pgtrickle.create_stream_table(
    name  => 'cheapest_products',
    query => $$
        SELECT p.id, p.name, p.price
        FROM products p
        WHERE p.price < ALL (
            SELECT cp.price
            FROM competitor_prices cp
            WHERE cp.product_id = p.id
        )
    $$,
    schedule => '1m'
);

Result: Widget (9.99 < all of [12.99, 11.50]) is included. Gadget (24.99 ≮ 19.99) is excluded. Gizmo (14.99 ≮ 14.99) is excluded.

How pg_trickle handles it internally:

1. WHERE price < ALL (SELECT ...) is parsed into an anti-join with a NULL-safe condition.
2. The condition NOT (x op col) is wrapped as (col IS NULL OR NOT (x op col)) to correctly handle NULL values in the subquery — if any subquery row is NULL, the ALL comparison fails (standard SQL semantics).
3. The anti-join uses the same incremental delta computation as NOT EXISTS, so changes to either products or competitor_prices are propagated efficiently.

Other common patterns:

-- Employees whose salary meets or exceeds all department maximums
WHERE salary >= ALL (SELECT max_salary FROM department_caps)

-- Orders with ratings better than all thresholds
WHERE rating > ALL (SELECT min_rating FROM quality_thresholds)

Auto-Rewrite Pipeline

pg_trickle transparently rewrites certain SQL constructs before parsing. These rewrites are applied automatically and require no user action:

Window Functions in Expressions (Auto-Rewrite)

Window functions nested inside expressions (e.g., CASE WHEN ROW_NUMBER() ..., ABS(RANK() OVER (...) - 5)) are automatically rewritten. pg_trickle lifts each window function call into a synthetic column in an inner subquery, then applies the original expression in the outer SELECT.

This rewrite is transparent — you write your query naturally and pg_trickle handles it:

Your query:

SELECT
    id,
    name,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1
         THEN 'top earner'
         ELSE 'other'
    END AS rank_label
FROM employees

What pg_trickle generates internally:

SELECT
    "__pgt_wf_inner".id,
    "__pgt_wf_inner".name,
    CASE WHEN "__pgt_wf_inner"."__pgt_wf_1" = 1
         THEN 'top earner'
         ELSE 'other'
    END AS "rank_label"
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS "__pgt_wf_1"
    FROM employees
) "__pgt_wf_inner"

The inner subquery produces the window function result as a plain column (__pgt_wf_1), which the DVM engine can maintain incrementally using its existing window function support. The outer expression is then a simple column reference.

More examples:

-- Arithmetic with window functions
SELECT id, ABS(RANK() OVER (ORDER BY score) - 5) AS adjusted_rank
FROM players

-- COALESCE with window function
SELECT id, COALESCE(LAG(value) OVER (ORDER BY ts), 0) AS prev_value
FROM sensor_readings

-- Multiple window functions in expressions
SELECT id,
       ROW_NUMBER() OVER (ORDER BY created_at) * 100 AS seq,
       SUM(amount) OVER (ORDER BY created_at) / COUNT(*) OVER (ORDER BY created_at) AS running_avg
FROM transactions

All of these are handled automatically — each distinct window function call is extracted to its own __pgt_wf_N synthetic column.

HAVING Clause

HAVING is fully supported. The filter predicate is applied on top of the aggregate delta computation — groups that pass the HAVING condition are included in the stream table.

SELECT pgtrickle.create_stream_table(
    name     => 'big_departments',
    query    => 'SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department HAVING COUNT(*) > 10',
    schedule => '1m'
);

Tables Without Primary Keys (Keyless Tables)

Tables without a primary key can be used as sources. pg_trickle generates a content-based row identity by hashing all column values using pg_trickle_hash_multi(). This allows DIFFERENTIAL mode to work, though at the cost of being unable to distinguish truly duplicate rows (rows with identical values in all columns).

-- No primary key — pg_trickle uses content hashing for row identity
CREATE TABLE events (ts TIMESTAMPTZ, payload JSONB);
SELECT pgtrickle.create_stream_table(
    name     => 'event_summary',
    query    => 'SELECT payload->>''type'' AS event_type, COUNT(*) FROM events GROUP BY 1',
    schedule => '1m'
);

Known Limitation — Duplicate Rows in Keyless Tables (G7.1)

When a keyless table contains exact duplicate rows (identical values in every column), content-based hashing produces the same __pgt_row_id for each copy. Consequences:

• INSERT of a duplicate row may appear as a no-op (the hash already exists in the stream table).
• DELETE of one copy may delete all copies (the MERGE matches on __pgt_row_id, hitting every duplicate).
• Aggregate counts over keyless tables with duplicates may drift from the true query result.

Recommendation: Add a PRIMARY KEY or at least a UNIQUE constraint to source tables used in DIFFERENTIAL mode. This eliminates the ambiguity entirely. If duplicates are expected and correctness matters, use FULL refresh mode, which always recomputes from scratch.

Volatile Function Detection

pg_trickle checks all functions and operators in the defining query against pg_proc.provolatile:

• VOLATILE functions (e.g., random(), clock_timestamp(), gen_random_uuid()) are rejected in DIFFERENTIAL and IMMEDIATE modes because they produce different results on each evaluation, breaking delta correctness.
• VOLATILE operators — custom operators backed by volatile functions are also detected. The check resolves the operator’s implementation function via pg_operator.oprcode and checks its volatility in pg_proc.
• STABLE functions (e.g., now(), current_timestamp, current_setting()) produce a warning in DIFFERENTIAL and IMMEDIATE modes — they are consistent within a single refresh but may differ between refreshes.
• IMMUTABLE functions are always safe and produce no warnings.

FULL mode accepts all volatility classes since it re-evaluates the entire query each time.

Volatile Function Policy (VOL-1)

The pg_trickle.volatile_function_policy GUC controls how volatile functions are handled:

-- Allow volatile functions with a warning
SET pg_trickle.volatile_function_policy = 'warn';

-- Allow volatile functions silently
SET pg_trickle.volatile_function_policy = 'allow';

-- Restore default (reject volatile functions)
SET pg_trickle.volatile_function_policy = 'reject';

COLLATE Expressions

COLLATE clauses on expressions are supported:

SELECT pgtrickle.create_stream_table(
    name     => 'sorted_names',
    query    => 'SELECT name COLLATE "C" AS c_name FROM users',
    schedule => '1m'
);

IS JSON Predicate (PostgreSQL 16+)

The IS JSON predicate validates whether a value is valid JSON. All variants are supported:

-- Filter rows with valid JSON
SELECT pgtrickle.create_stream_table(
    name     => 'valid_json_events',
    query    => 'SELECT id, payload FROM events WHERE payload::text IS JSON',
    schedule => '1m'
);

-- Type-specific checks
SELECT pgtrickle.create_stream_table(
    name         => 'json_objects_only',
    query        => 'SELECT id, data IS JSON OBJECT AS is_obj,
          data IS JSON ARRAY AS is_arr,
          data IS JSON SCALAR AS is_scalar
   FROM json_data',
    schedule     => '1m',
    refresh_mode => 'FULL'
);

Supported variants: IS JSON, IS JSON OBJECT, IS JSON ARRAY, IS JSON SCALAR, IS NOT JSON (all forms), WITH UNIQUE KEYS.

SQL/JSON Constructors (PostgreSQL 16+)

SQL-standard JSON constructor functions are supported in both FULL and DIFFERENTIAL modes:

-- JSON_OBJECT: construct a JSON object from key-value pairs
SELECT pgtrickle.create_stream_table(
    name     => 'user_json',
    query    => 'SELECT id, JSON_OBJECT(''name'' : name, ''age'' : age) AS data FROM users',
    schedule => '1m'
);

-- JSON_ARRAY: construct a JSON array from values
SELECT pgtrickle.create_stream_table(
    name         => 'value_arrays',
    query        => 'SELECT id, JSON_ARRAY(a, b, c) AS arr FROM measurements',
    schedule     => '1m',
    refresh_mode => 'FULL'
);

-- JSON(): parse a text value as JSON
-- JSON_SCALAR(): wrap a scalar value as JSON
-- JSON_SERIALIZE(): serialize a JSON value to text

Note: JSON_ARRAYAGG() and JSON_OBJECTAGG() are SQL-standard aggregate functions fully recognized by the DVM engine. In DIFFERENTIAL mode, they use the group-rescan strategy (affected groups are re-aggregated from source data). The full deparsed SQL is preserved to handle the special key: value, ABSENT ON NULL, ORDER BY, and RETURNING clause syntax.

JSON_TABLE (PostgreSQL 17+)

JSON_TABLE() generates a relational table from JSON data. It is supported in the FROM clause in both FULL and DIFFERENTIAL modes. Internally, it is modeled as a LateralFunction.

-- Extract structured data from a JSON column
SELECT pgtrickle.create_stream_table(
    name     => 'user_phones',
    query    => $$SELECT u.id, j.phone_type, j.phone_number
    FROM users u,
         JSON_TABLE(u.contact_info, '$.phones[*]'
           COLUMNS (
             phone_type TEXT PATH '$.type',
             phone_number TEXT PATH '$.number'
           )
         ) AS j$$,
    schedule => '1m'
);

Supported column types:

• Regular columns — name TYPE PATH '$.path' (with optional ON ERROR/ON EMPTY behaviors)
• EXISTS columns — name TYPE EXISTS PATH '$.path'
• Formatted columns — name TYPE FORMAT JSON PATH '$.path'
• Nested columns — NESTED PATH '$.path' COLUMNS (...)

The PASSING clause is also supported for passing named variables to path expressions.

Unsupported Expression Types

The following are rejected with clear error messages rather than producing broken SQL:

Note: Window functions inside expressions (e.g., CASE WHEN ROW_NUMBER() OVER (...) ...) were unsupported in earlier versions but are now automatically rewritten — see Auto-Rewrite Pipeline § Window Functions in Expressions.

Restrictions & Interoperability

Stream tables are standard PostgreSQL heap tables stored in the pgtrickle schema with an additional __pgt_row_id BIGINT PRIMARY KEY column managed by the refresh engine. This section describes what you can and cannot do with them.

Referencing Other Stream Tables

Stream tables can reference other stream tables in their defining query. This creates a dependency edge in the internal DAG, and the scheduler refreshes upstream tables before downstream ones. By default, cycles are detected and rejected at creation time.

When pg_trickle.allow_circular = true, circular dependencies are allowed for stream tables that use DIFFERENTIAL refresh mode and have monotone defining queries (no aggregates, EXCEPT, window functions, or NOT EXISTS/NOT IN). Cycle members are assigned an scc_id and the scheduler iterates them to a fixed point. Non-monotone operators are rejected because they prevent convergence.

-- ST1 reads from a base table
SELECT pgtrickle.create_stream_table(
    name     => 'order_totals',
    query    => 'SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id',
    schedule => '1m'
);

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

Views as Sources in Defining Queries

PostgreSQL views can be used as source tables in a stream table's defining query. Views are automatically inlined — replaced with their underlying SELECT definition as subqueries — so CDC triggers land on the actual base tables.

CREATE VIEW active_orders AS
  SELECT * FROM orders WHERE status = 'active';

-- This works (views are auto-inlined):
SELECT pgtrickle.create_stream_table(
    name     => 'order_summary',
    query    => 'SELECT customer_id, COUNT(*) FROM active_orders GROUP BY customer_id',
    schedule => '1m'
);
-- Internally, 'active_orders' is replaced with:
--   (SELECT ... FROM orders WHERE status = 'active') AS active_orders

Nested views (view → view → table) are fully expanded via a fixpoint loop. Column-renaming views (CREATE VIEW v(a, b) AS ...) work correctly — pg_get_viewdef() produces the proper column aliases.

When a view is inlined, the user's original SQL is stored in the original_query catalog column for reinit and introspection. The defining_query column contains the expanded (post-inlining) form.

DDL hooks: CREATE OR REPLACE VIEW on a view that was inlined into a stream table marks that ST for reinit. DROP VIEW sets affected STs to ERROR status.

Materialized views are rejected in DIFFERENTIAL mode — their stale-snapshot semantics prevent CDC triggers from tracking changes. Use the underlying query directly, or switch to FULL mode. In FULL mode, materialized views are allowed (no CDC needed).

Foreign tables are rejected in DIFFERENTIAL mode — row-level triggers cannot be created on foreign tables. Use FULL mode instead.

Partitioned Tables as Sources

Partitioned tables are fully supported as source tables in both FULL and DIFFERENTIAL modes. CDC triggers are installed on the partitioned parent table, and PostgreSQL 13+ ensures the trigger fires for all DML routed to child partitions. The change buffer uses the parent table's OID (pgtrickle_changes.changes_<parent_oid>).

CREATE TABLE orders (
    id INT, region TEXT, amount NUMERIC
) PARTITION BY LIST (region);
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('EU');

-- Works — inserts into any partition are captured:
SELECT pgtrickle.create_stream_table(
    name     => 'order_summary',
    query    => 'SELECT region, SUM(amount) FROM orders GROUP BY region',
    schedule => '1m'
);

ATTACH PARTITION detection: When a new partition is attached to a tracked source table via ALTER TABLE parent ATTACH PARTITION child ..., pg_trickle's DDL event trigger detects the change in partition structure and automatically marks affected stream tables for reinitialize. This ensures pre-existing rows in the newly attached partition are included on the next refresh. DETACH PARTITION is also detected and triggers reinitialization.

WAL mode: When using WAL-based CDC (cdc_mode = 'wal'), publications for partitioned source tables are created with publish_via_partition_root = true. This ensures changes from child partitions are published under the parent table's identity, matching trigger-mode CDC behavior.

Note: pg_trickle targets PostgreSQL 18. On PostgreSQL 12 or earlier (not supported), parent triggers do not fire for partition-routed rows, which would cause silent data loss.

Foreign Tables as Sources

Foreign tables (via postgres_fdw or other FDWs) can be used as stream table sources with these constraints:

-- Foreign table source — FULL refresh only
SELECT pgtrickle.create_stream_table(
    name         => 'remote_summary',
    query        => 'SELECT region, SUM(amount) FROM remote_orders GROUP BY region',
    schedule     => '5m',
    refresh_mode => 'FULL'
);

When pg_trickle detects a foreign table source, it emits an INFO message explaining the constraints. If you attempt to use DIFFERENTIAL mode without polling enabled, the creation will succeed but the refresh falls back to FULL.

Polling-based CDC creates a local snapshot table and computes EXCEPT ALL differences on each refresh. Enable with:

SET pg_trickle.foreign_table_polling = on;

For a complete step-by-step setup guide, see the Foreign Table Sources tutorial.

IMMEDIATE Mode Query Restrictions

The 'IMMEDIATE' refresh mode supports nearly all SQL constructs supported by 'DIFFERENTIAL' and 'FULL' modes. Queries are validated at stream table creation and when switching to IMMEDIATE mode via alter_stream_table.

Supported in IMMEDIATE mode:

• Simple SELECT ... FROM table scans, filters, projections
• JOIN (INNER, LEFT, FULL OUTER)
• GROUP BY with standard aggregates (COUNT, SUM, AVG, MIN, MAX, etc.)
• DISTINCT
• Non-recursive WITH (CTEs)
• UNION ALL, INTERSECT, EXCEPT
• EXISTS / IN subqueries (SemiJoin, AntiJoin)
• Subqueries in FROM
• Window functions (ROW_NUMBER, RANK, DENSE_RANK, etc.)
• LATERAL subqueries
• LATERAL set-returning functions (unnest(), jsonb_array_elements(), etc.)
• Scalar subqueries in SELECT
• Cascading IMMEDIATE stream tables (ST depending on another IMMEDIATE ST)
• Recursive CTEs (WITH RECURSIVE) — uses semi-naive evaluation (INSERT-only) or Delete-and-Rederive (DELETE/UPDATE); bounded by pg_trickle.ivm_recursive_max_depth (default 100) to guard against infinite loops from cyclic data

Not yet supported in IMMEDIATE mode:

None — all constructs that work in 'DIFFERENTIAL' mode are now also available in 'IMMEDIATE' mode.

Notes on WITH RECURSIVE in IMMEDIATE mode:

• A __pgt_depth counter is injected into the generated semi-naive SQL. Propagation stops when the counter reaches ivm_recursive_max_depth (default 100). Raise this GUC for deeper hierarchies or set it to 0 to disable the guard.
• A WARNING is emitted at stream table creation time reminding operators to monitor for stack depth limit exceeded errors on very deep hierarchies.
• Non-linear recursion (multiple self-references) is rejected — PostgreSQL itself enforces this restriction.

Attempting to create a stream table with an unsupported construct produces a clear error message.

Logical Replication Targets

Tables that receive data via logical replication require special consideration. Changes arriving via replication do not fire normal row-level triggers, which means CDC triggers will miss those changes.

pg_trickle emits a WARNING at stream table creation time if any source table is detected as a logical replication target (via pg_subscription_rel).

Workarounds:

• Use cdc_mode = 'wal' for WAL-based CDC that captures all changes regardless of origin.
• Use FULL refresh mode, which recomputes entirely from the current table state.
• Set a frequent refresh schedule with FULL mode to limit staleness.

Views on Stream Tables

PostgreSQL views can reference stream tables. The view reflects the data as of the most recent refresh.

CREATE VIEW top_customers AS
SELECT customer_id, total
FROM pgtrickle.order_totals
WHERE total > 500
ORDER BY total DESC;

Materialized Views on Stream Tables

Materialized views can reference stream tables, though this is typically redundant (both are physical snapshots of a query). The materialized view requires its own REFRESH MATERIALIZED VIEW — it does not auto-refresh when the stream table refreshes.

Logical Replication of Stream Tables

Stream tables can be published for logical replication like any ordinary table:

-- On publisher
CREATE PUBLICATION my_pub FOR TABLE pgtrickle.order_totals;

-- On subscriber
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=... dbname=...'
  PUBLICATION my_pub;

Caveats:

• The __pgt_row_id column is replicated (it is the primary key), which is an internal implementation detail.
• The subscriber receives materialized data, not the defining query. Refreshes on the publisher propagate as normal DML via logical replication.
• Do not install pg_trickle on the subscriber and attempt to refresh the replicated table — it will have no CDC triggers or catalog entries.
• The internal change buffer tables (pgtrickle_changes.changes_<oid>) and catalog tables are not published by default; subscribers only receive the final output.

Known Delta Computation Limitations

The following edge cases produce incorrect delta results in DIFFERENTIAL mode under specific data mutation patterns. They have no effect on FULL mode.

JOIN Key Column Change + Simultaneous Right-Side Delete — Fixed (EC-01)

Resolved in v0.14.0. This limitation no longer exists — the delta query now uses a pre-change right snapshot (R₀) for DELETE deltas, ensuring stale rows are correctly removed even when the join partner is simultaneously deleted.

The fix splits Part 1 of the JOIN delta into two arms:

• Part 1a (inserts): ΔL_inserts ⋈ R₁ — uses current right state
• Part 1b (deletes): ΔL_deletes ⋈ R₀ — uses pre-change right state

R₀ is reconstructed as R_current EXCEPT ALL ΔR_inserts UNION ALL ΔR_deletes (or via NOT EXISTS anti-join for simple Scan nodes). This ensures the DELETE half always finds the old join partner, even if that partner was deleted in the same cycle.

The fix applies to INNER JOIN, LEFT JOIN, and FULL OUTER JOIN delta operators. See DVM_OPERATORS.md for implementation details.

CUBE/ROLLUP Expansion Limit

CUBE(a, b, c...n) on N columns generates $2^N$ grouping set branches (a UNION ALL of N queries). pg_trickle rejects CUBE/ROLLUP that would produce more than 64 branches to prevent runaway memory usage during query generation. Use explicit GROUPING SETS(...) instead:

-- Rejected: CUBE(a, b, c, d, e, f, g) would generate 128 branches
-- Use instead:
SELECT pgtrickle.create_stream_table(
    name     => 'multi_dim',
    query    => 'SELECT a, b, c, SUM(v) FROM t
   GROUP BY GROUPING SETS ((a, b, c), (a, b), (a), ())',
    schedule => '5m'
);

What Is NOT Allowed

Tip: The __pgt_row_id column is visible but should be ignored by consuming queries — it is an implementation detail used for delta MERGE operations.

Internal __pgt_* Auxiliary Columns

Stream tables may contain additional hidden columns whose names begin with __pgt_. These are managed exclusively by the refresh engine — they are not part of the user-visible schema and should never be read or written by application queries.

__pgt_row_id — Row identity (always present)

Every stream table has a BIGINT PRIMARY KEY column named __pgt_row_id. It is a content hash of all output columns (xxHash3-128 with Fibonacci-mixing of multiple column hashes), updated by the refresh engine on every MERGE. It is used as the MERGE join key to detect inserts/updates/deletes.

__pgt_count — Group multiplicity (aggregates & DISTINCT)

Added when the defining query contains GROUP BY, DISTINCT, UNION ALL ... GROUP BY, or any aggregate expression that requires tracking how many source rows contribute to each output row.

__pgt_count_l / __pgt_count_r — Dual multiplicity (INTERSECT / EXCEPT)

Added when the defining query contains INTERSECT or EXCEPT. Stores independently the left-branch and right-branch row counts for Z-set delta algebra.

__pgt_aux_sum_<alias> / __pgt_aux_count_<alias> — Running totals for AVG

Pairs of auxiliary columns added for each AVG(expr) in the query. Instead of recomputing the average from scratch on each delta, the refresh engine maintains a running sum and count and derives the average algebraically.

Named __pgt_aux_sum_<output_alias> and __pgt_aux_count_<output_alias>, where <output_alias> is the column alias for the AVG expression in the SELECT list.

__pgt_aux_sum2_<alias> — Sum-of-squares for STDDEV / VARIANCE

Added alongside the sum/count pair when the query contains STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, or VAR_SAMP. Enables O(1) algebraic computation of variance from the Welford identity.

__pgt_aux_sumx_* / __pgt_aux_sumy_* / __pgt_aux_sumxy_* / __pgt_aux_sumx2_* / __pgt_aux_sumy2_* — Cross-product accumulators for regression aggregates

Five auxiliary columns per aggregate, used for O(1) algebraic maintenance of the twelve PostgreSQL regression and correlation aggregates.

The five columns are named with base prefix __pgt_aux_<kind>_<output_alias> where <kind> is sumx, sumy, sumxy, sumx2, or sumy2. The shared group count is stored in the companion __pgt_aux_count_<output_alias> column.

__pgt_aux_nonnull_<alias> — Non-NULL count for SUM + FULL OUTER JOIN

Added when the query contains SUM(expr) inside a FULL OUTER JOIN aggregate. When matched rows transition to unmatched (null-padded), standard algebraic SUM would produce 0 instead of NULL. This counter tracks how many non-NULL argument values exist in each group; when it reaches zero the SUM is definitively NULL without a full rescan.

__pgt_wf_<N> — Window function lift-out (query rewrite)

Added at query-rewrite time (before storage table creation) when the defining query contains window functions embedded inside larger expressions (e.g. CASE WHEN ROW_NUMBER() OVER (...) = 1 THEN ...). The engine lifts the window function to a synthetic inner-subquery column so the outer SELECT can reference it by alias.

__pgt_depth — Recursion depth counter (recursive CTE)

Present only inside the DVM-generated SQL for recursive CTE queries. Used to limit unbounded recursion in semi-naive evaluation. Not added as a permanent column to the storage table.

Rule of thumb: Unless you see an ALTER TABLE query mentioning one of these columns, they are transparent to consuming queries. Never SELECT __pgt_* columns in application code — their names, types, and presence may change across minor versions.

Row-Level Security (RLS)

Stream tables follow the same RLS model as PostgreSQL's built-in MATERIALIZED VIEW: the refresh always materializes the full, unfiltered result set. Access control is applied at read time via RLS policies on the stream table itself.

How It Works

Recommended Pattern: RLS on the Stream Table

-- 1. Create a stream table (materializes all rows)
SELECT pgtrickle.create_stream_table(
    name  => 'order_totals',
    query => 'SELECT tenant_id, SUM(amount) AS total FROM orders GROUP BY tenant_id'
);

-- 2. Enable RLS on the stream table
ALTER TABLE pgtrickle.order_totals ENABLE ROW LEVEL SECURITY;

-- 3. Create per-tenant policies
CREATE POLICY tenant_isolation ON pgtrickle.order_totals
    USING (tenant_id = current_setting('app.tenant_id')::INT);

-- 4. Each role sees only its own rows
SET app.tenant_id = '42';
SELECT * FROM pgtrickle.order_totals;  -- only tenant 42's rows

Note: This is identical to how you would apply RLS to a regular MATERIALIZED VIEW. One stream table serves all tenants; per-tenant filtering happens at query time with zero storage duplication.

Views

pgtrickle.stream_tables_info

Status overview with computed staleness information.

SELECT * FROM pgtrickle.stream_tables_info;

Columns include all pgtrickle.pgt_stream_tables columns plus:

pgtrickle.pg_stat_stream_tables

Comprehensive monitoring view combining catalog metadata with aggregate refresh statistics.

SELECT * FROM pgtrickle.pg_stat_stream_tables;

Key columns:

pgtrickle.quick_health

Single-row health summary for dashboards and alerting. Returns the overall health status of the pg_trickle extension at a glance.

SELECT * FROM pgtrickle.quick_health;

Status values:

• EMPTY — No stream tables exist.
• OK — All stream tables are healthy and up-to-date.
• WARNING — Some tables have errors or are stale.
• CRITICAL — At least one stream table is SUSPENDED.

pgtrickle.pgt_cdc_status

Convenience view for inspecting the CDC mode and WAL slot state of every TABLE-type source for all stream tables. Useful for monitoring in-progress TRIGGER→WAL transitions.

SELECT * FROM pgtrickle.pgt_cdc_status;

Subscribe to the pgtrickle_cdc_transition NOTIFY channel to receive real-time events when a source moves between CDC modes (payload is a JSON object with source_oid, from, and to fields).

Catalog Tables

pgtrickle.pgt_stream_tables

Core metadata for each stream table.

pgtrickle.pgt_dependencies

DAG edges — records which source tables each ST depends on, including CDC mode metadata.

pgtrickle.pgt_refresh_history

Audit log of all refresh operations.

pgtrickle.pgt_change_tracking

CDC slot tracking per source table.

pgtrickle.pgt_source_gates

Bootstrap source gate registry. One row per source table that has ever been gated. Only sources with gated = true are actively blocking scheduler refreshes.

pgtrickle.pgt_refresh_groups

User-declared Cross-Source Snapshot Consistency groups (v0.9.0). A refresh group guarantees that all member stream tables are refreshed against a snapshot taken at the same point in time, preventing partial-update visibility (e.g. orders and order_lines both reflecting the same transaction boundary).

Management API

-- Create a refresh group
SELECT pgtrickle.create_refresh_group(
    'orders_snapshot',
    ARRAY['public.orders_summary', 'public.order_lines_summary'],
    'repeatable_read'   -- or 'read_committed' (default)
);

-- List all groups:
SELECT * FROM pgtrickle.refresh_groups();

-- Remove a group:
SELECT pgtrickle.drop_refresh_group('orders_snapshot');

Validation rules:

• At least 2 member stream tables are required.
• All members must exist in pgt_stream_tables.
• No member can appear in more than one refresh group.
• Valid isolation levels: 'read_committed' (default), 'repeatable_read'.

Bootstrap Source Gating (v0.5.0)

These functions let operators pause and resume scheduler-driven refreshes for individual source tables — useful during large bulk loads or ETL windows.

pgtrickle.gate_source(source TEXT)

Mark a source table as gated. The scheduler will skip any stream table that reads from this source until ungate_source() is called.

SELECT pgtrickle.gate_source('my_schema.big_source');

Manual refresh_stream_table() calls are not affected by gates.

pgtrickle.ungate_source(source TEXT)

Clear a gate set by gate_source(). After this call the scheduler resumes normal refresh scheduling for dependent stream tables.

SELECT pgtrickle.ungate_source('my_schema.big_source');

pgtrickle.source_gates()

Table function returning the current gate status for all registered sources.

SELECT * FROM pgtrickle.source_gates();
-- source_table | schema_name | gated | gated_at | ungated_at | gated_by

Typical workflow

-- 1. Gate the source before a bulk load.
SELECT pgtrickle.gate_source('orders');

-- 2. Load historical data (scheduler sits idle for orders-based STs).
COPY orders FROM '/data/historical_orders.csv';

-- 3. Ungate — the next scheduler tick refreshes everything cleanly.
SELECT pgtrickle.ungate_source('orders');

pgtrickle.bootstrap_gate_status() (v0.6.0)

Rich introspection of bootstrap gate lifecycle. Returns the same columns as source_gates() plus computed fields for debugging.

SELECT * FROM pgtrickle.bootstrap_gate_status();
-- source_table | schema_name | gated | gated_at | ungated_at | gated_by | gate_duration | affected_stream_tables

Rows are sorted with currently-gated sources first, then alphabetically.

ETL Coordination Cookbook (v0.6.0)

Step-by-step recipes for common bulk-load patterns using source gating.

Recipe 1 — Single Source Bulk Load

Gate one source table during a large data import. The scheduler pauses refreshes for all stream tables that depend on this source.

-- 1. Gate the source before loading.
SELECT pgtrickle.gate_source('orders');

-- 2. Load the data.  The scheduler sits idle for orders-dependent STs.
COPY orders FROM '/data/orders_2026.csv' WITH (FORMAT csv, HEADER);

-- 3. Ungate.  On the next tick the scheduler refreshes everything cleanly.
SELECT pgtrickle.ungate_source('orders');

Recipe 2 — Coordinated Multi-Source Load

When multiple sources feed into a shared downstream stream table, gate them all before loading so no intermediate refreshes occur.

-- 1. Gate all sources that will be loaded.
SELECT pgtrickle.gate_source('orders');
SELECT pgtrickle.gate_source('order_lines');

-- 2. Load each source (can be parallel, any order).
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER);
COPY order_lines FROM '/data/lines.csv' WITH (FORMAT csv, HEADER);

-- 3. Ungate all sources.  The scheduler refreshes downstream STs once.
SELECT pgtrickle.ungate_source('orders');
SELECT pgtrickle.ungate_source('order_lines');

Recipe 3 — Gate + Deferred Initialization

Combine gating with initialize => false to prevent incomplete initial population when sources are loaded asynchronously.

-- 1. Gate sources before creating any stream tables.
SELECT pgtrickle.gate_source('orders');
SELECT pgtrickle.gate_source('order_lines');

-- 2. Create stream tables without initial population.
SELECT pgtrickle.create_stream_table(
    'order_summary',
    'SELECT region, SUM(amount) FROM orders GROUP BY region',
    '1m', initialize => false
);
SELECT pgtrickle.create_stream_table(
    'order_report',
    'SELECT s.region, s.total, l.line_count
     FROM order_summary s
     JOIN (SELECT region, COUNT(*) AS line_count FROM order_lines GROUP BY region) l
       USING (region)',
    '1m', initialize => false
);

-- 3. Run ETL processes (can be in separate transactions).
BEGIN;
  COPY orders FROM 's3://warehouse/orders.parquet';
  SELECT pgtrickle.ungate_source('orders');
COMMIT;

BEGIN;
  COPY order_lines FROM 's3://warehouse/lines.parquet';
  SELECT pgtrickle.ungate_source('order_lines');
COMMIT;

-- 4. Once all sources are ungated, the scheduler initializes and refreshes
--    all stream tables in dependency order.

Recipe 4 — Nightly Batch Pattern

For scheduled ETL that runs overnight, gate sources before the batch starts and ungate after the batch completes.

-- Nightly ETL script:

-- Gate all sources that will be refreshed.
SELECT pgtrickle.gate_source('sales');
SELECT pgtrickle.gate_source('inventory');

-- Truncate and reload (or use COPY, INSERT...SELECT, etc.).
TRUNCATE sales;
COPY sales FROM '/data/nightly/sales.csv' WITH (FORMAT csv, HEADER);

TRUNCATE inventory;
COPY inventory FROM '/data/nightly/inventory.csv' WITH (FORMAT csv, HEADER);

-- All data loaded — ungate and let the scheduler handle the rest.
SELECT pgtrickle.ungate_source('sales');
SELECT pgtrickle.ungate_source('inventory');

-- Verify: check the gate status to confirm everything is ungated.
SELECT * FROM pgtrickle.bootstrap_gate_status();

Recipe 5 — Monitoring During a Gated Load

Use bootstrap_gate_status() to monitor progress when streams appear stalled.

-- Check which sources are currently gated and how long they've been paused.
SELECT source_table, gate_duration, affected_stream_tables
FROM pgtrickle.bootstrap_gate_status()
WHERE gated = true;

-- If a gate has been active too long (e.g. ETL failed), ungate manually.
SELECT pgtrickle.ungate_source('stale_source');

Watermark Gating (v0.7.0)

Watermark gating is a scheduling control for ETL pipelines where multiple source tables are populated by separate jobs that finish at different times. Each ETL job declares "I'm done up to timestamp X", and the scheduler waits until all sources in a group are caught up within a configurable tolerance before refreshing downstream stream tables.

Catalog Tables

pgtrickle.pgt_watermarks

Per-source watermark state. One row per source table that has had a watermark advanced.

pgtrickle.pgt_watermark_groups

Watermark group definitions. Each group declares that a set of sources must be temporally aligned.

pgtrickle.pgt_template_cache

Added in v0.16.0. Cross-backend delta SQL template cache (UNLOGGED). Stores compiled delta query templates so new backends skip the ~45 ms DVM parse+differentiate step. Managed automatically — no user interaction required.

Functions

pgtrickle.advance_watermark(source TEXT, watermark TIMESTAMPTZ)

Signal that a source table's data is complete through the given timestamp.

• Monotonic: rejects watermarks that go backward (raises error).
• Idempotent: advancing to the same value is a silent no-op.
• Transactional: the watermark is part of the caller's transaction.

SELECT pgtrickle.advance_watermark('orders', '2026-03-01 12:05:00+00');

pgtrickle.create_watermark_group(group_name TEXT, sources TEXT[], tolerance_secs FLOAT8 DEFAULT 0)

Create a watermark group. Requires at least 2 sources.

• tolerance_secs: maximum allowed lag between the most-advanced and least-advanced watermarks. Default 0 means strict alignment.

SELECT pgtrickle.create_watermark_group(
    'order_pipeline',
    ARRAY['orders', 'order_lines'],
    0    -- strict alignment (default)
);

pgtrickle.drop_watermark_group(group_name TEXT)

Remove a watermark group by name.

SELECT pgtrickle.drop_watermark_group('order_pipeline');

pgtrickle.watermarks()

Return the current watermark state for all registered sources.

SELECT * FROM pgtrickle.watermarks();

pgtrickle.watermark_groups()

Return all watermark group definitions.

SELECT * FROM pgtrickle.watermark_groups();

pgtrickle.watermark_status()

Return live alignment status for each watermark group.

SELECT * FROM pgtrickle.watermark_status();

Recipes

Recipe 6 — Nightly ETL with Watermarks

-- Create a watermark group for the order pipeline.
SELECT pgtrickle.create_watermark_group(
    'order_pipeline',
    ARRAY['orders', 'order_lines']
);

-- Nightly ETL job 1: Load orders
BEGIN;
  COPY orders FROM '/data/orders_20260301.csv';
  SELECT pgtrickle.advance_watermark('orders', '2026-03-01');
COMMIT;

-- Nightly ETL job 2: Load order lines (may run later)
BEGIN;
  COPY order_lines FROM '/data/lines_20260301.csv';
  SELECT pgtrickle.advance_watermark('order_lines', '2026-03-01');
COMMIT;

-- order_report refreshes on the next tick after both watermarks align.

Recipe 7 — Micro-Batch Tolerance

-- Allow up to 30 seconds of skew between trades and quotes.
SELECT pgtrickle.create_watermark_group(
    'realtime_pipeline',
    ARRAY['trades', 'quotes'],
    30   -- 30-second tolerance
);

-- External process advances watermarks every few seconds.
SELECT pgtrickle.advance_watermark('trades', '2026-03-01 12:00:05+00');
SELECT pgtrickle.advance_watermark('quotes', '2026-03-01 12:00:02+00');
-- Lag is 3s, within 30s tolerance → stream tables refresh normally.

Recipe 8 — Monitoring Watermark Alignment

-- Check which groups are currently misaligned.
SELECT group_name, lag_secs, aligned
FROM pgtrickle.watermark_status()
WHERE NOT aligned;

-- Check individual source watermarks.
SELECT source_table, watermark, updated_at
FROM pgtrickle.watermarks()
ORDER BY watermark;

Stuck Watermark Detection (WM-7, v0.15.0)

When pg_trickle.watermark_holdback_timeout is set to a positive value (seconds), the scheduler periodically checks all watermark sources. If any source in a watermark group has not been advanced within the timeout, downstream stream tables in that group are paused (refresh is skipped) and a pgtrickle_alert NOTIFY is emitted.

This protects against silent data staleness when an ETL pipeline breaks and stops advancing watermarks -- without this guard, stream tables would continue refreshing with stale external data.

Behavior:

• Stuck detection: Every ~60 seconds, the scheduler checks updated_at for all watermark sources. If now() - updated_at > watermark_holdback_timeout, the source is stuck.
• Pause: Any stream table whose source set overlaps a group containing a stuck source is skipped. A SKIP record with "stuck" in the reason is logged to pgt_refresh_history.
• Alert: A pgtrickle_alert NOTIFY with event watermark_stuck is emitted (once per newly-stuck source, not repeated every check cycle).
• Auto-resume: When the stuck watermark is advanced via advance_watermark(), the next scheduler check detects the advancement, lifts the pause, and emits a watermark_resumed event.

Recipe 9 — Stuck Watermark Protection

-- Enable stuck-watermark detection with a 10-minute timeout.
ALTER SYSTEM SET pg_trickle.watermark_holdback_timeout = 600;
SELECT pg_reload_conf();

-- Listen for alerts in a monitoring process.
LISTEN pgtrickle_alert;

-- When the ETL pipeline breaks and stops calling advance_watermark(),
-- the scheduler will start skipping downstream STs after 10 minutes.
-- You'll receive a NOTIFY payload like:
--   {"event":"watermark_stuck","group":"order_pipeline","source_oid":16385,"age_secs":620}

-- When the ETL pipeline recovers and advances the watermark:
SELECT pgtrickle.advance_watermark('orders', '2026-03-02 00:00:00+00');
-- The scheduler automatically resumes, and you'll receive:
--   {"event":"watermark_resumed","source_oid":16385}

Developer Diagnostics (v0.12.0)

Four SQL-callable introspection functions that surface internal DVM state without side-effects. All functions are read-only — they never modify catalog tables or trigger refreshes.

pgtrickle.explain_query_rewrite(query TEXT)

Walk a query through the full DVM rewrite pipeline and report each pass.

Returns one row per rewrite pass. When a pass changes the query, changed = true and sql_after contains the SQL after the transformation. Two synthetic rows are appended: topk_detection (detects ORDER BY … LIMIT) and dvm_patterns (lists detected DVM constructs such as aggregation strategy, join types, and volatility).

SELECT pass_name, changed, sql_after
FROM pgtrickle.explain_query_rewrite(
  'SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id'
);

Return columns:

Rewrite passes (in order):

pgtrickle.diagnose_errors(name TEXT)

Return the last 5 FAILED refresh events for a stream table, with each error classified by type and supplied with a remediation hint.

SELECT event_time, error_type, error_message, remediation
FROM pgtrickle.diagnose_errors('my_stream_table');

Return columns:

Error types:

pgtrickle.list_auxiliary_columns(name TEXT)

List all __pgt_* internal columns on a stream table's storage relation, with an explanation of each column's role.

These columns are normally hidden from SELECT * output. This function surfaces them for debugging and operator visibility.

SELECT column_name, data_type, purpose
FROM pgtrickle.list_auxiliary_columns('my_stream_table');

Return columns:

Common auxiliary columns:

pgtrickle.validate_query(query TEXT)

Parse and validate a query through the DVM pipeline without creating a stream table. Returns detected SQL constructs, warnings, and the resolved refresh mode.

SELECT check_name, result, severity
FROM pgtrickle.validate_query(
  'SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id'
);

Return columns:

The first row always has check_name = 'resolved_refresh_mode' with the mode that would be assigned under refresh_mode = 'AUTO': DIFFERENTIAL, FULL, or TOPK.

Common check names:

Example output for a GROUP_RESCAN query:

SELECT check_name, result, severity
FROM pgtrickle.validate_query(
  'SELECT grp, STRING_AGG(tag, '','') FROM events GROUP BY grp'
);

Note on GROUP_RESCAN: STRING_AGG, ARRAY_AGG, BOOL_AND, and other non-algebraic aggregates use a group-rescan strategy — any change in a group triggers full re-aggregation from the source data for that group. This is still DIFFERENTIAL (only changed groups are rescanned), but has higher per-group cost than algebraic strategies. If this is performance-sensitive, consider pre-aggregating with a simpler aggregate and post-processing.

Delta SQL Profiling (v0.13.0)

pgtrickle.explain_delta(st_name text, format text DEFAULT 'text')

Generate the delta SQL query plan for a stream table without executing a refresh.

explain_delta produces the differential delta SQL that would be used on the next DIFFERENTIAL refresh, then runs EXPLAIN (ANALYZE false, FORMAT <format>) on it and returns the plan lines. This function is useful for:

• Identifying slow joins or missing indexes in auto-generated delta SQL.
• Comparing plan complexity between different query forms.
• Monitoring how the size of change buffers affects plan shape.

The delta SQL is generated against a hypothetical "scan all changes" window (LSN 0/0 → FF/FFFFFFFF) so the plan shows the full join/filter structure even when the change buffer is currently empty.

Parameters:

Returns: SETOF text — one row per plan line (text format) or one row containing the full JSON/XML/YAML plan.

Example:

-- Show the text plan for the delta query
SELECT line FROM pgtrickle.explain_delta('public.orders_summary');

-- Get the JSON plan for programmatic analysis
SELECT line FROM pgtrickle.explain_delta('public.orders_summary', 'json');

Environment variable (PGS_PROFILE_DELTA=1): When the environment variable PGS_PROFILE_DELTA=1 is set in the PostgreSQL server process, every DIFFERENTIAL refresh automatically captures EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) for the resolved delta SQL and writes the plan to /tmp/delta_plans/<schema>_<table>.json. This is intended for E2E test diagnostics and local profiling sessions.

pgtrickle.dedup_stats()

Show MERGE deduplication profiling counters accumulated since server start.

When the delta cannot be guaranteed to contain at most one row per __pgt_row_id (e.g. for aggregate queries or keyless sources), the MERGE must group and aggregate the delta before merging. This is tracked as dedup needed. A consistently high ratio indicates that pre-MERGE compaction in the change buffer would reduce refresh latency.

Returns: one row with:

Example:

SELECT * FROM pgtrickle.dedup_stats();
-- total_diff_refreshes | dedup_needed | dedup_ratio_pct
-- ----------------------+--------------+-----------------
--                  1234 |           87 |            7.05

A dedup_ratio_pct ≥ 10 is the threshold recommended for investigating a two-pass MERGE strategy. See plans/performance/REPORT_OVERALL_STATUS.md §14 for background.

pgtrickle.shared_buffer_stats()

Added in v0.13.0

D-4 observability function. Returns one row per shared change buffer (one per tracked source table), showing how many stream tables share the buffer, which columns are tracked, the safe cleanup frontier, and the current buffer size.

Return columns:

Example:

SELECT * FROM pgtrickle.shared_buffer_stats();
-- source_oid | source_table       | consumer_count | consumers                          | columns_tracked | safe_frontier_lsn | buffer_rows | is_partitioned
-- -----------+--------------------+----------------+------------------------------------+-----------------+-------------------+-------------+----------------
--      16456 | public.orders      |              3 | public.orders_by_region, public... |               5 | 0/1A2B3C4D        |         142 | f

UNLOGGED Change Buffers (v0.14.0)

pgtrickle.convert_buffers_to_unlogged()

Converts all existing logged change buffer tables to UNLOGGED. This eliminates WAL writes for trigger-inserted CDC rows, reducing WAL amplification by ~30%.

Returns: bigint — the number of buffer tables converted.

SELECT pgtrickle.convert_buffers_to_unlogged();
-- convert_buffers_to_unlogged
-- ----------------------------
--                            5

Warning: Each conversion acquires ACCESS EXCLUSIVE lock on the buffer table. Run this function during a low-traffic maintenance window to minimize lock contention.

After conversion: Buffer contents will be lost on crash recovery. The scheduler automatically detects this and enqueues a FULL refresh for affected stream tables. See pg_trickle.unlogged_buffers for the full trade-off discussion.

Refresh Mode Diagnostics (v0.14.0)

pgtrickle.recommend_refresh_mode(st_name TEXT DEFAULT NULL)

Analyze stream table workload characteristics and recommend the optimal refresh mode (FULL vs DIFFERENTIAL). When st_name is NULL, returns one row per stream table. When provided, returns a single row for the named stream table.

The function evaluates seven weighted signals — change ratio, empirical timing, query complexity, target size, index coverage, and latency variance — and computes a composite score. Scores above +0.15 recommend DIFFERENTIAL; below −0.15 recommend FULL; in between, the function recommends KEEP (current mode is near-optimal).

Parameters:

Return columns:

Example:

-- Check all stream tables
SELECT pgt_name, current_mode, recommended_mode, confidence, reason
FROM pgtrickle.recommend_refresh_mode();

-- Check a specific stream table
SELECT recommended_mode, confidence, reason, signals
FROM pgtrickle.recommend_refresh_mode('public.orders_summary');

Signal weights:

pgtrickle.refresh_efficiency()

Per-table refresh efficiency metrics. Returns operational statistics for every stream table — useful for monitoring dashboards and Grafana alerts.

Return columns:

Example:

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

Export API (v0.14.0)

pgtrickle.export_definition(st_name TEXT)

Export a stream table's configuration as reproducible DDL. Returns a SQL script containing DROP STREAM TABLE IF EXISTS followed by SELECT pgtrickle.create_stream_table(...) with all configured options, plus any ALTER STREAM TABLE calls for post-creation settings (tier, fuse mode, etc.).

Parameters:

Returns: text — SQL script that recreates the stream table.

Example:

-- Export a single definition
SELECT pgtrickle.export_definition('public.orders_summary');

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

dbt Integration (v0.13.0)

The dbt-pgtrickle package exposes two new config(...) keys added in v0.13.0: partition_by and the fuse circuit-breaker options. Use them directly in any stream_table materialization model.

For full dbt documentation see dbt-pgtrickle/README.md.

partition_by config

Partition the stream table's underlying storage table using PostgreSQL PARTITION BY RANGE. Only applied at creation time — changing it after the stream table exists has no effect (use --full-refresh to recreate).

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

SELECT
    event_day,
    user_id,
    COUNT(*) AS event_count
FROM {{ source('raw', 'events') }}
GROUP BY event_day, user_id

pg_trickle creates a PARTITION BY RANGE (event_day) storage table with an automatic default catch-all partition. Add named partitions via standard DDL:

CREATE TABLE analytics.events_by_day_2026
  PARTITION OF analytics.events_by_day
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

The partition_by value is stored in pgtrickle.pgt_stream_tables.st_partition_key and visible via pgtrickle.stream_tables_info.

fuse config

The fuse circuit breaker suspends differential refreshes when the incoming change volume exceeds a threshold, preventing runaway refresh cycles during bulk ingestion. Fuse parameters are applied via alter_stream_table() on every dbt run; they are a no-op if the values have not changed.

-- models/marts/order_totals.sql
{{ config(
    materialized='stream_table',
    schedule='5m',
    refresh_mode='DIFFERENTIAL',
    fuse='auto',
    fuse_ceiling=50000,
    fuse_sensitivity=3
) }}

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

Monitor fuse state via pgtrickle.dedup_stats() or check pgtrickle.pgt_stream_tables.fuse_state directly:

SELECT pgt_name, fuse_mode, fuse_state, fuse_ceiling, fuse_sensitivity
FROM pgtrickle.pgt_stream_tables
WHERE fuse_mode != 'off';

Dog Feeding — Self-Monitoring (v0.20.0)

Added in v0.20.0.

pg_trickle can monitor itself using its own stream tables. Five dog-feeding stream tables maintain reactive analytics over the internal catalog, replacing repeated full-scan diagnostic queries with continuously-maintained incremental views.

Quick Start

-- Create all five dog-feeding stream tables (idempotent).
SELECT pgtrickle.setup_dog_feeding();

-- Check status.
SELECT * FROM pgtrickle.dog_feeding_status();

-- View threshold recommendations (after 10+ refresh cycles).
SELECT * FROM pgtrickle.df_threshold_advice
WHERE confidence IN ('HIGH', 'MEDIUM');

-- View anomalies.
SELECT * FROM pgtrickle.df_anomaly_signals
WHERE duration_anomaly IS NOT NULL;

-- Enable auto-apply (optional).
SET pg_trickle.dog_feeding_auto_apply = 'threshold_only';

-- Clean up.
SELECT pgtrickle.teardown_dog_feeding();

pgtrickle.setup_dog_feeding()

Creates all five dog-feeding stream tables. Idempotent — safe to call multiple times. Emits a warm-up warning if pgt_refresh_history has fewer than 50 rows.

Stream tables created:

pgtrickle.teardown_dog_feeding()

Drops all dog-feeding stream tables. Safe with partial setups — missing tables are silently skipped. User stream tables are never affected.

pgtrickle.dog_feeding_status()

Returns the status of all five expected dog-feeding stream tables:

pgtrickle.scheduler_overhead()

Returns scheduler efficiency metrics for the last hour:

pgtrickle.explain_dag(format)

Returns the full refresh DAG as a Mermaid markdown (default) or Graphviz DOT string. Node colours: user STs = blue, dog-feeding STs = green, suspended = red, fused = orange.

-- Mermaid format (default).
SELECT pgtrickle.explain_dag();

-- Graphviz DOT format.
SELECT pgtrickle.explain_dag('dot');

Auto-Apply Policy

The pg_trickle.dog_feeding_auto_apply GUC controls whether analytics can automatically adjust stream table configuration:

Auto-apply is rate-limited to at most one threshold change per stream table per 10 minutes. Changes are logged to pgt_refresh_history with initiated_by = 'DOG_FEED'.

Confidence Levels and Sparse History

df_threshold_advice assigns a confidence level to each recommendation:

When you see LOW confidence: This is normal during the first minutes after setup_dog_feeding(). The stream tables need time to accumulate refresh history. In typical deployments with a 1-minute schedule, expect:

• LOW for the first ~10 minutes
• MEDIUM after ~10 minutes
• HIGH after ~20 minutes (requires at least 2 FULL refreshes — these happen naturally when the auto-threshold triggers a mode switch)

If a stream table uses FULL mode exclusively, the advice will remain at MEDIUM because no DIFFERENTIAL observations exist for comparison.

The sla_headroom_pct column shows how much faster DIFFERENTIAL is compared to FULL as a percentage. A value of 70% means "DIFF is 70% faster than FULL". This column is NULL when either FULL or DIFF observations are missing.

Public API Stability Contract

Added in v0.19.0 (DB-6).

Stable (will not break without a major version bump)

Unstable (may change in any release)

Versioning Policy

• Patch releases (0.x.Y): Bug fixes only. No breaking changes.
• Minor releases (0.X.0): New features. Stable API preserved; unstable surfaces may change. Breaking changes to stable API only with a deprecation cycle (WARNING for one release, removal in the next).
• Major release (1.0.0): Stable API locked. Breaking changes require a major version bump.

Configuration

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

Table of Contents

• Overview
• GUC Variables
  • Essential
    • pg_trickle.enabled
    • pg_trickle.cdc_mode
    • pg_trickle.scheduler_interval_ms
    • pg_trickle.event_driven_wake
    • pg_trickle.wake_debounce_ms
    • pg_trickle.min_schedule_seconds
    • pg_trickle.default_schedule_seconds
    • pg_trickle.max_consecutive_errors
  • WAL CDC
    • pg_trickle.wal_transition_timeout
    • pg_trickle.slot_lag_warning_threshold_mb
    • pg_trickle.slot_lag_critical_threshold_mb
  • Refresh Performance
    • pg_trickle.differential_max_change_ratio
    • pg_trickle.refresh_strategy
    • pg_trickle.cost_model_safety_margin
    • pg_trickle.max_delta_estimate_rows
    • pg_trickle.planner_aggressive
    • pg_trickle.merge_join_strategy
    • pg_trickle.merge_strategy
    • pg_trickle.merge_strategy_threshold
    • pg_trickle.merge_planner_hints (deprecated)
    • pg_trickle.merge_work_mem_mb
    • pg_trickle.merge_seqscan_threshold
    • pg_trickle.auto_backoff
    • pg_trickle.tiered_scheduling
    • pg_trickle.cleanup_use_truncate
    • pg_trickle.use_prepared_statements
    • pg_trickle.user_triggers
  • Guardrails & Limits
    • pg_trickle.block_source_ddl
    • pg_trickle.buffer_alert_threshold
    • pg_trickle.compact_threshold
    • pg_trickle.max_buffer_rows
    • pg_trickle.auto_index
    • pg_trickle.aggregate_fast_path
    • pg_trickle.template_cache
    • pg_trickle.buffer_partitioning
    • pg_trickle.max_grouping_set_branches
    • pg_trickle.max_parse_depth
    • pg_trickle.ivm_topk_max_limit
    • pg_trickle.ivm_recursive_max_depth
  • Parallel Refresh
    • pg_trickle.parallel_refresh_mode
    • pg_trickle.max_dynamic_refresh_workers
    • pg_trickle.max_concurrent_refreshes
    • pg_trickle.per_database_worker_quota
  • Advanced / Internal
    • pg_trickle.change_buffer_schema
    • pg_trickle.foreign_table_polling
    • pg_trickle.matview_polling
    • pg_trickle.cdc_trigger_mode
    • pg_trickle.tick_watermark_enabled
    • pg_trickle.watermark_holdback_timeout
    • pg_trickle.spill_threshold_blocks
    • pg_trickle.spill_consecutive_limit
    • pg_trickle.log_merge_sql
  • Guardrails & Diagnostics
    • pg_trickle.fuse_default_ceiling
    • pg_trickle.delta_amplification_threshold
    • pg_trickle.algebraic_drift_reset_cycles
    • pg_trickle.agg_diff_cardinality_threshold
  • Connection Pooler
    • pg_trickle.connection_pooler_mode
  • History & Retention
    • pg_trickle.history_retention_days
  • Circular Dependencies
    • pg_trickle.allow_circular
    • pg_trickle.max_fixpoint_iterations
• GUC Interaction Matrix
• Tuning Profiles
  • Low-Latency Profile
  • High-Throughput Profile
  • Resource-Constrained Profile
• Complete postgresql.conf Example
• Runtime Configuration
• Further Reading