Cleaning Up Old file_content Records to Reduce DMC Database Size

Overview

DMC stores HTML report data in the file_content table. This can grow to dominate the DMC database size over time. These are the reports from deploy, forecast, rollback, and packager operations. This article describes how to clean this table safely.

The script below deletes report content older than a cutoff date. Projects, Environments, Pipelines, and deployment/forecast/rollback/packager history stay intact; only the report content and its path record go.

What happens to old reports after cleanup

On the Status pages, DMC shows a gray dash and "No report available" tooltip for that operation instead of the green checkmark. The operation's changeset, labels, author, and timestamp still display normally, only the report link is gone.

On the Reports pages, attempting to View a report older than the cutoff date will result in an Object Not Found error. Downloading a report older than the cutoff date will result in an empty download.

Before you run this

Take a full backup of the database immediately before running this script. This is a destructive operation and cannot be undone from within DMC.
Test in a non-production environment first, ideally against a restored copy of the customer's actual database.
Run inside a transaction so you can roll back if row counts look wrong.

Instructions

Step 1: Preview what will be affected

SELECT count(*) FROM file_content_path WHERE created_date < :cutoff_date;

Step 2: Run the cleanup

BEGIN;

DELETE FROM file_content_path
WHERE created_date < :cutoff_date;
DELETE FROM file_content fc
WHERE NOT EXISTS (
  SELECT 1 FROM file_content_path fcp
  WHERE fcp.file_content_id = fc.id
);

-- Verify row counts look correct, then:
COMMIT;

Step 3: Verify

Confirm database/table size has dropped as expected.
Spot-check a few deployments older than the cutoff in the DMC Status pages. They should show "No report available" and the operation's other metadata (changeset, labels, author, timestamp) should still display normally.

Background

file_content is written once at the end of each deploy/forecast/rollback/packager run, purely for the DMC's HTML reporting UI. It has no foreign-key ties to any other table and plays no role in the deployment engine itself, deleting old rows (or disabling DMC entirely) has no effect on future deployments, including re-running an old release, which will simply generate a fresh report at run time. The only consequence is that reports created before your cutoff date become inaccessible.

Estimating space freed before you run this

Row count alone doesn't tell you how much disk space you'll get back. Use pg_column_size(), not length() or octet_length(), file_content is stored as Postgres text, and large values get TOASTed (compressed and stored out-of-line), so only pg_column_size() reports the actual on-disk bytes.

This query splits the whole table into what a given cutoff would free versus what it would keep, so you can compare both sides before committing to a date:

SELECT
    (fcp.created_date < :cutoff_date)                     AS before_cutoff,
    count(*)                                              AS row_count,
    pg_size_pretty(sum(pg_column_size(fc.file_content))) AS file_content_bytes,
    pg_size_pretty(
        sum(pg_column_size(fc.file_content)) + sum(pg_column_size(fcp.*))
    )                                                     AS total_bytes

FROM file_content_path fcp
JOIN file_content fc ON fc.id = fcp.file_content_id
GROUP BY before_cutoff
ORDER BY before_cutoff DESC;

This has the same cost problem as the row-count preview: it has to read and decompress every row, and there's no index on created_date to speed up the filter. If a full run is too slow, estimate instead: sample a subset of rows with TABLESAMPLE SYSTEM (1), compute average bytes per row from the sample, and multiply by the row count from pg_stat_user_tables scaled to the date range.