Let routine maintenance run

How to do it

In PostgreSQL, leave autovacuum on and tune it for busy tables instead of turning it off. Alert when the oldest transaction ID age passes 1 billion, because PostgreSQL stops accepting writes near 2 billion to prevent wraparound. In SQLite, run PRAGMA optimize before closing each short-lived connection, and every few hours on long-lived ones. Rebuild statistics after large data loads.

Example

-- PostgreSQL: expect no rows from either query
SELECT relname FROM pg_stat_user_tables WHERE n_dead_tup > n_live_tup AND n_live_tup > 10000;
SELECT datname FROM pg_database WHERE age(datfrozenxid) > 1000000000;

How to check

The statistics view shows a vacuum and analyse within the last 7 days for every table over 10,000 rows. No table has more dead rows than live ones, and the transaction ID age stays under the alert.