Database cleanup before PostgreSQL 18 upgrade: recommended retention period for log/statistics tables?

Support
1

Hi everyone,

I saw the PostgreSQL 18 upgrade guide for self-hosted Discourse:

It mentions that the upgrade process requires a significant amount of additional disk space, so I started checking my disk usage and database size before attempting the migration.

My current disk usage:

/dev/sda1        97G   44G   54G  45% /

My Discourse database size is about 21 GB. I found that most of the space is not used by posts, but by several statistics and log tables.

The largest tables are:

topic_views              5.2 GB
post_timings             1.9 GB
browser_pageview_events  1.8 GB
ai_api_audit_logs        1.6 GB
incoming_links           1.5 GB
user_auth_token_logs     1.2 GB

For comparison:

posts                    844 MB

I checked the data ranges:

topic_views:
2015-04-03 ~ 2026-08-03

incoming_links:
2015-04-09 ~ 2026-08-03

user_auth_token_logs:
2021-08-15 ~ 2026-08-03

ai_api_audit_logs:
2026-02-04 ~ 2026-08-03

browser_pageview_events:
2026-05-28 ~ 2026-08-03

I understand that these tables have different purposes, but I am not sure what retention periods are considered reasonable for a production Discourse instance.

My questions:

  1. For user_auth_token_logs, how long do you usually keep the records?

    • 6 months?
    • 1 year?
    • Longer for security auditing?

  2. For tables like incoming_links, topic_views, and post_timings, do you normally keep all historical data, or periodically remove older records?

  3. Is there any recommended cleanup or maintenance procedure before a PostgreSQL major version upgrade?

So far, I have not deleted anything. I only ran:

vacuumdb --analyze discourse

My goal is to free unnecessary disk space before upgrading to PostgreSQL 18, while keeping normal Discourse functionality and useful audit information.

I would appreciate any recommendations or real-world experience from people running self-hosted Discourse sites.

Thanks!

1 Like