Moving Data Storage Locations

You can use the SET_OBJECT_STORAGE_POLICY function to move data storage from an existing location (whether labeled or not) to another labeled location. You can use this function to accomplish two tasks:

  1. Create or update a storage policy:

    • Create new storage policy for the object
    • Update an existing policy to the target labeled location.
  2. Move all existing data for the specified objects to the target storage location.

Vertica moves the existing data the next time the Tuple Mover runs. Alternatively, you can enforce the data move to occur in the current transaction using the function's enforce_storage_move parameter. Consider setting this parameter to true if the Tuple Mover will not run on this data for a while, such as if the data is old.

Before actually moving the object to the target storage location, Vertica calculates the required storage and checks available space at the target. If there is insufficient free space, the function generates an error and stops execution. It does not attempt to find sufficient storage at another location.

Important: You should check available space on the new target location before starting a data move. However, be aware that checking does not guarantee that this space remains available during move execution. Checking target space does prevent attempts to move any data, if insufficient space is available.

Moving Data Storage While Setting a Storage Policy

The following example shows how to use SET_OBJECT_STORAGE_POLICY to set a storage policy for the table object states. Then, you can move the table's existing stored data to the labeled location, SSD. You force the move to occur during the function transaction by specifying the last parameter as true:

=> select set_object_storage_policy('states', 'SSD', 'true');
                            set_object_storage_policy                                                                                              
------------------------------------------------------------------------------------------------
 Object storage policy set.
Task: moving storages
(Table: public.states) (Projection: public.states_p1)
(Table: public.states) (Projection: public.states_p2)
(Table: public.states) (Projection: public.states_p3)
(1 row)

Note: Moving an object's current storage to a new target is a cluster-wide operation, so a failure on any node results in a warning message. The function then attempts to continue executing on other cluster nodes.

You can view the storage policies that are in effect: 

=> select * from storage_policies;
 schema_name | object_name | policy_details | location_label 
-------------+-------------+----------------+----------------
 public      | states      | Table          | SSD
(1 row)

Effects of Moving a Storage Location

Moving an object from one labeled storage location to another has the following effects:

Object type: Effect:

Schema or table

If data storage exists, moves data from source to target destination. Source data can reside on a labeled or unlabeled storage location, but will be moved to specified labeled location.

Cluster nodes unavailable when existing data is copied are updated by the TM when they rejoin the cluster. Alternately, you can enforce a data moveas part of the function transaction, by specifying the last parameter as true.

If a storage policy was in effect, the default storage location changes from the source to target location. This change affects all future TM operations, such as moveout and mergeout activities.

Table with specified partition min-keys and max_keys

Sets a policy or moves existing data only for the key_min and key_max ranges. Separate partition key ranges can have different storage policies from other ranges or the parent table.