show partition size

maksmaks Community Edition User

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?

Tagged:

Answers

  • Bryan_HBryan_H Vertica Employee Administrator
    edited September 2022

    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 |

  • maksmaks Community Edition User

    @Bryan_H thanks a lot!

  • marcothesanemarcothesane - Select Field - Administrator

    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        
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file