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:

Red Flag	Meaning	Fix
Seq Scan on large table	No usable index	Add index matching WHERE/JOIN
actual rows >> estimated rows	Stale statistics	ANALYZE tablename
Nested Loop with high rows	O(n*m) join	Check join conditions, add index
Sort with external merge	work_mem too small	Increase work_mem for session
Buffers: shared read >> hit	Cold cache or table too large	Check shared_buffers, add covering index
Hash Batch > 1	Hash join spilling to disk	Increase work_mem

Deep dive: Load ./references/query-tuning.md for plan node reference and optimization patterns.

Workload Profiles

Setting	OLTP	OLAP	Notes
shared_buffers	25% RAM	25% RAM	Same baseline
work_mem	4-16 MB	256 MB-1 GB	OLAP needs big sorts
effective_cache_size	75% RAM	75% RAM	Planner hint
random_page_cost	1.1 (SSD)	1.1 (SSD)	Lower for SSD
max_parallel_workers_per_gather	2	4-8	OLAP benefits more
checkpoint_completion_target	0.9	0.9	Spread checkpoint I/O
wal_buffers	64 MB	64 MB	-1 for auto
maintenance_work_mem	512 MB	1-2 GB	For VACUUM, CREATE INDEX

Deep dive: Load ./references/config-tuning.md for full postgresql.conf walkthrough and extension setup.

Common Operations

Backup & Restore

# Logical backup (single database)
pg_dump -Fc dbname > backup.dump

# Restore
pg_restore -d dbname backup.dump

# Parallel backup (faster for large DBs)
pg_dump -Fc -j4 dbname > backup.dump

# Base backup for PITR
pg_basebackup -D /backup/base -Ft -Xs -P

Vacuum & Maintenance

-- Manual vacuum (reclaim space, update stats)
VACUUM (VERBOSE, ANALYZE) tablename;

-- Full vacuum (rewrites table, exclusive lock)
VACUUM FULL tablename;  -- CAUTION: locks table

-- Reindex without downtime
REINDEX INDEX CONCURRENTLY idx_name;

-- Update statistics only
ANALYZE tablename;

Monitor Key Metrics

-- Slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

-- Table bloat indicator
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Lock contention
SELECT pid, relation::regclass, mode, granted, query
FROM pg_locks JOIN pg_stat_activity USING (pid)
WHERE NOT granted;

-- Cache hit ratio (should be > 99%)
SELECT sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;

Deep dive: Load ./references/operations.md for WAL archiving, PITR, autovacuum tuning, connection pooling.

Data Types Quick Reference

Type	Use When	Example
JSONB	Semi-structured data, flexible schema	'{"tags": ["a","b"]}'::jsonb
ARRAY	Fixed-type lists	ARRAY['a','b','c']
tsrange	Time periods, scheduling	'[2024-01-01, 2024-12-31)'::tsrange
tsvector	Full-text search	to_tsvector('english', body)
uuid	Distributed IDs	gen_random_uuid()
inet/cidr	IP addresses, networks	'192.168.1.0/24'::cidr

Deep dive: Load ./references/schema-design.md for normalization, constraints, RLS, generated columns, table inheritance.

Gotchas & Anti-Patterns

Mistake	Why It's Bad	Fix
SELECT * in production	Wastes bandwidth, blocks covering index scans	List columns explicitly
Function on indexed column (WHERE UPPER(email) = ...)	Prevents index use	Expression index: CREATE INDEX ... ON (UPPER(email))
NOT IN (subquery) with NULLs	Returns no rows if subquery has NULL	Use NOT EXISTS
Missing ANALYZE after bulk load	Planner uses stale row estimates	Run ANALYZE tablename
VACUUM FULL in production	Exclusive lock on entire table	Regular VACUUM + pg_repack
LIMIT without ORDER BY	Non-deterministic results	Always pair with ORDER BY
Offset pagination on large tables	Scans and discards rows	Keyset pagination: WHERE id > last_id
Too many indexes	Slows writes, wastes space	Audit with pg_stat_user_indexes
Single shared connection pool	Contention across services	Per-service pools via pgBouncer
default_transaction_isolation = serializable	Excessive serialization failures	Keep read committed, use explicit SERIALIZABLE where needed

Row-Level Security (RLS) Quick Start

-- Enable RLS on table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy: users see only their own rows
CREATE POLICY user_isolation ON documents
    USING (owner_id = current_setting('app.current_user_id')::int);

-- Policy: admins see everything
CREATE POLICY admin_access ON documents
    USING (current_setting('app.role') = 'admin');

-- Set context per request (from app layer)
SET app.current_user_id = '42';
SET app.role = 'user';

Full-Text Search Quick Start

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

-- Index it
CREATE INDEX idx_articles_fts ON articles USING gin(search_vector);

-- Search with ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & optimization') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

LISTEN/NOTIFY

-- Publisher
NOTIFY order_events, '{"order_id": 123, "status": "shipped"}';

-- Subscriber (in psql or app)
LISTEN order_events;

-- Check for notifications (app code)
-- Python: conn.poll(); conn.notifies
-- Node: client.on('notification', callback)

Reference Files

Load these for deep-dive topics. Each is self-contained.

Reference	When to Load
./references/schema-design.md	Designing tables, choosing types, constraints, RLS policies, JSONB modeling
./references/indexing.md	Choosing index types, composite/partial/expression indexes, index maintenance
./references/query-tuning.md	Reading EXPLAIN plans, pg_stat_statements, optimizing specific query patterns
./references/operations.md	Backup/restore, WAL/PITR, vacuum tuning, monitoring, connection pooling
./references/replication.md	Streaming/logical replication, failover, partitioning, FDW
./references/config-tuning.md	postgresql.conf settings, OLTP/OLAP profiles, extension setup

See Also

• sql-ops - Vendor-neutral SQL patterns (CTEs, window functions, JOINs)
• sqlite-ops - SQLite-specific patterns and operations
• python-database-ops - SQLAlchemy ORM and async database patterns