🛠️ Thursday Build: Smart Query Cache That Actually Works

Let's build a query cache that speeds up your app 100x for repetitive queries.

The Problem:
Same expensive queries running 1000 times/day.

The Solution:
Intelligent materialized view management!

Step 1: Create Cache Infrastructure

CREATE SCHEMA cache;

-- Cache tracking table
CREATE TABLE cache.query_cache (
    cache_id SERIAL PRIMARY KEY,
    query_hash TEXT UNIQUE,
    query_text TEXT,
    view_name TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    last_used TIMESTAMP DEFAULT NOW(),
    use_count INT DEFAULT 0,
    avg_exec_time_ms INT,
    size_bytes BIGINT
);

-- Auto-cleanup old caches
CREATE OR REPLACE FUNCTION cache.cleanup_old_caches()
RETURNS void AS $$
BEGIN
    -- Drop views unused for 7 days
    FOR r IN 
        SELECT view_name 
        FROM cache.query_cache 
        WHERE last_used < NOW() - INTERVAL '7 days'
    LOOP
        EXECUTE format('DROP MATERIALIZED VIEW IF EXISTS cache.%I', r.view_name);
        DELETE FROM cache.query_cache WHERE view_name = r.view_name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Step 2: Smart Cache Creator

CREATE OR REPLACE FUNCTION cache.create_smart_cache(
    p_query TEXT,
    p_threshold_ms INT DEFAULT 1000
)
RETURNS TEXT AS $$
DECLARE
    v_query_hash TEXT;
    v_view_name TEXT;
    v_exec_time INT;
BEGIN
    -- Generate query hash
    v_query_hash := md5(p_query);
    
    -- Check if already cached
    SELECT view_name INTO v_view_name
    FROM cache.query_cache
    WHERE query_hash = v_query_hash;
    
    IF v_view_name IS NOT NULL THEN
        -- Update usage stats
        UPDATE cache.query_cache 
        SET last_used = NOW(), use_count = use_count + 1
        WHERE query_hash = v_query_hash;
        
        RETURN format('SELECT * FROM cache.%I', v_view_name);
    END IF;
    
    -- Check if query is worth caching
    EXECUTE format('EXPLAIN (ANALYZE, FORMAT JSON) %s', p_query) INTO v_exec_time;
    
    IF v_exec_time < p_threshold_ms THEN
        RETURN p_query; -- Too fast, don't cache
    END IF;
    
    -- Create materialized view
    v_view_name := 'cache_' || v_query_hash;
    EXECUTE format('CREATE MATERIALIZED VIEW cache.%I AS %s', v_view_name, p_query);
    
    -- Track in cache table
    INSERT INTO cache.query_cache (query_hash, query_text, view_name, avg_exec_time_ms)
    VALUES (v_query_hash, p_query, v_view_name, v_exec_time);
    
    RETURN format('SELECT * FROM cache.%I', v_view_name);
END;
$$ LANGUAGE plpgsql;

Step 3: Automatic Refresh Strategy

-- Refresh based on data changes
CREATE OR REPLACE FUNCTION cache.smart_refresh()
RETURNS void AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT view_name, query_text
        FROM cache.query_cache
        WHERE last_used > NOW() - INTERVAL '1 hour'
        ORDER BY use_count DESC
        LIMIT 10  -- Refresh top 10 most used
    LOOP
        EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY cache.%I', r.view_name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Schedule hourly refresh
SELECT cron.schedule('refresh-cache', '0 * * * *', 'SELECT cache.smart_refresh()');

Step 4: Use It!

-- Your expensive query
SELECT cache.create_smart_cache($$
    SELECT 
        u.name,
        COUNT(o.id) as order_count,
        SUM(o.total) as total_spent,
        AVG(o.total) as avg_order
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE o.created_at > NOW() - INTERVAL '30 days'
    GROUP BY u.id, u.name
    ORDER BY total_spent DESC
    LIMIT 100
$$);

💪 Challenge:

Implement this cache
Test with your slowest query
Measure the speedup
Share your results!

Bonus: Add cache invalidation triggers when source data changes!

Tomorrow: The philosophy of caching

#PostgreSQL #Caching #Performance #WeekendProject

@postgres