I, unsurprisingly, have some thoughts on this 
The function colour problem is very real, and I do think that the idea of making absolutely everything async is probably not sensible at this point; a lot of code is perfectly fine as it is, and we don’t have the people to do such a big overhaul and then test it right now anyway.
I do think, though, that a fully async ORM is worth the cost still; Django is more and more used as a place to tie together a lot of API and database calls (that’s what it’s really good at, if you ask me), and this is the place where we could get significant speedups for common query use cases with some nice wrappers - a way to run multiple queries in parallel and get the results would be the obvious first one.
That said, I do think we’ll never be able to make it fully async only in the ORM core, as the slowdown in sync mode will just be too much. Given that, I’m very realistic about the fact that we may just not be able to write and maintain what are two parallel ORM cores (the query planning could be shared, but the execution layer would have to be written twice due to the function colour problem).
I don’t think templates are worth the effort at the moment, personally; the only real use case here would be streaming things out, and we should just be making sure we can support Jinja2 and its async support for use-cases like that.