diff options
| author | Dan Goodliffe <dan.goodliffe@octal.co.uk> | 2026-04-23 10:11:13 +0100 |
|---|---|---|
| committer | Dan Goodliffe <dan.goodliffe@octal.co.uk> | 2026-04-23 10:11:13 +0100 |
| commit | 7c59f2b66284b5b561c5368a0c834640ae0e611e (patch) | |
| tree | 52283c0147c33317e0683638ab88ae1828a91b20 /src/sql | |
| parent | 7a66f1241f4a6ec570a5c41490ee3698eeeaf66e (diff) | |
| download | webstat-7c59f2b66284b5b561c5368a0c834640ae0e611e.tar.bz2 webstat-7c59f2b66284b5b561c5368a0c834640ae0e611e.tar.xz webstat-7c59f2b66284b5b561c5368a0c834640ae0e611e.zip | |
Add BRIN index to access_log.request_time and improve purge
Purge is now fully request_time based and not hacked around id ranges.
Diffstat (limited to 'src/sql')
| -rw-r--r-- | src/sql/accessLogPurgeOld.sql | 23 |
1 files changed, 9 insertions, 14 deletions
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 |