Mattermost Audit Table Cleanup Guide

The audit table in Mattermost records important system actions including user login/logout attempts, channel activity (create, update, join, leave), team management (create, invite, remove members), user role or status changes, administrative updates (configuration and license changes), plugin operations (install, enable, disable), OAuth activity (app registration, token generation), and license uploads or removals.

Starting with Mattermost v5.20, the audit table is excluded from automatic retention cleanup. From this version onward, any audit log data remains in the database indefinitely unless manually deleted. 

How to Manually Clean the Audit Table 

To delete entries older than 90 days, run the following SQL command:

DELETE FROM audits
WHERE to_timestamp(createat / 1000) < NOW() - INTERVAL '90 days';

When rows are deleted in PostgreSQL, the space they occupied isn't immediately reclaimed; it remains as dead tuples until the database performs a cleanup operation. To reclaim this storage and make it available for reuse, you can use PostgreSQL’s standard VACUUM process.

To safely and efficiently reclaim storage from deleted rows, run the following command:

VACUUM audits;  

 If PostgreSQL’s autovacuum daemon is correctly configured and running, it will perform routine vacuuming automatically, often eliminating the need for manual intervention.

Note : Always test in staging first. Any manual database maintenance should be validated in a non-production environment before being applied to live systems.