A "good" host has no MySQL limit; a "good" hosts allows you to share your actual webspace with your MySQL space.
As for pruning content, no matter what you prune to, you'll have to prune to again; it'll be a never-ending cycle.
And average for a large community using a business-class forum software (such as UBB and others) is about couple of gigs.
No, UBB.Threads has no such archiving process at this time.
Also, on a VPS you'd have X ammount of server space, to use as you wish, may it be through MySQL or HTML; its like having your very own server, only shared with a handful of others versus a boatload of others (like at a sharedhost)