How would dual licensing go down, ideally in these cases?

I figured if that worked, Elastic, Redis, MariaDB, Mongo, Timescale, and Cockroach Labs would have gone that route

Start out that way, and you'll find it harder to build a user base. So instead companies have done it as a bait and switch (planned or otherwise)

Would it be fair to do something where you start off with the BSL approach where older code automatically becomes fully open source (like GPL or Apache), with the exceptions that the first N versions or N years of work will start off fully open, then the BSL stuff will kick in later?

I think this would be fair, and it has been done. Ghostscript had two branches, Aladdin Ghostscript and GNU Ghostscript, where GNU Ghostscript is approximately one year behind.

The idea behind dual licensing is that hosting providers would pay for a license and others wouldn't. The arguments that AGPL isn't strong enough are pretty speculative.