PostgreSQL高级: 锁、执行计划和分区

如果您已经掌握PostgreSQL基础，那么高级PostgreSQL可以通过三个主要领域来理解：

锁和并发 — 当多个事务同时访问数据时，PostgreSQL如何处理？
执行计划 — PostgreSQL如何决定运行SQL查询，以及如何知道查询在哪里慢？
分区 — 如何将非常大的表拆分为多个部分以进行更高效的查询/管理？

1. 锁 — PostgreSQL如何锁定数据？#

想象有两个事务：

-- 事务A
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

sql

在A还没有COMMIT时，事务B运行：

BEGIN;

UPDATE accounts
SET balance = balance + 50
WHERE id = 1;

sql

B将必须等待A完成。

这是一种行级锁定形式。

`SELECT ... FOR UPDATE`#

一个非常常见的模式：

BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

-- 处理逻辑

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

COMMIT;

sql

FOR UPDATE告诉PostgreSQL：

“我准备修改这一行，不要让其他事务同时修改它。”

当有如下逻辑时非常有用：

读取 → 检查 → 修改

text

示例：

SELECT stock
FROM products
WHERE id = 10
FOR UPDATE;

sql

然后：

UPDATE products
SET stock = stock - 1
WHERE id = 10;

sql

如果没有正确锁定，两个同时请求可能都会看到：

stock = 1

text

并且都认为产品还有库存。

需要知道的锁类型#

不需要立即记住所有内容。最重要的是：

行锁#

SELECT ...
FOR UPDATE;

sql

或者：

SELECT ...
FOR SHARE;

sql

当并发处于记录级别时使用。

表锁#

示例：

LOCK TABLE accounts;

sql

在表级别锁定。

通常，应用程序代码不应该随意使用表锁，因为它们容易降低并发性。

死锁#

这是非常重要的部分。

事务A：

锁定行1
↓
等待行2

text

事务B：

锁定行2
↓
等待行1

text

我们有：

A ──锁定──> 行1
A ──等待──> 行2

B ──锁定──> 行2
B ──等待──> 行1

text

没有人能继续。

PostgreSQL将检测死锁并终止一个事务。

预防方法#

确保所有事务以相同的顺序获取锁。

例如，总是：

先锁定较小的账户
→ 锁定较大的账户

text

而不是这个事务：

账户1 → 账户2

text

和那个事务：

账户2 → 账户1

text

2. 执行计划 — PostgreSQL实际如何运行查询？#

这是优化PostgreSQL时非常重要的技能。

您有一个查询：

SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

不要只问：

“有索引吗？”

要问：

PostgreSQL实际如何执行此查询？

使用：

EXPLAIN
SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

例如，PostgreSQL可能返回：

Index Scan using users_email_idx on users
  Index Cond: (email = 'alice@example.com')

text

这意味着PostgreSQL正在使用索引。

`EXPLAIN ANALYZE`#

更重要的是：

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

EXPLAIN给您计划的执行。

EXPLAIN ANALYZE将执行查询并测量实际性能。

示例：

Index Scan using users_email_idx on users
(cost=0.42..8.44 rows=1 width=100)
(actual time=0.035..0.037 rows=1 loops=1)

text

您应该关注：

cost
rows
actual time
loops
扫描类型

顺序扫描 vs 索引扫描#

假设表有：

10,000,000 users

text

查询：

SELECT *
FROM users
WHERE id = 123;

sql

PostgreSQL可能使用：

Index Scan

text

因为只需要找一行。

但查询：

SELECT *
FROM users
WHERE country = 'Vietnam';

sql

如果表的70%是Vietnam，PostgreSQL可能选择：

Seq Scan

text

即使country有索引。

这不一定是问题。

一个常见错误：

“Seq Scan = 查询错误。”

不正确。

如果需要读取表的大部分，顺序读取有时比使用索引更便宜。

需要知道的执行节点#

至少要理解：

Seq Scan
Index Scan
Index Only Scan
Bitmap Index Scan
Nested Loop
Hash Join
Merge Join
Sort
Aggregate

text

示例：

SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id;

sql

PostgreSQL可能选择：

Nested Loop

text

或者：

Hash Join

text

或者：

Merge Join

text

取决于数据、统计信息和成本模型。

查询优化示例#

假设：

SELECT *
FROM orders
WHERE customer_id = 100
  AND created_at >= '2026-01-01';

sql

索引：

CREATE INDEX idx_orders_customer
ON orders(customer_id);

sql

如果工作负载经常查询两个条件，可能会更好：

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

sql

然后：

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 100
  AND created_at >= '2026-01-01';

sql

您想看看规划器是否利用新索引。

3. 分区 — 拆分大表#

假设：

orders

text

有：

2 billion rows

text

数据有：

created_at

text

您可以按月分区：

orders
├── orders_2026_01
├── orders_2026_02
├── orders_2026_03
├── ...
└── orders_2026_08

text

而不是一个巨大的表。

范围分区#

示例：

CREATE TABLE orders (
    id bigint,
    created_at timestamp,
    customer_id bigint,
    amount numeric
) PARTITION BY RANGE (created_at);

sql

创建分区：

CREATE TABLE orders_2026_08
PARTITION OF orders
FOR VALUES FROM ('2026-08-01')
             TO ('2026-09-01');

sql

下个月的分区：

CREATE TABLE orders_2026_09
PARTITION OF orders
FOR VALUES FROM ('2026-09-01')
             TO ('2026-10-01');

sql

分区修剪#

这是分区有用的原因。

查询：

SELECT *
FROM orders
WHERE created_at >= '2026-08-01'
  AND created_at < '2026-09-01';

sql

PostgreSQL可能只读取：

orders_2026_08

text

而不是：

orders_2026_01
orders_2026_02
orders_2026_03
...
orders_2026_08

text

这称为分区修剪。

但分区不是”魔法性能按钮”#

示例：

SELECT *
FROM orders
WHERE customer_id = 123;

sql

如果按以下分区：

created_at

text

那么PostgreSQL可能仍然必须搜索多个分区。

因此，分区键必须匹配工作负载。

分区前的一个重要问题：

“我最大的查询通常按哪列过滤？”

4. 这三个主题如何相关？#

这是重要的部分。

想象一个电子商务系统：

orders: 2 billion rows

text

您有：

SELECT *
FROM orders
WHERE customer_id = 123
  AND created_at >= '2026-01-01';

sql

您可能需要：

分区#

拆分：

orders
→ 按 created_at

text

索引#

在每个分区内：

(customer_id, created_at)

sql

执行计划#

检查：

EXPLAIN ANALYZE

sql

看看PostgreSQL是否有：

分区修剪
       ↓
索引扫描
       ↓
少行

text

5. 锁 + 执行计划也相关#

慢查询不仅让用户烦恼。

示例：

事务A
    ↓
UPDATE
    ↓
保持锁
    ↓
查询运行30秒

text

同时：

事务B
    ↓
UPDATE同一行
    ↓
等待

text

未优化的查询可能导致锁保持更长时间，从而创建：

慢查询
   ↓
长事务
   ↓
锁争用
   ↓
更多等待
   ↓
更多慢请求

text

这就是为什么在调试生产PostgreSQL时，不应该只看：

“此查询需要5秒。”

还要问：

“在这5秒期间它保持什么锁？”

6. 学习高级PostgreSQL的路线图#

如果您的目标是高级/后端工程师，我将按此顺序学习：

flowchart TD
    A[PostgreSQL] --> B[事务]
    A --> C[查询计划]
    A --> D[存储]
    
    B --> E[隔离]
    B --> F[锁]
    B --> G[死锁]
    
    C --> H[EXPLAIN]
    C --> I[索引]
    
    D --> J[MVCC]
    D --> K[VACUUM]
    D --> L[Bloat]
    
    F --> M[并发]
    I --> N[性能]
    
    M --> O[分区]
    N --> O
    O --> P[大数据集]

在您询问的三个主题之后，非常值得学习的下一个主题是：

MVCC
VACUUM / autovacuum
索引内部 — B-tree、GIN、GiST、BRIN
事务隔离
死锁调试
CTE / 物化CTE
窗口函数
查询规划器和统计信息
表/索引膨胀
连接池
复制
读取副本
分区维护

如果以实践方式学习，三元组EXPLAIN ANALYZE + 锁 + MVCC应该优先考虑，因为它们帮助您调试大多数PostgreSQL生产问题。

1. 锁 — PostgreSQL如何锁定数据？#

SELECT ... FOR UPDATE#

需要知道的锁类型#

行锁#

表锁#

死锁#

预防方法#

2. 执行计划 — PostgreSQL实际如何运行查询？#

EXPLAIN ANALYZE#

顺序扫描 vs 索引扫描#

需要知道的执行节点#

查询优化示例#

3. 分区 — 拆分大表#

范围分区#

分区修剪#

但分区不是”魔法性能按钮”#

4. 这三个主题如何相关？#

分区#

索引#

执行计划#

5. 锁 + 执行计划也相关#

6. 学习高级PostgreSQL的路线图#

参考文献#

`SELECT ... FOR UPDATE`#

`EXPLAIN ANALYZE`#