Should there be a mechanism to reap builds?

[evanchaoli]

Concourse has a mechanism to reap build logs, but I'm not aware a way to reap builds, looks like to me the table builds just keeps growing. Our prod cluster now has 0.7M tuples in builds, I'm afraid that once table builds holds millions of tuples, the overall performance could be badly impacted.

After some investigation, I found some pipelines run periodical jobs every few minutes similar to cron jobs, which contributes significant amount of total builds. The pipeline owners agreed that they don't need to retain all builds, but they don't know how to reap old builds.

Similar to build_log_retention_policy, I think we should support build_retention_policy. If a job doesn't configure build_retention_policy, Concourse may by default retain all builds of the job; but if a job may configures to retain at most, say 500 builds, or last 30 days' builds, then Concourse should reap old builds of the job.

This feature would help limit size of table builds, and thus benefit overall performance of Concourse, and make a Concourse cluster to be able to host more pipelines.

[robwhitby]

Even without proper support in Concourse, as a first step it would be really handy to have a SQL script to safely prune the database of builds older than n days. Our db just keeps growing and growing, db load and query performance really suffer because we have millions of builds going back to 2018. If we could easily+safely delete everything e.g. < 2020 that would be a big win.

[evanchaoli]

@robwhitby You may delete old builds by

# delete builds older than 120 days of specified pipeline
delete from builds where pipeline_id = <pipeline id> and end_time < now() - '120 days'::interval

# delete builds older than 120 days of specified team
delete from builds where team_id = <team id> and end_time < now() - '120 days'::interval

# delete all builds older than 120 days 
delete from builds where end_time < now() - '120 days'::interval

Only thing to note is, before you delete builds, make sure build logs have been reaped, otherwise, I'm afraid build log might be leaked after builds are deleted.

CAUTION: this is not an official guide from Concourse team, though I have done that on my cluster, you should take your own risk to do it.

[robwhitby]

Thanks, I was hoping to prune all the related tables too, like build_resource_config_version_[inputs|outputs], resource_config_versions, pipeline_build_events etc etc. I think it's complicated by the danger of deleting too much and causing new builds to start running.