You could do a rolling archive of sorts (well, just taking more permanent snapshots, essentially). Since the record limit is per base and there's no base limit per workspace, there's really nothing stopping you from duplicating your base once per year/quarter/month, using the duplicate as your backup, and then deleting older records from the "live" base as needed.
The only issue would be ensuring that if anyone has to look up an older record not in the live base, that they go to the most recent base that includes that record so they get the most up-to-date version.
As for the on-demand scenario, it's essentially just "checking out" (as in a library) a subset of records at once from a separate database into Airtable, and then checking it back in once you're doing working with it. Examples include POS systems where you're not really interacting with records once they're created. In that case, they can be archived on a different site and only be called up if you're processing a refund, for example.
