postgres-ops（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

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:

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

Workload Profiles

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

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

Gotchas & Anti-Patterns

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.

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