pg_tviews

Transactional Materialized Views with Incremental Refresh for PostgreSQL

CI/CD Status:

Core infrastructure for FraiseQL's GraphQL Cascade — automatic incremental refresh of JSONB read models with 5,000-12,000× performance gains.

By Lionel Hamayon • Part of the FraiseQL framework

Features • Quick Start • Performance • Documentation • Architecture

---

🍓 Part of the FraiseQL Ecosystem

pg_tviews is the performance foundation for FraiseQL's CQRS architecture:

Server Stack (PostgreSQL + Python/Rust)

Tool	Purpose	Status	Performance Gain
pg_tviews	Incremental materialized views	Beta ⭐	100-500× faster
jsonb_delta	JSONB surgical updates	Stable	2-7× faster
pgGit	Database version control	Stable	Git for databases
confiture	PostgreSQL migrations	Stable	300-600× faster
fraiseql	GraphQL framework	Stable	7-10× faster
fraiseql-data	Seed data generation	Phase 6	Auto-dependency resolution

Client Libraries (TypeScript/JavaScript)

Library	Purpose	Framework Support
graphql-cascade	Automatic cache invalidation	Apollo, React Query, Relay, URQL

How pg_tviews fits:

• fraiseql uses pg_tviews for GraphQL read models (tv_* tables)
• jsonb_delta optimizes JSONB updates (1.5-3× faster)
• confiture manages TVIEW schema evolution
• graphql-cascade (client-side) invalidates browser caches when mutations trigger refreshes

Stack it up:

# Install extensions
CREATE EXTENSION pg_tviews;
CREATE EXTENSION jsonb_delta;  -- Optional: 1.5-3× faster JSONB

# Create incremental view
CREATE TABLE tv_post AS SELECT ...;

# Use with fraiseql GraphQL
@fraiseql.type(sql_source="tv_post")
class Post: ...

---

📋 Version Status

Current Version: 0.1.0-beta.1 (December 2025)

• Status: Public Beta - Feature-complete, API may change
• Production Use: Suitable for evaluation, not mission-critical systems
• Support: Community support via GitHub issues

Roadmap to 1.0.0 (Q1 2026):

• ✅ Core TVIEW functionality complete
• ✅ Comprehensive documentation (in progress)
• 🔄 Production hardening and testing
• 🔄 Security audit
• 🔄 Performance validation at scale

Breaking Changes: Minor API changes possible until 1.0.0. Pin to exact version in production.

---

🎯 The Problem

Traditional PostgreSQL materialized views require full rebuilds on every refresh—scanning entire tables and recomputing all rows. For large datasets or complex views with JOINs, this becomes prohibitively expensive:

-- Traditional approach: Full rebuild every time
REFRESH MATERIALIZED VIEW my_view;  -- Scans ALL rows, recomputes EVERYTHING

Result: Minutes of downtime, high I/O, locks, and stale data between refreshes.

✨ The Solution

pg_tviews brings incremental materialized view maintenance to PostgreSQL with surgical, row-level updates that happen automatically within your transactions:

-- pg_tviews: Automatic incremental refresh
CREATE TABLE tv_post AS
SELECT p.pk_post as pk_post, jsonb_build_object(...) as data
FROM tb_post p JOIN tb_user u ON p.fk_user = u.pk_user;

-- Just use your database normally:
INSERT INTO tb_post(title, fk_user) VALUES ('New Post', 123);
COMMIT;  -- tv_post automatically updated with ONLY the affected row!

Result: Millisecond updates, no full scans, always up-to-date, zero manual intervention.

🚀 Performance Optimization

For 1.5-3× faster JSONB updates, install the optional jsonb_delta extension:

CREATE EXTENSION jsonb_delta;  -- Optional: 1.5-3× faster JSONB updates
CREATE EXTENSION pg_tviews;

Without jsonb_delta, pg_tviews uses standard PostgreSQL JSONB operations (still fast, just not optimized).

---

🔑 Trinity Identifier Pattern

pg_tviews follows FraiseQL's trinity identifier conventions for optimal GraphQL Cascade performance:

• id (UUID): Public identifier for GraphQL/REST APIs
• pk_entity (integer): Primary key for efficient joins and lineage tracking
• fk_* (integer): Foreign keys for cascade propagation
• identifier (text): Optional unique slugs for SEO-friendly URLs
• {parent}_id (UUID): Optional UUID FKs for FraiseQL filtering

Example TVIEW with full trinity support:

CREATE TABLE tv_post AS
SELECT
    p.pk_post,           -- lineage root
    p.id,                -- GraphQL ID
    p.identifier,        -- SEO slug
    p.fk_user,           -- cascade FK
    u.id as user_id,     -- FraiseQL filtering FK
    jsonb_build_object(
        'id', p.id,
        'identifier', p.identifier,
        'title', p.title,
        'author', jsonb_build_object(
            'id', u.id,
            'identifier', u.identifier,
            'name', u.name,
            'email', u.email
        )
    ) as data
FROM tb_post p
JOIN tb_user u ON p.fk_user = u.pk_user;

---

🚀 Key Features

Automatic & Intelligent

• 🔍 Smart Dependency Detection: Automatically analyzes SQL to find source tables and relationships
• 🎯 Surgical Updates: Updates only affected rows—never full table scans
• 🔄 Transactional Consistency: Refresh happens atomically within your transaction
• 📊 Cascade Propagation: Automatically handles multi-level view dependencies

High Performance

• ⚡ 100-500× Faster Triggers: Statement-level triggers for bulk operations
• 💾 Query Plan Caching: 10× faster with cached prepared statements
• 📦 Bulk Optimization: N rows with just 2 queries instead of N queries
• 🎨 Smart Patching: 2× performance boost with optional jsonb_delta integration

Production-Ready

• 🔐 Two-Phase Commit (2PC): Distributed transaction support with queue persistence
• 🏊 Connection Pooling: Full PgBouncer/pgpool-II compatibility with DISCARD ALL handling
• 📈 Comprehensive Monitoring: Real-time metrics, health checks, performance views
• 🛡️ Enterprise-Grade Code: 100% clippy-strict compliance, panic-safe FFI, zero unwraps

Compliance & Security

• 📋 SBOM Generation: Automated Software Bill of Materials in SPDX 2.3 and CycloneDX 1.5 formats
• 🔐 Cryptographic Signing: Sigstore keyless + GPG maintainer signatures for all releases
• 🛡️ Dependency Security: Automated vulnerability scanning with cargo-audit + cargo-vet audits
• 🔄 Automated Updates: Dependabot integration for security patches and updates
• 🏗️ Reproducible Builds: Docker-based build environment with locked dependencies
• 🌍 International Compliance: EU Cyber Resilience Act, US EO 14028, PCI-DSS 4.0, ISO 27001
• 🔒 Supply Chain Security: SLSA Level 3 provenance with dependency transparency
• 📊 Vulnerability Management: Complete dependency inventory for CVE tracking

Developer-Friendly

• 📝 Simple API: pg_tviews_create() function for easy TVIEW creation
• 🔧 JSONB Optimized: Built for modern JSONB-heavy applications
• 📊 Array Support: Full INSERT/DELETE handling for array columns
• 🐛 Excellent Debugging: Rich error messages, debug functions, health checks

---

📊 Performance

Real-World Benchmarks

Operation	Traditional MV	pg_tviews	Improvement
Single row update	2,500ms	1.2ms	2,083×
Medium cascade (50 rows)	7,550ms	3.72ms	2,028×
Bulk operation (1K rows)	180,000ms	100ms	1,800×

Scaling Characteristics

• Linear scaling with data size for incremental updates
• Sub-linear scaling for cascading updates (graph caching)
• Constant time for cache hits (90%+ hit rate in production)
• O(1) queue operations with HashSet-based deduplication

---

🎬 Quick Start

Installation

# Prerequisites
# - PostgreSQL 13-18 installed
# - Rust toolchain 1.70+

# Install pgrx
cargo install --locked cargo-pgrx

# Initialize pgrx
cargo pgrx init

# Clone and build
git clone https://github.com/fraiseql/pg_tviews.git
cd pg_tviews
cargo pgrx install --release

# Enable in your database
psql -d your_database -c "CREATE EXTENSION pg_tviews;"

Your First TVIEW

-- Create base tables (FraiseQL style)
CREATE TABLE tb_user (
    pk_user BIGSERIAL PRIMARY KEY,
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    identifier TEXT UNIQUE,
    name TEXT,
    email TEXT
);

CREATE TABLE tb_post (
    pk_post BIGSERIAL PRIMARY KEY,
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    identifier TEXT UNIQUE,
    title TEXT,
    content TEXT,
    fk_user BIGINT REFERENCES tb_user(pk_user)
);

-- Create a TVIEW (note: tv_ prefix is required)
CREATE TABLE tv_post AS
SELECT
    p.pk_post as pk_post,  -- Primary key column (required)
    p.id,                  -- GraphQL ID
    p.identifier,          -- SEO slug
    p.fk_user,             -- Cascade FK
    u.id as user_id,       -- FraiseQL filtering FK
    jsonb_build_object(
        'id', p.id,
        'identifier', p.identifier,
        'title', p.title,
        'content', p.content,
        'author', jsonb_build_object(
            'id', u.id,
            'identifier', u.identifier,
            'name', u.name,
            'email', u.email
        )
    ) as data  -- JSONB data column (required)
FROM tb_post p
JOIN tb_user u ON p.fk_user = u.pk_user;

-- Use it like a table
SELECT data FROM tv_post WHERE data->>'title' ILIKE '%rust%';

-- It updates automatically!
INSERT INTO tb_user (identifier, name, email) VALUES ('alice', 'Alice', 'alice@example.com');
INSERT INTO tb_post (identifier, title, content, fk_user) VALUES
    ('learning-rust', 'Learning Rust', 'Rust is amazing!', 1);

-- tv_post is now automatically up-to-date!
SELECT data FROM tv_post;

Enable Advanced Features

-- Install statement-level triggers for 100-500× better bulk performance
SELECT pg_tviews_install_stmt_triggers();

-- Monitor system health
SELECT * FROM pg_tviews_health_check();

-- View real-time metrics
SELECT * FROM pg_tviews_queue_realtime;

---

🏗️ Architecture

High-Level Design

┌─────────────────────────────────────────────────────────────────┐
│                     User Application                            │
└────────────────────┬────────────────────────────────────────────┘
                     │ INSERT/UPDATE/DELETE
                     ▼
┌─────────────────────────────────────────────────────────────────┐
│                    PostgreSQL Core                              │
│  ┌──────────────┐     ┌──────────────┐     ┌──────────────┐    │
│  │  tb_* Tables │────▶│   Triggers   │────▶│ Refresh Queue│    │
│  │  (command)   │     │  (per-row or │     │ (thread-local)│   │
│  └──────────────┘     │  statement)  │     └──────┬────────┘    │
│                       └──────────────┘            │             │
│                       ┌──────────────┐            │             │
│                       │  ProcessUtil │            │             │
│                       │  Hook (DDL)  │            │             │
│                       └──────────────┘            │             │
│                                                   │             │
│                       ┌───────────────────────────▼──────────┐  │
│                       │    Transaction Callback Handler      │  │
│                       │  (PRE_COMMIT, COMMIT, ABORT, 2PC)    │  │
│                       └──────────┬────────────────────────────┘  │
│                                  │                               │
│                                  ▼                               │
│               ┌──────────────────────────────────────────┐      │
│               │      pg_tviews Refresh Engine            │      │
│               │                                           │      │
│               │  ┌─────────────────────────────────────┐ │      │
│               │  │  Dependency Graph Resolution        │ │      │
│               │  │  (Topological Sort, Cycle Detect)   │ │      │
│               │  └───────────┬──────────────────────────┘ │      │
│               │              │                            │      │
│               │              ▼                            │      │
│               │  ┌─────────────────────────────────────┐ │      │
│               │  │   Bulk Refresh Processor            │ │      │
│               │  │   (2 queries for N rows)            │ │      │
│               │  └───────────┬──────────────────────────┘ │      │
│               │              │                            │      │
│               │              ▼                            │      │
│               │  ┌─────────────────────────────────────┐ │      │
│               │  │  Cache Layer (Graph, Table, Plan)   │ │      │
│               │  └───────────┬──────────────────────────┘ │      │
│               │              │                            │      │
│               │              ▼                            │      │
│               │  ┌─────────────────────────────────────┐ │      │
│               │  │    Metrics & Monitoring              │ │      │
│               │  └─────────────────────────────────────┘ │      │
│               └──────────────────────────────────────────┘      │
│                                  │                               │
│                                  ▼                               │
│  ┌──────────────┐     ┌──────────────┐     ┌──────────────┐    │
│  │  TVIEW Tables│◀────│  Backing     │◀────│   Metadata   │    │
│  │  (tv_*)      │     │  Views (v_*) │     │  (pg_tview_*)│    │
│  └──────────────┘     └──────────────┘     └──────────────┘    │
└─────────────────────────────────────────────────────────────────┘

Key Components

1. Trigger System: Captures changes at source tables, enqueues refresh operations
2. Transaction Queue: Thread-local HashSet for deduplication and ACID guarantees
3. Dependency Graph: Resolves refresh order, detects cycles, enables cascading
4. Refresh Engine: Executes surgical updates with bulk optimization
5. Cache Layer: Three-tier caching (graph, table OIDs, query plans)
6. Monitoring: Real-time metrics, health checks, performance analytics

---

📚 Documentation

Getting Started

• Quick Start - Step-by-step setup guide
• Installation - Detailed installation instructions
• FraiseQL Integration - Framework integration guide

User Guides

• For Developers - Application integration patterns
• For Operators - Production deployment guide
• For Architects - CQRS design decisions

Reference

• API Reference - Complete function reference
• DDL Reference - CREATE/DROP TABLE syntax
• Syntax Comparison - TVIEW creation methods
• Error Reference - Error types and solutions
• Configuration - Configuration options

Operations

• Monitoring - Metrics and health checks
• Troubleshooting - Debugging procedures
• Performance - 📊 Complete performance guide (index)
  • Performance Best Practices - Essential patterns
  • Performance Analysis - Diagnostic tools
  • Index Optimization - Index strategies
  • Performance Tuning - Advanced tuning
  • Security - Security best practices
  • SBOM - Software Bill of Materials and supply chain security
• Disaster Recovery - Backup and recovery
• Runbooks - Operational procedures
• Upgrades - Version migration guides

Benchmarks

• Overview - Performance testing methodology and 4-way comparison
• Running Benchmarks - How to run benchmarks (Docker, pgrx, manual)
• Docker Setup - Advanced Docker benchmarking (requires jsonb_delta)
• Results Interpretation - Understanding benchmark results
• Results - Detailed benchmark data

Development

• Contributing - Development setup and contribution guidelines
• Testing - Testing patterns and procedures
• Architecture Deep Dive - Technical architecture details

---

🎯 Use Cases

Perfect For:

✅ FraiseQL Applications - Real-time GraphQL Cascade with UUID filtering ✅ E-commerce Dashboards - Real-time product aggregations with inventory ✅ Analytics Workloads - Pre-aggregated reporting tables that stay fresh ✅ API Response Caching - JSONB views for fast API responses ✅ Activity Feeds - User timelines with JOINed data ✅ Denormalization - Read-optimized tables without manual cache invalidation

Not Recommended For:

❌ Write-Heavy Tables - If you have >1000 writes/sec per table ❌ Simple Queries - If a regular index works fine ❌ Append-Only Logs - No need for incremental refresh

---

🤝 Contributing

Contributions welcome! This is a portfolio project, but I'm happy to collaborate:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

Development Setup: See DEVELOPMENT.md

---

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

---

⭐ If you find this project interesting, please consider starring it! ⭐

Built with ❤️ and Rust 🦀