show partition size

maks

Hello. Could you help me? There is a vertica cluster (version 12.0). The database has a table for which partitions are configured. The table is large, so I want to delete the oldest partitions, the largest ones. To do this, I need to know the size of each partition. How can I see the size of a partition?

Bryan_H

You can find this in v_monitor.partitions, though you may want to sum across all nodes, and JOIN to v_catalog.projections to calculate across multiple projections on the same table:

d2=> select * from v_monitor.partitions limit 1;
partition_key | projection_id | table_schema | projection_name | ros_id | ros_size_bytes | ros_row_count | node_name | deleted_row_count | location_label
---------------+-------------------+--------------+-------------------+-------------------+----------------+---------------+---------------+-------------------+----------------
2021-01-01 | 45035996273709536 | public | dump1090new_super | 45035996656645565 | 370795072 | 23918443 | v_d2_node0001 | 0 |

maks

@Bryan_H thanks a lot!

marcothesane

This is more detailed - if you want, for example, to find the 4 biggest partitions in the database:

SELECT 
  t.table_schema
, t.table_name
, p.partition_key
, SUM(p.ros_size_bytes) AS ros_size_bytes
FROM TABLES t
JOIN projections pj ON t.table_id = pj.anchor_table_id
JOIN partitions p USING(projection_id)
GROUP BY 1 , 2 , 3 ORDER BY 4 DESC LIMIT 4;
table_schema|table_name  |partition_key|ros_size_bytes
the_schema  |dc_the_table|2021-02-02   |1,556,987,825,392
the_schema  |dc_the_table|2021-02-08   |1,556,987,825,392
the_schema  |dc_the_table|2021-02-01   |1,556,987,825,392
the_schema  |dc_the_table|2021-02-12   |1,556,987,825,392