c77_dep

PostgreSQL Database Dependency Mapping Extension

← Back to Portfolio

c77_dep - PostgreSQL Database Dependency Mapping Extension

The c77_dep extension provides a comprehensive suite of tools for analyzing, visualizing, and managing database object dependencies in PostgreSQL databases. It helps database administrators and developers understand complex dependency relationships between tables, views, materialized views, and foreign tables.

PostgreSQL 10+ PostgreSQL License Version 1.0.0

📊 Overview

Managing dependencies in complex PostgreSQL databases can be challenging. When making schema changes, it's crucial to understand how objects are interconnected to prevent breaking changes. This extension provides views and functions to map these relationships and make informed decisions during database maintenance, migrations, and refactoring.

✨ Features

🗺️ Dependency Mapping

Core views that analyze and expose database object dependencies

💥 Impact Analysis

Tools to predict the impact of dropping objects or changing schemas

🔄 Circular Dependency Detection

Identification of problematic circular dependencies

⚠️ Risk Assessment

Scoring of objects based on their dependency characteristics

📋 Migration Planning

Functions to help determine the correct order for migrations

📈 Visualization

Export dependency graphs in DOT format for Graphviz visualization

📄 Reporting

Generate comprehensive dependency analysis reports

🔍 Core Views

  • c77_dep_dependencies_map: Base dependency mapping view for tables, views, and materialized views
  • c77_dep_dependencies_map_with_foreign: Enhanced view that also includes foreign tables

📊 Analysis Views

  • c77_dep_orphaned_objects: Objects with no dependencies
  • c77_dep_schema_complexity: Schema complexity metrics
  • c77_dep_object_type_summary: Summary of object types and dependencies
  • c77_dep_risk_assessment: Risk assessment for schema changes
  • c77_dep_hub_objects: Objects with many dependencies
  • c77_dep_cleanup_candidates: Objects that might need cleanup

🔧 Analysis Functions

💡 Examples

Finding orphaned objects in a schema

SELECT * FROM c77_dep_orphaned_objects 
WHERE relation LIKE 'myschema.%';

Analyzing the impact of dropping a table

SELECT * FROM c77_dep_analyze_drop_impact('myschema.mytable');

Generating a dependency graph for visualization

SELECT c77_dep_export_dependency_graph('myschema');

Save the output to a file with a .dot extension and visualize it with Graphviz:

dot -Tpng dependencies.dot -o dependencies.png

Generate a comprehensive dependency report

SELECT c77_dep_generate_report();

Find circular dependencies

SELECT * FROM c77_dep_detect_circular_dependencies();

Determine migration order for a schema

SELECT * FROM c77_dep_generate_migration_order()
WHERE objects_to_migrate @> ARRAY['myschema.%'];

📈 Graphviz Visualization Example

The extension can export dependency graphs in DOT format for visualization with Graphviz. Here's a workflow example:

-- Export dependency graph
\o dependencies.dot
SELECT c77_dep_export_dependency_graph('public', 3, true);
\o

-- Generate visualization (run in shell)
dot -Tpng dependencies.dot -o dependencies.png
dot -Tsvg dependencies.dot -o dependencies.svg

This creates visual dependency maps that help you understand complex relationships at a glance.

⚡ Performance Considerations

⚠️ Performance Notice

The dependency analysis can be resource-intensive on large databases. Consider:

  • Running analyses during off-peak hours
  • Filtering by schema to limit analysis scope
  • Using functions that target specific objects rather than analyzing the entire database

Best Practices

🚀 Migration Planning Workflow

Here's a typical workflow for planning database migrations:

-- 1. Generate comprehensive report
SELECT c77_dep_generate_report('public', true, true);

-- 2. Identify circular dependencies
SELECT * FROM c77_dep_detect_circular_dependencies();

-- 3. Assess risk for specific changes
SELECT * FROM c77_dep_risk_assessment WHERE relation LIKE '%target_table%';

-- 4. Plan migration order
SELECT * FROM c77_dep_generate_migration_order();

-- 5. Simulate schema changes
SELECT c77_dep_simulate_schema_change('old_schema', 'new_schema');

🛠️ Installation

Prerequisites

Installing the Extension

# Copy the extension files to your PostgreSQL extension directory
cp c77_dep.control c77_dep--1.0.0.sql /path/to/postgresql/share/extension/

# Connect to your database and create the extension
psql -d your_database
CREATE EXTENSION c77_dep;

🔍 Limitations

⚠️ Current Limitations

  • The extension focuses primarily on structural dependencies and may not capture all logical dependencies
  • Function dependencies are not included to avoid excessive resource usage
  • Very large databases may require schema-specific analysis rather than database-wide analysis

🗂️ Use Cases

Database Refactoring

Understand impact before making structural changes to avoid breaking dependent objects

Migration Planning

Determine correct order for migrating objects between environments

Technical Debt Analysis

Identify orphaned objects and cleanup candidates to reduce complexity

Impact Assessment

Evaluate risks before dropping or modifying database objects

Documentation

Generate visual maps and reports for database architecture documentation

Compliance

Track dependencies for regulatory requirements and change management

Smart dependency management for PostgreSQL!

This extension helps you make informed decisions about database changes by providing comprehensive dependency analysis and visualization tools.

View Git Repository →