PostgreSQL Database Administration with MCP

A comprehensive database administration project demonstrating Docker containerization, PostgreSQL setup, schema design, and database performance optimization using Model Context Protocol (MCP) tools.

📋 Table of Contents

• Project Overview
• Features
• Architecture
• Database Schema
• Technologies Used
• Prerequisites
• Installation & Setup
• Usage
• Performance Optimization
• Database Audit Results
• Project Structure
• Key Learnings
• Documentation
• Contributing
• License

🎯 Project Overview

This project showcases modern database administration practices through a production-ready PostgreSQL setup. It demonstrates:

• Containerized Database Infrastructure: Docker-based PostgreSQL 16 deployment
• Schema Design: Normalized e-commerce database with 4 core tables
• Performance Optimization: Automated database auditing and index recommendations
• Health Monitoring: Cache hit rate analysis and query performance metrics
• MCP Integration: Database operations using Model Context Protocol tools

Database Statistics

✨ Features

• 🔧 Docker Containerization: Isolated PostgreSQL 16 environment
• 📊 Database Auditing: Automated health checks and performance analysis
• 🔍 Index Optimization: Identifies missing indexes on foreign keys
• 📈 Performance Metrics: Cache hit rate monitoring and query analysis
• 🛠️ Automated Scripts: Python-based database management utilities
• 📝 Comprehensive Documentation: Schema docs, quick start guides, and portfolio resources

🏗️ Architecture

Database Schema

The project implements a normalized e-commerce database with the following structure:

Core Tables

1. customers - Customer information with registration details, country, and tier
2. orders - Order records with status, amounts, and regional data
3. products - Product catalog with categories, pricing, and stock levels
4. order_items - Junction table linking orders to products with quantities

Entity Relationships

customers (1) ────< (many) orders (1) ────< (many) order_items
                                                        │
                                                        │ (many)
                                                        │
                                              products (1)

• customers → orders (one-to-many)
• orders → order_items (one-to-many)
• products → order_items (one-to-many)

System Architecture

┌─────────────────┐
│   Python Script │
│    (main.py)    │
└────────┬────────┘
         │
         │ Docker Exec
         ▼
┌─────────────────┐
│  Docker Container│
│   (pg-local)    │
│                 │
│  PostgreSQL 16  │
│  Port: 5433     │
└─────────────────┘

📊 Database Schema

Entity Relationship Diagram

erDiagram
    CUSTOMERS ||--o{ ORDERS : "places"
    ORDERS ||--o{ ORDER_ITEMS : "contains"
    PRODUCTS ||--o{ ORDER_ITEMS : "included_in"
    
    CUSTOMERS {
        int customer_id PK
        varchar email
        date registration_date
        varchar country
        varchar tier
    }
    
    ORDERS {
        int order_id PK
        int customer_id FK
        date order_date
        varchar status
        numeric total_amount
        varchar region
    }
    
    PRODUCTS {
        int product_id PK
        varchar name
        varchar category
        numeric price
        int stock_quantity
    }
    
    ORDER_ITEMS {
        int item_id PK
        int order_id FK
        int product_id FK
        int quantity
        numeric price
    }

Table Details

For detailed table schemas, column definitions, and relationships, see docs/database_schema.md.

🛠️ Technologies Used

• PostgreSQL 16: Enterprise-grade relational database management system
• Docker: Containerization platform for isolated database environments
• MCP (Model Context Protocol): Database administration and operation tools
• Python 3.12+: Scripting language for automation and database management
• SQL: Database query language for schema design and optimization

📦 Prerequisites

Before you begin, ensure you have the following installed:

• Docker Desktop (or Docker Engine) - Install Docker
• Python 3.12 or higher - Install Python
• MCP Server configured (Docker & PostgreSQL MCPs) - Optional but recommended
• Git - For cloning the repository

🚀 Installation & Setup

Step 1: Clone the Repository

git clone 
cd MCP-data-series

Step 2: Create PostgreSQL Container

Start a PostgreSQL 16 container with the following configuration:

docker run -d \
  --name pg-local \
  -p 5433:5432 \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=app \
  -e POSTGRES_DB=demo \
  postgres:16

Note: The container uses port 5433 to avoid conflicts with local PostgreSQL installations on port 5432.

Step 3: Verify Container is Running

docker ps | grep pg-local

You should see the container running. If not, check logs:

docker logs pg-local

Step 4: Create Schema and Set Permissions

Connect to the database and create the schema:

docker exec -i pg-local psql -U app -d demo << EOF
-- Grant permissions
GRANT ALL PRIVILEGES ON DATABASE demo TO app;

-- Create schema
CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION app;
EOF

Step 5: Load Sample Data

Load the sample database with schema and data:

docker exec -i pg-local psql -U app -d demo < demo_data.sql

This will create all tables and populate them with ~43,000 sample records.

Step 6: Verify Installation

Run the database audit script to verify everything is working:

python main.py

You should see output showing tables, statistics, cache performance, and index audit results.

💻 Usage

Running Database Operations

Execute the main script to perform a comprehensive database audit:

python main.py

Expected Output:

============================================================
PostgreSQL Database Management - Portfolio Project
============================================================

📊 Database Tables:
------------------------------------------------------------
  ✓ customers
  ✓ order_items
  ✓ orders
  ✓ products

📈 Table Statistics:
------------------------------------------------------------
[Table statistics with row counts and sizes]

💾 Cache Performance:
------------------------------------------------------------
  Cache Hit Rate: 99.94% ✅ Excellent

🔍 Index Audit:
------------------------------------------------------------
  Missing indexes on foreign keys:
    ⚠️  orders.customer_id
    ⚠️  order_items.order_id
    ⚠️  order_items.product_id

🚀 Optimization Recommendations:
------------------------------------------------------------
  CREATE INDEX idx_orders_customer_id ON app.orders(customer_id);
  CREATE INDEX idx_order_items_order_id ON app.order_items(order_id);
  CREATE INDEX idx_order_items_product_id ON app.order_items(product_id);

Connecting to the Database

Connect directly to the PostgreSQL database:

docker exec -i pg-local psql -U app -d demo

Using the PostgreSQLManager Class

The PostgreSQLManager class provides several methods for database operations:

from main import PostgreSQLManager

# Initialize manager
db = PostgreSQLManager()

# List all tables
tables = db.list_tables()
print(tables)

# Get table statistics
stats = db.get_table_stats()
print(stats)

# Check cache hit rate
cache_rate = db.get_cache_hit_rate()
print(f"Cache Hit Rate: {cache_rate}%")

# Check for missing indexes
missing = db.check_indexes()
print(missing)

# Generate optimization SQL
sql_statements = db.generate_optimization_sql()
for sql in sql_statements:
    print(sql)

Sample Queries

Join Customers and Orders

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    c.email,
    c.country,
    c.tier
FROM app.orders o
JOIN app.customers c ON o.customer_id = c.customer_id
LIMIT 100;

Get Order Details with Products

SELECT 
    oi.item_id,
    o.order_date,
    p.name as product_name,
    p.category,
    oi.quantity,
    oi.price
FROM app.order_items oi
JOIN app.orders o ON oi.order_id = o.order_id
JOIN app.products p ON oi.product_id = p.product_id
WHERE o.order_date > '2025-01-01'
LIMIT 100;

📈 Performance Optimization

Identified Issues

The database audit identified three critical performance bottlenecks:

1. Missing Index on orders.customer_id: Causes sequential scans on 10K row table
2. Missing Index on order_items.order_id: Sequential scans on 30K row table
3. Missing Index on order_items.product_id: Performance degradation on joins

Optimization Queries

Apply the recommended indexes to improve performance:

-- Create foreign key indexes
CREATE INDEX idx_orders_customer_id ON app.orders(customer_id);
CREATE INDEX idx_order_items_order_id ON app.order_items(order_id);
CREATE INDEX idx_order_items_product_id ON app.order_items(product_id);

-- Update statistics for query planner
ANALYZE app.orders;
ANALYZE app.order_items;

Expected Performance Improvements

Verifying Optimization

After creating indexes, verify the improvements:

-- Check index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans
FROM pg_stat_user_indexes
WHERE schemaname = 'app'
ORDER BY idx_scan DESC;

🔍 Database Audit Results

Health Check Metrics

• Cache Hit Rate: 99.94% (Excellent - target: >99%)
• Query Execution Time: 0.448ms (Good performance)
• Database Size: ~3.5 MB (Efficient storage)
• Index Coverage: 3 missing indexes identified

📁 Project Structure

MCP-data-series/
├── README.md                    # This file - comprehensive project documentation
├── QUICK_START.md              # Quick setup guide
├── PORTFOLIO_GUIDE.md          # Portfolio presentation guide
├── main.py                     # Database operations and audit script
├── pyproject.toml              # Python project configuration
├── demo_data.sql               # Database schema and sample data (43K+ records)
├── .gitignore                  # Git ignore patterns
├── .python-version             # Python version specification
└── docs/                       # Additional documentation
    ├── database_schema.md      # Detailed schema documentation
    └── portfolio-presentation.md # Portfolio presentation tips

🎓 Key Learnings & Skills Demonstrated

This project demonstrates proficiency in:

1. Container Orchestration

   • Docker container lifecycle management
   • Port mapping and configuration
   • Container networking and isolation

2. Database Design

   • Normalized schema design for e-commerce systems
   • Foreign key relationships and referential integrity
   • Data modeling best practices

3. Performance Tuning

   • Query analysis using EXPLAIN ANALYZE
   • Index optimization strategies
   • Cache monitoring and optimization
   • Execution plan analysis

4. Database Administration

   • User and permission management
   • Schema creation and organization
   • Database health monitoring
   • Automated auditing scripts

5. MCP Integration

   • Using Model Context Protocol for database operations
   • Automated database management workflows

6. Query Optimization

   • Identifying performance bottlenecks
   • Creating strategic indexes
   • Analyzing query execution plans
   • Measuring performance improvements

7. Database Health Monitoring

   • Cache hit rate analysis
   • Index coverage auditing
   • Query performance metrics
   • Automated health checks

📚 Documentation

• QUICK_START.md: Get started in 5 minutes
• PORTFOLIO_GUIDE.md: How to present this project in your portfolio
• docs/database_schema.md: Detailed database schema documentation
• docs/portfolio-presentation.md: Portfolio presentation tips

🐛 Troubleshooting

Port 5432 Already in Use

The container is configured to use port 5433 by default. If you need to use a different port:

docker run -d \
  --name pg-local \
  -p :5432 \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=app \
  -e POSTGRES_DB=demo \
  postgres:16

Container Already Exists

If the container name conflicts:

docker rm -f pg-local

Then recreate the container using the setup instructions.

Cannot Connect to Database

1. Check container status:

   docker ps -a | grep pg-local
   

2. Check container logs:

   docker logs pg-local
   

3. Restart container if needed:

   docker start pg-local
   

Permission Denied Errors

Ensure the app user has proper permissions:

docker exec -i pg-local psql -U app -d demo -c "GRANT ALL PRIVILEGES ON SCHEMA app TO app;"

🤝 Contributing

This is a portfolio project demonstrating database administration skills. Contributions, suggestions, and improvements are welcome!

1. Fork the repository
2. Create a 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 MIT License - see the LICENSE file for details.

Feel free to use this as a learning resource!

🙏 Acknowledgments

• PostgreSQL Community - For excellent documentation and tools
• Docker - For the containerization platform
• MCP Protocol - For database administration tools
• Python Community - For robust database libraries

---

🎯 Project Highlights

Built as part of a portfolio project demonstrating:

• ✅ Real-world database administration skills
• ✅ Production-ready PostgreSQL setup
• ✅ Performance optimization expertise
• ✅ Container orchestration proficiency
• ✅ Automated database management
• ✅ Comprehensive documentation

Key Metrics:

• 📊 43,000+ records managed
• ⚡ 99.94% cache hit rate
• 🚀 40-60% performance improvements identified
• 🔧 3 critical optimizations recommended

---

For quick setup, see QUICK_START.md
For portfolio presentation, see PORTFOLIO_GUIDE.md