Why doesn't ducklake_table_column_stats reference a snapshot?

[jayhan94]

I've noticed that ducklake_table_column_stats doesn't reference a snapshot and instead contains the latest statistics. Is there a specific reason for this?

[Mytherin]

Yes, if ducklake_table_column_stats were to provide per-snapshot stats the table could grow very large if there are many snapshots. We would need to have one row per column per snapshot to fully keep track of table stats per snapshot.

By contrast - global stats per snapshot have limited utility. Having global stats is useful since we can use it during query planning and optimization - i.e. if we know an integer field is within the range [0, 100], we can store it using fewer bytes in a hash table. However, the global stats are unlikely to change significantly between snapshots.

The only drawback to having global stats is in case of deletions drastically changing the stats. Currently we only expand the min/max stats on inserts, and leave them as-is on deletions. As a result, the min/max bounds can only really grow - which means the most recent stats are still correct bounds also for all previous snapshots.

If we were to add support to recomputing the min/max bounds after significant deletions to the table, this would only be possible if old snapshots that referred to deleted rows were actually removed.

Perhaps a good compromise is to add a begin_snapshot/end_snapshot field to the table - and then just always update the latest stats in case of insertions to the table (i.e. we would just leave end_snapshot as NULL and never expire the latest stats). We could then implement the recomputing of min/max bounds after deletes by invalidating the previous stats and inserting the new ones starting at the relevant snapshot.

[jayhan94]

Here’s another idea: We only store a limited number of global stat snapshots—for example, the 10 most recent versions. If a request attempts to access older snapshots beyond this range, we default to the oldest available version.