Database Optimization

This document covers database schema design and optimization strategies for billion-user scale deployments.

Schema Design Principles

Use SMALLINT Enums Instead of VARCHAR

One of the most impactful optimizations is using integer enums instead of string-based status fields.

Before (inefficient):

CREATE TABLE auto_tasks (
    id UUID PRIMARY KEY,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    priority VARCHAR(20) NOT NULL DEFAULT 'normal',
    execution_mode VARCHAR(50) NOT NULL DEFAULT 'supervised',
    CONSTRAINT check_status CHECK (status IN ('pending', 'ready', 'running', 'paused', 'waiting_approval', 'completed', 'failed', 'cancelled'))
);

After (optimized):

CREATE TABLE auto_tasks (
    id UUID PRIMARY KEY,
    status SMALLINT NOT NULL DEFAULT 0,        -- 2 bytes
    priority SMALLINT NOT NULL DEFAULT 1,      -- 2 bytes
    execution_mode SMALLINT NOT NULL DEFAULT 1 -- 2 bytes
);

Storage Comparison

Savings per row with 5 enum fields:

• VARCHAR: ~50 bytes average
• SMALLINT: 10 bytes fixed
• Savings: 40 bytes per row = 40GB per billion rows

Enum Value Reference

All domain values in General Bots use SMALLINT. Reference table:

Channel Types

Message Role

Message Type

LLM Provider

Task Status

Task Priority

Execution Mode

Risk Level

Approval Status

Intent Type

Memory Type

Sync Status

Indexing Strategies

Composite Indexes for Common Queries

-- Session lookup by user
CREATE INDEX idx_sessions_user ON user_sessions(user_id, created_at DESC);

-- Messages by session (most common query)
CREATE INDEX idx_messages_session ON message_history(session_id, message_index);

-- Active tasks by status and priority
CREATE INDEX idx_tasks_status ON auto_tasks(status, priority) WHERE status < 5;

-- Tenant-scoped queries
CREATE INDEX idx_sessions_tenant ON user_sessions(tenant_id, created_at DESC);

Partial Indexes for Active Records

-- Only index active bots (saves space)
CREATE INDEX idx_bots_active ON bots(tenant_id, is_active) WHERE is_active = true;

-- Only index pending approvals
CREATE INDEX idx_approvals_pending ON task_approvals(task_id, expires_at) WHERE status = 0;

-- Only index unread messages
CREATE INDEX idx_messages_unread ON message_history(user_id, created_at) WHERE is_read = false;

BRIN Indexes for Time-Series Data

-- BRIN index for time-ordered data (much smaller than B-tree)
CREATE INDEX idx_messages_created_brin ON message_history USING BRIN (created_at);
CREATE INDEX idx_analytics_date_brin ON analytics_events USING BRIN (created_at);

Table Partitioning

Partition High-Volume Tables by Time

-- Partitioned messages table
CREATE TABLE message_history (
    id UUID NOT NULL,
    session_id UUID NOT NULL,
    tenant_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    -- other columns...
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE message_history_2025_01 PARTITION OF message_history
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE message_history_2025_02 PARTITION OF message_history
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- ... continue for each month

-- Default partition for future data
CREATE TABLE message_history_default PARTITION OF message_history DEFAULT;

Automatic Partition Management

-- Function to create next month's partition
CREATE OR REPLACE FUNCTION create_monthly_partition(
    table_name TEXT,
    partition_date DATE
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := table_name || '_' || to_char(partition_date, 'YYYY_MM');
    start_date := date_trunc('month', partition_date);
    end_date := start_date + INTERVAL '1 month';
    
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
        partition_name, table_name, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;

-- Create partitions for next 3 months
SELECT create_monthly_partition('message_history', NOW() + INTERVAL '1 month');
SELECT create_monthly_partition('message_history', NOW() + INTERVAL '2 months');
SELECT create_monthly_partition('message_history', NOW() + INTERVAL '3 months');

Connection Pooling

PgBouncer Configuration

; pgbouncer.ini
[databases]
gb_shard1 = host=shard1.db port=5432 dbname=gb
gb_shard2 = host=shard2.db port=5432 dbname=gb

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

; Pool settings
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 25
reserve_pool_timeout = 3

; Timeouts
server_connect_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600
client_idle_timeout = 0

Application Connection Settings

# config.toml
[database]
max_connections = 100
min_connections = 10
connection_timeout_secs = 5
idle_timeout_secs = 300
max_lifetime_secs = 1800

Query Optimization

Use EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM message_history
WHERE session_id = 'abc-123'
ORDER BY message_index;

Avoid N+1 Queries

Bad:

-- 1 query for sessions
SELECT * FROM user_sessions WHERE user_id = 'xyz';
-- N queries for messages (one per session)
SELECT * FROM message_history WHERE session_id = ?;

Good:

-- Single query with JOIN
SELECT s.*, m.*
FROM user_sessions s
LEFT JOIN message_history m ON m.session_id = s.id
WHERE s.user_id = 'xyz'
ORDER BY s.created_at DESC, m.message_index;

Use Covering Indexes

-- Index includes all needed columns (no table lookup)
CREATE INDEX idx_sessions_covering ON user_sessions(user_id, created_at DESC)
INCLUDE (title, message_count, last_activity_at);

Vacuum and Maintenance

Aggressive Autovacuum for High-Churn Tables

ALTER TABLE message_history SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2
);

ALTER TABLE user_sessions SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01
);

Regular Maintenance Tasks

-- Weekly: Reindex bloated indexes
REINDEX INDEX CONCURRENTLY idx_messages_session;

-- Monthly: Update statistics
ANALYZE VERBOSE message_history;

-- Quarterly: Cluster heavily-queried tables
CLUSTER message_history USING idx_messages_session;

Monitoring Queries

Table Bloat Check

SELECT
    schemaname || '.' || tablename AS table,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Slow Query Log

-- postgresql.conf
log_min_duration_statement = 100  -- Log queries > 100ms
log_statement = 'none'
log_lock_waits = on

Index Usage Statistics

SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 20;

Best Practices Summary

1. Use SMALLINT for enums - 2 bytes vs 10-50 bytes per field
2. Partition time-series tables - Monthly partitions for messages/analytics
3. Create partial indexes - Only index active/relevant rows
4. Use connection pooling - PgBouncer with transaction mode
5. Enable aggressive autovacuum - For high-churn tables
6. Monitor query performance - Log slow queries, check EXPLAIN plans
7. Use covering indexes - Include frequently-accessed columns
8. Avoid cross-shard queries - Keep tenant data together
9. Regular maintenance - Reindex, analyze, cluster as needed
10. Test at scale - Use production-like data volumes in staging