Query partition size from table

CrateDB SQL
, ,
1

Hi Community

Is there a way to get the size of a partition within a table?
I would like to start a new partition when the size exceed a certain amount of space.

Regards

2

Hi,

You could query it with

SELECT s.schema_name, s.table_name, s.partition_ident, sum(s.size)
FROM sys.shards s
WHERE primary
GROUP BY 1,2,3;

or

SELECT s.schema_name, s.table_name, s.partition_ident, tp.values, sum(s.size)
FROM sys.shards s
JOIN information_schema.table_partitions tp
  ON s.schema_name = tp.table_schema
  AND s.table_name = tp.table_name
  AND s.partition_ident = tp.partition_ident
WHERE primary
GROUP BY 1,2,3,4;

if you want the partition column values.

Be aware that it might be beneficial to use an automatic partition logic with e.g. a generated partition column. A generated partitioned column like part GENERATED ALWAYS AS date_trunc('month',ts) is also used for optimizing query plans and partition skipping.

2 Likes
3

@proddata Thanks for the fast response! :heart_eyes:

Unfortunately automatic partition logic by time failed in my case due to the fact, that incoming data varies massively. Since all my data pass through an ingest script I just thought about checking partition size from time to time and increase the partition_id by one if my limit has been reached.