在本章中，您将学习以下主题：
- 事务属性
- 事务隔离级别
- 什么是 MVCC 及其工作原理
- 保存点
- 死锁
- PostgreSQL 如何处理持久性和一致性：WAL
- VACUUM
事务简介
文档见这里。
事务是所有数据库系统的基本概念。事务的核心在于它将多个步骤捆绑成一个“全有或全无”的操作（原子性）。步骤之间的中间状态对其他并发事务不可见（隔离性），并且即使发生某些故障导致事务无法完成，所有步骤也不会对数据库产生任何影响（原子性）。
一致性和持久性隐藏在整体语义中。
书中的这段话总结更为简洁：
总而言之，ACID 属性意味着数据库必须能够处理整个工作单元（原子性），以永久的方式存储数据（持久性），而不会对数据进行混合更改（一致性），并且以并发操作的方式执行，就好像它们是单独执行的一样（隔离性）。
一些重要概念：
- 每个事务都会被自动分配一个唯一编号，简称xid（transaction identifier）。
- PostgreSQL 将生成和/或修改某个元组的 xid 存储在该元组本身中。
-- https://www.postgresql.org/docs/current/ddl-system-columns.html
-- xmin is the identity (transaction ID) of the inserting transaction for this row version.
postgres=# select xmin, * from empsalary;
xmin | id | depname | empno | salary | enroll_date
------+----+-----------+-------+--------+-------------
782 | 10 | develop | 11 | 5200 | 2007-08-01
782 | 11 | develop | 7 | 4200 | 2006-06-01
782 | 12 | develop | 9 | 4500 | 2008-01-01
782 | 13 | develop | 8 | 6000 | 2006-10-01
782 | 14 | develop | 10 | 5200 | 2006-08-01
782 | 15 | personnel | 5 | 3500 | 2007-12-10
782 | 16 | personnel | 2 | 3900 | 2006-10-15
819 | 17 | sales | 3 | 4800 | 2007-08-01
819 | 18 | sales | 1 | 5000 | 2006-10-01
819 | 19 | sales | 4 | 4800 | 2007-08-01
(10 rows)
- 可通过函数 txid_current() 检查当前交易。
postgres=# SELECT current_time, txid_current();
current_time | txid_current
--------------------+--------------
23:59:01.737195+00 | 1795
(1 row)
隐式事务和显式事务的比较
隐式事务:
PostgreSQL 实际上将每条 SQL 语句都视为在一个事务中执行。如果您未发出 BEGIN 命令，则每个单独的语句都会被隐式地包裹在 BEGIN 和 COMMIT 语句（如果成功）。
PostgreSQL executes transactions in “autocommit” mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).
显式事务
一组被 BEGIN 和 COMMIT 包裹的语句有时被称为事务块。
隐式事务示例：
create table tab01(a int);
insert into tab01 values(1);
insert into tab01 values(2);
insert into tab01 values(3),(4);
postgres=# select xmin, * from tab01;
xmin | a
------+---
1797 | 1
1798 | 2
1799 | 3
1799 | 4
(4 rows)
显式事务示例：
truncate table tab01;
begin;
insert into tab01 values(1);
insert into tab01 values(2);
insert into tab01 values(3),(4);
commit;
postgres=# select xmin, * from tab01;
xmin | a
------+---
1801 | 1
1801 | 2
1801 | 3
1801 | 4
(4 rows)
在事务执行期间，提示符会加一个星号：
postgres=# begin;
BEGIN
postgres=*# commit;
COMMIT
postgres=#
出错时，事务可能会被PG中断：
postgres=# select * from tab01;
a
---
1
2
3
4
(4 rows)
postgres=# begin;
BEGIN
postgres=*# insert into tab01 values(5);
INSERT 0 1
postgres=*# insert into tab01 values('6');
INSERT 0 1
postgres=*# insert into tab01 values('abc');
ERROR: invalid input syntax for type integer: "abc"
LINE 1: insert into tab01 values('abc');
^
postgres=!# insert into tab01 values(7);
ERROR: current transaction is aborted, commands ignored until end of transaction block
postgres=!# commit;
ROLLBACK
postgres=# select * from tab01;
a
---
1
2
3
4
(4 rows)
中断事务的原因可能是错误的值，不满足约束，错误的语法等，此时PG会主动发出rollback命令。
既然每个SQL语句都在其自己单独的隐含事务中执行。那么多个语句如果需要整体成功或失败，则需要使用显式事务。
事务内时间
交易是时间离散的（time-discrete）：交易过程中时间不会改变。
postgres=# begin;
BEGIN
postgres=*# select current_time;
current_time
--------------------
00:34:45.987817+00
(1 row)
postgres=*# select pg_sleep(5);
pg_sleep
----------
(1 row)
postgres=*# select current_time;
current_time
--------------------
00:34:45.987817+00
(1 row)
postgres=*# commit;
COMMIT
根据文档，以下这些 SQL 标准函数的返回值均基于当前事务的开始时间：
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)
PostgreSQL 还提供了一些函数，用于返回当前语句的开始时间以及调用函数时的实际当前时间。非 SQL 标准时间函数的完整列表如下：
statement_timestamp()
clock_timestamp()
timeofday()
例如：
postgres=# begin;
BEGIN
postgres=*# select current_time, transaction_timestamp()
, statement_timestamp()
, clock_timestamp()
, timeofday()
, now();
current_time | transaction_timestamp | statement_timestamp | clock_timestamp | timeofday | now
--------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------------+-----------------
--------------
00:47:37.986481+00 | 2025-06-23 00:47:37.986481+00 | 2025-06-23 00:47:43.613214+00 | 2025-06-23 00:47:43.613374+00 | Mon Jun 23 00:47:43.613374 2025 UTC | 2025-06-23 00:47
:37.986481+00
(1 row)
postgres=*# select pg_sleep(5);
pg_sleep
----------
(1 row)
postgres=*# select current_time, transaction_timestamp()
, statement_timestamp()
, clock_timestamp()
, timeofday()
, now();
current_time | transaction_timestamp | statement_timestamp | clock_timestamp | timeofday | now
--------------------+-------------------------------+------------------------------+-------------------------------+-------------------------------------+------------------
-------------
00:47:37.986481+00 | 2025-06-23 00:47:37.986481+00 | 2025-06-23 00:47:56.58665+00 | 2025-06-23 00:47:56.586723+00 | Mon Jun 23 00:47:56.586723 2025 UTC | 2025-06-23 00:47:
37.986481+00
(1 row)
postgres=*# commit;
COMMIT
总之，在文档中，说明了函数是基于start of current transaction，还是start of current statement。
更多关于事务标识符的信息——XID 环绕问题
xid是一个32 bit的整数，因此最多支持232。
💡 xid是递增分配的，较大的xid意味着更完的事务时间。
但当xid到达最大值时，又会循环往复使用（即transaction ID wraparound，从3开始，1和2内部使用）。为保持以上的规则，VACUUM会自动将之前的事务标记为“冻结”，即表示冻结事务的发生事件均在当前事务xid之前。
当前事务id快耗尽时，系统会发出警告：
WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "mydb".
如果忽略这些警告，则在距离xid耗尽仅剩不到 100 万笔交易时，系统将关闭并拒绝执行任何新交易。
ERROR: database is shut down to avoid wraparound data loss in database "mydb"
HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb".
虚拟和真实事务标识符
为避免浪费xid，事务最初仅获得虚拟xid，直到有必要时才会分配真实xid。例如，只读负载不会消耗xid，事务中出现写负载时才会分配真实xid。
postgres=# begin;
BEGIN
postgres=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
postgres=*# select count(*) from empsalary;
count
-------
10
(1 row)
postgres=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
postgres=*# delete from empsalary;
DELETE 10
postgres=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
1803
(1 row)
postgres=*# select txid_current();
txid_current
--------------
1803
(1 row)
postgres=*# rollback;
ROLLBACK
postgres=# select count(*) from empsalary;
count
-------
10
(1 row)
多版本并发控制
即MVCC。文档见这里。
💡多版本并发控制（MVCC）是一种在多用户环境中提高数据库性能的先进技术。
💡 多版本和锁模型之间的主要区别在于，在 MVCC 中，查询（读取）数据所获取的锁与写入数据所获取的锁不冲突，因此读取永远不会阻塞写入，写入也永远不会阻塞读取。
MVCC是通过快照实现的。快照本质上是事务 xid 的范围，它定义了当前事务可用的数据范围：数据库中标记为该范围内 xid 的每一行都将对当前事务可见和可用。换句话说，每个事务都只能看到数据库中所有可用数据的一个子集。
例如，更新表中一条数据时，PG不会做就地更新（in-place update），而是会保留老的数据，然后插入一条新数据。
特殊函数 txid_current_snapshot() 返回定义当前事务时间边界的最小和最大事务标识符。
接下来看一个示例，需要两个会话，我们将提示分别设为Session 1和Session 2。
先在会话1中执行：
demo=# \set PROMPT1 '[Session 1]@%/ %# '
[Session 1]@demo # \d tab01
Table "demo.tab01"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
[Session 1]@demo # select * from tab01;
a
---
1
2
(2 rows)
[Session 1]@demo # begin;
BEGIN
[Session 1]@demo # SELECT txid_current(), txid_current_snapshot();
txid_current | txid_current_snapshot
--------------+-----------------------
1804 | 1804:1804:
(1 row)
[Session 1]@demo # update tab01 set a=100;
UPDATE 2
[Session 1]@demo # select * from tab01;
a
-----
100
100
(2 rows)
然后切换到会话2：
demo=# \set PROMPT1 '[Session 2]@%/ %# '
[Session 2]@demo # select * from tab01;
a
---
1
2
(2 rows)
[Session 2]@demo # insert into tab01 values(999);
INSERT 0 1
[Session 2]@demo # select xmin, * from tab01;
xmin | a
------+-----
1773 | 1
1778 | 2
1805 | 999
(3 rows)
再回到会话1：
[Session 1]@demo # SELECT txid_current(), txid_current_snapshot();
txid_current | txid_current_snapshot
--------------+-----------------------
1804 | 1804:1806:
(1 row)
[Session 1]@demo # select xmin, * from tab01;
xmin | a
------+-----
1804 | 100
1804 | 100
1805 | 999
(3 rows)
[Session 1]@demo # commit;
COMMIT
此时在会话2中可以看到所有提交的数据：
[Session 2]@demo # select xmin, * from tab01;
xmin | a
------+-----
1804 | 100
1804 | 100
1805 | 999
(3 rows)
MVCC尽可能避免锁，但不能完全避免，例如两个事务同时修改表中相同行时。
由于要维护多个版本，MVCC会产生存储开销。VACUUM后天进程会自动清除不需要的快照（即没有事务引用行的xid时）。
事务隔离级别
隔离级参考文档中的下表（按最宽松到最严格的顺序）：
相关语句：
postgres=# \h BEGIN TRANSACTION
Command: BEGIN
Description: start a transaction block
Syntax:
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
where transaction_mode is one of:
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
[ NOT ] DEFERRABLE
URL: https://www.postgresql.org/docs/16/sql-begin.html
postgres=# \h SET TRANSACTION ISOLATION
Command: SET TRANSACTION
Description: set the characteristics of the current transaction
Syntax:
SET TRANSACTION transaction_mode [, ...]
SET TRANSACTION SNAPSHOT snapshot_id
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]
where transaction_mode is one of:
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
[ NOT ] DEFERRABLE
URL: https://www.postgresql.org/docs/16/sql-set-transaction.html
未提交读（Read Uncommitted）
允许脏读，PG不支持此隔离级。可以显式设置此隔离级，但会自动转为READ COMMITTED。
已提交读（Read Committed）
默认隔离级，防止脏读。
可重复读（Repeatable Read）
可重复读隔离级别仅查看事务开始前已提交的数据；它永远不会查看未提交的数据或事务执行期间并发事务提交的更改。（但是，每个查询都会看到在其自身事务中执行的先前更新的效果，即使这些更新尚未提交。）
此级别与“Read Committed”级别不同，因为可重复读事务中的查询看到的快照是事务中第一个非事务控制语句开始时的快照，而不是事务中当前语句开始时的快照。因此，单个事务中的连续 SELECT 命令看到的是相同的数据，也就是说，它们看不到在自身事务开始后提交的其他事务所做的更改。
可序列化（Serializable）
可序列化隔离级别提供最严格的事务隔离。此级别模拟所有已提交事务的串行执行；就好像事务是串行执行的，而不是并发执行的。
以下示例使用两个会话。
-- Session 1
postgres=# \set PROMPT1 '[Session 1]@%/ %# '
[Session 1]@postgres # select * from tab01;
a
---
1
2
3
4
(4 rows)
[Session 1]@postgres # BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
[Session 1]@postgres # update tab01 set a=a*100 where a=1;
UPDATE 1
-- Session 2
postgres=# \set PROMPT1 '[Session 2]@%/ %# '
[Session 2]@postgres # select * from tab01;
a
---
1
2
3
4
(4 rows)
[Session 2]@postgres # BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
[Session 2]@postgres # update tab01 set a=a*100 where a=2;
UPDATE 1
-- Session 1
[Session 1]@postgres # commit;
COMMIT
-- Session 2
[Session 2]@postgres # commit;
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.
[Session 2]@postgres # select * from tab01;
a
-----
2
3
4
100
(4 rows)
会话2提交失败，尽管其修改的是另一条数据。如果会话2先提交则会成功，则会话1提交会失败。
解释 MVCC
参看文档，和MVCC相关的4个系统隐含列：
- xmin：此行版本的插入事务的标识（事务 ID）。（行版本是行的独立状态；每次更新一行都会为同一逻辑行创建一个新的行版本。）
- xmax：删除事务的标识（事务 ID），对于未删除的行版本则为零。在可见的行版本中，此列可能为非零值。这通常表示删除事务尚未提交，或者尝试删除的操作已被回滚。
- cmin：插入事务内的命令标识符（从零开始）。
- cmax：删除事务内的命令标识符或零。
cmin和cmax是事务内命令的标识符。
示例：
demo=# truncate table tab01;
TRUNCATE TABLE
demo=# \d tab01
Table "demo.tab01"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
demo=# insert into tab01 values(1),(2),(3),(4);
INSERT 0 4
demo=# SELECT *, xmin, xmax, cmin, cmax, txid_current() from tab01;
a | xmin | xmax | cmin | cmax | txid_current
---+------+------+------+------+--------------
1 | 1811 | 0 | 0 | 0 | 1812
2 | 1811 | 0 | 0 | 0 | 1812
3 | 1811 | 0 | 0 | 0 | 1812
4 | 1811 | 0 | 0 | 0 | 1812
(4 rows)
demo=# begin;
BEGIN
demo=*# SELECT *, xmin, xmax, cmin, cmax, txid_current() from tab01;
a | xmin | xmax | cmin | cmax | txid_current
---+------+------+------+------+--------------
1 | 1811 | 0 | 0 | 0 | 1813
2 | 1811 | 0 | 0 | 0 | 1813
3 | 1811 | 0 | 0 | 0 | 1813
4 | 1811 | 0 | 0 | 0 | 1813
(4 rows)
demo=*# insert into tab01 values(5);
INSERT 0 1
demo=*# insert into tab01 values(6);
INSERT 0 1
-- cmin从0开始。
-- 第5和第6行分别是事务中的第1和第2个insert语句，因此cmin依次为0和1
-- xmin和xman很好理解，不解释了
demo=*# SELECT *, xmin, xmax, cmin, cmax, txid_current() from tab01;
a | xmin | xmax | cmin | cmax | txid_current
---+------+------+------+------+--------------
1 | 1811 | 0 | 0 | 0 | 1813
2 | 1811 | 0 | 0 | 0 | 1813
3 | 1811 | 0 | 0 | 0 | 1813
4 | 1811 | 0 | 0 | 0 | 1813
5 | 1813 | 0 | 0 | 0 | 1813
6 | 1813 | 0 | 1 | 1 | 1813
(6 rows)
-- 使用cursor以保存当时的快照
demo=*# declare tab01_cursor cursor for SELECT *, xmin, xmax, cmin, cmax, txid_current() from tab01;
DECLARE CURSOR
demo=*# delete from tab01 where a%2 != 0;
DELETE 3
demo=*# SELECT *, xmin, xmax, cmin, cmax, txid_current() from tab01;
a | xmin | xmax | cmin | cmax | txid_current
---+------+------+------+------+--------------
2 | 1811 | 0 | 0 | 0 | 1813
4 | 1811 | 0 | 0 | 0 | 1813
6 | 1813 | 0 | 1 | 1 | 1813
(3 rows)
-- 被删除的元组的 cmax 值为 2，表示这些元组是从事务中的第三个写入命令中删除的。
-- 但第五行的cmax为何是0而非2？
-- cmin 和 cmax 具有相同的值，这是因为这些字段与同一个存储重叠？
demo=*# fetch all from tab01_cursor;
a | xmin | xmax | cmin | cmax | txid_current
---+------+------+------+------+--------------
1 | 1811 | 1813 | 2 | 2 | 1813
2 | 1811 | 0 | 0 | 0 | 1813
3 | 1811 | 1813 | 2 | 2 | 1813
4 | 1811 | 0 | 0 | 0 | 1813
5 | 1813 | 1813 | 0 | 0 | 1813
6 | 1813 | 0 | 1 | 1 | 1813
(6 rows)
demo=*# commit;
COMMIT
demo=# fetch all from tab01_cursor;
ERROR: cursor "tab01_cursor" does not exist
demo=# SELECT *, xmin, xmax, cmin, cmax, txid_current() from tab01;
a | xmin | xmax | cmin | cmax | txid_current
---+------+------+------+------+--------------
2 | 1811 | 0 | 0 | 0 | 1814
4 | 1811 | 0 | 0 | 0 | 1814
6 | 1813 | 0 | 1 | 1 | 1814
(3 rows)
保存点
保存点（savepoint ）是事务内部的特殊标记，允许回滚在建立之后执行的所有命令，将事务状态恢复到保存点时的状态。
以下为官网的示例：
truncate table tab01;
BEGIN;
INSERT INTO tab01 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO tab01 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO tab01 VALUES (3);
COMMIT;
上述事务将插入值 1 和 3，但不会插入 2。
要建立并随后销毁保存点：
BEGIN;
INSERT INTO tab01 VALUES (3);
SAVEPOINT my_savepoint;
INSERT INTO tab01 VALUES (4);
RELEASE SAVEPOINT my_savepoint;
COMMIT;
死锁
按照文档，死锁（deadlock）指两个（或多个）事务各自持有对方想要的锁。PostgreSQL 会自动检测死锁情况，并通过中止其中一个事务来解决，从而允许其他事务完成。（具体哪个事务会被中止很难预测，因此不应依赖它。）
💡 防止死锁的最佳方法通常是确保使用数据库的所有应用程序以一致的顺序获取多个对象的锁。
PostgreSQL 如何处理持久性和一致性：WAL
官网中的描述非常重要和清晰，附原文如下：
Write-Ahead Logging (WAL) is a standard method for ensuring data integrity. Briefly, WAL’s central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after WAL records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the WAL records. (This is roll-forward recovery, also known as REDO.)
预写日志 (WAL) 是确保数据完整性的标准方法。简而言之，WAL 的核心概念是，对数据文件（表和索引所在的文件）的更改必须在记录更改之后写入，也就是说，在描述更改的 WAL 记录刷新到永久存储之后。如果我们遵循此过程，则无需在每次事务提交时都将数据页刷新到磁盘，因为我们知道，即使发生崩溃，我们也能够使用日志恢复数据库：任何尚未应用于数据页的更改都可以通过 WAL 记录重做。（这就是前滚恢复，也称为 REDO。）
Using WAL results in a significantly reduced number of disk writes, because only the WAL file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. The WAL file is written sequentially, and so the cost of syncing the WAL is much less than the cost of flushing the data pages. This is especially true for servers handling many small transactions touching different parts of the data store. Furthermore, when the server is processing many small concurrent transactions, one fsync of the WAL file may suffice to commit many transactions.
使用 WAL 可以显著减少磁盘写入次数，因为只需将 WAL 文件刷新到磁盘即可确保事务提交，而无需刷新事务更改的每个数据文件。WAL 文件是顺序写入的，因此同步 WAL 的开销远低于刷新数据页的开销。对于处理大量涉及数据存储不同部分的小型事务的服务器来说尤其如此。此外，当服务器处理许多小型并发事务时，一次 WAL 文件的 fsync 操作就足以提交许多事务。
WAL also makes it possible to support on-line backup and point-in-time recovery. By archiving the WAL data we can support reverting to any time instant covered by the available WAL data: we simply install a prior physical backup of the database, and replay the WAL just as far as the desired time. What’s more, the physical backup doesn’t have to be an instantaneous snapshot of the database state — if it is made over some period of time, then replaying the WAL for that period will fix any internal inconsistencies.
WAL 使在线备份和时间点恢复成为可能。通过归档 WAL 数据，我们可以支持恢复到可用 WAL 数据所涵盖的任何时间点：我们只需安装数据库的先前物理备份，然后重放 WAL 到所需的时间点即可。此外，物理备份不必是数据库状态的即时快照——如果它是在某个时间段内创建的，那么重放该时间段的 WAL 将修复任何内部不一致问题。
WAL
WAL内部原理详见文档。
💡 每个表和索引都存储为固定大小的页面数组（通常为 8 kB，但在编译服务器时可以选择不同的页面大小）。
💡 WAL 是自动启用的；管理员无需采取任何行动，只需确保满足 WAL 文件的磁盘空间要求，并进行必要的调整即可。
💡 WAL 文件存储在数据目录下的 pg_wal 目录中，作为一组段文件，通常每个段文件大小为 16 MB。每个段被分成多个页面，通常每个页面 8 kB。
💡 如果 WAL 与主数据库文件位于不同的磁盘上，则效果会更好。这可以通过将 pg_wal 目录移动到其他位置（当然，在服务器关闭的情况下）并在主数据目录中创建从原始位置到新位置的符号链接来实现。
WAL文件名为24字节，以8字节为界，分别表示Timeline ID，LSN 的高 32 位，LSN 的低 32 位：
$ ls -lh $PGDATA/pg_wal
total 96M
-rw-------. 1 postgres postgres 16M Jun 23 08:04 000000010000000000000008
-rw-------. 1 postgres postgres 16M Jun 21 13:47 000000010000000000000009
-rw-------. 1 postgres postgres 16M Jun 21 13:47 00000001000000000000000A
-rw-------. 1 postgres postgres 16M Jun 21 13:47 00000001000000000000000B
-rw-------. 1 postgres postgres 16M Jun 21 13:47 00000001000000000000000C
-rw-------. 1 postgres postgres 16M Jun 22 02:27 00000001000000000000000D
WAL 作为崩溃时的救援方法
崩溃即crash，可能导致数据文件不一致，因此数据库启动时需要重放WAL文件，即做rollback和roll forward。
检查点
根据文档，检查点是预写日志序列中的一个点，在该点处，所有数据文件都已更新，以反映日志中的信息。所有数据文件都将刷新到磁盘。
这里说得更详细：
检查点是事务序列中的一些点，在这些点上，可以保证堆和索引数据文件已使用在该检查点之前写入的所有信息进行更新。在检查点时刻，所有脏数据页都会被刷新到磁盘，并且一条特殊的检查点记录会写入 WAL 文件。（更改记录之前已刷新到 WAL 文件中。）如果发生崩溃，崩溃恢复过程会查看最新的检查点记录，以确定 WAL 中的哪个点（称为重做记录）应该启动 REDO 操作。在该点之前对数据文件所做的任何更改都保证已在磁盘上。因此，在检查点之后，包含重做记录的 WAL 段之前的 WAL 段不再需要，可以回收或删除。（进行 WAL 归档时，必须先归档 WAL 段，然后才能回收或删除 WAL 段。）
将所有脏数据页刷新到磁盘的检查点要求可能会导致大量 I/O 负载。不过检查点实现了两个节省：
- 节省了系统崩溃时所需的恢复时间
- 节省了WAL日志占用的磁盘空间（因可以回收或删除）
检查点配置参数
详见这里。
何时做系统自动检查点依赖以下参数，只要满足一个就会做检查点：
- checkpoint_timeout，默认5分钟，必须大于30秒
- max_wal_size，允许 WAL 增长的最大大小（软限制），默认1GB
也可以手动做检查点。
postgres=# show checkpoint_timeout;
checkpoint_timeout
--------------------
5min
(1 row)
postgres=# show max_wal_size;
max_wal_size
--------------
1GB
(1 row)
postgres=# SELECT name, setting, unit
FROM pg_settings
WHERE name IN ( 'checkpoint_timeout', 'max_wal_size' );
name | setting | unit
--------------------+---------+------
checkpoint_timeout | 300 | s
max_wal_size | 1024 | MB
(2 rows)
检查点限流
检查点要求将所有脏数据页刷新到磁盘，这可能会导致大量的 I/O 负载。因此，检查点活动会受到限流（throttled ），以便 I/O 在检查点启动时开始，并在下一个检查点启动之前完成；这最大限度地减少了检查点期间的性能下降。
checkpoint_completion_target 参数可实现检查点限流。
指定检查点完成的目标，以检查点之间总时间的百分比表示。默认值为 0.9，表示检查点几乎覆盖了所有可用间隔，从而提供相当一致的 I/O 负载，同时也留出一些时间用于检查点完成的开销。不建议降低此参数，因为这会导致检查点完成得更快。这会导致检查点期间的 I/O 速率更高，而在检查点完成和下一个计划的检查点之间，I/O 速率会降低。
postgres=# show checkpoint_completion_target;
checkpoint_completion_target
------------------------------
0.9
(1 row)
手动发出检查点
CHECKPOINT 命令会在发出后立即强制执行检查点，而无需等待系统调度的常规检查点。CHECKPOINT 不适用于正常操作。
以下情况可能需要手工检查点：
- 计划维护 / 停机前
- 备份前（物理备份）
- 大量批量写操作后
- 调试或性能实验
VACUUM
根据这里和这里。简单来说，VACCUM实现垃圾收集并选择性地分析数据库，详细来说，VACUUM 可以：
- 恢复或重用已更新或已删除行占用的磁盘空间。
- 更新 PostgreSQL 查询规划器使用的数据统计信息。
- 更新可见性映射，以加快仅索引扫描的速度。
- 防止因事务 ID 回绕或多事务 ID 回绕而丢失非常旧的数据。
手动 VACUUM
3种类型：
- 普通型，不带参数。默认。
- FULL，回收更多空间，但耗时更长，并且会独占锁定表。此方法还需要额外的磁盘空间。
- FREEZE，选择积极的“冻结”元组。
要了解普通VACCUM和FULL VACCUM的区别，请参见下例，参考了原书。
首先需要禁止autovacuum，需要重启PG。后面记得改回来。此过程略。
postgres=# show autovacuum;
autovacuum
------------
off
(1 row)
以下为测试全过程，重点看注释：
$ psql demo
psql (16.9)
Type "help" for help.
-- 创建测试表
demo=# create table test(a varchar(16));
CREATE TABLE
-- 插入10万行
demo=# insert into test select 'AAAAAAAAAABBBBBB' FROM generate_series( 1, 100000);
INSERT 0 100000
-- 因为autovacuum被禁，因此需要手工分析获取表信息。
-- 测试表约5M，占637数据页
demo=# ANALYZE test;
ANALYZE
demo=# SELECT relname, reltuples, relpages, pg_size_pretty( pg_relation_size( 'test' ) )
FROM pg_class WHERE relname = 'test' AND relkind = 'r';
relname | reltuples | relpages | pg_size_pretty
---------+-----------+----------+----------------
test | 100000 | 637 | 5096 kB
(1 row)
-- 全部更新
demo=# update test set a = 'aaaaaaaaaabbbbbb';
UPDATE 100000
-- 由于MVCC，数据页面和空间翻倍
demo=# ANALYZE test;
ANALYZE
demo=# SELECT relname, reltuples, relpages, pg_size_pretty( pg_relation_size( 'test' ) )
FROM pg_class WHERE relname = 'test' AND relkind = 'r';
relname | reltuples | relpages | pg_size_pretty
---------+-----------+----------+----------------
test | 100000 | 1274 | 10192 kB
(1 row)
-- 普通VACCUM
demo=# VACUUM VERBOSE test;
INFO: vacuuming "demo.demo.test"
INFO: finished vacuuming "demo.demo.test": index scans: 0
pages: 0 removed, 1274 remain, 1274 scanned (100.00% of total)
tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 1826, which was 0 XIDs old when operation ended
new relfrozenxid: 1824, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 637 pages from table (50.00% of total) had 100000 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 41.350 MB/s
buffer usage: 2556 hits, 0 misses, 290 dirtied
WAL usage: 2549 records, 1 full page images, 549162 bytes
system usage: CPU: user: 0.05 s, system: 0.00 s, elapsed: 0.05 s
VACUUM
-- 空间和页面看上去没有变化
demo=# ANALYZE test;
ANALYZE
demo=# SELECT relname, reltuples, relpages, pg_size_pretty( pg_relation_size( 'test' ) )
FROM pg_class WHERE relname = 'test' AND relkind = 'r';
relname | reltuples | relpages | pg_size_pretty
---------+-----------+----------+----------------
test | 100000 | 1274 | 10192 kB
(1 row)
-- 但实际上，普通VACCUM缺省释放了这些dead rows，只是没有recalim而已。
--
-- 再次更新，和前面不同，并没有分配新的空间，因为这些释放的空间可以容纳新的数据
demo=# update test set a = '0000000000123456';
UPDATE 100000
demo=# ANALYZE test;
ANALYZE
demo=# SELECT relname, reltuples, relpages, pg_size_pretty( pg_relation_size( 'test' ) )
FROM pg_class WHERE relname = 'test' AND relkind = 'r';
relname | reltuples | relpages | pg_size_pretty
---------+-----------+----------+----------------
test | 100000 | 1274 | 10192 kB
(1 row)
-- FULL VACCUM则不同，释放并重新声明了不需要的空间，并使页面紧致
demo=# VACUUM FULL VERBOSE test;
INFO: vacuuming "demo.test"
INFO: "demo.test": found 100000 removable, 100000 nonremovable row versions in 1274 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.16 s, system: 0.01 s, elapsed: 0.20 s.
VACUUM
demo=# ANALYZE test;
ANALYZE
demo=# SELECT relname, reltuples, relpages, pg_size_pretty( pg_relation_size( 'test' ) )
FROM pg_class WHERE relname = 'test' AND relkind = 'r';
relname | reltuples | relpages | pg_size_pretty
---------+-----------+----------+----------------
test | 100000 | 637 | 5096 kB
(1 row)
自动 VACUUM
自动 VACUUM是系统自动进行的维护操作，默认启用：
postgres=# show autovacuum;
autovacuum
------------
on
(1 row)
这是一个守护进程：
$ ps -ef|grep vacuum
postgres 11879 11873 0 10:55 ? 00:00:00 postgres: autovacuum launcher
和autovacuum相关的参数见这里。
postgres=# SELECT name, setting FROM pg_settings
WHERE name like '%autovacuum%';
name | setting
---------------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 3
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 60
autovacuum_vacuum_cost_delay | 2
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_insert_scale_factor | 0.2
autovacuum_vacuum_insert_threshold | 1000
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
log_autovacuum_min_duration | 600000
(15 rows)
简而言之，PG会自动决定何时做，做多少，你无需操心。
验证你的知识
- 什么是事务？
- 什么是 xid， 它会引起什么问题？
- 什么是MVCC？
- 什么是 WAL， 为什么重要？
- 什么是检查点？