PostgreSQL Extensions Guide: pg_stat_statements, pg_trgm, pg_cron, and More

-- Performance analysis
CREATE EXTENSION pg_stat_statements;  -- query statistics
CREATE EXTENSION pgstattuple;         -- table/index bloat analysis
CREATE EXTENSION pg_buffercache;      -- inspect buffer pool contents

-- Data types and functions
CREATE EXTENSION hstore;             -- key-value store in a column
CREATE EXTENSION citext;             -- case-insensitive text
CREATE EXTENSION uuid-ossp;          -- UUID generation functions
CREATE EXTENSION pg_trgm;            -- trigram similarity, fuzzy search

-- Advanced features
CREATE EXTENSION vector;             -- pgvector: AI embeddings
CREATE EXTENSION timescaledb;        -- time-series hypertables
CREATE EXTENSION pg_partman;         -- automated partition management
CREATE EXTENSION pg_cron;            -- cron-based job scheduling

CREATE EXTENSION pg_trgm;

-- GIN index for fast fuzzy search
CREATE INDEX idx_products_name_trgm ON products
USING GIN (name gin_trgm_ops);

-- Find similar product names
SELECT name, similarity(name, 'postgresl') AS sim
FROM products
WHERE name % 'postgresl'     -- similarity > 0.3 threshold
ORDER BY sim DESC
LIMIT 10;

CREATE EXTENSION pg_cron;

-- Vacuum a table every night at 2am
SELECT cron.schedule('nightly-vacuum', '0 2 * * *',
  $$VACUUM ANALYZE orders$$);

-- Purge old data weekly
SELECT cron.schedule('weekly-purge', '0 3 * * 0',
  $$DELETE FROM events WHERE created_at < now() - INTERVAL '90 days'$$);

-- List scheduled jobs
SELECT * FROM cron.job;

CREATE EXTENSION pg_buffercache;

-- Top tables by buffer cache usage
SELECT c.relname,
       count(*) AS buffers,
       pg_size_pretty(count(*) * 8192) AS cached_size
FROM pg_buffercache b
JOIN pg_class c ON c.relfilenode = b.relfilenode
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;