Contents

pg_trickle Ecosystem — Supportive Projects Plan

Date: 2026-02-24 Status: PROPOSED

Overview

This document describes the ecosystem of supportive projects around the pg_trickle PostgreSQL extension. Each project is designed to lower adoption friction, improve operability, or integrate pg_trickle with popular tools in the modern data stack.

All projects are maintained in separate repositories unless noted otherwise. The pg_trickle extension repo (pg-trickle) remains focused on the core Rust/pgrx extension.

Principles

  1. SQL is the API. Every integration wraps pg_trickle’s SQL functions — no custom wire protocols, no binary coupling to extension internals.
  2. Separate repos, separate release cadences. Ecosystem projects only change when the SQL API changes, not on every Rust refactor.
  3. Zero required dependencies. pg_trickle works standalone. Every ecosystem project is optional and additive.
  4. Start small, ship fast. Each project has a minimal viable deliverable that can ship in days, with expansion phases that follow.

Table of Contents

Roadmap Summary

Phase Projects Combined Effort Dependencies
Phase 1 — Observability Prometheus config, Grafana dashboard ~8 hours None
Phase 2 — Distribution Docker Hub image, CNPG hardening ~16 hours None
Phase 3 — Data Stack dbt macro package ~15 hours None
Phase 4 — Orchestration Airflow provider, CLI tool ~36 hours None
Phase 5 — Advanced dbt adapter, PGXN/apt, Flyway/Liquibase, ORMs ~100 hours Phase 3

Projects within the same phase can be developed in parallel. Cross-phase dependencies are noted in each project section.

Project 1 — dbt Macro Package (dbt-pgtrickle)

Full plan: ../dbt/PLAN_DBT_MACRO.md

Summary

A standalone dbt package containing a custom stream_table materialization that wraps pg_trickle’s SQL API. Works with the standard dbt-postgres adapter.

Repository

  • Repo: github.com/<org>/dbt-pgtrickle (separate)
  • Language: Jinja SQL
  • Distribution: Git install via packages.yml, later dbt Hub

Key Deliverables

Deliverable Description
stream_table materialization Maps dbt run → create_stream_table() / alter_stream_table()
Full-refresh support dbt run --full-refresh → drop_stream_table() + create_stream_table()
Source freshness bridge Maps dbt source freshness → pg_stat_stream_tables
Manual refresh operation dbt run-operation refresh --args '{"model_name": "..."}'
Integration test suite Seed data → create STs → verify → full refresh → verify

Effort: ~15 hours

Project 2 — Prometheus Exporter Config

Summary

A postgres_exporter custom queries configuration file that exposes pg_trickle metrics as Prometheus metrics. Requires zero custom code — just a YAML config file consumed by the standard postgres_exporter.

Repository

  • Repo: github.com/<org>/pgtrickle-monitoring (separate, shared with Grafana dashboard)
  • Language: YAML + SQL
  • Distribution: Git clone or copy the file

Metrics Exposed

From pgtrickle.pg_stat_stream_tables

Prometheus Metric Source Column Type Labels
pgtrickle_refreshes_total total_refreshes counter pgt_name, schema
pgtrickle_refreshes_successful_total successful_refreshes counter pgt_name
pgtrickle_refreshes_failed_total failed_refreshes counter pgt_name
pgtrickle_rows_inserted_total total_rows_inserted counter pgt_name
pgtrickle_rows_deleted_total total_rows_deleted counter pgt_name
pgtrickle_avg_refresh_duration_ms avg_duration_ms gauge pgt_name
pgtrickle_staleness_seconds staleness gauge pgt_name
pgtrickle_stale stale gauge (0/1) pgt_name
pgtrickle_consecutive_errors consecutive_errors gauge pgt_name
pgtrickle_is_populated is_populated gauge (0/1) pgt_name

From pgtrickle.check_cdc_health()

Prometheus Metric Source Column Type Labels
pgtrickle_cdc_mode cdc_mode gauge (enum) source_table
pgtrickle_cdc_lag_bytes lag_bytes gauge source_table, slot_name
pgtrickle_cdc_alert alert gauge (0/1) source_table, alert_type

Deliverable Structure

pgtrickle-monitoring/
├── README.md
├── prometheus/
│   ├── pgtrickle_queries.yml          # postgres_exporter custom queries
│   └── alerts.yml                    # Prometheus alerting rules
├── grafana/
│   └── pgtrickle-dashboard.json       # Grafana dashboard (see Project 3)
└── docker-compose.yml                # Full observability stack demo

Example: pgtrickle_queries.yml

pgtrickle_stream_table_stats:
  query: |
    SELECT
      pgt_schema AS schema,
      pgt_name,
      status,
      refresh_mode,
      COALESCE(EXTRACT(EPOCH FROM staleness), 0) AS staleness_seconds,
      CASE WHEN stale THEN 1 ELSE 0 END AS is_stale,
      consecutive_errors,
      CASE WHEN is_populated THEN 1 ELSE 0 END AS is_populated,
      total_refreshes,
      successful_refreshes,
      failed_refreshes,
      total_rows_inserted,
      total_rows_deleted,
      COALESCE(avg_duration_ms, 0) AS avg_duration_ms
    FROM pgtrickle.pg_stat_stream_tables
  metrics:
    - schema:
        usage: "LABEL"
    - pgt_name:
        usage: "LABEL"
    - status:
        usage: "LABEL"
    - refresh_mode:
        usage: "LABEL"
    - staleness_seconds:
        usage: "GAUGE"
        description: "Seconds since last refresh"
    - is_stale:
        usage: "GAUGE"
        description: "1 if stream table data is stale"
    - consecutive_errors:
        usage: "GAUGE"
        description: "Current consecutive error count"
    - is_populated:
        usage: "GAUGE"
        description: "1 if stream table has been populated"
    - total_refreshes:
        usage: "COUNTER"
        description: "Total refresh operations"
    - successful_refreshes:
        usage: "COUNTER"
        description: "Total successful refreshes"
    - failed_refreshes:
        usage: "COUNTER"
        description: "Total failed refreshes"
    - total_rows_inserted:
        usage: "COUNTER"
        description: "Total rows inserted across all refreshes"
    - total_rows_deleted:
        usage: "COUNTER"
        description: "Total rows deleted across all refreshes"
    - avg_duration_ms:
        usage: "GAUGE"
        description: "Average refresh duration in milliseconds"

pgtrickle_cdc_health:
  query: |
    SELECT
      source_table,
      cdc_mode,
      COALESCE(slot_name, '') AS slot_name,
      COALESCE(lag_bytes, 0) AS lag_bytes,
      COALESCE(confirmed_lsn::text, '') AS confirmed_lsn,
      CASE WHEN alert IS NOT NULL THEN 1 ELSE 0 END AS has_alert,
      COALESCE(alert, '') AS alert_type
    FROM pgtrickle.check_cdc_health()
  metrics:
    - source_table:
        usage: "LABEL"
    - cdc_mode:
        usage: "LABEL"
    - slot_name:
        usage: "LABEL"
    - lag_bytes:
        usage: "GAUGE"
        description: "Replication slot lag in bytes"
    - has_alert:
        usage: "GAUGE"
        description: "1 if CDC source has an active alert"
    - alert_type:
        usage: "LABEL"

Prometheus Alerting Rules

# prometheus/alerts.yml
groups:
  - name: pgtrickle
    rules:
      - alert: PgTrickleTableStale
        expr: pgtrickle_stream_table_stats_is_stale == 1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Stream table {{ $labels.pgt_name }} is stale"

      - alert: PgTrickleConsecutiveErrors
        expr: pgtrickle_stream_table_stats_consecutive_errors >= 3
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Stream table {{ $labels.pgt_name }} has {{ $value }} consecutive errors"

      - alert: PgTrickleCdcLagHigh
        expr: pgtrickle_cdc_health_lag_bytes > 1073741824
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "CDC lag for {{ $labels.source_table }} exceeds 1GB"

      - alert: PgTrickleCdcAlert
        expr: pgtrickle_cdc_health_has_alert == 1
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "CDC alert for {{ $labels.source_table }}: {{ $labels.alert_type }}"

Effort: ~4 hours

Project 3 — Grafana Dashboard

Summary

A pre-built Grafana dashboard JSON that visualizes pg_trickle metrics from Prometheus (Project 2). Importable via Grafana UI or provisioning.

Repository

  • Repo: Same as Project 2 (pgtrickle-monitoring)
  • Distribution: JSON file, optionally published to Grafana Dashboards

Dashboard Panels

Row 1 — Overview

Panel Type Query
Active Stream Tables Stat count(pgtrickle_..._status{status="ACTIVE"})
Stale Tables Stat (red if >0) count(pgtrickle_..._is_stale == 1)
Error Tables Stat (red if >0) count(pgtrickle_..._status{status="ERROR"})
Total Refreshes/min Stat rate(pgtrickle_..._total_refreshes[5m])

Row 2 — Refresh Performance

Panel Type Query
Avg Refresh Duration Time series pgtrickle_..._avg_duration_ms by pgt_name
Refresh Rate Time series rate(pgtrickle_..._total_refreshes[5m]) by pgt_name
Failure Rate Time series rate(pgtrickle_..._failed_refreshes[5m]) by pgt_name

Row 3 — Staleness

Panel Type Query
Staleness per ST Time series pgtrickle_..._staleness_seconds by pgt_name
Rows Changed/min Time series rate(pgtrickle_..._total_rows_inserted[5m]) + rate(pgtrickle_..._total_rows_deleted[5m])

Row 4 — CDC Health

Panel Type Query
CDC Mode per Source Table pgtrickle_cdc_health{cdc_mode} by source_table
Replication Lag Time series pgtrickle_cdc_health_lag_bytes by source_table
CDC Alerts Alert list pgtrickle_cdc_health_has_alert == 1

Row 5 — Per-Table Detail (variable: $stream_table)

Panel Type Query
Status Stat Current status
Consecutive Errors Stat Error count
Refresh History Time series Insert/delete counts over time
Avg Duration Trend Time series Duration over time

Docker Compose Demo Stack

# docker-compose.yml — one-command observability demo
version: '3.8'
services:
  postgres:
    image: pg_trickle:latest
    environment:
      POSTGRES_PASSWORD: postgres
    ports: ['5432:5432']

  postgres-exporter:
    image: quay.io/prometheuscommunity/postgres-exporter:latest
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:postgres@postgres:5432/postgres?sslmode=disable"
      PG_EXPORTER_EXTEND_QUERY_PATH: /etc/pgtrickle_queries.yml
    volumes:
      - ./prometheus/pgtrickle_queries.yml:/etc/pgtrickle_queries.yml:ro
    ports: ['9187:9187']

  prometheus:
    image: prom/prometheus:latest
    volumes:
      - ./prometheus/prometheus.yml:/etc/prometheus/prometheus.yml:ro
      - ./prometheus/alerts.yml:/etc/prometheus/alerts.yml:ro
    ports: ['9090:9090']

  grafana:
    image: grafana/grafana:latest
    volumes:
      - ./grafana/pgtrickle-dashboard.json:/var/lib/grafana/dashboards/pgtrickle.json:ro
      - ./grafana/provisioning:/etc/grafana/provisioning:ro
    ports: ['3000:3000']
    environment:
      GF_SECURITY_ADMIN_PASSWORD: admin

Effort: ~4 hours

Project 4 — Docker Hub Image

Summary

A production-ready, ready-to-run Docker image (postgres:18-pgtrickle) with pg_trickle pre-installed and configured in shared_preload_libraries. Published to Docker Hub and/or GitHub Container Registry (GHCR).

Repository

  • Repo: Same as pg-trickle main repo (Dockerfile + CI workflow)
  • Published to: Docker Hub (pgtrickle/postgres:18) and GHCR (ghcr.io/<org>/pg_trickle:latest)

Dockerfile

Based on the existing cnpg/Dockerfile but with shared_preload_libraries set by default (unlike the CNPG image which defers to the operator):

# Dockerfile.release (in pg-trickle repo root)
FROM pg_trickle_builder AS builder
# ... (reuse existing build stage from cnpg/Dockerfile) ...

FROM postgres:18.1
COPY --from=builder /usr/share/postgresql/18/extension/pg_trickle* \
     /usr/share/postgresql/18/extension/
COPY --from=builder /usr/lib/postgresql/18/lib/pg_trickle.so \
     /usr/lib/postgresql/18/lib/

# Pre-configure for immediate use
RUN echo "shared_preload_libraries = 'pg_trickle'" >> \
    /usr/share/postgresql/postgresql.conf.sample

CI Workflow

# .github/workflows/docker-publish.yml
name: Publish Docker Image
on:
  push:
    tags: ['v*']
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: docker/setup-buildx-action@v3
      - uses: docker/login-action@v3
        with:
          username: ${{ secrets.DOCKERHUB_USERNAME }}
          password: ${{ secrets.DOCKERHUB_TOKEN }}
      - uses: docker/build-push-action@v5
        with:
          push: true
          tags: |
            pgtrickle/postgres:18
            pgtrickle/postgres:${{ github.ref_name }}
          file: Dockerfile.release

Tags

Tag Meaning
pgtrickle/postgres:18 Latest pg_trickle on PG 18
pgtrickle/postgres:18-0.1.0 Specific pg_trickle version
pgtrickle/postgres:latest Alias for :18

Quick Start

docker run -d --name pgtrickle \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  pgtrickle/postgres:18

psql -h localhost -U postgres -c "CREATE EXTENSION pg_trickle;"

Effort: ~8 hours

Project 5 — CNPG Integration

Summary

Harden the existing cnpg/ directory into a production-grade CloudNativePG integration with tested manifests, a Helm chart, and CI that validates the manifests against a real CNPG cluster.

Repository

  • Repo: Same as pg-trickle main repo (cnpg/ directory) — CNPG manifests are deployment config, not a separate product
  • Helm chart: Optionally in a separate pgtrickle-helm repo if published to Artifact Hub

Deliverables

Phase 1 — Hardened Manifests (~4 hours)

  • Templatize cnpg/cluster-example.yaml with common variants:
    • Single-instance (dev)
    • 3-instance HA (production)
    • WAL-mode CDC enabled (with wal_level: logical)
  • Add Backup and ScheduledBackup CRDs
  • Add Pooler CRD (PgBouncer) for connection pooling
  • Document required RBAC for the pg_trickle extension
cnpg/
├── Dockerfile
├── cluster-dev.yaml           # Single instance, no backup
├── cluster-production.yaml    # 3 instances, backup, pooler
├── cluster-wal-cdc.yaml       # WAL CDC enabled
├── backup.yaml                # Barman-based backup to S3
└── README.md

Phase 2 — Helm Chart (~8 hours)

pgtrickle-helm/
├── Chart.yaml
├── values.yaml
├── templates/
│   ├── cluster.yaml
│   ├── backup.yaml
│   ├── pooler.yaml
│   └── _helpers.tpl
└── README.md

Key values.yaml parameters:

instances: 3
image: ghcr.io/<org>/pg_trickle:latest
pgtrickle:
  enabled: true
  schedulerIntervalMs: 1000
  minScheduleSeconds: 60
  maxConcurrentRefreshes: 4
  cdcMode: trigger             # trigger | auto | wal
  userTriggers: auto
backup:
  enabled: false
  s3Bucket: ""
  schedule: "0 0 * * *"
pooler:
  enabled: false
  instances: 2

Effort: ~12 hours total

Project 6 — Airflow Provider

Summary

An Apache Airflow provider package (airflow-provider-pgtrickle) containing operators and sensors for integrating pg_trickle into Airflow DAGs. Enables data teams to orchestrate stream table refreshes alongside their existing ETL/ELT pipelines.

Repository

  • Repo: github.com/<org>/airflow-provider-pgtrickle (separate)
  • Language: Python
  • Distribution: PyPI (pip install airflow-provider-pgtrickle)

Components

Operators

Operator Purpose SQL Called
PgTrickleCreateOperator Create a stream table pgtrickle.create_stream_table()
PgTrickleDropOperator Drop a stream table pgtrickle.drop_stream_table()
PgTrickleRefreshOperator Trigger a manual refresh pgtrickle.refresh_stream_table()
PgTrickleAlterOperator Alter schedule/mode/status pgtrickle.alter_stream_table()

Sensors

Sensor Purpose SQL Polled
PgTrickleFreshnessSensor Wait until a ST is fresh (not stale) pgtrickle.pg_stat_stream_tables
PgTrickleHealthSensor Wait until CDC health is OK pgtrickle.check_cdc_health()
PgTrickleStatusSensor Wait until ST reaches a target status pgtrickle.pgt_stream_tables

Hooks

Hook Purpose
PgTrickleHook Extends PostgresHook with pg_trickle-specific helper methods

Example DAG

from airflow import DAG
from airflow.utils.dates import days_ago
from airflow_provider_pgtrickle.operators import (
    PgTrickleRefreshOperator,
)
from airflow_provider_pgtrickle.sensors import (
    PgTrickleFreshnessSensor,
)

with DAG("pgtrickle_refresh", start_date=days_ago(1), schedule_interval="@hourly"):

    wait_fresh = PgTrickleFreshnessSensor(
        task_id="wait_for_orders_fresh",
        stream_table="order_totals",
        postgres_conn_id="my_pg",
        timeout=300,
    )

    refresh = PgTrickleRefreshOperator(
        task_id="refresh_order_totals",
        stream_table="order_totals",
        postgres_conn_id="my_pg",
    )

    wait_fresh >> refresh

File Structure

airflow-provider-pgtrickle/
├── pyproject.toml
├── README.md
├── airflow_provider_pgtrickle/
│   ├── __init__.py
│   ├── hooks/
│   │   └── pgtrickle.py              # PgTrickleHook (~60 lines)
│   ├── operators/
│   │   ├── __init__.py
│   │   ├── create.py                # ~40 lines
│   │   ├── drop.py                  # ~30 lines
│   │   ├── refresh.py               # ~30 lines
│   │   └── alter.py                 # ~40 lines
│   └── sensors/
│       ├── __init__.py
│       ├── freshness.py             # ~50 lines
│       ├── health.py                # ~50 lines
│       └── status.py                # ~40 lines
└── tests/
    ├── test_hook.py
    ├── test_operators.py
    └── test_sensors.py

Effort: ~16 hours

Project 7 — CLI Tool (pgtrickle)

Summary

A standalone command-line tool for managing pg_trickle from the terminal. Provides a user-friendly interface to common operations without writing SQL.

Repository

  • Repo: github.com/<org>/pgtrickle-cli (separate) or as src/bin/pgtrickle.rs in the main repo if written in Rust
  • Language: Rust (preferred — shares build infra) or Python with click + psycopg
  • Distribution: GitHub Releases (binaries), Homebrew, cargo install, or PyPI

Commands

pgtrickle — CLI for pg_trickle streaming tables

USAGE:
    pgtrickle [OPTIONS] <COMMAND>

CONNECTION:
    -h, --host <HOST>          PostgreSQL host [default: localhost]
    -p, --port <PORT>          PostgreSQL port [default: 5432]
    -U, --user <USER>          PostgreSQL user [default: postgres]
    -d, --dbname <DB>          Database name [default: postgres]
    --url <URL>                Connection URL (overrides host/port/user/dbname)

COMMANDS:
    list                       List all stream tables
    status <name>              Show detailed status for a stream table
    create <name> <query>      Create a stream table
    drop <name>                Drop a stream table
    refresh <name>             Trigger a manual refresh
    alter <name> [OPTIONS]     Alter schedule, mode, or status
    explain <name>             Show the DVM plan
    history <name> [--limit]   Show refresh history
    health                     Show CDC health for all sources
    stats                      Show aggregate refresh statistics
    watch [--interval]         Live-updating status display (like `watch`)

Example Usage

# List all stream tables with status
$ pgtrickle list
NAME             SCHEMA   STATUS   MODE           SCHEDULE  STALE  ERRORS
order_totals     public   ACTIVE   DIFFERENTIAL   5m        no     0
big_customers    public   ACTIVE   DIFFERENTIAL   5m        no     0
daily_revenue    public   ERROR    FULL           1h        yes    3

# Detailed status
$ pgtrickle status order_totals
Name:           order_totals
Schema:         public
Status:         ACTIVE
Refresh Mode:   DIFFERENTIAL
Schedule:       5m
Last Refresh:   2026-02-24 12:34:56 UTC (26s ago)
Staleness:      26s
Stale:          no
Total Refreshes: 1,247
Avg Duration:   42ms
Source Tables:   orders (TRIGGER), customers (WAL)

# Live watch (refreshes every 2s)
$ pgtrickle watch --interval 2s
┌──────────────┬────────┬──────────────┬──────┬───────┬──────────┐
│ NAME         │ STATUS │ MODE         │ STALE│ ERRORS│ LAST     │
├──────────────┼────────┼──────────────┼──────┼───────┼──────────┤
│ order_totals │ ACTIVE │ DIFFERENTIAL │ no   │ 0     │ 4s ago   │
│ big_customers│ ACTIVE │ DIFFERENTIAL │ no   │ 0     │ 4s ago   │
│ daily_revenue│ ERROR  │ FULL         │ yes  │ 3     │ 1h ago   │
└──────────────┴────────┴──────────────┴──────┴───────┴──────────┘

# Create from file
$ pgtrickle create my_table --file my_query.sql --schedule 10m --mode DIFFERENTIAL
Created stream table: my_table

# CDC health check
$ pgtrickle health
SOURCE TABLE     CDC MODE       SLOT                  LAG      ALERT
public.orders    WAL            pg_trickle_slot_16384  512KB    none
public.events    TRIGGER        —                     —        —
public.users     TRANSITIONING  pg_trickle_slot_16400  0B       none

Implementation Notes

If Rust: Use clap for argument parsing, tokio-postgres for DB access, tabled or comfy-table for table formatting, crossterm for the watch TUI.

If Python: Use click for CLI, psycopg[binary] for DB access, rich for table formatting and live display.

Effort: ~20 hours

Project 8 — dbt Adapter

Full plan: ../dbt/PLAN_DBT_ADAPTER.md

Summary

A full dbt-pgtrickle adapter extending dbt-postgres. Provides first-class stream table support: custom relation types, __pgt_row_id column filtering, native source freshness, and operational run-operations.

Repository

  • Repo: github.com/<org>/dbt-pgtrickle (same repo as the macro package, or superseding it)
  • Language: Python + Jinja SQL
  • Distribution: PyPI (pip install dbt-pgtrickle)
  • Prerequisite: Project 1 (macro package) is the stepping stone; the adapter absorbs its macros

Key Advantages Over Macro Package

Feature Macro Package Full Adapter
__pgt_row_id hidden No Yes
Relation type stream_table No (shows as table) Yes
Native source freshness Manual macro Adapter override
Connection-time extension check No Yes
Custom catalog entries No ST metadata in docs

Effort: ~54 hours

Project 9 — PGXN & OS Packages

Summary

Publish pg_trickle to PGXN (the PostgreSQL Extension Network) and produce .deb/.rpm packages for Linux distributions. This is the standard way PostgreSQL users discover and install extensions.

Repository

  • Repo: Same as pg-trickle main repo (packaging config + CI workflows)

Deliverables

9.1 — PGXN Publication (~4 hours)

Add a META.json at the repo root:

{
  "name": "pg_trickle",
  "abstract": "Streaming tables with incremental view maintenance for PostgreSQL",
  "version": "0.1.0",
  "maintainer": ["Your Name <you@example.com>"],
  "license": "postgresql",
  "provides": {
    "pg_trickle": {
      "file": "pg_trickle.control",
      "version": "0.1.0"
    }
  },
  "prereqs": {
    "runtime": {
      "requires": {
        "PostgreSQL": "18.0.0"
      }
    }
  },
  "resources": {
    "repository": {
      "url": "https://github.com/<org>/pg-trickle.git",
      "type": "git"
    },
    "bugtracker": {
      "web": "https://github.com/<org>/pg-trickle/issues"
    }
  },
  "generated_by": "hand",
  "meta-spec": { "version": "1.0.0" }
}

Register at pgxn.org and publish with pgxn-utils.

9.2 — Debian/Ubuntu Packages (~8 hours)

CI workflow that cross-compiles pg_trickle and produces .deb packages:

# .github/workflows/deb-package.yml
name: Build .deb
on:
  push:
    tags: ['v*']
jobs:
  build:
    strategy:
      matrix:
        os: [ubuntu-22.04, ubuntu-24.04, debian-12]
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Build in Docker
        run: |
          docker build -t pg-trickle-builder -f packaging/Dockerfile.${{ matrix.os }} .
          docker run --rm -v $(pwd)/dist:/dist pg-trickle-builder
      - uses: softprops/action-gh-release@v1
        with:
          files: dist/*.deb

9.3 — RPM Packages (~4 hours)

Similar workflow producing .rpm packages for RHEL/Rocky/Alma 9.

9.4 — Homebrew Formula (~4 hours)

For macOS development:

class PgTrickle < Formula
  desc "Streaming tables with incremental view maintenance for PostgreSQL"
  homepage "https://github.com/<org>/pg-trickle"
  url "https://github.com/<org>/pg-trickle/archive/refs/tags/v0.1.0.tar.gz"
  sha256 "..."
  license "PostgreSQL"
  depends_on "postgresql@18"
  depends_on "rust" => :build
  # ...
end

Effort: ~20 hours total

Project 10 — Flyway & Liquibase Support

Summary

Documentation and optional tooling for managing stream tables with database migration tools. Since stream tables are created via function calls (not DDL), migration tools need guidance on the correct patterns.

Repository

  • Repo: Documentation in pg-trickle main repo (docs/integrations/)
  • Optional tooling: Liquibase extension in a separate repo

Deliverables

10.1 — Flyway Guide (~4 hours)

Document the migration pattern in docs/integrations/FLYWAY.md:

-- V1__create_order_totals.sql
SELECT pgtrickle.create_stream_table(
    'order_totals',
    'SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id',
    '5m',
    'DIFFERENTIAL'
);

-- V2__update_order_totals_schedule.sql
SELECT pgtrickle.alter_stream_table('order_totals', schedule => '10m');

-- V3__drop_order_totals.sql
SELECT pgtrickle.drop_stream_table('order_totals');

Flyway executes arbitrary SQL, so no plugin is needed — just the documentation showing the pattern, rollback strategy, and idempotency considerations.

10.2 — Liquibase Guide (~4 hours)

Document the sql changeset pattern in docs/integrations/LIQUIBASE.md:

<changeSet id="1" author="dev">
    <sql>
        SELECT pgtrickle.create_stream_table(
            'order_totals',
            'SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id',
            '5m', 'DIFFERENTIAL'
        );
    </sql>
    <rollback>
        <sql>SELECT pgtrickle.drop_stream_table('order_totals');</sql>
    </rollback>
</changeSet>

10.3 — Liquibase Custom Change Type (Optional, ~16 hours)

A Liquibase extension that adds <createStreamTable> and <dropStreamTable> change types with proper XML schema, rollback support, and checksums.

<createStreamTable name="order_totals"
                   schedule="5m"
                   refreshMode="DIFFERENTIAL">
    <query>
        SELECT customer_id, SUM(amount) AS total
        FROM orders GROUP BY customer_id
    </query>
</createStreamTable>

Effort: ~8 hours (docs only), ~24 hours (with Liquibase extension)

Project 11 — ORM Integrations

Summary

Thin integration layers for popular ORMs to make stream tables usable as read-only models with metadata access (staleness, last refresh, status).

Repository

  • Repos: Separate per ORM (django-pgtrickle, sqlalchemy-pgtrickle)
  • Language: Python
  • Distribution: PyPI

11.1 — Django Integration (django-pgtrickle)

# django_pgtrickle/models.py
from django.db import models

class StreamTableManager(models.Manager):
    """Read-only manager that exposes pg_trickle metadata."""

    def get_queryset(self):
        return super().get_queryset().defer('__pgt_row_id')

    def is_stale(self) -> bool:
        """Check if the stream table data is stale."""
        from django.db import connection
        with connection.cursor() as cursor:
            cursor.execute(
                "SELECT stale FROM pgtrickle.stream_tables_info "
                "WHERE pgt_name = %s", [self.model._meta.db_table]
            )
            row = cursor.fetchone()
            return row[0] if row else None

    def last_refresh_at(self):
        """Get the timestamp of the last refresh."""
        ...

    def refresh(self):
        """Trigger a manual refresh."""
        ...


class StreamTableModel(models.Model):
    """Base class for Django models backed by pg_trickle stream tables."""

    objects = StreamTableManager()

    class Meta:
        abstract = True
        managed = False  # Django does not manage the table schema


# Usage:
class OrderTotals(StreamTableModel):
    customer_id = models.IntegerField(primary_key=True)
    total = models.DecimalField(max_digits=10, decimal_places=2)

    class Meta(StreamTableModel.Meta):
        db_table = 'order_totals'

Additional features: - Django management command: python manage.py pgtrickle_status - Django admin integration: read-only ModelAdmin with refresh button - Health check: Django health check backend for django-health-check

11.2 — SQLAlchemy Integration (sqlalchemy-pgtrickle)

# sqlalchemy_pgtrickle/mixin.py
from sqlalchemy import event, inspect
from sqlalchemy.ext.hybrid import hybrid_property

class StreamTableMixin:
    """Mixin for SQLAlchemy models backed by stream tables."""

    @classmethod
    def __declare_last__(cls):
        """Make the table read-only by rejecting writes."""
        @event.listens_for(cls, "before_insert")
        @event.listens_for(cls, "before_update")
        @event.listens_for(cls, "before_delete")
        def reject_write(mapper, connection, target):
            raise RuntimeError(
                f"{cls.__name__} is a stream table and cannot be modified directly."
            )

    @classmethod
    def is_stale(cls, session) -> bool:
        result = session.execute(
            "SELECT stale FROM pgtrickle.stream_tables_info "
            "WHERE pgt_name = :name",
            {"name": cls.__tablename__}
        )
        row = result.fetchone()
        return row[0] if row else None

    @classmethod
    def refresh(cls, session):
        session.execute(
            f"SELECT pgtrickle.refresh_stream_table('{cls.__tablename__}')"
        )
        session.commit()

Effort: ~16 hours per ORM (Django and SQLAlchemy)

Dependency Graph

                          ┌──────────────────┐
                          │   pg_trickle      │
                          │   (core ext)     │
                          └────────┬─────────┘
                                   │
              ┌────────────────────┼────────────────────┐
              │                    │                    │
              ▼                    ▼                    ▼
    ┌─────────────────┐  ┌─────────────────┐  ┌──────────────┐
    │ P2: Prometheus   │  │ P4: Docker Hub  │  │ P9: PGXN/    │
    │     Config       │  │     Image       │  │    apt/rpm   │
    └───────┬─────────┘  └────────┬────────┘  └──────────────┘
            │                     │
            ▼                     ▼
    ┌─────────────────┐  ┌─────────────────┐
    │ P3: Grafana     │  │ P5: CNPG        │
    │     Dashboard   │  │     Integration │
    └─────────────────┘  └─────────────────┘

    ┌─────────────────┐  ┌─────────────────┐  ┌──────────────┐
    │ P1: dbt Macro   │──│ P8: dbt Adapter │  │ P7: CLI Tool │
    │     Package     │  │    (absorbs P1) │  │              │
    └─────────────────┘  └─────────────────┘  └──────────────┘

    ┌─────────────────┐  ┌─────────────────┐
    │ P6: Airflow     │  │ P10: Flyway/    │
    │     Provider    │  │     Liquibase   │
    └─────────────────┘  └─────────────────┘

    ┌─────────────────┐
    │ P11: Django /   │
    │      SQLAlchemy │
    └─────────────────┘

Hard dependencies: - P3 (Grafana) requires P2 (Prometheus) — dashboard queries Prometheus metrics - P8 (dbt Adapter) supersedes P1 (dbt Macro) — adapter absorbs macros

Soft dependencies: - P5 (CNPG) works better with P4 (Docker Hub Image) published - P6 (Airflow) can reuse patterns from P7 (CLI) or vice versa

Everything else is independent and can be built in any order.

Cross-Cutting Concerns

Documentation Standard

Every ecosystem project must include: - README.md — Quick start (copy-paste in <2 minutes), prerequisites, configuration - CHANGELOG.md — Semantic versioned history - LICENSE — Same as pg_trickle (PostgreSQL license) - CI badge — Build status in README

Versioning

All ecosystem projects follow semantic versioning. The major version tracks compatibility with pg_trickle’s SQL API: - pg_trickle 0.x → ecosystem projects 0.x (unstable API) - pg_trickle 1.0 → ecosystem projects 1.0+ (stable API)

Testing Against pg_trickle

Every project that calls pg_trickle SQL functions must have integration tests running against a real PostgreSQL 18 instance with pg_trickle installed. Use the existing tests/Dockerfile.e2e as the base test image, or the Docker Hub image (Project 4) once published.

Shared CI Infrastructure

Projects can share GitHub Actions workflows: - Reusable workflow for “spin up PostgreSQL 18 + pg_trickle” as a service container - Reusable workflow for “build pg_trickle Docker image” (needed by all integration tests)

SQL API Stability Contract

All ecosystem projects depend on pg_trickle’s SQL API surface. Changes to these functions require coordinated updates:

Function Used By
pgtrickle.create_stream_table() P1, P6, P7, P8, P10
pgtrickle.alter_stream_table() P1, P6, P7, P8, P10
pgtrickle.drop_stream_table() P1, P6, P7, P8, P10
pgtrickle.refresh_stream_table() P1, P6, P7, P8, P11
pgtrickle.pg_stat_stream_tables (view) P1, P2, P3, P6, P7, P8, P11
pgtrickle.check_cdc_health() P2, P3, P6, P7, P8
pgtrickle.explain_st() P7, P8
pgtrickle.get_refresh_history() P7, P8
pgtrickle.pgt_stream_tables (table) P1, P7, P8, P10, P11
pgtrickle.stream_tables_info (view) P7, P11

Before making breaking changes to any of these, check the “Used By” column and update the corresponding ecosystem projects.

Total Effort Summary

Project Effort Priority
P1: dbt Macro Package 15h Phase 3
P2: Prometheus Config 4h Phase 1
P3: Grafana Dashboard 4h Phase 1
P4: Docker Hub Image 8h Phase 2
P5: CNPG Integration 12h Phase 2
P6: Airflow Provider 16h Phase 4
P7: CLI Tool 20h Phase 4
P8: dbt Adapter 54h Phase 5
P9: PGXN & OS Packages 20h Phase 5
P10: Flyway/Liquibase 8–24h Phase 5
P11: ORM Integrations 32h Phase 5
Total ~193–209h

The first two phases (Observability + Distribution) deliver the highest adoption impact for the lowest effort: ~28 hours for Prometheus, Grafana, Docker Hub, and CNPG.