CockroachDB: row-level TTL to simulate Redis
Tags: cockroachdb, redis
Published: 2023-04-03 16:53:52

## 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.

```sql
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.

> ```sql
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)._

[]()

>```sql
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._

[]()

> ```sql
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.

```sql
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)..._

```sql
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](https://www.cockroachlabs.com/docs/stable/row-level-ttl.html)

[TIMESTAMP / TIMESTAMPTZ](https://www.cockroachlabs.com/docs/stable/timestamp.html)

[CockroachDB in-memory storage options](https://www.cockroachlabs.com/docs/stable/cockroach-start.html#store)

[redis.io homepage](https://redis.io)