CockroachDB: row-level TTL to simulate Redis

what this blog covers

• row-level TTL implementation in CockroachDB
• examples of prepared statements with execution parameters
• usage demo that illustrate automatic deletion

what this is not...

Redis is a feature-rich, in-memory key/value database designed for high-performance caching and text-based queries against key-strings. This blog is not meant to replace a true Redis use-case, instead it provides an implementation to the most frequently used Redis capabilities, namely GET, SET, and EXPIRE functions.

• no expressions or conditional get capabilities
• no gets using multiple keys, scans, or wildcard queries
• no in-memory stores. Data is distributed across nodes, but always using disk for I/O. There are in-memory options that would provide benefit to this solution, but not covered here. At the bottom of this blog is a link to in-memory stores to improve performance.

---

implementation: table definition

The Redis table must contain 3 specific columns to facilitate the capabilities tied to row-level TTL, namely the key, the value, and the expired_at columns. The names of these columns can be adjusted to meet your application needs, provided that the prepared statements below are in-sync with your naming conventions.

create table redis_tbl (
  key string primary key,
  value string,
  expired_at timestamptz
) with (ttl_expiration_expression = 'expired_at');

alter table redis_tbl configure zone using gc.ttlseconds = 300;

• You can introduce additional app-specific columns including indexes to accommodate your workload.
• The key and value data-types can also be tailored to meet your needs. In fact I often use JSONB data-type for values for easy data-processing in my NodeJS apps.
• The expired_at column is a timestamp in seconds.
• Note on the gc.ttlseconds alteration. The default CockroachDB garbage collector removes tombstones after 25 hours (90000s) so the recommended practice is to protect your storage capacity by reducing this window, especially under workloads with many short-lived or churning keys.
• This example has GC set to 300 seconds (5 min), but should be adjusted based on anticipated usage and can be revisited & altered in a production environment.

implementation: prepared statements

For convenience, we create 3 prepared statements to provide the core functionality tied to set, get, and expire capabilities. These can be tailored to meet your application needs, including data-type augmentation or additional parameters.

prepare redis_set(string, string, integer) as
  upsert into redis_tbl values ($1, $2, cast(cast(now() as integer) + $3 as timestamptz));

The redis_set statement saves key/value data including an expiry duration (in seconds).

prepare redis_get(string) as
  select value from redis_tbl where key = $1 and expired_at > now();

The redis_get statement retrieves the stored value that’s identified by the key.

prepare redis_expire(string, integer) as
  update redis_tbl set expired_at = cast(cast(now() as integer) + $2 as timestamptz) where key = $1;

The redis_expire statement updates the expiry duration of an existing key to this new value (in seconds).

---

testing & usage: set, get, expire

Below is some basic usage of these operations. Note that time is of the essence when running tests since this intended to be a real-time demo.

execute redis_set('mz1', 'hello1', 10); -- entry is saved with a 10 second TTL

execute redis_get('mz1'); -- returns the 'mz1/hello1' row;

execute redis_expire('mz1', 10); -- entry is updated with a fresh 10 second TTL window

execute redis_get('mz1'); -- returns the 'mz1/hello1' row;

...wait 11 seconds to observe the DB changes (auto-deleted/expired rows)...

execute redis_get('mz1'); -- returns 0 rows;

execute redis_expire('mz1', 10); -- this is a no-op since mz1 expired due to row-level TTL.

This test-harness is not exhaustive but demonstrates the core behavior of CockroachDB highlighting outputs when keys exist and what you can expect after they’ve expired.

---

conclusion

If you’re already operating on a CockroachDB database, this is a quick extension to simulate Redis-style capabilities without the need to provision a dedicated Redis platform. For example, during the development of a web-application that requires session & cookie tracking, this technique is a quick add-on that lets you prove out your code and demo the app. When you’re ready to produce a production environment, you can then provision a true Redis platform and use that to perform the full scale of capabilities.

---

terminology & resources

Batch Delete Expired Data with Row-Level TTL

TIMESTAMP / TIMESTAMPTZ

CockroachDB in-memory storage options

redis.io homepage

Comments

[Mark Zlamal]

Bug fix: added the "and expired_at > now()" condition to ensure that we update expiry on rows that are still live. This bug fix prevents updating expiry on rows that are expired, but not garbage collected.

prepare redis_expire(string, integer) as
  update redis_tbl set expired_at = cast(cast(now() as integer) + $2 as timestamptz) where key = $1 and expired_at > now();

[Mark Zlamal]

UPDATE!
Instead of using the command:

• alter table redis_tbl configure zone using gc.ttlseconds = 300;

Don't use the zone-configuration, and instead consider using the ttl_job_cron parameter in the create table to have the same deletion-effect:

create table redis_tbl (
  key string primary key,
  value string,
  expired_at timestamptz
) with (ttl_expiration_expression = 'expired_at', ttl_job_cron = '*/5 * * * *');