Extensions & Performance Tuning

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries by total time (the biggest optimization targets)
SELECT
  substring(query, 1, 80) AS query,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Top queries by call count (high-frequency queries)
SELECT
  substring(query, 1, 80) AS query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Queries with worst buffer hit ratio (I/O bound)
SELECT
  substring(query, 1, 80) AS query,
  calls,
  shared_blks_hit,
  shared_blks_read,
  round(shared_blks_hit * 100.0 / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 100
ORDER BY hit_pct ASC
LIMIT 10;

-- Reset statistics (do periodically to see recent patterns)
SELECT pg_stat_statements_reset();

CREATE EXTENSION IF NOT EXISTS postgis;

-- Table with geographic point
CREATE TABLE restaurants (
  id serial PRIMARY KEY,
  name text NOT NULL,
  location geography(POINT, 4326) NOT NULL
);

-- Spatial index
CREATE INDEX idx_restaurants_location ON restaurants USING GIST (location);

-- Insert with lat/lon
INSERT INTO restaurants (name, location)
VALUES ('Pizza Place', ST_MakePoint(-73.9857, 40.7484)::geography);

-- Find restaurants within 2km of a point
SELECT name, ST_Distance(location, ST_MakePoint(-73.99, 40.75)::geography) AS dist_m
FROM restaurants
WHERE ST_DWithin(location, ST_MakePoint(-73.99, 40.75)::geography, 2000)
ORDER BY dist_m;

-- Nearest neighbor (KNN) — uses GiST index efficiently
SELECT name, location <-> ST_MakePoint(-73.99, 40.75)::geography AS dist
FROM restaurants
ORDER BY location <-> ST_MakePoint(-73.99, 40.75)::geography
LIMIT 5;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN trigram index — enables LIKE '%substring%' with index
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Now these use the index (impossible with B-tree):
SELECT * FROM products WHERE name LIKE '%wireless%';
SELECT * FROM products WHERE name ILIKE '%bluetooth%';

-- Similarity search (fuzzy matching)
SELECT name, similarity(name, 'postgre') AS sim
FROM products
WHERE similarity(name, 'postgre') > 0.3
ORDER BY sim DESC;

-- Trigram distance operator (for ORDER BY similarity)
SELECT name FROM products ORDER BY name <-> 'postgresql' LIMIT 5;

-- Add tsvector column (generated)
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

-- GIN index on tsvector
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

-- Search with ranking
SELECT title,
  ts_rank(search_vector, query) AS rank,
  ts_headline('english', body, query, 'MaxFragments=2') AS snippet
FROM articles, to_tsquery('english', 'postgresql & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- auto_explain: log plans for slow queries automatically
-- In postgresql.conf:
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '100ms'
-- auto_explain.log_analyze = true

-- pg_repack: online table compaction (no exclusive lock)
-- pg_repack --table=bloated_table --dbname=mydb

-- postgres_fdw: query remote database
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote-host', dbname 'analytics');
CREATE FOREIGN TABLE remote_events (
  id bigint, type text, created_at timestamptz
) SERVER remote_db OPTIONS (table_name 'events');
-- Now query remote_events as if it were local

-- ❌ Slow: COUNT for existence check
SELECT CASE WHEN COUNT(*) > 0 THEN true END
FROM orders WHERE user_id = 42;

-- ✅ Fast: EXISTS stops at first match
SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = 42);

-- ❌ Slow: function on indexed column
SELECT * FROM users WHERE EXTRACT(year FROM created_at) = 2024;

-- ✅ Fast: range query uses index directly
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ❌ Slow: row-by-row inserts
INSERT INTO events (type, data) VALUES ('click', '{}');
INSERT INTO events (type, data) VALUES ('view', '{}');
-- ... 10,000 times

-- ✅ Fast: batch insert with unnest
INSERT INTO events (type, data)
SELECT * FROM unnest(
  ARRAY['click', 'view', 'scroll'],
  ARRAY['{}', '{}', '{}']::jsonb[]
);

-- ✅ Fastest: COPY for bulk loading
COPY events (type, data) FROM '/path/to/data.csv' WITH CSV;

-- Currently running queries (find long-running ones)
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state = 'active' AND query_start < now() - interval '5 seconds'
ORDER BY duration DESC;

-- Table-level stats: sequential scans vs index scans
SELECT relname,
  seq_scan, idx_scan,
  round(idx_scan * 100.0 / nullif(seq_scan + idx_scan, 0), 1) AS idx_pct
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 100
ORDER BY seq_scan DESC
LIMIT 10;

-- Lock contention
SELECT blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks bk ON bk.locktype = bl.locktype
  AND bk.relation = bl.relation AND bk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = bk.pid
WHERE NOT bl.granted;