主页 / dba / 问题 / 42682
Accepted
mctuna
Asked: 2013-05-21 23:45:07 +0800 CST

从表中删除不必要记录的方法

  • 772

我们有一个数据库，每 7 秒存储 20 个温度计的温度。我们希望以每分钟仅保存一个温度而不是 3 个月以上的 8 个温度的方式删除所有记录。我打算按照该问题here中的讨论进行如下操作：

  1. 将（相对较少的）幸存行选择到临时表中。
  2. 截断表格。
  3. 重新插入幸存者。

但是所有设备都在不间断地工作并将值插入数据库，因此我无法截断和重命名 temp_tables，因为系统应该一直处于开启状态。或者我可以吗？如果没有，我必须通过 DELETE VACUUM 来完成吗？

顺便说一句，我们只删除一次超过 3 个月的所有不必要的记录，之后我们必须在前 4 个月每个月重复这个过程（我希望这部分很清楚）。如果我们在 05.2013 开头重复这个过程，我们必须删除 01.2013 月份的不必要记录。我怎样才能使这个过程自动化？我应该更好地为计划任务编写一个 .bat 文件还是有更好的方法？

postgresql delete

2 个回答

  1. 我可以建议你另一种方法/解决方案。您可以对表进行分区并删除超过 3 个月的分区，只需删除它们即可。这很快并且不会影响其他插入。但是，您似乎需要至少保留一个值；您可以计算要保留的值并将它们插入另一个表吗？

    • 2
  2. Best Answer

    您似乎误解了我对您之前的问题的部分建议：

    所以我不能截断和重命名 temp_tables，因为系统应该一直处于开启状态。

    没有涉及重命名。TRUNCATE运行INSERT. _ 唯一的阻塞操作是TRUNCATE. 我引用手册：

    TRUNCATE在它操作的每个表上获取一个ACCESS EXCLUSIVE锁，这会阻止该表上的所有其他并发操作。(...) 如果需要对表进行并发访问， DELETE则应使用该命令。

    INSERT如果它不是时间关键的并且允许等待直到锁被释放，它仍然可以工作。TRUNCATE通常非常快，如果您在单独的事务中运行它，它应该只阻塞几毫秒。注意：单独的事务，而不是单独的会话！您的临时表在会话中生死攸关。

    单独事务的缺点：如果在能够从临时表中重新插入之前丢失会话，则会丢失数据。可以肯定的是，您可以改用普通表。（这将提供删除旧表并重命名新表的替代路径。）

    无论哪种方式，您更新的问题清楚地表明您想要重复运行它，在同一个表中累积旧行。在这种情况下，TRUNCATE无论如何都不是一个好的选择。你总是可以只使用一个普通的DELETE. 大表的速度要慢得多，但并发INSERT根本没有被阻塞。autovacuuming 守护进程也必须做更多的工作。

    这主要是速度和安全性之间的权衡。

    SQL

    缓慢而可靠的方法的DELETE命令可能如下所示：

    DELETE FROM TABLE tbl t
USING (
   SELECT created_at FROM tbl WHERE created_at < now() - (interval '3 month')
   ) d
LEFT   JOIN (
   SELECT min(created_at) AS created_at
   FROM   tbl
   GROUP  BY date_trunc('minute', created_at)
   WHERE  created_at < now() - (interval '3 month')
   ) x USING (created_at)
WHERE  x.created_at IS NULL
AND    d.created_at = t.created_at;
    • 2

相关问题