Database Monitoring with Prometheus

┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│  Application │───▶│  Connection  │───▶│  Database   │
│    Layer     │    │    Pool      │    │   Server    │
└─────────────┘    └─────────────┘    └─────────────┘
       │                                      │
       ▼                                      ▼
  ┌─────────┐                          ┌────────────┐
  │  Cache   │                          │  Replicas  │
  │  Layer   │                          │  / Shards  │
  └─────────┘                          └────────────┘

# database.yml
database:
  primary:
    host: localhost
    port: 5432
    name: app_production
    pool_size: 20
    timeout: 5000
  replica:
    host: replica.internal
    port: 5432
    name: app_production
    pool_size: 10
  cache:
    host: redis.internal
    port: 6379
    ttl: 3600

-- Optimized schema for Database Monitoring with Prometheus

CREATE TABLE records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data JSONB NOT NULL DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    version INT DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- GIN index for JSONB queries
CREATE INDEX idx_records_data ON records USING GIN (data jsonb_path_ops);

-- Partial index for active records only
CREATE INDEX idx_records_active ON records (created_at DESC)
WHERE (data->>'status')::text = 'active';

-- Trigger for auto-updating timestamps
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    NEW.version = OLD.version + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_records_updated
    BEFORE UPDATE ON records
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- Connection and performance stats
SELECT
    datname AS database,
    numbackends AS connections,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    blks_hit * 100.0 / NULLIF(blks_hit + blks_read, 0) AS cache_hit_ratio,
    tup_returned AS rows_returned,
    tup_fetched AS rows_fetched
FROM pg_stat_database
WHERE datname = current_database();