Dropping Partitions
Use the DROP_PARTITION
function to drop a partition. Typically, this is a fast operation that discards all ROS containers that contain data for the partition. If the WOS contains table data, DROP_PARTITION
first forces a moveout operation.
Tip: When a ROS container has data for a single partition, you can discard that storage location with DROP_LOCATION
after dropping that partition.
Dropping Partitions Due to Space Constraints
If your hardware has fixed disk space, you might need to configure a regular process to roll out old data by dropping partitions.
For example, if you have only enough space to store data for a fixed number of days, configure Vertica to drop the partition with the oldest date. To do so, create a time-based job scheduler, such as cron
, to drop the partition on a regular basis during low-load periods.
If the ingest rate for data has peaks and valleys, you can use two techniques to manage how you drop partitions:
- Set up a process to check the disk space on a regular (daily) basis. If the percentage of used disk space exceeds a certain threshold—for example, 80%—drop the oldest partition.
- Add an artificial column in a partition that increments based on a metric like row count. For example, that column might increment each time that the row count increases by 100 rows. Set up a process that queries that column on a regular (daily) basis. If the value in the new column exceeds a certain threshold—for example, 100—drop the oldest partition, and set the column value back to 0.
Table Locks
DROP_PARTITION
acquires an exclusive O lock on the target table to block any DML operation (DELETE
, UPDATE
, INSERT
, or COPY
) that might affect table data. The lock also blocks SELECT
statements that are issued at SERIALIZABLE isolation level.
Examples
Using the example schema in Defining Partitions, the following command explicitly drops the 2009 partition key from table trade
:
SELECT DROP_PARTITION('trade', 2009); DROP_PARTITION ------------------- Partition dropped (1 row)
Here, the partition key is specified:
SELECT DROP_PARTITION('trade', EXTRACT('year' FROM '2009-01-01'::date)); DROP_PARTITION ------------------- Partition dropped (1 row)
The following example creates a table called dates and partitions the table by year:
CREATE TABLE dates (year INTEGER NOT NULL, month VARCHAR(8) NOT NULL) PARTITION BY year * 12 + month;
The following statement drops the partition using a constant for Oct 2010 (2010*12 + 10 = 24130):
SELECT DROP_PARTITION('dates', '24130'); DROP_PARTITION ------------------- Partition dropped (1 row)
Alternatively, the expression can be placed in line: SELECT DROP_PARTITION('dates', 2010*12 + 10);
The following command first reorganizes the data if it is unpartitioned and then explicitly drops the 2009 partition key from table trade
:
SELECT DROP_PARTITION('trade', 2009, false, true); DROP_PARTITION ------------------- Partition dropped (1 row)