PostgreSQL利用CTID删除重复数据

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

首先,介绍下背景:

PostgreSQL版本:PostgreSQL 13

需要加唯一索引的表及列:「t1表,c1列」,t1表中大量c1重复(2条,二选一)的数据行

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

直接给出我的解决方案:

-- 查询重复的行,以免误删(切记、切记、切记!!!)
select *from t1where CTID in (select min(CTID) as min_ctid from t1 group by c1 having count(*) > 1);​
deletefrom t1where 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/

 

 

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: