postgres-optimization

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL Optimization

PostgreSQL优化

Index Strategies

索引策略

sql

-- B-tree index for equality and range queries (default)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Composite index (column order matters: equality columns first, range last)
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- Partial index (smaller, faster for filtered queries)
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

-- Covering index (avoids table lookup entirely)
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name, avatar_url);

-- GIN index for JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- GiST index for full-text search
CREATE INDEX idx_articles_search ON articles USING GiST (
  to_tsvector('english', title || ' ' || body)
);

-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_large_table_col ON large_table (col);

sql

-- B-tree index for equality and range queries (default)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Composite index (column order matters: equality columns first, range last)
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- Partial index (smaller, faster for filtered queries)
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

-- Covering index (avoids table lookup entirely)
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name, avatar_url);

-- GIN index for JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- GiST index for full-text search
CREATE INDEX idx_articles_search ON articles USING GiST (
  to_tsvector('english', title || ' ' || body)
);

-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_large_table_col ON large_table (col);

Reading Query Plans

解读查询计划

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped'
  AND o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 20;

Key things to look for in the plan:

```
Seq Scan
```
on large tables indicates a missing index
```
Nested Loop
```
with high row estimates suggests missing join index
```
Sort
```
without
```
Index Scan
```
means the sort is happening in memory/disk
```
Buffers: shared hit
```
vs
```
shared read
```
shows cache efficiency

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped'
  AND o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 20;

查询计划中需要关注的关键点：

大表上出现
```
Seq Scan
```
表示缺少索引
高行数预估的
```
Nested Loop
```
意味着缺少连接索引
没有
```
Index Scan
```
的
```
Sort
```
操作表示排序在内存/磁盘中进行
```
Buffers: shared hit
```
与
```
shared read
```
的对比体现缓存效率

Partitioning

分区

sql

CREATE TABLE events (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    event_type  TEXT NOT NULL,
    payload     JSONB NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

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

-- Index on each partition (inherited automatically in PG 11+)
CREATE INDEX ON events (created_at, event_type);

Partition tables with more than 10M rows when queries consistently filter on the partition key.

sql

CREATE TABLE events (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    event_type  TEXT NOT NULL,
    payload     JSONB NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

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

-- Index on each partition (inherited automatically in PG 11+)
CREATE INDEX ON events (created_at, event_type);

当查询持续按分区键过滤时，对超过1000万行的表进行分区。

JSONB Operations

JSONB操作

sql

-- Query nested JSONB fields
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}'
  AND (metadata ->> 'price')::numeric < 500;

-- Update nested JSONB
UPDATE products
SET metadata = jsonb_set(metadata, '{stock}', to_jsonb(stock - 1))
WHERE id = 'abc';

-- Aggregate JSONB arrays
SELECT id, jsonb_array_elements_text(metadata -> 'tags') AS tag
FROM products
WHERE metadata ? 'tags';

sql

-- Query nested JSONB fields
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}'
  AND (metadata ->> 'price')::numeric < 500;

-- Update nested JSONB
UPDATE products
SET metadata = jsonb_set(metadata, '{stock}', to_jsonb(stock - 1))
WHERE id = 'abc';

-- Aggregate JSONB arrays
SELECT id, jsonb_array_elements_text(metadata -> 'tags') AS tag
FROM products
WHERE metadata ? 'tags';

Connection Pooling

连接池

ini

undefined

ini

undefined

pgbouncer.ini

[databases] app = host=localhost port=5432 dbname=app

[pgbouncer] pool_mode = transaction max_client_conn = 1000 default_pool_size = 25 min_pool_size = 5 reserve_pool_size = 5 server_idle_timeout = 300


Use transaction-level pooling for web applications. Session-level pooling for apps that use prepared statements or temp tables.

[databases] app = host=localhost port=5432 dbname=app

[pgbouncer] pool_mode = transaction max_client_conn = 1000 default_pool_size = 25 min_pool_size = 5 reserve_pool_size = 5 server_idle_timeout = 300


Web应用使用事务级连接池。对于使用预准备语句或临时表的应用，使用会话级连接池。

Common Tuning Parameters

常用调优参数

sql

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

-- Find unused indexes
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

sql

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

-- Find unused indexes
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Anti-Patterns

反模式

Creating indexes on every column instead of analyzing actual query patterns
Using
```
SELECT *
```
when only a few columns are needed
Not using
```
EXPLAIN ANALYZE
```
to verify index usage
Storing large blobs in JSONB when a separate table with proper types is better
Missing connection pooling (each connection uses ~10MB of server memory)
Running
```
VACUUM FULL
```
during peak hours (locks the entire table)

不分析实际查询模式就给每个列创建索引
只需要少数列时使用
```
SELECT *
```
不使用
```
EXPLAIN ANALYZE
```
验证索引使用情况
当单独表配合合适类型更合适时，仍在JSONB中存储大对象
未使用连接池（每个连接占用约10MB服务器内存）
高峰时段运行
```
VACUUM FULL
```
（会锁定整个表）

Checklist

检查清单

Indexes match actual query patterns (check
```
pg_stat_statements
```
)
Composite indexes ordered: equality, then sort, then range columns
```
EXPLAIN ANALYZE
```
run on all critical queries
Partial indexes used for frequently filtered subsets
Connection pooler (PgBouncer/pgcat) in front of PostgreSQL
Table partitioning considered for tables over 10M rows
Unused indexes identified and dropped
```
pg_stat_statements
```
enabled for query performance monitoring

索引与实际查询模式匹配（查看
```
pg_stat_statements
```
）
复合索引顺序正确：等值列，然后排序列，最后范围列
所有关键查询都已执行
```
EXPLAIN ANALYZE
```
对频繁过滤的子集使用部分索引
PostgreSQL前端部署连接池（PgBouncer/pgcat）
考虑对超过1000万行的表进行分区
识别并删除未使用的索引
启用
```
pg_stat_statements
```
以监控查询性能