從大表中刪除舊行，每個給定時間間隔保留一個

我正在嘗試從僅保存時間戳的表中刪除一些行（數據類型timestamptz）：

從數據庫/表中刪除的通常操作非常簡單，例如我想刪除特定日期之前的所有行。

delete from "andaz-rkugf" WHERE  snapshot_timestamp <= '2018-10-31'::date 

但我目前有一個不同的案例。我在這個表中有將近 100K 行，我不想刪除之前的每一行'2018-10-31'::date。這就是我想要一口氣做的事情：

1. 選擇之前的所有行'2018-10-31'::date
2. 按天和小時對它們進行分類（邏輯上是多餘的）。
3. 每 10 分鐘保留 1snapshot_timestamp個，每隔 1 個刪除。

因此，在這種情況下，之前的每個小時'2018-10-31'::date只會有 6 個 snapshot_timestamps。

如何正確快速地做到這一點？

假設

• 您沒有聲明，但螢幕截圖中的鍵符號表示snapshot_timestamp定義為PRIMARY KEY.
• 不能對涉及的行進行並發寫入。

否則你需要做更多。

最好的查詢取決於你的設置細節——最重要的是數據分佈。

每 10 分鐘間隔幾行

WITH keep AS (
  SELECT min(snapshot_timestamp) AS snapshot_timestamp
  FROM   "andaz-rkugf"
  WHERE  snapshot_timestamp <= '2018-10-31+0'  -- ①
  GROUP  BY extract(epoch FROM snapshot_timestamp)::bigint / 600  -- ②
  )
DELETE FROM "andaz-rkugf" a
WHERE  snapshot_timestamp <= '2018-10-31+0'
AND    NOT EXISTS (
  SELECT FROM keep k
  WHERE a.snapshot_timestamp = k.snapshot_timestamp
  );

db<>在這裡擺弄

CTEkeep在給定的截止時間之前每 10 分鐘間隔選擇最小時間戳。

值得注意的是，時間戳不必精確地落在 10 分鐘的範圍內。此查詢適用於任何時間戳。

②extract (epoch FROM snapshot_timestamp)提取自紀元以來的秒數。轉換bigint為 600 並除以 600（以 10 分鐘為間隔的秒數）。整數除法截斷，同一 10 分鐘間隔內的時間戳屬於同一組。採取min()每組（或任何你喜歡的）。看：

• 查詢中的 PostgreSQL 分區不起作用
• 將時間戳截斷為任意間隔

主DELETE刪除截止之前的所有行，這些行不是守門員，用NOT EXISTS. 有關的：

• 選擇其他表中不存在的行

對於截止前超過百分之幾的行，根本不會使用 PK 索引，順序掃描會更便宜。但是 PK 指數對於排除與重複的聯繫仍然是必不可少的。

①'2018-10-31'是完全有效的timestamp文字。00:00:00將假定失去的時間分量。不要將其轉換為date，那不會做任何有用的事情。無論哪種方式，如果沒有offset，則假定目前會話的時區設置。在使用 操作時timestamptz，明確聲明時區以避免偷偷摸摸的角落護理錯誤會更清晰。'2018-10-31 00:00:00+0'或者'2018-10-31+0'在我的範例中簡稱為 UTC 時間。

大桌子

對於大型表，應該使用索引而不是 CTE 創建一個臨時表。（也許不是為了刪除“僅”100k 行的一小部分。）比如：

CREATE TEMP TABLE keep AS
SELECT min(snapshot_timestamp) AS snapshot_timestamp
FROM   "andaz-rkugf"
WHERE  snapshot_timestamp <= '2018-10-31'
GROUP  BY extract(epoch FROM snapshot_timestamp)::bigint / 600
ORDER  BY 1;

CREATE INDEX ON keep (snapshot_timestamp);
ANALYZE keep;

DELETE FROM "andaz-rkugf" a
WHERE  snapshot_timestamp <= '2018-10-31'
AND    NOT EXISTS (
  SELECT FROM keep k
  WHERE a.snapshot_timestamp = k.snapshot_timestamp
  );

db<>在這裡擺弄

在同一個會話中執行所有。（不一定是相同的事務。）足夠temp_buffers的會將臨時表保留在 RAM 中（更快）。

每 10 分鐘間隔多行

意思是，獅子份額被刪除。刪除所有這些並重新插入幾個“守護者”會更快：

WITH del AS (
  DELETE FROM "andaz-rkugf" a
  WHERE  snapshot_timestamp <= '2018-10-31'
  RETURNING *
  )
INSERT INTO "andaz-rkugf"
SELECT min(snapshot_timestamp)
FROM   del
GROUP  BY extract(epoch FROM snapshot_timestamp)::bigint / 600
ORDER  BY 1;  -- optional

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/301942