1. What is the issue? Please be detailed.
I am trying to tidy up my Central install and reduce its overall size. I have a few thousand images associated with different forms and I intend to use the API functionality to do this:
My question is whether this will release any space within the database, and if not, is there a TRUSTED way of reducing the database size / allocation?
I am also using Borg backup l for the backup of Central, and getting close to my current cloud storage limits for this aspect, so would also be useful as a means of avoiding increasing costs if I can remove media and their associated space from each repository going forward (I know the monthly additional costs are not huge, but it all adds up, and I'm conscious of the carbon footprint associated with all this data I have floating around!)
2. What steps can we take to reproduce this issue?
3. What have you tried to fix the issue?
I haven't tried this yet, but have all my preparations in place!
For your specific scenario, I believe this should work, though maybe not for the reason you think. It also may take ~24 hrs for you to see the size reduction. This is what will happen under the hood when you DELETE a submission attachment via the API: the data will continue be stored in the database as a blob, but that blob will be unlinked from the submission attachment record. Then a daily cron job for purging deleted forms that have been in the trash for 30 days will do its work and also remove any unattached blobs, including the submission attachment blobs. You could also run that form purging script manually. Everything else should still function because Central is robust to missing submission attachments.
Just be aware that if you delete these attachments, even before they are purged from the database, it wont really be possible to get them back! So proceed with caution!
We are also actively working on ways to reduce storage size in Central, including:
making it possible to store blobs outside of the database
Hi @ktuite
Thanks for that confirmation and the caveats - really helpful to 'understand' what might be going on under the hood with the blobs.
I'm assuming that the daily cron script is already running (not something I should have set up?) - would it be wise for me to know a manual purging script? Or am I best left in the dark with that comfort that Central is doing its stuff?
You can probably tell how cautious I am with submissions by having posted on the forum before trying it (I know, very un-male of me!) - I have ensured I have local copies of attachments as well as the cloud back ups... Still hesitating - I have a redundant form that has some attachments so that's top of my list...
I wanted to clarify that when I mentioned "deleting" a submission attachment via the API, I meant the same thing as "clearing" an attachment. See API docs for Clearing a Submission Attachment (which is done with a DELETE request).
Under the hood, this will unlink the blob that stores the data for that attachment. The data will still exist in the DB taking up space until the daily purge cron job. The cleared submission attachment blobs should be deleted from the DB at that time. The 30 day wait period shouldn't apply because the purge script just removes any and all "unattached" blobs.
The only way some blobs may not be deleted is if they are shared, e.g. the same file with the exact same contents was somehow uploaded for two separate attachments, and were stored by the same blob. If only one of the attachments was cleared but the other remained, then the blob would not be deleted by the purge script.
We just discovered that if submissions have been edited, this technique of clearing the attachment to free up space does not work. The "clear attachments" API only works on the current version of the submission.
- I have a redundant form that has some attachments so that's top of my list...