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

Where it runs. pg_tviews is a compiled extension loaded with shared_preload_libraries: it runs on PostgreSQL you install or operate yourself, including on Kubernetes (operator image). Managed services (RDS, Cloud SQL, Azure, Supabase, Neon, …) only install extensions from their own allow-lists; there, use FraiseQL's v_* mode. See ADR 0232.

🍓 Part of the FraiseQL Ecosystem

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

Server Stack (PostgreSQL + Python/Rust)

Client Libraries (TypeScript/JavaScript)

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.28 (October 2026)

• 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:

• ✅ Core TVIEW functionality complete
• ✅ Comprehensive documentation
• 🔄 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). CREATE EXTENSION pg_tviews says so once, with a WARNING; after that each backend notes it once in the server log, and pg_tviews_health_check() reports it. Writes send no message.

🔑 Identifiers

A TVIEW's definition outputs pk_<entity> (the first such column names the entity; with DISTINCT ON, its key names the rows), and usually id and data. Nothing else is a naming rule: base tables, join columns and the column holding an embedded TVIEW's key may have any names, because pg_tviews reads the definition's query tree, not its spelling.

FraiseQL's trinity identifiers fit this naturally:

• id (UUID): public identifier for GraphQL/REST APIs
• pk_<entity> (integer): the row key, for joins and refreshes
• identifier (text): optional unique slug
• {parent}_id (UUID): optional UUID of a parent, for filtering

Example:

CREATE TABLE tv_post AS
SELECT
    p.pk_post,           -- lineage root
    p.id,                -- GraphQL ID
    p.identifier,        -- SEO slug
    p.fk_user,           -- the author's key
    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
• 🤝 Correct Under Concurrency: Concurrent writers to related rows wait for each other on the join values they share, so every TVIEW row equals its view once they commit (READ COMMITTED), or one fails with a retryable 40001 (REPEATABLE READ, SERIALIZABLE). See Concurrency
• 📊 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
• 🚀 UNLOGGED Tables, opt-in: faster writes, refilled automatically after a crash

Production-Ready

• 🏊 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 and license checks with cargo-audit and cargo-deny
• 🔄 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
• ⏸️ Bulk Operations: Suspend/resume triggers for safe bulk data loading (Issue #44)

📊 Performance

Real-World Benchmarks

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

⚡ Direct-patch fast path (Issue #56)

For an eligible UPDATE, pg_tviews builds a JSONB patch at trigger time from NEW and applies it straight to tv_<entity>.data with jsonb_smart_patch_scalar/_nested — skipping the backing-view recompute entirely (zero reads of the backing view). The same patch is derived for parent tviews that embed the entity as a nested object, so a single-field change with a wide fan-out (e.g. an author edited on 60 posts) propagates as a handful of grouped patch UPDATEs with no view queries.

Anything outside the eligibility boundary silently falls back to the normal recompute path, which stays the single source of truth — the fast path is a pure optimization and its data output is byte-identical to a recompute.

What qualifies

The fast path engages only when every condition holds (otherwise recompute):

• the change is a row-level UPDATE (INSERT/DELETE change membership);
• jsonb_delta is installed and pg_tviews.direct_patch_enabled = on;
• the entity is not DISTINCT ON and not a UNION;
• every changed column maps identity-style to a top-level data key (jsonb_build_object('bio', bio) — no expression, cast, or other relation);
• no changed column is a foreign key, the primary key, or a column the tview also projects outside data;
• the changed values' types are TEXT/VARCHAR, INT2/4/8, BOOL, UUID, JSONB, or NULL (anything else — float, numeric, timestamps, arrays — falls back);
• for a parent, its dependency on the child is nested_object with a path (array/scalar/UUID-fk parents recompute).

Copied parent columns (Issues #120, #224)

A tview that copies a column of a joined parent table into its data, at the top level or in nested objects as FraiseQL writes them ('author', jsonb_build_object('name', u.name) over JOIN tb_user u ON u.pk_user = p.fk_user), is written by one statement when that column changes: UPDATE tv_post … WHERE fk_user = <pk>, jsonb_set at each path, instead of recomputing every post of that user. Same rules as above, plus: the tview reads the parent through one join on its own base table, projects that fk_* column, reads the parent table only once, and uses the column nowhere else in its definition. Arrays of embedded rows and embeds two joins away recompute (#228). On the FraiseQL-shaped write benchmark an author's bio change costs ~0.15 ms over the plain UPDATE.

Kill-switch and observability

SET pg_tviews.direct_patch_enabled = off;   -- superuser: force the recompute path everywhere

tviews.stats exposes the counters per TVIEW, from any session:

SELECT entity, patch_captured, patch_applied, patch_fallbacks, view_recomputes
FROM tviews.stats;

An eligible update leaves view_recomputes unchanged and bumps patch_applied — proof it skipped the view. pg_tviews_queue_stats() shows the same counters for the calling session only.

Upgrade note

The column→key map is extracted when a TVIEW is registered. TVIEWs registered by an older release keep working on the recompute path until SELECT * FROM tviews.pg_tviews_reregister_all() re-derives their metadata in place (see Upgrading).

💾 LOGGED and UNLOGGED TVIEWs

TVIEWs are LOGGED tables by default: crash-safe, replicated, readable on a hot standby. A TVIEW whose rows can be recomputed and that needs the write speed declares logged: false and becomes an UNLOGGED table (no WAL for its refreshes).

SELECT pg_tviews_create_or_replace('tv_post', $$ SELECT … $$, '{"logged": false}');
CREATE UNLOGGED TABLE tv_tag AS SELECT …;                 -- the same, as DDL
SELECT * FROM pg_tviews_replication_status();             -- what a standby can serve

ALTER TABLE tv_post SET LOGGED switches one back, like the logged option.

⚠️ Hot standbys, promotion and crash restarts

An UNLOGGED table is not replicated. A hot standby cannot read an UNLOGGED TVIEW at all (ERROR: cannot access temporary or unlogged relations during recovery), and promotion or a crash restart leaves it empty. pg_tviews records which UNLOGGED TVIEWs it can trust in an UNLOGGED table the reset empties too. After a crash restart or a promotion, a background worker refills the reset TVIEWs of every database (pg_tviews.auto_rebuild_databases, default *); a write that reaches a reset TVIEW first refills it itself. A TVIEW that is merely empty is never refilled. After a restore, call SELECT * FROM pg_tviews_rebuild_all();. See docs/operations/replication.md.

Configuration

A TVIEW is its definition and its options (DDL reference): no setting changes what it is. The settings tune limits and sessions:

The superuser-only diagnostics pg_tviews.graph_cache_enabled, pg_tviews.table_cache_enabled and pg_tviews.direct_patch_enabled (all on) are hidden from SHOW ALL; results are the same either way. Diagnostics are DEBUG1 messages: SET client_min_messages = debug1 shows them.

SET pg_tviews.batch_size = 5000;            -- larger bulk-refresh chunks
SET pg_tviews.cache_size = 50000;           -- bigger per-session caches

GUCs require shared_preload_libraries = 'pg_tviews' (already needed for the extension) so they are registered at backend start.

What a definition needs

A TVIEW's definition is one SELECT that outputs pk_<entity> and reads at least one table. Every table it reads either maps its writes to the TVIEW's keys or follows the TVIEW's uncascaded_policy: under the default, error, a definition reading a table whose writes cannot be traced is refused at creation, with the reason and how to declare a full refresh instead.

Safety Guarantees

• ✅ Data Recovery: All TVIEW data reconstructible from base tables
• ✅ Transparent: Applications work unchanged
• ✅ Opt-in: UNLOGGED only for the TVIEWs that declare it
• ✅ Only after a reset: An empty TVIEW is refilled only when PostgreSQL reset it, once, whatever the number of concurrent writers
• ✅ Tested: Comprehensive crash simulation and recovery testing

🎬 Quick Start

Installation

# Prerequisites
# - PostgreSQL 16, 17 or 18 (the supported versions; CREATE EXTENSION refuses older ones)
# - Rust toolchain (pinned in rust-toolchain.toml)

# Install pgrx (must match project version)
cargo install --locked cargo-pgrx --version 0.17.0

# Initialize pgrx
cargo pgrx init

# Clone and build
git clone https://github.com/fraiseql/pg_tviews.git
cd pg_tviews
cargo pgrx install --release   # PostgreSQL 18; for 16 or 17 add
                               # --no-default-features --features pg16 (or pg17)

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

The tviews schema

Every object of the extension lives in the schema tviews, whatever the search_path at CREATE EXTENSION (CREATE EXTENSION pg_tviews SCHEMA other is refused). CREATE EXTENSION creates the schema when it is missing and refuses one owned by another role. Call the functions qualified, or add tviews to the search_path:

SELECT tviews.pg_tviews_create('tv_post', $$ SELECT ... $$);

ALTER DATABASE your_database SET search_path = "$user", public, tviews;
SELECT pg_tviews_create('tv_post', $$ SELECT ... $$);

TVIEWs themselves (tv_*, v_*) are created in the schema you name, or in current_schema(). Nothing pg_tviews does at run time needs tviews on the search_path. DROP EXTENSION pg_tviews leaves the tviews schema behind, empty.

Privileges

A TVIEW is maintained by its owner, like a materialized view: when a role writes to a base table, the refresh reads and writes each affected TVIEW as the owner of its tv_* table, with search_path set to pg_catalog, pg_temp. Writers need only their privileges on the base tables; grant application roles SELECT on the tv_* tables they read. The owner needs SELECT on everything its definition reads.

Creating TVIEWs from migrations

tviews.pg_tviews_create_or_replace(name, query, options) creates a TVIEW, or brings an existing one to query and options with the smallest change, so a migration can be applied again:

SELECT tviews.pg_tviews_create_or_replace('app.tv_post', $$
    SELECT p.pk_post, p.id, p.fk_user, jsonb_build_object('title', p.title) AS data
    FROM app.tb_post p $$, options => '{"fillfactor": 90}');
-- created | unchanged | altered (options only) | replaced (same columns, rows
-- reconciled in place) | rebuilt

The options passed are the whole declaration: an option left out is at its default (logged: true, fillfactor: 85, …), also on an existing TVIEW.

It runs the DDL as the caller and requires owning an existing TVIEW; a role that owns the schema and has TRIGGER on the base tables needs no superuser. CREATE TABLE tv_post AS SELECT … (optionally UNLOGGED, WITH (fillfactor = n, toast_tuple_target = n)) runs the same code with create-only semantics. See docs/reference/read-contract.md.

Reading what is registered

Tools read tviews.registry (one row per TVIEW: schema, name, entity, normalized query, every option, base tables, needs_reregister, backing view), tviews.stats (refresh counters per TVIEW) and check tviews.contract_version() (2). All three follow the stability rules in docs/reference/read-contract.md; tviews.pg_tview_meta and the other pg_tview_* tables are internal.

Upgrading

Each release has its own extension version (SELECT extversion FROM pg_extension WHERE extname = 'pg_tviews') and ships upgrade scripts:

1. Install the new package and restart PostgreSQL (the library is preloaded).
2. In each database: ALTER EXTENSION pg_tviews UPDATE;
3. When the release notes say so, or tviews.pg_tviews_health_check() reports TVIEWs to re-register: SELECT * FROM tviews.pg_tviews_reregister_all(); It re-derives each TVIEW's metadata and triggers from its definition, without touching its rows.

Between steps 1 and 2, writes to the TVIEWs' base tables fail with pg_tviews library catalog revision … does not match the installed extension: they are never served by a mismatched library.

Installs of 0.1.0 (every release up to 0.1.0-beta.19) cannot be updated in place. After step 1, run scripts/migrate-from-0.1.0.sql in each database instead: it moves the extension to tviews and re-registers every TVIEW, keeping their rows (not the audit log).

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

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

-- Size, HOT ratio and missing indexes of each TVIEW
SELECT * FROM pg_tviews_profile();

-- What each TVIEW's refreshes did, from any session
SELECT * FROM tviews.stats;

⏸️ Bulk Operations (Issue #44)

For bulk INSERT/UPDATE/DELETE operations (e.g., seed data loading, ETL imports) on tables with TVIEWs, use the suspend/resume API to prevent trigger-based refresh during the operation:

Basic Pattern

Suspension lasts until the end of the transaction, so run the load in one:

BEGIN;
SELECT pg_tviews_suspend_triggers();   -- no refresh from here on in this transaction

INSERT INTO customers SELECT * FROM staging_customers;
INSERT INTO orders SELECT * FROM staging_orders;

SELECT pg_tviews_resume_triggers();    -- rebuilds the changed TVIEWs, dependencies first
COMMIT;

Why This Matters

When bulk inserting into multiple related tables, triggers refresh TVIEW rows after each statement, while the related tables may not be loaded yet. With suspension, all the data is loaded first, and each TVIEW that changed (and every TVIEW that embeds it) is rebuilt once, in dependency order.

Ending the Transaction Without Resuming

An explicit COMMIT catches up the same way when the transaction is still suspended. An implicit commit (an autocommit statement, a DO block) cannot: it logs a WARNING naming the stale TVIEWs, to be fixed with pg_tviews_refresh(tview) or pg_tviews_refresh_all().

API Reference

• pg_tviews_suspend_triggers() - Start suspension (supports nesting)
• pg_tviews_resume_triggers() - Resume; rebuilds the TVIEWs that changed and those that embed them
• pg_tviews_refresh(tview) - Rebuild one TVIEW and every TVIEW that embeds it, in dependency order
• pg_tviews_refresh_all() - Rebuild every TVIEW in dependency order
• pg_tviews_is_suspended() - Check current suspension state
• pg_tviews_suspended_entities() - List entities that changed during suspension

Nested Suspension

Calls can be nested; each must be matched:

BEGIN;
SELECT pg_tviews_suspend_triggers();  -- depth 1
SELECT pg_tviews_suspend_triggers();  -- depth 2
-- operations...
SELECT pg_tviews_resume_triggers();   -- depth 1
SELECT pg_tviews_resume_triggers();   -- depth 0 (now resumed)
COMMIT;

Use Cases

• Seed data loading: DB initialization with initial data set
• ETL imports: Loading data from external sources into staging tables
• Snapshot imports: Restoring from database dumps or migrations
• Bulk migrations: Large data transformations

🏗️ 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
• Embed or Compose - Embed a list in the parent's document, or compose it at read time
• Change Feed - Cache invalidation across processes (tviews_changes)

Reference

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

Operations

• Monitoring - Metrics and health checks
• Troubleshooting - Debugging procedures
• Performance - 📊 Performance tuning
  • 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 - Methodology and the three-arm comparison
• Running Benchmarks - How to run the harness on a local pgrx cluster
• Results - Measured performance figures
• Results Interpretation - Reading the numbers honestly
• jsonb_delta Integration - jsonb_delta's role and the parity finding

Development

• Development - Development setup
• Testing - Testing patterns and procedures
• Architecture - Technical architecture

🎯 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 docs/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 🦀