Contents

REPORT_DB_SCHEMA_STABILITY.md — Database Schema Stability Assessment

Author: pg_trickle team
Date: 2026-02-25
Status: Assessment / Pre-1.0 Readiness
Scope: All database objects created by the pg_trickle extension

1. Executive Summary

Before releasing pg_trickle 1.0, we must audit every database object the extension creates — schemas, tables, views, functions, triggers, event triggers, GUCs, naming conventions, and internal column names — and decide which surfaces are public API (stable after 1.0) and which are internal (can evolve with migration scripts).

This document:

  1. Inventories all database objects and classifies them as public or internal
  2. Identifies bugs and inconsistencies that must be fixed before 1.0
  3. Proposes pre-1.0 changes to reduce future breaking changes
  4. Defines an upgrade/migration strategy using PostgreSQL’s built-in ALTER EXTENSION ... UPDATE mechanism
  5. Establishes stability contracts for each object category

2. Complete Object Inventory

2.1 Schemas

Schema Purpose API Level
pgtrickle Catalog tables, SQL functions, views Public
pgtrickle_changes Change buffer tables (configurable via GUC) Internal

2.2 Catalog Tables

pgtrickle.pgt_stream_tables — Core ST metadata

Column Type Constraints API
pgt_id BIGSERIAL PRIMARY KEY Internal (surrogate)
pgt_relid OID NOT NULL UNIQUE Internal
pgt_name TEXT NOT NULL Public (via views)
pgt_schema TEXT NOT NULL Public (via views)
defining_query TEXT NOT NULL Public (via explain_st)
original_query TEXT Internal
schedule TEXT Public
refresh_mode TEXT NOT NULL DEFAULT 'DIFFERENTIAL' Public
status TEXT NOT NULL DEFAULT 'INITIALIZING' Public
is_populated BOOLEAN NOT NULL DEFAULT FALSE Public
data_timestamp TIMESTAMPTZ Public
frontier JSONB Internal
last_refresh_at TIMESTAMPTZ Public
consecutive_errors INT NOT NULL DEFAULT 0 Public
needs_reinit BOOLEAN NOT NULL DEFAULT FALSE Internal
auto_threshold DOUBLE PRECISION Internal
last_full_ms DOUBLE PRECISION Internal
functions_used TEXT[] Internal
created_at TIMESTAMPTZ NOT NULL DEFAULT now() Public
updated_at TIMESTAMPTZ NOT NULL DEFAULT now() Public

pgtrickle.pgt_dependencies — Source dependency edges

Column Type Constraints API
pgt_id BIGINT FK → pgt_stream_tables, PK Internal
source_relid OID PK Internal
source_type TEXT CHECK IN (‘TABLE’,‘STREAM_TABLE’,‘VIEW’) Internal
columns_used TEXT[] Internal
column_snapshot JSONB Internal
schema_fingerprint TEXT Internal
cdc_mode TEXT DEFAULT ‘TRIGGER’, CHECK IN (‘TRIGGER’,‘TRANSITIONING’,‘WAL’) Internal
slot_name TEXT Internal
decoder_confirmed_lsn PG_LSN Internal
transition_started_at TIMESTAMPTZ Internal

pgtrickle.pgt_refresh_history — Audit log

Column Type Constraints API
refresh_id BIGSERIAL PRIMARY KEY Internal
pgt_id BIGINT NOT NULL Internal
data_timestamp TIMESTAMPTZ NOT NULL Public (via get_refresh_history)
start_time TIMESTAMPTZ NOT NULL Public
end_time TIMESTAMPTZ Public
action TEXT CHECK IN (…) Public
rows_inserted BIGINT DEFAULT 0 Public
rows_deleted BIGINT DEFAULT 0 Public
error_message TEXT Public
status TEXT CHECK IN (…) Public
initiated_by TEXT CHECK IN (…) Public
freshness_deadline TIMESTAMPTZ Internal

pgtrickle.pgt_change_tracking — Per-source CDC slot tracking

Column Type Constraints API
source_relid OID PRIMARY KEY Internal
slot_name TEXT NOT NULL Internal
last_consumed_lsn PG_LSN Internal
tracked_by_pgt_ids BIGINT[] Internal

2.3 Views

View Schema API Level
pgtrickle.stream_tables_info pgtrickle Public
pgtrickle.pg_stat_stream_tables pgtrickle Public

2.4 SQL Functions (Public API)

Function Signature Notes
pgtrickle.create_stream_table (name, query, schedule='1m', refresh_mode='DIFFERENTIAL', initialize=true) Core API
pgtrickle.alter_stream_table (name, schedule=NULL, refresh_mode=NULL, status=NULL) Core API
pgtrickle.drop_stream_table (name) Core API
pgtrickle.refresh_stream_table (name) Core API
pgtrickle.pgt_status () → SETOF record Status overview
pgtrickle.parse_duration_seconds (input) → BIGINT Utility
pgtrickle.st_refresh_stats () → SETOF record Monitoring
pgtrickle.get_refresh_history (name, max_rows=20) → SETOF record Monitoring
pgtrickle.get_staleness (name) → FLOAT8 Monitoring
pgtrickle.slot_health () → SETOF record Monitoring
pgtrickle.explain_st (name) → SETOF record Diagnostic
pgtrickle.check_cdc_health () → SETOF record Monitoring

2.5 SQL Functions (Internal)

Function Purpose
pgtrickle.pg_trickle_hash(text) → BIGINT Row identity hashing
pgtrickle.pg_trickle_hash_multi(text[]) → BIGINT Multi-column row hashing
pgtrickle._on_ddl_end() → event_trigger DDL tracking
pgtrickle._on_sql_drop() → event_trigger DROP tracking

2.6 Event Triggers

Name Event API Level
pg_trickle_ddl_tracker ddl_command_end Internal
pg_trickle_drop_tracker sql_drop Internal

2.7 Dynamic Objects (per stream table / source table)

Object Naming Pattern Schema
Storage table <user_schema>.<user_name> User-specified
Change buffer table changes_<source_oid> pgtrickle_changes
CDC row trigger pg_trickle_cdc_<oid> On source table
CDC truncate trigger pg_trickle_cdc_truncate_<oid> On source table
CDC trigger function pg_trickle_cdc_fn_<oid>() pgtrickle_changes
CDC truncate function pg_trickle_cdc_truncate_fn_<oid>() pgtrickle_changes
Change buffer index idx_changes_<oid>_lsn_pk_cid pgtrickle_changes
Replication slot pgtrickle_<oid> Cluster-wide
Publication pgtrickle_cdc_<oid> Cluster-wide

2.8 Hardcoded Column Names (Storage Tables)

Column Type When Created API Level
__pgt_row_id BIGINT Always Public (visible to users who query STs)
__pgt_count BIGINT Aggregate/DISTINCT queries Internal (but visible)

2.9 Hardcoded Column Names (Change Buffer Tables)

Column Type API Level
change_id BIGSERIAL Internal
lsn PG_LSN Internal
action CHAR(1) Internal
pk_hash BIGINT Internal
new_<colname> Per-source Internal
old_<colname> Per-source Internal

2.10 GUC Variables (16 total)

GUC Type Default API Level
pg_trickle.enabled BOOL true Public
pg_trickle.scheduler_interval_ms INT 1000 Public
pg_trickle.min_schedule_seconds INT 60 Public
pg_trickle.max_consecutive_errors INT 3 Public
pg_trickle.change_buffer_schema STRING 'pgtrickle_changes' Public
pg_trickle.max_concurrent_refreshes INT 4 Public
pg_trickle.differential_max_change_ratio FLOAT 0.15 Public
pg_trickle.cleanup_use_truncate BOOL true Public
pg_trickle.merge_planner_hints BOOL true Public
pg_trickle.merge_work_mem_mb INT 64 Public
pg_trickle.merge_strategy STRING 'auto' Public
pg_trickle.use_prepared_statements BOOL true Public
pg_trickle.user_triggers STRING 'auto' Public
pg_trickle.cdc_mode STRING 'trigger' Public
pg_trickle.wal_transition_timeout INT 300 Public
pg_trickle.block_source_ddl BOOL false Public

2.11 NOTIFY Channels

Channel API Level
pg_trickle_alert Public
pg_trickle_cdc_transition Public
pgtrickle_refresh Public

2.12 Advisory Lock Keys

Key Purpose
pgt_id (BIGINT) Concurrent refresh prevention

3. Bugs and Inconsistencies to Fix Before 1.0

3.1 CHECK Constraint Bugs — CRITICAL

Bug 1: pgt_stream_tables.refresh_mode CHECK constraint lists 'DIFFERENTIAL' twice:

CHECK (refresh_mode IN ('FULL', 'DIFFERENTIAL', 'DIFFERENTIAL'))

Should be:

CHECK (refresh_mode IN ('FULL', 'DIFFERENTIAL'))

Bug 2: pgt_refresh_history.action CHECK constraint lists 'DIFFERENTIAL' twice:

CHECK (action IN ('NO_DATA', 'FULL', 'DIFFERENTIAL', 'DIFFERENTIAL', 'REINITIALIZE', 'SKIP'))

Should be:

CHECK (action IN ('NO_DATA', 'FULL', 'DIFFERENTIAL', 'REINITIALIZE', 'SKIP'))

Severity: Low (duplicates in CHECK are logically harmless but signal sloppiness; they would become confusing in documentation or dump output).

Fix: Correct before 1.0 — straightforward edit in src/lib.rs.

3.2 Naming Inconsistency — GUC Prefix vs Schema

The GUC prefix is pg_trickle.* (with underscore), but the schema is pgtrickle (no underscore). This is mildly confusing but changing either now would be more disruptive than living with the inconsistency—the GUC prefix is baked into postgresql.conf files and the schema into SQL scripts.

Recommendation: Document the convention explicitly. Do not change.

3.3 NOTIFY Channel Naming Inconsistency

Three channels use inconsistent naming:

  • pg_trickle_alert — underscore style
  • pg_trickle_cdc_transition — underscore style
  • pgtrickle_refresh — no separator style

Recommendation: Rename pgtrickle_refresh → pg_trickle_refresh before 1.0 for consistency. This is a breaking change for any external listener, but since we’re pre-1.0, now is the time.

3.4 Missing Foreign Key on pgt_refresh_history

pgt_refresh_history.pgt_id references pgt_stream_tables.pgt_id logically but lacks a formal FK constraint. When a stream table is dropped, orphan history rows remain indefinitely.

Recommendation: Add REFERENCES pgtrickle.pgt_stream_tables(pgt_id) ON DELETE CASCADE or implement periodic cleanup. The CASCADE approach is simpler and appropriate since history is meaningless for dropped STs.

3.5 pgt_refresh_history Unbounded Growth

No built-in retention policy. Production deployments will accumulate millions of rows in the refresh history table.

Recommendation: Add a GUC pg_trickle.history_retention_days (default: 30) and a cleanup step in the scheduler that runs daily — DELETE FROM pgtrickle.pgt_refresh_history WHERE start_time < now() - interval '...'.

3.6 pgt_change_tracking — Orphan Risk

The tracked_by_pgt_ids BIGINT[] column can contain stale pgt_id values after a stream table is dropped if cleanup is interrupted.

Recommendation: Consider normalizing to a junction table, or accept the array design and add explicit cleanup in drop_stream_table_impl(). For 1.0, the current design is acceptable with a cleanup sweep.

4. Pre-1.0 Schema Changes to Improve Future Stability

4.1 Add a Schema Version Tracking Table

Create a metadata table to enable future migration scripts:

CREATE TABLE IF NOT EXISTS pgtrickle.pgt_schema_version (
    version     TEXT NOT NULL PRIMARY KEY,
    applied_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    description TEXT
);
INSERT INTO pgtrickle.pgt_schema_version (version, description)
VALUES ('1.0.0', 'Initial 1.0 release');

This lets migration scripts check the current schema version and apply only the needed transformations.

4.2 Reserve Columns for Future Use

Adding columns to existing tables is backwards-compatible (new code can handle NULL in new columns; old extension versions ignore unknown columns). There is no need to reserve columns. Instead, follow the additive-only principle:

  • Safe changes (minor version): ADD COLUMN (nullable), ADD INDEX, CREATE VIEW, new GUC with default, new function
  • Unsafe changes (major version): DROP COLUMN, ALTER COLUMN TYPE, RENAME COLUMN, drop/rename function, change function signature, rename GUC

4.3 Frontier JSONB Structure

The frontier column stores serialized JSON with this structure:

{
  "sources": {
    "<oid>": {"lsn": "0/1A2B3C4", "snapshot_ts": "1708XXX"}
  },
  "data_timestamp": "1708XXXZ"
}

Risk: The structure uses OID strings as keys. OIDs can be reused after table drop/recreate. The current code handles this correctly, but the serialization format is internal to the Rust Frontier struct and could change.

Recommendation: Treat the frontier JSONB column as fully opaque. Never expose its structure in public API documentation. If the format needs to change, a migration script can read/rewrite all frontier values.

4.4 Consider Extracting Schedule Parsing to SQL

parse_duration_seconds() is exposed as a public SQL function because the views stream_tables_info and pg_stat_stream_tables depend on it. This couples view definitions to a C function.

Recommendation: This is acceptable. The function is IMMUTABLE and PARALLEL SAFE, making it safe for view inlining. Keep as-is.

4.5 Normalize refresh_mode and status into ENUMs

Currently, refresh_mode, status, action, cdc_mode, and source_type are all TEXT with CHECK constraints. PostgreSQL ENUM types would provide type safety but are notoriously hard to modify (ALTER TYPE … ADD VALUE is append-only; removing values requires recreating the type).

Recommendation: Keep TEXT with CHECK constraints. This is more flexible for migrations — adding a new value just requires ALTER TABLE ... DROP CONSTRAINT ... ADD CONSTRAINT. ENUMs would create migration headaches.

4.6 Add pgt_id FK to pgt_refresh_history

As noted in §3.4:

ALTER TABLE pgtrickle.pgt_refresh_history
    ADD CONSTRAINT fk_hist_pgt_id
    FOREIGN KEY (pgt_id) REFERENCES pgtrickle.pgt_stream_tables(pgt_id)
    ON DELETE CASCADE;

5. Public API Stability Contract

After 1.0, these surfaces are stable and subject to semantic versioning:

5.1 Tier 1 — Strong Stability (Breaking Change = Major Version)

Surface Contract
Function names & parameter order create_stream_table(name, query, ...)
Function parameter defaults schedule DEFAULT '1m'
Function return types pgt_status() column names and types
View column names & types stream_tables_info.*, pg_stat_stream_tables.*
GUC names pg_trickle.enabled, etc.
Schema names pgtrickle, pgtrickle_changes
Storage table visible columns __pgt_row_id (always present), user columns
NOTIFY channel names pg_trickle_alert, pg_trickle_cdc_transition, …

5.2 Tier 2 — Moderate Stability (Can Add, Cannot Remove)

Surface Contract
Catalog table columns Can add nullable columns; cannot drop or rename
View columns Can add columns at the end; cannot remove
Function output columns Can append new columns to SETOF results
GUC values/ranges Can add new values (e.g., new merge strategy); cannot remove
CHECK constraint values Can add new enum-like values; cannot remove

5.3 Tier 3 — Internal (Can Change with Migration Script)

Surface Contract
Catalog table internal columns frontier, functions_used, auto_threshold, etc.
Change buffer table structure Column types, index layout
Dynamic object naming conventions changes_<oid>, pg_trickle_cdc_<oid>
Trigger function body (PL/pgSQL) Generated code, may change per-version
Hash function implementation pg_trickle_hash algorithm
Advisory lock key scheme Using pgt_id as lock key
Frontier JSONB format Internal serialization
Background worker name pg_trickle scheduler

6. Upgrade/Migration Strategy

6.1 PostgreSQL Extension Update Mechanism

PostgreSQL supports versioned extension upgrades via ALTER EXTENSION:

ALTER EXTENSION pg_trickle UPDATE TO '1.1.0';

This executes a migration script named pg_trickle--1.0.0--1.1.0.sql installed alongside the extension shared library.

6.2 Migration Script Architecture

Following ADR-063’s recommendation (Option 3: Hybrid), we use:

  1. pgrx auto-generated SQL — for function signature updates (C functions are automatically re-registered with CREATE OR REPLACE FUNCTION)
  2. Hand-written migration scripts — for catalog table changes, index additions, data migrations, view updates

Migration scripts live in sql/ and follow the naming convention:

sql/pg_trickle--1.0.0--1.1.0.sql
sql/pg_trickle--1.1.0--1.2.0.sql
sql/pg_trickle--1.2.0--2.0.0.sql

6.3 Migration Script Template

-- pg_trickle--1.0.0--1.1.0.sql
-- Migration: 1.0.0 → 1.1.0

-- 1. Schema version check (fail-safe)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pgtrickle.pgt_schema_version
        WHERE version = '1.0.0'
    ) THEN
        RAISE EXCEPTION 'pg_trickle: expected schema version 1.0.0, '
                         'run previous migrations first';
    END IF;
END $$;

-- 2. Catalog table changes (additive only for minor versions)
ALTER TABLE pgtrickle.pgt_stream_tables
    ADD COLUMN IF NOT EXISTS new_column TEXT;

-- 3. View updates (CREATE OR REPLACE preserves existing grants)
CREATE OR REPLACE VIEW pgtrickle.stream_tables_info AS
SELECT ... ;

-- 4. Function signature updates (handled by pgrx, typically no manual SQL)

-- 5. Data migrations
-- UPDATE pgtrickle.pgt_stream_tables SET new_column = ... WHERE ...;

-- 6. Index changes
-- CREATE INDEX IF NOT EXISTS ... ;

-- 7. Record schema version
INSERT INTO pgtrickle.pgt_schema_version (version, description)
VALUES ('1.1.0', 'Added new_column to pgt_stream_tables');

6.4 Dynamic Object Migration

Dynamic objects (change buffer tables, CDC triggers) present a unique challenge: they are not managed by the extension DDL but created at runtime by create_stream_table. Migration strategies:

Option A: Reinitialize-on-upgrade — Mark all STs as needs_reinit = TRUE during migration. The scheduler will rebuild each ST on its next cycle, recreating triggers and buffer tables with the new format. This is safe but slow for large deployments.

Option B: In-place migration — The migration script iterates over active STs and applies ALTER TABLE / CREATE OR REPLACE FUNCTION to each dynamic object. This is fast but complex and must handle edge cases (missing tables, in-progress refreshes, etc.).

Option C: Lazy migration — Dynamic objects are migrated on first access (e.g., the refresh code checks the buffer table schema and adds missing columns). This is transparent but adds code complexity.

Recommendation: Option A for major version upgrades. Option B for minor version upgrades that only require additive changes to dynamic objects. Include a pgtrickle.migrate() utility function that iterates over STs and applies any needed dynamic object changes.

6.5 Breaking Changes Procedure (Major Version)

For unavoidable breaking changes (column removals, type changes, renamed functions), the migration script must:

  1. Create new structures alongside old ones
  2. Copy/transform data from old to new
  3. Drop old structures
  4. Update schema version

Example: renaming pgt_stream_tables.schedule to refresh_interval:

-- Step 1: Add new column
ALTER TABLE pgtrickle.pgt_stream_tables ADD COLUMN refresh_interval TEXT;

-- Step 2: Copy data
UPDATE pgtrickle.pgt_stream_tables SET refresh_interval = schedule;

-- Step 3: Drop old column (after confirming all code uses new name)
ALTER TABLE pgtrickle.pgt_stream_tables DROP COLUMN schedule;

6.6 Rollback Strategy

PostgreSQL does not support downgrading extensions (ALTER EXTENSION ... UPDATE TO only goes forward). For rollback:

  1. pg_dump before upgrade — always take a logical backup
  2. Version-specific cleanup SQL — ship a pg_trickle--1.1.0--1.0.0.sql rollback script for emergency use (best-effort)
  3. Test in staging — always test upgrades against a production-like environment before applying

7. Specific Recommendations Before 1.0

7.1 Must-Fix (Blocking 1.0)

# Issue Section Effort
1 Fix duplicate 'DIFFERENTIAL' in CHECK constraints §3.1 10 min
2 Add FK on pgt_refresh_history.pgt_id §3.4 10 min
3 Add pgtrickle.pgt_schema_version table §4.1 30 min
4 Rename pgtrickle_refresh → pg_trickle_refresh channel §3.3 30 min

7.2 Should-Fix (Recommended for 1.0)

# Issue Section Effort
5 Add history retention GUC + scheduler cleanup §3.5 2–3 hours
6 Document public API stability contract §5 1 hour
7 Create migration script template §6.3 1 hour
8 Validate orphan cleanup in drop_stream_table §3.6 1 hour

7.3 Nice-to-Have (Can Ship After 1.0)

# Issue Section Effort
9 pgtrickle.migrate() utility function §6.4 4–6 hours
10 Automated migration test infrastructure §8 8–12 hours

8. Testing Migration Scripts

8.1 Migration Test Strategy

Each migration script should be tested with:

  1. Fresh install test — install version N directly; verify schema
  2. Upgrade test — install version N-1, populate with test data, run ALTER EXTENSION ... UPDATE TO 'N', verify all data preserved
  3. Idempotency test — run migration twice; verify no errors
  4. Data integrity test — verify FKs, indexes, and CHECK constraints still hold after migration

8.2 Test Infrastructure

Add an E2E test file tests/e2e_migration_tests.rs that:

  1. Creates a container with pg_trickle version N-1 installed
  2. Creates several stream tables with various configurations
  3. Triggers refreshes to populate history
  4. Upgrades to version N via ALTER EXTENSION
  5. Verifies catalog integrity
  6. Verifies stream tables still function (refresh, alter, drop)

9. Risk Assessment Summary

Risk Likelihood Impact Mitigation
Catalog column rename needed Low High Additive-only policy (§4.2)
Function signature change Medium High Careful parameter defaults (§5.1)
Frontier format change Medium Low Opaque JSONB (§4.3), migration rewrite
Change buffer schema change Medium Low Reinitialize-on-upgrade (§6.4 Option A)
GUC rename needed Low High Document clearly, never rename (§5.1)
New CHECK constraint value High None TEXT + CHECK is additive-safe (§4.5)
View column addition High None Append-only, CREATE OR REPLACE
Hash function algorithm change Low High Would require full reinit of all STs
Event trigger behavior change Low Medium Transparent to users

10. Comparison with Other PostgreSQL Extensions

Extension Schema versioning Migration approach
PostGIS Version table Hand-written upgrade scripts per version pair
pgaudit No catalog tables Configuration-only, no migration needed
pg_cron Single metadata table Manual ALTER TABLE in upgrade scripts
Citus Version tracking Elaborate migration framework with rollback
TimescaleDB _timescaledb_catalog.metadata Loader checks version, runs migrations

pg_trickle should follow the PostGIS/TimescaleDB pattern: a schema version table, hand-written migration scripts, and a pre-upgrade version check.

11. Conclusion

The pg_trickle database schema is in good shape for a 1.0 release after addressing the four must-fix items (§7.1). The TEXT-with-CHECK-constraint pattern for enum-like columns is the right choice for migration flexibility. The separation between public-facing views/functions and internal catalog columns provides a clean abstraction boundary.

The main risks are around function signature stability (adding parameters with defaults is safe; changing return column sets requires view updates) and dynamic object migration (change buffer tables and CDC triggers need per-ST migration logic for structural changes).

The recommended approach is:

  1. Fix the four must-fix items now
  2. Ship 1.0 with clear documentation of the stability contract
  3. Implement migration test infrastructure before 1.1
  4. Follow additive-only changes for minor versions; save breaking changes for major versions with comprehensive migration scripts