Prepared statements are great… until they suddenly aren’t. With the YugabyteDB JDBC Smart Driver (which inherits pgJDBC behavior), a prepared statement is executed using the extended protocol for the first five runs. On the 6th execution, the driver switches to a server‑side prepared statement.
That flip can cause the server to choose a generic plan instead of a custom plan. In many OLTP workloads, especially in distributed SQL where partition pruning, tablet locality, and skewed parameter selectivity matter, a generic plan can be noticeably slower.
This explains a common “warm‑up cliff”: your service starts fast, then gets slower right after a small number of calls.
If you’re running pre‑2025.1 YugabyteDB (without Postgres 15’s plan_cache_mode), the quickest way to keep performance stable is to disable the server‑side flip by connecting with prepareThreshold=0. On 2025.1+ (PG15‑based YSQL), you can keep server‑side prepares but force custom plans with plan_cache_mode=force_custom_plan when appropriate.
Minimal proof program (Smart Driver)
Below, a minimal Java program proves the behavior. What does it do?
Opens a single JDBC connection using the YugabyteDB JDBC Smart Driver,
Prepares
SELECT ?::int,Executes it 6 times, and
Prints the count from
pg_prepared_statementsafter each run.
When run with default settings (threshold 5), you’ll see the count jump on the 6th call. When run with prepareThreshold=0, it stays 0 for all 6 calls.
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.yb.demo</groupId>
<artifactId>yb-jdbc-preparethreshold-min</artifactId>
<version>1.0.0</version>
<properties>
<maven.compiler.source>17</maven.compiler.source>
<maven.compiler.target>17</maven.compiler.target>
</properties>
<dependencies>
<!-- YugabyteDB JDBC Smart Driver -->
<dependency>
<groupId>com.yugabyte</groupId>
<artifactId>jdbc-yugabytedb</artifactId>
<version>42.7.3-yb-4</version>
</dependency>
</dependencies>
<build>
<plugins>
<!-- Build a single runnable JAR with all deps -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-assembly-plugin</artifactId>
<version>3.6.0</version>
<configuration>
<archive>
<manifest>
<mainClass>com.yb.demo.PrepThresholdProof</mainClass>
</manifest>
</archive>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
</configuration>
<executions>
<execution>
<id>make-assembly</id>
<phase>package</phase>
<goals><goal>single</goal></goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
PrepThresholdProof.java
package com.yb.demo;
import java.sql.*;
public class PrepThresholdProof {
public static void main(String[] args) throws Exception {
if (args.length < 1) {
System.err.println("Usage: java -jar ...jar-with-dependencies.jar \"jdbc:yugabytedb://HOST:5433/DB?user=...&password=...&prepareThreshold=5\"");
System.exit(1);
}
String url = args[0];
// Ensure the YugabyteDB JDBC Smart Driver is loaded
Class.forName("com.yugabyte.Driver");
try (Connection conn = DriverManager.getConnection(url)) {
System.out.println("Connected. URL (redacted): " + url.replaceAll("password=[^&]+", "password=****"));
// One tiny prepared statement; no table needed.
String sql = "SELECT ?::int";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
for (int i = 1; i <= 6; i++) {
ps.setInt(1, i);
try (ResultSet rs = ps.executeQuery()) {
rs.next(); // consume
}
int preparedCount = countPrepared(conn);
System.out.printf("Run #%d -> pg_prepared_statements count: %d%n", i, preparedCount);
}
}
}
}
private static int countPrepared(Connection conn) throws SQLException {
try (Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT count(*) FROM pg_prepared_statements")) {
rs.next();
return rs.getInt(1);
}
}
}
Run the two proofs
1) Default behavior (server‑side prepare on 6th execution)
java -jar target/yb-jdbc-preparethreshold-min-1.0.0-jar-with-dependencies.jar \
"jdbc:yugabytedb://<HOST>:5433/<DB>?user=<USER>&password=<PW>"
Expected: runs #1–5 → 0, run #6 → 1 (at least one server‑side prepared statement now exists).
[root@localhost yb-jdbc-preparethreshold-min]# java -jar target/yb-jdbc-preparethreshold-min-1.0.0-jar-with-dependencies.jar "jdbc:yugabytedb://127.0.0.1:5433/yugabyte?user=yugabyte&password=password"
Connected. URL (redacted): jdbc:yugabytedb://127.0.0.1:5433/yugabyte?user=yugabyte&password=****
Run #1 -> pg_prepared_statements count: 0
Run #2 -> pg_prepared_statements count: 0
Run #3 -> pg_prepared_statements count: 0
Run #4 -> pg_prepared_statements count: 0
Run #5 -> pg_prepared_statements count: 1
Run #6 -> pg_prepared_statements count: 1
2) Disable server‑side prepare (keep every execution custom)
java -jar target/yb-jdbc-preparethreshold-min-1.0.0-jar-with-dependencies.jar \
"jdbc:yugabytedb://<HOST>:5433/<DB>?user=<USER>&password=<PW>&prepareThreshold=0"
Expected: runs #1–6 → always 0.
[root@localhost yb-jdbc-preparethreshold-min]# java -jar target/yb-jdbc-preparethreshold-min-1.0.0-jar-with-dependencies.jar "jdbc:yugabytedb://127.0.0.1:5433/yugabyte?user=yugabyte&password=password&prepareThreshold=0"
Connected. URL (redacted): jdbc:yugabytedb://127.0.0.1:5433/yugabyte?user=yugabyte&password=****&prepareThreshold=0
Run #1 -> pg_prepared_statements count: 0
Run #2 -> pg_prepared_statements count: 0
Run #3 -> pg_prepared_statements count: 0
Run #4 -> pg_prepared_statements count: 0
Run #5 -> pg_prepared_statements count: 0
Run #6 -> pg_prepared_statements count: 0
When to prefer plan_cache_mode instead
If you’re on a PG15‑based YugabyteDB (preview 2.25+ and the 2025.1 STS series), you can control plan selection on the server:
•
plan_cache_mode = force_custom_plan– always use a custom plan when executing cached plans.•
plan_cache_mode = auto– default behavior (let the server choose generic vs custom).
This is perfect when you want server‑side prepares (for reuse) but still force custom plans for a sensitive session, user, or workload. For older clusters that don’t support plan_cache_mode, stick with prepareThreshold=0 at the driver level.
Have Fun!
