PostgreSQL com PHP

O MySQL é o banco de dados mais comum no ecossistema PHP, mas o PostgreSQL é o mais poderoso. Enquanto o MySQL foi por muito tempo otimizado para leitura rápida e simplicidade, o PostgreSQL nasceu com foco em conformidade com o padrão SQL, integridade de dados e recursos avançados que outros bancos levaram anos para implementar — ou nunca implementaram.

Neste artigo vamos conectar o PHP ao PostgreSQL usando PDO, explorar os tipos de dados nativos do Postgres que não existem no MySQL, trabalhar com JSONB para dados semiestruturados e entender quando faz sentido escolher o PostgreSQL em vez do MySQL.

Por que PostgreSQL?

O PostgreSQL, frequentemente chamado apenas de "Postgres", é um sistema de banco de dados relacional de código aberto com mais de 35 anos de desenvolvimento ativo. Algumas vantagens concretas sobre o MySQL:

Tipos de dados ricos. O Postgres suporta arrays nativos, tipos geométricos (ponto, linha, polígono), ranges (intervalo de datas, de inteiros), tipos de rede (inet, cidr), e o JSONB — uma versão indexável e eficiente do JSON. O MySQL tem JSON desde 2015, mas a implementação do Postgres é significativamente mais poderosa.

Conformidade com SQL. O Postgres implementa o padrão SQL de forma muito mais completa, incluindo Common Table Expressions (CTEs) recursivos, window functions avançadas, e suporte a transações DDL (você pode fazer rollback de um CREATE TABLE, por exemplo).

Extensibilidade. O Postgres permite criar seus próprios tipos de dados, operadores e funções. Extensions como PostGIS (para dados geográficos), pgvector (para embeddings de IA), e TimescaleDB (para séries temporais) transformam o Postgres em um banco especializado quando necessário.

Confiabilidade. O Postgres tem reputação sólida de nunca perder dados. Seu sistema MVCC (Multi-Version Concurrency Control) é robusto e bem testado.

Instalação e configuração

No Linux (Ubuntu/Debian):

sudo apt install postgresql postgresql-contrib php-pgsql
sudo systemctl start postgresql

Criar banco e usuário:

sudo -u postgres psql

-- Dentro do psql:
CREATE DATABASE loja;
CREATE USER app_user WITH PASSWORD 'senha123';
GRANT ALL PRIVILEGES ON DATABASE loja TO app_user;

-- A partir do PostgreSQL 15 o GRANT acima não basta. Aquela versão removeu
-- a permissão de criação que o schema public dava a todos os usuários, e
-- sem a linha abaixo o app_user conecta normalmente, mas o primeiro
-- CREATE TABLE morre com "permission denied for schema public".
\c loja
GRANT ALL ON SCHEMA public TO app_user;
\q

Verificar conexão:

psql -h localhost -U app_user -d loja

Conectando com PDO

A beleza do PDO é que a mudança de MySQL para PostgreSQL exige apenas trocar o DSN. O resto do código permanece idêntico:

<?php
declare(strict_types=1);

// MySQL seria: mysql:host=localhost;dbname=loja;charset=utf8mb4
$dsn = 'pgsql:host=localhost;port=5432;dbname=loja';

try {
    $pdo = new PDO(
        dsn:      $dsn,
        username: 'app_user',
        password: 'senha123',
        options:  [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ]
    );

    echo "Conectado ao PostgreSQL com sucesso!\n";

} catch (PDOException $e) {
    throw new RuntimeException(
        "Falha na conexão: " . $e->getMessage()
    );
}

Note o PDO::ATTR_EMULATE_PREPARES => false. No PostgreSQL, isso é especialmente importante: garante que o PDO use prepared statements reais do servidor, o que melhora performance e segurança. O Postgres tem um cache interno de planos de execução para prepared statements, e desativar a emulação permite que esse cache seja utilizado.

Criando o schema

<?php

$pdo->exec("
    CREATE TABLE IF NOT EXISTS produtos (
        id          SERIAL PRIMARY KEY,
        -- O UNIQUE não é enfeite: o ON CONFLICT (nome) do UPSERT, mais
        -- adiante, exige uma constraint que cubra exatamente essa coluna. Sem
        -- ela o Postgres recusa com "there is no unique or exclusion
        -- constraint matching the ON CONFLICT specification".
        nome        VARCHAR(200)    NOT NULL UNIQUE,
        preco       NUMERIC(10, 2)  NOT NULL CHECK (preco >= 0),
        estoque     INTEGER         NOT NULL DEFAULT 0,
        ativo       BOOLEAN         NOT NULL DEFAULT TRUE,
        tags        TEXT[],
        metadata    JSONB,
        criado_em   TIMESTAMPTZ     NOT NULL DEFAULT NOW()
    )
");

Observe alguns tipos específicos do PostgreSQL: - SERIAL: auto-incremento (equivalente a AUTO_INCREMENT do MySQL). No Postgres moderno, prefere-se GENERATED ALWAYS AS IDENTITY, mas SERIAL ainda é amplamente usado. - NUMERIC(10, 2): tipo exato para dinheiro, sem erros de ponto flutuante. - BOOLEAN: tipo nativo no Postgres (no MySQL é TINYINT(1)). - TEXT[]: array de strings. - JSONB: JSON armazenado em formato binário, indexável. - TIMESTAMPTZ: timestamp com timezone (melhor prática para aplicações multi-fuso).

Arrays nativos

<?php

// Inserindo um produto com array de tags
$stmt = $pdo->prepare("
    INSERT INTO produtos (nome, preco, estoque, tags)
    VALUES (:nome, :preco, :estoque, :tags)
    RETURNING id, criado_em
");

// Arrays são passados como strings no formato do Postgres
$stmt->execute([
    ':nome'    => 'Teclado Mecânico',
    ':preco'   => '299.90',
    ':estoque' => 15,
    ':tags'    => '{periférico,teclado,mecânico}',
]);

$resultado = $stmt->fetch();
echo "Produto criado com ID: {$resultado['id']}\n";
echo "Criado em: {$resultado['criado_em']}\n";

// Note o RETURNING — permite retornar dados da linha inserida
// sem fazer um SELECT separado. O MySQL não tem isso.

// Consultando e decodificando o array
$stmt = $pdo->prepare("SELECT nome, tags FROM produtos WHERE id = :id");
$stmt->execute([':id' => $resultado['id']]);
$produto = $stmt->fetch();

// O PDO devolve o array como string: {periférico,teclado,mecânico}
// O parse fica por nossa conta, e tem duas pegadinhas.
//
// A primeira é o array vazio: o Postgres devolve "{}", e str_getcsv("") não
// retorna [] e sim [null] — um elemento nulo que segue adiante no foreach
// como se fosse uma tag de verdade.
//
// A segunda é que, desde o PHP 8.4, omitir o quarto argumento de str_getcsv
// emite Deprecated: o valor padrão vai mudar. Para array do Postgres o
// escape é a barra invertida, então é ela que se declara.
$interior = trim($produto['tags'], '{}');
$tags     = $interior === '' ? [] : str_getcsv($interior, ',', '"', '\\');
print_r($tags); // ['periférico', 'teclado', 'mecânico']

// Buscar produtos que contenham uma tag específica
$stmt = $pdo->prepare("
    SELECT nome, tags
    FROM produtos
    WHERE :tag = ANY(tags)
");
$stmt->execute([':tag' => 'mecânico']);

JSONB — JSON indexável

O JSONB é um dos recursos mais poderosos do PostgreSQL. Diferente do tipo JSON simples (que armazena o texto original), o JSONB armazena em formato binário parseado, permitindo indexação e consultas eficientes:

<?php

// Inserindo com JSONB
$metadata = json_encode([
    'fabricante' => 'Keychron',
    'switch'     => 'Brown',
    'bluetooth'  => true,
    'dimensoes'  => ['largura' => 36, 'altura' => 13, 'profundidade' => 12],
    'cores'      => ['preto', 'cinza'],
]);

$stmt = $pdo->prepare("
    UPDATE produtos
    SET metadata = :metadata::jsonb
    WHERE id = :id
");
$stmt->execute([':metadata' => $metadata, ':id' => 1]);

// Consultando campos específicos do JSONB com operador ->
$stmt = $pdo->query("
    SELECT
        nome,
        metadata->>'fabricante'        AS fabricante,
        metadata->>'switch'            AS switch,
        (metadata->>'bluetooth')::bool AS bluetooth,
        metadata->'dimensoes'->>'largura' AS largura
    FROM produtos
    WHERE metadata->>'fabricante' = 'Keychron'
      AND (metadata->>'bluetooth')::bool = true
");

while ($row = $stmt->fetch()) {
    echo "{$row['nome']} — {$row['fabricante']}, switch {$row['switch']}\n";
}

// Criando índice GIN para consultas JSONB eficientes
$pdo->exec("CREATE INDEX idx_produtos_metadata ON produtos USING gin(metadata)");

// Com o índice, consultas usando @> (contém) são muito eficientes:
$stmt = $pdo->prepare("
    SELECT nome FROM produtos
    WHERE metadata @> :filtro::jsonb
");
$stmt->execute([':filtro' => json_encode(['bluetooth' => true])]);

Transações e recursos avançados

<?php

// CTEs (Common Table Expressions) — disponíveis no MySQL 8+
// mas muito mais comuns e idiomáticas no PostgreSQL
$stmt = $pdo->query("
    WITH
    produtos_ativos AS (
        SELECT id, nome, preco, estoque
        FROM produtos
        WHERE ativo = true AND estoque > 0
    ),
    estatisticas AS (
        SELECT
            COUNT(*)            AS total,
            AVG(preco)          AS preco_medio,
            SUM(estoque)        AS estoque_total
        FROM produtos_ativos
    )
    SELECT p.nome, p.preco, e.preco_medio
    FROM produtos_ativos p, estatisticas e
    ORDER BY p.preco DESC
    LIMIT 10
");

// UPSERT nativo com ON CONFLICT
$stmt = $pdo->prepare("
    INSERT INTO produtos (nome, preco, estoque)
    VALUES (:nome, :preco, :estoque)
    ON CONFLICT (nome)
    DO UPDATE SET
        preco    = EXCLUDED.preco,
        estoque  = EXCLUDED.estoque,
        ativo    = true
    RETURNING id, (xmax = 0) AS inserido
");
// xmax = 0 indica que foi um INSERT; xmax != 0 indica UPDATE

Quando usar PostgreSQL vs MySQL

Critério	PostgreSQL	MySQL
Consultas complexas	✅ Excelente	✅ Bom
Dados geográficos (PostGIS)	✅ Referência	❌ Limitado
JSONB/documentos híbridos	✅ Excelente	⚠️ Funcional
Performance de leitura simples	✅ Ótimo	✅ Ótimo
Ecossistema WordPress/legado	⚠️ Menor	✅ Dominante
Hospedagem compartilhada	⚠️ Menos disponível	✅ Ubíquo
Conformidade SQL	✅ Excelente	⚠️ Parcial

Trocar de banco no PDO é mesmo uma questão de DSN, e é justamente essa facilidade que esconde a decisão real: o código que você escreve depois é que amarra você a um dos dois. RETURNING, ON CONFLICT, TEXT[], JSONB com índice GIN — nada disso tem tradução direta no MySQL, e usar esses recursos é uma escolha consciente de ficar no Postgres, não um detalhe de sintaxe.

Vale reparar numa diferença que não aparece em tabela comparativa nenhuma: no Postgres, CREATE TABLE e ALTER TABLE vivem dentro da transação e podem ser desfeitos com ROLLBACK. No MySQL, a DDL comita sozinha e encerra a transação em silêncio. Quem escreve migrations sente essa diferença no pior momento possível — quando a terceira instrução falha e as duas anteriores já estão gravadas.

Fontes e leituras recomendadas

Documentação oficial do PostgreSQL: https://www.postgresql.org/docs/
PHP Manual — PDO_PGSQL: https://www.php.net/manual/pt_BR/ref.pdo-pgsql.php
PostgreSQL vs MySQL — comparação técnica detalhada: https://www.postgresql.org/about/featurematrix/
JSONB no PostgreSQL — guia completo: https://www.postgresql.org/docs/current/datatype-json.html
PostgreSQL — indices: B-tree, GIN, parciais e por expressao: https://www.postgresql.org/docs/current/indexes.html
Use The Index, Luke — como o planejador decide usar (ou ignorar) o indice: https://use-the-index-luke.com/

Exercícios

Exercício 1

Instale o PostgreSQL localmente e conecte via PDO. Crie um banco biblioteca com tabela livros (id, titulo, autores TEXT[], generos TEXT[], metadata JSONB, publicado_em DATE).

Ver resposta

✓ Resposta: Use sempre JSONB, nunca JSON: o primeiro guarda em formato binário decomposto, aceita índice GIN e compara chaves rapidamente; o segundo guarda o texto cru e reprocessa a cada leitura. O índice parcial com WHERE metadata ? 'isbn' permite que livros sem ISBN convivam sem violar o unique.

<?php

declare(strict_types=1);

// createdb biblioteca     (ou: CREATE DATABASE biblioteca;)

final class ConexaoPostgres
{
    private static ?PDO $instancia = null;

    public static function obter(): PDO
    {
        if (self::$instancia instanceof PDO) {
            return self::$instancia;
        }

        // Note o DSN: `pgsql:` e a porta 5432 (MySQL usa 3306).
        $dsn = sprintf(
            'pgsql:host=%s;port=%s;dbname=%s;options=--client_encoding=UTF8',
            getenv('PG_HOST') ?: '127.0.0.1',
            getenv('PG_PORT') ?: '5432',
            getenv('PG_DB')   ?: 'biblioteca',
        );

        self::$instancia = new PDO($dsn, getenv('PG_USER') ?: 'postgres',
                                   getenv('PG_PASS') ?: '', [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ]);

        return self::$instancia;
    }
}

$pdo = ConexaoPostgres::obter();

$pdo->exec(<<<SQL
    CREATE TABLE IF NOT EXISTS livros (
        id            BIGSERIAL PRIMARY KEY,
        titulo        TEXT   NOT NULL,
        -- TEXT[]: array nativo. No MySQL isso viraria tabela de ligação.
        autores       TEXT[] NOT NULL DEFAULT '{}',
        generos       TEXT[] NOT NULL DEFAULT '{}',
        -- JSONB (binário, indexável) e não JSON (texto). Sempre JSONB.
        metadata      JSONB  NOT NULL DEFAULT '{}'::jsonb,
        publicado_em  DATE,
        criado_em     TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
SQL);

// Garante que o ISBN dentro do JSONB seja único — é a chave do UPSERT
// do exercício 4. Índice único sobre expressão: só o Postgres faz isso
// com essa naturalidade.
$pdo->exec(<<<SQL
    CREATE UNIQUE INDEX IF NOT EXISTS uk_livros_isbn
        ON livros ((metadata->>'isbn'))
        WHERE metadata ? 'isbn'
SQL);

echo 'Schema pronto.', PHP_EOL;

Exercício 2

Insira 5 livros com arrays de autores e metadados JSON (editora, ISBN, numero_paginas). Use RETURNING para capturar o id gerado.

Ver resposta

✓ Resposta: RETURNING resolve numa ida o que no MySQL exige lastInsertId() mais um SELECT — e funciona também em UPDATE e DELETE. A conversão de array é a pegadinha: o Postgres usa {"a","b"}, não o formato de array do PHP nem JSON.

<?php

declare(strict_types=1);

final class LivroRepositorio
{
    public function __construct(private readonly PDO $pdo) {}

    /**
     * RETURNING devolve a linha inserida na MESMA ida ao banco — não existe
     * lastInsertId() aqui, e nem faz falta: dá para trazer qualquer coluna,
     * inclusive as preenchidas por DEFAULT.
     */
    public function inserir(array $livro): array
    {
        $stmt = $this->pdo->prepare(<<<SQL
            INSERT INTO livros (titulo, autores, generos, metadata, publicado_em)
            VALUES (:titulo, :autores, :generos, :metadata::jsonb, :publicado)
            RETURNING id, titulo, criado_em
        SQL);

        $stmt->execute([
            ':titulo'    => $livro['titulo'],
            // PHP não converte array para array do Postgres: o formato é
            // {"a","b"} — com chaves, não colchetes.
            ':autores'   => $this->paraArrayPg($livro['autores']),
            ':generos'   => $this->paraArrayPg($livro['generos']),
            ':metadata'  => json_encode($livro['metadata'], JSON_THROW_ON_ERROR | JSON_UNESCAPED_UNICODE),
            ':publicado' => $livro['publicado_em'],
        ]);

        return $stmt->fetch();
    }

    /** @param string[] $itens */
    private function paraArrayPg(array $itens): string
    {
        $escapados = array_map(
            // Aspas e barras dentro do elemento precisam de escape.
            static fn(string $v): string => '"' . str_replace(['\\', '"'], ['\\\\', '\\"'], $v) . '"',
            $itens,
        );

        return '{' . implode(',', $escapados) . '}';
    }

    /** O caminho de volta: '{"a","b"}' → ['a', 'b'] */
    public static function deArrayPg(?string $bruto): array
    {
        if ($bruto === null || $bruto === '{}') {
            return [];
        }

        return str_getcsv(trim($bruto, '{}'), ',', '"');
    }
}

$repositorio = new LivroRepositorio($pdo);

$livros = [
    [
        'titulo'   => 'Grande Sertão: Veredas',
        'autores'  => ['João Guimarães Rosa'],
        'generos'  => ['romance', 'literatura brasileira'],
        'metadata' => ['editora' => 'Companhia das Letras', 'isbn' => '9788535911862',
                       'numero_paginas' => 624, 'preco' => 89.90],
        'publicado_em' => '1956-05-01',
    ],
    [
        'titulo'   => 'Ensaio sobre a Cegueira',
        'autores'  => ['José Saramago'],
        'generos'  => ['romance', 'distopia'],
        'metadata' => ['editora' => 'Companhia das Letras', 'isbn' => '9788535911871',
                       'numero_paginas' => 312, 'preco' => 64.90],
        'publicado_em' => '1995-10-20',
    ],
    [
        'titulo'   => 'O Cortiço',
        'autores'  => ['Aluísio Azevedo'],
        'generos'  => ['naturalismo', 'literatura brasileira'],
        'metadata' => ['editora' => 'Ática', 'isbn' => '9788508133055',
                       'numero_paginas' => 232, 'preco' => 42.50],
        'publicado_em' => '1890-01-01',
    ],
    [
        'titulo'   => 'Clean Code',
        'autores'  => ['Robert C. Martin'],
        'generos'  => ['tecnologia', 'programação'],
        'metadata' => ['editora' => 'Alta Books', 'isbn' => '9788576082675',
                       'numero_paginas' => 425, 'preco' => 129.00],
        'publicado_em' => '2009-08-01',
    ],
    [
        'titulo'   => 'Design Patterns',
        'autores'  => ['Erich Gamma', 'Richard Helm', 'Ralph Johnson', 'John Vlissides'],
        'generos'  => ['tecnologia', 'programação', 'arquitetura'],
        'metadata' => ['editora' => 'Bookman', 'isbn' => '9788573076103',
                       'numero_paginas' => 364, 'preco' => 189.00],
        'publicado_em' => '1994-10-31',
    ],
];

foreach ($livros as $livro) {
    $inserido = $repositorio->inserir($livro);

    printf("#%d %s (%s)%s", $inserido['id'], $inserido['titulo'],
           $inserido['criado_em'], PHP_EOL);
}

Exercício 3

Escreva uma query que use ANY() para buscar livros de um determinado gênero. Depois, use @> para buscar pelo JSONB (ex: todos da editora "Companhia das Letras").

Ver resposta

✓ Resposta: Cuidado com o operador ? do JSONB dentro do PDO: ele colide com o placeholder posicional. Com EMULATE_PREPARES = false e parâmetros nomeados, como aqui, funciona; misturando com ? posicional, o driver se confunde. E note: @> em array exige todos, && aceita qualquer.

<?php

declare(strict_types=1);

final class BuscaLivros
{
    public function __construct(private readonly PDO $pdo) {}

    /** ANY(): "o valor está em algum elemento do array?" */
    public function porGenero(string $genero): array
    {
        $stmt = $this->pdo->prepare(<<<SQL
            SELECT id, titulo, autores, generos, metadata->>'editora' AS editora
              FROM livros
             WHERE :genero = ANY(generos)
             ORDER BY titulo
        SQL);

        $stmt->execute([':genero' => $genero]);

        return $stmt->fetchAll();
    }

    /**
     * @> em array = "contém TODOS estes elementos" (não qualquer um).
     * Use quando precisar de romance E distopia, não romance OU distopia.
     */
    public function comTodosOsGeneros(array $generos): array
    {
        $stmt = $this->pdo->prepare(<<<SQL
            SELECT id, titulo, generos
              FROM livros
             WHERE generos @> :generos::text[]
             ORDER BY titulo
        SQL);

        $stmt->execute([':generos' => '{' . implode(',', array_map(
            static fn(string $g): string => '"' . $g . '"', $generos)) . '}']);

        return $stmt->fetchAll();
    }

    /** && em array = "tem QUALQUER um destes" (interseção). */
    public function comAlgumGenero(array $generos): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT id, titulo FROM livros WHERE generos && :generos::text[]'
        );

        $stmt->execute([':generos' => '{' . implode(',', $generos) . '}']);

        return $stmt->fetchAll();
    }

    /** @> em JSONB = "o documento contém este sub-documento". */
    public function porEditora(string $editora): array
    {
        $stmt = $this->pdo->prepare(<<<SQL
            SELECT id, titulo,
                   metadata->>'isbn'                    AS isbn,
                   (metadata->>'numero_paginas')::int   AS paginas,
                   (metadata->>'preco')::numeric        AS preco
              FROM livros
             WHERE metadata @> :filtro::jsonb
             ORDER BY titulo
        SQL);

        // O filtro é um JSON: {"editora": "Companhia das Letras"}
        $stmt->execute([':filtro' => json_encode(['editora' => $editora], JSON_UNESCAPED_UNICODE)]);

        return $stmt->fetchAll();
    }

    /** Combinando array, JSONB e comparação numérica dentro do JSON. */
    public function tecnicosCaros(float $precoMinimo): array
    {
        $stmt = $this->pdo->prepare(<<<SQL
            SELECT titulo,
                   array_to_string(autores, ', ')     AS autores,
                   (metadata->>'preco')::numeric      AS preco
              FROM livros
             WHERE generos @> '{"tecnologia"}'::text[]
               AND (metadata->>'preco')::numeric >= :preco
             ORDER BY preco DESC
        SQL);

        $stmt->execute([':preco' => $precoMinimo]);

        return $stmt->fetchAll();
    }

    /** ? testa a EXISTÊNCIA da chave — nada a ver com o valor. */
    public function semIsbn(): array
    {
        return $this->pdo->query(
            "SELECT id, titulo FROM livros WHERE NOT (metadata ? 'isbn')"
        )->fetchAll();
    }
}

$busca = new BuscaLivros($pdo);

print_r($busca->porGenero('literatura brasileira'));
// Grande Sertão: Veredas, O Cortiço

print_r($busca->porEditora('Companhia das Letras'));
// Grande Sertão: Veredas, Ensaio sobre a Cegueira

print_r($busca->comTodosOsGeneros(['tecnologia', 'programação']));
// Clean Code, Design Patterns

print_r($busca->tecnicosCaros(150.00));
// Design Patterns — 189.00

Exercício 4

Implemente um UPSERT: se o livro (pelo ISBN no JSONB) já existir, atualize o preço; caso contrário, insira.

Ver resposta

✓ Resposta: Dois truques valiosos: xmax = 0 no RETURNING revela se a linha foi inserida ou atualizada, e IS DISTINCT FROM compara tratando NULL como valor (diferente de <>, que devolve NULL quando um lado é nulo — e aí a condição nunca é verdadeira).

<?php

declare(strict_types=1);

final class UpsertLivro
{
    public function __construct(private readonly PDO $pdo) {}

    /**
     * ON CONFLICT é atômico: não há janela entre "verificar se existe" e
     * "inserir". Duas requisições simultâneas com o mesmo ISBN produzem
     * uma inserção e uma atualização — nunca duas linhas nem erro.
     */
    public function salvar(array $livro): array
    {
        $stmt = $this->pdo->prepare(<<<SQL
            INSERT INTO livros (titulo, autores, generos, metadata, publicado_em)
            VALUES (:titulo, :autores::text[], :generos::text[], :metadata::jsonb, :publicado)
            -- O alvo é a MESMA expressão do índice único criado no ex. 1.
            ON CONFLICT ((metadata->>'isbn')) WHERE metadata ? 'isbn'
            DO UPDATE SET
                -- jsonb_set troca só o preço, preservando as outras chaves.
                metadata = livros.metadata || jsonb_build_object(
                    'preco', EXCLUDED.metadata->'preco'
                ),
                atualizado_em = NOW()
            RETURNING id,
                      titulo,
                      (metadata->>'preco')::numeric AS preco,
                      -- xmax = 0 identifica INSERT; != 0 identifica UPDATE.
                      (xmax = 0) AS foi_inserido
        SQL);

        $stmt->execute([
            ':titulo'    => $livro['titulo'],
            ':autores'   => '{' . implode(',', array_map(
                fn($a) => '"' . $a . '"', $livro['autores'])) . '}',
            ':generos'   => '{' . implode(',', array_map(
                fn($g) => '"' . $g . '"', $livro['generos'])) . '}',
            ':metadata'  => json_encode($livro['metadata'], JSON_UNESCAPED_UNICODE),
            ':publicado' => $livro['publicado_em'] ?? null,
        ]);

        return $stmt->fetch();
    }

    /** Variante: só atualiza se o preço realmente mudou. */
    public function salvarSePrecoMudou(array $livro): ?array
    {
        $stmt = $this->pdo->prepare(<<<SQL
            INSERT INTO livros (titulo, autores, generos, metadata, publicado_em)
            VALUES (:titulo, '{}'::text[], '{}'::text[], :metadata::jsonb, NULL)
            ON CONFLICT ((metadata->>'isbn')) WHERE metadata ? 'isbn'
            DO UPDATE SET
                metadata = livros.metadata || jsonb_build_object(
                    'preco', EXCLUDED.metadata->'preco'
                )
            -- Cláusula WHERE no DO UPDATE: evita escrita inútil, que geraria
            -- WAL, invalidaria cache e dispararia trigger à toa.
            WHERE livros.metadata->>'preco' IS DISTINCT FROM EXCLUDED.metadata->>'preco'
            RETURNING id, (metadata->>'preco')::numeric AS preco
        SQL);

        $stmt->execute([
            ':titulo'   => $livro['titulo'],
            ':metadata' => json_encode($livro['metadata'], JSON_UNESCAPED_UNICODE),
        ]);

        // Sem linha devolvida = o preço já era esse, nada foi feito.
        return $stmt->fetch() ?: null;
    }

    /** DO NOTHING: insere se não existir, e ignora em silêncio se existir. */
    public function inserirSeNaoExistir(array $livro): bool
    {
        $stmt = $this->pdo->prepare(<<<SQL
            INSERT INTO livros (titulo, metadata)
            VALUES (:titulo, :metadata::jsonb)
            ON CONFLICT ((metadata->>'isbn')) WHERE metadata ? 'isbn'
            DO NOTHING
        SQL);

        $stmt->execute([
            ':titulo'   => $livro['titulo'],
            ':metadata' => json_encode($livro['metadata'], JSON_UNESCAPED_UNICODE),
        ]);

        return $stmt->rowCount() > 0;
    }
}

$upsert = new UpsertLivro($pdo);

// Primeira vez: insere.
$r = $upsert->salvar([
    'titulo'  => 'Clean Code',
    'autores' => ['Robert C. Martin'],
    'generos' => ['tecnologia'],
    'metadata'=> ['isbn' => '9788576082675', 'editora' => 'Alta Books', 'preco' => 129.00],
]);
var_dump($r['foi_inserido']);   // true

// Segunda vez, preço diferente: atualiza só o preço.
$r = $upsert->salvar([
    'titulo'  => 'Clean Code',
    'autores' => ['Robert C. Martin'],
    'generos' => ['tecnologia'],
    'metadata'=> ['isbn' => '9788576082675', 'preco' => 149.90],
]);
var_dump($r['foi_inserido']);   // false
echo $r['preco'], PHP_EOL;      // 149.90

Exercício 5

Desafio: crie um índice GIN no campo generos (array) e no campo metadata (JSONB). Use EXPLAIN ANALYZE para comparar o custo das queries com e sem índice.

Ver resposta

✓ Resposta: Três detalhes que fazem a medição valer: popular com volume real (com poucas linhas o Seq Scan vence sempre), rodar ANALYZE antes de medir, e saber que jsonb_path_ops gera índice bem menor que o GIN padrão — mas só serve para @>, não para ?.

<?php

declare(strict_types=1);

final class AnalisadorDeIndices
{
    public function __construct(private readonly PDO $pdo) {}

    public function popular(int $quantidade = 100_000): void
    {
        // Volume é indispensável: com 5 linhas o Postgres ignora qualquer
        // índice, porque ler a tabela inteira é mais barato. Medir índice
        // em tabela pequena não mede nada.
        $this->pdo->exec(<<<SQL
            INSERT INTO livros (titulo, autores, generos, metadata, publicado_em)
            SELECT
                'Livro ' || g,
                ARRAY['Autor ' || (g % 500)],
                (ARRAY['romance','tecnologia','historia','biografia','poesia'])[1 + (g % 5) : 2 + (g % 3)],
                jsonb_build_object(
                    'editora', (ARRAY['Companhia das Letras','Ática','Bookman'])[1 + (g % 3)],
                    'isbn', '978' || lpad(g::text, 10, '0'),
                    'numero_paginas', 100 + (g % 800),
                    'preco', round((20 + (g % 200))::numeric, 2)
                ),
                DATE '1950-01-01' + (g % 27000)
            FROM generate_series(1, {$quantidade}) g
            ON CONFLICT DO NOTHING
        SQL);

        // Sem ANALYZE, o planejador trabalha com estatísticas velhas e pode
        // escolher o plano errado mesmo com o índice existindo.
        $this->pdo->exec('ANALYZE livros');
    }

    public function explicar(string $sql, array $params = []): array
    {
        // ANALYZE executa de verdade e mede; BUFFERS mostra I/O.
        $stmt = $this->pdo->prepare(
            'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' . $sql
        );
        $stmt->execute($params);

        $plano = json_decode($stmt->fetchColumn(), true)[0];

        return [
            'tipo'          => $plano['Plan']['Node Type'],
            'custo_total'   => $plano['Plan']['Total Cost'],
            'tempo_ms'      => $plano['Execution Time'],
            'linhas'        => $plano['Plan']['Actual Rows'],
            'blocos_lidos'  => $plano['Plan']['Shared Read Blocks'] ?? 0,
        ];
    }

    public function comparar(string $rotulo, string $sql, array $params, array $indices): void
    {
        echo PHP_EOL, "═══ {$rotulo} ═══", PHP_EOL;

        foreach ($indices as $indice) {
            $this->pdo->exec("DROP INDEX IF EXISTS {$indice['nome']}");
        }

        $this->pdo->exec('ANALYZE livros');
        $sem = $this->explicar($sql, $params);

        foreach ($indices as $indice) {
            $this->pdo->exec($indice['sql']);
        }

        $this->pdo->exec('ANALYZE livros');
        $com = $this->explicar($sql, $params);

        printf("  SEM índice: %-16s %8.2f ms  (%d blocos)%s",
               $sem['tipo'], $sem['tempo_ms'], $sem['blocos_lidos'], PHP_EOL);
        printf("  COM índice: %-16s %8.2f ms  (%d blocos)%s",
               $com['tipo'], $com['tempo_ms'], $com['blocos_lidos'], PHP_EOL);

        $ganho = $sem['tempo_ms'] > 0 ? $sem['tempo_ms'] / max($com['tempo_ms'], 0.001) : 1;

        printf("  → %.1fx mais rápido%s", $ganho, PHP_EOL);
    }
}

$analisador = new AnalisadorDeIndices($pdo);
$analisador->popular(100_000);

// ---------- GIN em array --------------------------------------------------
$analisador->comparar(
    'Busca por gênero em TEXT[]',
    "SELECT id, titulo FROM livros WHERE generos @> ARRAY[:genero]::text[]",
    [':genero' => 'tecnologia'],
    [['nome' => 'idx_livros_generos_gin',
      'sql'  => 'CREATE INDEX idx_livros_generos_gin ON livros USING GIN (generos)']],
);

// ---------- GIN em JSONB --------------------------------------------------
$analisador->comparar(
    'Busca por editora em JSONB',
    "SELECT id, titulo FROM livros WHERE metadata @> :filtro::jsonb",
    [':filtro' => '{"editora":"Bookman"}'],
    [['nome' => 'idx_livros_metadata_gin',
      'sql'  => 'CREATE INDEX idx_livros_metadata_gin ON livros USING GIN (metadata)']],
);

// ---------- jsonb_path_ops: menor e mais rápido, só para @> --------------
$analisador->comparar(
    'Busca por editora com jsonb_path_ops',
    "SELECT id, titulo FROM livros WHERE metadata @> :filtro::jsonb",
    [':filtro' => '{"editora":"Ática"}'],
    [['nome' => 'idx_livros_metadata_path',
      'sql'  => 'CREATE INDEX idx_livros_metadata_path
                     ON livros USING GIN (metadata jsonb_path_ops)']],
);

echo PHP_EOL, 'Tamanho dos índices:', PHP_EOL;

foreach ($pdo->query(<<<SQL
    SELECT indexrelname AS indice, pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho
      FROM pg_stat_user_indexes WHERE relname = 'livros' ORDER BY pg_relation_size(indexrelid) DESC
SQL) as $linha) {
    printf("  %-32s %s%s", $linha['indice'], $linha['tamanho'], PHP_EOL);
}

// ═══ Busca por gênero em TEXT[] ═══
//   SEM índice: Seq Scan            48.21 ms  (1042 blocos)
//   COM índice: Bitmap Heap Scan     3.14 ms  (68 blocos)
//   → 15.4x mais rápido

Por que PostgreSQL?

Instalação e configuração

Conectando com PDO

Criando o schema

Arrays nativos

JSONB — JSON indexável

Transações e recursos avançados

Quando usar PostgreSQL vs MySQL

Fontes e leituras recomendadas

Exercícios

Exercício 1

Exercício 2

Exercício 3

Exercício 4

Exercício 5

Mais em PHP

Segurança Avançada em PHP

Deploy e Ambiente de Produção

Query Builder e Migrations