На PreparedStatement обычно заканчивается вводный курс по JDBC / PostgreSQL. Все довольно просто, пока не задумываешься: а работает ли оно как надо в моем приложении?

Разберемся в подробностях и закроем эту тему.

Основы

Банальный пример на JDBC, как не надо делать:

String sql = "select * from users where email = '" + email + "'";
statement.executeQuery(sql);

Проблема не только в sql-инъекции. У базы каждый раз новый запрос. Приходится его парсить, анализировать, строить план и тд.

Перепишем на PreparedStatement:

var ps = connection.prepareStatement(
    "select id, email from users where email = ?"
);
ps.setString(1, email);
ps.executeQuery();

SQL-шаблон стабилен, значение передается отдельно. В терминах JDBC PreparedStatement — это объект для SQL, который можно заранее подготовить и выполнять много раз с разными параметрами.

Слово “prepared” перегружено:

1. в Java это интерфейс JDBC;

2. в PostgreSQL есть SQL-команды PREPARE / EXECUTE;

3. а еще есть protocol-level prepared statements в extended query protocol: Parse, Bind, Execute.

Для Java-разработчика важнее всего именно третий вариант, потому что именно через него обычно работает pgJDBC. Но при этом как раз его и забывают. Разберемся по порядку.

Custom plan против generic plan

Prepared statement в PostgreSQL может выполняться с custom plan или generic plan. Custom plan строится под конкретные значения параметров. Это важно, если распределение данных неровное:

select * from orders
where created = $1 and deleted = $2;

Для deleted = false может быть выгоден один план (построен индекс по created для неудаленных записей). Для deleted = true - другой (редкий запрос, индекса нет).

В свою очередь generic plan один для всех значений: он экономит планирование, но иногда хуже исполняется.

По умолчанию PostgreSQL использует эвристику: первые несколько выполнений идут с custom plan, затем сервер сравнивает стоимость generic plan со средней стоимостью custom plan и выбирает, что дальше дешевле. Конечно, есть настройка plan_cache_mode, но ее обычно бзе повода не трогают.

Prepared statement не обязан ускорять каждый запрос. Если запрос одноразовый или планирование дешевое — выигрыша почти не будет. Основная польза в другом: безопасная параметризация, стабильный SQL, меньше parse/analyze работы, переиспользование метаданных и иногда плана.

Как это выглядит в протоколе PostgreSQL

В simple query protocol клиент отправляет серверу строку SQL целиком. В extended query protocol работа разбита на шаги: Parse создает prepared statement, Bind подставляет значения и формирует portal, Execute выполняет. В документации протокола сказано, что extended protocol позволяет передавать значения отдельно от текста запроса.

Есть две важные детали.

Первая: named prepared statement живет до конца текущей PostgreSQL-сессии, если его явно не закрыли. Это не глобальный объект базы. У каждого физического соединения свой набор prepared statements. Переподключились — потеряли named prepared statement.

Вторая: unnamed prepared statement живет до следующего Parse в unnamed statement. Поэтому реальный кэш prepared statements в Java-приложении почти всегда основан на named statements на конкретном connection.

pgJDBC: место, где начинается практика

pgJDBC — это тот самый PostgreSQL JDBC Driver, который вы обязательно подключаете к приложению.

При вызове connection.prepareStatement(...), pgJDBC использует extended protocol, но не обязательно сразу создает named server-side prepared statement.

По умолчанию работает так:

• prepareThreshold = 5;

• preparedStatementCacheQueries = 256 на каждое соединение;

• preparedStatementCacheSizeMiB = 5 на каждое соединение;

• preferQueryMode = extended.

На пятом выполнении одного и того же PreparedStatement драйвер переключается на server-side prepared statement. Кэш живет на уровне connection, а не на уровне Java-объекта, поэтому приложение может создавать и закрывать PreparedStatement заново.

Настраивать эти параметры можно прямо в URL:

jdbc:postgresql://localhost:5432/app?prepareThreshold=5&preparedStatementCacheQueries=256

В Spring Boot с встроенным HikariCP удобнее передавать свойства драйверу через properties:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/app
    hikari:
      maximum-pool-size: 10
      data-source-properties:
        prepareThreshold: 5
        preparedStatementCacheQueries: 256
        preparedStatementCacheSizeMiB: 5

Обычно дефолты pgJDBC нормальные. prepareThreshold=-1 для скорости лучше не ставить: он форсирует server-side prepared statements и binary transfer для поддерживаемых типов. Иногда это хорошо, а иногда вы просто раньше получаете побочные эффекты prepared statements.

Кстати, побочные эффекты у чрезмерно большого количества server-side prepared statements очень неприятные:

Во-первых, это память. Server-prepared statements потребляют память и на клиенте, и на сервере, поэтому у драйвера есть лимиты:preparedStatementCacheQueries и preparedStatementCacheSizeMiB. Важно, что лимиты считаются на каждое физическое соединение. Если в HikariCP maximum-pool-size = 10, а у pgJDBC дефолтные 256 queries, то это уже до 2560 известных драйверу SQL-шаблонов. Server-prepared станет только часть из них, но порядок величины понятен.

Во-вторых, приложение может сгенерировать сотни разных подвидов запросов из-за dynamic SQL, разной длины IN (...), условных фильтров или прыгающих типов параметров — кэш будет постоянно вытеснять старые statements и готовить новые. Вместо экономии можно получить лишнюю работу.

В-третьих, prepared statement не гарантирует лучший план. PostgreSQL сам выбирает custom или generic plan, но если запросы сильно зависят от конкретных значений параметров, generic plan может оказаться хуже. Это не повод выключать prepared statements везде, но повод не крутить prepareThreshold=-1 просто “чтобы было быстрее”.

HikariCP тут не главный герой

Spring Boot выбирает HikariCP как connection pool, если он есть в classpath. И выше можно заметить, что через него даже предоставляются некоторые настройки. Но в реальности HikariCP не отвечает за prepared statements!

В README HikariCP есть раздел про statement cache: кэш на уровне pool’а вынужден быть per-connection. Если 250 популярных запросов умножить на 20 соединений, получится много cached statement объектов. HikariCP считает такой кэш анти-паттерном и оставляет задачу JDBC-драйверу.

HikariCP управляет физическими соединениями, но вот кэшированием заведует pgJDBC. PostgreSQL держит на своей стороне named prepared statements внутри своей сессии (то есть на каждый Connection). Сам Connection из пула сегодня ваш, через миллисекунду чужой, а завтра закрыт и заменен. Поэтому ручной кэш PreparedStatement в приложении почти всегда означает, что кэшируют не на том уровне.

Тип параметра — часть запроса

Для prepared statement важен не только сам SQL-запрос, но и типы bind-параметров.

pgJDBC приводит хороший пример. Есть таблица rooms, поле name varchar и индекс по name. Напишем примерно такой код и в принципе все будет хорошо:

var ps = connection.prepareStatement(
    "select id from rooms where name = ?"
);
ps.setString(1, "42");

Но если потом кто-то передаст ps.setInt(1, 42), то для базы это уже другой тип параметра и другая история с приведениями. Можно потерять индекс или заставить драйвер инвалидировать server-side statement. В документации pgJDBC этот кейс разобран в разделе про bind placeholder datatypes.

Практическое правило: для одного placeholder используйте один и тот же тип. Если колонка текстовая — null тоже должен быть текстовым:

if (value == null) {
    ps.setNull(1, Types.VARCHAR);
} else {
    ps.setString(1, value);
}

setObject() — это не кнопка “сделай правильно”. Если тип не очевиден: лучше передать target SQL type явно.

DDL, select * и ошибки

Prepared statements живут в сессии, а схема базы иногда меняется. Отсюда ошибка:

cached plan must not change result type

Классический сценарий воспроизведения:

select * from users;
alter table users add column timezone text;
select * from users;

С select * меняется shape результата. У prepared statement уже были метаданные колонок, после DDL они стали другими. pgJDBC описывает этот крайний случай и рекомендует не использовать select * (это помимо еще миллиона причин, которые в данной статье не затрагиваются).

Сейчас эта ошибка встречается редко. Изменения схемы обычно делает инструмент миграций (Liquibase, Flyway и др) через отдельное соединение. Они, как правило, встроены в приложение и срабатывают при выходе новой версии, а значит с большой долей вероятности будет рестарт и пересоздание соединений. Плюс нормальные ORM обычно все-таки не делают select *. Но забывать об этой опасности не стоит.

Spring Data JPA и Hibernate

Hibernate работает через JDBC. Обычный repository method:

Optional<User> findByEmail(String email);

в итоге превращается в SQL с bind-параметром.

То есть рецепт тот же:

• пишем запросы через bind values, а не через конкатенацию строк;

• кэширование и server-side prepare оставляем pgJDBC;

• параметры драйвера передаем через HikariCP или JDBC URL.

Но есть нюанс: SQL text должен совпадать. Разная длина IN (...), dynamic update или условные фильтры дают разные statements для pgJDBC. Не каждый ORM-запрос станет одним горячим server-side prepared statement.

Для других Java ORM все аналогично.

PgBouncer

Это уже часть для продвинутых. Когда используется внешний connection pool — появляются некоторые сложности. Рассмотрим их на примере PgBouncer с настройкой pool_mode = transaction. Освежить в памяти основы можно например здесь.

В transaction режиме с prepared statements исторически было больно. Транзакция приложения закончилась, server connection вернулся в пул, следующая транзакция того же приложения может попасть на совсем другое физическое соединение. А server-side prepared statement живет как раз на физическом соединении PostgreSQL.

Начиная с PgBouncer 1.21 появилась поддержка protocol-level named prepared statements в transaction pooling. В текущей документации за это отвечает max_prepared_statements. Если значение не ноль, PgBouncer отслеживает команды протокола, назначает внутренние имена вроде PGBOUNCER_123, подготавливает statement на нужном server connection по требованию и переиспользует одинаковые query strings между клиентами.

То есть давняя страшилка “PgBouncer не поддерживает prepared statements” уже не такая страшная. Но есть границы:

• это работает для protocol-level prepared statements, а не для SQL PREPARE / EXECUTE;

• но давайте будем честными, кто из нас последний раз писал в бизнес-приложении PREPARE или EXECUTE?

• после DDL можно поймать тот же cached plan must not change result type, и документация PgBouncer советует делать RECONNECT, чтобы заставить server connections подготовить statements заново;

• чем больше max_prepared_statements, тем больше память на PostgreSQL-соединениях и больше работы у PgBouncer.

В Java при PgBouncer 1.21+ и max_prepared_statements > 0 — оставляйте дефолтный prepareThreshold=5 или увеличивайте по необходимости.

Если PgBouncer старее 1.21 или между приложением и PostgreSQL стоит несовместимый прокси — лучше поставить prepareThreshold=0. Вы потеряете server-side prepare (кэширование планов и тд), но сохраните JDBC PreparedStatement как безопасную параметризацию с защитой от SQL-инъекций.

Короткая памятка

PreparedStatement в Java — это JDBC API для SQL с параметрами, а не обещание, что PostgreSQL обязательно держит server-side prepared statement. В pgJDBC server-side prepare включен по умолчанию настройкой prepareThreshold=5. Кэш живет “на connection”.

Hibernate, Spring Data JPA и jOOQ не отменяют pgJDBC. Но нужно работать по правилам: стабильный SQL text, стабильные типы параметров, аккуратность с DDL и понимание connection pool.

PgBouncer в transaction pooling умеет protocol-level prepared statements, но не SQL PREPARE. Для современных версий устанавливайте в PgBouncer max_prepared_statements = 200 и оставляйте prepareThreshold > 0 в приложении. Для старых или несовместимых прокси - устанавливайте prepareThreshold = 0.