PostgreSQL with Spring Boot — Production Configuration End-to-End
Fast recall
Quick revision
Core answer
@JdbcTypeCode(SqlTypes.ARRAY), and native SQL for operators JPQL cannot express (@>, @@, <->). When PgBouncer sits in front, transaction pooling requires attention to prepared statements and forbids session features like LISTEN/NOTIFY — use transaction-scoped advisory locks (pg_advisory_xact_lock) or a session pool for those. YAML config, Flyway migrations, and Actuator/Micrometer metrics complete the stack.Whether you can assemble a correct, complete Spring Boot + PostgreSQL stack — connection pool sizing, dialect, JSONB mapping, native arrays, transactional boundaries, migration tooling, and the subtle PgBouncer-transaction-pooling interactions with Hibernate.
Copying the Hibernate `MySQLDialect` habit of leaving `ddl-auto: update` on, ignoring `jdbc.time_zone`, or enabling PgBouncer transaction pooling without disabling Hibernate's server-side prepared statement cache, then debugging `prepared statement "S_1" does not exist` in production.
Show `@JdbcTypeCode(SqlTypes.JSON)` for JSONB, `@Type(JsonType.class)` from Hypersistence Utilities for typed POJOs, `SqlTypes.ARRAY` for native `text[]`/`int[]`, `pg_advisory_xact_lock` as the transaction-safe alternative to `LISTEN/NOTIFY`, and a production YAML that wires HikariCP, Flyway, Actuator, OpenTelemetry, and PgBouncer together cleanly.
Interview-ready guide
Interview answer
Running Spring Boot on PostgreSQL in production is about getting five layers right: HikariCP, Hibernate, Flyway, PgBouncer when present, and observability.
HikariCP
- Size per pod to roughly two times the CPU cores; the aggregate across pods must fit under PgBouncer's
default_pool_sizeand under PostgreSQL'smax_connections - Use
connection-timeoutaround 3 seconds so pool saturation surfaces as errors, not hangs - Keep
max-lifetimebelow PgBouncer'sserver_lifetimeand below any load-balancer idle timeout
Hibernate
PostgreSQLDialectandjdbc.time_zone=UTC- Map JSONB with
@JdbcTypeCode(SqlTypes.JSON)for loose shapes or@Type(JsonType.class)from Hypersistence Utilities for typed POJOs - Map native arrays with
@JdbcTypeCode(SqlTypes.ARRAY)andcolumnDefinition="text[]"orint[] - Use native queries for operators JPQL cannot express —
@>,@@,<->, array overlap open-in-view: false,ddl-auto: validate, batch inserts withreWriteBatchedInserts=true
Flyway
- Schema changes are Flyway migrations, never Hibernate
- Transactional DDL means a failed migration rolls back cleanly
CREATE INDEX CONCURRENTLYcannot run inside a transaction, so it goes in its own migration
PgBouncer transaction pooling
- Transaction pooling is the default choice for stateless services, but it breaks session-scoped features
- Server-side prepared statements need PgBouncer 1.21+ with
max_prepared_statements, otherwise disable them in the JDBC URL LISTEN/NOTIFY, session-levelSET, session advisory locks all misbehave — useSET LOCAL,pg_advisory_xact_lock, and switch to outbox + polling or Debezium for cross-instance events
Observability
- Micrometer for HikariCP metrics, OpenTelemetry for JDBC spans,
pg_stat_statementsfor server-side hot queries, Hypersistence Utilities' statement-count validator in tests to prevent N+1 regressions
PostgreSQL with Spring Boot — Production Configuration End-to-End
Direct Answer
A production Spring Boot service on PostgreSQL combines HikariCP sized to (CPU cores × 2) per pod, the PostgreSQLDialect, Hibernate 6 SqlTypes.JSON or @Type(JsonType.class) for JSONB, native-array mapping via @JdbcTypeCode(SqlTypes.ARRAY), and native SQL for operators JPQL cannot express (@>, @@, <->). When PgBouncer sits in front, transaction pooling requires attention to prepared statements and forbids session features like LISTEN/NOTIFY — use transaction-scoped advisory locks (pg_advisory_xact_lock) or a session pool for those. YAML config, Flyway migrations, and Actuator/Micrometer metrics complete the stack.
Code Example
# src/main/resources/application.yml
spring:
application:
name: orders-api
datasource:
url: jdbc:postgresql://pgbouncer.internal:6432/orders?ApplicationName=${spring.application.name}
username: orders_api
password: ${DB_PASSWORD}
hikari:
pool-name: orders-hikari
# Target ~2x CPU cores per pod; aggregate across pods must fit under
# PgBouncer default_pool_size (and PostgreSQL max_connections)
maximum-pool-size: 10
minimum-idle: 2
connection-timeout: 3000 # ms
validation-timeout: 2000
idle-timeout: 60000
max-lifetime: 900000 # 15 min (ahead of PgBouncer server_lifetime)
leak-detection-threshold: 20000
# ---- PgBouncer transaction-pooling compatibility ----
# PgBouncer < 1.21: disable server-side prepared statements
# PgBouncer >= 1.21 with max_prepared_statements > 0: leave enabled
data-source-properties:
prepareThreshold: 0
preparedStatementCacheQueries: 0
reWriteBatchedInserts: true # batched INSERT → COPY-like rewrite
ApplicationName: ${spring.application.name}
stringtype: unspecified # Hibernate JSON handling compat
tcpKeepAlive: true
jpa:
open-in-view: false # always false in production
hibernate:
ddl-auto: validate # NEVER 'update' in production
properties:
hibernate:
dialect: org.hibernate.dialect.PostgreSQLDialect
jdbc:
time_zone: UTC
batch_size: 50
order_inserts: true
order_updates: true
lob.non_contextual_creation: true
order_updates: true
generate_statistics: false
query:
in_clause_parameter_padding: true
fail_on_pagination_over_collection_fetch: true
plan_cache_max_size: 2048
flyway:
enabled: true
locations: classpath:db/migration
baseline-on-migrate: false
validate-on-migrate: true
out-of-order: false
server:
shutdown: graceful
tomcat:
connection-timeout: 5s
management:
endpoints:
web:
exposure:
include: health, info, metrics, prometheus
endpoint:
health:
probes:
enabled: true
show-details: when_authorized
metrics:
tags:
service: ${spring.application.name}
distribution:
percentiles-histogram:
http.server.requests: true
logging:
level:
org.hibernate.SQL: info
org.hibernate.orm.jdbc.bind: info
com.zaxxer.hikari: info
Common Mistakes & Pitfalls
Copying the Hibernate MySQLDialect habit of leaving ddl-auto: update on, ignoring jdbc.time_zone, or enabling PgBouncer transaction pooling without disabling Hibernate's server-side prepared statement cache, then debugging prepared statement "S_1" does not exist in production.
Check your understanding
Answer these without looking back at the article.
- How do you map a PostgreSQL `jsonb` column in Hibernate 6, and when would you pick a typed POJO over `Map<String,Object>`?
- What changes in Spring Boot when you put PgBouncer in transaction pooling mode in front of PostgreSQL?
- How do you handle `LISTEN`/`NOTIFY` reliably under a transaction-pooled PgBouncer setup?
- How do you safely run `CREATE INDEX CONCURRENTLY` from a Flyway migration?
Related interview prep categories
1Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.