Statistics of table size not being refreshed

CrateDB
1

Continuing the discussion from How to find the size of single row in crate db:

Thanks for the answer @proddata:

select 
  schema_name,
  table_name,
  sum(num_docs) nb_records,
  SUM(size) table_size_bytes,
  sum(size) / sum(num_docs)::DOUBLE as avg_size_in_bytes
from sys.shards
where primary and table_name = '<table_name>'
group by 1,2 limit 100;

I have been deleting the records from the table, and the nb_records is effectively reduced. The problem is that table_size_bytes is not being reduced, and as result avg_size_in_bytes increases.

I tried REFRESH TABLE "doc"."table_name" ; but it did not help.

Can someone help or provide more information on how to refresh or force refreshing to get the latest statistics ?

2

CrateDB only soft deletes values to avoid expensive index operations.
You can run OPTIMIZE TABLE <table_name> to force delete those entries.

also see OPTIMIZE — CrateDB: Reference

this would happen over time automatically btw, and nothing that you would need to do manually in day to day operations