Some ideas about submissions/attachments management

[manghig]

Thanks @seewhy for your reply.

@seewhy

So far have kept my server alive and in working order for 3 years... I think I have around 10 projects, about 50 forms, 10 or so entity lists and thousands of submissions, most with attachments. So probably not very big, but enough to worry about.

I have less projects and forms and the database size is approaching the 150gb mark, then I need to have at least 150 more free gb on the server to be able to “vacuum full” once submissions/attachment will be purged from trash, if I understand it correctly.

@seewhy

As suggested by @jniles the 'max-pixels' parameter for images is invaluable, even if you are not worried about disk space.

Thanks.

@seewhy

I was running towards the brick wall of 'full disk', so investigated the Delete / clear attachments as soon as it was viable for me to do so. You will still find it in the docs (not removed), also referenced above:

https://docs.getodk.org/central-api-submission-management/#clearing-a-submission-attachment

(the summary text looks like it's pointing to the wrong section of the docs as I type this, so you might need to follow the link)

I think I've posted in the forum too. I can confirm that this works - it removes the attachment only (soft delete and then purge after 30 days) which can then allow the database size to shrink.

So, if I understand correctly the “clean attachments” method puts them in the soft delete/trash status as the deleted submissions, right?

As in UI doesn’t show anything about it (it only show the number of submissions deleted) I assumed that was not the case for attachments.

@seewhy

I think you know this, but you can use a pyODK script to handle bulk deletes:

Yes thanks. I prefer to use other scripting languages but it’s irrelevant here.

@seewhy

@manghig

he database, but it may not free up space for re-use by the operating system

I'm not a database administration expert, but I take that as meaning it frees up space inside the 'tent' of the database, but the 'tent' doesn't automatically collapse inside the warehouse of your disk, so you can't use that space until the size/shape of the tent changes - and there are other factors involved as to how / whether that happens so no black/white answer is correct. My experience is that the Central database reconfigures it's actual size on the disk during it's daily work-out, if there is enough space outside the tent to do so.

It is that maybe I’m just dumb, but I can’t understand this thing/concept.

@seewhy

I have also switched to S3 storage. So far (about 6 months) my provider (not AWS) has charged me a fixed fee for a fixed amount of space, irrespective of bandwidth.

Ah cool, can I kndly ask you some feedback here? Questions/doubts on using S3 compatible storage

@seewhy

I can't speak of any underlying reasons for what is and isn't available in the UI of Central, but it seems reasonable that an Open Source project that allows 'cash-free' access to its systems might have a different approach to a commercial offering that wants to get you 'hooked'. I always expect the 'entry hurdles' to be higher and there could be some 'here be dragons' moments. So the ODK team might not prioritise things that make it 'easy' to administer things, but are likely to provide tools to keep things safe, secure and logical. Respectfully, perhaps some of your desires are tending towards the commercial service?

Not sure if I understand you correctly. Just to be clear: I’m very familiarized with how a Open Source project is run and is developed. I’m a 15+ years contributor of a well known open source project. I contributed to it a lot of time (QA), some code and a substantial amount of money (well north of 20k euros). I know well that asking to add features or fix bugs is not enough and that to get things done is sometimes necessary to be proactive. I’m not asking to implement/fix anything here, I’m just suggesting a few things that I think would be valuable for the project. If it is possible have some of this ideas done as part of a commercial service, then I’m all ears.

@seewhy

Personally I wouldn't want the 30 day purge to be removed / changed - I really like the fact that the ODK team look after us 'idiots' by keeping us away from 'hot surfaces'! Adding complexity so that someone poorly qualified (e.g. me) can break something inadvertently doesn't seem like a good idea.

I have seen this argument in at least another thread. I’m not saying that the 30 day retention period should be removed or changed, I’m arguing that there should be a way to allow at least the system administrator to get around it.

@seewhy

We've all (probably) been guilty of wanting something for nothing - 'free tier' is paid for by someone, and it doesn't always stay that way, so making commercial / long-term decisions on that basis might not be good practice.

see above.

cheers!