Oliver Bertuch (Oct 16 2026 at 17:54): Oliver Bertuch (Oct 16 2026 at 17:54):To whom it may concern. Deleted ~52M log records from the last 6.5 years from our prod. database today. Took ~90min to delete + vacuum. Here are some handy SQL snippets I picked up along the way. Startup time from 10m down to 2m.
Get list of tables sorted by size
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
pg_size_pretty(pg_table_size(c.oid)) AS table_size,
pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p') -- ordinary tables (and partitioned tables on PG 10+)
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname NOT LIKE 'pg_toast%'
ORDER BY pg_total_relation_size(c.oid) DESC, -- sort by real bytes, not by the pretty text
n.nspname,
c.relname;
Copy old logs to CSV, avoid buffering the full result in client memory.
kubectl exec postgresql-7fb9699986-rwqxc -- psql -U dataverse dataverse -c "COPY (SELECT * FROM actionlogrecord WHERE starttime < '2026-09-01 00:00:00' ORDER BY starttime) TO STDOUT WITH (FORMAT csv, HEADER);" > actionlogrecord-20260501-till-20260901.csv
Remove old logs and free space
Note: Vacuum means downtime! When deleting large numbers of rows, this will take more than just a few minutes!
DELETE FROM actionlogrecord WHERE starttime < '2026-09-01 00:00:00';
vacuum full actionlogrecord;
Last updated: Nov 02 2026 at 07:01 UTC