Extensão — PostgreSQL: o Banco Servidor, JSONB e Arrays

Nesta aula o projeto das Trilhas da Ilha passa a usar um banco servidor de verdade. Você vai subir o PostgreSQL, instalado na máquina ou num contêiner, usar arrays e JSONB com índices que procuram dentro deles e fazer busca textual em português. Depois vem o controle de concorrência que deixa leitores e escritores trabalharem juntos, e uma demonstração da atualização perdida, a armadilha de concorrência mais comum em bancos de dados.

O SQLite levou o projeto até aqui com uma simplicidade difícil de bater: um arquivo, nenhuma instalação. Mas ele tem limites que um site com muitos usuários encontra depressa. Só uma escrita acontece por vez no banco inteiro, como o exercício 5 de "Spring Boot pela Primeira Vez" discutiu. Não há acesso pela rede, então duas instâncias da aplicação não podem compartilhar o mesmo banco. E faltam tipos e recursos que tornam o trabalho com dados muito mais expressivo. O PostgreSQL é o banco relacional de código aberto mais completo e, há alguns anos, o mais usado por desenvolvedores nas pesquisas da Stack Overflow. Esta aula leva o projeto para ele e explora o que ele oferece além do SQL comum.

Um pouco de história

O PostgreSQL nasceu em 1986 na Universidade da Califórnia em Berkeley, como o projeto Postgres, liderado por Michael Stonebraker. O nome indicava o sucessor do Ingres, um banco pioneiro que o mesmo grupo havia criado nos anos 1970. O Postgres introduziu ideias que só décadas depois chegariam aos outros bancos, como tipos definidos pelo usuário e extensões. Em 1995, ganhou uma interface SQL e virou Postgres95, e em 1996 foi rebatizado de PostgreSQL e passou a ser mantido por uma comunidade global, sem uma empresa dona. Uma versão principal sai a cada ano. A 18, de setembro de 2025, é a mais recente, e é a que o projeto usa. As consultas e os programas JDBC desta aula foram executados no PostgreSQL 18.6.

Subindo o banco

Um banco servidor precisa estar instalado e rodando. Há dois caminhos, e os dois terminam no mesmo lugar: um banco trilhas, de um usuário trilhas com a senha trilhas, escutando na porta 5432 da sua máquina.

Com o PostgreSQL instalado. No Windows, o instalador do site oficial já traz o cliente psql; no Linux, basta o pacote postgresql da distribuição; no macOS, o Postgres.app ou o Homebrew. O usuário e o banco são criados uma única vez, com o psql conectado como administrador:

# Linux: o administrador é o usuário postgres do sistema
sudo -u postgres psql

# Windows: com a senha escolhida na instalação
psql -U postgres

# macOS (Postgres.app ou Homebrew): o administrador é o seu próprio usuário
psql postgres

postgres=# CREATE ROLE trilhas LOGIN PASSWORD 'trilhas';
CREATE ROLE
postgres=# CREATE DATABASE trilhas OWNER trilhas;
CREATE DATABASE
postgres=# \q

Daí em diante, a conexão usa o usuário novo, e o psql pede a senha: psql -h localhost -U trilhas trilhas. Os exemplos desta aula foram executados assim, sem contêiner.

Com Docker. Quem prefere não instalar o banco pode usar o Docker, que executa programas em contêineres, ambientes isolados criados a partir de imagens prontas. A instalação do Docker Desktop, no Windows e no macOS, ou do Docker Engine, no Linux, está descrita no site do projeto. Com ele instalado, um arquivo compose.yaml na raiz do projeto descreve o banco:

services:
  postgres:
    image: postgres:18
    environment:
      POSTGRES_DB: trilhas
      POSTGRES_USER: trilhas
      POSTGRES_PASSWORD: trilhas
    ports:
      - "5432:5432"
    volumes:
      - dados-postgres:/var/lib/postgresql

volumes:
  dados-postgres:

docker compose up -d baixa a imagem oficial do PostgreSQL 18, cria o banco trilhas com o usuário e a senha indicados e o deixa escutando na porta 5432 da sua máquina. O volume dados-postgres guarda os dados fora do contêiner, então eles sobrevivem quando o contêiner é recriado. A partir da versão 18, a imagem oficial guarda os dados dentro de /var/lib/postgresql, e é essa pasta que o volume deve cobrir; tutoriais para versões anteriores usam /var/lib/postgresql/data. Para conversar com o banco, o cliente de linha de comando psql vem dentro do contêiner: docker compose exec postgres psql -U trilhas.

A senha "trilhas" num arquivo do repositório é aceitável só para desenvolvimento local. "Do Laptop ao Servidor" vai tratar dos segredos de produção.

O esquema no dialeto do PostgreSQL

O PostgreSQL segue o padrão SQL de perto, mas cada banco tem seu dialeto, e as migrações precisam ser escritas nele. A primeira migração do projeto, para o PostgreSQL:

-- db/migration/postgresql/V1__esquema_inicial.sql
CREATE TABLE trilha (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nome            TEXT             NOT NULL UNIQUE,
    regiao          TEXT             NOT NULL,
    distancia_km    DOUBLE PRECISION NOT NULL CHECK (distancia_km > 0),
    dificuldade     TEXT             NOT NULL CHECK (dificuldade IN ('FACIL', 'MODERADA', 'DIFICIL')),
    duracao_min     INTEGER          NOT NULL CHECK (duracao_min > 0),
    altitude_max_m  INTEGER          CHECK (altitude_max_m > 0),
    etiquetas       TEXT[]           NOT NULL DEFAULT '{}',
    detalhes        JSONB            NOT NULL DEFAULT '{}',
    descricao       TEXT             NOT NULL DEFAULT '',
    cadastrada_em   TIMESTAMPTZ      NOT NULL DEFAULT now()
);

CREATE TABLE ponto_de_interesse (
    trilha_id  BIGINT  NOT NULL REFERENCES trilha (id) ON DELETE CASCADE,
    ordem      INTEGER NOT NULL,
    nome       TEXT    NOT NULL CHECK (length(nome) BETWEEN 1 AND 100),
    PRIMARY KEY (trilha_id, ordem)
);

CREATE TABLE avaliacao (
    id          BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    trilha_id   BIGINT  NOT NULL REFERENCES trilha (id) ON DELETE CASCADE,
    autor       TEXT    NOT NULL,
    nota        INTEGER NOT NULL CHECK (nota BETWEEN 1 AND 5),
    comentario  TEXT,
    data        DATE    NOT NULL
);

CREATE INDEX idx_avaliacao_trilha ON avaliacao (trilha_id);
CREATE INDEX idx_trilha_etiquetas ON trilha USING GIN (etiquetas);
CREATE INDEX idx_trilha_detalhes ON trilha USING GIN (detalhes jsonb_path_ops);

Comparando com o SQLite, as diferenças contam muito sobre o PostgreSQL. A chave primária usa GENERATED ALWAYS AS IDENTITY, a forma do padrão SQL para colunas autoincrementadas. O ALWAYS proíbe que alguém informe o id à mão, o que evita colisões com a sequência. Os tipos são rigorosos por natureza, sem precisar do STRICT. As datas têm tipos próprios: DATE para dias e TIMESTAMPTZ para instantes, que guarda o momento em UTC e o converte para o fuso de quem consulta, o equivalente ao Instant e ao OffsetDateTime de "Tempo, Dinheiro e Padrões". As chaves estrangeiras funcionam sempre, sem nenhum PRAGMA. E há três colunas novas, que o SQLite não conseguiria representar da mesma forma: etiquetas, um array de textos, detalhes, um documento JSON, e descricao, que vai ser usada na busca textual.

A distância é DOUBLE PRECISION, o double do Java, e não NUMERIC, o tipo decimal exato do PostgreSQL. A escolha é deliberada: com o ddl-auto=validate de "O Banco que Evolui", o Hibernate confere se o tipo da coluna combina com o do campo da entidade, e um NUMERIC só combina com um BigDecimal. Para dinheiro, a escolha seria a oposta, pelo motivo de "Tempo, Dinheiro e Padrões".

Como o dialeto muda de banco para banco, as migrações ficam numa pasta por banco, e o Spring Boot escolhe a certa com um marcador na configuração, mostrado mais adiante.

Arrays

Um array guarda vários valores do mesmo tipo numa única coluna. As etiquetas de uma trilha, como "praia" e "mirante", são o caso típico: poucos valores, sem dados próprios, consultados como um conjunto. Com quatro trilhas de exemplo gravadas:

INSERT INTO trilha (nome, regiao, distancia_km, dificuldade, duracao_min, altitude_max_m, etiquetas, detalhes, descricao) VALUES
  ('Lagoinha do Leste', 'Sul', 4.5, 'MODERADA', 120, 310, '{praia,mirante,camping}',
   '{"estacionamento": true, "agua": false, "cachorros": false, "guia": {"obrigatorio": false}}',
   'Travessia pela mata até uma praia isolada, com uma lagoa de água doce atrás da areia.'),
  ('Costão do Santinho', 'Norte', 3.2, 'FACIL', 90, NULL, '{praia,arqueologia}',
   '{"estacionamento": true, "agua": true, "cachorros": true}',
   'Caminho curto pelo costão, com inscrições rupestres gravadas nas rochas.'),
  ('Morro da Coroa', 'Leste', 6.8, 'DIFICIL', 210, 480, '{mata,cachoeira}',
   '{"estacionamento": false, "agua": true, "guia": {"obrigatorio": true, "contato": "Associação de Guias"}}',
   'Trilha longa e íngreme na mata fechada, com uma cachoeira no topo.'),
  ('Praia do Saquinho', 'Sul', 2.1, 'FACIL', 60, NULL, '{praia}',
   '{"estacionamento": true, "cachorros": true}',
   'Caminhada leve pela costa até uma pequena praia de pescadores.');

as consultas usam operadores próprios dos arrays:

trilhas=> SELECT nome, etiquetas FROM trilha WHERE 'praia' = ANY (etiquetas) ORDER BY nome;
        nome        |        etiquetas
--------------------+-------------------------
 Costão do Santinho | {praia,arqueologia}
 Lagoinha do Leste  | {praia,mirante,camping}
 Praia do Saquinho  | {praia}
(3 rows)

trilhas=> SELECT nome FROM trilha WHERE etiquetas @> '{praia,mirante}';
       nome
-------------------
 Lagoinha do Leste
(1 row)

trilhas=> SELECT unnest(etiquetas) AS etiqueta, COUNT(*) FROM trilha GROUP BY 1 ORDER BY 2 DESC, 1;
  etiqueta   | count
-------------+-------
 praia       |     3
 arqueologia |     1
 cachoeira   |     1
 camping     |     1
 mata        |     1
 mirante     |     1
(6 rows)

O = ANY pergunta se um valor está no array. O @>, lido como "contém", pergunta se o array contém todos os valores de outro. O unnest transforma o array em linhas, uma por elemento, o que permite agrupá-las e contá-las como numa tabela: a terceira consulta monta, em uma linha de SQL, a nuvem de etiquetas do site.

Com o @>, o índice GIN da migração entra em ação. Um índice comum, do tipo B-tree, ordena valores inteiros e não sabe olhar dentro de um array. Um GIN (Generalized Inverted Index) é um índice invertido, como o índice remissivo de um livro: para cada etiqueta, ele guarda a lista das linhas que a contêm. Para medir, a tabela ganha 200 mil trilhas sintéticas, 400 delas com a etiqueta "cachoeira" (401, contando o Morro da Coroa) e 200 que aceitam cachorros:

INSERT INTO trilha (nome, regiao, distancia_km, dificuldade, duracao_min, etiquetas, detalhes)
SELECT 'Sintética ' || i, 'Sul', 1 + i % 10, 'FACIL', 30 + i % 200,
       CASE WHEN i % 500 = 0 THEN '{mata,cachoeira}'::text[] ELSE '{mata}' END,
       CASE WHEN i % 1000 = 0 THEN '{"cachorros": true}'::jsonb ELSE '{"cachorros": false}' END
  FROM generate_series(1, 200000) AS i;

VACUUM ANALYZE trilha;   -- atualiza as estatísticas que o planejador usa

O EXPLAIN ANALYZE, que executa a consulta e mostra o que o banco realmente fez, confirma o uso do índice. O BUFFERS OFF esconde as linhas sobre páginas lidas, que o PostgreSQL 18 passou a mostrar por padrão:

trilhas=> EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF) SELECT nome FROM trilha WHERE etiquetas @> '{cachoeira}';
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on trilha (actual time=0.104..2.266 rows=401.00 loops=1)
   Recheck Cond: (etiquetas @> '{cachoeira}'::text[])
   Heap Blocks: exact=401
   ->  Bitmap Index Scan on idx_trilha_etiquetas (actual time=0.070..0.070 rows=401.00 loops=1)
         Index Cond: (etiquetas @> '{cachoeira}'::text[])
         Index Searches: 1
 Planning Time: 0.425 ms
 Execution Time: 2.322 ms

O Bitmap Index Scan consulta o índice e marca as linhas que interessam, e o Bitmap Heap Scan lê só essas linhas da tabela: 401 linhas lidas de 200 mil.

Arrays não substituem tabelas relacionadas. Os pontos de interesse continuam numa tabela à parte, porque têm ordem e poderiam ganhar dados próprios, como coordenadas. As avaliações também, porque têm autor, nota e data, e são criadas por usuários diferentes ao mesmo tempo. Arrays servem para listas curtas de valores simples, tratadas como um todo.

JSONB: documentos dentro de linhas

Algumas informações não cabem bem em colunas fixas. Os detalhes práticos de uma trilha, como estacionamento, água potável, se aceita cachorros e se exige guia, variam de trilha para trilha, e novos itens aparecem com o tempo. Criar uma coluna para cada um geraria uma tabela larga e cheia de nulos. O PostgreSQL oferece o tipo JSONB, que guarda um documento JSON num formato binário decomposto, pronto para ser consultado e indexado. O tipo JSON comum também existe e guarda o texto como veio. O JSONB é o recomendado para quase todos os usos.

trilhas=> SELECT nome, detalhes ->> 'agua' AS agua, detalhes -> 'guia' ->> 'contato' AS contato_do_guia FROM trilha ORDER BY id;
        nome        | agua  |   contato_do_guia
--------------------+-------+---------------------
 Lagoinha do Leste  | false |
 Costão do Santinho | true  |
 Morro da Coroa     | true  | Associação de Guias
 Praia do Saquinho  |       |
(4 rows)

trilhas=> SELECT nome FROM trilha WHERE detalhes @> '{"estacionamento": true, "cachorros": true}' ORDER BY nome;
        nome
--------------------
 Costão do Santinho
 Praia do Saquinho
(2 rows)

trilhas=> SELECT nome FROM trilha WHERE (detalhes -> 'guia' ->> 'obrigatorio')::boolean;
      nome
----------------
 Morro da Coroa
(1 row)

trilhas=> UPDATE trilha SET detalhes = jsonb_set(detalhes, '{agua}', 'true') WHERE nome = 'Lagoinha do Leste' RETURNING nome, detalhes;
       nome        |                                          detalhes
-------------------+--------------------------------------------------------------------------------------------
 Lagoinha do Leste | {"agua": true, "guia": {"obrigatorio": false}, "cachorros": false, "estacionamento": true}

O operador -> extrai um campo como JSON, e o ->> o extrai como texto. Encadeados, eles navegam por documentos aninhados. A Praia do Saquinho, que não tem o campo "agua", devolve nulo, e não um erro. O @> pergunta se o documento contém outro. O jsonb_set altera um campo sem reescrever o documento na aplicação, e o RETURNING devolve as linhas alteradas, e o programa Java mais adiante o usa para obter o id gerado. Repare que o documento devolvido tem as chaves em outra ordem: o JSONB não preserva a ordem das chaves nem os espaços, porque guarda o documento decomposto.

O índice GIN com jsonb_path_ops acelera as consultas de contenção, mas com uma sutileza que vale uma medição. Na mesma tabela de 200 mil trilhas, duas consultas que fazem a mesma pergunta:

trilhas=> EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF) SELECT nome FROM trilha WHERE detalhes @> '{"cachorros": true}';
 Bitmap Heap Scan on trilha (actual time=0.046..0.201 rows=202.00 loops=1)
   ->  Bitmap Index Scan on idx_trilha_detalhes (actual time=0.025..0.025 rows=202.00 loops=1)
 Execution Time: 0.216 ms

trilhas=> EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF) SELECT nome FROM trilha WHERE detalhes ->> 'cachorros' = 'true';
 Gather (actual time=0.306..20.844 rows=202.00 loops=1)
   Workers Planned: 2
   ->  Parallel Seq Scan on trilha (actual time=0.098..13.432 rows=67.33 loops=3)
         Filter: ((detalhes ->> 'cachorros'::text) = 'true'::text)
         Rows Removed by Filter: 66601
 Execution Time: 20.865 ms

A primeira usa o índice e leva 0,2 ms. A segunda, logicamente equivalente, não usa o índice: o banco lê a tabela inteira, dividindo o trabalho entre três processos, e leva 21 ms, cerca de cem vezes mais. O índice GIN sabe responder ao operador @>, e não à comparação de um campo extraído. A lição vale para todo índice de todo banco: ele serve a operadores específicos, e a forma de escrever a consulta decide se ele é usado. O plano de execução é a única forma de saber.

O JSONB traz de volta uma tentação que "MongoDB" vai discutir a fundo: guardar tudo em documentos e abandonar as tabelas. A regra prática é usar colunas para o que tem estrutura fixa, é consultado com frequência e precisa de restrições, e o JSONB para o que é variável, opcional e consultado de vez em quando. Os detalhes práticos da trilha se encaixam no segundo grupo. O nome, a distância e a dificuldade, no primeiro.

Busca textual em português

Os visitantes do site vão querer buscar trilhas por palavras: "cachoeira", "praia isolada". Um LIKE '%cachoeira%' resolve o caso mais simples, mas não encontra "cachoeiras" numa descrição que diz "cachoeira", não ordena por relevância e não usa índice comum. O PostgreSQL tem um mecanismo completo de busca textual, com suporte ao português:

trilhas=> SELECT to_tsvector('portuguese', 'Trilha longa e íngreme na mata fechada, com uma cachoeira no topo.');
                              to_tsvector
-----------------------------------------------------------------------
 'cachoeir':10 'fech':7 'long':2 'mat':6 'top':12 'trilh':1 'íngrem':4

O to_tsvector transforma um texto em lexemas. As palavras são reduzidas ao radical por um stemmer da língua portuguesa, de forma que "cachoeira" e "cachoeiras" viram o mesmo "cachoeir". As palavras vazias, como "e", "na", "com" e "uma", são descartadas, e a posição de cada palavra é guardada. Uma consulta passa pelo mesmo processo, e a busca compara radicais. A migração que acrescenta a busca ao projeto cria uma coluna gerada, calculada pelo próprio banco a partir de outras e mantida atualizada automaticamente, e um índice GIN sobre ela:

-- db/migration/postgresql/V2__busca_textual.sql
ALTER TABLE trilha ADD COLUMN busca tsvector
    GENERATED ALWAYS AS (to_tsvector('portuguese', nome || ' ' || descricao)) STORED;
CREATE INDEX idx_trilha_busca ON trilha USING GIN (busca);

As consultas usam o websearch_to_tsquery, que entende a sintaxe que as pessoas usam nos buscadores: palavras soltas, frases entre aspas e o sinal de menos para excluir:

trilhas=> SELECT nome, ts_rank(busca, consulta) AS relevancia
            FROM trilha, websearch_to_tsquery('portuguese', 'cachoeiras') AS consulta
           WHERE busca @@ consulta;
      nome      | relevancia
----------------+------------
 Morro da Coroa | 0.06079271

trilhas=> SELECT nome FROM trilha WHERE busca @@ websearch_to_tsquery('portuguese', '"praia isolada"');
       nome
-------------------
 Lagoinha do Leste

trilhas=> SELECT nome FROM trilha WHERE busca @@ websearch_to_tsquery('portuguese', 'praia -lagoa');
       nome
-------------------
 Praia do Saquinho

trilhas=> SELECT nome FROM trilha WHERE descricao ILIKE '%cachoeiras%';
 nome
------
(0 rows)

A busca por "cachoeiras" encontrou a descrição com "cachoeira", e o ILIKE não. A frase entre aspas exige as palavras em sequência, e o -lagoa excluiu a Lagoinha do Leste, cuja descrição fala de uma lagoa. O ts_rank calcula uma relevância, usada para ordenar os resultados quando há muitos. Para acentos digitados de formas diferentes, como "iNGREME" e "íngreme", a extensão unaccent do PostgreSQL remove os acentos antes da comparação. Para um site de trilhas, esse mecanismo embutido dispensa, por muito tempo, um motor de busca separado como o Elasticsearch.

Do Java: arrays, JSONB e o SQLState

O driver JDBC do PostgreSQL, org.postgresql:postgresql, entende os tipos próprios do banco. Um programa que insere uma trilha com array e JSONB, lê de volta, busca por etiqueta e provoca um erro:

import java.sql.*;
import java.time.OffsetDateTime;
import java.util.*;

public class PostgresJdbc {
    static final String URL = "jdbc:postgresql://localhost:5432/trilhas";

    static Connection abrir() throws SQLException {
        return DriverManager.getConnection(URL, "trilhas", "trilhas");
    }

    public static void main(String[] args) throws Exception {
        try (Connection c = abrir()) {
            System.out.println(c.getMetaData().getDatabaseProductName() + " " + c.getMetaData().getDatabaseProductVersion()
                    + ", driver " + c.getMetaData().getDriverVersion());

            // 1. Inserir com array, JSONB e RETURNING
            long id;
            try (PreparedStatement ps = c.prepareStatement("""
                    INSERT INTO trilha (nome, regiao, distancia_km, dificuldade, duracao_min, etiquetas, detalhes)
                    VALUES (?, ?, ?, ?, ?, ?, ?::jsonb)
                    RETURNING id, cadastrada_em""")) {
                ps.setString(1, "Pico do Ribeirão");
                ps.setString(2, "Sul");
                ps.setDouble(3, 5.4);
                ps.setString(4, "DIFICIL");
                ps.setInt(5, 180);
                ps.setArray(6, c.createArrayOf("text", new String[]{"mata", "mirante"}));
                ps.setString(7, "{\"estacionamento\": false, \"agua\": true}");
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    id = rs.getLong("id");
                    System.out.println("id " + id + ", cadastrada em " + rs.getObject("cadastrada_em", OffsetDateTime.class));
                }
            }

            // 2. Ler de volta: o array vira String[], o JSONB chega como texto
            try (PreparedStatement ps = c.prepareStatement("SELECT etiquetas, detalhes FROM trilha WHERE id = ?")) {
                ps.setLong(1, id);
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    String[] etiquetas = (String[]) rs.getArray("etiquetas").getArray();
                    System.out.println("etiquetas: " + Arrays.toString(etiquetas) + ", detalhes: " + rs.getString("detalhes"));
                }
            }

            // 3. Buscar por etiqueta, passando o array como parâmetro
            try (PreparedStatement ps = c.prepareStatement("SELECT nome FROM trilha WHERE etiquetas @> ? ORDER BY nome")) {
                ps.setArray(1, c.createArrayOf("text", new String[]{"mirante"}));
                try (ResultSet rs = ps.executeQuery()) {
                    List<String> nomes = new ArrayList<>();
                    while (rs.next()) nomes.add(rs.getString(1));
                    System.out.println("com mirante: " + nomes);
                }
            }

            // 4. Erros com SQLState padronizado
            try (Statement s = c.createStatement()) {
                s.executeUpdate("INSERT INTO trilha (nome, regiao, distancia_km, dificuldade, duracao_min) VALUES ('Pico do Ribeirão', 'Sul', 1, 'FACIL', 10)");
            } catch (SQLException e) {
                System.out.println("SQLState " + e.getSQLState() + ": " + e.getMessage().lines().findFirst().orElse(""));
            }
        }
    }
}

PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2), driver 42.7.13
id 200005, cadastrada em 2026-10-10T15:17:50.198962Z
etiquetas: [mata, mirante], detalhes: {"agua": true, "estacionamento": false}
com mirante: [Lagoinha do Leste, Pico do Ribeirão]
SQLState 23505: ERROR: duplicate key value violates unique constraint "trilha_nome_key"

A URL agora aponta para um servidor, jdbc:postgresql://localhost:5432/trilhas, com usuário e senha. O createArrayOf cria um java.sql.Array a partir de um array Java, e o getArray faz o caminho inverso. O JSONB viaja como texto, com a conversão ?::jsonb no próprio SQL. O TIMESTAMPTZ é lido diretamente como OffsetDateTime pelo getObject com classe, do JDBC 4.2, sem passar pelas antigas java.sql.Timestamp.

O SQLState agora vem preenchido: 23505 é "violação de unicidade" no padrão SQL, e a mensagem diz até o nome da restrição que falhou. É o código que um tratamento de erros deve examinar, e não o texto da mensagem.

O id 200005 é outra lição. As 200 mil trilhas sintéticas usadas nas medições foram apagadas, mas a sequência que gera os identificadores não volta atrás. Sequências nunca reaproveitam valores, nem os de linhas apagadas, nem os de inserções que falharam ou foram desfeitas, porque garantir números consecutivos exigiria que todas as transações esperassem umas pelas outras. Um id serve para identificar, e buracos na numeração são normais. Quem precisa de uma numeração sem falhas, como a de notas fiscais, precisa de outro mecanismo.

Concorrência: MVCC e a atualização perdida

Com um banco servidor, dezenas de conexões trabalham ao mesmo tempo, e os problemas de "O Cadeado e a Memória" reaparecem, agora entre transações. O PostgreSQL os trata com o MVCC (Multi-Version Concurrency Control, controle de concorrência por múltiplas versões). Uma atualização não sobrescreve a linha: cria uma nova versão dela, e cada transação enxerga as versões que já estavam confirmadas quando ela começou a ler. A consequência mais visível é que quem lê não espera quem escreve. Um programa com duas conexões mostra isso: o escritor altera a duração do Morro da Coroa para 999, sem confirmar, e o leitor consulta a mesma linha nesse momento:

durante a transação do escritor, o leitor vê: 210

O leitor não ficou bloqueado e não viu o valor não confirmado. Esse fenômeno, a leitura suja, o PostgreSQL nunca permite. Viu a versão anterior, 210, e o escritor então desfez tudo.

Mas o MVCC não protege contra tudo. O segundo experimento registra visitas às trilhas: dez threads virtuais, cada uma com sua conexão, registram cem visitas cada, e o contador deveria terminar em mil. São três formas de fazer o registro:

// Ler, somar no Java, gravar: duas transações podem ler o mesmo valor
static final Visita LER_E_GRAVAR = c -> {
    int atual;
    try (Statement s = c.createStatement();
         ResultSet rs = s.executeQuery("SELECT visitas FROM trilha WHERE nome = 'Morro da Coroa'")) {
        rs.next();
        atual = rs.getInt(1);
    }
    try (PreparedStatement ps = c.prepareStatement("UPDATE trilha SET visitas = ? WHERE nome = 'Morro da Coroa'")) {
        ps.setInt(1, atual + 1);
        ps.executeUpdate();
    }
};

// O mesmo, mas travando a linha na leitura
static final Visita COM_FOR_UPDATE = c -> {
    int atual;
    try (Statement s = c.createStatement();
         ResultSet rs = s.executeQuery("SELECT visitas FROM trilha WHERE nome = 'Morro da Coroa' FOR UPDATE")) {
        rs.next();
        atual = rs.getInt(1);
    }
    try (PreparedStatement ps = c.prepareStatement("UPDATE trilha SET visitas = ? WHERE nome = 'Morro da Coroa'")) {
        ps.setInt(1, atual + 1);
        ps.executeUpdate();
    }
};

// Deixar o banco somar: uma única operação atômica
static final Visita ATOMICA = c -> {
    try (Statement s = c.createStatement()) {
        s.executeUpdate("UPDATE trilha SET visitas = visitas + 1 WHERE nome = 'Morro da Coroa'");
    }
};

Visita é uma interface funcional cujo método recebe a conexão e pode lançar SQLException, e cada registro é confirmado com commit em seguida. A coluna visitas foi acrescentada por mais uma migração, com DEFAULT 0. Duas rodadas de medição:

ler e gravar    1000 visitas registradas → contador = 101  (1732 ms)
FOR UPDATE      1000 visitas registradas → contador = 1000  (1708 ms)
atômica         1000 visitas registradas → contador = 1000  (1552 ms)
ler e gravar    1000 visitas registradas → contador = 101  (1503 ms)
FOR UPDATE      1000 visitas registradas → contador = 1000  (1666 ms)
atômica         1000 visitas registradas → contador = 1000  (1512 ms)

Das mil visitas, a primeira forma registrou 101, nas duas rodadas. Nenhum erro, nenhuma exceção, nenhum aviso: quase 90% das visitas desapareceram. É a atualização perdida (lost update), o valor++ de "Fazendo Duas Coisas ao Mesmo Tempo", agora entre transações de banco. Duas transações leem 41 ao mesmo tempo, as duas calculam 42 no Java, e as duas gravam 42: uma visita sumiu. O MVCC garante que cada transação veja dados consistentes, e não que as decisões tomadas com base neles continuem válidas quando ela grava.

As duas correções são as mesmas de "O Cadeado e a Memória" e "Ferramentas de Precisão". O SELECT ... FOR UPDATE trava a linha na leitura. Uma segunda transação que tente o mesmo FOR UPDATE espera a primeira confirmar, e então lê o valor já atualizado. É o synchronized, com o banco guardando a trava. A atualização atômica, SET visitas = visitas + 1, faz a leitura e a gravação numa única operação dentro do banco, como o incrementAndGet de um AtomicInteger, e é a mais rápida das três, porque não mantém a trava durante uma ida e volta ao Java. A regra é a mesma que valia para as threads: sempre que possível, deixe o banco fazer a conta.

A transação @Transactional do Spring não resolveria o problema sozinha. O nível de isolamento padrão do PostgreSQL, Read Committed, permite a atualização perdida nesse padrão de "ler, calcular, gravar". O nível Serializable a impediria, fazendo uma das transações falhar com um erro de serialização (SQLState 40001), que a aplicação precisaria tratar tentando de novo. No JPA, a solução mais comum é outra, o bloqueio otimista, com um campo anotado com @Version: o Hibernate inclui a versão no WHERE do UPDATE e lança uma exceção se outra transação alterou a linha antes, a mesma ideia do compare-and-set de "Ferramentas de Precisão".

O projeto das Trilhas da Ilha no PostgreSQL

No pom.xml, entram o driver e o módulo do Flyway para o PostgreSQL:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

A configuração passa a usar perfis, como os de "Spring Boot pela Primeira Vez": o application.properties guarda o que vale para todos os bancos, e cada banco tem o seu arquivo. O application-postgres.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/trilhas
spring.datasource.username=trilhas
spring.datasource.password=trilhas

E no application.properties comum:

spring.profiles.default=postgres
spring.flyway.locations=classpath:db/migration/{vendor}
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false

O marcador {vendor} é substituído pelo Spring Boot pelo nome do banco detectado na conexão, e as migrações do PostgreSQL ficam em db/migration/postgresql. As do SQLite, se o projeto quiser mantê-lo como opção, vão para db/migration/sqlite, com as propriedades de "Spring Boot pela Primeira Vez" num application-sqlite.properties. Não é preciso informar o dialeto: o Hibernate o detecta pela conexão. O spring.profiles.default escolhe o perfil quando nenhum outro é ativado, e para rodar com o SQLite basta --spring.profiles.active=sqlite.

Na entidade Trilha, os tipos novos são mapeados com a anotação @JdbcTypeCode, do Hibernate, que diz qual tipo SQL usar para um campo:

import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.type.SqlTypes;

    @JdbcTypeCode(SqlTypes.ARRAY)
    @Column(nullable = false)
    private List<String> etiquetas = new ArrayList<>();

    @JdbcTypeCode(SqlTypes.JSON)
    @Column(nullable = false)
    private DetalhesDaTrilha detalhes = new DetalhesDaTrilha(null, null, null);

    @Column(nullable = false)
    private String descricao = "";

    @Column(name = "cadastrada_em", nullable = false)
    private java.time.OffsetDateTime cadastradaEm = java.time.OffsetDateTime.now();

package br.com.exemplo.trilhas;

/** Os detalhes práticos de uma trilha, guardados como um documento JSON. Campos ausentes ficam nulos. */
public record DetalhesDaTrilha(Boolean estacionamento, Boolean agua, Boolean cachorros) {}

Com SqlTypes.ARRAY, a lista vira um text[]. Com SqlTypes.JSON, o record é convertido para JSON e gravado num jsonb, e o Hibernate usa para isso a biblioteca de JSON que encontrar no classpath, o Jackson que o Spring Boot já traz. Os tipos Boolean com maiúscula são deliberados: um detalhe que não foi informado fica nulo, e não false. Os conversores de datas de "Objetos no Banco", que só existiam por causa do SQLite, não são mais necessários, porque o PostgreSQL tem tipos de data de verdade.

As consultas que usam recursos próprios do PostgreSQL, como a busca textual, vão como consultas nativas no repositório do Spring Data:

@Query(value = """
        SELECT * FROM trilha
         WHERE busca @@ websearch_to_tsquery('portuguese', :termos)
         ORDER BY ts_rank(busca, websearch_to_tsquery('portuguese', :termos)) DESC
         LIMIT 20
        """, nativeQuery = true)
List<Trilha> buscarPorTexto(String termos);

Com nativeQuery = true, o SQL vai para o banco como está, e o Hibernate converte as linhas em entidades. É o equilíbrio usual: JPQL e consultas derivadas para o que é comum a todos os bancos, SQL nativo para o que faz o PostgreSQL valer a pena. O preço é que essas consultas amarram o projeto a esse banco, tema da próxima aula.

O projeto agora roda num banco servidor, com arrays, documentos, busca textual e um controle de concorrência sério. Mas o PostgreSQL não é o único banco servidor que um desenvolvedor Java encontra. O MySQL é pelo menos tão comum, especialmente em hospedagens e sistemas mais antigos, e as diferenças entre os dois vão muito além da sintaxe.

Fontes e leituras recomendadas

• PostgreSQL, "Documentation" (versão 18): o manual oficial, com os capítulos sobre arrays, JSONB, busca textual, índices e MVCC. https://www.postgresql.org/docs/18/
• PostgreSQL, "JSON Types" e "GIN Indexes": a diferença entre json e jsonb, os operadores e as classes de operadores de índice, como a jsonb_path_ops. https://www.postgresql.org/docs/18/datatype-json.html
• PostgreSQL, "Full Text Search": o capítulo sobre tsvector, tsquery, dicionários e ranking. https://www.postgresql.org/docs/18/textsearch.html
• PostgreSQL, "Concurrency Control": o MVCC, os níveis de isolamento e os bloqueios explícitos, incluindo o SELECT ... FOR UPDATE. https://www.postgresql.org/docs/18/mvcc.html
• Docker Hub, imagem oficial "postgres": as variáveis de ambiente, os volumes e as mudanças da imagem do PostgreSQL 18. https://hub.docker.com/_/postgres
• Martin Kleppmann, Designing Data-Intensive Applications (O'Reilly, 2017), capítulo 7: a melhor explicação acessível dos níveis de isolamento e das anomalias de concorrência, incluindo a atualização perdida.

Exercícios

Exercício 1

As duas consultas abaixo devolvem as mesmas trilhas. Explique por que só a primeira pode usar o índice idx_trilha_etiquetas e reescreva a segunda para que use.

SELECT nome FROM trilha WHERE etiquetas @> '{mirante}';
SELECT nome FROM trilha WHERE 'mirante' = ANY (etiquetas);

Exercício 2

Um colega propõe guardar tudo da trilha num único campo JSONB: nome, distância, dificuldade, pontos de interesse e avaliações. Liste o que se perde em relação ao esquema da aula.

Exercício 3

No experimento das visitas, a forma "ler e gravar" perdeu quase 90% das visitas, e não 50% ou 10%. Por que a perda foi tão grande, e por que ela seria menor com duas threads em vez de dez?

Exercício 4

Implemente o bloqueio otimista na entidade Trilha com @Version e descreva o que acontece quando duas transações alteram a mesma trilha ao mesmo tempo. Em que situações ele é preferível ao FOR UPDATE?

@Version
private long versao;

-- V4__versao_da_trilha.sql
ALTER TABLE trilha ADD COLUMN versao BIGINT NOT NULL DEFAULT 0;

Exercício 5

O id da trilha inserida pelo programa Java foi 200005, embora a tabela tivesse só cinco trilhas. Explique por que o PostgreSQL não reaproveita os identificadores das linhas apagadas nem os de inserções desfeitas, e por que isso é uma decisão de desempenho.