Experiment Summary

This page documents a performance experiment comparing Spring Data JPA and JdbcTemplate on PostgreSQL with a dataset of 10 million rows.

👉 Full narrative and analysis:
Read the Medium article

Test Environment

• Java 25
• Spring Boot 4.0.2
• PostgreSQL 15
• HikariCP (default)
• Docker (local environment)
• Fixed RPS load test

Dataset

• Table: transactions
• Rows: 10,000,000
• Indexes:
  • (created_at DESC)
  • (status, created_at DESC)

Sample Data Generation Script (PostgreSQL)

Table Definition

CREATE TABLE transactions (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

This schema intentionally mirrors a typical production table:

• Time-based data
• Status filtering
• Read-heavy access patterns

Generate 10 Million Rows

This approach uses generate_series, which is far faster and more reliable than inserting rows from an application loop.

INSERT INTO transactions (user_id, status, amount, created_at)
SELECT
  (random() * 100000)::BIGINT AS user_id,
  CASE
    WHEN random() < 0.33 THEN 'SUCCESS'
    WHEN random() < 0.66 THEN 'FAILED'
    ELSE 'PENDING'
  END AS status,
  ROUND((random() * 10000)::numeric, 2) AS amount,
  NOW() - (random() * INTERVAL '365 days') AS created_at
FROM generate_series(1, 10000000);

Notes

• Data distribution is intentionally realistic
• created_at follows a time-series pattern
• Status values are evenly spread
• Insert time (local SSD):
  • ~2–5 minutes (native PostgreSQL)
  • ~5–8 minutes (Docker volume)

Indexes (Create After Insert)

Always create indexes after loading data.

CREATE INDEX idx_transactions_created_at
ON transactions (created_at DESC);

CREATE INDEX idx_transactions_status_created_at
ON transactions (status, created_at DESC);

Query Pattern

SELECT ...
FROM transactions
WHERE status = ?
ORDER BY created_at DESC
LIMIT ? OFFSET ?

This matches common pagination + filtering queries.

Update Planner Statistics

This step is mandatory for accurate performance testing.

ANALYZE transactions;

Without this, PostgreSQL may select inefficient query plans, skewing your results.

Example Query Used in the Experiment

This is the query pattern tested in both JPA and JDBC:

SELECT id, user_id, status, amount, created_at
FROM transactions
WHERE status = 'SUCCESS'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

This reflects:

• Dashboard views
• Admin panels
• Reporting pages

Why This Dataset Works Well for Performance Testing

• Large enough to expose hidden ORM costs
• Still small enough to run locally
• Reproducible
• No artificial optimizations

This setup highlights application-side overhead, not database weakness.

Optional: Cleanup Script

DROP TABLE transactions;

JPA Entity + Repository

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Table(name = "transactions",
       indexes = {
           @Index(name = "idx_created_at", columnList = "created_at"),
           @Index(name = "idx_status_created_at", columnList = "status, created_at")
       })
public class TransactionEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_id", nullable = false)
    private Long userId;

    @Column(nullable = false, length = 20)
    private String status;

    @Column(nullable = false, precision = 12, scale = 2)
    private BigDecimal amount;

    @Column(name = "created_at", nullable = false)
    private LocalDateTime createdAt;

    protected TransactionEntity() {
        // JPA only
    }

    // getters only (read-only use case)
    public Long getId() { return id; }
    public Long getUserId() { return userId; }
    public String getStatus() { return status; }
    public BigDecimal getAmount() { return amount; }
    public LocalDateTime getCreatedAt() { return createdAt; }
}

Why this matters

• Full entity mapping
• Managed persistence context
• Dirty checking enabled (even for reads)
• Object hydration cost is real

This is exactly how JPA is used in most production systems.

Spring Data JPA Repository

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface TransactionRepository
        extends JpaRepository<TransactionEntity, Long> {

    Page<TransactionEntity> findByStatusOrderByCreatedAtDesc(
            String status,
            Pageable pageable
    );
}

JPA Service Method (Used in the Test)

import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

@Service
public class JpaTransactionService {

    private final TransactionRepository repository;

    public JpaTransactionService(TransactionRepository repository) {
        this.repository = repository;
    }

    public void fetchPage(String status, int page, int size) {
        Pageable pageable = PageRequest.of(page, size);
        repository.findByStatusOrderByCreatedAtDesc(status, pageable);
    }
}

⚠️ Important for fairness

• No projections
• No read-only hints
• No native queries
• Default behavior only

JdbcTemplate Implementation (Comparison Baseline)

Row Mapper

import org.springframework.jdbc.core.RowMapper;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;

public class TransactionRowMapper implements RowMapper<TransactionDto> {

    @Override
    public TransactionDto mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new TransactionDto(
                rs.getLong("id"),
                rs.getLong("user_id"),
                rs.getString("status"),
                rs.getBigDecimal("amount"),
                rs.getTimestamp("created_at").toLocalDateTime()
        );
    }
}

DTO (Not an Entity)

import java.math.BigDecimal;
import java.time.LocalDateTime;

public record TransactionDto(
        Long id,
        Long userId,
        String status,
        BigDecimal amount,
        LocalDateTime createdAt
) {}

Key difference:

• No persistence context
• No lifecycle tracking
• No dirty checking
• No proxies

JdbcTemplate Service

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class JdbcTransactionService {

    private final JdbcTemplate jdbcTemplate;
    private final TransactionRowMapper rowMapper = new TransactionRowMapper();

    public JdbcTransactionService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<TransactionDto> fetchPage(
            String status,
            int limit,
            int offset
    ) {
        String sql = """
            SELECT id, user_id, status, amount, created_at
            FROM transactions
            WHERE status = ?
            ORDER BY created_at DESC
            LIMIT ? OFFSET ?
            """;

        return jdbcTemplate.query(
                sql,
                rowMapper,
                status,
                limit,
                offset
        );
    }
}

REST Controller for Load Testing (JPA vs JDBC)

Controller

import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/load-test")
public class LoadTestController {

    private final JpaTransactionService jpaService;
    private final JdbcTransactionService jdbcService;

    public LoadTestController(
            JpaTransactionService jpaService,
            JdbcTransactionService jdbcService
    ) {
        this.jpaService = jpaService;
        this.jdbcService = jdbcService;
    }

    /**
     * JPA endpoint
     *
     * Example:
     * GET /load-test/jpa?status=SUCCESS&page=100&size=50
     */
    @GetMapping("/jpa")
    public void jpaTest(
            @RequestParam String status,
            @RequestParam int page,
            @RequestParam int size
    ) {
        jpaService.fetchPage(status, page, size);
    }

    /**
     * JDBC endpoint
     *
     * Example:
     * GET /load-test/jdbc?status=SUCCESS&limit=50&offset=5000
     */
    @GetMapping("/jdbc")
    public void jdbcTest(
            @RequestParam String status,
            @RequestParam int limit,
            @RequestParam int offset
    ) {
        jdbcService.fetchPage(status, limit, offset);
    }
}

JMeter Configuration (Recommended)

Thread Group

• Threads: 10–200 (start small)
• Ramp-up: 30–60 sec
• Loop count: Forever (with test duration)
• Durations: 300-600 sec
• Startup delay: 30 sec

Result Summary

Why the Difference Is So Huge (Not 2×, But 1000×)

The gap isn’t about SQL speed.

It’s about where the work happens.

• JDBC pays once per query
• JPA pays per row, per request, per transaction

For each row, JPA:

• Creates entity objects
• Tracks them in the persistence context
• Performs dirty checking
• Increases heap usage and GC pressure

At a small scale, this cost is invisible.
With millions of rows and concurrent traffic, it explodes.

That’s why JDBC stays in milliseconds
while JPA drifts into seconds — or worse.

How to Keep JPA — Without Falling Into the Trap

JPA isn’t slow because the database is slow.
It’s slow because it does a lot of hidden work per row.

To keep JPA and avoid performance collapse:

• Use entities for writes, not for heavy reads
  Reads should return DTOs or projections.
• Mark read paths as @Transactional(readOnly = true)
  This disables unnecessary dirty checking.
• Clear the persistence context for large reads
  Prevents memory buildup and GC pressure.
• Avoid OFFSET pagination on large tables
  Switch to keyset (cursor-based) pagination.
• Use native queries or JDBC selectively
  Especially for reporting and dashboards.

JPA should model the domain, not serve every query.

Key Observations

• Pagination cost increased with the dataset size
• Database CPU remained stable
• Application-side overhead dominated latency
• JPA object lifecycle added measurable cost

Reproducibility

• SQL data generation script
• Load test configuration
• Application configuration

Github

Reference

This experiment supports the analysis described in the Medium article and is intended as a technical reference, not a tutorial.

FAQ

Is JPA always slower than JDBC?

No. JPA is often fine for transactional workloads and moderate result sizes. It becomes expensive when large reads create many managed entities or keep too much state in the persistence context.

When should backend teams switch from JPA to JDBC?

Use JDBC or projection-based reads when the endpoint is read-heavy, returns many rows, or needs predictable memory use and lower mapping overhead.

What should be measured before blaming PostgreSQL?

Measure database execution time, rows returned, JDBC fetch time, entity hydration time, GC pressure, and response serialization separately.