Every mature general-purpose database system makes use of both CoW/immutable design principles as well as locking and update-in-place in various contexts. It remains to be seen how "temporal databases" (as you call them) will adapt to real-world demands, but it might not look as radically different as you think.
Also, for anyone who thinks that CoW is the be-all-end-all design principle, I have my doubts. The main reason is that it doesn't compose very cleanly -- a design that involves CoW over CoW over CoW starts to look wasteful after a while.
That being said, I would not be surprised if some of these new databases found some new trade-offs that worked very nicely and got adopted by more traditional systems. Storage is not nearly as much of a concern for many kinds of problems, and it seems natural that such a shift would lead to some design changes.
When he describes traditional databases:
"What’s happening in such a database is a combination of short-term immutability, read and write optimizations to save and/or coalesce redundant work, and continuous “compaction” and reuse of disk space to stabilize disk usage and avoid infinite growth."
He's saying that's what people arrive at after working out the issues with immutability. When you say: "Increasingly these implementation constraints will disappear as people attack them." the author would probably respond: "Yes, and people will attack them with a lot of the same strategies that SQL databases are already using today".