pg_trickle vs pg_ivm — Comparison Report & Gap Analysis

Date: 2026-02-28 (merged 2026-03-01, updated 2026-03-20) Author: Internal research Status: Reference document

1. Executive Summary

Both pg_trickle and pg_ivm implement Incremental View Maintenance (IVM) as PostgreSQL extensions — the goal of keeping materialized query results up-to-date without full recomputation. Despite the shared objective they differ fundamentally in design philosophy, maintenance model, SQL coverage, operational model, and target audience.

pg_ivm is a mature, widely-deployed C extension (1.4k GitHub stars, 17 releases) focused on immediate, synchronous IVM that runs inside the same transaction as the base-table write. pg_trickle is a Rust extension (v0.9.0) offering both deferred (scheduled) and immediate (transactional) IVM with a richer SQL dialect, a dependency DAG, and built-in operational tooling.

pg_trickle is significantly ahead of pg_ivm in SQL coverage, operator support, aggregate support, and operational features. As of v0.2.1, pg_trickle also matches pg_ivm’s core strength — immediate, in-transaction maintenance — via the IMMEDIATE refresh mode (all phases complete). pg_ivm’s one remaining structural advantage is broader PostgreSQL version support (PG 13–18):

  • IMMEDIATE mode — fully implemented. Statement-level AFTER triggers with transition tables update stream tables within the same transaction as base-table DML. Window functions, LATERAL, scalar subqueries, cascading IMMEDIATE stream tables, WITH RECURSIVE (with a stack-depth warning), and TopK micro-refresh are all supported. See PLAN_TRANSACTIONAL_IVM.md.
  • AUTO refresh mode — new default for create_stream_table. Selects DIFFERENTIAL when the query supports it and transparently falls back to FULL otherwise, eliminating the need to choose a mode at creation time.
  • pg_ivm compatibility layer — postponed. The pgivm.create_immv() / pgivm.refresh_immv() / pgivm.pg_ivm_immv wrappers (Phase 2) are deferred to post-1.0.
  • PLAN_PG_BACKCOMPAT.md details backporting pg_trickle to PG 14–18 (recommended) or PG 16–18 (minimum viable), requiring ~2.5–3 weeks of effort primarily in #[cfg]-gating ~435 lines of JSON/SQL-standard parse-tree handling.

With IMMEDIATE mode fully implemented, Row Level Security support (v0.5.0), pg_dump/restore support (v0.8.0), algebraic aggregate maintenance (v0.9.0), parallel refresh (v0.4.0), circular pipeline support (v0.7.0), watermark APIs (v0.7.0), and 40+ unique features, pg_ivm’s only remaining advantages are PG version breadth and production maturity.

2. Project Overview

Attribute pg_ivm pg_trickle
Repository sraoss/pg_ivm grove/pg-trickle
Language C Rust (pgrx 0.17)
Latest release 1.13 (2025-10-20) 0.9.0 (2026-03-20)
Stars ~1,400 early stage
License PostgreSQL License Apache 2.0
PG versions 13 – 18 18 only; PG 14–18 planned
Schema pgivm pgtrickle / pgtrickle_changes
Shared library required Yes (shared_preload_libraries or session_preload_libraries) Yes (shared_preload_libraries, required for background worker)
Background worker No Yes (scheduler + optional WAL decoder)

3. Maintenance Model

This is the most important design difference between the two extensions.

pg_ivm — Immediate Maintenance

pg_ivm updates its views synchronously inside the same transaction that modified the base table. When a row is inserted/updated/deleted, AFTER row triggers fire and update the IMMV before the transaction commits.

BEGIN;
  UPDATE base_table ...;   -- triggers fire here
  -- IMMV is updated before COMMIT
COMMIT;

Consequences:

  • The IMMV is always exactly consistent with the committed state of the base table — zero staleness.
  • Write latency increases by the cost of view maintenance. For large joins or aggregates on popular tables this can be significant.
  • Locking: ExclusiveLock is held on the IMMV during maintenance to prevent concurrent anomalies. In REPEATABLE READ or SERIALIZABLE isolation, errors are raised when conflicts are detected.
  • TRUNCATE on a base table triggers full IMMV refresh (for most view types).
  • Not compatible with logical replication (subscriber nodes are not updated).

pg_trickle — Deferred, Scheduled Maintenance

pg_trickle updates its stream tables asynchronously, driven by a background worker scheduler. Changes are captured by row-level triggers (or optionally by WAL decoding) into change-buffer tables and are applied in batch on the next refresh cycle.

-- Write path: only a trigger INSERT into change buffer
BEGIN;
  UPDATE base_table ...;   -- trigger captures delta into pgtrickle_changes.*
COMMIT;

-- Separate refresh cycle (background worker):
  apply_delta_to_stream_table(...)

Consequences:

  • Write latency is minimized — the trigger write into the change buffer is ~2–50 μs regardless of view complexity.
  • Stream tables are stale between refresh cycles. The staleness bound is configurable (e.g. '30s', '5m', '@hourly', or cron expressions).
  • Refresh can be triggered manually: pgtrickle.refresh_stream_table(...).
  • Multiple stream tables can share a refresh pipeline ordered by dependency (topological DAG scheduling).
  • The WAL-based CDC mode (pg_trickle.cdc_mode = 'wal') eliminates trigger overhead entirely when wal_level = logical is available.
  • Append-only fast path (v0.5.0): append_only => true skips merge for INSERT-only tables with auto-fallback if DELETE/UPDATE detected.
  • Source gating (v0.5.0): pause CDC during bulk loads via gate_source() and ungate_source() to avoid trigger overhead during large batch inserts.

Implemented: pg_trickle IMMEDIATE Mode

pg_trickle now offers an IMMEDIATE refresh mode (Phase 1 + Phase 3 complete) that uses statement-level AFTER triggers with transition tables — the same mechanism pg_ivm uses. Key implementation details:

  • Reuses the DVM engine — the Scan operator reads from transition tables (via temporary views) instead of change buffer tables.
  • Phase 1 (complete): core IMMEDIATE engine — INSERT/UPDATE/DELETE/TRUNCATE handling, advisory lock-based concurrency (IvmLockMode), mode switching via alter_stream_table, query restriction validation.
  • Phase 2 (postponed): pgivm.* compatibility layer for drop-in migration.
  • Phase 3 (complete): extended SQL support — window functions, LATERAL, scalar subqueries, cascading IMMEDIATE stream tables, WITH RECURSIVE (IM1: supported with a stack-depth warning), and TopK micro-refresh (IM2: recomputes top-K on every DML, gated by pg_trickle.ivm_topk_max_limit).
  • Phase 4 (complete): delta SQL template caching (IVM_DELTA_CACHE); ENR-based transition tables and C-level triggers deferred to post-1.0 as optimizations only.
-- Create an IMMEDIATE stream table (zero staleness)
SELECT pgtrickle.create_stream_table(
    'live_totals',
    'SELECT region, SUM(amount) AS total FROM orders GROUP BY region',
    NULL,          -- no schedule needed
    'IMMEDIATE'
);

-- Updates propagate within the same transaction
BEGIN;
  INSERT INTO orders (region, amount) VALUES ('EU', 100);
  SELECT * FROM live_totals;  -- already includes the new row
COMMIT;

4. SQL Feature Coverage — Summary

Dimension pg_ivm pg_trickle Winner
Maintenance timing Immediate (in-transaction triggers) Deferred (scheduler/manual) and IMMEDIATE (in-transaction) pg_trickle (offers both models)
PostgreSQL versions 13–18 18 only; PG 14–18 planned pg_ivm (today); planned parity
Aggregate functions 5 (COUNT, SUM, AVG, MIN, MAX) 60+ (all built-in aggregates incl. algebraic O(1) for COUNT/SUM/AVG/STDDEV/VAR) pg_trickle
FILTER clause on aggregates No Yes pg_trickle
HAVING clause No Yes pg_trickle
Inner joins Yes (including self-join) Yes (including self-join, NATURAL, nested) pg_trickle
Outer joins Yes (limited — equijoin, single condition, many restrictions) Yes (LEFT/RIGHT/FULL, nested, complex conditions) pg_trickle
DISTINCT Yes (reference-counted) Yes (reference-counted) Tie
DISTINCT ON No Yes (auto-rewritten to ROW_NUMBER) pg_trickle
UNION / INTERSECT / EXCEPT No Yes (all 6 variants, bag + set) pg_trickle
Window functions No Yes (partition recomputation) pg_trickle
CTEs (non-recursive) Simple only (no aggregates, no DISTINCT inside) Full (aggregates, DISTINCT, multi-reference shared delta) pg_trickle
CTEs (recursive) No Yes (semi-naive, DRed, recomputation; IMMEDIATE mode with stack-depth warning) pg_trickle
Subqueries in FROM Simple only (no aggregates/DISTINCT inside) Full support pg_trickle
EXISTS subqueries Yes (WHERE only, AND only, no agg/DISTINCT) Yes (WHERE + targetlist, AND/OR, agg/DISTINCT inside) pg_trickle
NOT EXISTS / NOT IN No Yes (anti-join operator) pg_trickle
IN (subquery) No Yes (semi-join operator) pg_trickle
Scalar subquery in SELECT No Yes (scalar subquery operator) pg_trickle
LATERAL subqueries No Yes (row-scoped recomputation) pg_trickle
LATERAL SRFs No Yes (jsonb_array_elements, unnest, etc.) pg_trickle
JSON_TABLE (PG 17+) No Yes pg_trickle
GROUPING SETS / CUBE / ROLLUP No Yes (auto-rewritten to UNION ALL) pg_trickle
Views as sources No (simple tables only) Yes (auto-inlined, nested) pg_trickle
Partitioned tables No Yes pg_trickle
Foreign tables No FULL mode only pg_trickle
Cascading (view-on-view) No Yes (DAG-aware scheduling) pg_trickle
Background scheduling No (user must trigger) Yes (cron + duration, background worker) pg_trickle
Monitoring / observability 1 catalog table Extensive (stats, history, staleness, CDC health, NOTIFY) pg_trickle
CDC mechanism Triggers only Hybrid (triggers + optional WAL) pg_trickle
DDL tracking No automatic handling Yes (event triggers, auto-reinit) pg_trickle
TRUNCATE handling Yes (auto-truncate IMMV) IMMEDIATE mode: full refresh in same txn; DEFERRED: queued full refresh Tie (functionally equivalent in IMMEDIATE mode)
Auto-indexing Yes (on GROUP BY / DISTINCT / PK columns) No (user creates indexes) pg_ivm
Row Level Security Yes (with limitations) Yes (refreshes see all data; RLS on stream table; IMMEDIATE mode secured) pg_trickle (richer model)
Concurrency model ExclusiveLock on IMMV during maintenance Advisory locks, non-blocking reads, parallel refresh pg_trickle
Data type restrictions Must have btree opclass (no json, xml, point) No documented type restrictions pg_trickle
Maturity / ecosystem 4 years, 1.4k stars, PGXN, yum packages v0.9.0 released, 1,100+ unit tests + 900+ E2E tests, 22 TPC-H benchmarks, dbt integration pg_ivm

4.1 Areas Where pg_ivm Wins

Of the ~35 dimensions in the summary table above, pg_ivm holds an advantage in only 3 (down from 6 before IMMEDIATE mode and RLS were implemented). One is substantive, two are temporary gaps with existing plans.

1. PostgreSQL Version Support (substantive, planned resolution)

pg_ivm ships pre-built packages for PostgreSQL 13–18 across all major Linux distros via yum.postgresql.org and PGXN. pg_trickle currently targets PG 18 only.

This is the single largest remaining structural gap. PG 13 is EOL (Nov 2025), but PG 14–17 are widely deployed in production environments. Users on those versions simply cannot use pg_trickle today.

Planned resolution: PLAN_PG_BACKCOMPAT.md details backporting to PG 14–18 (~2.5–3 weeks). pgrx 0.17 already supports PG 14–18 via feature flags; ~435 lines in parser.rs need #[cfg] gating for JSON/SQL-standard parse-tree handling.

2. Auto-Indexing (substantive, low priority)

When pg_ivm creates an IMMV, it automatically adds indexes on columns used in GROUP BY, DISTINCT, and primary keys. This is a genuine usability advantage — new users get reasonable read performance without manual intervention.

pg_trickle leaves index creation entirely to the user. For DIFFERENTIAL mode stream tables, the DVM engine’s MERGE-based delta application already uses the stream table’s primary key (which is auto-created), and index-aware MERGE (pg_trickle.merge_seqscan_threshold, added v0.9.0) uses index lookups for tiny change ratios, but secondary indexes for read-side query patterns must be added manually.

Impact: Low — experienced users always create application-specific indexes anyway. Auto-indexing mostly helps onboarding and simple use-cases.

Planned resolution: Tracked as part of the pg_ivm compatibility layer (Phase 2, postponed to post-1.0). Could also be implemented independently as a CREATE INDEX IF NOT EXISTS step in create_stream_table.

3. Maturity / Ecosystem (temporary, closing over time)

pg_ivm has 4 years of production use, ~1,400 GitHub stars, 17 releases, and is distributed via PGXN, yum, and apt package repositories. It has a track record of stability and a community of users.

pg_trickle is a v0.9.0 series release with 1,100+ unit tests, 200+ integration tests, 570+ light E2E tests, 90+ full E2E tests, and 22 TPC-H correctness benchmarks—but no wide production deployments yet. It lacks the battle-testing that comes from years of real-world usage.

Impact: High for risk-averse organizations considering production adoption. Low for greenfield projects or teams willing to adopt early.

Resolution: This gap closes naturally with time, releases, and adoption. The dbt integration (dbt-pgtrickle) and CNPG/Kubernetes deployment support accelerate ecosystem development.

5. Detailed SQL Comparison

5.1 Aggregate Functions

Function pg_ivm pg_trickle
COUNT(*) / COUNT(expr) ✅ Algebraic ✅ Algebraic (O(1) running total, v0.9.0)
SUM ✅ Algebraic ✅ Algebraic (O(1) running total, v0.9.0)
AVG ✅ Algebraic (via SUM/COUNT) ✅ Algebraic (O(1) via SUM/COUNT decomposition, v0.9.0)
MIN ✅ Semi-algebraic (rescan on extremum delete) ✅ Semi-algebraic (O(1) unless extremum deleted, v0.9.0 safety guard)
MAX ✅ Semi-algebraic (rescan on extremum delete) ✅ Semi-algebraic (O(1) unless extremum deleted, v0.9.0 safety guard)
BOOL_AND / BOOL_OR ❌ ✅ Group-rescan
STRING_AGG ❌ ✅ Group-rescan
ARRAY_AGG ❌ ✅ Group-rescan
JSON_AGG / JSONB_AGG ❌ ✅ Group-rescan
BIT_AND / BIT_OR / BIT_XOR ❌ ✅ Group-rescan
JSON_OBJECT_AGG / JSONB_OBJECT_AGG ❌ ✅ Group-rescan
STDDEV / VARIANCE (all variants) ❌ ✅ Algebraic (O(1) sum-of-squares decomposition, v0.9.0)
MODE / PERCENTILE_CONT / PERCENTILE_DISC ❌ ✅ Group-rescan
CORR / COVAR / REGR_* (11 functions) ❌ ✅ Group-rescan
ANY_VALUE (PG 16+) ❌ ✅ Group-rescan
JSON_ARRAYAGG / JSON_OBJECTAGG (PG 16+) ❌ ✅ Group-rescan
User-defined aggregates (CREATE AGGREGATE) ❌ ✅ Group-rescan
FILTER (WHERE) clause ❌ ✅
WITHIN GROUP (ORDER BY) ❌ ✅
COUNT(DISTINCT expr) / SUM(DISTINCT expr) ❌ ✅
Total 5 60+

Gap for pg_ivm: Massive. Only 5 of ~60 built-in aggregate functions are supported. pg_trickle v0.9.0 also introduced algebraic (O(1)) maintenance for COUNT, SUM, AVG, STDDEV, and VARIANCE — meaning these aggregates update in constant time per changed row via running totals, whereas pg_ivm’s algebraic support is limited to COUNT, SUM, AVG. pg_trickle additionally supports user-defined aggregates via group-rescan and floating-point drift correction (pg_trickle.algebraic_drift_reset_cycles).

5.2 Joins

Feature pg_ivm pg_trickle
Inner join ✅ ✅
Self-join ✅ ✅
LEFT JOIN ✅ (restricted) ✅ (full)
RIGHT JOIN ✅ (restricted) ✅ (normalized to LEFT)
FULL OUTER JOIN ✅ (restricted) ✅ (8-part delta)
NATURAL JOIN ? ✅
Cross join ? ✅
Nested joins (3+ tables) ✅ ✅
Non-equi joins (theta) ? ✅
Outer join + aggregates ❌ ✅
Outer join + subqueries ❌ ✅
Outer join + CASE/non-strict ❌ ✅
Outer join multi-condition ❌ (single equality only) ✅

Gap for pg_ivm: Outer joins are heavily restricted — single equijoin condition, no aggregates, no subqueries, no CASE expressions, no IS NULL in WHERE.

5.3 Subqueries

Feature pg_ivm pg_trickle
Simple subquery in FROM ✅ (no aggregates/DISTINCT inside) ✅ (full support)
EXISTS in WHERE ✅ (AND only, no agg/DISTINCT inside) ✅ (AND + OR, full SQL inside)
NOT EXISTS in WHERE ❌ ✅ (anti-join operator)
IN (subquery) ❌ ✅ (rewritten to semi-join)
NOT IN (subquery) ❌ ✅ (rewritten to anti-join)
ALL (subquery) ❌ ✅ (rewritten to anti-join)
Scalar subquery in SELECT ❌ ✅ (scalar subquery operator)
Scalar subquery in WHERE ❌ ✅ (auto-rewritten to CROSS JOIN)
LATERAL subquery in FROM ❌ ✅ (row-scoped recomputation)
LATERAL SRF in FROM ❌ ✅ (jsonb_array_elements, unnest, etc.)
Subqueries in OR ❌ ✅ (auto-rewritten to UNION)

Gap for pg_ivm: Severely limited subquery support. No anti-joins, no scalar subqueries, no LATERAL, no SRFs.

5.4 CTEs

Feature pg_ivm pg_trickle
Simple non-recursive CTE ✅ (no aggregates/DISTINCT inside) ✅ (full SQL inside)
Multi-reference CTE ? ✅ (shared delta optimization)
Chained CTEs ? ✅
WITH RECURSIVE ❌ ✅ (semi-naive, DRed, recomputation; IMMEDIATE mode with stack-depth warning)

Gap for pg_ivm: No recursive CTEs, no aggregates/DISTINCT inside CTEs.

5.5 Set Operations

Feature pg_ivm pg_trickle
UNION ALL ❌ ✅
UNION (set) ❌ ✅ (via DISTINCT + UNION ALL)
INTERSECT ❌ ✅ (dual-count multiplicity)
INTERSECT ALL ❌ ✅
EXCEPT ❌ ✅ (dual-count multiplicity)
EXCEPT ALL ❌ ✅

Gap for pg_ivm: No set operations at all.

5.6 Window Functions

Feature pg_ivm pg_trickle
ROW_NUMBER, RANK, DENSE_RANK ❌ ✅
SUM/AVG/COUNT OVER () ❌ ✅
Frame clauses (ROWS/RANGE/GROUPS) ❌ ✅
Named WINDOW clauses ❌ ✅
PARTITION BY recomputation ❌ ✅

Gap for pg_ivm: Window functions are completely unsupported.

5.7 DISTINCT & Grouping

Feature pg_ivm pg_trickle
SELECT DISTINCT ✅ ✅
DISTINCT ON (expr, …) ❌ ✅ (auto-rewritten to ROW_NUMBER)
GROUP BY ✅ ✅
GROUPING SETS ❌ ✅ (auto-rewritten to UNION ALL)
CUBE ❌ ✅ (auto-rewritten via GROUPING SETS)
ROLLUP ❌ ✅ (auto-rewritten via GROUPING SETS)
GROUPING() function ❌ ✅
HAVING ❌ ✅

5.8 Source Table Types

Source type pg_ivm pg_trickle
Simple heap tables ✅ ✅
Views ❌ ✅ (auto-inlined)
Materialized views ❌ FULL mode only
Partitioned tables ❌ ✅
Partitions ❌ ✅ (via parent)
Foreign tables ❌ FULL mode only
Other IMMVs / stream tables ❌ ✅ (DAG cascading)

Gap for pg_ivm: Only simple heap tables. No views, no partitioned tables, no cascading.

6. API Comparison

pg_ivm API

-- Create an IMMV
SELECT pgivm.create_immv('myview', 'SELECT * FROM mytab');

-- Full refresh (emergency)
SELECT pgivm.refresh_immv('myview', true);   -- with data
SELECT pgivm.refresh_immv('myview', false);  -- disable maintenance

-- Inspect
SELECT immvrelid, pgivm.get_immv_def(immvrelid)
FROM pgivm.pg_ivm_immv;

-- Drop
DROP TABLE myview;

-- Rename
ALTER TABLE myview RENAME TO myview2;

pg_ivm IMMVs are standard PostgreSQL tables. They can be dropped with DROP TABLE and renamed with ALTER TABLE.

pg_trickle API

-- Create a stream table (AUTO mode: DIFFERENTIAL when possible, FULL fallback)
SELECT pgtrickle.create_stream_table(
    'order_totals',
    'SELECT region, SUM(amount) AS total FROM orders GROUP BY region'
    -- refresh_mode defaults to 'AUTO', schedule defaults to 'calculated'
);

-- Create a stream table (explicit deferred, scheduled)
SELECT pgtrickle.create_stream_table(
    'order_totals',
    'SELECT region, SUM(amount) AS total FROM orders GROUP BY region',
    schedule     => '2m',
    refresh_mode => 'DIFFERENTIAL'
);

-- Create a stream table (immediate, in-transaction)
SELECT pgtrickle.create_stream_table(
    'live_totals',
    'SELECT region, SUM(amount) AS total FROM orders GROUP BY region',
    schedule     => NULL,
    refresh_mode => 'IMMEDIATE'
);

-- Manual refresh
SELECT pgtrickle.refresh_stream_table('order_totals');

-- Alter schedule, mode, or defining query
SELECT pgtrickle.alter_stream_table('order_totals', schedule => '5m');
SELECT pgtrickle.alter_stream_table(
    'order_totals',
    query => 'SELECT region, SUM(amount) AS total FROM orders WHERE active GROUP BY region'
);

-- Drop
SELECT pgtrickle.drop_stream_table('order_totals');

-- Status and monitoring
SELECT * FROM pgtrickle.pgt_status();
SELECT * FROM pgtrickle.pg_stat_stream_tables;
SELECT * FROM pgtrickle.pgt_stream_tables;

-- DAG inspection
SELECT * FROM pgtrickle.pgt_dependencies;

-- Extended observability (added v0.2.0+)
SELECT * FROM pgtrickle.change_buffer_sizes();  -- CDC buffer health
SELECT * FROM pgtrickle.list_sources('order_totals');  -- source table stats
SELECT * FROM pgtrickle.dependency_tree();  -- ASCII DAG view
SELECT * FROM pgtrickle.health_check();  -- OK/WARN/ERROR triage
SELECT * FROM pgtrickle.refresh_timeline();  -- cross-stream history
SELECT * FROM pgtrickle.trigger_inventory();  -- CDC trigger audit
SELECT * FROM pgtrickle.diamond_groups();  -- diamond consistency groups

-- Source gating (v0.5.0)
SELECT pgtrickle.gate_source('orders');      -- pause CDC
SELECT pgtrickle.ungate_source('orders');    -- resume CDC
SELECT * FROM pgtrickle.source_gates();      -- gate status

-- Watermarks (v0.7.0)
SELECT pgtrickle.advance_watermark('orders', '2026-03-20 12:00:00');
SELECT pgtrickle.create_watermark_group('sync', ARRAY['orders','products'], 30);
SELECT * FROM pgtrickle.watermarks();
SELECT * FROM pgtrickle.watermark_status();

-- Parallel refresh monitoring (v0.4.0)
SELECT * FROM pgtrickle.worker_pool_status();
SELECT * FROM pgtrickle.parallel_job_status();

-- Refresh groups (v0.9.0)
SELECT pgtrickle.create_refresh_group('my_group', ARRAY['st1','st2']);
SELECT pgtrickle.drop_refresh_group('my_group');

-- Idempotent DDL (v0.6.0)
SELECT pgtrickle.create_or_replace_stream_table(
    'order_totals',
    'SELECT region, SUM(amount) AS total FROM orders GROUP BY region'
);

pg_trickle stream tables are regular PostgreSQL tables but managed through the pgtrickle schema’s API functions. They cannot be renamed with ALTER TABLE (use alter_stream_table).

7. Scheduling and Dependency Management

Capability pg_ivm pg_trickle
Automatic scheduling ❌ (immediate only, no scheduler) ✅ background worker
Manual refresh ✅ refresh_immv() ✅ refresh_stream_table()
Cron schedules ❌ ✅ (standard 5/6-field cron + aliases)
Duration-based staleness bounds ❌ ✅ ('30s', '5m', '1h', …)
Dependency DAG ❌ ✅ (stream tables can reference other stream tables)
Topological refresh ordering ❌ ✅ (upstream refreshes before downstream)
CALCULATED schedule propagation ❌ ✅ (consumers drive upstream schedules)
Parallel refresh ❌ ✅ (worker pool with database + cluster caps, v0.4.0)
Circular pipeline support ❌ ✅ (monotone cycles with fixed-point iteration, v0.7.0)
Watermark coordination ❌ ✅ (multi-source readiness gates, v0.7.0)
Refresh group management ❌ ✅ (atomic multi-ST refresh, v0.9.0)

pg_trickle’s DAG scheduling is a significant differentiator: you can build multi-layer pipelines where each downstream stream table is automatically refreshed after its upstream dependencies.

8. Change Data Capture

Attribute pg_ivm pg_trickle
Mechanism AFTER row triggers (inline, same txn) AFTER row/statement triggers → change buffer
WAL-based CDC ❌ ✅ optional (pg_trickle.cdc_mode = 'wal')
Statement-level triggers ❌ ✅ (v0.4.0, reduced overhead for bulk operations)
Logical replication slots Not used Used in WAL mode only
Write-side overhead Higher (view maintenance in txn) Lower (small trigger insert only)
Change buffer tables None (applied immediately) pgtrickle_changes.changes_<oid>
TRUNCATE handling IMMV truncated/refreshed synchronously Change buffer cleared; full refresh queued

9. Concurrency and Isolation

pg_ivm

  • Holds ExclusiveLock on the IMMV during incremental update.
  • In READ COMMITTED: serializes concurrent updates to the same IMMV.
  • In REPEATABLE READ / SERIALIZABLE: raises an error when a concurrent transaction has already updated the IMMV.
  • Single-table INSERT-only IMMVs use the lighter RowExclusiveLock.

pg_trickle

  • Refresh operations acquire an advisory lock per stream table so only one refresh can run at a time.
  • Base table writes are never blocked by refresh operations.
  • Parallel refresh (v0.4.0): pg_trickle.parallel_refresh_mode = 'on' enables a worker pool with per-database (max_concurrent_refreshes, default 4) and cluster-wide (max_dynamic_refresh_workers) caps.
  • Atomic refresh groups for diamond dependencies.
  • Crash recovery: in-flight refreshes are marked failed on restart; the scheduler retries on the next cycle.

10. Observability

Feature pg_ivm pg_trickle
Catalog of managed views pgivm.pg_ivm_immv pgtrickle.pgt_stream_tables
Per-refresh timing/history ❌ ✅ pgtrickle.pgt_refresh_history
Staleness reporting ❌ ✅ stale column + get_staleness()
Scheduler status ❌ ✅ pgtrickle.pgt_status()
NOTIFY-based alerting ❌ ✅ pgtrickle_refresh channel (10+ alert types)
Error tracking ❌ ✅ consecutive error counter, last error message
dbt integration ❌ ✅ dbt-pgtrickle macro package
Explain/introspection ❌ ✅ explain_st
CDC buffer health ❌ ✅ pgtrickle.change_buffer_sizes() (v0.2.0)
Source table stats ❌ ✅ pgtrickle.list_sources() (v0.2.0)
Dependency tree view ❌ ✅ pgtrickle.dependency_tree() (v0.2.0)
Health triage ❌ ✅ pgtrickle.health_check() (v0.2.0)
Cross-stream refresh history ❌ ✅ pgtrickle.refresh_timeline() (v0.2.0)
CDC trigger audit ❌ ✅ pgtrickle.trigger_inventory() (v0.2.0)
Diamond group inspection ❌ ✅ pgtrickle.diamond_groups() (v0.2.0)
Quick health summary ❌ ✅ pgtrickle.quick_health view (v0.5.0)
Source gating status ❌ ✅ pgtrickle.source_gates() (v0.5.0)
Watermark monitoring ❌ ✅ pgtrickle.watermarks() / watermark_status() (v0.7.0)
Parallel worker status ❌ ✅ pgtrickle.worker_pool_status() / parallel_job_status() (v0.4.0)
SCC cycle status ❌ ✅ pgtrickle.pgt_scc_status() (v0.7.0)
Replication slot health ❌ ✅ pgtrickle.slot_health()
CDC mode per-source ❌ ✅ pgtrickle.pgt_cdc_status view

11. Installation and Deployment

Attribute pg_ivm pg_trickle
Pre-built packages RPM via yum.postgresql.org OCI image, tarball
CNPG / Kubernetes ❌ (no OCI image) ✅ OCI extension image + CNPG smoke tests
Docker local dev Manual ✅ documented + Docker Hub image
shared_preload_libraries Required (or session_preload_libraries) Required
Extension upgrade scripts ✅ (1.0 → 1.1 → … → 1.13) ✅ (0.1.3 → … → 0.9.0, CI completeness check, upgrade E2E tests)
pg_dump / restore Manual IMMV recreation required ✅ Standard pg_dump supported (v0.8.0)

12. Performance Characteristics

pg_ivm

  • Write path: slower — every DML statement triggers inline view maintenance. From the README example: a single row update on a 10M-row join IMMV takes ~15 ms vs ~9 ms for a plain table update.
  • Read path: instant — IMMV is always current, no refresh needed on read.
  • Refresh (full): comparable to REFRESH MATERIALIZED VIEW (~20 seconds for a 10M-row join in the example).

pg_trickle

  • Write path: minimal overhead — only a small trigger INSERT into the change buffer (~2–50 μs per row). In WAL mode, zero trigger overhead. Statement-level CDC triggers (v0.4.0) further reduce overhead for bulk ops.
  • Read path: instant from the materialized table (potentially stale).
  • Refresh (differential): proportional to the number of changed rows, not the total table size. A single-row change on a million-row aggregate touches one row’s worth of computation. Algebraic aggregates (v0.9.0) like COUNT/SUM/AVG/STDDEV/VAR update in O(1) constant time per changed row.
  • Refresh (full): re-runs the entire query; comparable to REFRESH MATERIALIZED VIEW.
  • Parallel refresh (v0.4.0): linear speedup with worker pool size.
  • I/O optimizations (v0.9.0): column skipping, source skipping in joins, WHERE filter push-down, index-aware MERGE for tiny change ratios, scalar subquery short-circuit.

13. Known Limitations

pg_ivm Limitations

  • Adds latency to every write on tracked base tables.
  • Cannot track tables modified via logical replication (subscriber nodes are not updated).
  • pg_dump / pg_upgrade require manual recreation of all IMMVs.
  • Limited aggregate support (no user-defined aggregates, no window functions).
  • Column type restrictions (btree operator class required in target list).
  • No scheduler or background worker — refresh is immediate only.
  • On high-churn tables, min/max aggregates can trigger expensive rescans.

pg_trickle Limitations

  • In DIFFERENTIAL/FULL mode, data is stale between refresh cycles. Use IMMEDIATE mode for zero-staleness, in-transaction consistency.
  • Recursive CTEs in IMMEDIATE mode emit a stack-depth warning; very deep recursion may hit PostgreSQL’s stack limit.
  • Recursive CTEs in DIFFERENTIAL mode fall back to full recomputation for mixed DELETE/UPDATE changes (DRed scheduled for v0.10.0+).
  • LIMIT without ORDER BY is not supported in defining queries.
  • OFFSET without ORDER BY … LIMIT is not supported. Paged TopK (ORDER BY … LIMIT N OFFSET M) is fully supported.
  • ORDER BY + LIMIT (TopK) without OFFSET uses scoped recomputation (MERGE).
  • Volatile SQL functions rejected in DIFFERENTIAL mode.
  • Materialized views as sources not supported in DIFFERENTIAL mode.
  • Window functions in expressions (e.g. CASE WHEN ROW_NUMBER() OVER (...) > 5) require FULL mode.
  • Foreign tables as sources require FULL mode.
  • ALTER EXTENSION pg_trickle UPDATE migration scripts ship from v0.2.1; continuous upgrade path through v0.9.0.
  • Targets PostgreSQL 18 only; no backport to PG 13–17 (planned for PG 14–18).
  • v0.9.x series — extensive testing but not yet production-hardened at scale.

14. PostgreSQL Version Support

pg_ivm pg_trickle (current) pg_trickle (planned)
PG 13 ✅ ❌ ❌ (EOL Nov 2025)
PG 14 ✅ ❌ ✅ (full plan)
PG 15 ✅ ❌ ✅ (full plan)
PG 16 ✅ ❌ ✅ (MVP target)
PG 17 ✅ ❌ ✅ (MVP target)
PG 18 ✅ ✅ ✅

Planned resolution: PLAN_PG_BACKCOMPAT.md:

  • Minimum viable (PG 16–18): ~1.5 weeks effort.
  • Full target (PG 14–18): ~2.5–3 weeks effort.
  • pgrx 0.17.0 already supports PG 14–18 via feature flags.
  • ~435 lines in src/dvm/parser.rs need #[cfg] gating (all in JSON/SQL-standard sections). The remaining ~13,500 lines compile unchanged.

Feature degradation matrix:

Feature PG 14 PG 15 PG 16 PG 17 PG 18
Core streaming tables ✅ ✅ ✅ ✅ ✅
Trigger-based CDC ✅ ✅ ✅ ✅ ✅
Differential refresh ✅ ✅ ✅ ✅ ✅
SQL/JSON constructors — — ✅ ✅ ✅
JSON_TABLE — — — ✅ ✅
WAL-based CDC Needs test Needs test Likely Likely ✅

15. Features Unique to Each System

Features Unique to pg_trickle (42 items, no pg_ivm equivalent)

  1. IMMEDIATE + deferred modes (pg_ivm is immediate-only; pg_trickle offers both)
  2. 60+ aggregate functions (vs 5), including algebraic O(1) for COUNT/SUM/AVG/STDDEV/VAR
  3. FILTER / HAVING / WITHIN GROUP on aggregates
  4. Window functions (partition recomputation)
  5. Set operations (UNION ALL, UNION, INTERSECT, EXCEPT — all 6 variants)
  6. Recursive CTEs (semi-naive, DRed, recomputation; including IMMEDIATE mode with stack-depth warning)
  7. LATERAL subqueries and SRFs (jsonb_array_elements, unnest, JSON_TABLE)
  8. Anti-join / semi-join operators (NOT EXISTS, NOT IN, IN, EXISTS with full SQL)
  9. Scalar subqueries in SELECT list
  10. Views as sources (auto-inlined with nested expansion)
  11. Partitioned table support (RANGE, LIST, HASH with auto-rebuild on ATTACH PARTITION)
  12. Cascading stream tables (ST referencing other STs via DAG)
  13. Background scheduler (cron + duration + canonical periods) with multi-database auto-discovery
  14. GROUPING SETS / CUBE / ROLLUP (auto-rewritten)
  15. DISTINCT ON (auto-rewritten to ROW_NUMBER)
  16. Hybrid CDC (trigger → WAL transition)
  17. DDL change detection and automatic reinitialization (including ALTER FUNCTION body changes)
  18. Monitoring suite (15+ observability functions: change_buffer_sizes, list_sources, dependency_tree, health_check, refresh_timeline, trigger_inventory, diamond_groups, source_gates, watermarks, watermark_groups, watermark_status, worker_pool_status, parallel_job_status, pgt_scc_status, slot_health, check_cdc_health)
  19. Auto-rewrite pipeline (6 transparent SQL rewrites)
  20. Volatile function detection
  21. AUTO refresh mode (smart DIFFERENTIAL/FULL selection with transparent fallback)
  22. ALTER QUERY — change the defining query of an existing stream table online, with schema-change classification and OID-preserving migration
  23. dbt macro package (materialization, status macro, health test, refresh operation)
  24. CNPG / Kubernetes deployment
  25. SQL/JSON constructors (JSON_OBJECT, JSON_ARRAY, etc.)
  26. JSON_TABLE support (PG 17+)
  27. TopK stream tables (ORDER BY + LIMIT, including IMMEDIATE mode via micro-refresh)
  28. Paged TopK (ORDER BY + LIMIT + OFFSET for server-side pagination)
  29. Diamond dependency consistency (multi-path refresh atomicity with SAVEPOINT)
  30. Extension upgrade infrastructure (SQL migration scripts, CI completeness check, upgrade E2E tests, per-release SQL baselines)
  31. Row Level Security (refreshes see all data; RLS policies on ST itself; IMMEDIATE mode secured; internal change buffers shielded from RLS interference) (v0.5.0)
  32. Source gating (pause/resume CDC for bulk loads: gate_source, ungate_source) (v0.5.0)
  33. Append-only fast path (append_only => true skips merge for INSERT-only tables) (v0.5.0)
  34. Parallel refresh (background worker pool with per-database and cluster-wide caps, atomic groups for diamond dependencies) (v0.4.0)
  35. Statement-level CDC triggers (reduced write-side overhead for bulk operations) (v0.4.0)
  36. Circular pipeline support (monotone cycles with fixed-point iteration, max_fixpoint_iterations safety limit, SCC status monitoring) (v0.7.0)
  37. Watermark APIs (delay refresh until multi-source data is ready: advance_watermark, create_watermark_group, tolerance-based readiness) (v0.7.0)
  38. pg_dump / pg_restore support (safe backup with auto-reconnect of streams) (v0.8.0)
  39. Algebraic aggregate maintenance (O(1) constant-time updates for COUNT/SUM/AVG/STDDEV/VAR with floating-point drift correction) (v0.9.0)
  40. Refresh group management (create_refresh_group, drop_refresh_group for atomic multi-ST refresh) (v0.9.0)
  41. Automatic backoff (exponential slowdown for overloaded streams) (v0.9.0)
  42. Index-aware MERGE (use index lookups for tiny change ratios) (v0.9.0)

Features Unique to pg_ivm (with planned resolutions)

# Feature Status Ref
1 Immediate (synchronous) maintenance ✅ Closed — IMMEDIATE refresh mode fully implemented (all phases) PLAN_TRANSACTIONAL_IVM
2 Auto-index creation on GROUP BY / DISTINCT / PK Postponed (Phase 2 of transactional IVM) PLAN_TRANSACTIONAL_IVM §5.2
3 TRUNCATE propagation (auto-truncate IMMV) ✅ Closed — IMMEDIATE mode fires full refresh on TRUNCATE PLAN_TRANSACTIONAL_IVM §3.2
4 Row Level Security respect ✅ Closed — v0.5.0: refreshes see all data; RLS on ST itself; IMMEDIATE mode secured; change buffers shielded ROW_LEVEL_SECURITY.md
5 PostgreSQL 13–17 support PG 14–18 backcompat planned (~2.5–3 weeks) PLAN_PG_BACKCOMPAT
6 session_preload_libraries Not applicable (background worker needs shared_preload) —
7 Rename via ALTER TABLE Event trigger support (low effort) —
8 Drop via DROP TABLE Postponed (Phase 2 of transactional IVM) PLAN_TRANSACTIONAL_IVM §4.3
9 Extension upgrade scripts ✅ Closed — Scripts ship from v0.2.1; CI completeness check and upgrade E2E tests in place —
10 pg_dump / pg_restore ✅ Closed — v0.8.0: safe backup with pg_dump and pg_restore, auto-reconnect streams —

Of the 10 items, 5 are now closed (immediate maintenance, TRUNCATE, RLS, upgrade scripts, pg_dump), 3 have concrete implementation plans, and 2 are low-priority or not applicable.

16. Use-Case Fit

Scenario Recommended
Need views consistent within the same transaction Either (pg_trickle IMMEDIATE mode or pg_ivm)
Application cannot tolerate any view staleness Either (pg_trickle IMMEDIATE mode or pg_ivm)
High write throughput, views can be slightly stale pg_trickle (DIFFERENTIAL mode)
Multi-layer summary pipelines with dependencies pg_trickle
Time-based or cron-driven refresh schedules pg_trickle
Views with complex SQL (window functions, CTEs, UNION) pg_trickle
Simple aggregation with zero-staleness requirement Either (pg_trickle has richer SQL coverage)
Kubernetes / CloudNativePG deployment pg_trickle
dbt integration pg_trickle
Circular / self-referencing pipelines pg_trickle
Multi-source watermark coordination pg_trickle
High-throughput bulk loading (append-only) pg_trickle (append-only fast path)
Row Level Security on analytical summaries pg_trickle (richer RLS model)
pg_dump / pg_restore workflow pg_trickle
PostgreSQL 13–17 pg_ivm
PostgreSQL 18 pg_trickle (superset of pg_ivm)
Production-hardened, stable API pg_ivm
Early adopter, rich SQL coverage needed pg_trickle

17. Coexistence

The two extensions can be installed in the same database simultaneously — they use different schemas (pgivm vs pgtrickle/pgtrickle_changes) and do not interfere with each other. However, with pg_trickle’s IMMEDIATE mode now available and its dramatically broader feature set (v0.9.0), there is little reason to use both:

  • Use pg_trickle IMMEDIATE for small, critical lookup tables that must be perfectly consistent within transactions (the use-case that previously required pg_ivm).
  • Use pg_trickle DIFFERENTIAL/FULL for large analytical summary tables, multi-layer aggregation pipelines, circular pipelines, or views where slight staleness is acceptable.
  • Use pg_trickle AUTO (default) to let the system choose the best strategy.
  • Use pg_ivm only if you need PostgreSQL 13–17 support or prefer its mature, battle-tested codebase.

18. Recommendations

Planned work that closes pg_ivm gaps

Priority Item Plan Effort Closes Gaps
✅ Done IMMEDIATE refresh mode (all phases) PLAN_TRANSACTIONAL_IVM Complete #1 (immediate maintenance), #3 (TRUNCATE)
✅ Done Extension upgrade scripts v0.2.1 release Complete #9 (upgrade scripts)
✅ Done Row Level Security v0.5.0 release Complete #4 (RLS)
✅ Done pg_dump / pg_restore v0.8.0 release Complete #10 (backup/restore)
Postponed pg_ivm compatibility layer PLAN_TRANSACTIONAL_IVM Phase 2 Deferred to post-1.0 #2 (auto-indexing), #7 (rename), #8 (DROP TABLE)
High PG 16–18 backcompat (MVP) PLAN_PG_BACKCOMPAT §11 ~1.5 weeks #5 (PG version support)
Medium PG 14–18 backcompat (full) PLAN_PG_BACKCOMPAT §5 ~2.5–3 weeks #5 (PG version support)

Remaining small gaps (no existing plan)

Priority Item Description Effort
Low ALTER TABLE RENAME Detect rename via event trigger, update catalog 2–4h

Not worth pursuing

Item Reason
PG 13 support EOL since November 2025. Incompatible raw_parser() API.
session_preload_libraries Requires background worker, which needs shared_preload_libraries.

19. Conclusion

pg_trickle covers all of pg_ivm’s SQL surface and extends it dramatically with 55+ additional aggregate functions (including algebraic O(1) maintenance for COUNT/SUM/AVG/STDDEV/VAR), window functions, set operations, recursive CTEs, LATERAL support, anti/semi-joins, circular pipeline support, watermark coordination, parallel refresh, Row Level Security, and a comprehensive operational layer.

The immediate maintenance gap is now fully closed: pg_trickle’s IMMEDIATE refresh mode provides the same in-transaction consistency as pg_ivm, while also supporting window functions, LATERAL, scalar subqueries, WITH RECURSIVE (IM1), TopK micro-refresh (IM2), and cascading stream tables in IMMEDIATE mode — all of which pg_ivm cannot do.

The upgrade infrastructure gap is also closed: v0.2.1 ships SQL migration scripts with continuous upgrade path through v0.9.0, a CI completeness checker, and upgrade E2E tests, matching pg_ivm’s upgrade path story.

The Row Level Security gap is closed (v0.5.0): refreshes see all data, RLS policies on the stream table itself control access, and IMMEDIATE mode is secured with shielded change buffers.

The pg_dump/restore gap is closed (v0.8.0): safe backup with standard PostgreSQL tools and automatic stream reconnection on restore.

The one remaining structural gap is PG version support:

  • PLAN_PG_BACKCOMPAT details backporting to PG 14–18 (or PG 16–18 as MVP) in ~2.5–3 weeks, primarily by #[cfg]- gating ~435 lines of JSON/SQL-standard parse-tree code.

Once backcompat is implemented, pg_trickle will be a strict superset of pg_ivm in every dimension: same immediate maintenance model, comparable PG version support (14–18 vs 13–18, with PG 13 EOL), dramatically wider SQL coverage (60+ aggregates vs 5, 21 DVM operators, 42 unique features), and a complete operational layer that pg_ivm entirely lacks.

For users migrating from pg_ivm, the IMMEDIATE refresh mode already provides the same zero-staleness guarantee. A full compatibility layer (pgivm.create_immv, pgivm.refresh_immv, pgivm.pg_ivm_immv) is planned for post-1.0 to enable zero-change migration.

References