-- PostgreSQL maintenance plan 
-- Target: watchdocstats
-- Update: 2026
 
-- 1. DATABASE ANALYSIS (Updating statistics for the optimizer)
-- Helps PostgreSQL choose the best execution plans
ANALYZE;
 
-- 2. PURGE OF OLD DATA (> 2 years)
-- In PostgreSQL, DELETE is effective, but a VACUUM must follow.
DELETE FROM documents WHERE docdateoperation < NOW() - INTERVAL '2 years';
DELETE FROM incidents WHERE incdate < NOW() - INTERVAL '2 years';
DELETE FROM queuescounters WHERE cntdate < NOW() - INTERVAL '2 years';
 
-- 3. VACUUM(Cleaning up space left vacant by purging)
-- VACUUM ANALYZE frees up internal space and updates statistics after purging.
VACUUM ANALYZE documents;
VACUUM ANALYZE incidents;
VACUUM ANALYZE queuescounters;
 
-- 4. REINDEX (If the database is heavily used for writing)
-- Optional: only do this once a week, as it blocks writing to the table.
-- On recent versions (12+), you can add CONCURRENTLY to avoid blocking.
REINDEX TABLE documents;
REINDEX TABLE incidents;