One interesting feature that some DBs implement is something like SELECT AS OF SYSTEM TIME (https://www.cockroachlabs.com/docs/stable/as-of-system-time....) which _kinda_ does this.

However in practice this usually dramatically slows down reads if you have to constantly skip over the historic rows so you probably don't want to keep garbage round longer than absolutely necessary. The concept of a historic table mentioned below could be interesting though - especially if it could be offloaded to cold storage.