🐘 PostgreSQL Lab: Real-World Database Demo

PostgreSQL demo focused on clean schema design, real-world queries, and performance-aware patterns.

A developer-ready PostgreSQL lab showcasing advanced SQL techniques, schema design best practices, and performance optimization strategies.

🚀 Quick Start

# Clone and run in 3 commands
git clone https://github.com/ethauber/postgresql-lab
cd postgresql-lab
docker compose up --build -d

# After the image is built
# 1. Start the containers (if already built)
make start

# 2. Start the API server
make api

🏷️ Tags

#postgres #sql #backend #docker #fastapi #database

📊 Demo Features

🏗️ Schema Design

• 3 interconnected tables: users, products, orders with proper foreign keys
• Realistic relationships: Users place orders for products with pricing history
• Performance indexes: Strategic indexing for common query patterns

📈 Advanced Queries

• CTEs (Common Table Expressions): Modular subqueries for composable, readable analytics logic
  • CTEs are essentially temporary named result sets that exist only within the scope of a single query and defined using the WITH clause
  • 🔗 Official PostgreSQL docs on CTEs
• Window Functions: Ranking, running totals, and time-based analysis
  • Window function in postgresql is a set of rows within a query result and is achieved by using any of the following functions:
    • ROW_NUMBER()
    • RANK()
    • DENSE_RANK()
    • PERCENT_RANK()
    • CUME_DIST()
    • NTILE()
    • FIRST_VALUE()
    • LAST_VALUE()
    • LEAD()
    • LAG()
    • SUM() (When used with OVER clause, it calculates the sum of the values in the window)
  • 🔗 Official PostgreSQL docs on Window Functions
• JOINs: Multiple table relationships with optimization insights
• Materialized Views: Performance optimization for heavy analytics
  • A materialized view is a pre-computed result of a query that is stored in a table and is updated automatically when the underlying data changes.
  • 🔗 Official PostgreSQL docs on Materialized Views

🔧 Developer Experience

• Docker setup: One-command environment
• Seed data: Realistic sample data with 1000+ records
• API endpoints: RESTful interface with Swagger documentation
• Query examples: Production-ready analytics queries

🗄️ Database Schema

users (1)
  │
  └──< orders (N) >──┐
                     │
              products (N)

users (id, name, email, created_at)
    ↓
orders (id, user_id, product_id, quantity, price, order_date)
    ↓
products (id, name, category, base_price, created_at)

Key Relationships

• Users can place multiple orders
• Products have pricing history through orders
• Strategic indexes on user_id, product_id, and order_date

📊 Sample Queries

1. Top Customers by Revenue (Subquery + Window Functions)

-- Ranks users by total spending with running totals
SELECT 
    u.name,
    total_spent,
    RANK() OVER (ORDER BY total_spent DESC) as customer_rank,
    SUM(total_spent) OVER (ORDER BY total_spent DESC) as running_total
FROM (
    SELECT user_id, SUM(quantity * price) as total_spent
    FROM orders 
    GROUP BY user_id
) user_totals
JOIN users u ON u.id = user_totals.user_id;

2. Product Performance Analysis (CTE)

-- Analyzes product performance with multiple metrics
WITH product_stats AS (
    SELECT 
        p.name,
        COUNT(o.id) as order_count,
        SUM(o.quantity) as total_quantity,
        AVG(o.price) as avg_price,
        SUM(o.quantity * o.price) as total_revenue
    FROM products p
    LEFT JOIN orders o ON p.id = o.product_id
    GROUP BY p.id, p.name
)
SELECT 
    name,
    order_count,
    total_quantity,
    ROUND(avg_price::numeric, 2) as avg_price,
    ROUND(total_revenue::numeric, 2) as total_revenue,
    CASE 
        WHEN total_revenue > 10000 THEN 'High Performer'
        WHEN total_revenue > 5000 THEN 'Medium Performer'
        ELSE 'Low Performer'
    END as performance_category
FROM product_stats
ORDER BY total_revenue DESC;

🚀 Performance Highlights

Index Optimization

• Performance improvement on user order queries with composite index on (user_id, order_date)
• Materialized view for monthly revenue reports reduces query time from 2.3s to 0.1s
• Strategic indexing on foreign keys and date columns

Query Optimization

• CTEs improve readability and maintainability of complex analytics
• Window functions enable efficient ranking and running calculations
• Proper JOIN strategies minimize data transfer and execution time

🛠️ API Endpoints

The demo includes a FastAPI backend with these endpoints:

• GET /users - List all users with order counts
• GET /products - Product catalog with performance metrics
• GET /analytics/top-customers - Top customers by revenue
• GET /analytics/product-performance - Product performance analysis
• GET /docs - Interactive Swagger documentation

📁 Project Structure

postgres-lab/
|- api/
│   |- Dockerfile         # API container configuration
│   |- main.py            # FastAPI application
│   |- requirements.txt   # Python dependencies
|- queries/
│   |- analytics.sql       # Advanced analytics queries
│   |- reporting.sql       # Reporting and views
|- docker-compose.yml     # Development environment
|- Makefile               # Makefile for convenience
|- README.md              # This file
|- schema.sql             # Table definitions and indexes
|- seed.sql               # Sample data generation

🎯 Why This Matters

✅ Demonstrates relational modeling - Clean, normalized schema with proper relationships
✅ Shows query fluency - Real-world analytics patterns used in production
✅ Built with optimization in mind - Indexes, views, and performance considerations
✅ API ready for extension - RESTful interface for frontend integration
✅ Developer-friendly - Docker setup, clear documentation, and examples

🔧 Development

• Docker version 28.3.0, build 38b7060
• GNU Make 3.81

Local Development

# Start all services (database, pgAdmin, API)
docker compose up -d

# Or use the Makefile
make setup

# Run queries
docker compose exec postgres psql -U postgres -d demo -f queries/analytics.sql

# Start the API (if not already running)
make api

Database Connection

• Host: localhost
• Port: 5432
• Database: demo
• Username: postgres
• Password: password

📈 Performance Metrics

Query Type	Measured Performance	Optimization Impact
User Orders Query	9.4ms execution time	Uses idx_orders_date index for date filtering
Materialized View	0.06ms execution time	156x faster than complex aggregation
Index Usage	74,452 tuples read via indexes	Efficient data access patterns

🔍 Verify Performance Yourself

Run these commands to see actual performance metrics:

# Test query performance with EXPLAIN ANALYZE
make perftest

# Check index usage statistics
make performance

# Run complex analytics queries
make queries

Real Performance Output (Docker Environment):

Environment: PostgreSQL 15 on Docker (2GB RAM, 2 CPU cores)
Dataset: 58 users, 32 products, 36,800 orders

=== User Orders Query Performance ===
Planning Time: 1.038 ms
Execution Time: 9.357 ms
Index Scan: idx_orders_date (7,452 rows)
Buffers: shared hit=338 (100% cache hit ratio)

=== Materialized View Performance ===
Planning Time: 0.800 ms
Execution Time: 0.060 ms
Seq Scan: 24 rows from pre-computed view
Buffers: shared hit=1 (100% cache hit ratio)

=== Index Usage Statistics ===

index_name	scans	tuples_read
users_pkey	36801	36801
products_pkey	36800	36800
idx_orders_date	1	7452

---

Written to demonstrate PostgreSQL skills