InfluxDB schema design recommendations
Use the following guidelines to design your schema for simpler and more performant queries.
- InfluxDB data structure
- Schema restrictions
- Design for performance
- Design for query simplicity
InfluxDB data structure
The InfluxDB data model organizes time series data into buckets and measurements. A bucket can contain multiple measurements. Measurements contain multiple tags and fields.
- Bucket: Named location where time series data is stored.
In the InfluxDB SQL implementation, a bucket is synonymous with a database.
A bucket can contain multiple measurements.
- Measurement: Logical grouping for time series data.
In the InfluxDB SQL implementation, a measurement is synonymous with a table.
All points in a given measurement should have the same tags.
A measurement contains multiple tags and fields.
- Tags: Key-value pairs that store metadata string values for each point–for example, a value that identifies or differentiates the data source or context–for example, host, location, station, etc. Tag values may be null.
- Fields: Key-value pairs that store data for each point–for example, temperature, pressure, stock price, etc. Field values may be null, but at least one field value is not null on any given row.
- Timestamp: Timestamp associated with the data. When stored on disk and queried, all data is ordered by time. In InfluxDB, a timestamp is a nanosecond-scale unix timestamp in UTC. A timestamp is never null.
- Measurement: Logical grouping for time series data. In the InfluxDB SQL implementation, a measurement is synonymous with a table. All points in a given measurement should have the same tags. A measurement contains multiple tags and fields.
In time series data, the primary key for a row of data is typically a combination of timestamp and other attributes that uniquely identify each data point. In InfluxDB, the primary key for a row is the combination of the point’s timestamp and tag set - the collection of tag keys and tag values on the point. A row’s primary key tag set does not include tags with null values.
Tags versus fields
When designing your schema for InfluxDB, a common question is, “what should be a tag and what should be a field?” The following guidelines should help answer that question as you design your schema.
- Use tags to store metadata, or identifying information, about the source or context of the data.
- Use fields to store measured values.
- Tag values can only be strings.
- Field values can be any of the following data types:
- Unsigned integer
InfluxDB Cloud Serverless doesn’t index tag values or field values. Tag keys, field keys, and other metadata are indexed to optimize performance.
The InfluxDB v3 storage engine supports infinite tag value and series cardinality. Unlike InfluxDB backed by the TSM storage engine, tag value cardinality doesn’t affect the overall performance of your database.
Do not use duplicate names for tags and fields
Tags and fields within the same measurement can’t be named the same. All tags and fields are stored as unique columns in a table representing the measurement on disk. If you attempt to write a measurement that contains tags or fields with the same name, the write fails due to a column conflict.
Measurements can contain up to 200 columns
A measurement can contain up to 200 columns. Each row requires a time column, but the rest represent tags and fields stored in the measurement. Therefore, a measurement can contain one time column and 199 total field and tag columns. If you attempt to write to a measurement and exceed the 200 column limit, the write request fails and InfluxDB returns an error.
Design for performance
How you structure your schema within a measurement can affect the overall performance of queries against that measurement. The following guidelines help to optimize query performance:
Avoid wide schemas
A wide schema is one with many tags and fields and corresponding columns for each. With the InfluxDB IOx storage engine, wide schemas don’t impact query execution performance. Because IOx is a columnar database, it executes queries only against columns selected in the query.
Although a wide schema won’t affect query performance, it can lead to the following:
- More resources required for persisting and compacting data during ingestion.
- Decreased sorting performance due to complex primary keys with too many tags.
The InfluxDB IOx storage engine has a limit of 200 columns per measurement.
To avoid a wide schema, limit the number of tags and fields stored in a measurement. If you need to store more than 199 total tags and fields, consider segmenting your fields into a separate measurement.
Avoid too many tags
In InfluxDB, the primary key for a row is the combination of the point’s timestamp and tag set - the collection of tag keys and tag values on the point. A point that contains more tags has a more complex primary key, which could impact sorting performance if you sort using all parts of the key.
Avoid sparse schemas
A sparse schema is one where, for many rows, columns contain null values.
These generally stem from the following:
Sparse schemas require the InfluxDB query engine to evaluate many null columns, adding unnecessary overhead to storing and querying data.
For an example of a sparse schema, view the non-homogenous schema example below.
Writing individual fields with different timestamps
Reporting fields at different times with different timestamps creates distinct rows that contain null values–for example:
tagset, and then report
field B with the same
tagset, but with a different timestamp.
The result is two rows: one row has a null value for field A and the other has a null value for field B.
In contrast, if you report fields at different times while using the same tagset and timestamp, the existing row is updated. This requires slightly more resources at ingestion time, but then gets resolved at persistence time or compaction time and avoids a sparse schema.
Measurement schemas should be homogenous
Data stored within a measurement should be “homogenous,” meaning each row should have the same tag and field keys. All rows stored in a measurement share the same columns, but if a point doesn’t include a value for a column, the column value is null. A measurement full of null values has a “sparse” schema.
Design for query simplicity
Naming conventions for measurements, tag keys, and field keys can simplify or complicate the process of writing queries for your data. The following guidelines help to ensure writing queries for your data is as simple as possible.
Keep measurement names, tags, and fields simple
Use one tag or one field for each data attribute. If your source data contains multiple data attributes in a single parameter, split each attribute into its own tag or field.
Measurement names, tag keys, and field keys should be simple and accurately describe what each contains. Keep names free of data. The most common cause of a complex naming convention is when you try to “embed” data attributes into a measurement name, tag key, or field key.
When each key and value represents one attribute (not multiple concatenated attributes) of your data, you’ll reduce the need for regular expressions in your queries. Without regular expressions, your queries will be easier to write and more performant.
For example, consider the following line protocol that embeds multiple attributes (location, model, and ID) into a
sensor tag value:
home,sensor=loc-kitchen.model-A612.id-1726ZA temp=72.1 home,sensor=loc-bath.model-A612.id-2635YB temp=71.8
To query data from the sensor with ID
1726ZA, you have to use either SQL pattern
matching or regular expressions to evaluate the
SELECT * FROM home WHERE sensor LIKE '%id-1726ZA%'
SELECT * FROM home WHERE sensor =~ /id-1726ZA/
SQL pattern matching and regular expressions both complicate the query and are less performant than simple equality expressions.
The better approach would be to write each sensor attribute as a separate tag:
home,location=kitchen,sensor_model=A612,sensor_id=1726ZA temp=72.1 home,location=bath,sensor_model=A612,sensor_id=2635YB temp=71.8
To query data from the sensor with ID
1726ZA using this schema, you can use a
simple equality expression:
SELECT * FROM home WHERE sensor_id = '1726ZA'
This query is easier to write and is more performant than using pattern matching or regular expressions.
Avoid keywords and special characters
To simplify query writing, avoid using reserved keywords or special characters in measurement names, tag keys, and field keys.
When using SQL or InfluxQL to query measurements, tags, and fields with special characters or keywords, you have to wrap these keys in double quotes.
SELECT "example-field", "tag@1-23" FROM "example-measurement" WHERE "tag@1-23" = 'ABC'
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.