PostgreSQL for AI and Machine Learning - A Comprehensive Guide

-- Enable vector operations
CREATE EXTENSION IF NOT EXISTS vector;

-- Install MADlib for machine learning
CREATE EXTENSION IF NOT EXISTS madlib;

-- Enable full-text search capabilities
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create a table for storing embeddings
CREATE TABLE embeddings (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)  -- For OpenAI embeddings
);

-- Create an index for similarity search
CREATE INDEX ON embeddings 
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Create a view for feature engineering
CREATE VIEW ml_features AS
SELECT 
    id,
    -- Numeric features
    ARRAY[
        age::float,
        income::float,
        credit_score::float
    ] AS numeric_features,
    -- Text features using TF-IDF
    to_tsvector('english', description) AS text_features
FROM customer_data;

-- Standardize numeric features
CREATE OR REPLACE FUNCTION standardize_features(
    features float[]
) RETURNS float[] AS $$
DECLARE
    mean float[];
    std float[];
BEGIN
    -- Calculate mean and standard deviation
    SELECT 
        array_agg(avg),
        array_agg(stddev)
    INTO mean, std
    FROM (
        SELECT 
            generate_subscripts(features, 1) as i,
            avg(features[i]) OVER () as avg,
            stddev(features[i]) OVER () as stddev
        FROM unnest(features) as t
    ) s;
    
    -- Apply standardization
    RETURN array_agg(
        (features[i] - mean[i]) / NULLIF(std[i], 0)
    )
    FROM generate_subscripts(features, 1) i;
END;
$$ LANGUAGE plpgsql;

-- Find similar items using cosine similarity
SELECT 
    id,
    content,
    1 - (embedding <=> query_embedding) as similarity
FROM embeddings
WHERE 1 - (embedding <=> query_embedding) > 0.8
ORDER BY embedding <=> query_embedding
LIMIT 5;

-- Create a function for real-time predictions
CREATE OR REPLACE FUNCTION predict_customer_churn(
    customer_features json
) RETURNS float AS $$
DECLARE
    prediction float;
BEGIN
    -- Preprocess features
    WITH processed_features AS (
        SELECT standardize_features(
            ARRAY[
                (customer_features->>'age')::float,
                (customer_features->>'usage')::float,
                (customer_features->>'billing')::float
            ]
        ) AS features
    )
    
    -- Apply model (example using logistic regression)
    SELECT 
        madlib.logistic_predict(
            features,
            (SELECT model_coefficients FROM ml_models WHERE name = 'churn_model')
        )
    INTO prediction
    FROM processed_features;
    
    RETURN prediction;
END;
$$ LANGUAGE plpgsql;

-- Enable parallel processing
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;

-- Create partitioned tables for large datasets
CREATE TABLE ml_training_data (
    id SERIAL,
    features vector(1536),
    label int
) PARTITION BY RANGE (id);

-- Create partitions
CREATE TABLE ml_training_data_p1 PARTITION OF ml_training_data
    FOR VALUES FROM (1) TO (1000000);
CREATE TABLE ml_training_data_p2 PARTITION OF ml_training_data
    FOR VALUES FROM (1000000) TO (2000000);

-- Create specialized indexes for ML workloads
CREATE INDEX ON ml_training_data USING GiST (features);
CREATE INDEX ON ml_training_data USING BRIN (id);

-- Create a monitoring view for ML operations
CREATE VIEW ml_operations_monitor AS
SELECT 
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_analyze
FROM pg_stat_user_tables
WHERE relname LIKE 'ml_%';

-- Monitor vector operation performance
CREATE VIEW vector_performance_stats AS
SELECT 
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%vector%';