Spring Data with PostgreSQL

Part 22 of 59

May 03, 2026 Abhay 6 min read

Spring Data with PostgreSQL

PostgreSQL offers powerful features beyond basic SQL — JSONB, arrays, full-text search, advisory locks. This article shows how to use them from Spring Boot and how to tune HikariCP for production.

Project Setup

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/orderdb
    username: app
    password: ${DB_PASSWORD}
    driver-class-name: org.postgresql.Driver
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: validate

HikariCP — Connection Pool Tuning

Spring Boot uses HikariCP by default — the fastest JDBC connection pool. Tune it for production:

spring:
  datasource:
    hikari:
      # Pool size: start with (core_count * 2) + effective_spindle_count
      maximum-pool-size: 20
      minimum-idle: 5

      # Connection lifecycle
      connection-timeout: 30000      # max wait for a connection (ms)
      idle-timeout: 600000           # close idle connections after 10 min
      max-lifetime: 1800000          # max connection age (30 min — before PG kills it)
      keepalive-time: 60000          # ping idle connections every 60s

      # Validation
      connection-test-query: SELECT 1
      validation-timeout: 5000

      # Pool name (shows in metrics/logs)
      pool-name: OrderServicePool

      # PostgreSQL-specific
      data-source-properties:
        ApplicationName: order-service          # shows in pg_stat_activity
        socketTimeout: 60                       # seconds
        ssl: true
        sslmode: require

Key insight: More connections ≠ more throughput. Database connections are expensive. PostgreSQL performs best with a modest pool. The optimal formula is approximately:

pool_size = number_of_cores * 2 + number_of_disks

For a 4-core machine with SSD: 4 * 2 + 1 = 9. For most apps, 10-20 is sufficient.

JSONB — Storing and Querying JSON

PostgreSQL’s jsonb type stores JSON in binary format — fast to query with indexes.

Storing JSONB with a String column

The simplest approach — store as String, convert manually:

@Entity
@Table(name = "orders")
public class Order {

    @Column(name = "metadata", columnDefinition = "jsonb")
    private String metadata;  // stored as JSONB in PG, as String in Java
}

-- Flyway migration
ALTER TABLE orders ADD COLUMN metadata JSONB;

Querying JSONB with native SQL

@Query(value = """
    SELECT * FROM orders
    WHERE metadata->>'channel' = :channel
    AND metadata->'tags' ? :tag
    AND (metadata->>'priority')::int > :minPriority
    """, nativeQuery = true)
List<Order> findByMetadata(
    @Param("channel") String channel,
    @Param("tag") String tag,
    @Param("minPriority") int minPriority
);

JSONB operators:

-> — get JSON element by key (returns JSON)
->> — get JSON element as text
? — does key exist?
@> — does JSON contain this value?
#>> — get element at path

Using a JSONB converter

@Converter
public class OrderMetadataConverter implements AttributeConverter<OrderMetadata, String> {

    private final ObjectMapper mapper = JsonMapper.builder().findAndAddModules().build();

    @Override
    public String convertToDatabaseColumn(OrderMetadata metadata) {
        try {
            return metadata == null ? null : mapper.writeValueAsString(metadata);
        } catch (JsonProcessingException e) {
            throw new IllegalStateException("Cannot serialize metadata", e);
        }
    }

    @Override
    public OrderMetadata convertToEntityAttribute(String json) {
        try {
            return json == null ? null : mapper.readValue(json, OrderMetadata.class);
        } catch (JsonProcessingException e) {
            throw new IllegalStateException("Cannot deserialize metadata", e);
        }
    }
}

public record OrderMetadata(
    String channel,
    String referralCode,
    List<String> tags,
    Map<String, String> customFields
) {}

// In entity
@Convert(converter = OrderMetadataConverter.class)
@Column(columnDefinition = "jsonb")
private OrderMetadata metadata;

Indexing JSONB

-- GIN index for full JSON containment queries (@>)
CREATE INDEX idx_orders_metadata_gin ON orders USING GIN (metadata);

-- Index a specific JSON key
CREATE INDEX idx_orders_channel ON orders ((metadata->>'channel'));

-- Index for existence checks (?)
CREATE INDEX idx_orders_tags_gin ON orders USING GIN ((metadata->'tags'));

PostgreSQL Arrays

-- Migration
ALTER TABLE products ADD COLUMN tags TEXT[];
ALTER TABLE orders ADD COLUMN applied_promo_codes TEXT[];

@Entity
public class Product {

    // String array stored in PostgreSQL TEXT[] column
    @Column(columnDefinition = "text[]")
    private String[] tags;
}

Query with native SQL:

@Query(value = """
    SELECT * FROM products
    WHERE tags @> ARRAY[:tag]::text[]
    """, nativeQuery = true)
List<Product> findByTag(@Param("tag") String tag);

@Query(value = """
    SELECT * FROM products
    WHERE tags && ARRAY[:tags]::text[]
    """, nativeQuery = true)
List<Product> findByAnyTag(@Param("tags") String[] tags);

Full-Text Search

PostgreSQL’s built-in full-text search is powerful and doesn’t need Elasticsearch for many use cases.

-- Migration: add tsvector column and index
ALTER TABLE products
    ADD COLUMN search_vector TSVECTOR
        GENERATED ALWAYS AS (
            to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
        ) STORED;

CREATE INDEX idx_products_fts ON products USING GIN (search_vector);

Query from Spring Data:

@Query(value = """
    SELECT *,
           ts_rank(search_vector, websearch_to_tsquery('english', :query)) AS rank
    FROM products
    WHERE search_vector @@ websearch_to_tsquery('english', :query)
    ORDER BY rank DESC
    LIMIT :limit
    """, nativeQuery = true)
List<Product> fullTextSearch(@Param("query") String query, @Param("limit") int limit);

The websearch_to_tsquery function handles user input safely — spaces become AND, quotes become phrases.

"spring boot" tutorial → 'spring' & 'boot' & 'tutorial'

Advisory Locks — Distributed Locking

PostgreSQL advisory locks give you application-level distributed locks — no Redis required for simple cases:

@Service
@RequiredArgsConstructor
public class InventoryService {

    private final JdbcTemplate jdbcTemplate;

    @Transactional
    public void reserveInventory(UUID productId, int quantity) {
        // Acquire lock based on product ID hash
        long lockKey = Math.abs(productId.hashCode());

        Boolean locked = jdbcTemplate.queryForObject(
            "SELECT pg_try_advisory_xact_lock(?)",
            Boolean.class,
            lockKey
        );

        if (Boolean.FALSE.equals(locked)) {
            throw new ConcurrentModificationException(
                "Another request is modifying inventory for product " + productId
            );
        }

        // Lock held for duration of transaction
        // Any other request trying to lock same key waits (or fails with try_advisory)
        Inventory inv = inventoryRepository.findByProductId(productId).orElseThrow();
        if (inv.getAvailable() < quantity) {
            throw new InsufficientInventoryException(productId, quantity, inv.getAvailable());
        }
        inv.setAvailable(inv.getAvailable() - quantity);
        inv.setReserved(inv.getReserved() + quantity);
    }
}

pg_advisory_xact_lock — lock for the transaction duration (auto-released on commit/rollback)
pg_advisory_lock — session-level lock (must be manually released)
pg_try_advisory_xact_lock — non-blocking: returns false instead of waiting

Read Replica Routing

Route read-only transactions to a replica automatically with Spring’s AbstractRoutingDataSource:

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.replica")
    public DataSource replicaDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource routingDataSource() {
        Map<Object, Object> dataSources = Map.of(
            "primary", primaryDataSource(),
            "replica", replicaDataSource()
        );

        AbstractRoutingDataSource routing = new AbstractRoutingDataSource() {
            @Override
            protected Object determineCurrentLookupKey() {
                return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
                    ? "replica"
                    : "primary";
            }
        };
        routing.setTargetDataSources(dataSources);
        routing.setDefaultTargetDataSource(primaryDataSource());
        return routing;
    }
}

spring:
  datasource:
    primary:
      url: jdbc:postgresql://primary-db:5432/orders
      username: app
      password: ${DB_PRIMARY_PASSWORD}
    replica:
      url: jdbc:postgresql://replica-db:5432/orders
      username: app_readonly
      password: ${DB_REPLICA_PASSWORD}

Now @Transactional(readOnly = true) methods automatically hit the replica.

Running PostgreSQL in Development with Docker

# Start PostgreSQL
docker run -d \
  --name postgres-dev \
  -e POSTGRES_DB=orderdb \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  postgres:16

# Connect
psql -h localhost -U app -d orderdb

Or with Docker Compose:

# docker-compose.yml
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: orderdb
      POSTGRES_USER: app
      POSTGRES_PASSWORD: secret
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./db/init:/docker-entrypoint-initdb.d  # auto-run SQL on first start

volumes:
  postgres-data:

Testing with Testcontainers

@SpringBootTest
@Testcontainers
class OrderRepositoryIntegrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired
    private OrderRepository orderRepository;

    @Test
    @Transactional
    void shouldSaveAndRetrieveOrder() {
        Order order = new Order();
        order.setCustomerId(UUID.randomUUID());
        order.setStatus(OrderStatus.PENDING);
        order.setTotalAmount(BigDecimal.valueOf(99.99));

        Order saved = orderRepository.save(order);

        assertThat(saved.getId()).isNotNull();
        assertThat(orderRepository.findById(saved.getId())).isPresent();
    }

    @Test
    void shouldSearchByMetadata() {
        // Tests JSONB queries against a real PostgreSQL instance
        Order order = new Order();
        order.setMetadata(new OrderMetadata("web", "REF123", List.of("vip"), Map.of()));
        orderRepository.save(order);

        List<Order> results = orderRepository.findByChannel("web");
        assertThat(results).hasSize(1);
    }
}

Testcontainers starts a real PostgreSQL for each test run — no mocking, no H2 compatibility issues.

What You’ve Learned

HikariCP is Spring Boot’s default pool — tune maximumPoolSize based on CPU count, not blindly set it high
jsonb columns store structured data without schema changes; use GIN indexes for fast queries
PostgreSQL arrays with @> and && operators for set operations
Full-text search with tsvector generated columns and websearch_to_tsquery — no Elasticsearch needed for basic search
Advisory locks for distributed coordination using PostgreSQL’s own lock mechanism
Route readOnly = true transactions to read replicas with AbstractRoutingDataSource
Always test against real PostgreSQL with Testcontainers — H2 doesn’t support jsonb, arrays, or FTS

This completes Part 3: Data Access with Spring Data JPA. Next up: Part 4 — Spring Security.

Next: Article 23 — Spring Security Fundamentals — filter chain, authentication, and authorization from the ground up.