Add BRIN index to access_log.request_time and improve purge

Purge is now fully request_time based and not hacked around id ranges.

3 files changed, 13 insertions, 16 deletions

diff --git a/src/ingestor.cpp b/src/ingestor.cpp
index cc5270f..c41454f 100644
--- a/src/ingestor.cpp
+++ b/src/ingestor.cpp
@@ -439,8 +439,8 @@ namespace WebStat {
 		auto dbconn = dbpool->get();
 		const auto stopAt = Job::LastRunTime::clock::now() + settings.purgeDeleteMaxTime;
 		const auto purge = dbconn->modify(SQL::ACCESS_LOG_PURGE_OLD, SQL::ACCESS_LOG_PURGE_OLD_OPTS);
-		purge->bindParam(0, settings.purgeDeleteMax);
-		purge->bindParam(1, std::format("{} days", settings.purgeDaysToKeep));
+		purge->bindParam(0, std::format("{} days", settings.purgeDaysToKeep));
+		purge->bindParam(1, settings.purgeDeleteMax);
 		unsigned int purgedTotal {};
 		while (stopAt > Job::LastRunTime::clock::now()) {
 			const auto purged = purge->execute();
diff --git a/src/schema.sql b/src/schema.sql
index b211505..9d39bcd 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -107,6 +107,8 @@ CREATE TABLE access_log(
 	CONSTRAINT fk_access_log_content_type FOREIGN KEY (content_type) REFERENCES entities(id) ON UPDATE CASCADE
 );
 
+CREATE INDEX idx_access_log_request_time ON access_log USING BRIN(request_time) WITH (autosummarize = TRUE);
+
 CREATE OR REPLACE VIEW access_log_view AS
 SELECT
 	l.id,
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