Spring Boot with PostgreSQL

PostgreSQL is the most popular production database for Spring Boot. This chapter covers Docker setup, configuration, JSONB, and connection pooling.

---

PostgreSQL with Docker

docker run -d \
  --name bookstore-postgres \
  -e POSTGRES_DB=bookstore \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  postgres:16

Or with Docker Compose:

# docker-compose.yml
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: bookstore
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

docker compose up -d

---

Spring Boot Configuration

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/bookstore
    username: admin
    password: secret
    driver-class-name: org.postgresql.Driver
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 300000
      connection-timeout: 20000
      max-lifetime: 1200000
  jpa:
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true
    show-sql: true

---

HikariCP Connection Pool

Spring Boot uses HikariCP by default — the fastest Java connection pool:

spring:
  datasource:
    hikari:
      maximum-pool-size: 20        # max connections
      minimum-idle: 5              # min idle connections
      idle-timeout: 300000         # 5 min — close idle connections
      connection-timeout: 20000    # 20 sec — wait for connection
      max-lifetime: 1200000        # 20 min — max connection age
      pool-name: BookstorePool

Rule of thumb: pool-size = (core_count * 2) + effective_spindle_count. For most apps, 10–20 connections is sufficient.

---

PostgreSQL JSONB Columns

@Entity
@Table(name = "books")
public class Book {

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

    private String title;

    @Column(columnDefinition = "jsonb")
    @JdbcTypeCode(SqlTypes.JSON)
    private Map<String, Object> metadata;
}

Migration:

ALTER TABLE books ADD COLUMN metadata JSONB DEFAULT '{}';
CREATE INDEX idx_books_metadata ON books USING GIN (metadata);

Query JSONB:

@Query(value = "SELECT * FROM books WHERE metadata->>'language' = :lang", nativeQuery = true)
List<Book> findByMetadataLanguage(@Param("lang") String language);

---

Profile-Based DB Config

# application-dev.yml — H2 for development
spring:
  datasource:
    url: jdbc:h2:mem:devdb
  jpa:
    hibernate:
      ddl-auto: create-drop

# application-prod.yml — PostgreSQL for production
spring:
  datasource:
    url: ${DATABASE_URL}
    username: ${DATABASE_USERNAME}
    password: ${DATABASE_PASSWORD}
  jpa:
    hibernate:
      ddl-auto: validate

---

Key Takeaways

1. Use Docker for local PostgreSQL — reproducible, disposable.
2. HikariCP is auto-configured — tune pool size for your workload.
3. PostgreSQL JSONB stores semi-structured data with indexing.
4. Use profiles — H2 for dev, PostgreSQL for prod.
5. Never hardcode credentials — use environment variables.

---

What’s Next?

Next: Spring Boot with MySQL — differences from PostgreSQL setup.