DEV Community

Cover image for Prepared Statements in PgJDBC
Duy Nguyen Thanh
Duy Nguyen Thanh

Posted on

Prepared Statements in PgJDBC

#postgres #java #backend

RDBMS systems commonly try to improve query performance by caching a statement's execution context and re-executing it with different parameter values. This approach avoids the overhead of parsing, rewriting, and planning on each execution. PostgreSQL implements this through Prepared Statements.

Disclaimer: This article is written for the purpose of understanding how Prepared Statements work in Java and its official PostgreSQL JDBC driver (PgJDBC). It might not be a comprehensive guide to Prepared Statements and is not intended to be a substitute for reading the official documentation.

1. Simple and Extended Query Protocols

PostgreSQL executes SQL commands through one of two protocols:

  1. The Simple Query protocol: The client sends a textual query string, which is parsed and immediately executed by the server.
  2. The Extended Query protocol: The processing of queries is separated into multiple steps:
    • Parse: creates a Prepared Statement from a textual query string
    • Bind: creates a Portal given a Prepared Statement and values for any needed parameters
    • Execute: runs a Portal's query
    • Sync: provides a resynchronization point for error recovery

1.1. Simple Query Protocol

The Simple Query Protocol provides the most direct method for executing queries.

The following diagram illustrates the flow of the Simple Query Protocol.

How Simple Query protocol works

Despite its simplicity, the Simple Query Protocol does not support granular control over query execution phases. This is where the Extended Query Protocol comes in.

1.2. Extended Query Protocol

The Extended Query Protocol performs the same fundamental steps as the Simple Query Protocol, but executes them in discrete phases, so that some of the work can be done once and reused for multiple executions.

Multi-step processing requires state management, which PostgreSQL handles through two object types: Prepared Statements and Portals.

  • Prepared Statements represent the parsed query string and semantic analysis of the query. They are themselves not executable until parameter values are provided.
  • Portals represent a ready-to-execute or already-partially-executed statement, with any missing parameter values replaced by the values provided by the client.

The server maintains multiple named prepared statements and portals simultaneously. Each is identified by a unique name upon creation. However, these exist only within the scope of a single session and cannot be shared between sessions.

The following sequence diagram illustrates the flow of the Extended Query Protocol, showing the interaction between Client and Server across the four main steps:

How Extended Query protocol works

1.2.1. Query Planning

Query planning is a key efficiency factor in the Extended Query Protocol. It occurs during Bind message processing. It is also the process that decides whether to use a generic plan or to create and use custom plans.

  • Custom plan: Created for a specific statement and parameter set, requiring query planning for each execution.
  • Generic plan: Created for a parameterized statement, enabling plan reuse across multiple executions.

The following flowchart illustrates the plan selection logic for Prepared Statement execution:

Query plan selection logic

References: plancache.c

The server selects between generic and custom plans by evaluating the following conditions in order:

  1. One-shot plan: Use custom plan
  2. No parameters: Use generic plan
  3. No parsing or rewriting required: Use generic plan
  4. The plan_cache_mode configuration:
    • FORCE_GENERIC_PLAN -> Use generic plan
    • FORCE_CUSTOM_PLAN -> Use custom plan
  5. Fewer than 5 existing custom plans: Use custom plan
  6. Average cost of existing custom plans < generic plan cost: Use custom plan

Generic plans typically have lower execution cost than equivalent custom plans, as query planning overhead is eliminated.

1.2.2. Pipelining

The Extended Query Protocol supports pipelining, allowing queries to be sent without waiting for earlier ones to complete. Pipelining has the following characteristics:

  • Dependent Steps: Omit Sync between dependent steps for error recovery. If an early step fails, the server skips subsequent commands until the next Sync.
  • Independent Segments: Separate independently committable segments with Sync messages.
  • Completion: Count ReadyForQuery messages (matching Sync count), not CommandComplete (unreliable due to skipped commands).

1.3. Comparison

TL;DR: The Simple Query Protocol sends a complete query string that is parsed and executed each time. The Extended Query Protocol separates query processing into phases: Parse (create statement), Bind (supply parameters), and Execute. This separation enables plan reuse and improved performance for repeated executions.

Feature Simple Query Protocol (SQP) Extended Query Protocol (EQP)
Execution Flow Single message (Query) containing the entire text string, followed by responses and a final ReadyForQuery message. Multi-step process: Parse (create statement), Bind (create portal/supply parameters), Execute, and Sync (resynchronization).
Multi-Statement Support Allows one or more SQL commands (separated by semicolons) in a single Query message. Cannot include more than one SQL statement in a Parse message.
Parameter Handling Data values must be inserted directly into the query text string. Data values are supplied as separate parameters during the Bind message.
Reusability None; the query is processed and discarded upon completion. High; preparatory steps (Parse) create prepared statements and (Bind) create portals that can be re-used multiple times.
Output Format Always text, except when using FETCH from a cursor declared with the BINARY option. Can be specified as text or binary using format codes in the Bind message.
Error Handling Execution stops immediately at the first error (ErrorResponse is sent), and the rest of the query string is aborted. After an error (ErrorResponse), the backend reads and discards messages until a Sync message is reached, ensuring a clean resynchronization.

2. JDBC Prepare Statements

In Java, it is essential to distinguish between Client-side Prepared Statements and Server-side Prepared Statements described in the previous section.

PreparedStatement ps = connection.prepareStatement("SELECT * FROM users WHERE id = ?");
ps.setInt(1, 1);
ResultSet rs = ps.executeQuery();
Enter fullscreen mode Exit fullscreen mode

The above example creates a Client-side Prepared Statements that eliminates repeated parsing and statement creation, allowing parameter binding and execution to be repeated efficiently.

PgJDBC supports additional connection properties beyond the JDBC URL and credentials to configure driver behavior. Some of these properties, which are particularly relevant to Prepared Statements, are:

Property Type Default Value Description
prepareThreshold int 5 Determine the number of PreparedStatement executions required before switching over to use server-side prepared statements (a server-side prepared statement is created). The value 0 disables the server-side prepared statements generation.
preparedStatementCacheQueries int 256 Determine the number of queries that are cached in each connection.
The default is 256, meaning if you use more than 256 different queries in prepareStatement() calls, the least recently used ones will be discarded. The value of 0 disables the cache.
The cache allows application to benefit from Server Prepared Statements (see prepareThreshold ) even if the prepared statement is closed after each execution.
It is also worth to note that each connection has its own cache.
preparedStatementCacheSizeMiB int 5 Determine the maximum size (in mebibytes) of the prepared queries cache. The main aim of this setting is to prevent OutOfMemoryError.
The default is 5, meaning if you happen to cache more than 5 MiB of queries the least recently used ones will be discarded.
A value of 0 disables the cache.
preferQueryMode string extended Specifies which mode is used to execute queries to database:
- simple means (‘Q’ execute, no parse, no bind, text mode only)
- extended means always use bind/execute messages
- extendedForPrepared means extended for prepared statements only
- endedCacheEverything means use extended protocol and try cache every statement (including Statement.execute(String sql)) in a query cache.

The following image illustrates the performance difference between the two approaches when executing a SELECT statement with a single parameter and different prepareThreshold values.

Prepared Statements Benchmark

Benchmark environment:

  • CPU: Apple M4 Pro
  • Memory: 16GB
  • PostgreSQL Version: 18.0
  • PgJDBC Version: 42.7.8

The source code of the benchmark is available here.

The benchmark results demonstrate the following:

  • prepareThreshold = 0: Server-side prepared statements are disabled. Latency remains consistent across executions.
  • prepareThreshold = x (where x > 0): The first x-1 executions use client-side prepared statements. At the x-th execution, a server-side prepared statement is created, reducing latency for subsequent executions.
  • Generic plan adoption: After 5 executions with the server-side prepared statement (executions x through x+4), the server switches from custom plans to a generic plan, further reducing latency.
  • Generic plan generation: At execution x+5, a slight latency spike occurs as the server performs query planning to generate and persist the generic plan.

References

Top comments (0)