Phase 8Intermediate9 of 18 in Database Design

Query Optimization and Application Access

Finding and fixing slow queries, keyset pagination, connection pooling with HikariCP and PgBouncer, JPA/Hibernate mapping and the N+1 problem, and Spring transaction propagation and pitfalls.

Most production database problems are caused by the application: unbounded queries, N+1 loops, oversized pools, and transactions that stay open across network calls. This phase fixes them at the source.

0/5 · 0%

5 topics ~41 min 8 code blocks & diagrams

Start with the first topic

8.1

Finding and Fixing Slow Queries

Find slow queries with pg_stat_statements (by total time, not just mean), reproduce them with EXPLAIN ANALYZE, and fix the usual causes: missing or wrong index, functions or implicit casts on indexed columns, SELECT *, unbounded result sets, large OFFSETs, N+1 query loops, and poor estimates.

8 min 1 code practice

8.2

Pagination: OFFSET vs Keyset (Cursor)

OFFSET pagination reads and discards all skipped rows, so page 10,000 is slow, and rows shift between pages under concurrent inserts. Keyset (cursor) pagination remembers the last row's sort key and continues WHERE (created_at, id) < (?, ?): constant cost per page and stable results, at the cost of no random page jumps.

8 min 2 code practice

8.3

Connection Pooling: HikariCP, PgBouncer and Pool Sizing

Database connections are expensive (a PostgreSQL connection is a process with several MB of memory), so applications reuse them through a pool. Size pools small (throughput peaks at a few connections per CPU core), set connection and statement timeouts, detect leaks, and put PgBouncer in front when many application instances would otherwise exceed max_connections.

8 min 2 code practice

8.4

JPA and Hibernate: Mapping, Fetching and the N+1 Problem

JPA maps entities to tables and relationships to foreign keys, but default fetching can silently issue one query per row (N+1). Make associations LAZY, load what each use case needs with fetch joins, entity graphs or batch fetching, use DTO projections for reads, and apply @Version for optimistic locking.

9 min 2 code practice

8.5

Spring Transactions: Propagation, Read-Only, Rollback and Pitfalls

@Transactional wraps a method in a database transaction via a proxy. Propagation decides whether to join an existing transaction (REQUIRED), suspend it and start a new one (REQUIRES_NEW), or use a savepoint (NESTED). Rollback happens on unchecked exceptions by default. Self-invocation bypasses the proxy, and long transactions hold connections and locks.