Postgresql Database Engineering

# PostgreSQL Database Engineering Examples

Comprehensive real-world examples covering indexing, partitioning, replication, performance optimization, and production database management.

## Table of Contents

1. [Index Optimization Examples](#index-optimization-examples)
2. [Query Performance Analysis](#query-performance-analysis)
3. [Table Partitioning](#table-partitioning)
4. [Streaming Replication Setup](#streaming-replication-setup)
5. [Connection Pooling with pgBouncer](#connection-pooling-with-pgbouncer)
6. [Backup and Recovery](#backup-and-recovery)
7. [Database Migration Strategies](#database-migration-strategies)
8. [Monitoring with pg_stat Views](#monitoring-with-pg_stat-views)
9. [VACUUM and Maintenance](#vacuum-and-maintenance)
10. [JSON and JSONB Optimization](#json-and-jsonb-optimization)
11. [Full-Text Search Implementation](#full-text-search-implementation)
12. [High Availability with Patroni](#high-availability-with-patroni)
13. [Logical Replication for Multi-Region](#logical-replication-for-multi-region)
14. [Performance Tuning for OLTP](#performance-tuning-for-oltp)
15. [Advanced Partitioning Strategies](#advanced-partitioning-strategies)
16. [Connection Pool Optimization](#connection-pool-optimization)
17. [Query Optimization Workshop](#query-optimization-workshop)
18. [Production Incident Resolution](#production-incident-resolution)

---

## 1. Index Optimization Examples

### Example 1.1: E-Commerce Product Search Optimization

**Scenario:** E-commerce site with slow product searches on multiple criteria.

**Initial Table:**
```sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    category_id INTEGER NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    stock_quantity INTEGER NOT NULL,
    brand TEXT,
    tags TEXT[],
    attributes JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Initial data: 5 million products
INSERT INTO products (name, description, category_id, price, stock_quantity, brand, tags, attributes)
SELECT
    'Product ' || i,
    'Description for product ' || i,
    (i % 100) + 1,
    random() * 1000,
    (random() * 100)::INTEGER,
    'Brand ' || ((i % 50) + 1),
    ARRAY['tag' || ((i % 20) + 1), 'tag' || ((i % 30) + 1)],
    jsonb_build_object('color', CASE (i % 5) WHEN 0 THEN 'red' WHEN 1 THEN 'blue' ELSE 'green' END)
FROM generate_series(1, 5000000) i;
```

**Problem Query (Slow):**
```sql
-- Query: Find products by category, price range, and in stock
EXPLAIN ANALYZE
SELECT id, name, price, stock_quantity
FROM products
WHERE category_id = 42
  AND price BETWEEN 100 AND 500
  AND stock_quantity > 0
ORDER BY created_at DESC
LIMIT 20;

-- Result: Seq Scan on products (cost=0.00..180000.00 rows=1000 width=50)
--         Execution time: 3500.234 ms
```

**Optimization Strategy:**

**Step 1: Analyze Query Pattern**
```sql
-- Check selectivity of each condition
SELECT COUNT(*) FROM products WHERE category_id = 42;
-- Result: 50,000 rows (1% of table)

SELECT COUNT(*) FROM products WHERE price BETWEEN 100 AND 500;
-- Result: 2,000,000 rows (40% of table)

SELECT COUNT(*) FROM products WHERE stock_quantity > 0;
-- Result: 4,500,000 rows (90% of table)

-- Most selective: category_id
-- Secondary: price range
-- Least selective: stock_quantity
```

**Step 2: Create Composite Index**
```sql
-- Composite index: Most selective column first, range column last
CREATE INDEX CONCURRENTLY idx_products_category_price_stock
ON products (category_id, price, stock_quantity)
WHERE stock_quantity > 0;  -- Partial index to reduce size

-- Analyze the table after index creation
ANALYZE products;
```

**Step 3: Verify Improvement**
```sql
EXPLAIN ANALYZE
SELECT id, name, price, stock_quantity
FROM products
WHERE category_id = 42
  AND price BETWEEN 100 AND 500
  AND stock_quantity > 0
ORDER BY created_at DESC
LIMIT 20;

-- Result: Index Scan using idx_products_category_price_stock
--         (cost=0.43..850