3 ms·

At least in Postgres, having a huge amount of "dead" data in large tables is problematic because vacuum always has to read the full data set. Even with conditi

by aeyes 4y ago

At least in Postgres, having a huge amount of "dead" data in large tables is problematic because vacuum always has to read the full data set. 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.

layer8 4y ago
Shouldn’t partitioning help with that? (I have no experience with Postgres.)
- tomnipotent 4y ago
  Only if those partitions are on separate storage, otherwise you have the same number of dead tuples/dirty pages.
  - klysm 4y ago
    Not sure that is true, Postgres can rule our entire partitions and scan less stuff I think?
    - tomnipotent 4y ago
      Doesn't help with writes. More dead tuples can mean more pages scanned for the same amount of data.
anarazel 4y ago
Vacuum does not have to read the full data set every time. The visibility map tracks, on a block level, whether all rows in a page are known to be visible to everyone (starting in 8.4 or such) and whether the page is "frozen", i.e., does not contain visibility information that might need vacuuming (starting in 9.6 IIRC). 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).
paulmd 4y ago
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)

by aeyes 4y ago

layer8 4y ago

Shouldn’t partitioning help with that? (I have no experience with Postgres.)

tomnipotent 4y ago
Only if those partitions are on separate storage, otherwise you have the same number of dead tuples/dirty pages.
- klysm 4y ago
  Not sure that is true, Postgres can rule our entire partitions and scan less stuff I think?
  - tomnipotent 4y ago
    Doesn't help with writes. More dead tuples can mean more pages scanned for the same amount of data.

anarazel 4y ago

Vacuum does not have to read the full data set every time. The visibility map tracks, on a block level, whether all rows in a page are known to be visible to everyone (starting in 8.4 or such) and whether the page is "frozen", i.e., does not contain visibility information that might need vacuuming (starting in 9.6 IIRC). 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).

paulmd 4y ago

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)