postgres-ops

PostgreSQL 运维

涵盖从架构设计到生产运维的 PostgreSQL 全面技能。

快速连接

# 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';

索引类型选择

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)

索引快速参考

索引	最适用于	大小	写入开销
B-tree	相等查询、范围查询、排序	中等	低
GIN	数组、JSONB、FTS、三元组	大	高
GiST	几何数据、范围、FTS	中等	中等
BRIN	相关数据（时间戳）	极小	极低
Hash	仅精确相等查询	中等	低

深入了解：加载 ./references/indexing.md，了解复合、部分、表达式和覆盖索引策略。

EXPLAIN ANALYZE 工作流

-- 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:

警示信号	含义	解决方法
在大型表上执行 Seq Scan	没有可用索引	添加与 WHERE/JOIN 匹配的索引
actual rows 远大于 estimated rows	统计信息已过时	执行 ANALYZE tablename
Nested Loop 的行数很高	O(n*m) 连接	检查连接条件并添加索引
Sort 使用 external merge	work_mem 太小	为会话增大 work_mem
Buffers: shared read 远大于 hit	缓存未预热或表过大	检查 shared_buffers，添加覆盖索引
Hash Batch > 1	哈希连接溢写到磁盘	增大 work_mem

深入了解：加载 ./references/query-tuning.md，查看计划节点参考和优化模式。

工作负载配置

设置	OLTP	OLAP	说明
shared_buffers	25% RAM	25% RAM	相同基准值
work_mem	4-16 MB	256 MB-1 GB	OLAP 需要较大的排序空间
effective_cache_size	75% RAM	75% RAM	规划器提示
random_page_cost	1.1 (SSD)	1.1 (SSD)	SSD 下取值较低
max_parallel_workers_per_gather	2	4-8	OLAP 获益更多
checkpoint_completion_target	0.9	0.9	分散检查点 I/O
wal_buffers	64 MB	64 MB	-1 表示自动
maintenance_work_mem	512 MB	1-2 GB	用于 VACUUM、CREATE INDEX

深入了解：加载 ./references/config-tuning.md，查看完整的 postgresql.conf 配置指南和扩展设置。

常见操作

备份与恢复

# 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

清理与维护

-- 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;

监控关键指标

-- 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;

深入了解：加载 ./references/operations.md，了解 WAL 归档、PITR、autovacuum 调优和连接池。

数据类型快速参考

类型	使用场景	示例
JSONB	半结构化数据、灵活架构	'{"tags": ["a","b"]}'::jsonb
ARRAY	固定类型的列表	ARRAY['a','b','c']
tsrange	时间段、日程安排	'[2024-01-01, 2024-12-31)'::tsrange
tsvector	全文搜索	to_tsvector('english', body)
uuid	分布式 ID	gen_random_uuid()
inet/cidr	IP 地址、网络	'192.168.1.0/24'::cidr

深入了解：加载 ./references/schema-design.md，了解规范化、约束、RLS、生成列和表继承。

注意事项与反模式

错误	不良影响	修复方法
在生产环境中使用 SELECT *	浪费带宽，阻碍覆盖索引扫描	明确列出列名
在索引列上使用函数（WHERE UPPER(email) = ...）	导致无法使用索引	使用表达式索引：CREATE INDEX ... ON (UPPER(email))
使用含有 NULL 的 NOT IN (subquery)	如果子查询包含 NULL，则不会返回任何行	使用 NOT EXISTS
批量加载后未运行 ANALYZE	查询规划器使用过时的行数估算	运行 ANALYZE tablename
在生产环境中使用 VACUUM FULL	整张表被加排他锁	使用常规 VACUUM 和 pg_repack
使用不带 ORDER BY 的 LIMIT	结果不确定	始终与 ORDER BY 配合使用
在大型表上使用偏移分页	扫描并丢弃行	使用键集分页：WHERE id > last_id
索引过多	写入变慢，浪费空间	使用 pg_stat_user_indexes 审查索引
多个服务共用一个连接池	服务之间发生争用	通过 pgBouncer 为每个服务配置连接池
default_transaction_isolation = serializable	序列化失败过多	保持 read committed，在需要时显式使用 SERIALIZABLE

行级安全（RLS）快速入门

-- 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';

全文搜索快速入门

-- 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)

参考文件

需要深入了解相关主题时，请加载这些文件。每个文件均可独立使用。

参考文件	加载时机
./references/schema-design.md	设计表、选择类型、设置约束、RLS 策略和 JSONB 建模
./references/indexing.md	选择索引类型、复合索引／部分索引／表达式索引，以及索引维护
./references/query-tuning.md	阅读 EXPLAIN 计划、使用 pg_stat_statements、优化特定查询模式
./references/operations.md	备份／恢复、WAL／PITR、VACUUM 调优、监控和连接池
./references/replication.md	流复制／逻辑复制、故障转移、分区和 FDW
./references/config-tuning.md	postgresql.conf 设置、OLTP／OLAP 配置方案、扩展配置

另请参阅

• sql-ops - 与供应商无关的 SQL 模式（CTE、窗口函数、JOIN）
• sqlite-ops - SQLite 专属模式和操作
• python-database-ops - SQLAlchemy ORM 和异步数据库模式