Prepared statements
Prepared statements are SQL queries that are sent to the server in advance. They are parsed by the server, avoiding that cost at execution time. The client can request a statement to be executed by using its name and by passing optional parameters.
PgDog supports prepared statements in transaction mode. In session mode, no special handling is required.
How it works
When the client sends a Parse
message, PgDog records the query in a global cache. If it's a new query, PgDog creates a new prepared statement
entry and gives it a unique name. The Parse
message is then renamed and sent to the server. This way, multiple clients can send the same prepared
statement through PgDog without causing "duplicate prepared statement"
errors.

While the global cache helps with statement reuse, each client keeps its own mapping of prepared statement names.
This allows the clients to send Bind
and Describe
messages, as if they were using a dedicated server connection.
Close
messages are ignored and CloseComplete
is returned to the client. PgDog manages prepared statements
at a global level, so clients can't evict a prepared statement potentially used by another client.
Cache limit
Prepared statements are stored in memory. On machines constrained by RAM, it's reasonable to introduce a limit on how many statements should be stored. This limit is controlled through configuration:
This limit is strictly enforced on server connections: if a prepared statement needs to be sent to a server connection and it would exceed this capacity, the least recently used statement will be closed to allow for more space on the connection.
Since clients re-use prepared statements, this limit isn't enforced for clients: they can prepare as many statements as they wish (and you have memory for). Each statement keeps a counter of when it's used by a client. If the counter reaches zero, i.e., all clients either closed it explicitly or disconnected, the statement is removed from the global cache.
Tracking used statements
The number of prepared statements and what they are can be tracked by executing this command on the admin database:
Additionally, each server connection entry in SHOW SERVERS
will report the number of currently active statements.
Simple protocol
While prepared statements are typically sent using the extended protocol (Parse
, Bind
, Describe
), Postgres
supports preparing statements using the PREPARE
command, and executing them using the EXECUTE
command.
PgDog supports rewriting these prepared statements to make sure their names are globally unique, just like with the extended protocol.
For example:
will be rewritten by PgDog to:
Statements sent over the simple protocol are not checked against the global cache. Each new statement is given a unique global name. Since this requires PgDog to parse each incoming query, and that's computationally expensive, this feature is diabled by default.
You can enable it by setting to prepared_statements
to "full"
.
Statements prepared using this method can be executed normally with Bind
and Execute
messages. Result data types can be inspected with Describe
, just
like a regular prepared statement.