從表中刪除不必要記錄的方法
我們有一個數據庫,每 7 秒儲存 20 個溫度計的溫度。我們希望以每分鐘僅保存一個溫度而不是 3 個月以上的 8 個溫度的方式刪除所有記錄。我打算按照該問題here中的討論進行如下操作:
- 將(相對較少的)倖存行選擇到臨時表中。
- 截斷表格。
- 重新插入倖存者。
但是所有設備都在不間斷地工作並將值插入數據庫,因此我無法截斷和重命名 temp_tables,因為系統應該一直處於開啟狀態。或者我可以嗎?如果沒有,我必須通過 DELETE VACUUM 來完成嗎?
順便說一句,我們只刪除一次超過 3 個月的所有不必要的記錄,之後我們必須在前 4 個月每個月重複這個過程(我希望這部分很清楚)。如果我們在 05.2013 開頭重複這個過程,我們必須刪除 01.2013 月份的不必要記錄。我怎樣才能使這個過程自動化?我應該更好地為計劃任務編寫一個 .bat 文件還是有更好的方法?
您似乎誤解了我對您之前的問題的部分建議:
所以我不能截斷和重命名 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;