-- TRUNCATE TABLE public.audit;

-- TRUNCATE TABLE public.job_logs;

-- TRUNCATE TABLE public.v2_job_completed;

-- 1. delete audit

DELETE from public.audit where timestamp <  NOW() - INTERVAL '7 days';

VACUUM FULL public.audit;

-- 2. delete job_logs

DELETE from public.job_logs where created_at <  NOW() - INTERVAL '7 days';

VACUUM FULL public.job_logs;

-- 3. delete audit v2_job_completed

DELETE from public.v2_job_completed where started_at <  NOW() - INTERVAL '7 days';

VACUUM FULL public.v2_job_completed;

-- 4. delete v2_job

DELETE from public.v2_job where created_at <  NOW() - INTERVAL '7 days';

VACUUM FULL public.v2_job;

-- 5. log size after cleaned

SELECT

    schemaname || '.' || relname AS table_name,

    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,

    pg_total_relation_size(relid) AS total_size_bytes

FROM

    pg_catalog.pg_statio_user_tables

ORDER BY

    pg_total_relation_size(relid) DESC;