Partitions
QuestDB offers the option to partition tables by intervals of time. Data for each interval is stored in separate sets of files.
Properties#
- Available partition intervals are
NONE,DAY,MONTHandYEAR. - Default behaviour is
PARTITION BY NONE. - Partitions are defined at table creation. For more information, refer to CREATE TABLE section.
Requirements#
Partition is only available on tables with a designated timestamp. The main
benefit of using a designated timestamp is that the field in question will
enforce an increasing policy on timestamp value. This allows you to leverage
specific high-performance time series functions. For more information on
designated timestamp, refer to the
designated timestamp section.
Advantages#
- Reduced disk IO for timestamp interval searches. This is because our SQL optimiser leverages partitioning.
- Significantly improved calculations and seek times. This is achieved by leveraging the chronology and relative immutability of data for previous partitions.
- Physical separation of data files. This makes it easily to implement file retention policies or extract certain intervals.
Storage example#
Each partition effectively is a directory on the host machine corresponding to
the partitioning interval. In the example below, we assume a table trips that
has been partitioned using PARTITION BY MONTH.
Each partition on the disk contains the column data files of the corresponding timestamp interval.