Creating Storage Policies

You create a storage policy to associate a database object with a labeled storage location. To do so, use the SET_OBJECT_STORAGE_POLICY function. When a storage policy exists, Vertica uses the labeled location as the default storage location for the object data. Storage policies let you determine where to store your critical data. For example, suppose you create a storage location with the label SSD representing the fastest available storage on the cluster nodes. You can then create storage policies to associate tables with that labeled location. One storage policy can exist per database object.

Note: You cannot include temporary files in storage policies. Storage policies are for use only with data files on storage locations for DATA. Storage policies are not valid for USER locations.

You can create a storage policy for any database object (database, schemas, tables, and partition ranges). Each time data is loaded and updated, Vertica checks to see whether the object has a storage policy. If it does, Vertica automatically 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.

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 have 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.

Storage Levels and Priorities

Vertica assigns storage levels to database objects. The database is the highest storage level (because nothing exists above the database level), and partition min_ and max_key ranges are considered the lowest level objects. In addition to storage levels, storage priorities exist. The lower the storage level of an object, the higher its storage priority.

Consider this example of database objects, listed in storage level order, with the highest level, Sales database, first:

Object	Storage Level	Storage Policy	Storage Priority	Labeled Location
`Sales` (database)	Highest	YES	Lower	STANDARD
`Region` (schema)	Medium	NO	Medium	N/A
`Income` (table)	Lower	YES	Higher/highest	FAST
`Month` (partitions)	Lowest	NO	Highest	N/A

Storage policies exist for the database and table objects, with default storage on the locations STANDARD and FAST, respectively.

In this case, when TM operations occur, such as moveout and mergeout, table data has the highest priority. The TM moves data from WOS to ROS to the FAST labeled location.

Any schema data changes are prioritized after table data. Because the Region schema has no storage policy, Vertica searches up the storage levels for a policy. In this case, that is the Sales database itself. If a database storage policy is in effect, Region schema data is moved from WOS to ROS to the STANDARD storage location, using its parent object's default storage location.

If the Sales database object had no storage policy, the TM operations would use existing storage locations and mechanisms.

Using the SET_OBJECT_STORAGE_POLICY Function

To set a storage policy, use the SET_OBJECT_STORAGE_POLICY function.

This example shows how to set a storage policy for the table test to use the storage 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)

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

VMART=> select node_name, projection_name, storage_type, location_label from v_monitor.storage_containers;
    node_name     |   projection_name    | storage_type | location_label
------------------+----------------------+--------------+---------------
 v_vmart_node0001 | states_p             | ROS          | 
 v_vmart_node0001 | states_p             | ROS          | 
 v_vmart_node0001 | t1_b1                | ROS          | 
 v_vmart_node0001 | newstates_b0         | ROS          | LEVEL3
 v_vmart_node0001 | newstates_b0         | ROS          | LEVEL3
 v_vmart_node0001 | newstates_b1         | ROS          | LEVEL3
 v_vmart_node0001 | newstates_b1         | ROS          | LEVEL3
 v_vmart_node0001 | newstates_b1         | ROS          | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | ROS          | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | ROS          | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | ROS          | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | ROS          | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | ROS          | LEVEL3
 v_vmart_node0001 | states_p_v1_node0001 | ROS          | LEVEL3
 v_vmart_node0001 | states_b0            | ROS          | SSD
 v_vmart_node0001 | states_b0            | ROS          | SSD
 v_vmart_node0001 | states_b1            | ROS          | SSD
 v_vmart_node0001 | states_b1            | ROS          | SSD
 v_vmart_node0001 | states_b1            | ROS          | SSD
.
.
.

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.

Effects of Creating Storage Policies

Creating storage policies has the following effects:

Create policy for...	Storage effect:
Database	The highest storage level and the lowest storage priority. This is the default policy when no lower-level or higher priority policies exist. At storage time, Vertica uses the database policy for all objects without storage policies.
Schema	The mid-level storage, also with a medium priority, compared to lower storage level objects. If a table's schema has no policy, the TM searches the next higher level, the database, using that policy, if it exists. If it does not, the TM uses existing storage mechanisms.
Table	A lower storage level than a schema, with the highest storage priority, if no policy exists for the table's partition key ranges. If a table has no storage policy, Vertica checks the next higher storage level (the schema) for a policy and uses that. If the schema has no policy, it checks the next higher level, the database, and uses that. If no database policy exists, the TM uses existing storage mechanisms.
Partition min_key and max_key ranges	The lowest level policy that can be in effect. During storage processing, partition key ranges with a policy have the highest priority. If no policy exists, the parent table is checked, and so on as described for the other database objects.