Part 196: PostgreSQL Super-Extensions: pg_stat_statements & pg_trgm

Two essential extensions that transform database observability and text searching.

1. pg_stat_statements: The Slow Query Microscope

Tracks execution statistics of all queries executed across your entire database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 5 queries consuming the most total execution time:
SELECT 
    ROUND(total_exec_time::numeric, 2) AS total_time_ms,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_time_ms,
    ROUND((100.0 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct_total,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

2. pg_trgm: Fuzzy Trigram Search

Breaks text into 3-character slices, enabling lightning-fast fuzzy typo-tolerant matching and indexed regex:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create GiST/GIN Trigram Index on customer names
CREATE INDEX idx_cust_name_trgm ON customers USING gin (full_name gin_trgm_ops);

-- Lightning-fast fuzzy search (matches "Jonathon", "Jonathan", "Jonathn"):
SELECT full_name, similarity(full_name, 'Jonathon') AS sim
FROM customers
WHERE full_name % 'Jonathon'
ORDER BY sim DESC;

Key Takeaways

• Install pg_stat_statements on day one on every production PostgreSQL cluster.

In Part 197, we explore Database Health Check Runbook: Table Bloat & Connection Leaks!