Umami
Umami is a simple, privacy-focused web analytics tool and an alternative to Google Analytics. Running Umami on PikaPods? These notes cover a few things worth knowing. Anything else is covered in the official Umami docs.
No Analytics after Update
Section titled “No Analytics after Update”After your pod was updated to version v1.39.4 on Nov 2, 2022, you may see a blank analytics page. This is due to authentication changes and can be resolved easily by logging out and back in again, as mentioned here.
Clear Old Analytics Data
Section titled “Clear Old Analytics Data”Umami does not currently include a built-in option to automatically purge old analytics data. Large installations can therefore grow over time, especially in the website_event and session tables. If revenue tracking, heatmaps or session replay are enabled, revenue, heatmap_event and session_replay can also grow.
Before deleting data, make sure you have a backup or are comfortable permanently removing old analytics history. The following examples remove data older than 3 months. Session replay recordings are much larger, so they are removed after 1 month, except for replays saved in Umami.
Optional: preview how many rows would be removed:
SELECT 'event_data' AS table_name, COUNT(*) AS rowsFROM event_dataWHERE website_event_id IN ( SELECT event_id FROM website_event WHERE created_at < NOW() - INTERVAL '3 months')UNION ALLSELECT 'website_event', COUNT(*)FROM website_eventWHERE created_at < NOW() - INTERVAL '3 months'UNION ALLSELECT 'session_data', COUNT(*)FROM session_dataWHERE created_at < NOW() - INTERVAL '3 months'UNION ALLSELECT 'revenue', COUNT(*)FROM revenueWHERE created_at < NOW() - INTERVAL '3 months'UNION ALLSELECT 'heatmap_event', COUNT(*)FROM heatmap_eventWHERE created_at < NOW() - INTERVAL '3 months'UNION ALLSELECT 'session_replay', COUNT(*)FROM session_replayWHERE created_at < NOW() - INTERVAL '1 month' AND visit_id NOT IN (SELECT visit_id FROM session_replay_saved)UNION ALLSELECT 'session', COUNT(*)FROM "session"WHERE created_at < NOW() - INTERVAL '3 months';Delete analytics data older than 3 months in the correct order:
BEGIN;
DELETE FROM event_dataWHERE website_event_id IN ( SELECT event_id FROM website_event WHERE created_at < NOW() - INTERVAL '3 months' OR session_id IN ( SELECT session_id FROM "session" WHERE created_at < NOW() - INTERVAL '3 months' ));
DELETE FROM website_eventWHERE created_at < NOW() - INTERVAL '3 months' OR session_id IN ( SELECT session_id FROM "session" WHERE created_at < NOW() - INTERVAL '3 months' );
DELETE FROM session_dataWHERE created_at < NOW() - INTERVAL '3 months' OR session_id IN ( SELECT session_id FROM "session" WHERE created_at < NOW() - INTERVAL '3 months' );
DELETE FROM revenueWHERE created_at < NOW() - INTERVAL '3 months' OR session_id IN ( SELECT session_id FROM "session" WHERE created_at < NOW() - INTERVAL '3 months' );
DELETE FROM heatmap_eventWHERE created_at < NOW() - INTERVAL '3 months';
DELETE FROM "session"WHERE created_at < NOW() - INTERVAL '3 months';
COMMIT;Session replay recordings can be very large, so remove them in batches. Run this repeatedly until it reports DELETE 0:
DELETE FROM session_replayWHERE ctid IN ( SELECT ctid FROM session_replay WHERE created_at < NOW() - INTERVAL '1 month' AND visit_id NOT IN (SELECT visit_id FROM session_replay_saved) LIMIT 20000);Note that the delete can take a while on large databases. If your database is larger than about 20 GB, contact support and we can run the cleanup for you. PostgreSQL does not return the freed space to disk right away, but it reuses it for new data, so the database stops growing.