From 7c59f2b66284b5b561c5368a0c834640ae0e611e Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan.goodliffe@octal.co.uk>
Date: Thu, 23 Apr 2026 10:11:13 +0100
Subject: Add BRIN index to access_log.request_time and improve purge

Purge is now fully request_time based and not hacked around id ranges.
---
 src/sql/accessLogPurgeOld.sql | 23 +++++++++--------------
 1 file changed, 9 insertions(+), 14 deletions(-)

(limited to 'src/sql')

diff --git a/src/sql/accessLogPurgeOld.sql b/src/sql/accessLogPurgeOld.sql
index 18ec775..00e55b5 100644
--- a/src/sql/accessLogPurgeOld.sql
+++ b/src/sql/accessLogPurgeOld.sql
@@ -1,18 +1,13 @@
-WITH scope AS (
+WITH delete_batch AS (
 	SELECT
-		id
+		ctid
 	FROM
 		access_log
+	WHERE
+		request_time < CURRENT_DATE - ?::interval
 	ORDER BY
-		id
-	LIMIT ?
-),
-scoperange AS (
-	SELECT
-		min(id) minid,
-		max(id) maxid
-	FROM
-		scope)
-DELETE FROM access_log USING scoperange
-WHERE request_time < CURRENT_DATE - ?::interval
-	AND access_log.id BETWEEN scoperange.minid AND scoperange.maxid
+		request_time
+	FOR UPDATE
+LIMIT ?)
+DELETE FROM access_log AS al USING delete_batch AS del
+WHERE al.ctid = del.ctid
-- 
cgit v1.3