mysql迁移至postgresql_Oracle迁移PostgreSQL: 浅谈RowID、Ctid和MVCC

原文: https://postgres.fun/20200627082800.html

背景

最近有位社区朋友在做Oracle迁移PostgreSQL项目，应用程序大量使用Oracle的rowid特性，咨询我: 如果迁移到PostgreSQL数据库，是否可以使用PostgreSQL的ctid来代替rowid？

我的回答:不可以。

原因是Oracle的rowid不会随着表上记录的UPDATE变化，而PostgreSQL记录的ctid会随着UPDATE变化，如果业务逻辑依赖ctid进行处理则会带来问题。

举个例子，以下两个业务场景在高并发时，Oracle能够正常处理。

业务1：
sql1：select * from t1 where key=:
sql2：update t1 set col1= where rowid=:;

业务2：
sql3：select * from t1 where key=:
sql4：update t1 set col2= where rowid=:

如果切换换到 PostgreSQL，使用ctid代替rowid，如下:

业务1：
sql1：select * from t1 where key=:
sql2：update t1 set col1= where ctid=:;

业务2：
sql3：select * from t1 where key=:
sql4：update t1 set col2= where ctid=:

当业务1和业务2高并发时，假如sql1和sql3得到的rowid是同一个，那么执行sql2和sql4必定有一个会失败，因为先执行的sql会更改rowid，后执行的sql必定会更新不到记录，虽然update不报错，但并不符合业务逻辑。

关于rowid和ctid的不同，深层次是Oracle和PostgreSQL的MVCC(Multiversion Concurrency Control)多版本并发控制的不同，这个知识点涉及的内容很多，讲清楚并不容易。

首先看看rowid和ctid。

RowID和ctid

简单看看 Oracle的 rowid 和 PostgreSQL ctid。

RowID

rowid是Oracle的一个重要特性，用于定位数据库表中一条记录的唯一地址，如果不对表进行数据文件物理位置变动的操作，例如move等，表中每条数据的 rowid 是不会变化的。

rowid是一个伪列，并不实际存储在表中，也不占用物理数据文件，它只是标记表中每条数据的物理位置。

rowid基于64进制，共18位字符，格式为以下:

OOOOOOFFFBBBBBBRRR

rowid由四部分组成:

• OOOOOOr: Data object numbe,记录的对象编号
• FFFr: Datafile number,记录所在的表空间的数据文件编号
• BBBBBB: Data block,记录所在的数据块编号
• RRR: row number,记录的行编号

接着看一个 rowid的示例，如下:

SELECT ROWID, last_name FROM employees WHERE department_id = 20;

ROWID              LAST_NAME 
------------------ ---------- 
AAAAaoAATAAABrXAAA BORTINS

得到的结果是18位的字符，可使用Oracle的dbms_rowid包的相关函数对rowid的四部分进行解析，生成可读的Data object numbe、Datafile number、Data block、row number。

Ctid

PostgreSQL的ctid也可以用来物理标记表的一条记录，如果记录被UPDATE后，记录的ctid会变化，因此，ctid作为记录的长期标识是无效的，推荐使用表的主键标识记录。

ctid 的结构较 rowid 简单，如下:

(Data block, Row)

具体为以下: - Data block: 记录所在的数据块编号 - Row: 记录的行编号

看一个ctid的示例，如下:

mydb=> CREATE TABLE t2(id int4,name text);
CREATE TABLE

mydb=> INSERT INTO t2 VALUES(1,'a'),(2,'b');
INSERT 0 2

mydb=> SELECT ctid,* FROM t2;
 ctid  | id | name
-------+----+------
 (0,1) |  1 | a
 (0,2) |  2 | b
(2 rows)

ctid数据小括号的第一位是数据块编号，第二位是记录的行编号。

MVCC的实现方式

Oracle的rowid和PostgreSQL的ctid的差异根源是Oracle和PostgreSQL的MVCC机制的差异。

Oracle的MVCC机制中引入了undo表空间，当记录被update时会在undo表空间中保存记录的旧版本，而老的记录的物理位置并不会发生变化，记录的rowid是不变的。

PostgreSQL的MVCC机制现阶段并没有引入undo(可能后续大版本会引入)，当表的记录被update时，该记录的新版本(tuple_new)将会插入到表的数据块中，同时，此记录的老版本(tuple_old)仍然保持在原数据块中，只是标记为过期，直到VACCUM进程对tuple_old处理后，tuple_old才会被回收。

Ctid实践

我们来看一个示例，当PostgreSQL的表记录被update后的ctid的变化。

创建测试表并插入记录，如下:

mydb=> CREATE TABLE t3 (id int4, info text);
CREATE TABLE

mydb=> INSERT INTO t3 (id,info) VALUES(1,'a'),(2,'b');
INSERT 0 2

更新id=1这条记录，如下:

mydb=> SELECT ctid, * FROM t3;
 ctid  | id | info
-------+----+------
 (0,1) |  1 | a
 (0,2) |  2 | b
(2 rows)

mydb=> UPDATE t3 SET info='aa' WHERE id=1;
UPDATE 1

mydb=> SELECT ctid, * FROM t3;
 ctid  | id | info
-------+----+------
 (0,2) |  2 | b
 (0,3) |  1 | aa
(2 rows)

发现id=1这条记录的ctid由原来的(0,1)变成了现在的(0,3)，ctid变化了，说明在表的物理文件上新增了记录(tuple)。

这个示例验证了PostgreSQL的MVCC机制。

建议方案

本文简单介绍了rowid和ctid的结构，以及Oracle和PostgreSQL的MVCC机制的不同，回到本文开头社区朋友提出的问题，由于项目中的应用程序大量使用了rowid特性，建议切换到PostgreSQL过程中对应用进行改造，使用主键标识数据记录。

当然，对于复杂的业务系统必将带来大的应用改造工作量，以及数据库表结构变更的工作量(没有主健的表可能需要增加主健)，在项目中需重点考虑权衡。

参考

• Oracle RowID Datatype
• Oracle中的rowid
• PostgreSQL System Columns
• PostgreSQL、Oracle/MySQL和SQL Server的MVCC实现原理方式