pg_trickle

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

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

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

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

How it works

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

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

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

Choose your path

What's new

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

Project & licensing

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