Well, many SQL databases, although not SQLite, have table partitioning feature which does exactly this. The main benefit is that one can configure tablespace (in PostgreSQL terms; essentially the drives to store data) and storage parameters separately for each partition.

Indexes are just the tip of the iceberg (but sometimes just using partial indexes may do wonders), it’s ability to tweak stuff like fillfactor for a frequently updated small portion of the table (e.g. active sessions vs archived sessions) is that makes a lot of difference.