Delete old rows from a big table, keeping one per given time interval

Question

I am trying to delete some rows from a table in which I have saved timestamps only (data type timestamptz):

The usual operation to delete from DB/table is very simple such as I want to delete all rows before a specific date.

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

But I have a different use case at the moment. I have almost 100K rows in this table, and I don't want to delete every row before '2018-10-31'::date. This is what I am trying to do in one go:

Select all rows before '2018-10-31'::date
Categorize them for day and hour (logically redundant).
Keep 1 snapshot_timestamp per 10 minutes and delete every other.

So in this case every hour before '2018-10-31'::date will just have 6 snapshot_timestamps only.

How to do this properly and fast?

score 0 · Answer 1 · answered Oct 31 '21 at 17:53

You can work with EXTRACT(MINUTE FROM snapshot_timestamp )::int % 10 != 0.

That transforms your timestamp to simple true/false and you can index it.

With that value available you can build an index on this column and the original timestamp (or its date value to save some space).

CREATE INDEX help_delete ON "andaz-rkugf"(
  (EXTRACT(MINUTE FROM snapshot_timestamp)::int % 10 != 0), 
  (snapshot_timestamp::date)
);
DELETE FROM "andaz-rkugf"
WHERE (EXTRACT(MINUTE FROM snapshot_timestamp)::int % 10 != 0) = true
  AND snapshot_timestamp::date <= '2018-10-31'::date

That way the first part of the index is ref-access on all the rows with true (so all the rows that are not exact tens) ad the second part is just a range scan on date.

Example can be seen and EXPLAIN with the index usage checked at https://www.db-fiddle.com/f/tdWUykRtdVi19DdLxY2bRz/1

To make the index even smaller you could make it PARTIAL and filter only rows that are actually true.

Erwin Brandstetter · Accepted Answer · 2021-10-31T22:01:58.480

Assumptions

You did not declare, but the key symbol in the screenshot indicates that snapshot_timestamp is defined as PRIMARY KEY.
There cannot be concurrent writes to involved rows.

Else you need to do more.

The best query depends on details of your setup - most importantly data distribution.

For few rows per 10-minute interval

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<>fiddle here

The CTE keep selects the minimum timestamp for every 10-minute interval before the given cut-off.
Notably, timestamps do not have to fall on 10-minute boundaries exactly. This query works with any timestamps.

② extract (epoch FROM snapshot_timestamp) extracts the number of seconds since the epoch. Cast to bigint and divide by 600 (seconds in a 10-minute interval). Integer division truncates, timestamps in the same 10-minute interval fall into the same group. Take min() per group (or whatever you prefer). See:

The main DELETE removes all rows before the cut-off that aren't keepers, pinned down with NOT EXISTS. Related:

Select rows which are not present in other table

For more than a few percent of rows before the cut-off, the PK index is not going to be used at all, a sequential scan will be cheaper. But the PK index is still essential to rule out ties with duplicates.

① '2018-10-31' is a perfectly valid timestamp literal. 00:00:00 will be assumed for the missing time component. Don't cast it to date, that wouldn't do anything useful. Either way, without offset, the time zone setting of the current session is assumed. While operating with timestamptz, it's cleaner to declare the time zone explicitly to avoid sneaky corner-care errors. '2018-10-31 00:00:00+0' or '2018-10-31+0' for short in my example declares UTC time.

Large table

For large tables it should pay to create a temporary table with index instead of the CTE. (Maybe not for deleting a small part of "just" 100k rows.) Like:

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<>fiddle here

Run all in the same session. (Not necessarily the same transaction.) Sufficient temp_buffers will keep the temp table in RAM (faster).

For many rows per 10-minute interval

Meaning, the lion share is deleted. It will be faster to just delete all of them and re-insert the few "keepers":

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<>fiddle here

Delete old rows from a big table, keeping one per given time interval

2 Answers2

Assumptions

For few rows per 10-minute interval

Large table

For many rows per 10-minute interval