Manage data partitioning
When writing data to InfluxDB Clustered, the InfluxDB v3 storage engine stores data in the Object store in Apache Parquet format. Each Parquet file represents a partition–a logical grouping of data. By default, InfluxDB partitions each table by day. InfluxDB Clustered lets you customize the partitioning strategy and partition by tag values and different time intervals. Customize your partitioning strategy to optimize query performance for your specific schema and workload.
- Advantages
- Disadvantages
- Limitations
- How partitioning works
- Partitions in the query life cycle
- Partition guides
Advantages
The primary advantage of custom partitioning is that it lets you customize your storage structure to improve query performance specific to your schema and workload.
- Optimized storage for improved performance on specific types of queries. For example, if queries often select data with a specific tag value, you can partition by that tag to improve the performance of those queries.
- Optimized storage for specific types of data. For example, if the data you store is sparse and the time ranges you query are often much larger than a day, you could partition your data by week instead of by day.
Disadvantages
Using custom partitioning may increase the load on other parts of the InfluxDB v3 storage engine, but each can be scaled individually to address the added load.
The following disadvantages assume that your custom partitioning strategy includes additional tags to partition by or partition intervals smaller than a day.
- Increased load on the Ingester as it groups data into smaller partitions and files.
- Increased load on the Catalog as more references to partition Parquet file locations are stored and queried.
- Increased load on the Compactor as more partition Parquet files need to be compacted.
- Increased costs associated with Object storage as more partition Parquet files are created and stored.
- Risk of decreased performance for queries that don’t use tags in the WHERE clause. These queries may end up reading many partitions and smaller files, degrading performance.
Limitations
Custom partitioning has the following limitations:
- Database and table partitions can only be defined on create. You cannot update the partition strategy of a database or table after it has been created.
- A partition template must include a time part.
- You can partition by up to eight dimensions (seven tags and a time interval).
How partitioning works
Partition templates
A partition template defines the pattern used for partition keys and determines the time interval that data is partitioned by. Partition templates use tag values and Rust strftime date and time formatting syntax.
For more detailed information, see Partition templates.
Partition keys
A partition key uniquely identifies a partition.
A partition template defines the partition key format.
Partition keys are
composed of up to 8 dimensions (1 time part and up to 7 tag or tag bucket parts).
Each part is delimited by the partition key separator (|
).
The default format for partition keys is %Y-%m-%d
(for example, 2024-01-01
).
Partitions in the query life cycle
When querying data:
- The Catalog provides the v3 query engine (Querier) with the locations of partitions that contain the queried time series data.
- The query engine reads all rows in the returned partitions to identify what rows match the logic in the query and should be included in the query result.
The faster the query engine can identify what partitions to read and then read the data in those partitions, the more performant queries are.
For more information about the query lifecycle, see InfluxDB v3 query life cycle.
Query example
Consider the following query that selects everything in the production
table
where the line
tag is A
and the station
tag is cnc
:
SELECT *
FROM production
WHERE
time >= now() - INTERVAL '1 week'
AND line = 'A'
AND station = 'cnc'
Using the default partitioning strategy (by day), the query engine reads eight separate partitions (one partition for today and one for each of the last seven days):
- 2024-10-11
- 2024-10-10
- 2024-10-09
- 2024-10-08
- 2024-10-07
- 2024-10-06
- 2024-10-05
- 2024-10-04
The query engine must scan all rows in the partitions to identify rows
where line
is A
and station
is cnc
. This process takes valuable time
and results in less performant queries.
However, if you partition by other tags, InfluxDB can identify partitions that contain only the tag values your query needs and spend less time scanning rows to see if they contain the tag values.
For example, if data is partitioned by line
, station
, and day, although
there are more partition files, the query engine can quickly identify and read
only those with data relevant to the query:
-
A | cnc | 2024-10-11
-
A | wld | 2024-10-11
-
B | cnc | 2024-10-11
-
B | wld | 2024-10-11
-
A | cnc | 2024-10-10
-
A | wld | 2024-10-10
-
B | cnc | 2024-10-10
-
B | wld | 2024-10-10
-
A | cnc | 2024-10-09
-
A | wld | 2024-10-09
-
B | cnc | 2024-10-09
-
B | wld | 2024-10-09
-
A | cnc | 2024-10-08
-
A | wld | 2024-10-08
-
B | cnc | 2024-10-08
-
B | wld | 2024-10-08
-
A | cnc | 2024-10-07
-
A | wld | 2024-10-07
-
B | cnc | 2024-10-07
-
B | wld | 2024-10-07
-
A | cnc | 2024-10-06
-
A | wld | 2024-10-06
-
B | cnc | 2024-10-06
-
B | wld | 2024-10-06
-
A | cnc | 2024-10-05
-
A | wld | 2024-10-05
-
B | cnc | 2024-10-05
-
B | wld | 2024-10-05
-
A | cnc | 2024-10-04
-
A | wld | 2024-10-04
-
B | cnc | 2024-10-04
-
B | wld | 2024-10-04
Partition guides
Define custom partitions
Use the influxctl
CLI to define custom partition strategies when creating a database or table.
Use partition templates
Learn how to define custom partitioning strategies using partition templates. Data can be partitioned by tag and time.
Partitioning best practices
Learn best practices for applying custom partition strategies to your data stored in InfluxDB.
View partition information
Query partition information from InfluxDB v3 system tables to view partition templates and verify partitions are working as intended.
SELECT * FROM system.partitions WHERE table_name = 'example-table'
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.