Create optimized materialized views with synthetic keys and content hashing
Manage materialized view health with automatic staleness detection and refresh
Analyze table fitness for partitioning, indexing, and query optimization
Monitor data quality with encoding issue detection and isolation

Features

Materialized View Management

Create materialized views with synthetic keys and proper partitioning
Track content hashes to detect data changes efficiently
Isolate records with encoding issues into separate views
Monitor materialized view health with configurable thresholds
Automatically refresh views based on staleness metrics
Estimate refresh times based on historical performance

Table Fitness Analysis

Analyze column characteristics for partitioning and indexing
Identify optimal column combinations for keys and partitioning
Evaluate data quality with comprehensive metrics
Calculate overall Data Quality Index (DQI)
Use statistical sampling for efficient analysis of large tables

Requirements

PostgreSQL 11 or later
c77_dbh extension (dependency)

Installation

Quick Install

If you have both extensions available in your PostgreSQL extensions directory:

CREATE EXTENSION c77_dbh;  -- Install dependency first
CREATE EXTENSION c77_mvc;

From Source

# Clone repository
git clone https://git.jctr3.com/trogers1884/c77_mvc.git
cd c77_mvc

# Copy files to PostgreSQL extension directory
export PGEXTDIR=$(pg_config --sharedir)/extension
sudo cp c77_mvc.control $PGEXTDIR/
sudo cp c77_mvc--1.0.sql $PGEXTDIR/

# Create extension in your database
psql -d your_database -c "CREATE EXTENSION c77_dbh;"
psql -d your_database -c "CREATE EXTENSION c77_mvc;"

For detailed installation instructions, see INSTALLATION.md.

Basic Usage

Table Fitness Analysis

-- Analyze a table for fitness metrics
SELECT * FROM public.c77_mvc_analyze_table_fitness('schema_name', 'table_name');

Creating Optimized Materialized Views

-- Create an optimized materialized view
SELECT * FROM public.c77_mvc_create_optimized_matv(
  'source_schema',           -- Source schema
  'source_table',            -- Source table
  'target_schema',           -- Target schema
  'matc_target_view_name',   -- Target materialized view name (must start with matc_)
  ARRAY['customer_id'],      -- Partition columns
  ARRAY['last_updated'],     -- Order-by columns
  ARRAY['notes'],            -- Columns to exclude from hash calculation (optional)
  false                      -- Filter for latest records only (optional)
);

Managing Materialized View Health

-- Check materialized view health
SELECT * FROM public.c77_mvc_manage_matv_health(
  'schema_name',       -- Schema name
  'matc_view_name',    -- Materialized view name
  'quick',             -- Validation type: 'quick', 'daily', 'full'
  NULL                 -- Action: NULL, 'refresh', 'repair', 'reindex'
);

-- Check and refresh if needed
SELECT * FROM public.c77_mvc_manage_matv_health(
  'schema_name',
  'matc_view_name',
  'daily',
  'refresh'
);

For comprehensive usage examples, see USAGE.md.

View Structure

When you create an optimized materialized view, the extension creates multiple objects:

Object	Naming Pattern	Purpose
View	vtw_*	Source view with content hash, synthetic key, and encoding status
Materialized View	matc_*	Materialized copy of the vtw_ view
View	vm_*	Clean data view (excludes encoding issues)
View	vprob_*	Problematic data view (only encoding issues)

Documentation

Contributing

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

License

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

Acknowledgements

Developed by [Your Name/Organization]
Special thanks to contributors and testers