@yanokwa Hi. I did cleared attachments with https://docs.getodk.org/central-api-submission-management/#clearing-a-submission-attachment now I can see on Central that attachment are missing
I then did the VACUUM FULL; operation but after it finished the size of the DB remained unchanged, as seen with
SELECT pg_size_pretty(pg_database_size('odk')) AS size;
before and after clearing attachments with the Central API.
I did another check (before and after) with
odk=# SELECT relname, relkind, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 15;
relname | relkind | pg_size_pretty
--------------------------------------+---------+----------------
blobs | r | 116 GB
pg_toast_16560 | t | 116 GB
pg_toast_16560_index | i | 1294 MB
audits | r | 84 MB
client_audits | r | 74 MB
submission_defs | r | 39 MB
client_audits_start_index | i | 13 MB
audits_action_acteeid_loggedat_index | i | 9800 kB
audits_acteeid_loggedat_index | i | 6992 kB
submissions | r | 6160 kB
audits_actorid_action_loggedat_index | i | 6040 kB
form_defs | r | 5240 kB
pg_toast_16915 | t | 5024 kB
form_field_values | r | 4728 kB
submission_attachments | r | 4392 kB
(15 rows)
As far as I have understood, when UPDATING rows with BLOBs rather then DELETING them (as I assume the “clear submission attachments” API method does):
a new row version is created
the old row version remains in place
the TOASTed BLOB rows referenced by the old row are still there
Is this the correct interpretation? If yes, does this mean that the only way to really shrink the database size is to DELETE submissions with attachments and wait 30 days (other than using a S3 compatible storage)?
thanks in advance