← back to Watches

database/POSTGRESQL_MIGRATION_GUIDE.md

567 lines

# PostgreSQL Migration Guide - Omega Watches

## Enterprise Database Architecture

This migration transforms the Omega Watches system from JSON file storage to a production-grade PostgreSQL database with advanced features.

---

## Features Implemented

### 1. Schema Design
- ✅ **Normalized Relational Schema** - Proper 3NF normalization
- ✅ **Custom Domain Types** - Type safety with constraints
- ✅ **Enum Types** - Controlled vocabularies for data integrity
- ✅ **Table Partitioning** - Price history partitioned by year
- ✅ **Foreign Key Constraints** - Referential integrity

### 2. Full-Text Search
- ✅ **GIN Indices** - Fast full-text search using tsvector
- ✅ **Trigram Matching** - Fuzzy search with pg_trgm
- ✅ **Weighted Search** - Model/series ranked higher than descriptions
- ✅ **Auto-Updated Vectors** - Triggers maintain search indices

### 3. Performance Optimization
- ✅ **Materialized Views** - Pre-calculated statistics for fast queries
- ✅ **Comprehensive Indexing** - 30+ strategic indices
- ✅ **Partial Indices** - Space-efficient conditional indexing
- ✅ **Query Optimization** - Analyzed tables for planner

### 4. Connection Pooling
- ✅ **Advanced Pool Manager** - Primary/replica routing
- ✅ **Circuit Breaker Pattern** - Fault tolerance
- ✅ **Automatic Failover** - Falls back to primary if replica fails
- ✅ **Read/Write Splitting** - Optimizes query routing
- ✅ **Connection Limits** - Prevents resource exhaustion

### 5. Replication Support
- ✅ **Streaming Replication** - WAL-based replication
- ✅ **Read Replicas** - Horizontal read scaling
- ✅ **Setup Scripts** - Automated master/replica configuration
- ✅ **Monitoring Tools** - Replication lag tracking

### 6. Materialized Views
- ✅ **watch_statistics** - Pre-calculated price metrics
- ✅ **series_performance** - Series-level aggregations
- ✅ **decade_performance** - Historical trend analysis
- ✅ **Concurrent Refresh** - No downtime updates

### 7. ETL Pipeline
- ✅ **Full Data Migration** - JSON to PostgreSQL
- ✅ **Data Transformation** - Type conversion and validation
- ✅ **Data Quality Scoring** - Automatic quality assessment
- ✅ **Job Tracking** - ETL execution monitoring
- ✅ **Error Handling** - Graceful failure recovery
- ✅ **Batch Processing** - Efficient bulk operations

### 8. Audit & Monitoring
- ✅ **Audit Logging** - Complete change history
- ✅ **View Tracking** - User activity monitoring
- ✅ **Performance Metrics** - Query performance tracking
- ✅ **Health Checks** - Database health monitoring

---

## Architecture Overview

```
┌─────────────────────────────────────────────────────────────┐
│                     Application Layer                       │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐     │
│  │ Express API  │  │  ETL Pipeline │  │  Admin Tools │     │
│  └──────┬───────┘  └──────┬────────┘  └──────┬───────┘     │
│         │                 │                   │             │
└─────────┼─────────────────┼───────────────────┼─────────────┘
          │                 │                   │
┌─────────▼─────────────────▼───────────────────▼─────────────┐
│              Connection Pool Manager (PgBouncer)            │
│  ┌────────────────────┐          ┌────────────────────┐    │
│  │  Primary Pool      │          │  Replica Pool      │    │
│  │  • Max: 20 conns   │          │  • Max: 30 conns   │    │
│  │  • Write queries   │          │  • Read queries    │    │
│  │  • Transactions    │          │  • Analytics       │    │
│  └─────────┬──────────┘          └─────────┬──────────┘    │
└────────────┼─────────────────────────────────┼──────────────┘
             │                                 │
┌────────────▼─────────────┐    ┌──────────────▼──────────────┐
│   PRIMARY DATABASE       │    │   READ REPLICA (Optional)   │
│   (Master)               │◄───┤   (Standby)                 │
│                          │    │                             │
│   ┌──────────────────┐   │    │   ┌──────────────────┐     │
│   │  omega schema    │   │    │   │  omega schema    │     │
│   │  • watches       │   │    │   │  (read-only)     │     │
│   │  • price_history │   │    │   └──────────────────┘     │
│   │  • specifications│   │    │                             │
│   │  • features      │   │    │   Streaming Replication     │
│   │  • mat. views    │   │    │   (Async, WAL-based)        │
│   └──────────────────┘   │    └─────────────────────────────┘
└──────────────────────────┘
```

---

## Database Schema

### Core Tables

#### `omega.watches`
Main watch catalog with full-text search support.
```sql
CREATE TABLE omega.watches (
    id UUID PRIMARY KEY,
    slug VARCHAR(255) UNIQUE,
    model VARCHAR(255) NOT NULL,
    series watch_series_enum,
    reference VARCHAR(100),
    year_introduced watch_year,
    search_vector tsvector,  -- Auto-updated for full-text search
    ...
);
```

#### `omega.price_history` (Partitioned)
Price tracking with decade-based partitioning for scalability.
```sql
CREATE TABLE omega.price_history (
    id UUID,
    watch_id UUID REFERENCES watches(id),
    year watch_year,
    price positive_price,
    condition condition_enum,
    ...
    PRIMARY KEY (id, year)
) PARTITION BY RANGE (year);
```

#### `omega.specifications`
Technical details with extracted numeric values.
```sql
CREATE TABLE omega.specifications (
    watch_id UUID UNIQUE REFERENCES watches(id),
    case_diameter NUMERIC(5,2),  -- Extracted from case_size
    movement_type movement_type_enum,
    power_reserve_hours INTEGER,  -- Extracted from power_reserve
    ...
);
```

### Materialized Views

#### `omega.watch_statistics`
Pre-calculated metrics for every watch:
- Current price, original price
- Appreciation percentage
- CAGR (Compound Annual Growth Rate)
- Price volatility
- Min/max/median prices

**Refresh**: `SELECT omega.refresh_all_materialized_views();`

---

## Installation & Deployment

### Prerequisites
```bash
# PostgreSQL 14+
sudo apt install postgresql postgresql-contrib

# Required extensions
psql -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
psql -c "CREATE EXTENSION IF NOT EXISTS btree_gin;"
```

### Quick Start
```bash
cd /root/Projects/watches/database

# 1. Deploy database schema and run ETL
./deploy-database.sh

# 2. Update environment variables
nano ../.env.db
# Set DB_PASSWORD

# 3. Verify deployment
psql -d omega_watches -c "SELECT * FROM omega.get_system_stats();"
```

### Manual Migration Steps
```bash
# 1. Create database
sudo -u postgres psql -c "CREATE DATABASE omega_watches;"

# 2. Deploy schema
sudo -u postgres psql -d omega_watches -f enhanced-schema.sql

# 3. Run ETL pipeline
node etl-pipeline.js

# 4. Refresh materialized views
psql -d omega_watches -c "SELECT omega.refresh_all_materialized_views();"
```

---

## Replication Setup

### Master Configuration
```bash
# Setup primary server
./setup-replication.sh master

# Save the generated replication credentials
```

### Replica Configuration
```bash
# On replica server
export MASTER_HOST=your-master-ip
export REPLICATION_PASSWORD=generated-password
./setup-replication.sh replica
```

### Monitor Replication
```bash
# Real-time monitoring
monitor-replication

# Check replication lag
psql -c "SELECT NOW() - pg_last_xact_replay_timestamp() AS lag;"
```

---

## Usage Examples

### Application Integration

#### Initialize Pool Manager
```javascript
import { getPoolManager } from './database/pool-manager.js';
import WatchRepository from './database/repositories/watch-repository.js';

// Configure with replica support
const pool = getPoolManager({
    primary: {
        host: 'localhost',
        port: 5432,
        database: 'omega_watches',
        user: 'postgres',
        password: process.env.DB_PASSWORD,
        max: 20
    },
    replica: {
        host: 'replica-server',
        port: 5432,
        max: 30
    }
});

const watchRepo = new WatchRepository(pool);
```

#### Query Examples
```javascript
// Get all Speedmaster watches
const speedmasters = await watchRepo.findAll({
    series: 'Speedmaster',
    page: 1,
    limit: 20
});

// Full-text search
const results = await watchRepo.search('moonwatch professional');

// Get trending watches (last 7 days)
const trending = await watchRepo.getTrending(7, 10);

// Get watch with full details
const watch = await watchRepo.findBySlug('speedmaster-moonwatch-1957');

// Calculate appreciation
const appreciation = await watchRepo.calculateAppreciation(
    watchId,
    1969,  // start year
    2024   // end year
);

// Compare multiple watches
const comparison = await watchRepo.compareWatches([
    'watch-id-1',
    'watch-id-2',
    'watch-id-3'
]);
```

### Direct SQL Queries

#### Full-Text Search
```sql
-- Search watches by keyword
SELECT id, model, series, ts_rank(search_vector, query) as rank
FROM omega.watches,
     to_tsquery('english', 'speedmaster & moonwatch') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;
```

#### Top Performers
```sql
-- Get best performing watches
SELECT model, series, appreciation_percentage, cagr_percentage
FROM omega.watch_statistics
WHERE appreciation_percentage IS NOT NULL
ORDER BY appreciation_percentage DESC
LIMIT 20;
```

#### Price History Analysis
```sql
-- Get yearly average prices by series
SELECT
    EXTRACT(YEAR FROM year) as year,
    w.series,
    AVG(ph.price) as avg_price,
    COUNT(*) as sample_size
FROM omega.price_history ph
JOIN omega.watches w ON ph.watch_id = w.id
GROUP BY year, w.series
ORDER BY year DESC, avg_price DESC;
```

---

## Performance Tuning

### Index Optimization
```sql
-- Check index usage
SELECT * FROM omega.index_usage ORDER BY idx_scan DESC;

-- Find unused indices
SELECT * FROM omega.index_usage WHERE idx_scan = 0;

-- Rebuild indices
REINDEX DATABASE omega_watches;
```

### Query Performance
```sql
-- Enable query timing
\timing

-- Analyze slow queries
SELECT
    query,
    calls,
    total_time,
    mean_time,
    max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

-- Explain query plan
EXPLAIN ANALYZE
SELECT * FROM omega.watches
WHERE series = 'Speedmaster';
```

### Maintenance
```sql
-- Vacuum and analyze
VACUUM ANALYZE omega.watches;
VACUUM ANALYZE omega.price_history;

-- Update statistics
ANALYZE;

-- Refresh materialized views
SELECT omega.refresh_all_materialized_views();
```

---

## Backup & Recovery

### Automated Backups
```bash
# Run backup script (installed by deployment)
sudo /usr/local/bin/backup-omega-db

# Setup cron job for daily backups
echo "0 2 * * * /usr/local/bin/backup-omega-db" | crontab -
```

### Manual Backup
```bash
# Full database dump
pg_dump -U postgres -Fc omega_watches > omega_backup.dump

# Schema only
pg_dump -U postgres -s omega_watches > schema.sql

# Data only
pg_dump -U postgres -a omega_watches > data.sql
```

### Restore
```bash
# Restore from dump
pg_restore -U postgres -d omega_watches omega_backup.dump

# Restore from SQL
psql -U postgres -d omega_watches -f schema.sql
```

---

## Monitoring & Alerts

### Health Check
```javascript
// Application health check
const health = await pool.healthCheck();
console.log(health);

// Database stats
const stats = await watchRepo.getDatabaseStats();
```

### Connection Pool Stats
```javascript
const stats = pool.getStats();
console.log('Primary pool:', stats.primary);
console.log('Replica pool:', stats.replica);
console.log('Metrics:', stats.metrics);
```

### Database Metrics
```sql
-- System statistics
SELECT * FROM omega.get_system_stats();

-- Table sizes
SELECT * FROM omega.database_stats;

-- Active connections
SELECT * FROM pg_stat_activity WHERE datname = 'omega_watches';

-- Cache hit ratio (should be >90%)
SELECT
    SUM(blks_hit) * 100.0 / SUM(blks_hit + blks_read) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = 'omega_watches';
```

---

## Migration Checklist

- [ ] PostgreSQL 14+ installed
- [ ] Database created with UTF8 encoding
- [ ] Schema deployed (`enhanced-schema.sql`)
- [ ] ETL pipeline executed successfully
- [ ] Materialized views refreshed
- [ ] Indices created and analyzed
- [ ] Connection pool configured
- [ ] Environment variables set
- [ ] Backup script scheduled
- [ ] Monitoring enabled
- [ ] Replication configured (optional)
- [ ] Application updated to use PostgreSQL
- [ ] Performance tested
- [ ] JSON files backed up (for rollback)

---

## Troubleshooting

### Common Issues

#### Connection Refused
```bash
# Check PostgreSQL is running
sudo systemctl status postgresql

# Check listen addresses
sudo grep listen_addresses /etc/postgresql/14/main/postgresql.conf

# Should be: listen_addresses = '*'
```

#### Slow Queries
```sql
-- Enable slow query logging
ALTER DATABASE omega_watches SET log_min_duration_statement = 1000;

-- Check for missing indices
SELECT * FROM pg_stat_user_tables WHERE idx_scan = 0;
```

#### Replication Lag
```sql
-- Check lag on replica
SELECT NOW() - pg_last_xact_replay_timestamp() AS lag;

-- Check replication status on master
SELECT * FROM pg_stat_replication;
```

#### Out of Connections
```javascript
// Increase pool size
const pool = getPoolManager({
    primary: { max: 50 },  // Increase from 20
    replica: { max: 100 }  // Increase from 30
});
```

---

## Performance Benchmarks

### Expected Performance
- **Simple SELECT**: < 5ms
- **Complex JOIN**: < 50ms
- **Full-text search**: < 100ms
- **Materialized view refresh**: < 2s
- **ETL full reload**: < 30s (32 watches)

### Optimization Tips
1. Use materialized views for analytics queries
2. Route read queries to replica
3. Enable connection pooling
4. Keep statistics up-to-date
5. Monitor slow query log

---

## Future Enhancements

- [ ] PgBouncer integration for connection pooling
- [ ] Redis caching layer
- [ ] TimescaleDB for time-series analytics
- [ ] GraphQL API layer
- [ ] Real-time notifications (LISTEN/NOTIFY)
- [ ] Multi-region replication
- [ ] Automated failover with Patroni
- [ ] Column-level encryption

---

## Support & Resources

- **PostgreSQL Documentation**: https://www.postgresql.org/docs/
- **pg_trgm**: https://www.postgresql.org/docs/current/pgtrgm.html
- **Partitioning**: https://www.postgresql.org/docs/current/ddl-partitioning.html
- **Replication**: https://www.postgresql.org/docs/current/warm-standby.html

---

**Version**: 2.0
**Last Updated**: 2025-11-17
**Author**: Database Architect