# v0.1.0 — The Foundation

> **Full technical details:** [v0.1.x.md-full.md](v0.1.x.md-full.md)

**Status: ✅ Released** | **Scope: Very Large** (~6–8 months)

> The first complete release of pg_trickle: a PostgreSQL extension that keeps
> summary tables automatically up to date as your underlying data changes —
> without full recomputation on every query.

---

## What problem does this solve?

A common challenge in data-driven applications is showing up-to-date
aggregates — totals, averages, recent activity counts — without slowing every
read query to a crawl. The traditional choices are: recompute from scratch on
every request (slow at scale), use a scheduled batch job (always stale), or
build a custom caching layer (complex, fragile).

pg_trickle takes a different approach: it automatically maintains *stream
tables* — materialised results that are kept current incrementally, applying
only the changes that have occurred since the last update. This is called
**incremental view maintenance (IVM)**, and v0.1.0 delivers the complete
engine for the first time.

---

## A Differential Engine for SQL

The heart of pg_trickle is a **differential dataflow engine** that understands
21 SQL operations and knows how to update their results incrementally. Rather
than re-running a query over millions of rows to incorporate three new rows,
the engine computes exactly the delta — the minimum work required.

*In plain terms:* if your dashboard shows "total orders by region" and 50 new
orders arrive, pg_trickle updates those totals in milliseconds by processing
just those 50 rows, not the entire orders table.

The engine handles filters, joins, aggregates (COUNT, SUM, MIN, MAX, AVG),
DISTINCT, subqueries, window functions, CASE expressions, UNION, and more.

---

## Change Capture via Triggers

To know which rows changed, pg_trickle installs lightweight **row-level AFTER
triggers** on your source tables. Every INSERT, UPDATE, and DELETE is
automatically captured into a small change buffer in the same transaction —
guaranteed to be consistent with your data, no matter what.

A **WAL decoder** provides an alternative capture path for workloads where
triggers are not desirable.

*In plain terms:* pg_trickle watches your tables for changes automatically.
You do not need to instrument your application code — the database itself
reports every change.

---

## Background Scheduler

Refreshes do not happen synchronously during every write. Instead, a
**background worker** wakes up on a configurable schedule, checks which stream
tables have pending changes, and processes them in dependency order. Stream
tables that depend on other stream tables are always refreshed in the correct
sequence.

The scheduler supports cron-style expressions, fixed intervals, and an
event-driven mode that wakes up as soon as changes are detected.

---

## Monitoring and Diagnostics

A full set of diagnostic functions lets you inspect the health of your stream
tables:

- **`check_cdc_health()`** — detects change-capture problems before they
  affect query results
- **`explain_st(name)`** — shows the defining query, current refresh mode,
  last refresh time, and any warnings
- **`pg_stat_stream_tables`** view — a live overview of all stream tables and
  their status

---

## dbt Integration

For teams using **dbt** for data transformation, pg_trickle ships a macro that
creates stream tables directly from dbt models. Your dbt models become
continuously-maintained live views with no extra configuration.

---

## Production-Grade from Day One

v0.1.0 launches with a comprehensive test suite:

- **1,300+ tests** across unit, integration, and end-to-end tiers
- **22 TPC-H benchmark queries** validating correctness under complex SQL
- **Testcontainers-based** integration tests that run against a real
  PostgreSQL instance — no mocking
- Property-based tests for the most critical invariants

---

## Scope

v0.1.0 is the complete foundation of pg_trickle — the differential engine,
change capture, scheduling, monitoring, and dbt integration. Everything in
future versions is built on top of this core. The release underwent
approximately six to eight months of development and represents a
production-capable incremental view maintenance system for PostgreSQL.
