postgresql

PostgreSQL 核心知识

深入了解：使用 mcp__documentation__fetch_docs，并将 technology 设为 postgresql，获取全面的文档。

表定义

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created_at DESC);

常见查询

-- Select with pagination
SELECT * FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Join
SELECT u.*, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.id = $1;

-- Aggregate
SELECT
    DATE_TRUNC('day', created_at) as day,
    COUNT(*) as count
FROM users
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day DESC;

-- Upsert
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

数据类型

类型	用途
SERIAL	自动递增 ID
UUID	唯一标识符
VARCHAR(n)	可变长度字符串
TEXT	长文本
JSONB	JSON 数据（可建立索引）
TIMESTAMP	日期和时间
BOOLEAN	真/假

性能

• 使用 EXPLAIN ANALYZE 检查查询
• 为 WHERE/JOIN 列添加索引
• 查询时优先使用 JSONB，而非 JSON
• 对筛选查询使用部分索引

不适用场景

• MySQL 专用语法 - MySQL 数据库请使用 mysql 技能（AUTO_INCREMENT、GROUP_CONCAT）
• NoSQL 操作 - 文档型/键值存储请使用 mongodb 或 redis 技能
• Oracle PL/SQL - Oracle 专用过程式代码请使用 plsql 技能
• SQL Server T-SQL - SQL Server 专用功能请使用 tsql 技能
• ORM 抽象 - 请使用特定框架的技能（Prisma、TypeORM、Spring Data JPA）

反模式

反模式	问题	解决方案
在生产环境中使用 SELECT *	传输不必要的数据，架构变更时会出错	明确指定所需列
UPDATE/DELETE 缺少 WHERE	意外修改所有行	始终包含 WHERE 子句，并使用事务
JOIN/WHERE 列缺少索引	全表扫描，查询缓慢	为频繁查询的列添加索引
在索引列上使用函数	无法使用索引：WHERE UPPER(email) = 'X'	使用函数索引或修改查询
LIKE '%pattern'	无法使用索引，需要全表扫描	使用 LIKE 'pattern%' 或全文搜索
大型表缺少 LIMIT	可能导致应用崩溃、内存问题	始终对结果分页
存储逗号分隔的值	无法高效查询，违反规范化原则	使用数组类型或关联表
缺少外键	数据完整性问题，产生孤立记录	定义适当的 FK 约束
N+1 查询问题	循环中每行执行一次查询	使用 JOIN 或批量查询
长时间运行的事务	锁定资源，阻塞其他查询	缩短事务时间，并使用适当的隔离级别

快速故障排除

问题	诊断	修复
查询缓慢	EXPLAIN ANALYZE query	添加索引、重写查询、更新统计信息
CPU 使用率高	使用 pg_stat_statements 查找慢查询	优化耗时最多的查询，添加连接池
达到连接数上限	SELECT count(*) FROM pg_stat_activity	增大 max_connections，使用 PgBouncer
锁竞争	SELECT * FROM pg_locks WHERE NOT granted	缩短事务时间，使用较低的隔离级别
磁盘空间已满	SELECT pg_size_pretty(pg_database_size('mydb'))	执行 VACUUM、归档旧数据、扩充存储空间
复制延迟	检查 pg_stat_replication	增加资源，调整检查点设置
缓存命中率 < 95%	SELECT sum(blks_hit)/sum(blks_hit+blks_read) FROM pg_stat_database	增大 shared_buffers
死元组不断累积	SELECT n_dead_tup FROM pg_stat_user_tables	执行 VACUUM，调整 autovacuum

生产就绪

安全配置

-- Create application user with limited privileges
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Row Level Security (RLS)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY users_tenant_isolation ON users
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Force SSL connections
-- In postgresql.conf:
-- ssl = on
-- ssl_cert_file = '/path/to/server.crt'
-- ssl_key_file = '/path/to/server.key'

-- Connection with SSL
psql "postgresql://user:pass@host:5432/db?sslmode=require"

连接池（PgBouncer）

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

备份与恢复

# Continuous archiving (WAL)
# postgresql.conf:
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
wal_level = replica

# Full backup with pg_basebackup
pg_basebackup -D /backup/base -Ft -Xs -P -U replication

# Point-in-time recovery (recovery.signal)
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2024-01-15 10:00:00'

性能调优

-- postgresql.conf recommendations (for 16GB RAM server)
shared_buffers = 4GB                # 25% of RAM
effective_cache_size = 12GB         # 75% of RAM
maintenance_work_mem = 1GB
work_mem = 64MB
wal_buffers = 64MB
max_connections = 200
checkpoint_completion_target = 0.9
random_page_cost = 1.1              # SSD

-- Query optimization
SET log_min_duration_statement = 1000;  -- Log queries > 1s
ANALYZE;  -- Update statistics

监控指标

指标	警报阈值
连接数	> max_connections 的 80%
缓存命中率	< 95%
复制延迟	> 1MB 或 > 10s
死元组比例	> 10%
长时间运行的事务	> 5 分钟
锁等待事件	> 10/分钟

监控查询

-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- Cache hit ratio
SELECT
    round(100 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) as cache_hit_ratio
FROM pg_stat_database;

-- Table bloat (dead tuples)
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 minutes';

-- Replication lag
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag
FROM pg_stat_replication;

高可用性

# Patroni configuration for HA
scope: postgres-cluster
name: node1

restapi:
  listen: 0.0.0.0:8008

etcd:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379

postgresql:
  listen: 0.0.0.0:5432
  data_dir: /data/postgres
  parameters:
    max_connections: 200
    shared_buffers: 4GB

检查清单

• 已启用 SSL/TLS 加密
• 用户账户遵循最小权限原则
• 已配置连接池（PgBouncer）
• 已配置 WAL 归档
• 定期使用 pg_dump 备份
• 已测试时间点恢复
• 已设置监控查询
• 已启用慢查询日志
• 已安排 VACUUM/ANALYZE
• 已配置复制（如果使用 HA）
• 已设置连接数限制
• 行级安全（如果是多租户）

参考文档

• 索引
• JSON 查询