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
  • Partition templates
  • Partition keys
• Partitions in the query life cycle
• Partition guides
  • Define custom partitions
  • Use partition templates
  • Partitioning best practices
  • View partition information

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.

• 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).

production,line=A,station=cnc temp=81.2,qty=35i 1704063600000000000
production,line=A,station=wld temp=92.8,qty=35i 1704063600000000000
production,line=B,station=cnc temp=101.1,qty=43i 1704063600000000000
production,line=B,station=wld temp=102.4,qty=43i 1704063600000000000
production,line=A,station=cnc temp=81.9,qty=36i 1704067200000000000
production,line=A,station=wld temp=110.0,qty=22i 1704067200000000000
production,line=B,station=cnc temp=101.8,qty=44i 1704067200000000000
production,line=B,station=wld temp=105.7,qty=44i 1704067200000000000
production,line=A,station=cnc temp=82.2,qty=35i 1704070800000000000
production,line=A,station=wld temp=92.1,qty=30i 1704070800000000000
production,line=B,station=cnc temp=102.4,qty=43i 1704070800000000000
production,line=B,station=wld temp=106.5,qty=43i 1704070800000000000

Partitions in the query life cycle

When querying data:

1. The Catalog provides the v3 query engine (Querier) with the locations of partitions that contain the queried time series data.
2. 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-11-20
• 2024-11-19
• 2024-11-18
• 2024-11-17
• 2024-11-16
• 2024-11-15
• 2024-11-14
• 2024-11-13

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:

Partition guides

SELECT * FROM system.partitions WHERE table_name = 'example-table'