【翻译】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, nam