chore: delete old analytics

Commit `483a3a2`

Eric Bower · 2026-05-03 13:02:30 -0400 EDT

parent 493d4d6

chore: delete old analytics

1 files changed, +8, -0

M scripts/reap.sql

+8, -0

......@@ -1 +1,9 @@
-- delete unused accounts
SELECT count(*) FROM app_users u WHERE NOT EXISTS (SELECT 1 FROM posts WHERE user_id = u.id) AND NOT EXISTS (SELECT 1 FROM projects WHERE user_id = u.id) AND NOT EXISTS (SELECT 1 FROM access_logs WHERE user_id = u.id AND created_at > NOW() - INTERVAL '1 year');

-- how many visits will be deleted
SELECT count(*) FROM analytics_visits WHERE created_at < NOW() - INTERVAL '1 year';
-- delete old analytic visits
DELETE FROM analytics_visits WHERE created_at < NOW() - INTERVAL '1 year';
-- batch delete
WITH deleted AS (DELETE FROM analytics_visits WHERE ctid IN (SELECT ctid FROM analytics_visits WHERE created_at < NOW() - INTERVAL '1 year' LIMIT 100000) RETURNING 1) SELECT count(*) FROM deleted;

...	...		@@ -1 +1,9 @@
	1	+	-- delete unused accounts
1	2		SELECT count(*) FROM app_users u WHERE NOT EXISTS (SELECT 1 FROM posts WHERE user_id = u.id) AND NOT EXISTS (SELECT 1 FROM projects WHERE user_id = u.id) AND NOT EXISTS (SELECT 1 FROM access_logs WHERE user_id = u.id AND created_at > NOW() - INTERVAL '1 year');
	3	+
	4	+	-- how many visits will be deleted
	5	+	SELECT count(*) FROM analytics_visits WHERE created_at < NOW() - INTERVAL '1 year';
	6	+	-- delete old analytic visits
	7	+	DELETE FROM analytics_visits WHERE created_at < NOW() - INTERVAL '1 year';
	8	+	-- batch delete
	9	+	WITH deleted AS (DELETE FROM analytics_visits WHERE ctid IN (SELECT ctid FROM analytics_visits WHERE created_at < NOW() - INTERVAL '1 year' LIMIT 100000) RETURNING 1) SELECT count(*) FROM deleted;

Commit 483a3a2

Commit `483a3a2`