pg_trickle vs ReadySet — Comparison & Synergy Report

Date: 2026-03-01
Author: Internal research
Status: Reference document

1. Executive Summary

pg_trickle and ReadySet both tackle the problem of serving pre-computed query results from PostgreSQL, but they occupy fundamentally different positions in the stack and make opposite architectural trade-offs.

pg_trickle is a PostgreSQL extension that runs inside the database. It materializes complex SQL queries into durable PostgreSQL tables and keeps them fresh via scheduled differential maintenance — no external infrastructure required.

ReadySet (formerly Noria) is a standalone proxy that sits between the application and PostgreSQL. It intercepts SELECT queries at the wire-protocol level, compiles them into an in-memory dataflow graph, and serves cached results with sub-millisecond latency — transparently, with zero application code changes.

The two projects are complementary rather than competing: pg_trickle excels at complex analytical materialization with broad SQL coverage and durable results; ReadySet excels at transparent read-scaling of simple OLTP queries with near-zero latency. A layered deployment — pg_trickle materializes, ReadySet caches — is the most powerful combination.

2. Project Overview

Attribute pg_trickle ReadySet
Repository grove/pg-trickle readysettech/readyset
Heritage DBSP (Budiu et al., 2023) Noria (Gjengset et al., OSDI 2018)
Language Rust (pgrx 0.17) Rust
Latest release 0.1.2 (2026-02-28) See note in §14
License Apache 2.0 Source-available (BSL 1.1 → Apache 2.0 conversion)
PG versions 18 only 13 – 16 (MySQL also supported)
Architecture PostgreSQL extension (in-process) Standalone proxy (out-of-process)
Deployment unit shared_preload_libraries + CREATE EXTENSION Separate binary (readyset server + adapter)
wal_level = logical Optional (trigger CDC works without it) Required
Replication slot Optional (WAL mode only) Required
Result storage Durable PostgreSQL tables In-memory (evictable, lost on restart)
Freshness model Scheduled (seconds → hours, cron) Continuous (replication-lag, sub-second)
Background worker Yes (1 worker) N/A (separate process)
Connection pooling Compatible with session-mode pooling Replaces the pooler (is the pooler)

3. Architecture

pg_trickle — Extension Inside PostgreSQL

┌─────────────────────────────────────────────────┐
│                  PostgreSQL 18                   │
│                                                  │
│  ┌────────────┐    ┌────────────────────────┐   │
│  │ Base       │───▶│ pg_trickle extension    │   │
│  │ Tables     │ CDC│                          │   │
│  │            │    │  ┌─────────────────┐    │   │
│  └────────────┘    │  │ DVM Engine      │    │   │
│                    │  │ (delta SQL gen)  │    │   │
│                    │  └────────┬────────┘    │   │
│                    │           │ MERGE       │   │
│                    │  ┌────────▼────────┐    │   │
│  ┌─────────────┐  │  │ Stream Tables   │    │   │
│  │ Application │◀─┼──│ (PG tables)     │    │   │
│  └─────────────┘  │  └─────────────────┘    │   │
│                    └────────────────────────┘   │
└─────────────────────────────────────────────────┘
  • Everything runs inside a single PostgreSQL process.
  • Stream tables are standard PostgreSQL tables — indexable, joinable, backed up.
  • No external processes or network hops.

ReadySet — External Proxy

┌────────────┐     ┌──────────────────────┐     ┌──────────────┐
│            │ SQL │                      │ WAL │              │
│ Application├────▶│   ReadySet Proxy     │◀────┤  PostgreSQL  │
│            │◀────┤                      │ SQL │              │
│            │cache│  ┌────────────────┐  │────▶│              │
└────────────┘ hit │  │ In-memory      │  │fall-│              │
                   │  │ Dataflow Graph │  │back │              │
                   │  │ (Noria engine) │  │     │              │
                   │  └────────────────┘  │     │              │
                   └──────────────────────┘     └──────────────┘
  • ReadySet is a separate process with its own memory space.
  • Applications connect to ReadySet instead of PostgreSQL directly.
  • Cache hits are served from in-memory dataflow state; cache misses fall through to upstream PostgreSQL.
  • WAL logical replication feeds changes into the dataflow graph continuously.

Combined — Layered Architecture

                    ┌──────────────────────┐
┌────────────┐     │   ReadySet Proxy     │
│            │ SQL │                      │
│ Application├────▶│  Caches reads from   │
│            │◀────│  stream tables +     │
└────────────┘     │  base tables         │
                   └──────────┬───────────┘
                              │ WAL + SQL
                   ┌──────────▼───────────────────────┐
                   │          PostgreSQL 18            │
                   │                                    │
                   │  ┌──────────┐   ┌──────────────┐ │
                   │  │ Base     │──▶│ pg_trickle   │ │
                   │  │ Tables   │CDC│              │ │
                   │  └──────────┘   │ ┌──────────┐ │ │
                   │                 │ │ Stream   │ │ │
                   │ ReadySet caches │ │ Tables   │ │ │
                   │ these tables ◀──┤ │ (durable)│ │ │
                   │                 │ └──────────┘ │ │
                   │                 └──────────────┘ │
                   └──────────────────────────────────┘
  • pg_trickle materializes complex queries into simple, flat stream tables.
  • ReadySet caches reads against those stream tables (and base tables) for sub-millisecond application-tier latency.
  • Each system handles what it does best: pg_trickle for complex SQL transformation, ReadySet for read-scaling and transparent caching.

4. Execution Model

This is the most fundamental design difference.

pg_trickle — Periodic Batch Refresh

pg_trickle has no persistent dataflow graph. On each scheduled refresh cycle:

  1. The DVM engine generates a delta SQL query (CTE chain) from the operator tree.
  2. PostgreSQL’s own planner and executor evaluate that query.
  3. Results are merged into the stream table via MERGE.
  4. No operator state persists between refresh cycles — auxiliary state lives in the stream table itself (__pgt_count columns) and change buffer tables.
  Time ─────────────────────────────────────────────▶

  Writes │ │││  │  ││││ │  │ │││  │  ││││ │  │
         ▼ ▼▼▼  ▼  ▼▼▼▼ ▼  ▼ ▼▼▼  ▼  ▼▼▼▼ ▼  ▼
  CDC    ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
         │         buffer          │         buffer
         ▼                         ▼
  Refresh █████                    █████
         t=0    stale              t=1    stale

Staleness is bounded by the refresh schedule: '30s', '5m', '@hourly', or cron expressions. Between refreshes, stream tables serve stale (but consistent) data.

ReadySet — Continuous Streaming Dataflow

ReadySet maintains a persistent in-memory dataflow graph with long-lived stateful operators. Changes flow through the graph continuously:

  1. WAL logical replication delivers row changes to ReadySet.
  2. Changes propagate through the dataflow operators (join, aggregate, project).
  3. Materialized results are updated in-place in memory.
  4. Application SELECTs hit the materialized cache directly.
  Time ─────────────────────────────────────────────▶

  Writes │ │││  │  ││││ │  │ │││  │  ││││ │  │
         ▼ ▼▼▼  ▼  ▼▼▼▼ ▼  ▼ ▼▼▼  ▼  ▼▼▼▼ ▼  ▼
  WAL    ▓ ▓▓▓  ▓  ▓▓▓▓ ▓  ▓ ▓▓▓  ▓  ▓▓▓▓ ▓  ▓
         ▼ ▼▼▼  ▼  ▼▼▼▼ ▼  ▼ ▼▼▼  ▼  ▼▼▼▼ ▼  ▼
  Cache  ████████████████████████████████████████████
         always fresh (within replication lag)

Staleness equals replication lag — typically sub-second. There is no scheduled refresh cycle; the cache is always converging toward the current database state.

Implications

Property pg_trickle ReadySet
Latency to see a write in results Refresh interval (seconds–hours) Replication lag (ms–seconds)
CPU cost model Proportional to changes × query complexity, batched Proportional to changes × dataflow depth, streaming
Memory model PostgreSQL shared buffers (disk-backed) Dedicated process memory (in-memory only)
Result after crash/restart Intact (durable PG tables) Lost (must re-snapshot from upstream)
State between cycles Only auxiliary columns in PG tables Full operator state in memory

5. SQL Feature Coverage

Comparison Table

Feature pg_trickle ReadySet
Simple SELECT / projection ✅ ✅
WHERE filters ✅ ✅
HAVING ✅ ✅
INNER JOIN ✅ ✅
LEFT JOIN ✅ ✅
RIGHT JOIN ✅ ⚠️ Limited
FULL OUTER JOIN ✅ ❌
NATURAL JOIN ✅ ❌
CROSS JOIN ✅ ❌
Multi-way JOIN (3+ tables) ✅ ✅
GROUP BY + COUNT, SUM, AVG ✅ ✅
GROUP BY + MIN, MAX ✅ ✅
GROUP BY + STRING_AGG, ARRAY_AGG ✅ ❌
GROUP BY + BOOL_AND/OR ✅ ❌
GROUP BY + JSON_AGG, JSONB_AGG ✅ ❌
GROUP BY + STDDEV, VARIANCE, regression ✅ ❌
GROUPING SETS / CUBE / ROLLUP ✅ ❌
DISTINCT ✅ ✅
DISTINCT ON ✅ ❌
UNION ALL ✅ ✅
UNION (deduplicated) ✅ ❌
INTERSECT / EXCEPT ✅ ❌
Subqueries in FROM ✅ ⚠️ Limited
EXISTS / NOT EXISTS ✅ ❌
IN (subquery) ✅ ⚠️ Limited (correlated: ❌)
Scalar subqueries ✅ ❌
Non-recursive CTEs ✅ ⚠️ Limited
WITH RECURSIVE ✅ ❌
WINDOW functions ✅ ❌
LATERAL / SRFs ✅ ❌
JSON_TABLE (PG 17+) ✅ ❌
ORDER BY ⚠️ Silently ignored ✅
LIMIT / OFFSET ❌ (DIFFERENTIAL) ✅
Parameterized queries N/A (defines whole query) ✅ (key lookup pattern)
Views as sources ✅ (auto-inlined) ✅ (falls through to PG)
Partitioned tables ✅ ⚠️ Depends on version
Volatile functions ❌ DIFFERENTIAL / ⚠️ FULL ❌

Key SQL Gaps

ReadySet cannot handle: - Window functions (ROW_NUMBER, RANK, LAG, LEAD, etc.) - Recursive CTEs (WITH RECURSIVE) - LATERAL joins and set-returning functions - FULL/CROSS/NATURAL joins - Complex aggregates (STRING_AGG, ARRAY_AGG, JSON_AGG, statistical) - Set operations beyond UNION ALL (INTERSECT, EXCEPT, UNION with dedup) - GROUPING SETS / CUBE / ROLLUP - Correlated subqueries and EXISTS/NOT EXISTS

pg_trickle cannot handle: - LIMIT/OFFSET in DIFFERENTIAL mode - Parameterized query caching (it materializes entire result sets, not lookup patterns) - ORDER BY preservation (silently dropped)

This gap profile makes the two systems highly complementary: pg_trickle can pre-materialize the complex queries that ReadySet cannot cache, producing simple flat tables that ReadySet can cache.

6. Change Data Capture

Attribute pg_trickle ReadySet
Primary CDC mechanism Row-level AFTER triggers WAL logical replication
WAL-based CDC ✅ Optional (auto-transitions from triggers) ✅ Required
wal_level = logical required No (trigger mode works without it) Yes
Replication slot required No (trigger mode) / Yes (WAL mode) Yes
Replication slot type Logical (WAL mode only) Logical
Output plugin pgoutput pgoutput
Write-path overhead (trigger mode) ~20–55 μs/row (trigger insert to buffer) N/A
Write-path overhead (WAL mode) ~0 μs (eliminated trigger overhead) ~0 μs (WAL is written regardless)
Hybrid transition ✅ Trigger → WAL seamless transition N/A (WAL-only)
Fallback on error ✅ WAL → trigger fallback ❌ Must fix replication
TRUNCATE handling Buffer cleared; full refresh queued Table cache invalidated; re-snapshot
DDL handling Event triggers detect ALTER/DROP Partial (requires cache re-creation)
Change buffer tables pgtrickle_changes.changes_<oid> In-memory operator state

Key Difference

pg_trickle’s hybrid CDC is a significant operational advantage: it works out-of-the-box with zero PostgreSQL configuration changes (trigger mode), and can optionally transition to WAL for lower overhead. ReadySet requires wal_level = logical and a replication slot — which means a PostgreSQL restart on many managed hosting platforms and consumes WAL retention resources.

For environments where wal_level = logical cannot be enabled (e.g., some managed databases, compliance environments), pg_trickle is the only option.

7. State & Durability

Attribute pg_trickle ReadySet
Result storage medium PostgreSQL heap tables (disk-backed) In-process memory (RAM only)
Survives process restart ✅ Yes ❌ No (cache lost, must warm)
Survives PostgreSQL restart ✅ Yes ✅ (ReadySet is separate process)
Survives ReadySet restart N/A ❌ Cold start, re-snapshot required
Backup / pg_dump ✅ Standard pg_dump includes stream tables ❌ Not applicable
Indexable ✅ Standard PostgreSQL indexes ❌ Internal data structures only
Joinable with other tables ✅ Full SQL on stream tables ❌ Cache results only via proxy
Point-in-time recovery ✅ Via PostgreSQL PITR ❌
Eviction None (fully materialized) ✅ LRU eviction of cold entries
Memory footprint PostgreSQL shared_buffers (shared) Dedicated per-cache memory
Cold start time None (tables already populated) Seconds–minutes (re-snapshot)

Implications

pg_trickle’s durable storage means stream tables are first-class PostgreSQL citizens: they can be indexed for fast lookups, joined in ad-hoc queries, included in pg_dump backups, and recovered via PITR. They persist across all types of restarts.

ReadySet’s in-memory storage gives it sub-millisecond read latency but at the cost of durability — a ReadySet restart requires re-snapshotting base tables and replaying WAL to rebuild the cache. For large datasets, this cold-start penalty can be significant (minutes).

8. Scheduling & Freshness

Attribute pg_trickle ReadySet
Freshness model Scheduled (poll-based) Continuous (push-based)
Typical staleness Seconds to hours (configurable) Milliseconds (replication lag)
Duration schedules ✅ ('30s', '5m', '1h') N/A
Cron schedules ✅ (5/6-field cron + @daily aliases) N/A
Manual refresh ✅ refresh_stream_table() N/A (always refreshing)
Dependency DAG ✅ Topological ordering across stream tables ❌ No chained/cascading views
CALCULATED schedule propagation ✅ Consumers drive upstream schedules ❌
Freshness guarantee on read Data is from last refresh cycle Data is from last replicated WAL position
Pause/resume ✅ pg_trickle.enabled = false Stop/start proxy

The Freshness Spectrum

  ◀── pg_trickle ──────────────────────────────────▶
  hours    minutes    seconds    100ms    10ms    1ms
  ├──────────┼──────────┼──────────┼───────┼──────┤
  cron     duration   aggressive          repl. lag
  schedules  schedules  schedule          ◀─ ReadySet ─▶

pg_trickle covers the left side of the spectrum (analytical summaries that can tolerate seconds-to-hours staleness). ReadySet covers the right side (application-facing reads that need near-real-time freshness). Together they span the full range.

9. Deployment & Operations

Attribute pg_trickle ReadySet
External process required No Yes (ReadySet server + adapter)
PostgreSQL configuration shared_preload_libraries addition wal_level = logical, replication slot, publication
PostgreSQL restart required Yes (for shared_preload_libraries) Yes (for wal_level change, if not already set)
Application code changes None (query stream tables directly) None (transparent proxy)
Connection string change No Yes (point to ReadySet instead of PG)
Kubernetes deployment CNPG Image Volume (single pod) Separate Deployment/StatefulSet
Docker local dev Extension in PG container Separate container + PG container
Resource isolation Shares PostgreSQL resources Separate memory, CPU, network
Scaling model Vertical (PG instance) Horizontal (multiple ReadySet instances)
Connection pooling Compatible with session-mode pools (PgBouncer session mode) Replaces the pooler — ReadySet is itself a connection pool
High availability Follows PostgreSQL HA (patroni, CNPG) Requires separate HA setup
Monitoring Built-in SQL views, NOTIFY Prometheus metrics endpoint

Operational Complexity

pg_trickle is operationally simpler for teams already running PostgreSQL — it is just an extension with no additional infrastructure to deploy, monitor, or maintain. The trade-off is that it shares PostgreSQL’s resources (CPU, memory, I/O) during refresh cycles.

ReadySet requires a separate deployment but provides resource isolation: heavy read traffic is served from ReadySet’s memory without touching PostgreSQL at all. This makes it attractive for teams that need to offload reads from a saturated primary.

10. Concurrency & Isolation

pg_trickle

  • Refresh operations acquire a per-stream-table advisory lock — only one refresh per stream table at a time.
  • Base table writes are never blocked by refresh operations.
  • The background worker processes stream tables sequentially (within the same DAG layer) or in parallel across independent branches.
  • Stream table reads during refresh see the previous (pre-refresh) state — standard MVCC isolation.

ReadySet

  • Reads are served from in-memory state — no PostgreSQL lock interaction.
  • Writes pass through to upstream PostgreSQL (if ReadySet is in read/write proxy mode) or are routed directly.
  • Cache consistency is eventual — reads may briefly lag behind the most recent write due to WAL propagation delay.
  • No PostgreSQL lock contention from ReadySet’s cache maintenance.

Key Difference

Neither system introduces write-path lock contention on base tables. pg_trickle’s advisory locks are only between concurrent refreshes of the same stream table. ReadySet’s reads are entirely lock-free (from PostgreSQL’s perspective) because they’re served from external memory.

11. Observability

Feature pg_trickle ReadySet
Catalog of managed objects ✅ pgtrickle.pgt_stream_tables ✅ SHOW CACHES / SHOW PROXIED QUERIES
Per-refresh timing/history ✅ pgtrickle.pgt_refresh_history ❌ (no refresh concept)
Staleness reporting ✅ stale column in monitoring views N/A (always streaming)
Scheduler status ✅ pgtrickle.pgt_status() N/A
NOTIFY-based alerting ✅ pgtrickle_refresh channel ❌
Prometheus metrics ⚠️ Planned (v0.4.0) ✅ Built-in metrics endpoint
Grafana dashboard ⚠️ Planned (v0.4.0) ✅ Available
Error tracking ✅ Consecutive error counter, last error ✅ Query-level error reporting
Cache hit/miss rates N/A ✅ Per-query hit rate tracking
Replication lag monitoring ✅ (WAL mode: LSN frontier) ✅ WAL position tracking
dbt integration ✅ dbt-pgtrickle package ❌

12. Known Limitations

pg_trickle Limitations

  • Data is stale between refresh cycles — not suitable for sub-second freshness requirements.
  • LIMIT / OFFSET not supported in DIFFERENTIAL mode.
  • Volatile SQL functions rejected in DIFFERENTIAL mode.
  • Materialized views as sources not supported in DIFFERENTIAL mode.
  • Extension upgrade migrations not yet implemented (planned for v0.2.0+).
  • Targets PostgreSQL 18 only — no backport to PG 13–17.
  • Early release — not yet production-hardened.
  • Refresh cycles consume PostgreSQL CPU/I/O (shared resources).
  • Not compatible with transaction-mode PgBouncer.
  • No parameterized query caching — materializes entire result sets.

ReadySet Limitations

  • Requires wal_level = logical — not available on all managed PG platforms.
  • Requires a replication slot — consumes WAL retention resources.
  • SQL coverage significantly narrower than pg_trickle (no window functions, recursive CTEs, LATERAL, complex aggregates, set operations).
  • In-memory only — cache lost on restart, cold-start penalty.
  • No query result durability — cannot pg_dump, index, or join cached results.
  • No multi-layer view dependencies (no DAG / cascading).
  • No dbt integration.
  • Cannot handle DDL changes gracefully — requires manual cache recreation.
  • Project status uncertain — see §15.
  • MySQL wire-protocol mode may receive more investment than PostgreSQL mode.
  • Cache eviction under memory pressure can cause performance cliffs.
  • Full outer joins, complex subqueries, window functions not supported.

13. Performance Characteristics

Write Path

Metric pg_trickle (trigger mode) pg_trickle (WAL mode) ReadySet
Per-row overhead ~20–55 μs (trigger insert) ~0 μs (WAL is already written) ~0 μs (WAL is already written)
Locking impact on writes None (trigger is async-safe) None None
WAL volume impact Change buffer inserts generate WAL Reads existing WAL Reads existing WAL

Read Path

Metric pg_trickle ReadySet
Read latency Standard PostgreSQL table read (disk/buffer) Sub-millisecond (in-memory)
Index support Full PostgreSQL index support Internal lookup structures
Concurrent readers PostgreSQL MVCC (unlimited) Lock-free in-memory reads
Cold cache No cold cache (durable tables) Full table scan from PG to warm

Refresh / Maintenance Cost

Metric pg_trickle (DIFFERENTIAL) pg_trickle (FULL) ReadySet
Cost model Proportional to Δ(changes) × query complexity Full recomputation Proportional to Δ(changes) × dataflow depth
Single-row change on 1M table Touches ~1 row’s computation Recomputes all 1M rows Updates affected cache entries
CPU location PostgreSQL backend (shared) PostgreSQL backend ReadySet process (isolated)
Blocking during maintenance No (MVCC) No (MVCC) No (in-memory)

Resource Isolation

pg_trickle’s refresh cycles compete with application queries for PostgreSQL’s CPU and I/O budget. ReadySet’s maintenance runs in a separate process with its own resource allocation. For write-heavy workloads where PostgreSQL CPU is the bottleneck, ReadySet’s resource isolation is advantageous.

14. Use-Case Fit

Scenario Recommended
Complex analytical materialization (multi-join, aggregates, CTEs, windows) pg_trickle
Transparent read-scaling of simple OLTP queries ReadySet
Zero additional infrastructure pg_trickle
Sub-second freshness for simple queries ReadySet
Multi-layer view pipelines with dependency ordering pg_trickle
Results must survive restarts / be backed up pg_trickle
Offload read traffic from saturated primary ReadySet
Parameterized key-value lookup caching ReadySet
dbt transformation pipelines pg_trickle
wal_level = logical not available pg_trickle
Kubernetes / CNPG deployment with minimal pods pg_trickle
Horizontal read scaling across multiple cache nodes ReadySet
Complex SQL + low-latency reads Both (see §16)
PostgreSQL 13–17 support required ReadySet
PostgreSQL 18 pg_trickle (or both)
Long-term project stability requirement pg_trickle (see §15)
Application cannot change connection string pg_trickle

15. ReadySet Project Status

ReadySet Inc. launched a managed cloud product (ReadySet Cloud) but shut it down. The company’s trajectory has been turbulent — layoffs, strategic pivots, and periods of reduced open-source activity. As of early 2026:

  • The open-source repository remains available but commit frequency has declined.
  • No new major releases in the PostgreSQL adapter for several months.
  • The MySQL adapter appears to receive more attention than PostgreSQL.
  • Community engagement (issues, PRs, Discord) has slowed.
  • The BSL 1.1 license (converting to Apache 2.0 after 4 years) may limit commercial use before conversion.

Risk assessment for integration planning:

Risk Impact Mitigation
ReadySet project becomes unmaintained High — no bug fixes, PG version support pg_trickle stands alone; ReadySet is additive
License terms change Medium — BSL 1.1 restricts some commercial use Use pg_trickle as primary; ReadySet as optional
PostgreSQL adapter lags behind MySQL Medium — PG-specific bugs may persist Test thoroughly before production deployment
API/wire-protocol breaking changes Low — wire protocol is stable Pin ReadySet version in deployments

This uncertainty is the primary reason pg_trickle should be treated as the foundational layer, with ReadySet as an optional, additive optimization — not a dependency.

16. Synergies & Integration Opportunities

Opportunity 1: Layered Architecture — Materialize + Cache

The highest-value synergy is using pg_trickle for complex SQL transformation and ReadySet for read-scaling:

  Complex query ──▶ pg_trickle ──▶ Stream Table ──▶ ReadySet ──▶ App
  (window funcs,     (scheduled      (durable PG     (sub-ms      (zero
   recursive CTEs,    DIFFERENTIAL    table, flat     in-memory    code
   37 aggregates)     refresh)        and simple)     cache)       change)

Example workflow:

-- 1. pg_trickle materializes a complex analytical query
SELECT pgtrickle.create_stream_table(
    'customer_lifetime_value',
    $$
    WITH monthly AS (
        SELECT customer_id,
               date_trunc('month', order_date) AS month,
               SUM(amount) AS monthly_total,
               COUNT(*) AS order_count
        FROM orders
        GROUP BY customer_id, date_trunc('month', order_date)
    )
    SELECT customer_id,
           SUM(monthly_total) AS lifetime_value,
           AVG(monthly_total) AS avg_monthly,
           COUNT(DISTINCT month) AS active_months,
           MAX(month) AS last_active_month,
           STDDEV(monthly_total) AS spend_volatility
    FROM monthly
    GROUP BY customer_id
    $$,
    '5m',
    'DIFFERENTIAL'
);

-- 2. ReadySet caches the simple lookup against the stream table
--    (application connects through ReadySet proxy)
--    This query is trivially cacheable by ReadySet:
SELECT * FROM customer_lifetime_value WHERE customer_id = $1;

pg_trickle handles the complex SQL that ReadySet cannot (CTE, STDDEV, COUNT(DISTINCT), multi-level aggregation). ReadySet handles the parameterized key-value lookup pattern that pg_trickle doesn’t optimize for (it materializes the whole table, but ReadySet caches the specific lookup).

Opportunity 2: SQL Coverage Gap-Filling

ReadySet’s SQL parser rejects queries it cannot incrementally maintain. For these queries, pg_trickle can pre-materialize the result:

Unsupported by ReadySet pg_trickle Materializes ReadySet Caches
Window functions (ROW_NUMBER() OVER(...)) Stream table with pre-computed rank Simple SELECT WHERE rank <= N
WITH RECURSIVE (graph traversal) Stream table with flattened paths Key lookup on flattened result
INTERSECT / EXCEPT Stream table with set operation result Full scan or filtered read
FULL OUTER JOIN Stream table with full join result Key lookup against result
GROUPING SETS / CUBE Stream table with rolled-up aggregates Parameterized group filter
Complex aggregates (STDDEV, ARRAY_AGG) Pre-aggregated stream table Simple reads

Opportunity 3: Mixed Freshness Tiers

Different data consumers have different freshness requirements. Deploy both systems to serve the full spectrum:

Data Freshness Need System Refresh
User profile lookups Near-real-time ReadySet (direct cache) Continuous WAL
Order totals by region Minutes pg_trickle '2m' schedule
Daily revenue dashboard Hourly pg_trickle '@hourly' cron
Product recommendations Seconds ReadySet (from pg_trickle ST) Continuous WAL + 30s pg_trickle
Inventory counts Sub-second ReadySet (direct cache) Continuous WAL

Opportunity 4: CDC Knowledge Sharing

Both projects implement PostgreSQL WAL consumption. Areas of shared learning:

  • Replication slot lifecycle: Creation, monitoring, cleanup, and handling of slot invalidation due to WAL retention limits.
  • Failover behavior: How logical replication slots behave during primary failover (Patroni, CNPG) — both projects must handle slot migration or re-creation.
  • WAL retention pressure: Replication slots prevent WAL cleanup. Monitoring and alerting for growing pg_wal sizes is critical for both.
  • pgoutput plugin quirks: Schema change handling, TOAST column behavior, and replica identity settings affect both systems.

Opportunity 5: dbt Ecosystem Bridge

pg_trickle has dbt-pgtrickle for managing stream tables in dbt projects. A hypothetical extension could orchestrate both systems:

# dbt model: models/customer_clv.sql
# pg_trickle materializes the transformation
{{ config(
    materialized='stream_table',
    schedule='5m',
    refresh_mode='DIFFERENTIAL'
) }}
SELECT customer_id, SUM(amount) AS lifetime_value
FROM {{ ref('orders') }}
GROUP BY customer_id

# A dbt post-hook or meta tag could emit ReadySet cache hints:
# {{ config(meta={'readyset_cache': true}) }}

This is speculative but illustrates how dbt could orchestrate both the materialization layer (pg_trickle) and the caching layer (ReadySet) from a single project definition.

Opportunity 6: Partial Materialization Inspiration

ReadySet (via Noria) pioneered partially-stateful dataflow — only materializing the working set and evicting cold entries. pg_trickle currently always fully materializes stream tables.

For very large stream tables (millions of rows) where only a small fraction is actively queried, a future pg_trickle feature could borrow this concept:

  • Partial refresh: Only maintain incremental state for rows matching a predicate (e.g., WHERE order_date > now() - interval '90 days').
  • Tiered storage: Keep hot rows in the stream table, archive cold rows to a partitioned history table, refresh only the hot partition differentially.

This would not replace ReadySet’s real-time eviction but could reduce pg_trickle’s storage and refresh cost for large, time-partitioned datasets.

17. Coexistence

pg_trickle and ReadySet can coexist in the same deployment without conflicts:

Aspect Interaction
PostgreSQL schemas No overlap (pgtrickle / pgtrickle_changes vs. none)
Replication slots Each uses its own slot (if pg_trickle is in WAL mode)
Triggers pg_trickle’s CDC triggers are invisible to ReadySet
Connection routing App → ReadySet → PostgreSQL; pg_trickle runs inside PG
WAL consumption Both read the same WAL; no interference
Monitoring Separate systems (SQL views vs. Prometheus endpoint)
Resource contention ReadySet offloads reads; pg_trickle refresh may compete with PG writes

Deployment Topology

┌─────────────────────────────────────────────────────────────┐
│  Kubernetes Cluster                                          │
│                                                              │
│  ┌──────────────────┐    ┌────────────────────────────────┐ │
│  │  App Pods         │    │  ReadySet Deployment           │ │
│  │  (connect to RS)  │───▶│  - readyset-server             │ │
│  └──────────────────┘    │  - readyset-adapter             │ │
│                          └──────────────┬─────────────────┘ │
│                                         │ WAL + SQL          │
│                          ┌──────────────▼─────────────────┐ │
│                          │  CNPG Cluster                   │ │
│                          │  PostgreSQL 18                   │ │
│                          │  + pg_trickle extension          │ │
│                          │  ┌──────────────────────────┐   │ │
│                          │  │ Stream Tables (durable)   │   │ │
│                          │  │ Base Tables               │   │ │
│                          │  └──────────────────────────┘   │ │
│                          └─────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘

Monitoring integration: - pg_trickle: query pgtrickle.pgt_status() views via SQL (or future Prometheus exporter, planned for v0.4.0). - ReadySet: scrape the built-in /metrics endpoint. - Both metrics can feed into the same Grafana instance for unified visibility.

18. Summary Table

Dimension pg_trickle ReadySet
Architecture PostgreSQL extension (in-process) Standalone proxy (out-of-process)
Deployment CREATE EXTENSION — zero extra infra Separate server + adapter binary
Theoretical basis DBSP (Budiu 2023) Noria (Gjengset 2018)
Execution model Periodic batch (SQL CTE execution) Continuous streaming dataflow
Result storage Durable PostgreSQL tables In-memory (evictable)
Freshness Scheduled (seconds–hours) Continuous (replication-lag)
SQL coverage Broad (21 operators, 37+ aggregates) Narrow (simple joins + aggregates)
Window functions ✅ ❌
Recursive CTEs ✅ ❌
LATERAL / SRFs ✅ ❌
Set operations ✅ UNION/INTERSECT/EXCEPT ⚠️ UNION ALL only
Parameterized queries ❌ (full materialization) ✅ (key lookup caching)
Multi-layer DAG ✅ ❌
CDC mechanism Hybrid trigger → WAL WAL-only
wal_level = logical required No (optional) Yes
Write-path overhead ~20–55 μs (trigger) / ~0 (WAL) ~0 (WAL)
Read latency PostgreSQL table scan Sub-millisecond (memory)
Crash durability ✅ Tables survive all restarts ❌ Cache lost on restart
Connection pooling Session-mode compatible Replaces the pooler
dbt integration ✅ dbt-pgtrickle ❌
Kubernetes (CNPG) ✅ Image Volume Separate deployment
Monitoring SQL views + NOTIFY Prometheus endpoint
PG version support 18 only 13–16
License Apache 2.0 BSL 1.1 (→ Apache 2.0)
Project status Active, early-stage Uncertain (see §15)
Best for Complex analytical materialization Simple OLTP read-scaling
Together Materializes → flat table Caches → sub-ms reads

19. Recommendations

  1. Treat pg_trickle as the foundational layer. It requires no external infrastructure and produces durable, queryable results. ReadySet is additive.

  2. Evaluate ReadySet for read-scaling only if the deployment already has wal_level = logical and the team is comfortable operating a separate proxy.

  3. The layered pattern is the highest-value synergy: use pg_trickle for complex SQL materialization, ReadySet for caching simple lookups against those materialized tables.

  4. Monitor ReadySet’s project health before committing to operational dependency. Given the uncertain project trajectory (§15), treat ReadySet as optional and replaceable (e.g., with application-level caching or Redis if ReadySet becomes unavailable).

  5. No implementation work needed in pg_trickle to enable ReadySet integration — stream tables are standard PostgreSQL tables. ReadySet can cache them immediately. The synergy is architectural, not code-level.

  6. Consider a tutorial/guide (separate document) showing the layered deployment pattern if user demand warrants it.

References

  • ReadySet repository: https://github.com/readysettech/readyset
  • ReadySet documentation: https://docs.readyset.io/
  • Noria paper: Gjengset, J., Schwarzkopf, M., Behrens, J., Araújo, L.T., Lam, E., Kohler, E., Kaashoek, M.F., & Morris, R. (2018). “Noria: Dynamic, Partially-Stateful Data-Flow for High-Performance Web Applications.” Proceedings of OSDI 2018, 213–231.
  • DBSP paper: Budiu, M., Ryzhyk, L., McSherry, F., & Tannen, V. (2023). “DBSP: Automatic Incremental View Maintenance for Rich Query Languages.” PVLDB, 16(7), 1601–1614. https://arxiv.org/abs/2203.16684
  • pg_trickle architecture: ../../docs/ARCHITECTURE.md
  • pg_trickle DVM operators: ../../docs/DVM_OPERATORS.md
  • pg_trickle ESSENCE: ../../ESSENCE.md
  • pg_trickle vs pg_ivm comparison: REPORT_PG_IVM_COMPARISON.md