postgres-ops

PostgreSQL Operations

Comprehensive PostgreSQL skill covering schema design through production operations.

Quick Connection

# Standard connection
psql "postgresql://user:pass@localhost:5432/dbname"

# With SSL
psql "postgresql://user:pass@host:5432/dbname?sslmode=require"

# Environment variables (libpq)
export PGHOST=localhost PGPORT=5432 PGDATABASE=mydb PGUSER=myuser PGPASSWORD=secret
psql

# Connection pooling (pgBouncer default)
psql "postgresql://user:pass@localhost:6432/dbname"

-- Check current connection
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();

-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

Index Type Selection

What query pattern are you optimizing?
│
├─ Equality (WHERE col = val)
│  └─ B-tree (default, almost always right)
│
├─ Range (WHERE col > val, ORDER BY, BETWEEN)
│  └─ B-tree
│
├─ Array/JSONB containment (@>, ?, ?|, ?&)
│  └─ GIN
│
├─ Full-text search (@@)
│  └─ GIN with tsvector
│
├─ Geometric/range overlap (&&, <->)
│  └─ GiST
│
├─ Pattern matching (LIKE '%text%', similarity)
│  └─ GIN with pg_trgm (gin_trgm_ops)
│
├─ Large table, few distinct values, append-only
│  └─ BRIN (tiny index, good for timestamps)
│
└─ Exact equality only, no range/sort needed
   └─ Hash (rare - B-tree usually better)

Quick Index Reference

Index	Best For	Size	Write Cost
B-tree	Equality, range, sort	Medium	Low
GIN	Arrays, JSONB, FTS, trigrams	Large	High
GiST	Geometry, ranges, FTS	Medium	Medium
BRIN	Correlated data (timestamps)	Tiny	Very low
Hash	Exact equality only	Medium	Low

Deep dive: Load ./references/indexing.md for composite, partial, expression, and covering index strategies.

EXPLAIN ANALYZE Workflow

-- Step 1: Run with ANALYZE and BUFFERS
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- Step 2: Read bottom-up. Find the slowest node.
-- Step 3: Check estimates vs actuals
--   actual rows=10000, rows=100  -> bad estimate, run ANALYZE
-- Step 4: Look for these red flags: