PostgreSQL Extended Query Lifecycle

This page walks through the complete journey of a PostgreSQL prepared statement as it moves through ProxySQL — from the initial Parse message sent by a client, through one or more Bind/Execute cycles, and finally to Close. All behaviour described here applies to the PostgreSQL Extended Query Protocol as supported in ProxySQL v3.0.3+.

Preparation Phase

When a client sends a Parse message, ProxySQL intercepts it before it reaches any backend server.

Hash Computation

ProxySQL computes a content hash (digest) that uniquely identifies the prepared statement within a specific context. The hash is derived from:

Including parameter types is specific to the PostgreSQL protocol. The same SQL text prepared with int4 parameters and again with text parameters will produce different digests and therefore separate global cache entries, because the backend treats them as distinct prepared statements with potentially different execution plans.

Global Cache Lookup

After computing the hash, ProxySQL performs a lookup against the global prepared statement cache — a process-wide structure shared across all client connections.

Cache hit — an equivalent statement has been prepared before:

1. Retrieve the existing global entry and its global ID.
2. Record a mapping from the client’s statement name to that global ID in the per-connection client map.
3. Increment the client reference count on the global entry.
4. Return ParseComplete to the client immediately.

No backend connection is contacted. The client receives a valid response without any indication that the result came from the cache rather than a live backend interaction.

Cache miss — no matching entry exists:

1. Forward the Parse message to a backend connection selected via query rules (or the default hostgroup). ProxySQL rewrites the statement name to proxysql_ps_{n} before sending.
2. Receive ParseComplete from the backend.
3. Create a new global entry in the cache, storing the SQL text, parameter type metadata, and a newly assigned global ID.
4. Record the backend-side name (proxysql_ps_{n}) in the backend map for that connection, keyed by the global ID.
5. Map the client’s original statement name to the new global ID in the per-connection client map.
6. Return ParseComplete to the client.

In both cases the client receives a standard ParseComplete response and is unaware of what path was taken internally.

Execution Phase

Execution in the PostgreSQL Extended Query Protocol consists of a Bind message followed by an Execute message. ProxySQL queues these messages as they arrive and processes them together when a Sync message is received.

Statement Name Translation

When processing the queued messages on Sync, ProxySQL performs the following translation steps:

1. Resolve client name to global ID: Look up the statement name from the Bind message in the per-connection client map to obtain the global ID. If no mapping exists, the statement was never prepared in this session and an error is returned.

2. Select a backend connection: Choose a connection from the pool of the target hostgroup, determined by re-evaluating query rules against the statement’s SQL text and the current session context.

3. Resolve global ID to backend name: Look up the global ID in the backend map for the selected connection to find the proxysql_ps_{n} name scoped to that connection.

4. Rewrite and forward: Replace the client’s statement name in the Bind message with the backend-local proxysql_ps_{n} name and forward the Bind and Execute messages to the backend.

Lazy Preparation

When step 3 finds that the selected backend connection has no entry for this global ID — because the connection is newly established, was recycled, or this is the first time the statement has been routed to this hostgroup — ProxySQL performs lazy preparation transparently before forwarding the Bind:

1. Using the SQL text and parameter type metadata stored in the global cache entry, send a new Parse message to the backend with a fresh proxysql_ps_{n} name scoped to that connection.
2. Receive ParseComplete from the backend.
3. Record the new proxysql_ps_{n} name in the backend map for this connection.
4. Proceed with the Bind and Execute as normal.

Lazy preparation is transparent to the client. The Bind/Execute response arrives without any indication that an additional Parse was sent to the backend. This mechanism means ProxySQL never needs to pre-populate all backend connections with every known statement; it prepares each statement on a given backend only when execution is actually routed there.

Close Phase

When a client sends a Close message targeting a statement name, ProxySQL handles teardown at the client level without contacting any backend.

Client-Side Cleanup

1. Remove the client’s statement name from the per-connection client map.
2. Decrement the client reference count on the corresponding global cache entry.

Backend Statement Retention

Backend connections are not contacted during a client Close. The statement remains prepared on any backend connections that have it in their backend map. This is intentional:

• Other active client connections may be executing the same statement concurrently.
• Keeping the backend statement alive means future clients can route executions to those backends without incurring a lazy preparation round-trip.
• Sending a Close to every backend that holds the statement would add latency and protocol overhead for little benefit.

Global Entry Retention

The global cache entry is retained as long as either reference count is greater than zero:

Once evicted, the next Parse for an equivalent statement (same SQL, user, schema, and parameter types) will go through the cache-miss path again.

Error Scenarios

Backend Connection Lost

If a backend connection is dropped due to a network failure, server restart, or connection pool eviction, the global cache entry is unaffected. The backend map entries for the lost connection are discarded along with the connection itself. On the next execution that routes to a new backend connection from the same hostgroup, lazy preparation runs automatically using the metadata stored in the global cache. From the client’s perspective, execution proceeds normally after any transparent reconnection; the client does not need to re-send Parse.

Schema Changes After Preparation

If a DDL statement such as ALTER TABLE or DROP COLUMN is executed against a table referenced by a cached prepared statement, the metadata stored in the global cache may no longer match the backend’s current schema. When ProxySQL detects a metadata mismatch — indicated by an error response from the backend during execution — it refreshes the cached metadata by re-preparing the statement on the backend and updating the global entry. Subsequent executions use the refreshed metadata.

Routing to a Different Backend

Because query rules are re-evaluated at execution time rather than being locked at preparation time, a Bind/Execute sequence may be routed to a different hostgroup or backend connection than the one used during the original Parse. ProxySQL handles this transparently:

1. Select a connection from the newly determined hostgroup.
2. Check the backend map for that connection.
3. If the statement is not yet prepared on that connection, perform lazy preparation using the global cache metadata.
4. Rewrite the Bind message with the new backend-local name and forward normally.

This allows correct routing of prepared statements into transactions, to read replicas, or to sharded hostgroups, without requiring the client to re-send Parse.

Related Documentation

• PostgreSQL Prepared Statements Architecture
• PostgreSQL Prepared Statement Cache
• PostgreSQL Extended Query Protocol
• PostgreSQL Connection Pooling, Multiplexing & Caching