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.
For more information about partitions, see the following sections in the Vertica documentation:
- CREATE TABLE in the SQL Reference Manual
- Working with Table Partitions in the Administrator's Guide