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:

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.

If the operation cannot obtain an O lock on the target table, Vertica tries to close any internal tuple mover (TM) sessions that are running on that table. If successful, the operation can proceed. Explicit TM operations that are running in user sessions do not close. If an explicit TM operation is running on the table, the operation proceeds only when the TM operation is complete.

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)