pg_trickle TUI — User Guide

pgtrickle is a terminal tool for managing and monitoring pg_trickle stream tables. It works in two modes:

• Interactive dashboard — run pgtrickle with no arguments to launch a live-updating TUI that shows all your stream tables, their health, dependencies, and configuration.
• One-shot CLI — run pgtrickle <command> to perform a single operation and exit. Output goes to stdout in table, JSON, or CSV format. Designed for scripts, CI pipelines, and automation.

Building

The TUI is a standalone Rust binary in the pgtrickle-tui workspace member. It does not require the PostgreSQL extension to compile — only a Rust toolchain.

# Build (debug)
cargo build -p pgtrickle-tui

# Build (release, optimized)
cargo build --release -p pgtrickle-tui

# The binary is at:
#   target/debug/pgtrickle       (debug)
#   target/release/pgtrickle     (release)

To install it on your PATH:

cargo install --path pgtrickle-tui

Verify:

pgtrickle --version
pgtrickle --help

Requirements

• Rust 2024 edition (1.85+)
• A running PostgreSQL 18 server with the pg_trickle extension installed
• Network access to the database (no local socket required)

Connecting to a Database

pgtrickle resolves connection parameters in this order (first match wins):

Connection flags work with every subcommand and with the interactive dashboard:

# URL-style connection
pgtrickle --url postgres://admin:secret@db.example.com:5432/analytics

# Environment variables (most common in production)
export PGHOST=db.example.com
export PGPORT=5432
export PGDATABASE=analytics
export PGUSER=admin
export PGPASSWORD=secret
pgtrickle list

# Explicit flags
pgtrickle --host db.example.com --dbname analytics --user admin list

Interactive Dashboard

Run pgtrickle with no subcommand:

pgtrickle

This opens a full-screen terminal UI that auto-refreshes every 2 seconds. The screen has three areas:

• Header — application name, current view, connection status (● connected / ✗ disconnected), and time since last poll.
• Body — the active view (see below).
• Footer — keyboard shortcuts for switching views and a filter indicator.

Press q or Ctrl+C to exit.

Views

There are 14 views. Switch between them by pressing the key shown:

Keyboard Shortcuts

Navigation — works in all views:

Write actions (view-specific):

Global actions:

View switching:

Press 1–9, w, f, m, d, g, or i to jump directly to any view. The active view and selected table are shown in both the header bar and the footer nav bar.

Command Palette

Press : to open the command palette. Tab-completion works on stream table names. Available commands:

LISTEN/NOTIFY

The TUI opens a second, dedicated database connection that runs LISTEN pg_trickle_alert. Alerts (refresh failures, auto-suspension events, etc.) appear in the Alerts view (9) in real time, without waiting for the next poll cycle.

CLI Subcommands

Every subcommand runs non-interactively: it connects, executes one query, prints the result, and exits. This makes them suitable for shell scripts, cron jobs, CI pipelines, and monitoring probes.

Output Formats

All subcommands that produce tabular output accept --format / -f:

Command Reference

pgtrickle list

List all stream tables with status, mode, schedule, tier, and refresh stats.

pgtrickle list
pgtrickle list --format json

pgtrickle status <name>

Show detailed status for a single stream table.

pgtrickle status order_totals
pgtrickle status order_totals --format json

pgtrickle refresh <name>

Trigger a manual refresh of one stream table, or all of them.

pgtrickle refresh order_totals
pgtrickle refresh --all

pgtrickle create <name> <query>

Create a new stream table with the given defining query.

pgtrickle create my_totals "SELECT region, SUM(amount) FROM orders GROUP BY region"
pgtrickle create my_totals "SELECT ..." --schedule 5m --mode differential
pgtrickle create my_totals "SELECT ..." --no-initialize

pgtrickle drop <name>

Drop a stream table.

pgtrickle drop my_totals

pgtrickle alter <name>

Change a stream table's settings.

pgtrickle alter order_totals --mode full
pgtrickle alter order_totals --schedule 10m
pgtrickle alter order_totals --tier cold
pgtrickle alter order_totals --status paused
pgtrickle alter order_totals --query "SELECT ..."

pgtrickle export <name>

Print the DDL (SQL definition) for a stream table.

pgtrickle export order_totals

pgtrickle diag [name]

Show refresh mode diagnostics and recommendations. Without a name, shows all tables. With a name, shows diagnostics for that table only.

pgtrickle diag
pgtrickle diag order_totals
pgtrickle diag --format json

pgtrickle cdc

Show CDC change buffer sizes and health.

pgtrickle cdc
pgtrickle cdc --format json

pgtrickle graph

Print the stream table dependency graph as an ASCII tree.

pgtrickle graph
pgtrickle graph --format json

pgtrickle config

Show all pg_trickle.* GUC parameters, or set one.

pgtrickle config
pgtrickle config --set pg_trickle.unlogged_buffers=true
pgtrickle config --format json

The --set flag runs ALTER SYSTEM SET followed by pg_reload_conf().

pgtrickle health

Run system health checks. Returns exit code 1 if any check is CRITICAL.

pgtrickle health
pgtrickle health --format json

# Use in CI/monitoring:
pgtrickle health || echo "Health check failed"

pgtrickle workers

Show the background worker pool status and pending job queue.

pgtrickle workers
pgtrickle workers --format json

pgtrickle fuse

Show fuse (circuit breaker) status for all stream tables.

pgtrickle fuse
pgtrickle fuse --format json

pgtrickle watermarks

Show watermark groups and source gating status.

pgtrickle watermarks
pgtrickle watermarks --format json

pgtrickle explain <name>

Inspect the generated delta SQL, DVM operator tree, or deduplication stats for a stream table. By default shows the delta SQL.

pgtrickle explain order_totals                  # Delta SQL
pgtrickle explain order_totals --analyze        # EXPLAIN ANALYZE on the delta
pgtrickle explain order_totals --operators      # DVM operator tree
pgtrickle explain order_totals --dedup          # Dedup stats per source
pgtrickle explain order_totals --format json

pgtrickle watch

Non-interactive continuous output mode. Polls the database and prints a status table at regular intervals. Useful for CI logs, monitoring, and terminals without TUI support.

pgtrickle watch                     # Default: every 2 seconds
pgtrickle watch -n 10               # Every 10 seconds
pgtrickle watch --compact           # One line per table
pgtrickle watch --no-color          # No ANSI color codes
pgtrickle watch --append            # Append mode (don't clear screen)

# Log to a file
pgtrickle watch --compact --no-color --append >> /var/log/pgtrickle.log

pgtrickle completions <shell>

Generate shell completion scripts. Install them once and get tab-completion for all subcommands and flags.

# Bash
pgtrickle completions bash > /etc/bash_completion.d/pgtrickle
# or for the current user:
pgtrickle completions bash > ~/.local/share/bash-completion/completions/pgtrickle

# Zsh
pgtrickle completions zsh > ~/.zfunc/_pgtrickle

# Fish
pgtrickle completions fish > ~/.config/fish/completions/pgtrickle.fish

# PowerShell
pgtrickle completions powershell > pgtrickle.ps1

Examples

Quick health check in CI

#!/bin/bash
set -e
export PGHOST=db.example.com PGDATABASE=analytics PGUSER=monitor

pgtrickle health || { echo "pg_trickle health check failed"; exit 1; }
pgtrickle list --format json | jq '.[] | select(.status != "ACTIVE")'

Monitor stream tables in a tmux pane

pgtrickle watch -n 5

Export all definitions for version control

for name in $(pgtrickle list --format json | jq -r '.[].name'); do
  pgtrickle export "$name" > "sql/stream_tables/${name}.sql"
done

Debug a slow differential refresh

pgtrickle explain order_totals --analyze
pgtrickle explain order_totals --operators
pgtrickle explain order_totals --dedup

How It Works

The TUI connects to PostgreSQL using tokio-postgres (async, no TLS by default) and queries pg_trickle's built-in SQL API functions:

In interactive mode, a background task polls all of these every 2 seconds and pushes state updates to the rendering loop. A second connection runs LISTEN pg_trickle_alert for real-time notifications.

The TUI is purely a client — it reads from pg_trickle's monitoring API and sends commands (refresh, create, drop, alter) through the same SQL functions you would call from psql. It does not require any special privileges beyond what the pg_trickle SQL API requires.

Planned: cache_stats() and health_summary() Integration

Status: Not yet surfaced in the TUI (v0.18.0 gap).

The following SQL functions are available but not yet integrated into the TUI:

• pgtrickle.cache_stats() — template cache hit rate, L1 hits, evictions, delta cache entries. Useful for monitoring cache effectiveness.
• pgtrickle.health_summary() — single-row deployment overview with total/active/error/stale stream table counts, P99 refresh latency, scheduler status, and cache hit rate.

Lightest integration path: Add cache hit rate to the Dashboard status ribbon (currently shows scheduler status from quick_health). The Health Checks view (8) could display health_summary() fields alongside the existing health_check() results. Both functions are already available via raw SQL (psql, Grafana, or the Prometheus exporter).

Tech Stack