I am very new to LARGE DATABASE related stuff and Postgres, We plan to have a Postgres db with a million of rows that can grow up to Terabytes because rows include binary data (images).
We plan to implement two features of automatic deletion:
- When timestamps of rows are older than (x) days
- When database size on disk reaches a configured limit (like "8 TB")
I struggle with the second feature. If I understand correctly, only
VACUUM FULL reclaims disk space, but it also blocks the table, that's not possible because constantly rows are inserted and there is no time slot for that. Normal
VACUUM just marks the rows as dead and does not reclaim disk space.
My program monitors the table size. When it reaches say 95% (usage) of the configured limit I delete some rows and do a normal
VACUUM. The table size doesn't shrink and on next check it detects that the table size is over 95% (usage) again.
I am curious about if there are better solutions to this problem?