Auto clean-up feature for the Prefect internal database

[rmnvncnt]

I figured out that our Prefect server deployment was running slow over time and we had trouble scheduling new jobs or updating data in the UI. The issue was the Prefect internal database that was overflowing with logs from old runs and using a script suggested by @Arthurhussey helped mitigate the problem by removing logs older than a week.

While this solution worked in my case, having a scheduled flow tampering with the Prefect database directly might be a source of issues downhill.

It would be very nice if Prefect server had a way of cleaning its logs automatically. For instance, an environment variable similar to PREFECT_EVENTS_RETENTION_PERIOD for flow runs and task runs.

The initial discussion :

@rmnvncnt the Prefect server doesn't have any auto clean-up features right now, but if that's something you'd like, please open an issue so we can discuss it further!

It looks like the issue of deployments not being displayed has been solved by reducing the amount of data in your DB so the scheduler can insert scheduled runs, so I'm going close this issue.

Originally posted by @desertaxle in https://github.com/PrefectHQ/prefect/issues/15919#issuecomment-2483931935

[mikelogaciuk]

That is good idea.

In my company, we delete everything from:

• aritfact
• flow_run
• flow_run_state
• task_run
• task_run_state
• events
• event_resources
• log

That is older than 60 days (WHERE created < (CURRENT_DATE -60);).

And we do of course a periodic VACUUM on those tables in order to get the storage back.