88 GB of Files Nobody Points At: Archive First, Purge Later, and the Hibernate Hook Underneath
Table of Contents
A quarter of the original files on our two Cloudinary accounts have nothing pointing at them. 88.6 GB, about 222,000 files, and the part of it we can count in the database grows by roughly 700 rows a day.
None of them can be identified by looking at the file. You have to look everywhere else.
Part 1 was about files that should have been deleted and weren’t: a vendor setting and two bugs in the delete path. Those fixes stop new leaks. This one is about the backlog, and about the harder question underneath it. How do you know a file is unused when it can be attached in 28 different places?
What we landed on is a nightly job in two steps, archive then purge, with a grace period between them, and a Hibernate listener that keeps a reference table up to date inside the same transaction as every save. The listener is the part I argued with myself about the longest, so it gets most of the words.
A Spring Boot monolith on PostgreSQL and Hibernate 6.6, files on Cloudinary. Client details are anonymised; the counts come from a read replica of production measured in September and October 2026, the timings from a throwaway harness run against the real entities.
1. Why cascade delete can’t do it
Every uploaded file has a media_file row: public id, size, resource type, tenant. The things that use a file point at that row from 28 places: 16 relational ones (join tables and file-id columns), 10 jsonb columns where form answers and custom fields keep file ids as strings, and 2 file-id columns that were never given a foreign key.
media_file (648,000 rows)
^
┌──────────────┬─────┴──────┬──────────────┬─────────────────┐
invoice_media_file intake_request invoice. expense_claim. form answers
(join table) _media_file public_id attachment_id (jsonb, 10 columns)
(join table) (column) (column)
16 relational places + 10 jsonb columns + 2 columns with no FK = 28 places
When an invoice is deleted, the cascade drops the join row and stops there. It has to. The same file can sit on a renewal and on a vendor record, and the invoice’s cascade knows nothing about either. Cascading into media_file would mean, on every delete, checking all 16 places and parsing 10 jsonb columns for a surviving pointer. Nobody wrote that, so the file stays: a row nobody points at, and an asset on Cloudinary that Part 1’s fixes will never see.
The count on the production follower: 90,178 rows with no parent in any of the 16 places, 24.3 GB of files. It was 79,100 on 21 September and 90,178 on 7 October, so about 700 a day. By year of creation, 13,219 of them were made in 2025 and 65,274 in 2026. Five times more, and the year isn’t over.
Postgres isn’t the problem. media_file is 236 MB of a 76 GB database. The 24.3 GB are on Cloudinary, next to the 64.3 GB of files that don’t even have a row anymore.
A file can’t tell you it’s unused. Only the absence of 28 other things can.
2. The shape: one table that knows, an archive, a purge
Three decisions, none of them clever on its own.
A table that answers “who points at this file?” in one lookup. media_file_reference has one row per pointer: public_id, source (which of the 28 places), parent_id, created_at, and a key_name for the jsonb pointers. Primary key on the first three, foreign key to media_file with ON DELETE CASCADE. The clean-up’s question, “does anything reference this file?”, becomes an indexed probe instead of a 16-way union over the parent tables.
Archive before purge. A file with no reference row doesn’t get deleted. Its row moves to media_file_archive, which has the same columns plus a region and an archived_at. Cloudinary isn’t touched. The undo is moving the row back. A file only becomes a candidate once it’s been uploaded for a week, which guards the gap between an upload and the save that attaches it; join tables carry no timestamp, so the upload date is the only clock there is.
Purge after a grace period. Rows that have sat in the archive long enough are purged in a fixed order: lock the row, re-check the references, mark it purged, commit, and only then delete the Cloudinary asset in the region the row recorded. The row stays, as a tombstone. Purge is off by default and has to be switched on separately.
media-file.cleanup.enabled=true
media-file.cleanup.purge-enabled=false
media-file.cleanup.archive-after-days=7
media-file.cleanup.purge-after-days=30
media-file.cleanup.batch-size=500
upload a week later, ARCHIVE 30 days later: PURGE
nothing references row moves to row marked purged, then
it media_file_archive the Cloudinary asset deleted
Cloudinary untouched row kept as a tombstone
<- archive-after -> <------- purge-after ------->
undo: move the row back
Plus an admin endpoint to run the same job with a dry-run flag, so the first runs print what they would archive and archive nothing. That’s how the job is meant to go out: enabled, purge off, dry run first, batch size small.
Two details in the archive table that look like omissions and aren’t. The archive has no foreign key on tenant_id, because archived rows are the only remaining pointer to Cloudinary assets the purge still has to delete, and they have to outlive a tenant deletion. And it carries the region explicitly, because a tenant deletion drops the tenant row the region used to come from, and a purge that doesn’t know the region has to try every account.
Nothing irreversible happens on the night something is found.
3. The whole design rests on the reference table being right
If a save attaches a file and no reference row appears, the job archives a file that’s in use, purges it a month later, and the first symptom is a user’s missing document. So the question is how to make sure the table can’t drift.
Three things make that hard in this codebase. The 28 places are where pointers live; 53 places in the code write one, and most of them never call a service that could insert a reference: the parent’s JPA cascade writes the join rows during the flush, from a Set<MediaFile> someone added to. And a new kind of pointer, a new column or jsonb field, is invisible to every design until someone declares it. A CI guard is planned to fail the build on a suspiciously named column with no foreign key, or a jsonb column nobody has classified.
We looked at three ways to keep the table right.
A: every write site inserts the reference itself. Plain service code, the insert sits next to the write, nothing new to learn. And 53 call sites to update today, plus every feature that attaches a file from now on. A forgotten call doesn’t fail and doesn’t log; it just leaves a file to be archived while it’s in use. No CI check can catch a call that isn’t there. And the cascaded links need a flush before the insert can see them.
B: a JPA entity listener. @PostPersist and @PostUpdate callbacks, a pattern already on 34 entities here. They fire, but in the middle of the flush, when the database is half written: at the callback for a new request, its join rows and its cascaded file’s row don’t exist yet.
A collection change on some entities fires no callback at all. And handing the work off to a Spring transaction phase doesn’t rescue it, for a reason that gets its own section below.
C: one Hibernate event listener, with the SQL deferred to a hook Hibernate runs after its final flush and just before the JDBC commit. Hibernate calls that hook a before-completion process. New pattern, zero of them on main before this one. One hook for the whole application: any code path that saves a declared field is covered, including the ones written next year. It runs after Hibernate’s last flush and before the commit, in the same transaction, so if it fails the save rolls back with it. It sees collection changes. The cost is that it’s Hibernate’s API rather than the JPA standard, so a major Hibernate upgrade can touch it.
| A: write sites | B: JPA listener | C: Hibernate listener | |
|---|---|---|---|
| covers cascade writes | after a manual flush | fires, can’t compute | yes |
| same transaction as the save | yes | fires in it, can’t compute there | yes |
| a forgotten call is possible | 53 today, more later | no | no |
| new pattern | no | no | yes |
C is the one we built. B was the one I wanted, because it was already there, and I ran three throwaway test campaigns trying to make it work before giving up.
4. B against C is one timeline
Both the JPA callback and the Hibernate listener fire inside the flush, the moment Hibernate turns queued changes into SQL. The difference is what happens to the work afterwards.
@Transactional service method
|
| business code: invoice.getMediaFiles().add(file) (nothing flushed yet)
|
v
Spring processCommit
|
|-- triggerBeforeCommit <- @TransactionalEventListener(BEFORE_COMMIT) runs HERE
|
|-- doCommit
| |
| |-- Hibernate commit-time flush
| | entity inserts / updates <- @PostPersist / @PostUpdate fire here
| | collection rows written (join rows not written yet)
| |
| |-- before-completion processes <- C writes the reference rows HERE: all flushed, nothing committed
| |
| |-- JDBC COMMIT
|
|-- triggerAfterCommit <- AFTER_COMMIT runs here (lost if the app dies first)
Read it from the top. A typical service method changes the entity and returns; nobody calls flush(). The change is flushed by the commit itself, inside doCommit.
Spring runs its BEFORE_COMMIT synchronizations before doCommit. So a JPA callback that publishes an event during that flush is publishing it after the BEFORE_COMMIT listeners have already been walked, and the listener never fires. We measured it on a real invoice listener: with no explicit flush, the BEFORE_COMMIT handler didn’t run and the AFTER_COMMIT one did; with an em.flush() added to the method body, both ran.
AFTER_COMMIT fixes the ordering and breaks the guarantee. The save is committed, then the handler runs. If the process dies in between, the invoice has its file and the reference table doesn’t know, and the clean-up will eventually archive a file that’s in use. The whole point of the table is that this can’t happen.
And doing the work inside the callback itself doesn’t help either, because of what the database looks like at that moment. At @PostPersist for a new request saved with a new document: request row written, join rows zero, the document’s own row zero. A reference computed there is empty.
C splits the work in two. The Hibernate listener fires in the same place as the JPA callback but only writes down “invoice 42 touched a file field”, in memory. The SQL runs later, from a BeforeTransactionCompletionProcess, which Hibernate calls after its commit-time flush and before the JDBC commit. Everything is written, nothing is committed, same connection, same transaction. If the recompute throws, the save rolls back with it.
Same transaction or it doesn’t count.
5. What the hook looks like from the entity’s side
One annotation on the field, 16 of them across 14 entities. That’s the whole footprint outside the package that holds the hook.
@ManyToMany(fetch = FetchType.LAZY, cascade = { PERSIST, MERGE, REFRESH, DETACH })
@JoinTable(name = "invoice_media_file", ...)
@MediaFileReferenceHolder(MediaFileReferenceSource.INVOICE_MEDIA_FILE)
private Set<MediaFile> mediaFiles = new HashSet<>();
At boot, a resolver walks Hibernate’s mapping model and works out, for each annotated field (a holder, in the code: the entity plus the field that points at files), the table, the parent column and the file column. Nobody writes that down by hand. An integrator then appends one listener to the insert, update, delete and collection events.
During the flush, the listener does one thing: it marks the parent. The first mark in a transaction registers the two completion processes.
// simplified
void mark(EventSource session, MediaFileHolderMapping holder, UUID parentId) {
var tx = session.accessTransaction();
var marks = marksByTransaction.computeIfAbsent(tx, t -> {
session.getActionQueue().registerProcess(
(BeforeTransactionCompletionProcess) s -> recompute(t)); // after the last flush
session.getActionQueue().registerProcess(
(AfterTransactionCompletionProcess) (ok, s) -> marksByTransaction.remove(t));
return new LinkedHashMap<>();
});
marks.computeIfAbsent(holder, h -> new LinkedHashSet<>()).add(parentId);
}
Before the commit, one SQL statement per source that was touched, over the parents that were marked. Same statement for all 16 sources, with the table and column names filled in from the resolver (simplified: the real one carries schema names and casts):
WITH live AS (
SELECT DISTINCT s.media_file_id AS public_id, s.invoice_id AS parent_id
FROM invoice_media_file s
WHERE s.invoice_id = ANY(:parents)
AND s.media_file_id IS NOT NULL),
stale AS (
DELETE FROM media_file_reference r
WHERE r.source = 'INVOICE_MEDIA_FILE'
AND r.parent_id = ANY(:parents)
AND NOT EXISTS (SELECT 1 FROM live l
WHERE l.parent_id = r.parent_id AND l.public_id = r.public_id))
INSERT INTO media_file_reference (public_id, source, parent_id)
SELECT public_id, 'INVOICE_MEDIA_FILE', parent_id FROM live
ON CONFLICT DO NOTHING
It doesn’t try to work out what changed. It reads what the parent points at right now, deletes the references that aren’t in that set, inserts the ones that are missing. Idempotent, and scoped by parent id rather than tenant, which matters because most join tables don’t carry a tenant column.
invoice 42 saved: file B removed, file C added
live (invoice_media_file, now) A C
refs (before) A B
^ not in live: deleted
refs (after) A C
^ missing: inserted. A untouched.
What it costs, measured on a laptop against the real entities: one extra statement per save, about 0.3 ms on an invoice save whose transaction takes 3.7 ms. A tenant wipe of 5,000 invoices, the largest transaction we have, paid about 20 ms on 5.4 seconds.
There’s a kill switch, media-file-reference.registration.enabled, on by default in the code and switched off in production until the backfill exists. Off, saves keep working and only the reference rows stop; the one-shot backfill (next ticket) rebuilds the table from the 28 places. The only trace of the switch having been off is a warning at boot, so the rule is a rule: if it was, run the backfill before the next archive.
6. What it leaves out, on purpose
The 10 jsonb columns and the 2 columns with no foreign key are the next pull request, same hook, a different live query per column. Until then the archive step has to read those columns itself.
Bulk JPQL deletes and native SQL skip Hibernate’s events, so they skip the hook. A scan of main found nothing that adds a pointer that way; what exists only removes parents, which can leave an extra reference behind, and an extra reference keeps a file, it never loses one.
A save racing the nightly job: the archive step locks the row, re-checks the references, then moves it, so a save that attaches the file at the same moment either sees it, or waits and then fails visibly on the foreign key. Measured on throwaway shapes, not on the real job yet.
A new kind of pointer that nobody annotates is the CI guard’s job, not the hook’s.
And Hibernate 7. The hook compiles against 6.6 and not against 7.2: two API changes to make at the Spring Boot 4 upgrade, the before-completion registration and the way entity events expose their session, plus one decision about stateless sessions, which nothing on main uses. The idea survives the upgrade; two method calls don’t.
7. What generalises
“Is this still used?” is a question about everything else, not about the thing. A file, a row, a flag, an S3 object: none of them carry their own reference count. Either you store the answer when the pointer is written, or you rebuild it on every delete by scanning every place a pointer could be. The second option is what the cascade couldn’t afford, and it’s what the 28 places would have cost the job every night.
A clean-up that deletes production data should start out unable to. Archive, a grace period, purge behind its own switch, a dry run that prints and does nothing. The first time the job runs for real, the worst it can do is move a row to a table you can move it back from.
“Before commit” means two different things in Spring and in Hibernate. Spring’s BEFORE_COMMIT phase runs before Hibernate’s commit-time flush; Hibernate’s before-completion process runs after it. If the change you’re reacting to was never flushed explicitly, which describes most service methods, only the second one sees it. I’d written about the Spring phase earlier this year without that caveat, and I’ve added it there.
Reach for the new pattern when the old one fails the test, not before. The JPA listener was already on 34 entities, and I wanted it to work. Three throwaway campaigns later it had failed each time, and the Hibernate listener had passed the cases it couldn’t. That’s the order to do it in: make the existing pattern prove it can’t, then pay for the new one.
Write the reference in the same transaction as the pointer, or don’t build a clean-up on it.
Part 1 is the ‘Deleted 500’ story: the leaks this job doesn’t have to clean up anymore. Part 3 is the transformations line, 99.5% of our transformations were uploads. For what flush and commit actually do in a Spring service, there’s save, saveAndFlush and REQUIRES_NEW, and the BEFORE_COMMIT phase has its own section in the threads and transactions piece.
