Capstone: Database Reliability Review

review_areas:
  configuration:
    - Memory settings (shared_buffers, work_mem)
    - Connection limits (max_connections)
    - Autovacuum tuning
    - WAL and checkpoint settings

  high_availability:
    - Replication setup (synchronous vs asynchronous)
    - Failover procedures (manual vs automated)
    - Replica health and lag

  backups:
    - Backup strategy (pg_dump, pg_basebackup, WAL archiving)
    - Recovery point objective (RPO) and recovery time objective (RTO)
    - Backup verification and restore testing

  monitoring:
    - Metrics collection (pg_stat_statements, pg_exporter)
    - Alerting rules (lag, connections, disk, locks)
    - Dashboard coverage

  security:
    - Authentication (SCRAM, mTLS)
    - Authorization (RBAC, row-level security)
    - Encryption (at rest and in transit)
    - Audit logging

  operations:
    - Migration procedures
    - Incident runbooks
    - On-call procedures
    - Capacity planning

-- 1. Configuration audit
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
    'shared_buffers', 'work_mem', 'effective_cache_size',
    'max_connections', 'autovacuum', 'wal_level',
    'checkpoint_timeout', 'max_wal_size'
);

-- 2. Connection health
SELECT
    count(*) AS total_connections,
    current_setting('max_connections')::int AS max_connections,
    ROUND(count(*)::numeric / current_setting('max_connections')::int * 100, 1) AS pct_used
FROM pg_stat_activity;

-- 3. Cache hit ratio (should be > 99%)
SELECT
    ROUND(100.0 * sum(blks_hit) / GREATEST(sum(blks_hit) + sum(blks_read), 1), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

-- 4. Dead tuple accumulation
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 5. Replication status
SELECT
    client_addr,
    state,
    replay_lag,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- 6. Unused indexes (candidates for removal)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname LIKE 'idx_%';

-- 7. Long-running queries
SELECT pid, now() - query_start AS duration, LEFT(query, 80)
FROM pg_stat_activity
WHERE state = 'active' AND query_start < now() - INTERVAL '5 minutes';

-- Check backup status (if using pgBackRest)
-- pgbackrest info

-- Check WAL archiving status
SELECT
    archived_count,
    failed_count,
    last_archived_wal,
    last_archived_time,
    last_failed_wal,
    last_failed_time
FROM pg_stat_archiver;

-- Calculate potential data loss window
-- Time since last successful backup
SELECT
    now() - pg_postmaster_start_time() AS uptime,
    (SELECT last_archived_time FROM pg_stat_archiver) AS last_wal_archive;

improvement_plan:
  title: "PostgreSQL Reliability Improvement Plan"
  date: "2024-06-15"
  reviewer: "SRE Team"

  findings:
    - area: "Configuration"
      finding: "shared_buffers set to 128MB on 32GB server"
      risk: "High"
      recommendation: "Increase to 8GB"
      effort: "Low (config change + restart)"
      timeline: "This sprint"

    - area: "Backups"
      finding: "No WAL archiving configured"
      risk: "Critical"
      recommendation: "Enable WAL archiving to S3"
      effort: "Medium (setup + testing)"
      timeline: "Next sprint"

    - area: "Monitoring"
      finding: "No replication lag alerts"
      risk: "High"
      recommendation: "Add Prometheus alert for lag > 30s"
      effort: "Low (alerting rule)"
      timeline: "This sprint"

    - area: "Security"
      finding: "Application using superuser account"
      risk: "High"
      recommendation: "Create dedicated read-write user with minimal permissions"
      effort: "Medium (code + config change)"
      timeline: "Next sprint"

  timeline:
    immediate:   # This week
      - "Fix shared_buffers"
      - "Add replication lag alert"

    short_term:  # This month
      - "Enable WAL archiving"
      - "Create restricted database user"
      - "Enable auto-explain"

    medium_term: # This quarter
      - "Implement connection pooling with PgBouncer"
      - "Set up pg_basebackup CronJob"
      - "Create database incident runbooks"

    long_term:   # This year
      - "Evaluate database operator for HA"
      - "Implement automated backup verification"
      - "Conduct disaster recovery drill"