Skip to content

SCALABLE_ARCHITECTURE.md

Latest commit

 

History

History
453 lines (350 loc) · 10.9 KB

SCALABLE_ARCHITECTURE.md

File metadata and controls

453 lines (350 loc) · 10.9 KB
Raw

Scalable Database Architecture Documentation

Overview

This document describes the scalable PostgreSQL architecture implemented to handle 1 million users for the Vesting Vault platform. The architecture uses read replicas and load balancing to ensure high availability and performance.

Architecture Components

1. Database Cluster

Master (Primary) Database

  • Purpose: Handles all write operations (INSERT, UPDATE, DELETE)
  • Configuration: PostgreSQL 15 with replication enabled
  • Port: 5432
  • Features:
    • WAL (Write-Ahead Logging) for replication
    • Optimized for write performance
    • Automatic backup and point-in-time recovery

Read Replicas

  • Purpose: Handles all read operations (SELECT queries)
  • Count: 2 replicas (configurable)
  • Ports: 5433, 5434
  • Features:
    • Hot standby for real-time replication
    • Read-only operations only
    • Automatic failover support

2. Connection Pooling (PgBouncer)

Write Pool

  • Purpose: Manages connections to the master database
  • Port: 6432
  • Pool Size: 20 connections
  • Mode: Transaction pooling

Read Pool

  • Purpose: Manages connections to read replicas
  • Port: 6433
  • Pool Size: 40 connections
  • Mode: Transaction pooling
  • Load Balancing: Round-robin between healthy replicas

3. Application Layer

Database Router

  • Purpose: Automatically routes queries to appropriate database
  • Features:
    • Read/write splitting based on operation type
    • Automatic failover to master for critical reads
    • Replica lag monitoring

Failover Service

  • Purpose: Handles replica failures and recovery
  • Features:
    • Health monitoring of all replicas
    • Automatic exclusion of failed replicas
    • Recovery detection and reintegration

4. Health Monitoring

Database Health Monitor

  • Purpose: Continuous monitoring of database cluster health
  • Port: 3001
  • Endpoints:
    • /health - Current health status
    • /metrics - Prometheus-style metrics
    • /health/cached - Cached health status

Deployment Guide

Prerequisites

  • Docker and Docker Compose
  • Sufficient disk space for databases (recommended: 100GB+)
  • Network bandwidth for replication traffic

Quick Start

  1. Environment Setup

    cp .env.staging .env.local
# Edit .env.local with your configuration

  2. Deploy the Cluster

    chmod +x scripts/deploy-scalable.sh
./scripts/deploy-scalable.sh staging

  3. Verify Deployment

    # Check health status
curl http://localhost:3001/health

# View metrics
curl http://localhost:3001/metrics

Environment Variables

Database Configuration

# Enable cluster mode
DB_CLUSTER_MODE=true

# Write database (master)
DB_WRITE_HOST=pgbouncer-write
DB_WRITE_PORT=6432
DB_WRITE_NAME=vesting_vault
DB_WRITE_USER=postgres
DB_WRITE_PASSWORD=your_password

# Read database (replicas)
DB_READ_HOST=pgbouncer-read
DB_READ_PORT=6433
DB_READ_NAME=vesting_vault
DB_READ_USER=postgres
DB_READ_PASSWORD=your_password

# Direct replica connections (for failover)
DB_READ_HOSTS=postgres-replica-1:5432,postgres-replica-2:5432

# Performance tuning
DB_REPLICA_LAG_THRESHOLD=1000

Performance Characteristics

Read Operations

  • Throughput: Up to 10x improvement with 2 replicas
  • Latency: Sub-100ms for cached queries
  • Concurrency: 40+ concurrent read connections

Write Operations

  • Throughput: Optimized for high-frequency writes
  • Latency: <50ms for standard writes
  • Durability: Synchronous replication to replicas

Failover Performance

  • Detection Time: <30 seconds
  • Failover Time: <5 seconds
  • Recovery Time: Automatic within 5 minutes

Monitoring and Alerting

Key Metrics

Database Health

  • Master database availability
  • Replica database availability
  • Replication lag (bytes)
  • Connection pool utilization

Performance Metrics

  • Query latency (p50, p95, p99)
  • Throughput (queries/second)
  • Error rates
  • Connection pool statistics

Alerting Thresholds

  • Replica lag > 1MB
  • Database availability < 99.9%
  • Connection pool utilization > 80%
  • Query latency > 1 second

Monitoring Endpoints

Health Check

curl http://localhost:3001/health

Response format:

{
  "timestamp": "2024-01-01T00:00:00.000Z",
  "master": { "healthy": true },
  "replicas": [
    { "index": 0, "healthy": true, "lag": 1024 },
    { "index": 1, "healthy": true, "lag": 2048 }
  ],
  "overall": "healthy"
}

Metrics

curl http://localhost:3001/metrics

Prometheus-style metrics for monitoring systems.

Testing

Scalability Tests

Run the comprehensive test suite:

chmod +x scripts/test-scalability.sh
./scripts/test-scalability.sh staging

Test Categories

  1. Read/Write Splitting - Verifies queries go to correct database
  2. Load Balancing - Tests distribution across replicas
  3. Replica Lag - Monitors replication performance
  4. Failover - Tests automatic failover behavior
  5. Health Monitoring - Validates monitoring systems
  6. Performance - Benchmarks concurrent operations
  7. Connection Pooling - Tests pool limits and efficiency
  8. Data Consistency - Ensures data integrity
  9. High Availability - Tests resilience to failures
  10. Stress Test - Validates performance under load

Troubleshooting

Common Issues

Replica Lag

Symptoms: Read operations return stale data

Solutions:

  1. Check network bandwidth between master and replicas
  2. Monitor disk I/O on replicas
  3. Consider reducing write frequency
  4. Increase wal_keep_size on master

Connection Pool Exhaustion

Symptoms: "Too many connections" errors

Solutions:

  1. Increase pool size in PgBouncer configuration
  2. Optimize application connection usage
  3. Implement connection timeouts
  4. Monitor connection pool metrics

Replica Failures

Symptoms: Failed health checks, reduced read capacity

Solutions:

  1. Check replica logs for errors
  2. Verify network connectivity
  3. Restart replica service
  4. Reinitialize replica from master backup

Debug Commands

Check Replication Status

# Master
docker-compose exec postgres-master psql -U postgres -d vesting_vault -c "SELECT * FROM pg_stat_replication;"

# Replicas
docker-compose exec postgres-replica-1 psql -U postgres -d vesting_vault -c "SELECT pg_last_wal_replay_lsn();"

Monitor Connection Pools

# Write pool
docker-compose exec pgbouncer-write psql -U postgres -d pgbouncer -c "SHOW POOLS;"

# Read pool
docker-compose exec pgbouncer-read psql -U postgres -d pgbouncer -c "SHOW POOLS;"

View Logs

# All services
docker-compose logs -f

# Specific service
docker-compose logs -f postgres-master

Scaling Guidelines

Horizontal Scaling

Adding Replicas

  1. Update docker-compose-scalable.yml
  2. Add new replica configuration
  3. Update PgBouncer read pool configuration
  4. Restart services
  5. Verify replication status

Removing Replicas

  1. Stop traffic to replica (PgBouncer)
  2. Wait for existing connections to drain
  3. Stop replica service
  4. Update configuration
  5. Restart services

Vertical Scaling

Master Database

  • Increase CPU cores for write operations
  • Add RAM for larger shared_buffers
  • Use SSD storage for better I/O
  • Consider partitioning for large tables

Replica Databases

  • Scale based on read workload
  • More replicas = better read performance
  • Consider read-specific optimizations

Security Considerations

Network Security

  • Use SSL/TLS for all database connections
  • Implement network segmentation
  • Restrict access to database ports
  • Use VPN or private networks

Authentication

  • Strong passwords for all database users
  • Separate users for different applications
  • Regular password rotation
  • Implement connection limits

Data Protection

  • Enable transparent data encryption
  • Regular backups with encryption
  • Audit logging for all operations
  • Compliance with data protection regulations

Backup and Recovery

Backup Strategy

  1. Continuous Archiving: WAL files shipped to backup storage
  2. Full Backups: Daily base backups
  3. Point-in-Time Recovery: Restore to any point in time
  4. Cross-Region Replication: Disaster recovery

Recovery Procedures

  1. Master Failure: Promote replica to master
  2. Replica Failure: Rebuild from master backup
  3. Partial Corruption: Point-in-time recovery
  4. Complete Loss: Restore from latest backup

Performance Tuning

PostgreSQL Configuration

Master Optimizations

# Memory
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB

# WAL for replication
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
wal_keep_size = 1GB

# Checkpoints
checkpoint_completion_target = 0.9

Replica Optimizations

# Read performance
hot_standby = on
max_standby_streaming_delay = 30s

# Recovery
restore_command = 'cp /var/lib/postgresql/archive/%f %p'

Application Optimizations

Connection Management

  • Use connection pooling
  • Implement proper connection timeouts
  • Close connections properly
  • Monitor connection usage

Query Optimization

  • Use appropriate indexes
  • Optimize slow queries
  • Implement query caching
  • Monitor query performance

Migration Guide

From Single Instance

  1. Preparation

    • Take full backup
    • Schedule maintenance window
    • Prepare new infrastructure

  2. Setup

    • Deploy new cluster
    • Configure replication
    • Test failover procedures

  3. Migration

    • Stop application writes
    • Final data sync
    • Update application configuration
    • Switch to new cluster
    • Verify all systems

  4. Cleanup

    • Decommission old instance
    • Update monitoring
    • Document changes

Best Practices

Operations

  • Regular health checks
  • Automated failover testing
  • Performance monitoring
  • Capacity planning

Development

  • Use read replicas for reporting
  • Implement circuit breakers
  • Handle replica lag gracefully
  • Test with realistic data volumes

Security

  • Regular security updates
  • Access control reviews
  • Audit log analysis
  • Incident response planning

Support and Maintenance

Regular Tasks

  • Daily health checks
  • Weekly performance reviews
  • Monthly security updates
  • Quarterly capacity planning

Emergency Procedures

  • 24/7 monitoring alerts
  • On-call rotation
  • Incident response plan
  • Communication procedures

Conclusion

This scalable architecture provides the foundation for handling 1 million users while maintaining high performance and availability. The combination of read replicas, connection pooling, and intelligent routing ensures that the Vesting Vault platform can scale efficiently as user demand grows.

Regular monitoring, testing, and optimization are essential to maintain optimal performance. The provided tools and procedures enable the operations team to manage the database cluster effectively.