Skip to content

Pgsql

MVCC（多版本并发控制）

MVCC（Multi-Version Concurrency Control）是 PostgreSQL 提供的一种事务并发控制机制。

核心思想： 通过对数据库记录的版本进行管理，不同的事务可以访问数据的不同版本，从而避免读写冲突。

实现机制：

  • PostgreSQL 中，每条数据记录都会存储两个标识字段：xmin 和 xmax
  • xmin：表示插入该版本的事务 ID
  • xmax：表示删除该版本的事务 ID
  • 当查询数据时，PostgreSQL 会根据这些标识和当前事务的可见性规则判断哪些数据对当下事务是"可见"的
  • 通过 MVCC，读操作不会阻塞写操作，写操作也不会阻塞读操作，从而实现高效的并发控制

流复制（Streaming Replication）

PostgreSQL 流复制是一种高效的主从复制机制，它通过传输 WAL（Write Ahead Log）日志实现主服务器的数据实时同步到从服务器。

工作机制：

  1. 主服务器记录更改到 WAL 日志
  2. WAL 日志通过流复制协议发送到从服务器
  3. 从服务器将接收到的日志应用到自身的数据库实例上，从而保持与主服务器的同步状态

特点和要点：

  • 流复制允许主从服务器之间的异步或同步复制
  • 支持只读从服务器，通过从服务器处理读请求以减轻主服务器的负担

事务隔离级别

事务隔离级别用于定义一个事务在访问数据时的可见性规则。

PostgreSQL 支持以下四种隔离级别：

1. Read Uncommitted（未提交读）

  • 允许事务读取其他事务尚未提交的数据
  • PostgreSQL 的实现实际上是将其作为 Read Committed 处理

2. Read Committed（提交读）（默认级别）

  • 只能看到其他事务已提交的更改

3. Repeatable Read（可重复读）

  • 在同一个事务中，查询多次相同的数据集合，结果不会变化（即使数据被其他事务更新）

4. Serializable（可序列化）

  • 提供最高级的事务隔离，事务以串行化的方式执行，确保事务之间没有任何并发冲突，但性能受限

设置隔离级别的语句：

SET TRANSACTION ISOLATION LEVEL <level>;

分区表

分区表是一种将大表拆分为多个小表的技术，目的是提升性能（查询、数据管理等）。在 PostgreSQL 中，分区表从 v10 版本开始成为原生特性。

分区类型：

1. Range Partitioning（范围分区）

  • 按一定范围值将行数据分配到不同的分区
  • 示例：按日期范围进行分区
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    amount NUMERIC,
    sale_date DATE
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_q1 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_q2_part PARTITION OF sales
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

2. List Partitioning（列表分区）

  • 按特定列表值分配数据到不同分区
  • 示例：按区域分区
CREATE TABLE sales PARTITION BY LIST (region);

CREATE TABLE sales_east PARTITION OF sales
FOR VALUES IN ('east');

CREATE TABLE sales_west PARTITION OF sales
FOR VALUES IN ('west');

3. Hash Partitioning（哈希分区）

  • 通过对分区键的哈希计算将数据分配到分区
  • 示例：按用户 ID 分区
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
) PARTITION BY HASH (id);

CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);

查询性能优化

1. 使用索引

  • 创建合适的索引（B-Tree、GIN、GiST、BRIN 等）
  • 示例：为常用查询字段添加索引
CREATE INDEX idx_users_name ON users(name);

2. 合理的 VACUUM 和 ANALYZE

  • 定期执行 VACUUM 和 ANALYZE：清理废弃空间并更新统计信息
VACUUM ANALYZE;

3. 查看执行计划

  • 使用 EXPLAIN 或 EXPLAIN ANALYZE 分析查询计划，优化慢查询
EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'John';

4. 调整表结构

  • 考虑表分区或表分解，减少不必要的 I/O 操作

5. 调整 PostgreSQL 配置

  • 调整参数如 work_mem，maintenance_work_mem，shared_buffers 等，获得更好的性能
# 修改 postgresql.conf
shared_buffers = 512MB
work_mem = 16MB

死锁处理与避免

死锁的处理方式：

  • PostgreSQL 检测到死锁后，会随机中止其中一个事务并向客户端返回错误

避免死锁的常见方法：

1. 固定的事务处理顺序

  • 按约定的顺序请求锁定资源。例如：总是先请求表 A，再请求表 B

2. 减少锁的范围和时间

  • 确保事务中只锁定必要的资源，并尽量缩短锁定时间

3. 使用 NOWAIT 锁模式

  • 如果表已被锁定，立即返回而不是等待
SELECT * FROM my_table FOR UPDATE NOWAIT;

4. 分解事务

  • 将一个大事务拆解为多个较小的事务，降低死锁概率