PostgreSQL利用CTID删除重复数据

由于业务需要，准备加一个唯一索引，可是一执行命令，直接提示「表中列c1有重复的数据」，因此先想办法删除重复的行。对于我这种快20年接触数据库的人，各种数据库都玩儿过，不在乎什么方法解决这种问题，但总归还是要考虑一些执行效率的。

首先，介绍下背景：

PostgreSQL版本：PostgreSQL 13

需要加唯一索引的表及列：「t1表，c1列」，t1表中大量c1重复（2条，二选一）的数据行

业务需求：删除最先入库的数据，保留用户最近修改的那条数据

直接给出我的解决方案：

-- 查询重复的行，以免误删（切记、切记、切记！！！）
select *
from t1
where CTID in (select min(CTID) as min_ctid from t1 group by c1 having count(*) > 1);


delete
from t1
where CTID in (select min(CTID) as min_ctid from t1 group by c1 having count(*) > 1);

代码很简单，但是里面有一个字段值得注意「CTID」，它也是这篇文章我想简单解释的主题。

先贴几段PostgreSQL官方的相关解释（不让贴大于300字符的引用😂）：

• ctid
  

• 
  

  The physical location of the row version within its table. Note that although the ctid
   can be used to locate the row version very quickly, a row's ctid
   will change if it is updated or moved by VACUUM FULL
  . Therefore ctid
   is useless as a long-term row identifier. A primary key should be used to identify logical rows.

• 
  

• A final identifier type used by the system is tid, or tuple identifier (row identifier). This is the data type of the system column ctid. A tuple ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

• 
  

• PostgreSQL's VACUUM command has to process each table on a regular basis for several reasons:

• To recover or reuse disk space occupied by updated or deleted rows.

• To update data statistics used by the PostgreSQL query planner.

• To update the visibility map, which speeds up index-only scans.

• To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.

• 
  

• VACUUM Parameters:

• FULL

• Selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table.

• ......

通常，一行数据的ctid格式：(7,21)，那么括号中的7和21表示什么意思呢？

block number, tuple index within block，即该数据所在的物理块号及块中的元祖索引位置，解释为：第7块中的第21个元素。通过这两个值直接定位了数据的物理位置。

因此，通过物理位置索引数据自然是较快的，但是官方文档也直接给出了建议：VACUUM FULL
这个指令会重新改变ctid的值，意味着不能长期使用ctid作为条件操作业务数据，还是需要用主键。

这里简单解释下VACUUM FULL
，VACUUM是一个垃圾回收指令，实际上被删除或者被更新废弃的元组并没有在物理上从表中移除，它们将一直存在直到一次 VACUUM 被执行。一个ctid值的表示意味着物理空间上的表示，如果长时间不被回收将会使物理可用空间变得越来越少。因此VACUUM操作后就标记那些可回收的物理位置为可用，同时数据就可能涉及到ctid值的变更（FULL模式），当然这样的操作其实比较复杂，里面会涉及交换空间（副本）的一系列问题，不是一蹴而就那么简单。另外，VACUUM通常不需要手动执行，通过autovacuum 进程可以较好的处理这样的问题。

最后，回到实际应用中，临时删除重复的数据可以使用ctid，更重要的是在设计初期就需要投入相应时间来推敲业务需求。毕竟操作线上数据库犹如“刀尖上的舞者”，美丽背后必有代价。

PS：平时基本不更新博客或者公众号，最近实在是太忙了，必须写点东西缓冲下我的状态，受不了了，另外，国庆估计都要在家里蹲了，忙个啥呀我，哈哈😂。

参考链接：
https://www.postgresql.org/docs/13/ddl-system-columns.html
https://www.postgresql.org/docs/13/datatype-oid.html
https://www.postgresql.org/docs/13/runtime-config-autovacuum.html
https://www.postgresql.org/docs/13/sql-vacuum.html
https://pngtree.com/

‍