Couchbase N1QL Prepared Statements: What the Plan Cache Actually Buys You

The Problem: One Query Shape, Parsed Over and Over

An application that fetches hotels by city usually sends the same N1QL text with a different value each time: SELECT name FROM ... WHERE city = $1. Unless you take an extra step, the Query service parses, validates, and plans that statement on every request. The statement text is identical, so the work is repeated for no reason.

N1QL prepared statements exist to remove that repetition. You plan the statement once, keep the plan on the server, and send only the statement name plus parameter values afterwards. The same mechanism also pushes values through parameter markers instead of string concatenation, which is the practical defense against injection in query text.

What PREPARE and EXECUTE Actually Do

Two statements do the work. PREPARE compiles a statement and returns a name; EXECUTE runs a named statement with values bound to its parameter markers.

PREPARE hotels_by_city AS
SELECT name, address
FROM `travel-sample`.inventory.hotel
WHERE city = $city AND type = "hotel";

EXECUTE hotels_by_city USING {"city": "San Francisco"};

Notes on the syntax above:

• $city is a named parameter marker. Positional markers ($1, $2) work too; pick one style and stay consistent.
• Values are passed as JSON, so strings, numbers, booleans, nulls, arrays, and objects keep their types without manual quoting.
• Parameter markers bind values only. You cannot parameterize a keyspace, collection, or field name, so any dynamic identifier still needs an allowlist in application code.

SDKs wrap this cycle. Instead of sending PREPARE yourself, you ask the SDK to prepare a statement and then execute it with parameters; the SDK keeps the returned name and reuses it. Method names and options differ between the Java, .NET, Python, Go, and Node.js SDKs and between major SDK versions, so check your SDK's query documentation rather than copying a snippet from another language. The important part is the same everywhere: prepare once, execute many times.

A Worked Example You Can Reproduce

Run these in the Query Workbench or the cbq shell against the travel-sample bucket, as a user with query access to that bucket. Nothing here writes data, so the only state to clean up is the prepared statement itself.

1. Run the PREPARE statement above. Expect a single result containing the generated statement name. If you get a syntax error, check the backticks around travel-sample and the quoting of the string literal.
2. Run EXECUTE hotels_by_city USING {"city": "Paris"};. Expect the same rows you would get from the equivalent ad-hoc SELECT.
3. Run it again with a different city. The second execution should not require re-planning, because the name resolves to the stored plan.
4. Inspect system:prepareds to see the prepared statement registered on the node that handled the request. Confirm this catalog exists on your server version before relying on it in a runbook.
5. Compare against the ad-hoc form: run the same SELECT with a literal value in a loop, then the EXECUTE form in a loop, and watch Query service CPU and request latency in the cluster's monitoring. Treat any numbers you collect as specific to your cluster, indexes, and data — not as a general benchmark.

When you are finished, release the statement with DEALLOCATE PREPARE hotels_by_city; if your server version supports that syntax; otherwise restarting the Query service or letting the cache evict the entry also clears it. Confirm the exact syntax for your version before putting it in automation.

The Trade-off: Stale Plans and Cache Scope

A cached plan is a snapshot of the optimizer's decision. If you later create a secondary index that would be a better access path, the existing prepared statement can keep using the old plan until it is re-prepared or evicted. Couchbase does not automatically re-plan every prepared statement when you run DDL.

Two more properties matter operationally:

• The plan cache lives in Query service memory and is finite. Under memory pressure, entries can be evicted, and the next execution pays the planning cost again.
• Prepared statements are handled per Query node. In a multi-node query deployment, requests can land on different nodes, which is why the SDK manages the prepare/execute lifecycle for you instead of leaving node affinity to your application. Verify the retry and re-prepare behavior in your SDK version.

Prepared statements also do not fix a bad index strategy. If a query has no usable index, caching the plan just caches a slow plan.

What to Do Next

1. Find your most frequent parameterized query shapes in system:completed_requests and rank them by request count.
2. Convert the top few to prepared statements in the SDK, keeping parameter markers for all values.
3. Add a step to your index-creation runbook that re-prepares or releases affected statements after DDL.
4. Watch Query service CPU and latency before and after, and check plan cache hit/miss counters if your version exposes them. If hits stay low, something is changing the statement text — a differing literal, a different parameter style, or a per-request statement built by concatenation.

The payoff is real but modest and workload-dependent: you remove repeated parsing and planning from the hot path, and you get parameter binding as a side effect. The cost is a small amount of lifecycle management, mostly around schema changes.