We have a big table with a column where we save all the html sent by our app. In few months, the table has grown a lot. We have a requirement, the HTML must be saved for two months. We want to delete the HTML after two months, but we don't want to delete the row because the row has information that we need.
Can we achieve save space in database updating the column to null? Do we need to do an AUTOVACUUM if the answers is yes?
Thanks a lot!
The update would initially just take up more space, as a new version of each tuple would get written. The old space wouldn't be returned to the OS until all transactions old enough to see the old data were closed and a VACUUM FULL was done. But it would be available for internal reuse after a vacuum. This might be good enough, but letting it just be reused does mean your data for a given time range will be scattered throughout the table, not concentrated in one section.
Do you routinely use this html data, or just need to retain it "just in case"? If the latter, maybe you can just log it outside the database. Then delete the log file when they get old enough, or map them into the database with file_fdw if that becomes necessary.
You might want to partition the data by time, then occasionally copy all the data except html to a new table for an aged time range, then swap the large partition out and the smaller one in.
You could also partition the data horizontally, putting the html in its own table with just one other column, being the primary key which links it up with the rest of the its logical row. Then use one of the previously mentioned methods to get rid of chunks of this data once it ages enough.
