A developer’s guide to schema migrations, DTOs and transaction scoping.

A developer’s guide to schema migrations, DTOs, and transaction scoping.

It rarely starts with a crash. It starts with a slightly slower endpoint. Then a spike in traffic. Then suddenly:

  • Your connection pool is exhausted
  • Queries multiply unexpectedly
  • Memory usage spikes
  • And debugging becomes guesswork

These are not random failures — they are symptoms of missing Day Zero decisions.

Let’s break down a few practices that prevent them.

⚡ TL;DR (Quick Recap)

  • Use Flyway or Liquibase with ddl-auto=validate
  • Default to LAZY loading + explicit fetch strategies or projections
  • Paginate every query with enforced limits
  • Use @Transactional(readOnly = true) by default
  • Tune HikariCP intentionally (don’t trust defaults)

Own Your Schema — Migrate, Don’t Auto-Generate

Relying on ddl-auto=update is one of the fastest ways to lose control of your database.

It works — until it doesn’t:

  • No version history
  • No rollback strategy
  • No review process
  • Silent drift between environments

The Better Approach

Use migration tools like Flyway or Liquibase and treat schema as code.

spring:
  jpa:
    hibernate:
      ddl-auto: validate
  flyway:
    enabled: true

Each schema change becomes:

  • Versioned (V1__init.sql)
  • Reviewed in pull requests
  • Reproducible across environments

Why It Matters

Without this:

  • Production bugs become untraceable
  • Deployments become risky
  • Teams lose confidence in data integrity

With it:

  • Schema evolution becomes predictable
  • CI/CD pipelines stay safe
  • Rollbacks are possible

Fetch Intentionally — Lazy by Default, Explicit by Design

The default fetch strategy is one of the most underestimated performance risks. Eager loading looks harmless — but it silently multiplies queries.

Prefer LAZY where possible, and override defaults explicitly when needed.

@ManyToOne(fetch = FetchType.LAZY)
private Customer customer;

Then fetch intentionally:

@Query("""
SELECT DISTINCT o FROM Order o
JOIN FETCH o.customer
LEFT JOIN FETCH o.items
WHERE o.id = :id
""")
Optional<Order> findDetailedOrder(Long id);

Or Better: Use Projections

For most reads, entities are overkill.

public record OrderSummary(Long id, String customerName) {}

// Repository interface — Spring Data projects automatically when return type matches
public interface OrderRepository extends JpaRepository<Order, Long> {
    List<OrderSummary> findByCustomerId(Long customerId);
    // or with JPQL:
    @Query("SELECT new com.example.dto.OrderSummary(o.id, o.customer.name) FROM Order o WHERE o.customer.id = :customerId")
    List<OrderSummary> findSummariesByCustomerId(Long customerId);
}

Why It Matters

Without control:

  • You get N+1 query explosions
  • Performance degrades linearly with data size

With intentional fetching:

  • Queries stay predictable
  • Performance remains stable under load

Paginate Everything — No Exceptions

Returning an unbounded list is a hidden time bomb. Paginate all externally exposed collection endpoints unless the dataset is strictly bounded and small.

It works — until:

  • Data grows
  • A client requests too much
  • Memory collapses

Every query must be paginated.

Page<Product> findByCategory(String category, Pageable pageable);

And enforce limits:

var safeSize = Math.min(size, 100);
PageRequest.of(page, safeSize);

Page vs Slice

  • Page<T> → includes total count (extra query)
  • Slice<T> → faster, no count (ideal for infinite scroll)

Why It Matters

Without pagination:

  • OOM crashes become inevitable
  • DB queries block connections

With pagination:

  • Load is controlled
  • System remains responsive

Transaction Management — Keep It Small and Explicit

Transactions are not just about correctness — they are about resource control.

Every active transaction:

  • Holds a DB connection
  • Consumes memory
  • Blocks concurrency

Default to read-only, and keep transactions short. Setting readOnly = true tells Hibernate to disable dirty checking on entities. This reduces memory overhead because Hibernate doesn't need to keep "snapshots" of entities to check for changes at the end of the transaction.

@Service
@Transactional(readOnly = true)
public class OrderQueryService {
}

Override for writes:

@Transactional  // defaults to readOnly = false
public void updateOrder(...) { }

OSIV Elephant in the Room

OSIV keeps the persistence context open across the request, which can trigger additional queries during serialization and prolong connection usage.

Use:

spring:
  jpa:
    open-in-view: false

Anti-Pattern to Avoid

Long transactions that include:

  • External API calls
  • Complex processing
  • Blocking operations

Why It Matters

Without control:

  • Connection pool exhaustion
  • Increased latency

With proper scoping:

  • Higher throughput
  • Better scalability

Configure HikariCP Like You Mean It

HikariCP is fast — but only if configured correctly.

Default values are safe but rarely optimal for high-throughput systems. Size the pool deliberately.

# Rule of thumb: pool size = (core_count * 2) + effective_spindle_count
# For a 4-core app server talking to a local Postgres: ~10 is a reasonable start
# Ref: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
spring:
  datasource:
    hikari:
      maximum-pool-size: 10        # tune based on DB capacity, not app server cores alone
      minimum-idle: 5              # set equal to maximum-pool-size for stable throughput workloads
      connection-timeout: 30000    # 30s — fail fast if pool is starved
      max-lifetime: 1800000        # 30min — must be less than DB's wait_timeout
      leak-detection-threshold: 60000  # flag connections open > 60s (good for dev; tune for prod)

Key Insights

  • Too small → threads wait, latency spikes
  • Too large → DB overwhelmed
  • Wrong lifetime → broken connections

Observability Matters

Track:

  • Active connections
  • Pending requests
  • Pool saturation

Why It Matters

Without tuning:

  • Performance becomes unpredictable

With tuning:

  • System behaves consistently under load

Final Takeaways

The persistence layer is not just infrastructure — it is your system’s backbone.

These practices work because they address the real failure modes:

  • Schema drift
  • Query inefficiency
  • Resource exhaustion
  • Hidden performance bottlenecks

You don’t need more tools. You need better defaults.

Make these decisions on Day Zero — and your system will scale with confidence instead of surprises.

You can find example of code on GitHub.

Originally posted on marconak-matej.medium.com.