Completely remove old tasks to reduce the data base size

[manuelhaussmann]

Hi,
over the last couple of years I have acquired a large set of done and deleted tasks which result in a rather large sqlite file. Is there a way to selectively remove them? I could just restart with a new data base, but would like to keep part of them, while removing all the irrelevant minor stuff that are recurring daily.

Neither delete nor purge really seem solve this.

Thank you for your work!

[djmitche]

SQLite doesn't automatically shrink databases -- https://sqlite.org/lang_vacuum.html

However, you could vacuum the database yourself:

$ sqlite3 .task/taskchampion.sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> vacuum;

hopefully that reduces the size?

[smemsh]

https://www.sqlite.org/pragma.html#pragma_auto_vacuum

[djmitche]

Adding auto-vacuum automatically would be difficult:

Auto-vacuuming is only possible if the database stores some additional information that allows each database page to be traced backwards to its referrer. Therefore, auto-vacuuming must be turned on before any tables are created. It is not possible to enable or disable auto-vacuum after a table has been created.

I think this is a sufficiently niche situation that this discussion can serve as a fix.

[smemsh]

Personally, my preference is to explicitly vacuum, but thinking of all the people that might use Taskwarrior -- and for who knows how long and with what usage patterns -- it might be sensible to have auto-vacuum be the default for new installations. It wouldn't have to be grandfathered in for existing DBs, because those already up and running could either just manually vacuum, or dump/init/load if they really wanted to enable auto-vacuum. A release note would probably be enough. For most people, it wouldn't be worth it (and it's only space, not performance that is reclaimed). New users would get it for free.

Just an idea, don't feel strongly about it, just throwing it out there. I wonder if using vaccum reclaimed enough space to be worthwhile for @manuelhaussmann ?

[manuelhaussmann]

Thanks for the tips. Given the answers I realized, that my question was probably not correct for what I was actually thinking about.
I have the feeling that deleting recurring tasks takes longer than "regular" tasks, and that this increases with the age of the parent task, i.e., the number of tasks it has already created. My idea was that purging them would permanently remove them as far as taskwarrior is concerned. But that might not actually be the case if I understand the discussion in #3644 correctly.

[djmitche]

Looking at the implementation of the purge and delete commands, both will iterate over all child tasks of a recurring task, so should take about the same amount of time.