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

תמיכה
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!

2

It’s a good question, I think. But note that the linked topic has been updated and now says you need 2X database size as free disk space - which, it seems, you do already have.

In my own case, I see I have many unpruned docker images, a large swap file, all my backups, and in one case 4G of journal files too.

So, as a complement to reducing the size of the database on disk, one can look at other things on disk which can be reduced.

3

You can control the retention period of this one using site settings.

Those are meant to be kept forever, and they are usually the largest tables in most instances, so I wouldn’t worry about that.