PostgreSQL 17: novos recursos para desenvolvedores de aplicativos

O PostgreSQL 17 chegou com um conjunto de recursos que finalmente torna populares vários recursos SQL há muito solicitados. Embora os administradores de banco de dados se concentrem em melhorias de vácuo e replicação lógica, os desenvolvedores de aplicativos obtêm adições igualmente atraentes: JSON_TABLE para consultar JSON como dados relacionais, melhorias significativas nas instruções MERGE, novas funções agregadas e ganhos de desempenho que afetam todas as consultas.

Este guia se concentra exclusivamente nos recursos que mudam a forma como você escreve o código do aplicativo — os padrões SQL, pontos de integração ORM e decisões arquitetônicas que se tornam possíveis ou melhores no PostgreSQL 17.

Principais conclusões

• JSON_TABLE agora é padrão — consulte arrays JSON como se fossem tabelas sem funções personalizadas
• A instrução MERGE obteve a cláusula RETURNING — obtém as linhas afetadas sem uma segunda consulta
• ANY_VALUE() agregado torna o agrupamento de consultas mais simples sem listas GROUP BY completas
• Melhorias incrementais na classificação reduzem o custo do plano de consulta em dados solicitados
• O comando COPY suporta ON_ERROR para pular linhas incorretas em vez de falhar totalmente
• A visualização pg_stat_io fornece estatísticas precisas de E/S por relação para análise de desempenho
• A replicação lógica agora suporta sequências, fechando uma lacuna importante para configurações ativas-ativas
• Melhorias no VACUUM reduzem o acúmulo de inchaço — menos autoaspiradores de emergência

---

JSON_TABLE: Consultando JSON como dados relacionais

O recurso SQL de maior impacto para desenvolvedores de aplicativos é JSON_TABLE. Os aplicativos frequentemente armazenam matrizes ou objetos aninhados em colunas JSONB. Antes do PostgreSQL 17, consultar esses dados exigia chamadas de função jsonb_array_elements estranhas com junções laterais.

Considere uma coluna JSONB armazenando os itens de linha de um pedido:

-- orders.metadata column:
-- {
--   "items": [
--     {"sku": "PROD-001", "qty": 2, "price": 49.99},
--     {"sku": "PROD-002", "qty": 1, "price": 129.99}
--   ]
-- }

Antes do PostgreSQL 17 (usando jsonb_array_elements):

SELECT
  o.id,
  o.created_at,
  item->>'sku' AS sku,
  (item->>'qty')::int AS qty,
  (item->>'price')::numeric AS price
FROM orders o,
     jsonb_array_elements(o.metadata->'items') AS item
WHERE o.organization_id = $1;

PostgreSQL 17 com JSON_TABLE:

SELECT
  o.id,
  o.created_at,
  items.sku,
  items.qty,
  items.price
FROM orders o,
     JSON_TABLE(
       o.metadata,
       '$.items[*]'
       COLUMNS (
         sku VARCHAR PATH '$.sku',
         qty INTEGER PATH '$.qty',
         price NUMERIC PATH '$.price'
       )
     ) AS items
WHERE o.organization_id = $1;

A versão JSON_TABLE é mais legível, fornece tipos de colunas explícitos e participa da otimização de junção do planejador de consultas. Ele também lida com chaves ausentes normalmente com as opções de coluna DEFAULT NULL ON EMPTY e DEFAULT 0 ON ERROR.

Exemplo mais complexo — JSON aninhado com tratamento de erros:

SELECT
  products.id,
  specs.weight_kg,
  specs.color,
  specs.dimensions_cm
FROM products,
     JSON_TABLE(
       products.specifications,
       '$.specs'
       COLUMNS (
         weight_kg NUMERIC PATH '$.weight'
           DEFAULT 0 ON EMPTY DEFAULT 0 ON ERROR,
         color VARCHAR PATH '$.color'
           DEFAULT 'unknown' ON EMPTY,
         dimensions_cm VARCHAR PATH '$.dimensions'
           DEFAULT NULL ON EMPTY
       )
     ) AS specs
WHERE products.organization_id = $1;

---

MERGE com RETURNING

A instrução MERGE (introduzida no PostgreSQL 15) permite lógica condicional de inserção ou atualização em uma única instrução. O PostgreSQL 17 adiciona RETURNING, o que antes era impossível:

-- Before PostgreSQL 17: MERGE had no RETURNING
-- Required a separate SELECT after MERGE

-- PostgreSQL 17: MERGE with RETURNING
MERGE INTO contacts AS target
USING (
  SELECT $1::uuid AS organization_id,
         $2::varchar AS email,
         $3::varchar AS name
) AS source
ON target.organization_id = source.organization_id
   AND target.email = source.email
WHEN MATCHED THEN
  UPDATE SET
    name = source.name,
    updated_at = NOW()
WHEN NOT MATCHED THEN
  INSERT (organization_id, email, name, created_at, updated_at)
  VALUES (source.organization_id, source.email, source.name, NOW(), NOW())
RETURNING
  id,
  email,
  created_at,
  CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation;

O truque xmax = 0 identifica se a linha foi inserida (xmax = 0) ou atualizada (xmax! = 0). Esse padrão é essencial para pipelines de importação de dados idempotentes, nos quais você precisa saber o que realmente mudou.

Usando MERGE no Drizzle (via SQL bruto até que o Drizzle adicione suporte nativo a MERGE):

import { sql } from 'drizzle-orm';
import { db } from '@ecosire/db';

async function upsertContact(
  orgId: string,
  email: string,
  name: string
) {
  const result = await db.execute(sql`
    MERGE INTO contacts AS target
    USING (SELECT ${orgId}::uuid, ${email}::varchar, ${name}::varchar)
      AS source(organization_id, email, name)
    ON target.organization_id = source.organization_id
       AND target.email = source.email
    WHEN MATCHED THEN
      UPDATE SET name = source.name, updated_at = NOW()
    WHEN NOT MATCHED THEN
      INSERT (organization_id, email, name, created_at, updated_at)
      VALUES (source.organization_id, source.email, source.name, NOW(), NOW())
    RETURNING id, email, CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS op
  `);

  return result.rows[0];
}

---

ANY_VALUE() Agregado

Uma frustração comum de GROUP BY no PostgreSQL: você deseja agrupar por uma coluna, mas também selecionar uma coluna não agregada da mesma linha. Anteriormente, você tinha que adicioná-lo a GROUP BY (alterando a semântica de agrupamento) ou usar MAX() como solução alternativa.

-- Before: Awkward workaround
SELECT
  department_id,
  MAX(employee_name) AS any_employee_name, -- Wrong semantic
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- PostgreSQL 17: ANY_VALUE() expresses intent clearly
SELECT
  department_id,
  ANY_VALUE(employee_name) AS sample_employee, -- Pick any one
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

ANY_VALUE() é semanticamente correto - diz explicitamente "Não me importa qual valor do grupo, apenas me dê um." Isto é útil para consultas de diagnóstico, depuração e casos em que qualquer valor representativo é aceitável.

---

Melhorias de classificação incremental

O PostgreSQL 17 melhorou significativamente o desempenho da classificação incremental. Isso afeta qualquer consulta com ORDER BY em uma coluna de índice não inicial:

-- This benefits from incremental sorting in PostgreSQL 17
SELECT *
FROM orders
WHERE organization_id = $1    -- Index on (organization_id, created_at)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Before 17: Sort had to materialize all matching rows
-- After 17: Sort processes data in chunks, memory usage drops significantly

Para aplicativos com muita paginação — painéis, visualizações de lista, relatórios — essa melhoria reduz a pressão da memória e melhora os tempos de resposta sem quaisquer alterações nas consultas.

---

COPIAR com ON_ERROR

Os pipelines de importação de dados frequentemente encontram linhas malformadas. Antes do PostgreSQL 17, uma única linha incorreta falhava em toda a operação COPY. Agora você pode pular linhas ruins:

-- Skip rows with format errors instead of failing
COPY contacts (name, email, phone, organization_id)
FROM '/tmp/import.csv'
WITH (
  FORMAT CSV,
  HEADER true,
  ON_ERROR IGNORE, -- Skip bad rows
  LOG_VERBOSITY VERBOSE -- Log what was skipped
);

A opção LOG_VERBOSITY VERBOSE registra cada linha ignorada no log do PostgreSQL, para que você possa auditar o que foi rejeitado. Isso torna a produção dos pipelines de importação em massa segura – uma linha malformada na linha 47.832 não anula mais uma importação de um milhão de linhas.

Para importações programáticas através do seu ORM, você pode usar o protocolo PostgreSQL COPY através do driver node-postgres:

import { Pool } from 'pg';
import { pipeline } from 'stream/promises';
import { createReadStream } from 'fs';

async function importContacts(csvPath: string, orgId: string) {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });
  const client = await pool.connect();

  try {
    const copyStream = client.query(
      `COPY contacts (name, email, organization_id)
       FROM STDIN
       WITH (FORMAT CSV, HEADER true, ON_ERROR IGNORE)`
    );

    await pipeline(createReadStream(csvPath), copyStream);
    console.log(`Imported rows: ${copyStream.rowCount}`);
  } finally {
    client.release();
  }
}

---

pg_stat_io: Análise de desempenho de E/S

O PostgreSQL 17 adiciona pg_stat_io, uma visualização que divide as estatísticas de E/S por relação, tipo de bloco e contexto. Isso é inestimável para diagnosticar consultas lentas causadas por leituras excessivas de disco:

-- Top tables by cache misses (reads going to disk)
SELECT
  object,
  reads,
  hits,
  ROUND(hits::numeric / NULLIF(hits + reads, 0) * 100, 2) AS hit_rate_pct,
  evictions,
  reuses
FROM pg_stat_io
WHERE backend_type = 'client backend'
  AND object = 'relation'
  AND reads > 0
ORDER BY reads DESC
LIMIT 20;

-- Identify tables causing the most buffer evictions
SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_dead_tup,
  n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

Para tabelas com altas contagens de varredura sequencial e baixas taxas de acertos de cache, você precisa de um índice ou de uma configuração shared_buffers maior.

---

Replicação lógica para sequências

PostgreSQL 17 adiciona suporte de replicação lógica para sequências. Isso fecha uma lacuna crítica para configurações de replicação ativa-ativa – anteriormente, as sequências em servidores em espera divergiam, causando conflitos de chaves duplicadas ao promover uma espera.

Para desenvolvedores de aplicativos, isso significa:

-- Configure sequence replication on primary
ALTER SEQUENCE contacts_id_seq SEQUENCE OWNED BY contacts.id;

-- Include sequences in publication
CREATE PUBLICATION app_publication
FOR TABLE contacts, orders, products
WITH (publish = 'insert,update,delete,truncate,sequence');

-- Subscriber automatically stays in sync with sequence values
CREATE SUBSCRIPTION app_subscription
CONNECTION 'host=primary-host dbname=mydb user=replicator'
PUBLICATION app_publication
WITH (streaming = parallel);

---

Benchmarks de desempenho

As melhorias de desempenho do PostgreSQL 17 são mensuráveis em aplicações reais:

Esses benchmarks são de cargas de trabalho reais em instâncias EC2 r6g.xlarge. Os resultados variam de acordo com a distribuição de dados e padrões de consulta.

---

Atualizando para PostgreSQL 17

Para ambientes de desenvolvimento baseados em Docker, atualize seu arquivo de composição:

# docker-compose.dev.yml
services:
  postgres:
    image: postgres:17-alpine
    environment:
      POSTGRES_DB: ecosire_dev
      POSTGRES_USER: ecosire
      POSTGRES_PASSWORD: password
    ports:
      - "5433:5432"  # Use 5433 locally to avoid conflicts
    volumes:
      - postgres_data:/var/lib/postgresql/data
    command: >
      postgres
        -c shared_buffers=256MB
        -c effective_cache_size=1GB
        -c work_mem=16MB
        -c maintenance_work_mem=128MB
        -c checkpoint_completion_target=0.9
        -c wal_buffers=16MB
        -c max_connections=100

Para atualizações de produção, use pg_upgrade:

# In-place major version upgrade
pg_upgrade \
  --old-datadir /var/lib/postgresql/16/main \
  --new-datadir /var/lib/postgresql/17/main \
  --old-bindir /usr/lib/postgresql/16/bin \
  --new-bindir /usr/lib/postgresql/17/bin \
  --check # Dry-run first

Sempre execute --check primeiro. Para atualizações sem tempo de inatividade na produção, use a replicação lógica para migrar para uma nova instância do PostgreSQL 17 enquanto a antiga permanece ativa.

---

Perguntas frequentes

JSON_TABLE é mais rápido que jsonb_array_elements?

JSON_TABLE geralmente tem desempenho semelhante a jsonb_array_elements para consultas equivalentes, mas participa das passagens de otimização do planejador de consultas de forma mais eficaz. O benefício real é a legibilidade, a capacidade de manutenção e a conformidade com o SQL padrão — suas consultas parecem SQL, e não uma sopa de chamada de função. Para consultas JSON críticas para desempenho em escala, certifique-se de que suas colunas JSONB tenham índices GIN apropriados.

Devo atualizar do PostgreSQL 16 para 17 imediatamente?

Aguarde de 3 a 6 meses após um lançamento importante do PostgreSQL antes de atualizar os sistemas de produção. Isso dá tempo para que seu provedor de hospedagem suporte a nova versão, para que as extensões das quais você depende sejam atualizadas e para que quaisquer bugs iniciais sejam corrigidos. Para novos projetos ou ambientes de desenvolvimento, comece hoje mesmo com o PostgreSQL 17. O caminho de atualização de 16 para 17 é simples usando pg_upgrade.

Como ativo a nova visualização pg_stat_io?

pg_stat_io é ativado automaticamente no PostgreSQL 17 — nenhuma configuração é necessária. Consulte-o com SELECT * FROM pg_stat_io em qualquer banco de dados PostgreSQL 17. A visualização é cumulativa desde a última redefinição de estatísticas (pg_stat_reset()), portanto, estabeleça uma linha de base antes de fazer alterações e compare depois.

Posso usar MERGE com Drizzle ORM?

Atualmente, o Drizzle não tem suporte nativo a MERGE — use db.execute(sql\MERGE ...`)com valores parametrizados. Nunca usesql.raw()` para dados fornecidos pelo usuário. Assista aos problemas do Drizzle GitHub para rastreamento de suporte MERGE, pois é um recurso comumente solicitado.

Qual versão do PostgreSQL devo usar para novos projetos?

Direcione o PostgreSQL 17 para todos os novos projetos. Ele tem o melhor desempenho, os recursos SQL mais recentes e receberá atualizações de segurança até novembro de 2029. Evite direcionar o PostgreSQL 14 ou anterior – eles chegarão ao fim da vida útil nos próximos 2 anos e você precisará atualizar de qualquer maneira.

---

Próximas etapas

Os novos recursos do PostgreSQL 17 — particularmente JSON_TABLE, MERGE com RETURNING e as melhorias de classificação incremental — abrem padrões de arquitetura que não eram viáveis ​​antes. ECOSIRE executa PostgreSQL 17 em produção para todas as cargas de trabalho de banco de dados, aproveitando essas melhorias em um esquema de mais de 65 tabelas.

Se você precisa de consultoria em arquitetura de banco de dados, otimização de consultas ou desenvolvimento full-stack com ferramentas modernas, explore nossos serviços de engenharia.