Top 9 PostgreSQL Extensions Used by Tiger Data Customers in 2026

useEffect(() => {
    fetch('/api/metrics/cpu').then(res => res.json()).then(setCpuData);
}, []);  // User clicks "Last Hour" - Loading... 30 seconds... still loading...
Backend (Node.js):
app.get('/api/metrics/cpu', async (req, res) => {
    const result = await db.query(`
        SELECT date_trunc('minute', time) AS minute, AVG(cpu_percent) AS avg_cpu
        FROM metrics WHERE time > NOW() - INTERVAL '1 hour' GROUP BY minute ORDER BY minute
    `);
    res.json(result.rows);  // With 100M rows, this takes 30+ seconds
});

useEffect(() => {
    fetch('/api/metrics/cpu').then(res => res.json()).then(setCpuData);
}, []);  // User clicks "Last Hour" - Data appears in 50ms

app.get('/api/metrics/cpu', async (req, res) => {
    const result = await db.query(`
        SELECT time_bucket('1 minute', time) AS minute, AVG(cpu_percent) AS avg_cpu
        FROM metrics WHERE time > NOW() - INTERVAL '1 hour' GROUP BY minute ORDER BY minute
    `);
    res.json(result.rows);  // Hypertable: only scans last hour's chunk. 50ms!
});

CREATE TABLE metrics (time TIMESTAMPTZ NOT NULL, server TEXT, cpu_percent FLOAT);
SELECT create_hypertable('metrics', 'time');  -- This one line makes it fast!

-- Compress old data (1TB to 100GB)
ALTER TABLE metrics SET (timescaledb.compress);
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- Auto-delete data older than 90 days
SELECT add_retention_policy('metrics', INTERVAL '90 days');

const updateProfile = async (name) => {
    await fetch('/api/user', { 
        method: 'PUT', 
        body: JSON.stringify({ name }) 
    });
};

app.put('/api/user', async (req, res) => {
    const old = await db.query('SELECT * FROM users WHERE id = $1', [req.userId]);
    await db.query('UPDATE users SET name = $1 WHERE id = $2', [req.body.name, req.userId]);
    await db.query('INSERT INTO audit_log VALUES ($1, $2, $3)', [req.userId, old.rows[0], req.body]);
    res.json({ success: true });
});

app.put('/api/user', async (req, res) => {
    const old = await db.query('SELECT * FROM users WHERE id = $1', [req.userId]);
    await db.query('UPDATE users SET name = $1 WHERE id = $2', [req.body.name, req.userId]);
    await db.query('INSERT INTO audit_log VALUES ($1, $2, $3)', [req.userId, old.rows[0], req.body]);
    res.json({ success: true });
});

app.put('/api/user', async (req, res) => {
    const old = await db.query('SELECT * FROM users WHERE id = $1', [req.userId]);
    await db.query('UPDATE users SET name = $1 WHERE id = $2', [req.body.name, req.userId]);
    // Oops! Forgot the audit log here. Bug found 6 months later.
    res.json({ success: true });
});

const updateProfile = async (name) => {
    await fetch('/api/user', { 
        method: 'PUT', 
        body: JSON.stringify({ name }) 
    });
};

app.put('/api/user', async (req, res) => {
    const { name } = req.body;
    await db.query('UPDATE users SET name = $1 WHERE id = $2', [name, req.userId]);
    res.json({ success: true });
});
// That's it. No audit logic needed. The database handles it.

CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (user_id, old_data, new_data, changed_at)
    VALUES (NEW.id, to_jsonb(OLD), to_jsonb(NEW), NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION log_changes();

Developer: "I think it's the search query?"
DBA: "Let me enable query logging..."
*3 hours later, parsing 50GB of logs*
DBA: "Actually, it might be the dashboard query?"
*Users still complaining*

-- Show me the 5 queries eating the most time
SELECT 
    substring(query, 1, 50) AS query,
    calls,
    round(total_exec_time / 1000) AS total_seconds,
    round(mean_exec_time) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- Queries that return few rows but read lots of data = missing index
SELECT query, calls, rows
FROM pg_stat_statements
WHERE calls > 100                    -- Runs often
  AND rows / calls < 10              -- Returns few rows per call
  AND shared_blks_read / calls > 100 -- But reads tons of data
LIMIT 5;

const login = async (email, password) => {
    const res = await fetch('/api/login', { method: 'POST', body: JSON.stringify({ email, password }) });
    return res.json();
};

app.post('/api/login', async (req, res) => {
    const user = await db.query('SELECT * FROM users WHERE email = $1 AND password = $2', [req.body.email, req.body.password]);
    res.json(user.rows[0]);  // Plain text! Database leaked = everyone's password exposed
});

app.post('/api/login', async (req, res) => {
    const user = await db.query(
        `SELECT * FROM users WHERE email = $1 AND password_hash = crypt($2, password_hash)`,
        [req.body.email, req.body.password]  // pgcrypto compares hashes
    );
    res.json(user.rows[0]);  // Database leaked? Attackers see '$2a$06$xK9m...' - useless!
});

INSERT INTO users (email, password_hash) VALUES ('alice@example.com', crypt('password123', gen_salt('bf')));

-- Store encrypted (only you can decrypt with the key)
INSERT INTO medical_records (patient_id, diagnosis)
VALUES (42, pgp_sym_encrypt('Diabetes Type 2', 'secret-key'));

-- Decrypt when needed
SELECT pgp_sym_decrypt(diagnosis, 'secret-key') FROM medical_records WHERE patient_id = 42;

SELECT encode(gen_random_bytes(32), 'hex') AS api_key;     -- 'a7f3b2c1d4e5...'
SELECT encode(gen_random_bytes(16), 'base64') AS token;    -- 'xK9mN2pQ4rS6tU8v'

import csv
import psycopg2

# Parse the CSV
with open('tradeshow_leads.csv') as f:
    leads = list(csv.DictReader(f))

# Create temp table, insert rows, run query...
# 30 lines of code for a simple question

# Copy file to server's data directory
scp tradeshow_leads.csv your-server:/var/lib/postgresql/data/imports/

-- One-time setup: create a foreign table pointing to the file
CREATE FOREIGN TABLE tradeshow_leads (email TEXT, company TEXT, booth TEXT)
SERVER files OPTIONS (filename '/var/lib/postgresql/data/imports/tradeshow_leads.csv', format 'csv', header 'true');

-- Now query it like any table!
SELECT t.email, t.company, c.plan
FROM tradeshow_leads t JOIN customers c ON t.email = c.email;

const search = async (query) => {
    const res = await fetch(`/api/search?q=${query}`);
    setResults(await res.json());  // User types: "reset my password" - No results
};

app.get('/api/search', async (req, res) => {
    const results = await db.query(
        `SELECT * FROM articles WHERE title ILIKE $1 OR content ILIKE $1`,
        [`%${req.query.q}%`]
    );
    res.json(results.rows);  // Misses "Account Recovery Steps" - no matching keywords!
});

const search = async (query) => {
    const res = await fetch(`/api/search?q=${query}`);
    setResults(await res.json());  // "reset my password" finds "Account Recovery Steps"
};

app.get('/api/search', async (req, res) => {
    const results = await db.query(
        `SELECT title, content FROM articles
         ORDER BY embedding <=> ai.openai_embed('text-embedding-3-small', $1) LIMIT 5`,
        [req.query.q]
    );
    res.json(results.rows);  // Finds "Account Recovery Steps" - same meaning!
});

ALTER TABLE articles ADD COLUMN embedding vector(1536);
CREATE INDEX ON articles USING ivfflat (embedding vector_cosine_ops);
UPDATE articles SET embedding = ai.openai_embed('text-embedding-3-small', content);

SELECT name, price FROM products WHERE id != 42
ORDER BY embedding <=> (SELECT embedding FROM products WHERE id = 42) LIMIT 4;

CREATE INDEX ON products USING hnsw (embedding vector_cosine_ops);
-- 50M vectors = 64GB RAM needed
-- Build time: 8 hours
-- Query time: 500ms (slowing down as data grows)

CREATE INDEX ON products USING diskann (embedding vector_cosine_ops);
-- 50M vectors = 8GB RAM (index on disk)
-- Build time: 1 hour
-- Query time: 5ms (stays fast)

app.post('/api/ask', async (req, res) => {
    // Call 1: Get embedding from OpenAI
    const embedding = await openai.embeddings.create({ 
        model: 'text-embedding-3-small', 
        input: req.body.question 
    });
    
    // Call 2: Find similar docs in database
    const docs = await db.query(
        `SELECT content FROM docs ORDER BY embedding <=> $1 LIMIT 3`, 
        [embedding.data[0].embedding]
    );
    
    // Call 3: Get answer from OpenAI
    const answer = await openai.chat.completions.create({
        model: 'gpt-4',
        messages: [{ role: 'user', content: docs.rows.map(d => d.content).join('\n') + '\n\n' + req.body.question }]
    });
res.json({ answer: answer.choices[0].message.content });
});

app.post('/api/ask', async (req, res) => {
    const result = await db.query(`
        WITH relevant_docs AS (
            SELECT content FROM docs 
            ORDER BY embedding <=> ai.openai_embed('text-embedding-3-small', $1) 
            LIMIT 3
        )
        SELECT ai.openai_chat_complete('gpt-4', 
            jsonb_build_array(jsonb_build_object('role', 'user', 'content', 
                (SELECT string_agg(content, E'\n') FROM relevant_docs) || E'\n\n' || $1
            ))
        ) AS answer
    `, [req.body.question]);
    res.json(result.rows[0]);
});

-- Generate embeddings for all your articles (one-time)
UPDATE articles SET embedding = ai.openai_embed('text-embedding-3-small', content);

-- Summarize text with GPT-4
SELECT ai.openai_chat_complete('gpt-4', 
    jsonb_build_array(jsonb_build_object('role', 'user', 'content', 'Summarize: ' || article_text))
);
-- Use local Ollama models (free, private, no API costs)
SELECT ai.ollama_generate('llama3', 'Explain this error: connection refused');
SELECT ai.ollama_embed('nomic-embed-text', 'your text here');

const findNearbyStores = async (lat, lng) => {
    const res = await fetch(`/api/stores/nearby?lat=${lat}&lng=${lng}`);
    return res.json();
};

app.get('/api/stores/nearby', async (req, res) => {
    const stores = await db.query(`
        SELECT *, (3959 * acos(cos(radians($1)) * cos(radians(lat)) * 
        cos(radians(lng) - radians($2)) + sin(radians($1)) * sin(radians(lat)))) AS distance
        FROM stores HAVING distance < 5 ORDER BY distance
    `, [req.query.lat, req.query.lng]);
    res.json(stores.rows);  // Slow. No index. Wrong at poles.
});

const findNearbyStores = async (lat, lng) => {
    const res = await fetch(`/api/stores/nearby?lat=${lat}&lng=${lng}`);
    return res.json();
};

app.get('/api/stores/nearby', async (req, res) => {
    const stores = await db.query(`
        SELECT name, address, ST_Distance(location, ST_Point($2, $1)::geography) / 1609 AS miles
        FROM stores
        WHERE ST_DWithin(location, ST_Point($2, $1)::geography, 8047)
        ORDER BY location <-> ST_Point($2, $1)::geography
    `, [req.query.lat, req.query.lng]);  
    res.json(stores.rows);  // Uses spatial index. Fast!
});

ALTER TABLE stores ADD COLUMN location GEOGRAPHY(POINT, 4326);
CREATE INDEX ON stores USING GIST(location);

-- Which delivery zone is this address in?
SELECT zone_name, delivery_fee FROM delivery_zones
WHERE ST_Contains(zone_polygon, ST_Point(-73.99, 40.73)::geometry);

-- Uber-style: find available drivers within 2 miles
SELECT name, ST_Distance(location, ST_Point(-73.99, 40.73)::geography) AS meters
FROM drivers
WHERE ST_DWithin(location, ST_Point(-73.99, 40.73)::geography, 3218) AND available = true
ORDER BY meters LIMIT 5;

-- Pre-installed (already available)
CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- User-installed (available on Tiger Data)
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS vectorscale;
CREATE EXTENSION IF NOT EXISTS ai;
CREATE EXTENSION IF NOT EXISTS pg_auth_mon;
CREATE EXTENSION IF NOT EXISTS postgis;