Over at Free Range Bits, Peter Jones has a nice solution to the problem of large Org mode archive files. The idea of an archive file is that you can move a retired subtree of a file to an archive so that the original file doesn’t grow too large. Of course, this means that eventually the archive file will also grow large bringing the usual performance problems.
Jones’ idea is to save an archived subtree to the day’s journal file. In a sense this is the sweet spot. Yes, you’ll have many files holding the archived subtrees instead of one but those files exist anyway and since there’s a new one everyday, they never get very large.
Th mechanics of Jones’ solution is specific to his particular configuration, which uses the Org-roam journaling functionality. In particular, that functionality use a separate file for each day’s journal entries but any journaling system that uses separate daily files will work just as well. For that matter, any information store that uses daily (or weekly, monthly, or other short period) files is also a candidate but journal files seems especially appropriate.
Jones has a simple function that does the archiving for him by using the Org-roam functionality. It would be easy to roll your own depending on your specific setup. The important part is how to tie it into the Org archiving system, which Jones shows in his implementation.
If you use Org mode archiving, you should definitely take a look at Jones’ post. It has some good ideas.