Spring BootPostgreSQL Integration
Spring Boot

PostgreSQL Integration

PostgreSQL is the preferred relational database for Spring Boot applications requiring advanced SQL features — window functions, CTEs, JSONB, full-text search, array types, and advisory locks. Spring Boot auto-configures everything from a single datasource URL. This entry covers setup, PostgreSQL-specific JPA mappings, advanced query features, and testing with Testcontainers.

Dependencies and Setup

The PostgreSQL JDBC driver and spring-boot-starter-data-jpa are all that is needed. Hibernate auto-detects the PostgreSQL dialect from Spring Boot 3 onwards.
XML
<!-- pom.xml: -->
<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>

# ── application.yml: ──────────────────────────────────────────────────
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: postgres
    password: ${DB_PASSWORD}
    driver-class-name: org.postgresql.Driver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 20000
      idle-timeout: 300000
      max-lifetime: 600000        # PostgreSQL default idle timeout is 10min
      keepalive-time: 60000
      pool-name: myapp-pg-pool

  jpa:
    hibernate:
      ddl-auto: validate
    open-in-view: false
    show-sql: false
    properties:
      hibernate:
        # Hibernate 6 auto-detects PostgreSQLDialect — explicit only if needed:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true
        jdbc:
          batch_size: 25
          batch_versioned_data: true
        default_batch_fetch_size: 25

  # ── Flyway for schema management: ────────────────────────────────────
  flyway:
    enabled: true
    locations: classpath:db/migration
    validate-on-migrate: true

PostgreSQL-Specific JPA Mappings

PostgreSQL supports JSONB, arrays, UUID primary keys, enums, and SERIAL/SEQUENCE generation. These map to JPA entities with specific column definitions and converters.
Java
@Entity
@Table(name = "products",
       indexes = {
           @Index(name = "idx_products_category", columnList = "category"),
           @Index(name = "idx_products_tags", columnList = "tags")
       })
public class Product {

    // PostgreSQL UUID primary key — no AUTO_INCREMENT, no sequence needed:
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(columnDefinition = "uuid", updatable = false)
    private UUID id;

    @Column(nullable = false, length = 200)
    private String name;

    // PostgreSQL TEXT — no length limit:
    @Column(columnDefinition = "TEXT")
    private String description;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;

    // PostgreSQL JSONB — binary JSON with indexing support:
    @Column(columnDefinition = "jsonb")
    @Convert(converter = JsonbConverter.class)
    private Map<String, Object> metadata;

    // PostgreSQL array — mapped via custom type or converter:
    @Column(columnDefinition = "text[]")
    @Convert(converter = StringArrayConverter.class)
    private List<String> tags;

    // PostgreSQL native enum — requires CREATE TYPE in migration:
    @Column(columnDefinition = "product_status")
    @Enumerated(EnumType.STRING)
    private ProductStatus status;

    // TIMESTAMPTZ — timestamp with time zone:
    @Column(nullable = false, updatable = false,
            columnDefinition = "TIMESTAMPTZ")
    private OffsetDateTime createdAt;
}

// ── JSONB converter: ──────────────────────────────────────────────────
@Converter
public class JsonbConverter
        implements AttributeConverter<Map<String, Object>, String> {

    private static final ObjectMapper mapper = new ObjectMapper();

    @Override
    public String convertToDatabaseColumn(Map<String, Object> map) {
        try { return map == null ? null : mapper.writeValueAsString(map); }
        catch (JsonProcessingException e) { throw new RuntimeException(e); }
    }

    @Override
    public Map<String, Object> convertToEntityAttribute(String json) {
        try { return json == null ? null : mapper.readValue(json, Map.class); }
        catch (JsonProcessingException e) { throw new RuntimeException(e); }
    }
}

// ── Flyway migration for PostgreSQL-specific DDL: ─────────────────────
-- V1__create_products_table.sql
CREATE TYPE product_status AS ENUM ('ACTIVE', 'INACTIVE', 'DISCONTINUED');

CREATE TABLE products (
    id          UUID            NOT NULL DEFAULT gen_random_uuid(),
    name        VARCHAR(200)    NOT NULL,
    description TEXT,
    price       NUMERIC(10,2)   NOT NULL,
    metadata    JSONB,
    tags        TEXT[],
    status      product_status  NOT NULL DEFAULT 'ACTIVE',
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
);

CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_products_name_fts ON products
    USING GIN (to_tsvector('english', name));

PostgreSQL-Specific Native Queries

PostgreSQL's advanced SQL features — window functions, CTEs, JSONB operators, full-text search, UPSERT, and array operators — require native queries. These are the features that most justify choosing PostgreSQL over other databases.
Java
@Repository
public interface ProductRepository extends JpaRepository<Product, UUID> {

    // ── PostgreSQL UPSERT (ON CONFLICT DO UPDATE): ────────────────────
    @Modifying
    @Transactional
    @Query(value = """
            INSERT INTO products (id, name, price, status)
            VALUES (:id, :name, :price, :status)
            ON CONFLICT (id) DO UPDATE
                SET name   = EXCLUDED.name,
                    price  = EXCLUDED.price,
                    status = EXCLUDED.status,
                    updated_at = NOW()
            """,
           nativeQuery = true)
    void upsert(@Param("id") UUID id,
                @Param("name") String name,
                @Param("price") BigDecimal price,
                @Param("status") String status);

    // ── PostgreSQL full-text search: ──────────────────────────────────
    @Query(value = """
            SELECT p.* FROM products p
            WHERE to_tsvector('english', p.name || ' ' || COALESCE(p.description, ''))
                  @@ plainto_tsquery('english', :query)
            ORDER BY ts_rank(
                to_tsvector('english', p.name || ' ' || COALESCE(p.description, '')),
                plainto_tsquery('english', :query)
            ) DESC
            """,
           nativeQuery = true)
    List<Product> fullTextSearch(@Param("query") String query);

    // ── JSONB operators: ──────────────────────────────────────────────
    @Query(value = "SELECT * FROM products WHERE metadata ->> 'brand' = :brand",
           nativeQuery = true)
    List<Product> findByBrand(@Param("brand") String brand);

    @Query(value = "SELECT * FROM products WHERE metadata @> :filter::jsonb",
           nativeQuery = true)
    List<Product> findByMetadataContaining(@Param("filter") String jsonFilter);

    // ── Array operators: ──────────────────────────────────────────────
    @Query(value = "SELECT * FROM products WHERE :tag = ANY(tags)",
           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") List<String> tags);

    // ── Window functions: ─────────────────────────────────────────────
    @Query(value = """
            SELECT
                p.*,
                AVG(p.price) OVER (PARTITION BY p.category) AS category_avg_price,
                RANK()       OVER (PARTITION BY p.category ORDER BY p.price DESC)
                    AS price_rank_in_category,
                LAG(p.price) OVER (ORDER BY p.created_at) AS prev_price
            FROM products p
            WHERE p.status = 'ACTIVE'
            """,
           nativeQuery = true)
    List<Object[]> findWithWindowStats();

    // ── Recursive CTE: ────────────────────────────────────────────────
    @Query(value = """
            WITH RECURSIVE category_tree AS (
                SELECT id, name, parent_id, 0 AS depth
                FROM categories
                WHERE parent_id IS NULL
                UNION ALL
                SELECT c.id, c.name, c.parent_id, ct.depth + 1
                FROM categories c
                JOIN category_tree ct ON ct.id = c.parent_id
            )
            SELECT * FROM category_tree ORDER BY depth, name
            """,
           nativeQuery = true)
    List<Object[]> findCategoryTree();
}

Sequences and ID Generation

PostgreSQL sequences are more efficient than IDENTITY for high-insert workloads because Hibernate can fetch a batch of IDs before inserting, enabling JDBC batch operations.
Java
// ── SEQUENCE strategy with allocationSize — enables batch inserts: ────
@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
                    generator = "orders_seq")
    @SequenceGenerator(
        name = "orders_seq",
        sequenceName = "orders_id_seq",   // PostgreSQL sequence name
        allocationSize = 50               // fetch 50 IDs per round trip
    )
    private Long id;
}

-- Flyway migration — create the sequence:
-- V2__create_orders_sequence.sql
CREATE SEQUENCE orders_id_seq
    START WITH 1
    INCREMENT BY 50     -- must match allocationSize in Java
    NO MINVALUE
    NO MAXVALUE
    CACHE 50;

CREATE TABLE orders (
    id          BIGINT          NOT NULL DEFAULT nextval('orders_id_seq'),
    reference   VARCHAR(50)     NOT NULL UNIQUE,
    total       NUMERIC(12,2)   NOT NULL,
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
);

// ── UUID with gen_random_uuid() — no sequence needed: ─────────────────
@Entity
@Table(name = "events")
public class Event {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(columnDefinition = "uuid", updatable = false, nullable = false)
    private UUID id;
}

-- Flyway migration:
CREATE TABLE events (
    id         UUID        NOT NULL DEFAULT gen_random_uuid(),
    type       VARCHAR(50) NOT NULL,
    payload    JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
);

Docker and Testcontainers

PostgreSQL in Docker for local development and Testcontainers for integration tests provide identical database behaviour between development, CI, and production.
yaml
# ── docker-compose.yml: ───────────────────────────────────────────────
services:
  postgres:
    image: postgres:16-alpine
    container_name: myapp-postgres
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: apppassword
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d
    command: >
      postgres
        -c shared_preload_libraries=pg_stat_statements
        -c pg_stat_statements.track=all
        -c max_connections=100

volumes:
  postgres_data:

# ── application-dev.yml: ──────────────────────────────────────────────
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: appuser
    password: apppassword
  jpa:
    hibernate:
      ddl-auto: create-drop

# ── Testcontainers integration test: ──────────────────────────────────
<!-- pom.xml: -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>

@SpringBootTest
@Testcontainers
class ProductRepositoryTest {

    @Container
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine")
            .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 ProductRepository productRepository;

    @Test
    void upsertCreatesAndUpdates() {
        UUID id = UUID.randomUUID();
        productRepository.upsert(id, "Widget", new BigDecimal("9.99"), "ACTIVE");
        productRepository.upsert(id, "Widget Pro", new BigDecimal("19.99"), "ACTIVE");

        Product found = productRepository.findById(id).orElseThrow();
        assertThat(found.getName()).isEqualTo("Widget Pro");
    }
}
Prev
MySQL Integration
Next
MongoDB Integration