postgresql-best-practices

PostgreSQL 最佳实践

核心原则

• 利用 PostgreSQL 的高级功能，构建稳健的数据模型
• 使用 EXPLAIN ANALYZE 和恰当的索引策略优化查询
• 适当使用 PostgreSQL 原生数据类型
• 实施恰当的连接池和资源管理
• 遵循 PostgreSQL 特有的安全最佳实践

架构设计

数据类型

• 使用恰当的原生类型：UUID、JSONB、ARRAY、INET、CIDR
• 对需要时区感知的应用，优先使用 TIMESTAMPTZ 而非 TIMESTAMP
• 无需长度限制时，使用 TEXT 而非 VARCHAR
• 精确的十进制计算（如财务数据）可考虑使用 NUMERIC
• 使用 SERIAL 或 BIGSERIAL 生成自动递增 ID；分布式系统则可使用 UUID

CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customers(customer_id),
    order_data JSONB NOT NULL DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',
    total_amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

表设计

• 始终定义主键
• 使用外键，并设置恰当的 ON DELETE/UPDATE 操作
• 在适当的位置添加 NOT NULL 约束
• 使用 CHECK 约束验证数据
• 大型表可考虑进行分区

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'inactive', 'discontinued')),
    metadata JSONB DEFAULT '{}'
);

分区

• 对大型表（数百万行）使用声明式分区
• 选择恰当的分区策略：RANGE、LIST 或 HASH
• 分区后，在分区表上创建索引

CREATE TABLE events (
    event_id BIGSERIAL,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

索引策略

索引类型

• 对等值和范围查询使用 B-tree 索引（默认类型）
• 对 JSONB、数组和全文搜索使用 GIN 索引
• 对几何数据和范围类型使用 GiST 索引
• 对大型且自然有序的数据使用 BRIN 索引
• 对带筛选条件的查询可考虑使用部分索引

-- B-tree index for common lookups
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- GIN index for JSONB queries
CREATE INDEX idx_orders_data ON orders USING GIN (order_data);

-- Partial index for active records only
CREATE INDEX idx_active_products ON products(name) WHERE status = 'active';

-- Covering index to avoid table lookup
CREATE INDEX idx_orders_covering ON orders(customer_id)
    INCLUDE (order_date, total_amount);

索引维护

• 定期运行 ANALYZE 更新统计信息
• 使用 REINDEX 处理膨胀的索引
• 使用 pg_stat_user_indexes 监控索引使用情况
• 删除未使用的索引，以减少写入开销

-- Check index usage
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

查询优化

EXPLAIN ANALYZE

• 始终分析慢查询的查询计划
• 留意大型表上的顺序扫描
• 根据查询计划找出缺失的索引
• 留意行数估算值与实际行数之间的差异是否过大

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.name, COUNT(o.order_id)
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.created_at > '2024-01-01'
GROUP BY c.customer_id, c.name;

公用表表达式（CTE）

• 使用 CTE 组织复杂查询
• 注意：在较旧的 PostgreSQL 版本中，CTE 会阻止优化
• 在 PostgreSQL 12+ 中使用 MATERIALIZED/NOT MATERIALIZED 提示

WITH recent_orders AS MATERIALIZED (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders
    WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY customer_id
)
SELECT c.name, ro.order_count, ro.total_spent
FROM customers c
JOIN recent_orders ro ON c.customer_id = ro.customer_id
WHERE ro.total_spent > 1000;

窗口函数

• 在分析型查询中使用窗口函数
• 利用 PARTITION BY 和 ORDER BY 执行复杂计算

SELECT
    order_id,
    customer_id,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_rank
FROM orders;

JSONB 最佳实践

• 使用 JSONB 而非 JSON，以获得更好的性能和索引支持
• 为需要查询的 JSONB 列创建 GIN 索引
• 使用包含运算符（@>、<@）实现高效查询
• 将经常查询的字段提取到常规列中

-- Efficient JSONB query with GIN index
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- Extract specific fields
SELECT
    product_id,
    metadata->>'brand' AS brand,
    (metadata->>'rating')::numeric AS rating
FROM products
WHERE metadata ? 'rating';

连接管理

连接池

• 使用 PgBouncer 或 pgpool-II 实现连接池
• 根据工作负载设置恰当的连接池大小
• 对生命周期较短的连接使用事务池模式

连接设置

-- Recommended session settings
SET statement_timeout = '30s';
SET lock_timeout = '10s';
SET idle_in_transaction_session_timeout = '60s';

事务与锁

• 使用恰当的事务隔离级别
• 缩短事务时长，以减少锁竞争
• 使用顾问锁实现应用级锁定
• 监控并解决锁冲突

-- Use advisory locks for application coordination
SELECT pg_advisory_lock(hashtext('resource_name'));
-- Do work
SELECT pg_advisory_unlock(hashtext('resource_name'));

-- Check for blocking queries
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid;

维护

Vacuum 和 Analyze

• 启用 autovacuum，并根据工作负载调整
• 批量操作后手动运行 VACUUM ANALYZE
• 监控表膨胀情况

-- Check table bloat
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

备份策略

• 使用 pg_dump 进行逻辑备份
• 使用 pg_basebackup 进行物理备份
• 通过归档 WAL 实现时间点恢复（PITR）
• 定期测试备份恢复

安全

• 使用 SSL/TLS 建立连接
• 在多租户应用中实施行级安全（RLS）
• 使用角色以及 GRANT/REVOKE 控制访问
• 使用 pgAudit 扩展审计敏感操作

-- Enable row-level security
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY documents_tenant_policy ON documents
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Grant minimal privileges
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT USAGE ON SEQUENCE orders_order_id_seq TO app_user;

监控

• 使用 pg_stat_statements 扩展进行监控
• 跟踪慢查询并定期优化
• 为复制延迟、连接数和磁盘使用量设置警报
• 使用 pg_stat_activity 监控活动查询

-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;