Time Partitions

QuestDB partitions tables by time intervals, storing each interval's data in a separate directory. This physical separation is fundamental to time-series performance - it allows the database to skip irrelevant time ranges entirely during queries and enables efficient data lifecycle management.

Why partition​

Partitioning provides significant benefits for time-series workloads:

  • Query performance: The SQL optimizer skips partitions outside your query's time range. A query for "last hour" on a table with years of data reads only one partition, not the entire table.
  • Data lifecycle: Drop old data instantly with DROP PARTITION - no expensive DELETE operations. Detach partitions to cold storage, reattach when needed.
  • Write efficiency: Out-of-order data only rewrites affected partitions, not the entire table. Smaller partitions mean less write amplification.
  • Concurrent access: Different partitions can be written and read simultaneously without contention.

How partitions work​

Partitioning requires a designated timestamp column. QuestDB uses this timestamp to determine which partition stores each row.

Diagram showing how table data is organized into time-based partition directories, each containing column files

Each partition is a directory on disk named by its time interval. Inside, each column is stored as a separate file (.d for data, plus index files for SYMBOL columns).

Choosing a partition interval​

Available intervals: HOUR, DAY, WEEK, MONTH, YEAR, or NONE.

Target 30-80 million rows per partition for tables with average-sized rows. Tables with many columns should aim for the lower end; tables with few columns can go higher.

Choose your interval based on how much data you ingest:

Your data volumeRecommended interval
>1 billion rows/dayHOUR
30-500 million rows/dayDAY
5-30 million rows/dayWEEK
1-5 million rows/dayMONTH
<1 million rows/dayYEAR

Why this matters:

  • Too many small partitions increases syscall overhead. Each partition is a directory, and operations like queries and compaction must interact with many filesystem objects.
  • Too few large partitions can hurt out-of-order write performance. When late data arrives, QuestDB may need to rewrite portions of the partition. Smaller partitions limit how much data gets rewritten in worst-case scenarios.

Other considerations:

  • Match your most common query patterns (if you typically query by day, DAY partitions align well)
  • You can change partitioning later, but it requires recreating the table

For ILP (InfluxDB Line Protocol) ingestion, the default is DAY. Change it via line.default.partition.by in server.conf.

Creating partitioned tables​

Specify partitioning at table creation:

CREATE TABLE trades (
    ts TIMESTAMP,
    symbol SYMBOL,
    price DOUBLE,
    amount DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY;

Default behavior by creation method​

Creation methodDefault partition
SQL CREATE TABLE (no PARTITION BY)NONE
SQL CREATE TABLE (with PARTITION BY)As specified
ILP auto-created tablesDAY

Partition directory naming​

IntervalDirectory formatExample
HOURYYYY-MM-DDTHH2026-01-15T09
DAYYYYY-MM-DD2026-01-15
WEEKYYYY-Www2026-W03
MONTHYYYY-MM2026-01
YEARYYYY2026

Inspecting partitions​

Use SHOW PARTITIONS or the table_partitions() function:

SHOW PARTITIONS FROM trades;
indexpartitionBynameminTimestampmaxTimestampnumRowsdiskSizeHuman
0DAY2026-01-152026-01-15T00:00:00Z2026-01-15T23:59:59Z144000068.0 MiB
1DAY2026-01-162026-01-16T00:00:00Z2026-01-16T12:30:00Z75000035.2 MiB

The table_partitions() function returns the same data and can be used in queries with WHERE, JOIN, or UNION:

SELECT name, numRows, diskSizeHuman
FROM table_partitions('trades')
WHERE numRows > 1000000;

Storage on disk​

A partitioned table's directory structure:

db/trades/
├── 2026-01-15/           # Partition directory
│   ├── ts.d              # Timestamp column data
│   ├── symbol.d          # Symbol column data
│   ├── symbol.k          # Symbol column index
│   ├── symbol.v          # Symbol column values
│   ├── price.d           # Price column data
│   └── amount.d          # Amount column data
├── 2026-01-16/
│   ├── ts.d
│   ├── ...
└── _txn                  # Transaction metadata

Partition splitting and squashing​

When out-of-order data arrives for an existing partition, QuestDB may split that partition to avoid rewriting all its data. This is an optimization for write performance.

A split occurs when:

  • The existing partition prefix is larger than the new data plus suffix
  • The prefix exceeds cairo.o3.partition.split.min.size (default: 50MB)

Split partitions appear with timestamp suffixes in SHOW PARTITIONS:

namenumRows
2026-01-151259999
2026-01-15T205959-88000160002

QuestDB automatically squashes splits:

  • Non-active partitions: squashed at end of each commit
  • Active (latest) partition: squashed when splits exceed cairo.o3.last.partition.max.splits (default: 20)

To manually squash all splits:

ALTER TABLE trades SQUASH PARTITIONS;

Partition operations (ATTACH, DETACH, DROP) treat all splits of a partition as a single unit.

See also​