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,YEAR,MONTH,DAY, andHOUR. - Default behavior is
PARTITION BY NONE. - Partitions are defined at table creation. For more information, refer to CREATE TABLE section.
- The naming convention for partition directories is as follows:
| Table Partition | Partition format |
|---|---|
HOUR | YYYY-MM-DD-HH |
DAY | YYYY-MM-DD |
MONTH | YYYY-MM |
YEAR | YYYY |
info
Partitioning is only possible on tables which have a designated timestamp. For more information on designated timestamps, 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.