150 lines
4.5 KiB
Markdown
150 lines
4.5 KiB
Markdown
# c77_mvc PostgreSQL Extension
|
|
|
|
[](https://www.postgresql.org/)
|
|
|
|
A PostgreSQL extension for materialized view management and table fitness analysis.
|
|
|
|
## Overview
|
|
|
|
c77_mvc (Materialized View and table fitness utilities) provides database administrators and developers with tools to:
|
|
|
|
1. **Create optimized materialized views** with synthetic keys and content hashing
|
|
2. **Manage materialized view health** with automatic staleness detection and refresh
|
|
3. **Analyze table fitness** for partitioning, indexing, and query optimization
|
|
4. **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:
|
|
|
|
```sql
|
|
CREATE EXTENSION c77_dbh; -- Install dependency first
|
|
CREATE EXTENSION c77_mvc;
|
|
```
|
|
|
|
### From Source
|
|
|
|
```bash
|
|
# 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](INSTALLATION.md).
|
|
|
|
## Basic Usage
|
|
|
|
### Table Fitness Analysis
|
|
|
|
```sql
|
|
-- Analyze a table for fitness metrics
|
|
SELECT * FROM public.c77_mvc_analyze_table_fitness('schema_name', 'table_name');
|
|
```
|
|
|
|
### Creating Optimized Materialized Views
|
|
|
|
```sql
|
|
-- 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
|
|
|
|
```sql
|
|
-- 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](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
|
|
|
|
- [Installation Guide](INSTALLATION.md)
|
|
- [Usage Guide](USAGE.md)
|
|
- [Technical Assessment](TECHNICAL.md)
|
|
|
|
## Contributing
|
|
|
|
1. Fork the repository
|
|
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
|
|
3. Commit your changes (`git commit -m 'Add some amazing feature'`)
|
|
4. Push to the branch (`git push origin feature/amazing-feature`)
|
|
5. 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
|