0xDarkMatter/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:

| 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