This is practically the #1 use-case for partitions imo. Partitions are tables with syntactic sugar (that Postgres understands in its query planner) so partitio

This is practically the #1 use-case for partitions imo. Partitions are tables with syntactic sugar (that Postgres understands in its query planner) so partitions maintain their own indexes and are vacuumed individually. If you structure your partitions into hot/cold, or hot/cold_day1/cold_day2/etc then you get several advantages: * 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)