Database Performance at Scale

-- Create partitioned audit_logs table
CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type VARCHAR(100) NOT NULL,
    entity_id UUID NOT NULL,
    action VARCHAR(50) NOT NULL,
    old_values JSONB,
    new_values JSONB,
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE audit_logs_2026_01 PARTITION OF audit_logs
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE audit_logs_2026_02 PARTITION OF audit_logs
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Auto-create partitions (pg_partman extension)
SELECT partman.create_parent(
    p_parent_table := 'public.audit_logs',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3
);

-- Composite index for tenant + status queries (most common)
CREATE INDEX idx_po_tenant_status
    ON purchase_orders (tenant_id, status)
    WHERE is_deleted = FALSE;

-- Partial index for active vendors only
CREATE INDEX idx_vendor_active
    ON vendors (tenant_id, vendor_code)
    WHERE status = 'ACTIVE';

-- GIN index for full-text search
CREATE INDEX idx_vendor_search
    ON vendors USING GIN (
        to_tsvector('english', name || ' ' || COALESCE(gstin, ''))
    );

-- BRIN index for time-series data (audit logs)
CREATE INDEX idx_audit_created_brin
    ON audit_logs USING BRIN (created_at);

-- Identify unused indexes (run weekly)
SELECT
    schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

# pgbouncer.ini (Production)
[databases]
reqvise = host=127.0.0.1 port=5432 dbname=reqvise

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool settings
pool_mode = transaction
max_client_conn = 500
default_pool_size = 100
min_pool_size = 25
reserve_pool_size = 10
reserve_pool_timeout = 5

# Timeouts
server_connect_timeout = 15
server_idle_timeout = 60
client_idle_timeout = 300
query_timeout = 30

// EF Core Read Replica Configuration
public class ReqviseDbContext : DbContext
{
    private readonly bool _useReadReplica;

    public ReqviseDbContext(DbContextOptions options, bool useReadReplica = false)
        : base(options)
    {
        _useReadReplica = useReadReplica;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionString = _useReadReplica
            ? Configuration["ConnectionStrings:ReadReplica"]
            : Configuration["ConnectionStrings:Primary"];

        optionsBuilder.UseNpgsql(connectionString);
    }
}

// Service using read replica for reports
public class AnalyticsService
{
    private readonly ReqviseDbContext _readContext;

    public AnalyticsService(IDbContextFactory<ReqviseDbContext> factory)
    {
        _readContext = factory.CreateDbContext(useReadReplica: true);
    }

    public async Task<DashboardKpis> GetKpisAsync()
    {
        return await _readContext.PurchaseOrders
            .AsNoTracking()
            .GroupBy(p => p.Status)
            .Select(g => new { Status = g.Key, Count = g.Count() })
            .ToListAsync();
    }
}

-- Spend by Category materialized view
CREATE MATERIALIZED VIEW mv_spend_by_category AS
SELECT
    c.id AS category_id,
    c.name AS category_name,
    po.tenant_id,
    DATE_TRUNC('month', po.po_date) AS month,
    SUM(poi.total_amount) AS total_spend,
    COUNT(DISTINCT po.id) AS po_count
FROM po_items poi
JOIN purchase_orders po ON poi.po_id = po.id
JOIN products p ON poi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE po.status IN ('APPROVED', 'COMPLETED')
GROUP BY c.id, c.name, po.tenant_id, DATE_TRUNC('month', po.po_date);

-- Create unique index for concurrent refresh
CREATE UNIQUE INDEX idx_mv_spend_unique
    ON mv_spend_by_category (category_id, tenant_id, month);

-- Refresh concurrently (no lock)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_spend_by_category;

-- Archive audit logs older than 2 years
INSERT INTO audit_logs_archive
SELECT * FROM audit_logs
WHERE created_at < NOW() - INTERVAL '2 years';

DELETE FROM audit_logs
WHERE created_at < NOW() - INTERVAL '2 years';

-- Run as Hangfire scheduled job
[RecurringJob("archive-audit-logs", "0 2 1 */3 *")] // Quarterly at 2 AM
public async Task ArchiveAuditLogsAsync()
{
    await _dbContext.Database.ExecuteSqlRawAsync(archiveSql);
    _logger.Information("Archived audit logs older than 2 years");
}