Query timestamps in Cassandra drivers

Timestamps determine the order of precedence for operations on the same column value from different queries. In Apache Cassandra®-based databases, each mutation (update, insert, delete) is assigned a microsecond-precision timestamp to order operations relative to each other.

The order of precedence for operations on the same column value is as follows:

Data with the latest timestamp.
If the operations have the same timestamp, DELETE has priority over INSERT and UPDATE.
If the operations have the same timestamp and type, the lexically larger value of data has priority. For example, the value 2 is chosen over 1.

Timestamp generation

Timestamps can be generated by the driver client or the server-side node coordinating the request.

DataStax-compatible drivers use client-generated timestamps by default for Cassandra versions 2.1 and later, DSE versions 4.7 and later, Astra DB, and HCD. Older versions of Cassandra and DSE don’t support client timestamps, as they were introduced in the CQL native protocol version 3.

Client-side timestamp generation is the default to keep order of operations predictable from the perspective of a single client. By using monotonically increasing client-side timestamps, the driver ensures that all operations are written in the sequential order that they were executed within the scope of that instance.

Without client timestamps, the client is at the whim of timestamps assigned by coordinating nodes. Coordinating nodes assign timestamps based on their internal system clock. In a distributed system with multiple nodes, it is difficult to keep all system clocks synchronized. Each node is subject to clock drifts, ranging from tens of milliseconds to seconds, even when the nodes use NTP or other clock synchronization software.

When using server-side timestamps, it is possible for operations to be applied out of order, even when executed sequentially from a single client instance. This can lead to unexpected results, such as missing or incorrect data.

Example: Unexpected order of operations caused by server timestamps

A client executes DELETE FROM tbl_a WHERE key = 0. The query is sent to Node A, which creates a delete mutation with timestamp 10.
The client then executes UPDATE tbl_a SET x = 'hello' WHERE key = 0. The query is sent to Node B, which creates an update mutation with timestamp 9.
Finally, the client executes SELECT x FROM tbl_a WHERE key = 0, which receives a result set with 0 rows.

It should be unexpected that no rows were returned from the SELECT query. Even though the initial DELETE operation was executed, programmatically, before the UPDATE operation, it takes precedence because the largest server timestamp (10) was assigned to it.

+ This scenario is avoided completely by using client timestamps.

When to use server timestamps

One possible downside to using client timestamps is that the number of client application servers often outnumber database nodes in production environments. It is not unusual for different applications using the same database cluster to be managed by different teams. In these cases, it can be operationally challenging to keep the clocks synchronized between many different client application servers.

Out-of-sync client application server clocks is an issue only when clients make updates to the same partition values as other clients within a window that could be smaller than the expected clock drift between client nodes.

Even in this case, it may not be important that updates made in this window be properly ordered in the sequence in which they were executed. It is possible that these updates were made by different parties who are not aware of one another. If it is important, then consider using lightweight transactions (LWT).

Lightweight transactions and client timestamps

When executing lightweight transactions (LWTs), any client timestamp assigned to those operations is discarded. This is because Cassandra-based clusters maintain a separate timestamp generator that ensures the assigned timestamp increases monotonically across all LWTs.

However, don’t mix LWTs and other mutation operations on a single table. This is an anti-pattern because the timestamp mechanism used for non-LWT operations is different than the one used by LWTs, even when using server timestamps.

Synchronize clocks across servers

Regardless of the timestamp strategy, DataStax strongly recommends using a service like NTP to keep the system clocks synchronized across all machines in the data ecosystem.

DataStax also recommends that you measure and understand the degree of clock drift among all the servers in your production environment to be aware of time windows that may exist between nodes. Use utilities and commands, such as clockdiff, ntpdate -q, and ntp -q, to measure clock differences between servers.