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.