A comprehensive database administration project demonstrating Docker containerization, PostgreSQL setup, schema design, and database performance optimization using Model Context Protocol (MCP) tools.
概览
A comprehensive database administration project demonstrating Docker containerization, PostgreSQL setup, schema design, and database performance optimization using Model Context Protocol (MCP) tools. This project showcases modern database administration practices through a production-ready PostgreSQL setup. It demonstrates: - : Docker-based PostgreSQL 16 deployment - : Normalized e-commerce database with 4 core tables - : Automated database auditing and index recommendations - : Cache hit rate analysis and query performance metrics - : Database operations using Model Context Protocol tools - 🔧 : Isolated PostgreSQL 16 environment - 📊 : Automated health checks and performance analysis - 🔍 : Identifies missing indexes on foreign keys - 📈 : Cache hit rate monitoring and query analysis - 🛠️ : Python-based database management utilities - 📝 : Schema docs, quick start guides, and portfolio resources The project implements a normalized e-commerce database with the following...
README
以下为原文 README 节选，完整内容请查看原文。
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
| Metric | Value |
|---|---|
| Total Tables | 4 |
| Total Records | ~43,000 rows |
| Database Size | ~3.5 MB |
| Cache Hit Rate | 99.94% |
| Customers | 2,000 |
| Orders | 10,000 |
| Order Items | 30,000 |
| Products | 1,000 |
✨ 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
customers- Customer information with registration details, country, and tierorders- Order records with status, amounts, and regional dataproducts- Product catalog with categories, pricing, and stock levelsorder_items- Junction table linking orders to products with quantities
安装
docker run -d \