PostgreSQL CTID是Oracle ROWID的替代

前言

今天有个业务的妹子问我“在吗？”

我说什么事？

她说：“我们Oracle的数据库里有rowid，想问问在PostgreSQL中怎么迁移过去使用。”

我说：“rowid没有，只有ctid。”

她又问：“ctid是什么东东？”

我说：“Oracle的rowid是什么，在PostgreSQL中ctid就是什么？如果你的表上或者是程序上有Oracle的rowid，那肯定是不支持的。需要改造成ctid。”

CTID 代表了什么？

在Oracle中，每一行数据都会有一个唯一标识符，简称为rowid。而在PostgreSQL里面，我们可以使用ctid，表中的每一行数据都有唯一的ctid，它表示元组的位置。

我们首先来创建一个表。

create table t(id serial);
insert into t values(generate_series(1,10));

postgres=# select ctid,id from t;
  ctid  | id 
--------+----
 (0,1)  |  1
 (0,2)  |  2
 (0,3)  |  3
 (0,4)  |  4
 (0,5)  |  5
 (0,6)  |  6
 (0,7)  |  7
 (0,8)  |  8
 (0,9)  |  9
 (0,10) | 10
(10 rows)

复制

这里可以看到ctid表示的值。对于(0,1)，第一个数据0代表page号，第二个数字代表了元组编号。ctid的值都是顺序的。如果我们只想显示page编号或者元组编号，我们该怎么写呢？

postgres=# SELECT (ctid::text::point)[0]::bigint AS page_number,
          (ctid::text::point)[1]::bigint AS tuple_number  FROM t;
 page_number | tuple_number 
-------------+--------------
           0 |            1
           0 |            2
           0 |            3
           0 |            4
           0 |            5
           0 |            6
           0 |            7
           0 |            8
           0 |            9
           0 |           10
(10 rows)           

复制

MVCC下的CTID

我们知道PostgreSQL实现MVCC的机制和Oracle、MySQL是不一样的，当更新一个值的时候，我们讲观察到更新的那个值将拥有新的CTID，它不会使用就值所使用的CTID。

postgres=# update t set id=11 where id=5;
UPDATE 1
postgres=# SELECT (ctid::text::point)[0]::bigint AS page_number,
           (ctid::text::point)[1]::bigint AS tuple_number,
           id from t;
 page_number | tuple_number | id 
-------------+--------------+----
           0 |            1 |  1
           0 |            2 |  2
           0 |            3 |  3
           0 |            4 |  4
           0 |            6 |  6
           0 |            7 |  7
           0 |            8 |  8
           0 |            9 |  9
           0 |           10 | 10
           0 |           11 | 11

复制

这里可以看到当我把id=5的改成id=11，这一行的ctid也变成了(0,11)。而(0,5)这个ctid暂时看不见了。而当我们执行vacumm full之后，你会发现ctid被重置了，重置是顺序分配的。

postgres=# vacuum full t;
VACUUM
postgres=# SELECT (ctid::text::point)[0]::bigint AS page_number,
           (ctid::text::point)[1]::bigint AS tuple_number,
           id from t;
 page_number | tuple_number | id 
-------------+--------------+----
           0 |            1 |  1
           0 |            2 |  2
           0 |            3 |  3
           0 |            4 |  4
           0 |            5 |  6
           0 |            6 |  7
           0 |            7 |  8
           0 |            8 |  9
           0 |            9 | 10
           0 |           10 | 11

复制

之前的(0,6)对应的是6，现在(0,5)对应了6。

理论上在vacumm analyze之后，死元组被清理掉之后，应该可以重用。但是我在PostgreSQL 13这个版本遇到的情况是仍然无法使用。这让我有点诧异。

postgres=# update t set id =12 where id=11;
UPDATE 1
postgres=# SELECT (ctid::text::point)[0]::bigint AS page_number,
(ctid::text::point)[1]::bigint AS tuple_number,
id from t;
 page_number | tuple_number | id 
-------------+--------------+----
           0 |            1 |  1
           0 |            2 |  2
           0 |            3 |  3
           0 |            4 |  4
           0 |            5 |  6
           0 |            6 |  7
           0 |            7 |  8
           0 |            8 |  9
           0 |            9 | 10
           0 |           11 | 12
(10 rows)

postgres=# vacuum analyze t;
VACUUM
postgres=# SELECT (ctid::text::point)[0]::bigint AS page_number,
(ctid::text::point)[1]::bigint AS tuple_number,
id from t;
 page_number | tuple_number | id 
-------------+--------------+----
           0 |            1 |  1
           0 |            2 |  2
           0 |            3 |  3
           0 |            4 |  4
           0 |            5 |  6
           0 |            6 |  7
           0 |            7 |  8
           0 |            8 |  9
           0 |            9 | 10
           0 |           11 | 12
(10 rows)

postgres=# insert into t values(11);
INSERT 0 1
postgres=# SELECT (ctid::text::point)[0]::bigint AS page_number,
(ctid::text::point)[1]::bigint AS tuple_number,
id from t;
 page_number | tuple_number | id 
-------------+--------------+----
           0 |            1 |  1
           0 |            2 |  2
           0 |            3 |  3
           0 |            4 |  4
           0 |            5 |  6
           0 |            6 |  7
           0 |            7 |  8
           0 |            8 |  9
           0 |            9 | 10
           0 |           11 | 12
           0 |           12 | 11
(11 rows)

复制

后记

今天学习了CTID的皮毛知识，最近在外地出差真的很忙啊，连回复开发妹子的时间都没有了。