Query the Latest Values in Under 10ms with the InfluxDB 3 Last Value Cache

By Scott Anderson / Developer
Feb 10, 2025

Navigate to:

As part of the InfluxDB 3 Core and InfluxDB 3 Enterprise public alpha, the Last Value Cache (LVC) is available for testing. The LVC lets you cache the most recent values for specific fields in a table, improving the performance of queries that return the most recent value of a field for specific time series or the last N values of a field, typical of many monitoring workloads. With the LVC, these types of queries return in under 10ms.

The LVC is an in-memory cache that stores the last N number of values for specific fields of time series in a table. When you create an LVC, you can specify what fields to cache, what tags to include in the cache (which determines how many unique time series you store the last values of), and the number of values to cache for each unique tag set.

For example, let’s use a dataset with the following schema (similar to the home sensor sample dataset):

  • home (table)
    • tags:
      • room
        • kitchen
        • living room
      • wall
        • north
        • east
        • south
    • fields:
      • co (integer)
      • temp (float)
      • hum (float)

If you were to cache the last value for each field per room and wall, the LVC would look similar to this:

+-------------+-------+----+------+------+---------------------+
| room        | wall  | co | hum  | temp | time                |
+-------------+-------+----+------+------+---------------------+
| Kitchen     | east  | 26 | 36.5 | 22.7 | 2025-02-10T20:00:00 |
| Living Room | north | 17 | 36.4 | 22.2 | 2025-02-10T20:00:00 |
| Living Room | south | 16 | 36.3 | 22.1 | 2025-02-10T20:00:00 |
+-------------+-------+----+------+------+---------------------+

If you were to cache the last four values of each field per room and wall, the LVC would look similar to:

+-------------+-------+----+------+------+---------------------+
| room        | wall  | co | hum  | temp | time                |
+-------------+-------+----+------+------+---------------------+
| Kitchen     | east  | 26 | 36.5 | 22.7 | 2025-02-10T20:00:00 |
| Kitchen     | east  | 9  | 36.0 | 22.7 | 2025-02-10T17:00:00 |
| Kitchen     | east  | 3  | 36.2 | 22.7 | 2025-02-10T15:00:00 |
| Kitchen     | east  | 0  | 36.1 | 22.7 | 2025-02-10T10:00:00 |
| Living Room | north | 17 | 36.4 | 22.2 | 2025-02-10T20:00:00 |
| Living Room | north | 5  | 35.9 | 22.6 | 2025-02-10T17:00:00 |
| Living Room | north | 1  | 36.1 | 22.3 | 2025-02-10T15:00:00 |
| Living Room | north | 0  | 36.0 | 21.8 | 2025-02-10T10:00:00 |
| Living Room | south | 16 | 36.3 | 22.1 | 2025-02-10T20:00:00 |
| Living Room | south | 4  | 35.8 | 22.5 | 2025-02-10T17:00:00 |
| Living Room | south | 0  | 36.0 | 22.3 | 2025-02-10T15:00:00 |
| Living Room | south | 0  | 35.9 | 21.8 | 2025-02-10T10:00:00 |
+-------------+-------+----+------+------+---------------------+

Why use a Last Value Cache?

In short, the LVC provides last-value query responses in under 10ms, simplifying a common query type. Let’s say that you are building a monitoring dashboard and only need to know the last reported values for specific fields. Without using the LVC, you’d have to run a query similar to:

SELECT
  room,
  wall,
  selector_last(co, time)['value'] as co,
  selector_last(temp, time)['value'] as temp,
  selector_last(hum, time)['value'] as hum,
  selector_last(hum, time)['time'] AS time
FROM
  home
GROUP BY
  room,
  wall
WHERE
  time >= now() - INTERVAL '1 day'
  AND time <= now()

While this query will give you the last reported values, there are some things to note:

  • The time value in each row is specific to only the hum field. If field values are reported independently with sporadic timestamps, the time value may be inaccurate for the other fields.
  • The query includes a time range, which prevents the query engine from having to read all the data in the table to generate results. Without the time bounds, this query could potentially be very “heavy,” depending on the amount of data in the table. This also means that if the last reported value is outside the queried time range, it is not included in the results.

To query this same data from the LVC, the query would look similar to:

SELECT * FROM last_cache('home', 'homeSensorCache')

Time bounds aren’t necessary. All the returned timestamps are specific to the last reported value of each field. Results return in under 10ms, and the query is just simpler.

The LVC also unlocks some other functionalities, but we’ll discuss that in a future post.

Set up a Last Value Cache

An LVC is associated with a table, which can have multiple LVCs. You can add an LVC to an existing table, but for this example, we’ll create a new table to store the home sensor sample dataset.

  1. Use the influxdb3 create table command to create a new home table. Because we know the schema of the sample data, we can pre-create the table with the necessary tag and field columns :
influxdb3 create table \
  --tags room \
  --fields co:int64 temp:float64 hum:float64 \
  --database example_db
  home
  1. Use the influxdb3 create last_cache command to create a new LVC associated with the home table. You can provide the following:
  • Table (--table): (Required) The name of the table to associate the LVC with.
  • Cache name: A unique name for the cache. If you don’t provide one, InfluxDB automatically generates a cache name for you.
  • Key columns (--key-columns): Specify which columns to include in the primary key of the cache. Rows in the LVC are uniquely identified by their timestamp and key columns, so include all the columns you need to identify each row. These are typically tags, but you can use any columns with the following types:
    • String
    • Integer
    • Unsigned integer
    • Boolean
  • Value columns (--value-columns): Specify which columns to cache as value columns. These are typically fields but can also be tags. By default, time and columns other than those specified as --key-columns are cached as value columns.
  • Count (--count): The number of values to cache per unique key column combination. The default count is 1.

In this example, we’ll create an LVC named homeLastCache associated with the home table. We’ll use the room tag as a key column, all the fields as value columns, and only cache the latest value for each field per room:

influxdb3 create last_cache \
  --database example-db \
  --table home \
  --key-columns room \
  homeLastCache
  1. Write data to the table associated with the cache. For this example, write the home sensor sample data (Commands and data are provided in the link, and you can adjust the timestamps of the data).

Note: Values are cached on write. When you create a cache, it will not cache previously written points, only newly written points.

Query data in the Last Value Cache

Use the last_cache() function in the FROM clause of an SQL SELECT statement to query data from the LVC. last_cache() supports the following arguments:

  • table_name: (required) The name of the table the LVC is associated with, formatted as a string literal.
  • cache_name: The name of the LVC to query from, formatted as a string literal (only required if there is more than one LVC associated with the table).
last_cache(table_name, cache_name)

To query the LVC for the written sample data, execute the following query:

SELECT * FROM last_cache('home', 'homeCache')

This is just a simple SQL query, so you can include other SQL clauses to modify query results. For example, if you only want the last temperature value for the Kitchen, you can use the following query:

SELECT
  room,
  temp`
FROM
  last_cache('home', 'homeCache')
WHERE
  room = 'Kitchen'

Note: InfluxQL does not support the last_cache() function, so you can only access the data in the LVC using SQL queries.

What to know about the Last Value Cache

The InfluxDB 3 Last Value Cache is an incredibly powerful tool, but there are important things to know when using it. LVCs are stored in memory; the larger the cache, the more memory it requires to maintain it. It’s essential to balance the size of your LVCs with the amount of memory it takes to store them. Things to consider:

High Cardinality Key Columns

“Cardinality” refers to the number of unique key column combinations in your cached data. While the InfluxDB 3 storage engine is not limited by cardinality, it does affect the LVC. Higher cardinality increases memory requirements for storing the LVC and can affect LVC query performance. We recommend the following:

  • Only use tags important to your query workload as key columns in the LVC. Caching tags or fields as key columns unnecessarily results in higher cardinality without any benefit.
  • Avoid including high cardinality key columns in your LVC.
  • Don’t include multiple high-cardinality key columns in your LVC

For a general idea of total key column cardinality in an LVC, you can use the following equation:

num_uniq_col_val_N [× num_uniq_col_val_N …] = key_column_cardinality

Value Count

By increasing the number of values to store in the LVC, you increase the number of rows stored in the cache and the amount of memory required to store them. Be judicious with the number of values to store. This count is per unique key column combination. If you include two tags as key columns, one with three unique values and the other with 10, you could have up to 30 unique key column combinations. If you want to keep the last 10 values, you could potentially have 300+ rows in the cache. In reality, this isn’t a huge cache, but it illustrates how key column cardinality and the number of values you want to cache can explode your cache size.

To get an idea of the number of rows required to cache the specified number of values, use the following equation:

key_column_cardinality × count = number_of_rows

Last Value Caches Are Flushed When the Server Stops

Because the LVC is an in-memory cache, the cache is flushed any time the server stops. After a server restart, InfluxDB only writes new values to the LVC when you write data, so there may be a period of time when some values are unavailable in the LVC.

Defining Value Columns

When creating an LVC, if you include the --value-columns options to specify which fields to cache as value columns, any new fields added in the future will not be added to the cache. However, if you omit the --value-columns option, all columns other than those specified as --key-columns are cached as value columns, including columns that are added later.

Share your feedback

The InfluxDB 3 Last Value Cache is a powerful tool that lets you get the best performance on queries that need to return the latest reported values. It’s another tool in your time series toolbelt that helps make sure your workload is as performant as possible.

Try the LVC, and let us know what you think! Check out our Getting Started Guide for Core and Enterprise, and share your feedback with our development team on Discord in the #influxdb3_core channel, on Slack in the #influxdb3_core channel, or on our Community Site.