---
title: "PREPARE COMPILED: Real Prepared Plans with an LRU Plan Cache"
description: "`PREPARE COMPILED foo AS SELECT ...; EXECUTE COMPILED foo;` — a fingerprint-keyed LRU plan cache shared across connections. How Lite v3.6.0 and Full v7.4.0 normalise SQL, parameterise literals, and dispatch in microseconds."
date: 2026-05-22
updated: 2026-05-22
tag: "Engineering"
canonical: https://www.heliosdb.com/blog/prepare-compiled-lru-plan-cache.html
---

# PREPARE COMPILED: Real Prepared Plans with an LRU Plan Cache

PostgreSQL's `PREPARE` caches a parsed and analysed statement on the connection. Repeated `EXECUTE` calls skip parsing and re-planning unless statistics drift — a real win for hot-path queries inside an ORM. HeliosDB Lite v3.6.0 and Full v7.4.0 add a parallel surface, `PREPARE COMPILED`, that caches the plan in the *engine* rather than the *connection*. The cache is shared across all sessions, fingerprint-keyed, LRU-bounded, and survives connection churn. This post walks through how it works.

## Surface area

```
-- Compile once
PREPARE COMPILED hot_query AS
SELECT * FROM orders
WHERE tenant_id = $1 AND status = $2 AND created_at >= $3
ORDER BY created_at DESC LIMIT 50;

-- Execute on any session
EXECUTE COMPILED hot_query (42, 'open', '2026-01-01');

-- Drop
DEALLOCATE COMPILED hot_query;
```

Inspection helpers expose stats — executions, last-used time, plan size:

```
SELECT name, executions, last_used_at, plan_size_bytes
FROM helios_compiled_plans
ORDER BY executions DESC;
```

## The fingerprint

Plans are keyed by a stable 64-bit fingerprint of normalised SQL. Normalisation strips comments, collapses whitespace, lowercases keywords, and parameterises literal numbers and strings — so `SELECT 1` and `SELECT 2` produce the same fingerprint, while `SELECT * FROM t WHERE a=1` and `SELECT * FROM t WHERE b=1` do not. Fingerprints are deterministic across processes and platforms; you can compute them client-side and pre-warm the cache if you want.

## The cache

The cache is a bounded LRU keyed by fingerprint, with eviction by true least-recently-used. Per-entry data: SQL text, typed parameter slots, the plan body, a hit counter, last-used timestamp, and plan size in bytes. Inspect the live cache via `helios_compiled_plans`.

## What it costs and what it buys

Cost: an O(1) hash lookup on every parse. Cache misses fall through to the parser and planner, then insert — the first call is slightly slower than the no-cache baseline because of the insert overhead. Subsequent calls skip parsing and analysis entirely.

Benefit: on a workload with 99% cache hits (a typical ORM hot-loop), the parser and planner drop out of CPU profiles. Wall-clock improvement on representative OLTP loops with a moderate-complexity query lands in the high single digits to mid-teens, depending on edition and per-call overhead.

## Versus connection-scoped PREPARE

Both surfaces co-exist. Use `PREPARE` for connection-local hot-path queries that benefit from per-connection plan customisation. Use `PREPARE COMPILED` for queries hot across *many* connections — pooled web traffic, agent fleets — where you want the cache to outlive any single session.