Query ran at 2025-01-27T21:35:03.043Z on CrateDB 6.0.0

SELECT
  table_name,
  SUM(num_docs) as records,
  (SUM(size) / (1024 * 1024)) as total_size_mib,
  (SUM(size) / count(*)) / (1024 * 1024) as avg_size_per_shard_in_mib,
  (SUM(size) / SUM(num_docs) :: DOUBLE) as avg_size_in_bytes_per_record
FROM
  sys.shards
WHERE
  PRIMARY
GROUP BY
  1
ORDER BY
  avg_size_per_shard_in_mib

6 record(s) returned in 0.0543s, showing 15 row(s).

table_name	records	total_size_mib	avg_size_per_shard_in_mib	avg_size_in_bytes_per_record
"taxi_bestcompression"	2964624	304	50	107.64517287858426
"taxi_nocolumnstore_bestcompression"	2964624	309	51	109.3654793997485
"taxi_indexoff"	2964624	326	54	115.51199747421596
"taxi_indexoff_nocolumnstore"	2964624	367	61	129.8943582727523
"taxi"	2964624	415	69	147.0150788093195
"taxi_nocolumnstore"	2964624	452	75	159.91934457792962

Query ran at 2025-01-27T22:04:43.630Z on CrateDB 5.9.8

SELECT
  table_name,
  SUM(num_docs) as records,
  (SUM(size) / (1024 * 1024)) as total_size_mib,
  (SUM(size) / count(*)) / (1024 * 1024) as avg_size_per_shard_in_mib,
  (SUM(size) / SUM(num_docs) :: DOUBLE) as avg_size_in_bytes_per_record
FROM
  sys.shards
WHERE
  PRIMARY
GROUP BY
  1
ORDER BY
  avg_size_per_shard_in_mib

7 record(s) returned in 0.0549s, showing 15 row(s).

table_name	records	total_size_mib	avg_size_per_shard_in_mib	avg_size_in_bytes_per_record
"taxi_indexoff_nocolumnstore_bestcompression"	2964624	142	23	50.2667815547604
"taxi_nocolumnstore_bestcompression"	2964624	221	36	78.40036308145653
"taxi_indexoff_nocolumnstore"	2964624	241	40	85.4500982249351
"taxi_bestcompression"	2964624	301	50	106.47062460534625
"taxi_indexoff"	2964624	329	54	116.4456258196655
"taxi_nocolumnstore"	2964624	329	54	116.47662873942868
"taxi"	2964624	405	67	143.37222831630586

Query ran at 2025-01-27T22:24:27.461Z on CrateDB 6.0.0

SELECT
  table_name,
  SUM(num_docs) as records,
  (SUM(size) / (1024 * 1024)) as total_size_mib,
  (SUM(size) / count(*)) / (1024 * 1024) as avg_size_per_shard_in_mib,
  (SUM(size) / SUM(num_docs) :: DOUBLE) as avg_size_in_bytes_per_record
FROM
  sys.shards
WHERE
  PRIMARY
GROUP BY
  1
ORDER BY
  avg_size_per_shard_in_mib

7 record(s) returned in 0.0546s, showing 15 row(s).

table_name	records	total_size_mib	avg_size_per_shard_in_mib	avg_size_in_bytes_per_record
"taxi_indexoff_nocolumnstore_bestcompression"	2964624	224	37	79.56457985903103
"taxi_bestcompression"	2964624	299	49	106.04130675593262
"taxi_nocolumnstore_bestcompression"	2964624	305	50	107.98966816702557
"taxi_indexoff"	2964624	330	55	116.89085563633027
"taxi_indexoff_nocolumnstore"	2964624	350	58	124.09746025128312
"taxi"	2964624	404	67	143.15506047309879
"taxi_nocolumnstore"	2964624	443	73	156.84691650610668

Update after running

OPTIMIZE TABLE {TABLE} WITH (MAX_NUM_SEGMENTS=1)

Query ran at 2025-01-28T09:53:34.197Z on CrateDB 6.0.0

SELECT
  table_name,
  SUM(num_docs) as records,
  (SUM(size) / (1024 * 1024)) as total_size_mib,
  (SUM(size) / count(*)) / (1024 * 1024) as avg_size_per_shard_in_mib,
  (SUM(size) / SUM(num_docs) :: DOUBLE) as avg_size_in_bytes_per_record
FROM
  sys.shards
WHERE
  PRIMARY
GROUP BY
  1
ORDER BY
  avg_size_per_shard_in_mib

7 record(s) returned in 0.0547s, showing 15 row(s).

table_name	records	total_size_mib	avg_size_per_shard_in_mib	avg_size_in_bytes_per_record
"taxi_indexoff_nocolumnstore_bestcompression"	2964624	115	19	40.913586006184936
"taxi_indexoff"	2964624	145	24	51.316324093713064
"taxi_indexoff_nocolumnstore"	2964624	156	26	55.338895590132175
"taxi_nocolumnstore_bestcompression"	2964624	198	33	70.09250009444705
"taxi_bestcompression"	2964624	221	36	78.25342168180518
"taxi"	2964624	227	37	80.63651511962394
"taxi_nocolumnstore"	2964624	239	39	84.66696653605989