Manage the Distinct Value Cache
The InfluxDB 3 Core Distinct Value Cache (DVC) lets you cache distinct values of one or more columns in a table, improving the performance of queries that return distinct tag and field values.
The DVC is an in-memory cache that stores distinct values for specific columns in a table. When you create an DVC, you can specify what columns’ distinct values to cache, the maximum number of distinct value combinations to cache, and the maximum age of cached values. A DVC is associated with a table, which can have multiple DVCs.
- Create a Distinct Value Cache
- Query a Distinct Value Cache
- Show information about Distinct Value Caches
- Delete a Distinct Value Cache
Consider a dataset with the following schema:
- wind_data (table)
- tags:
- country
- multiple European countries
- county
- multiple European counties
- city
- multiple European cities
- country
- fields:
- wind_speed (float)
- wind_direction (integer)
- tags:
If you cache distinct values for country, county, and city, the DVC looks
similar to this:
| country | county | city |
|---|---|---|
| Austria | Salzburg | Salzburg |
| Austria | Vienna | Vienna |
| Belgium | Antwerp | Antwerp |
| Belgium | West Flanders | Bruges |
| Czech Republic | Liberec Region | Liberec |
| Czech Republic | Prague | Prague |
| Denmark | Capital Region | Copenhagen |
| Denmark | Southern Denmark | Odense |
| Estonia | Ida-Viru County | Kohtla-Järve |
| Estonia | Ida-Viru County | Narva |
| … | … | … |
Repeated values in DVC results
Distinct values may appear multiple times in a column when querying the DVC, but only when associated with distinct values in other columns. If you query a single column in the DVC, no values are repeated in the results.
Null column values
Null column values are still considered values and are cached in the DVC. If you write data to a table and don’t provide a value for an existing column, the column value is cached as null and treated as a distinct value.
Create a Distinct Value Cache
Use the influxdb3 create distinct_cache command
to create a Distinct Value Cache.
influxdb3 create distinct_cache \
--database example-db \
--token 00xoXX0xXXx0000XxxxXx0Xx0xx0 \
--table wind_data \
--columns country,county,city \
--max-cardinality 10000 \
--max-age 24h \
windDistinctCache
Query a Distinct Value Cache
Use the distinct_cache() SQL function
in the FROM clause of an SQL SELECT statement to query data from the
Distinct Value Cache.
SELECT * FROM distinct_cache('table-name', 'cache-name')
You must use SQL to query the DVC.
InfluxQL does not support the distinct_cache() function.
Show information about Distinct Value Caches
Use the influxdb3 show system table command to query and output Distinct Value
Cache information from the distinct_caches system table.
influxdb3 show system \
--database example-db \
--token 00xoXX0xXXx0000XxxxXx0Xx0xx0 \
table distinct_caches
Delete a Distinct Value Cache
Use the influxdb3 delete distinct_cache command
to delete a Distinct Value Cache.
influxdb3 delete distinct_cache \
--database example-db \
--token 00xoXX0xXXx0000XxxxXx0Xx0xx0 \
--table wind_data \
windDistinctCache
Important things to know about the Distinct Value Cache
DVCs are stored in memory; the larger the cache, the more memory your InfluxDB 3 node requires to maintain it. Consider the following:
High cardinality limits
“Cardinality” refers to the number of unique key column combinations in your cached data and essentially defines the maximum number of rows to store in your DVC. While the InfluxDB 3 storage engine is not limited by cardinality, it does affect the DVC. You can define a custom maximum cardinality limit for a DVC, but higher cardinality increases memory requirements for storing the DVC and can affect DVC query performance.
Distinct Value Caches are flushed when the server stops
Because the DVC is an in-memory cache, the cache is flushed any time the server stops. After a server restart, InfluxDB 3 Core only writes new values to the DVC when you write data, so there may be a period of time when some values are unavailable in the DVC.
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 3 Core and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.