* hot and cold do not churn each others indexes or tables, you effectively have only one set of indexes (and data) that's actually churning and the others are stable.
* hot and cold can be treated differently - you can perform more aggressive indexing on data once you know it's out of the hot-set, or partition your hot data onto a tablespace with dedicated hardware while cold data is on bulk hardware, etc. Since queries are planned onto each partition individually, postgres can often select "the right way" for each partition.
* "deleted_at" is a special case of cold table. Dropping any partition is basically free, so if you partition into date ranges, then at the end of your data retention period you just drop the partition for that date range, which doesn't churn indexes or induce vacuuming
If data can never exit the cold-data state, then it's effectively an append-only table too, it just exists as a supplement to your online/hot-data table but it doesn't require special attention/etc. So we're in agreement on that point, that's a good design feature if you can swing it!
(note that for audit logging, I think it's simpler to just do the separate table. But the partition strategy does have some advantages for "cold" or "dead" data as a more generic concern imo)
However, indexes do currently have to be scanned as a whole. But that's only done by autovacuum if there's enough row versions for that to be worth it (in recent versions).
Even with conditional indexes where you exclude deleted data you take a significant performance hit reading dead blocks because there is no way to quickly vacuum them. You accumulate hours of bloat until your vacuum finishes.
You can't beat a separate insert only archive table which you never have to vacuum.