Cassandra Prepared Statements, in depth: the server cache, driver lifecycle, binding rules and the failures they prevent

What preparing does on the server

When a client sends a PREPARE message, the node parses the CQL, resolves the table and columns, checks types, and stores the resulting statement object in its prepared statement cache. The cache key is an MD5 digest of the query string, combined with the keyspace when the query does not name one explicitly. That has a precise consequence: the identity of a prepared statement is its exact text. Two strings that differ only in whitespace or in an inlined literal are two different statements.

The reply carries the statement id and two kinds of metadata. The variables metadata lists each bind marker's name and CQL type, plus the positions of any bind markers that make up the partition key. The result metadata describes the columns a SELECT will return. From protocol v5, used by Cassandra 4.0 and later, it also carries a result metadata id, which matters when the schema changes later. An EXECUTE message then contains the statement id, the serialised values, the consistency level and paging state. No text is parsed again.

The lifecycle across the cluster

The server cache is per node and is not replicated by Cassandra itself. Drivers therefore manage it. The Java driver 4.x sends the first PREPARE to one node, so a malformed query fails once rather than on every node, then prepares the statement on all other nodes in the background, controlled by datastax-java-driver.advanced.prepared-statements.prepare-on-all-nodes. When a node that was down comes back, the driver re-prepares the statements it knows about on that node, controlled by datastax-java-driver.advanced.prepared-statements.reprepare-on-up. Leave both enabled unless you have measured a reason not to.

If an execution reaches a node that does not know the id, for example after a restart or an eviction, the node answers with an UNPREPARED error. The driver keeps the query text for each prepared statement, sends PREPARE to that node, and retries the EXECUTE transparently. The application never sees it, but each one costs an extra round trip. Since Cassandra 3.10 nodes also persist their cache to the system.prepared_statements table and reload it at startup, which avoids most re-prepare storms after rolling restarts.

Preparing and binding in code

Prepare once, at startup or on first use, and reuse the PreparedStatement object for the life of the session. The Java driver 4.x manual states that the session caches prepared statements, so calling prepare twice with the same string returns the same instance. That makes a repeated call cheap but not free, and it does nothing to help when the string itself varies.

// Java driver 4.x
public final class OrderRepository {
    private final CqlSession session;
    private final PreparedStatement insert;
    private final PreparedStatement byCustomer;

    public OrderRepository(CqlSession session) {
        this.session = session;
        this.insert = session.prepare(
            "INSERT INTO shop.orders_by_customer (customer_id, order_ts, order_id, total) "
          + "VALUES (?, ?, ?, ?)");
        this.byCustomer = session.prepare(
            "SELECT order_ts, order_id, total FROM shop.orders_by_customer "
          + "WHERE customer_id = :cid LIMIT :n");
    }

    public void save(Order o) {
        session.execute(insert.bind(o.customerId(), o.ts(), o.id(), o.total())
                              .setIdempotent(true));       // safe to retry and speculate
    }

    public List<Row> recent(UUID customerId, int n) {
        BoundStatement bs = byCustomer.boundStatementBuilder()
            .setUuid("cid", customerId)
            .setInt("n", n)
            .build();
        return session.execute(bs).all();
    }
}

# Python driver
from cassandra.query import UNSET_VALUE

insert = session.prepare(
    "INSERT INTO shop.orders_by_customer (customer_id, order_ts, order_id, total, note) "
    "VALUES (?, ?, ?, ?, ?)")

def save(order):
    note = order.note if order.note is not None else UNSET_VALUE   # no tombstone for a missing note
    session.execute(insert, (order.customer_id, order.ts, order.id, order.total, note))

Two details in this code matter. Marking a statement idempotent tells the Java driver it may retry it after a timeout and run speculative executions; the driver defaults to non-idempotent, so a plain insert is not retried unless you say so. And named markers such as :cid make bindings readable and survive column reordering in the query text.

The server cache and how it floods

Each node's cache is bounded by prepared_statements_cache_size in cassandra.yaml (prepared_statements_cache_size_mb in older releases). By default it is sized automatically at 1/256th of the heap or 10 MB, whichever is larger. A healthy application has a few dozen to a few hundred distinct statements, which fit easily. Trouble starts when the number of distinct strings is unbounded, and the classic cause is preparing a string with values concatenated into it:

// Anti-pattern: every customer id produces a new statement and a new cache entry on every node
session.execute(session.prepare(
    "SELECT * FROM shop.orders_by_customer WHERE customer_id = " + customerId));

The server cache evicts the least recently used entries, so legitimate statements are pushed out, executions start hitting UNPREPARED, and every node spends CPU parsing. The symptoms are measurable. The CQL metrics group exposes PreparedStatementsCount, PreparedStatementsEvicted, PreparedStatementsExecuted, RegularStatementsExecuted and PreparedStatementsRatio, and nodes log a periodic warning that prepared statements were discarded because the cache limit was reached. A steadily climbing count with non-zero evictions is a cache flood; you can find the offending text by querying system.prepared_statements on any node and grouping query_string values by shape. Our operational metrics article shows where these fit into a dashboard.

Binding rules that prevent data problems

Binding is where data modelling mistakes leak in. The important rules:

• Null writes a tombstone. Binding null to a column in an INSERT or UPDATE deletes that cell. From native protocol v4 (Cassandra 2.2) a variable can be left unset instead, which writes nothing; in the Java driver 4.x any variable you do not bind is unset, and unset(name) clears one explicitly. Inserting objects with many optional fields as nulls is a reliable way to generate millions of tombstones; see our tombstones article.
• Variable-length IN lists. WHERE id IN (?, ?, ?) produces a different statement for each list length. Write WHERE id IN ? and bind a list instead, and keep such lists short because the coordinator fans out to every partition named.
• Markers work for values, not identifiers. LIMIT ?, USING TTL ? and USING TIMESTAMP ? accept bind markers; table and column names do not. If the table name varies, prepare one statement per table, from a finite list.
• Types are checked client-side. Binding a long to an int column fails in the driver before anything is sent, which is a feature: the metadata tells the driver the exact codec to use.

Token-aware routing comes from the metadata

Because the variables metadata names which markers form the partition key, the driver can compute the routing key from the bound values, hash it with the cluster's partitioner, and send the request directly to a replica that owns the token. That saves a network hop on every request and spreads coordinator work evenly; our token ring article explains the ownership the driver is reading. A simple, unprepared statement carries no such metadata, so unless you set the routing key yourself the driver picks a coordinator without knowing where the data lives.

Routing needs the whole partition key bound with equality. A query restricted on only part of a composite key, or one that uses IN on the partition key, has no single routing key, and the coordinator does the fan-out. Batches follow the same rule: a logged batch spanning many partitions loses the benefit; our batch article covers when batching helps.

Schema changes and SELECT *

Result metadata is captured at prepare time. Before protocol v5, if you prepared SELECT * FROM t and then added a column, the server returned rows with the new column while the driver still decoded them with the old metadata. Depending on the driver this meant missing columns or decoding errors until the application restarted. The fix, tracked as CASSANDRA-10786, arrived with Cassandra 4 and protocol v5: the client sends the result metadata id it holds, the server notices when it is stale and returns the new metadata, and the Java driver updates its cache transparently.

Even on Cassandra 4 or later, list columns explicitly in prepared SELECTs. Explicit lists make the cost of a query visible, keep an added large column from silently inflating every read, and make the application independent of protocol negotiation, since a cluster or driver that falls back to protocol v4 brings the old behaviour back. Dropping or renaming a column used by a prepared statement still breaks it, so deploy application changes before schema removals.

Worked example: diagnosing a cache flood

A team ran a recommendation service on a 12-node cluster with 8 GB heaps, which gives a cache of 32 MB per node. After a release, p99 read latency rose from 9 ms to 60 ms, and node CPU rose by a third with unchanged traffic. The numbers here are illustrative; the sequence is the one to follow.

1. Metrics showed PreparedStatementsEvicted climbing by thousands per minute on every node, and the logs carried the cache-limit warning. PreparedStatementsRatio was still high, so the code was preparing, but preparing too many distinct strings.
2. Querying system.prepared_statements returned more than 200,000 rows, nearly all variants of one SELECT that differed in an inlined IN list of item ids.
3. The release had replaced WHERE item_id IN ? with a builder that rendered literal ids. Restoring the single bound-list statement fixed the shape.
4. To clear the flood, the team rolled the service first; the evicted legitimate statements were re-prepared on demand and the cache size settled at 140 entries.

Latency returned to baseline within minutes of the deploy. The follow-up was an alert on any sustained rise in PreparedStatementsEvicted, and a unit test that counts distinct query strings produced by the data access layer over a fixture run.

Failure modes and trade-offs

There are cases where a plain statement is fine: one-off administrative queries, schema statements, and tools that run each query once. The trade-off is simple. Preparing costs one extra round trip per node and a cache entry for each query shape, and buys cheaper execution, client-side type checking and replica routing for every later execution. For anything that runs more than a handful of times, prepare it.

What to do next

1. Grep your data access code for string concatenation or formatting into CQL and replace every inlined value with a bind marker.
2. Move every prepare call to startup or a lazily initialised field, and confirm the number of distinct statements is small and stable.
3. Add dashboard panels and alerts for PreparedStatementsCount and PreparedStatementsEvicted on every node.
4. Audit inserts and updates for bound nulls on optional fields and leave those values unset instead.
5. Replace SELECT * in prepared statements with explicit column lists, and confirm the driver negotiates protocol v5 on Cassandra 4 or later.
6. Mark idempotent statements explicitly so retries and speculative execution can work.