Partitions

QuestDB offers the option to partition tables by intervals of time. Data for each interval is stored in separate sets of files.

Diagram of data column files and how they are partitioned to form a table

Properties#

  • Available partition intervals are NONE, DAY, MONTH and YEAR.
  • Default behaviour is PARTITION BY NONE.
  • Partitions are defined at table creation. For more information, refer to CREATE TABLE section.
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.

[quest-user trips]$ dir
2017-03 2017-10 2018-05 2019-02
2017-04 2017-11 2018-06 2019-03
2017-05 2017-12 2018-07 2019-04
2017-06 2018-01 2018-08 2019-05
2017-07 2018-02 2018-09 2019-06
2017-08 2018-03 2018-10
2017-09 2018-04 2018-11

Each partition on the disk contains the column data files of the corresponding timestamp interval.

[quest-user 2019-06]$ dir
_archive cab_type.v dropoff_latitude.d ehail_fee.d
cab_type.d congestion_surcharge.d dropoff_location_id.d extra.d
cab_type.k dropoff_datetime.d dropoff_longitude.d fare_amount.d