On Wed, 11 May 2022 at 20:02, David Gauthier <dfgpostgres@gmail.com> wrote:
> I have a table with a bytea column which, of course, contains binary data. After 60 days, I no longer need the
binarydata but want to retain the rest of the record. Of course it's easy to just update the bytea column to null for
theolder records. But I can almost imagine this record on disk with a big "hole" in the middle where the bytea data
usedto be. Is there a PG daemon (the vacuum ?) that will "heal the hole" in time?
We have a similar situation, and run a weekly task that backs up the
table, sets the outdated records to NULL, truncates the original
table, then copies the records back in. With this approach the disk
space is reclaimed immediately. The table is only written to by
automated processes, so we have a weekly maintenance window where
these aren't running. This might be a possible approach.