Introducing pg_trickle — incremental view maintenance for PostgreSQL, no external infrastructure required

[grove]

Hey everyone 👋

We've been heads-down building pg_trickle and have just opened the repository.
This is our first public milestone, so we wanted to introduce the project, share
where it stands, and invite you to poke at it, break it, and tell us what would
make it useful for your work.

---

The problem

PostgreSQL's materialized views are powerful but frustrating.
REFRESH MATERIALIZED VIEW re-runs the entire query from scratch, even if
only one row changed in a million-row table. Your choices: burn CPU on full
recomputation, or accept stale data. Most teams end up building bespoke pipelines
just to keep summary tables current.

What pg_trickle does

You define a stream table — a SQL query plus a schedule — and the extension
handles the rest:

SELECT pgstream.create_stream_table(
    'regional_totals',
    'SELECT region, SUM(amount) AS total, COUNT(*) AS cnt
     FROM orders
     GROUP BY region',
    '1m',           -- refresh bound: no more than 1 minute stale
    'DIFFERENTIAL'  -- incremental mode: only process changed rows
);

-- Query it like any regular table
SELECT * FROM regional_totals;

On each refresh cycle pg_trickle derives a delta query that processes only the
changed rows and merges the result in. One INSERT into a million-row source
table → pg_trickle touches exactly one row's worth of computation.

Key features:

• Differential view maintenance — delta queries derived automatically from
  the SQL operator tree (DBSP framework)
• Trigger-based CDC by default — no wal_level = logical, no replication
  slots required. Transitions to WAL-based capture automatically when available
• DAG-aware scheduling — you declare freshness requirements at the consumer;
  the system propagates the cadence upstream
• Broad SQL coverage — JOINs, GROUP BY, DISTINCT, CTEs (including
  WITH RECURSIVE), window functions, subqueries, LATERAL, and more
• dbt integration — a macro package for managing stream tables in dbt projects
• Written in Rust using pgrx, targets PostgreSQL 18

---

Status

This is a pre-release (v0.1.0). All 13 design phases are implemented and
1,300+ tests pass, but the project is not yet production-hardened. APIs and
configuration options may change.

The ROADMAP tracks
the path to v1.0 — highlights include correctness hardening (v0.2.0),
production-readiness (v0.3.0), Prometheus/Grafana observability (v0.4.0), and
formal packaging on PGXN and Docker Hub (v1.0.0).

---

We'd love to hear from you

A few things that would genuinely help us prioritise:

1. What are you trying to do? Dashboards? Reporting? Event-driven triggers?
   We want to understand real workloads, not imagined ones.

2. What SQL do you need that we don't support yet? Check the
   Known Limitations
   section — are any of those blockers for you?

3. How are you handling materialized view refresh today? Cron jobs, Debezium,
   rolling your own? We're curious what the landscape looks like for real teams.

4. What would make a dbt integration genuinely useful? The macro package
   exists but the full adapter is on the roadmap — your input shapes that.

---

Get started

cargo pgrx install --release --pg-config $(pg_config --bindir)/pg_config

• Getting started tutorial
• SQL reference
• Architecture overview
• ESSENCE.md — plain-language description if you want the full story before looking at code

Feel free to open a bug report or feature request,
or ask questions right here in Discussions. We read everything.