This is actually what we're building @ Bemi https://bemi.io/

We're hoping to make it so that this becomes automatic with Postgres vs moving to specialized databases.

We recently open sourced the core tech that implements system versioned tables https://github.com/BemiHQ/bemi, check it out if interested :)