Spring Boot, JPA and PostgreSQL REST API Example

Swapping MySQL for PostgreSQL in a Spring Boot application is a driver, a URL and a dialect. That part takes two minutes. What follows is the set of things that behave differently afterwards, one of which quietly halves your insert throughput.

Written against Spring Boot 3.2, Hibernate 6.4, Java 17 and PostgreSQL 16.

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/notes
spring.datasource.username=notes
spring.datasource.password=${DB_PASSWORD}

spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

No dialect property. Hibernate 6 detects PostgreSQL from the connection metadata, and setting hibernate.dialect by hand is how projects end up pinned to a dialect class that a later Hibernate renames.

ddl-auto=validate rather than update. Validate compares the entities to the schema at startup and fails on a mismatch, which is the useful half of update without the schema drift.

The generation strategy actually matters here

On MySQL, GenerationType.IDENTITY is the natural choice, AUTO_INCREMENT is how MySQL works. Carrying that habit to PostgreSQL costs you batching:

// works, and silently disables JDBC batch inserts
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

With IDENTITY, Hibernate cannot know the id until the row is inserted, so it must execute each INSERT immediately and read the generated key back. Batching is impossible by construction: a hundred saves are a hundred round trips, whatever batch_size says.

A sequence lets Hibernate allocate ids in advance and send one batch:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "note_seq")
@SequenceGenerator(name = "note_seq", sequenceName = "note_id_seq", allocationSize = 50)
private Long id;

CREATE SEQUENCE note_id_seq INCREMENT BY 50;

allocationSize must match the sequence’s INCREMENT BY. They default to 50 and 1 respectively, and a mismatch produces duplicate key violations under concurrency: Hibernate hands out ids it believes it owns while another instance is using the same range. It is a genuinely nasty bug because it appears only with two writers.

Hibernate 6’s GenerationType.AUTO maps to a sequence on PostgreSQL, so leaving it as AUTO is a reasonable default. Ids will have gaps, since each instance reserves a block and unused ones are never returned. Gaps in a surrogate key are not a problem; if someone believes they are. That is a requirements conversation rather than a technical one.

Timestamps

PostgreSQL has two timestamp types and only one of them is safe:

SQL type	Stores	Maps to
timestamptz	an instant, normalised to UTC	Instant, OffsetDateTime
timestamp	wall-clock text, no zone	LocalDateTime

@Column(name = "created_at", nullable = false, columnDefinition = "timestamptz")
private Instant createdAt;

Use timestamptz for anything that happened. timestamp stores what someone typed and loses which zone they meant, which is only correct when the zone genuinely does not matter.

One JDBC detail worth setting, because it removes a whole category of confusion:

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

Without it the driver converts using the JVM’s default zone, so the same code writes different values on a laptop in Berlin and a container in UTC.

The entity

@Entity
@Table(name = "notes", indexes = {
        @Index(name = "idx_notes_author", columnList = "author_id"),
        @Index(name = "idx_notes_created", columnList = "created_at")
})
public class Note {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "note_seq")
    @SequenceGenerator(name = "note_seq", sequenceName = "note_id_seq", allocationSize = 50)
    private Long id;

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

    @Column(nullable = false, columnDefinition = "text")
    private String content;

    @JdbcTypeCode(SqlTypes.ARRAY)
    @Column(name = "tags", columnDefinition = "text[]")
    private String[] tags = new String[0];

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

    @Column(name = "created_at", nullable = false, updatable = false,
            columnDefinition = "timestamptz")
    private Instant createdAt;
}

columnDefinition = "text" rather than a varchar(n). PostgreSQL’s text has no length limit and no performance penalty compared to varchar — the two are the same type internally, and a length constraint should exist because the domain requires it, not by default.

Two column types have no MySQL equivalent worth using, and Hibernate 6 maps both natively:

text[] — a real array column. @JdbcTypeCode(SqlTypes.ARRAY) binds it to a Java array, so a tag list needs no join table and no @ElementCollection.

jsonb — binary JSON, indexable and queryable. @JdbcTypeCode(SqlTypes.JSON) binds it to a Map or a POJO. Use it for genuinely schemaless attributes; a jsonb column holding fields you always read is a table you have not designed yet.

Repository, with PostgreSQL-specific queries

public interface NoteRepository extends JpaRepository<Note, Long> {

    // array containment — no join table involved
    @Query(value = "select * from notes where tags @> array[:tag]::text[]",
           nativeQuery = true)
    List<Note> findByTag(@Param("tag") String tag);

    // jsonb key lookup
    @Query(value = "select * from notes where metadata ->> 'source' = :source",
           nativeQuery = true)
    List<Note> findBySource(@Param("source") String source);

    // full-text search, ranked
    @Query(value = """
            select * from notes
            where to_tsvector('english', title || ' ' || content)
                  @@ plainto_tsquery('english', :q)
            order by ts_rank(to_tsvector('english', title || ' ' || content),
                             plainto_tsquery('english', :q)) desc
            """, nativeQuery = true)
    List<Note> search(@Param("q") String query);
}

These are native queries because JPQL has no vocabulary for @>, ->> or @@. That is a fair trade: you get capabilities the abstraction does not cover, and you accept that those methods are PostgreSQL-only.

Each needs an index to be worth anything:

CREATE INDEX idx_notes_tags ON notes USING gin (tags);
CREATE INDEX idx_notes_metadata ON notes USING gin (metadata);
CREATE INDEX idx_notes_fts ON notes USING gin
    (to_tsvector('english', title || ' ' || content));

GIN indexes are what make array containment, jsonb lookups and full-text search fast. Without them each query is a sequential scan, and the feature looks slow when it is simply unindexed.

For search on a large table, store the tsvector in a generated column and index that, rather than recomputing it per query:

ALTER TABLE notes ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;
CREATE INDEX idx_notes_search ON notes USING gin (search_vector);

Upsert

MySQL has ON DUPLICATE KEY UPDATE; PostgreSQL has ON CONFLICT, and JPA has neither. save() is select-then-insert-or-update, which is two statements and a race between them.

@Modifying
@Query(value = """
        insert into notes (id, title, content, created_at)
        values (:id, :title, :content, now())
        on conflict (id) do update
        set title = excluded.title,
            content = excluded.content
        """, nativeQuery = true)
void upsert(@Param("id") Long id,
            @Param("title") String title,
            @Param("content") String content);

excluded refers to the row that was proposed. This is atomic, which the read-modify-write pair is not — under concurrency save() can lose an update, and ON CONFLICT cannot.

Case-insensitive uniqueness

A common requirement, and lower(email) scattered through queries is the wrong solution. PostgreSQL has two better ones:

-- a functional unique index: the constraint lives in the database
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));

-- or the citext extension: the column itself compares case-insensitively
CREATE EXTENSION IF NOT EXISTS citext;
ALTER TABLE users ALTER COLUMN email TYPE citext;

The functional index requires every query to use lower(email) to hit it. citext moves the behaviour into the type, so ordinary equality works — at the cost of an extension and a column type Hibernate needs columnDefinition to preserve.

What ends up different from MySQL

Beyond the above, the differences that surface in practice:

• Identifier case. PostgreSQL folds unquoted identifiers to lower case; MySQL’s behaviour depends on the platform. Use snake_case everywhere and never quote.
• text versus varchar. No reason to prefer varchar on PostgreSQL.
• Booleans are real. boolean, not tinyint(1).
• Transactional DDL. A failed migration rolls back cleanly, which MySQL cannot do. Worth relying on in Flyway migrations.
• Stricter typing. PostgreSQL rejects comparisons and inserts MySQL silently coerces. That is a feature, and it does surface latent bugs during a migration.
• No implicit index on a foreign key. MySQL creates one; PostgreSQL does not. Declare them, or every parent-side delete scans the child table.

That last one is the most common performance surprise after a MySQL-to-PostgreSQL move.

Frequently asked questions

Do I need to set the Hibernate dialect?

No. Hibernate 6 detects PostgreSQL from connection metadata. Hardcoding a dialect class pins you to a name that may change.

Why should I use SEQUENCE instead of IDENTITY?

IDENTITY requires each insert to execute immediately so the key can be read back, which makes JDBC batching impossible. A sequence pre-allocates ids and allows batches.

Why am I getting duplicate key violations with a sequence?

allocationSize does not match the sequence’s INCREMENT BY. They default to 50 and 1. Make them equal.

Why do my ids have gaps?

Each instance reserves a block of allocationSize ids and unused ones are discarded. That is normal for a surrogate key.

timestamp or timestamptz?

timestamptz for anything that happened — it stores an instant. Plain timestamp is wall-clock text with no zone. Also set hibernate.jdbc.time_zone=UTC so the driver does not use the JVM default.

How do I map a jsonb column?

@JdbcTypeCode(SqlTypes.JSON) with columnDefinition = "jsonb". Hibernate 6 supports it natively; earlier versions needed a custom type.

Can I use PostgreSQL arrays instead of a join table?

Yes — @JdbcTypeCode(SqlTypes.ARRAY) on a Java array with columnDefinition = "text[]". Add a GIN index for containment queries.

Why is my full-text search slow?

It is doing a sequential scan. Add a GIN index on the to_tsvector(...) expression, or store the vector in a generated column and index that.

How do I upsert with JPA?

You cannot, portably. Use a native insert ... on conflict ... do update. save() is select-then-write and can lose an update under concurrency.

Do I need indexes on foreign keys?

On PostgreSQL, yes — unlike MySQL it does not create one automatically, and without it every parent delete scans the child table.

Where should I go next?

The MySQL version of this API covers the controller and repository layers in full, and one-to-many mapping covers relationships between these entities.