【翻译】PostgreSQL中MVCC和VACUUM 的理解

本文翻译自 <PostgreSQL_13_Cookbook>一书中的 " MVCC implementation and VACUUM in PostgreSQL "，易于初学者理解MVCC相关知识

  与Oracle和mysql类似的关系数据库相比，PostgreSQL中的MVCC实现是唯一的。MVCC的意思是多版本并发控制。对于数据库的完整性来说，在运行事务时需要MVCC来支持一致性，这样读取和写入就不会相互阻塞。

  为了更好地理解它，考虑一个假设的情况，事务A在上午9点开始，来获得表中所有记录的总数： foo.bar ( 10,000,020 条记录).由于这是一张非常大的表，我们就认为它20分钟完成。另一个事务B在上午9:10开始从同一表中删除20条记录。事务A在上午9点开始，上午9点20结束，它始终应该看到与9点相同的记录数，1000020条记录，无需考虑到事务B在上午九点10分删除了一些记录。尽管这个行为总是依赖于隔离级别，但它仍然能够提供数据的一致视图来了解查询实际运行时的情况。它是如何工作的?内部发生了什么?我们将在这个文章中讨论。

Getting ready

  Oracle和mysql数据库有单独的UNDO存储，用于存储所需的一致性的过去映像。如果一个表的现有记录被修改(更新或删除)，过去的映像将被复制到一个单独的位置。这样，如果有一个现有的事务在记录被修改之前开始，它仍然可以像在它被修改之前访问到记录。但是，这个UNDO是在一个单独的位置维护的，而不是在同一个表中。

  在PostgreSQL中，UNDO在它自己的表中维护。这意味着修改前的元组和修改后的元组都存储在同一个表中。

How to do it…

  在接下来的步骤中，我们将通过详细解释一些系统列来了解PostgreSQL是如何实现MVCC的。我们还将考虑一个简单的示例，在该示例中，我们创建一个有两列的表，插入一些记录，并查看分配给这些记录的事务id。然后，我们将查询系统列，如xmin和xmax，并了解如何在同一个表中维护多个版本的行。这个练习不仅将帮助您理解MVCC，还将向您展示一些在日常管理生活中有用的常见查询：

1. 创建一个schema和一张两列的表并且插入一些数据

postgres=# CREATE SCHEMA foo; 
CREATE SCHEMA 
postgres=# CREATE TABLE foo.bar (id int, name varchar(5)); 
CREATE TABLE 
postgres=# INSERT INTO foo.bar VALUES (generate_series(1,5),'avi'); 
INSERT 0 5 

2. 查询pg_attribute表，查看添加到表中的系统列以及两个列id和name:

postgres=# SELECT attname, format_type (atttypid,atttypmod) FROM pg_attribute 
WHERE attrelid = 'foo.bar'::regclass::oid ORDER BY attnum; 
attname | format_type 
----------+---------------------- 
tableoid | oid 
cmax | cid 
xmax | xidCluster Management Techniques 
cmin | cid 
xmin | xid 
ctid | tid 
id | integer 
name | character varying(5) 
(8 rows) 

3. 然后，我们将使用select * from table命令从表中选择所有的列，并理解我们没有看到任何与系统列相关的数据

postgres=# SELECT * FROM foo.bar LIMIT 1; 
id | name 
----+------ 
1 | avi 
(1 row) 

4. 现在，想要查看系统列的值，我们应该在select命令中包含系统列名，看看它存储了什么:

postgres=# select xmin,* from foo.bar limit 1; 
xmin | id | name 
-------+----+------ 
11705 | 1 | avi 
(1 row) 

5. 让我们查询pg_class表，以查看步骤1中创建的表的oid:

postgres=# SELECT oid, relname FROM pg_class WHERE relname = 'bar'; 
oid | relname 
-------+--------- 
31239 | bar 
(1 row) 

6. 如果我们有两个具有相同名称bar的表，但在不同的schema中，它们不共享相同的oid，如下面的示例所示。在这个例子中，我们将在与第1步创建的表不同的模式中创建另一个表，并看到这两个表的oid是不同的:

postgres=# CREATE TABLE public.bar (id int, name varchar(5)); 
CREATE TABLE 
postgres=# SELECT oid, relname FROM pg_class WHERE relname = 'bar' 
and relkind = 't'; 
oid | relname 
-------+--------- 
31242 | bar
31239 | bar 
(2 rows) 

7. 为了正确识别属于特定模式的表，我们可以将pg_namespace与pg_class连接起来，如下所示:

postgres=# SELECT pc.oid, pn.nspname, pc.relname FROM pg_class pc JOIN pg_namespace pn ON pc.relnamespace = pn.oid WHERE pn.nspname = 'foo' AND pc.relname = 'bar'; 
oid | nspname | relname 
-------+---------+--------- 
31239 | foo | bar 
(1 row) 

8. 我们还可以使用regclass来标识完全限定表的oid。完全限定表是指连同schemaname(schemaname.tablename)一起指定的表:

postgres=# select 'foo.bar'::regclass::oid; 
oid 
------- 
31239 
(1 row) 

9. 在这一步中，我们将看到如何从每个记录的表中看到system列tableoid，而且它与表的oid相同:

postgres=# select tableoid, id, name from foo.bar limit 1; 
tableoid | id | name 
----------+----+------ 
31239 | 1 | avi 
(1 row) 

10. PostgreSQL中的每个事务都有一个唯一的事务ID。在这个步骤中，我们将看到一个事务ID如何在一个事务块中保持不变，以及如何在一个新事务中更改:

postgres=# BEGIN; 
BEGIN 
postgres=# select txid_current(); 
txid_current 
-------------- 
11902 
(1 row)
postgres=# select txid_current(); 
txid_current 
-------------- 
11902 
(1 row) 
postgres=# END; 
COMMIT 
postgres=# select txid_current(); 
txid_current 
-------------- 
11903 
(1 row) 

11. 通过显式查询xmin，我们可以通过查找每个记录的xmin值来查看插入记录的事务ID。注意以下日志中所有记录的xmin值:

postgres=# select xmin,* from foo.bar; 
xmin | id | name 
-------+----+------ 
11705 | 1 | avi 
11705 | 2 | avi 
11705 | 3 | avi 
11705 | 4 | avi 
11705 | 5 | avi 
11905 | 6 | avi 
(6 rows) 

12. 我们还可以通过显式地选择每条记录来找到它的xmax。如果xmax为0，说明它从未被删除，并且是可见的:

postgres=# select xmin, xmax, * from foo.bar ; 
xmin | xmax | id | name 
-------+------+----+------ 
11705 | 0 | 1 | avi 
11705 | 0 | 2 | avi 
11705 | 0 | 3 | avi 
11705 | 0 | 4 | avi 
11705 | 0 | 5 | avi 
11905 | 0 | 6 | avi 
11907 | 0 | 7 | avi 
(7 rows)

13. 如果我们执行删除操作来删除一条记录，后续的选择查询将看不到被删除的记录:

postgres=# BEGIN; 
BEGIN 
postgres=# DELETE FROM foo.bar WHERE id = 7; 
DELETE 1 
postgres=# COMMIT; 
COMMIT 
postgres=# select xmin, xmax, * from foo.bar ; 
xmin | xmax | id | name 
-------+------+----+------ 
11705 | 0 | 1 | avi 
11705 | 0 | 2 | avi 
11705 | 0 | 3 | avi 
11705 | 0 | 4 | avi 
11705 | 0 | 5 | avi 
11905 | 0 | 6 | avi 
(6 rows) 

14. 现在，让我们用两个并行的终端。在一个终端中，我们删除一条记录，然后在提交删除之前，观察另一个终端中被删除记录的xmin和xmax值:

  Terminal 1: 运行delete，但不提交。注意执行delete的事务ID:

postgres=# BEGIN; 
BEGIN 
postgres=# select txid_current(); 
txid_current 
-------------- 
11911 
(1 row) 
postgres=# DELETE FROM foo.bar WHERE id = 6; 
DELETE 1 

 Terminal 2:我们可以看到，在终端1中，xmax值被更改为执行delete的事务ID:

postgres=# select xmin, xmax, * from foo.bar ; 
xmin | xmax | id | name 
-------+-------+----+------ 
11705 | 0 | 1 | avi 
11705 | 0 | 2 | avi 
11705 | 0 | 3 | avi 
11705 | 0 | 4 | avi 
11705 | 0 | 5 | avi 
11905 | 11911 | 6 | avi 
(6 rows) 

15. 回滚删除操作，现在来看xmax值:

  Terminal 1:让我们发出回滚而不是提交，这样记录就不会被删除:

postgres=# BEGIN; 
BEGIN 
postgres=# select txid_current(); 
txid_current 
-------------- 
11911 
(1 row) 
postgres=# DELETE FROM foo.bar WHERE id = 6; 
DELETE 1 
postgres=# ROLLBACK; 
ROLLBACK 

  Terminal 2: 我们可以看到，xmax仍然保持不变，但内部提示位xact_rolled_backed将被设为true:

$ psql -d postgres -c "select xmin, xmax, id, name from 
foo.bar" 
xmin | xmax | id | name 
-------+-------+----+------ 
11705 | 0 | 1 | avi 
11705 | 0 | 2 | avi 
11705 | 0 | 3 | avi 
11705 | 0 | 4 | avi 
11705 | 0 | 5 | avi 
11905 | 11911 | 6 | avi 
(6 rows)

16. 我们可以通过查询system列ctid来查询每个元组的位置:

postgres=# select xmin, xmax, ctid, * from foo.bar ; 
xmin | xmax | ctid | id | name 
-------+-------+-------+----+------ 
11705 | 0 | (0,1) | 1 | avi 
11705 | 0 | (0,2) | 2 | avi 
11705 | 0 | (0,3) | 3 | avi 
11705 | 0 | (0,4) | 4 | avi 
11705 | 0 | (0,5) | 5 | avi 
11905 | 11911 | (0,6) | 6 | avi 
(6 rows) 

How it works…

  为了理解MVCC在PostgreSQL中的工作方式，理解PostgreSQL中表的一些系统列是很重要的。上面的示例演示了PostgreSQL中一个表的隐藏列，以及当它们对应的记录被修改时它们的值所发生的变化。

  如果您观察步骤1，可以看到一个名为foo.bar的两列的表已经创建。然而，当您看到步骤2中的输出时，有趣的是，它不仅有两个列，而且还有一些由PostgreSQL自动创建的附加列。

  通过输出，可以清楚地看到，当我们使用create table语法创建表时，假定要创建的表还有6个额外的列。为了理解这些列如何对PostgreSQL中MVCC的实现方式产生重大影响，让我们详细了解这些系统列。

  尽管这些列被认为是隐藏的，但这并不意味着这些列中的值对管理员来说是一个谜。这些列被认为是隐藏列的原因是它们被排除在select * from table的输出之外，如步骤3的输出所示。

  为了查看哪些值存储在这些隐藏列中，需要在SELECT语句中专门使用这些列，如步骤4所示。在这个例子中，我们看到了选择表中的所有列与只选择系统列以及实际列之间的区别。

tableoid

  现在，在学习tableoid之前，了解什么是OID是很重要的。PostgreSQL中的OID代表对象标识符。当在PostgreSQL中创建表时，一个带有表名和模式名的新记录被插入到系统表pg_class和pg_namespace中。在PostgreSQL内部，oid被用作系统表的主键。为了找到前面创建的表foo.bar的oid，最简单的方法是查询pg_class系统表，如步骤5所示。

  但是，如果在两个不同的模式中有多个具有相同名称的表呢?在PostgreSQL中，在一个数据库中可以有多个模式。例如，如果我们观察步骤6中的输出，可以看到与步骤1中创建的表同名的表也是在不同的模式中创建的。

  因此，为了找到对应于恰当模式的表的oid, pg_class可以与系统表pg_namespace(其中包含模式名和模式的oid)相连接。对于pg_class中的每个关系，其模式的oid也会被插入。想要查看实际效果，步骤7中的日志包含简单的SQL来用于标识属于特定模式的特定表的oid。

  还有另一种查找表OID的简单方法，使用regclass来替代foo.bar中包含模式名和表名，如步骤8所示。现在要简单地理解tableoid，它只是包含表的oid的一列，与pg_class表中可见的oid相同。请参见步骤9，该步骤演示了如何选择tableoid以及表的其他列。

xmin

  PostgreSQL管理员应该意识到xmin是重要列之一。管理员的日常工作完全取决于对xmin的理解。为了更好地理解xmin，让我们学习一下PostgreSQL中的事务id。在本章中，我们不打算讨论事务id的问题;这只是个介绍。现在，让我们记住事务ID是分配给事务的唯一标识符。

  PostgreSQL中的事务ID为32位无符号整数。它是循环的，这意味着它从0开始，到42亿(4,294,967,295)，然后再次从0开始。函数txid_current()显示当前事务的ID。如果我们仔细观察步骤10中的输出，我们会看到事务ID在整个事务中保持不变(在BEGIN和END之间)，但对于另一个新事务它会增量地更改。

  正如我们现在理解的事务ID一样，xmin只不过是插入该元组的事务ID。例如，在步骤11的输出中，我们可以看到前5条记录是由ID为11705的事务插入的，最后一条记录是由ID为11905的事务插入的。

  xmin中的差异在确定哪些元组对事务可见时至关重要。例如，在11905年之前开始的事务中的SQL语句可能无法看到其未来事务插入的记录。

xmax

  当有元组被删除或更新时，xmax值会产生显著差异。在我们开始学习xmax之前，请查看步骤12中的日志，它显示了 foo.bar表记录的xmax值。

  在log中，我们看到xmax的值是0。当该行从未被删除或试图删除时，xmax的值为0。当您考虑删除一条记录时，可能会发生两种情况:

  1.事务发出了一个delete命令并提交了该命令。

  2.事务发出了一个delete命令，但它还没有被提交。

  在第一个场景中，当发布和提交一个删除时，记录不再可见，这是很容易理解的，如步骤13所示。因此，讨论该记录的xmax值是没有意义的。

  但是，在第二个场景中，删除还没有提交，那该怎么办呢?为了演示这一点，我在一个终端中发出了一个delete命令，并查看了另一个终端中的xmax值，如步骤14所示。如果仔细查看终端2日志，就会发现xmax值已经被发出删除操作的事务ID更新。请注意，当发出ROLLBACK时，xmax值与发出delete的事务ID保持一致。当删除被提交时，如前所述，记录对将来的select不再可见。

  正如在步骤15中所看到的，如果我发出ROLLBACK而不是COMMIT，那么xmax值将保持与在回滚之前发出delete的事务ID相同。

  正如我们现在理解的xmin和xmax，当事务在表上运行SELECT时，对事务可见的记录是(xmin <= txid_current()) 和(xmax = 0 OR txid_current() < xmax)的元组。

select * from foo.bar where id = 2 ; 

  由事务内部发出的前面的SQL使用以下逻辑:

select * from foo.bar where id = 2 (and xmin <= txid_current() AND (xmax = 0 OR txid_current() < xmax)); 

ctid

  ctid是表示一个元组在Postgres表中的位置的字段。对于每个元组，它是唯一的。它包含页/块号以及该页中的元组索引。例如，步骤16中的日志显示所有元组都存储在第0页中，它还显示了它们在页中的位置。

pageinspect

  我们将在以后的章节中讨论PostgreSQL中的扩展。现在，可以将它们视为可以附加到现有Postgres代码以实现特定功能的一段外部代码。Pageinspect是一个扩展，包含在contrib模块中，它在显示页面内容时很有用。表的所有元组存储在一个或多个页面中。这个扩展为存储在每个页面中的内容提供了粒度可见性。

  要创建这个扩展，我们只需执行步骤17中所示的命令:

postgres=# CREATE EXTENSION pageinspect ; 
CREATE EXTENSION 
-- Verify 
postgres=# \dx 
List of installed extensions 
Name | Version | Schema | Description 
-------------+---------+------------+-------------------------------------- 
----------------- 
pageinspect | 1.6 | public | inspect the contents of database pages at 
a low level 
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language 
(2 rows)

  这个扩展提供两个功能:

  get_raw_page:读取指定的8KB页面

  Heap_page_item_attrs:显示每个元组的元数据和数据

  从前面的日志中，我们看到，从表中删除一条记录后，有6条记录。但是，记录真的从表中删除了吗?让我们看看页面中存储了什么。

  由于表中元组很少，从下面的输出中我们可以看到，这个表只有1个8kb大小的页:

$ psql -d postgres -c "select relname, relpages from pg_class where oid='foo.bar'::regclass::oid" 
relname | relpages 
---------+---------- 
bar | 1 
(1 row) 
$ psql -c "show block_size" 
block_size 
------------ 
8192 
(1 row) 

  页面序列从0开始。因此，我们将使用pageinspect来查看第0页里面有什么

$ psql -d postgres -c "SELECT t_xmin, t_xmax, t_field3 as t_cid, t_ctid 
FROM heap_page_items(get_raw_page('foo.bar',0))" 
t_xmin | t_xmax | t_cid | t_ctid 
--------+--------+-------+-------- 
11705 | 0 | 0 | (0,1) 
11705 | 0 | 0 | (0,2) 
11705 | 0 | 0 | (0,3) 
11705 | 0 | 0 | (0,4) 
11705 | 0 | 0 | (0,5) 
11905 | 11911 | 0 | (0,6) 
11907 | 11910 | 0 | (0,7) 
(7 rows)

  在前面的日志中，我们看到没有ctid =(0,7)的元组。但是我们已经删除了id = 7的记录(已提交)。这还算令人惊讶吗?

  我认为现在不是了，因为我们前面讨论过UNDO存储在它自己的表中。因此，先前删除的元组仍然存储在表中，直到清理进程删除它。只有当没有依赖于已删除记录的事务时，清理进程(VACUUM)才会删除它。在下面的日志中，我们看到这个ctid的记录的xmax_committed设置为t (true)。这意味着一个由事务ID 11910发出的删除被提交了:

postgres=# \x 
Expanded display is on. 
postgres=# SELECT lp, 
t_ctid AS ctid, 
t_xmin AS xmin, 
t_xmax AS xmax, 
(t_infomask & 128)::boolean AS xmax_is_lock, 
(t_infomask & 1024)::boolean AS xmax_committed, 
(t_infomask & 2048)::boolean AS xmax_rolled_back, 
(t_infomask & 4096)::boolean AS xmax_multixact, 
t_attrs[1] AS p_id, 
t_attrs[2] AS p_val 
FROM heap_page_item_attrs( 
get_raw_page('foo.bar', 0), 
'foo.bar' 
) WHERE lp = 7; 
-[ RECORD 1 ]----+----------- 
lp | 7 
ctid | (0,7) 
xmin | 11907 
xmax | 11910 
xmax_is_lock | f 
xmax_committed | t 
xmax_rolled_back | f 
xmax_multixact | f 
p_id | \x07000000 
p_val | \x09617669

There’s more…

  到目前为止，在前面的小节中，我们已经了解了MVCC在PostgreSQL中的工作原理。最后的结论是，由于删除或更新，每个表中可能会维护多个行版本。在一段时间内，可能仍有许多这样的删除记录存储在每个页面中。这样的记录/元组被称为死元组。被插入且未被修改的元组称为活元组。死元组占用更多的空间，可能会降低数据库中查询的性能。我们应该如何管理这些死元组?我们应该进行定期的人工维护还是自动维护?如果是自动的，那是什么工作?所有这些问题的答案都是VACUUM。现在让我们详细地了解一下。

  当您启动Postgres时，您应该会看到一个正在运行的后台进程列表，如下面的截图所示。这些进程各自承担一些责任，以尽可能最好的方式帮助用户。其中一个进程是autovacuum launcher进程。这个进程负责启动表上的VACUUM和ANALYZE任务:

  VACUUM会清理死元组，以便它们所占用的空间可以被将来的插入所重用(一个更新操作相当于将该记录标记删除，再插入新记录)。而ANALYZE收集表的统计信息以便于解析器为使用该表的查询准备的执行计划是最优的。在PostgreSQL (PostgreSQL .conf)中有一些参数被这个进程使用来决定什么时候运行一个AUTOVACUUM vacuum或者一个autovacuum分析一张表。