Creating storage policies

Vertica meta-function SET_OBJECT_STORAGE_POLICY creates a storage policy that associates a database object with a labeled storage location. When an object has a storage policy, Vertica uses the labeled location as the default storage location for that object's data.

You can create storage policies for the database, schemas, tables, and partition ranges. Each object can be associated with one storage policy. Each time data is loaded and updated, Vertica checks whether the object has a storage policy. If it does, Vertica uses the labeled storage location. If no storage policy exists for an object or its parent entities, data storage processing continues using standard storage algorithms on available storage locations. If all storage locations are labeled, Vertica uses one of them.

Storage policies let you determine where to store critical data. For example, you might create a storage location with the label SSD that represents the fastest available storage on the cluster nodes. You then create storage policies to associate tables with that labeled location. For example, the following SET_OBJECT_STORAGE_POLICY statement sets a storage policy on table test to use the storage location labeled SSD as its default location:

=>  SELECT SET_OBJECT_STORAGE_POLICY('test','ssd', true);
            SET_OBJECT_STORAGE_POLICY
--------------------------------------------------
Object storage policy set.
Task: moving storages
(Table: public.test) (Projection: public.test_b0)
(Table: public.test) (Projection: public.test_b1)

(1 row)

Creating one or more storage policies does not require that policies exist for all database objects. A site can support objects with or without storage policies. You can add storage policies for a discrete set of priority objects, while letting other objects exist without a policy, so they use available storage.

Creating policies based on storage performance

You can measure the performance of any disk storage location (see Measuring storage performance). Then, using the performance measurements, set the storage location performance. Vertica uses the performance measurements you set to rank its storage locations and, through ranking, to determine which key projection columns to store on higher performing locations, as described in Setting storage performance.

If you already set the performance of your site's storage locations, and decide to use storage policies, any storage location with an associated policy has a higher priority than the storage ranking setting.

You can use storage policies to move older data to less-expensive storage locations while keeping it available for queries. See Creating storage policies for low-priority data.

Storage hierarchy and precedence

Vertica determines where to store object data according to the following hierarchy of storage policies, listed below in ascending order of precedence:

1. Database

2. Schema

3. Table

4. Table partition

If an object lacks its own storage policy, it uses the storage policy of its parent object. For example, table Region.Income in database Sales is partitioned by months. Labeled storage policies FAST and STANDARD are assigned to the table and database, respectively. No storage policy is assigned to the table's partitions or its parent schema, so these use the storage policies of their parent objects, FAST and STANDARD, respectively:

When Tuple Mover operations such as mergeout occur, all Income data moves to the FAST storage location. Other tables in the Region schema use their own storage policy. If a Region table lacks its own storarage policy, Tuple Mover uses the next storage policy above it—in this case, it uses database storage policy and moves the table data to STANDARD.

Querying existing storage policies

You can query existing storage policies, listed in the location_label column of system table STORAGE_CONTAINERS:

=> SELECT node_name, projection_name, location_label FROM v_monitor.storage_containers;
    node_name     |   projection_name    | location_label
------------------+----------------------+----------------
 v_vmart_node0001 | states_p             |
 v_vmart_node0001 | states_p             |
 v_vmart_node0001 | t1_b1                |
 v_vmart_node0001 | newstates_b0         | LEVEL3
 v_vmart_node0001 | newstates_b0         | LEVEL3
 v_vmart_node0001 | newstates_b1         | LEVEL3
 v_vmart_node0001 | newstates_b1         | LEVEL3
 v_vmart_node0001 | newstates_b1         | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | LEVEL3
 v_vmart_node0001 | states_b0            | SSD
 v_vmart_node0001 | states_b0            | SSD
 v_vmart_node0001 | states_b1            | SSD
 v_vmart_node0001 | states_b1            | SSD
 v_vmart_node0001 | states_b1            | SSD
...

Forcing existing data storage to a new storage location

By default, the Tuple Mover enforces object storage policies after all pending mergeout operations are complete. SET_OBJECT_STORAGE_POLICY moves existing data storage to a new location immediately, if you set its parameter enforce-storage-move to true. You might want to force a move, even though it means waiting for the operation to complete before continuing, if the data being moved is old. The Tuple Mover runs less frequently on older data.