Viewing partition storage data

Vertica provides various ways to view how your table partitions are organized and stored.

Vertica provides various ways to view how your table partitions are organized and stored:

  • Query the PARTITIONS system table.

  • Dump partition keys.

Querying PARTITIONS table

The following table and projection definitions partition store_order data on order dates, and groups together partitions of the same year:

=> CREATE TABLE public.store_orders
  (order_no int, order_date timestamp NOT NULL, shipper varchar(20), ship_date date)
  PARTITION BY ((order_date)::date) GROUP BY (date_trunc('year', (order_date)::date));

=> CREATE PROJECTION public.store_orders_super
   AS SELECT order_no, order_date, shipper, ship_date FROM store_orders
   ORDER BY order_no, order_date, shipper, ship_date UNSEGMENTED ALL NODES;

=> COPY store_orders FROM '/home/dbadmin/export_store_orders_data.txt';

After loading data into this table, you can query the PARTITIONS table to determine how many ROS containers store the grouped partitions for projection store_orders_unseg, across all nodes. Each node has eight ROS containers, each container storing partitions of one partition group:

=> SELECT COUNT (partition_key) NumPartitions, ros_id, node_name FROM PARTITIONS
       WHERE projection_name ilike 'store_orders%' GROUP BY ros_id, node_name ORDER BY node_name, NumPartitions;
 NumPartitions |      ros_id       |    node_name
---------------+-------------------+------------------
           173 | 45035996274562779 | v_vmart_node0001
           211 | 45035996274562791 | v_vmart_node0001
           212 | 45035996274562783 | v_vmart_node0001
           213 | 45035996274562787 | v_vmart_node0001
           173 | 49539595901916471 | v_vmart_node0002
           211 | 49539595901916483 | v_vmart_node0002
           212 | 49539595901916475 | v_vmart_node0002
           213 | 49539595901916479 | v_vmart_node0002
           173 | 54043195529286985 | v_vmart_node0003
           211 | 54043195529286997 | v_vmart_node0003
           212 | 54043195529286989 | v_vmart_node0003
           213 | 54043195529286993 | v_vmart_node0003
(12 rows)

Dumping partition keys

The database provides several functions that let you inspect how individual partitions are stored on the cluster, at several levels:

Given the previous table and projection, DUMP_PROJECTION_PARTITION_KEYS shows the contents of four ROS containers on each node:


=> SELECT DUMP_PROJECTION_PARTITION_KEYS('store_orders_super');
 ...
 Partition keys on node v_vmart_node0001
  Projection 'store_orders_super'
   Storage [ROS container]
     No of partition keys: 173
     Partition keys: 2017-01-02 2017-01-03 2017-01-04 2017-01-05 2017-01-06 2017-01-09 2017-01-10
2017-01-11 2017-01-12 2017-01-13 2017-01-16 2017-01-17 2017-01-18 2017-01-19 2017-01-20 2017-01-23
2017-01-24 2017-01-25 2017-01-26 2017-01-27 2017-02-01 2017-02-02 2017-02-03 2017-02-06 2017-02-07
2017-02-08 2017-02-09 2017-02-10 2017-02-13 2017-02-14 2017-02-15 2017-02-16 2017-02-17 2017-02-20
...
2017-09-01 2017-09-04 2017-09-05 2017-09-06 2017-09-07 2017-09-08 2017-09-11 2017-09-12 2017-09-13
2017-09-14 2017-09-15 2017-09-18 2017-09-19 2017-09-20 2017-09-21 2017-09-22 2017-09-25 2017-09-26 2017-09-27
   Storage [ROS container]
     No of partition keys: 212
     Partition keys: 2016-01-01 2016-01-04 2016-01-05 2016-01-06 2016-01-07 2016-01-08 2016-01-11
2016-01-12 2016-01-13 2016-01-14 2016-01-15 2016-01-18 2016-01-19 2016-01-20 2016-01-21 2016-01-22
2016-01-25 2016-01-26 2016-01-27 2016-02-01 2016-02-02 2016-02-03 2016-02-04 2016-02-05 2016-02-08
2016-02-09 2016-02-10 2016-02-11 2016-02-12 2016-02-15 2016-02-16 2016-02-17 2016-02-18 2016-02-19
...
2016-11-01 2016-11-02 2016-11-03 2016-11-04 2016-11-07 2016-11-08 2016-11-09 2016-11-10 2016-11-11
2016-11-14 2016-11-15 2016-11-16 2016-11-17 2016-11-18 2016-11-21 2016-11-22 2016-11-23 2016-11-24 2016-11-25
   Storage [ROS container]
     No of partition keys: 213
     Partition keys: 2015-01-01 2015-01-02 2015-01-05 2015-01-06 2015-01-07 2015-01-08 2015-01-09
2015-01-12 2015-01-13 2015-01-14 2015-01-15 2015-01-16 2015-01-19 2015-01-20 2015-01-21 2015-01-22
2015-01-23 2015-01-26 2015-01-27 2015-02-02 2015-02-03 2015-02-04 2015-02-05 2015-02-06 2015-02-09
2015-02-10 2015-02-11 2015-02-12 2015-02-13 2015-02-16 2015-02-17 2015-02-18 2015-02-19 2015-02-20
...
2015-11-02 2015-11-03 2015-11-04 2015-11-05 2015-11-06 2015-11-09 2015-11-10 2015-11-11 2015-11-12
2015-11-13 2015-11-16 2015-11-17 2015-11-18 2015-11-19 2015-11-20 2015-11-23 2015-11-24 2015-11-25
2015-11-26 2015-11-27
   Storage [ROS container]
     No of partition keys: 211
     Partition keys: 2014-01-01 2014-01-02 2014-01-03 2014-01-06 2014-01-07 2014-01-08 2014-01-09
2014-01-10 2014-01-13 2014-01-14 2014-01-15 2014-01-16 2014-01-17 2014-01-20 2014-01-21 2014-01-22
2014-01-23 2014-01-24 2014-01-27 2014-02-03 2014-02-04 2014-02-05 2014-02-06 2014-02-07 2014-02-10
2014-02-11 2014-02-12 2014-02-13 2014-02-14 2014-02-17 2014-02-18 2014-02-19 2014-02-20 2014-02-21
...
2014-11-04 2014-11-05 2014-11-06 2014-11-07 2014-11-10 2014-11-11 2014-11-12 2014-11-13 2014-11-14
2014-11-17 2014-11-18 2014-11-19 2014-11-20 2014-11-21 2014-11-24 2014-11-25 2014-11-26 2014-11-27
   Storage [ROS container]
     No of partition keys: 173
...