postgresql CLUSTER 命令不清除死元组

Question

我们有一个后台进程(无限循环中的 linux 守护进程)自动从放置在特定目录中的 csv 文件中获取所有行，并将它们导入到一个表中。守护进程一个一个地处理目录中出现的任何文件，用 python 编写，并使用 psycopg2 连接到我们的 postgresql 数据库。

该过程使用 INSERT 语句导入这些记录，但首先删除与 csv 文件中的任何记录具有相同唯一键的任何表记录。通常，该过程正在为它插入的每条记录删除一条记录。所以当这个守护进程在后台运行时，它正在删除然后插入行。每次处理一个文件时，它都会专门提交事务、关闭游标，然后关闭连接。

我们希望定期(一天两次)运行 CLUSTER 来删除死元组，并使表的磁盘大小保持在可管理的范围内。

但是，此进程中的某些内容正在阻止 CLUSTER 命令删除进程运行时正在删除的所有记录的死元组。我们知道会发生这种情况，因为如果我们在进程运行时运行 CLUSTER，则包含此导入数据的表的磁盘大小不会减少，并且 pg_stat_user_tables 将显示许多死元组。

如果我们停止进程然后运行 ​​CLUSTER，表的磁盘大小将急剧减少并且 pg_stat_user_tables 将报告所有死元组都消失了。

奇怪的是，我们每次处理每个文件时都会提交事务并关闭连接，所以我不知道是什么不允许在进程运行时删除死元组。

同样奇怪的是，如果我们停止该进程，然后再次启动该进程，然后执行 CLUSTER，它将删除所有由 previous 守护进程运行创建的死元组；但是 CLUSTER 的任何后续调用都不会清除由守护进程的当前运行创建的任何死元组(当然它仍在运行)。

因此，某事 一直在维护与死元组的某种链接，直到进程停止，即使我们已经提交了事务并关闭了与创建这些死元组的 postgres 的所有连接。 pg_locks 不报告任何打开的锁，也没有报告正在运行的事务，因此它看起来不像是锁或打开的事务问题。

归根结底，这会阻止我们定期在桌面上运行 CLUSTER，以免它不断增长。

我确信对此有一个简单的答案，但我无法在任何地方找到它。该过程的一些框架代码如下。这真的是一个简单的过程，所以我不知道这里发生了什么。任何指导将不胜感激。

while True:
    l = [(get_modified_time(fname), fname) for fname in os.listdir('/tmp/data')]
    l.sort()

    for (t, fname) in l:
        conn = psycopg2.connect("dbname='dbname' user='user' password='password'")
        cursor = conn.cursor()

        # Calls a postgresql function that reads a file and imports it into 
        # a table via INSERT statements and DELETEs any records that have the 
        # same unique key as any of the records in the file.
        cursor.execute("SELECT import('%s', '%s');" % (fname, t))

        conn.commit()
        cursor.close()
        conn.close()

        os.remove(get_full_pathname(fname))

        time.sleep(0.100)

Answer 1

autovacuum 有什么问题？当 autovacuum 完成它的工作时，您不必使用 CLUSTER 来清理死元组。 CLUSTER 不是为此而生的，它是 VACUUM。

如果您将过程更改为 UPDATE 重复项，当您使用较低的 FILLFACTOR:HOT 更新时，情况可能会变得更好。这些更快，回收空间，在存储中保持相同的顺序并且不需要 VACUUM 或 CLUSTER。