PostgreSQL 17: Nuevas funciones para desarrolladores de aplicaciones

PostgreSQL 17 aterrizó con un conjunto de características que finalmente incorpora varias capacidades de SQL solicitadas desde hace mucho tiempo. Mientras que los administradores de bases de datos se centrarán en mejoras de vacío y mejoras en la replicación lógica, los desarrolladores de aplicaciones obtendrán adiciones igualmente convincentes: JSON_TABLE para consultar JSON como datos relacionales, mejoras significativas en las declaraciones MERGE, nuevas funciones agregadas y mejoras de rendimiento que afectan cada consulta.

Esta guía se centra exclusivamente en características que cambian la forma en que escribe el código de la aplicación: los patrones SQL, los puntos de integración ORM y las decisiones arquitectónicas que son posibles o mejores en PostgreSQL 17.

Conclusiones clave

• JSON_TABLE ahora es estándar: consulta matrices JSON como si fueran tablas sin funciones personalizadas
• La declaración MERGE obtuvo la cláusula RETURNING: obtiene las filas afectadas sin una segunda consulta
• ANY_VALUE() agregado simplifica las consultas de agrupación sin listas GROUP BY completas
• Las mejoras de clasificación incrementales reducen el costo del plan de consulta en los datos ordenados
• El comando COPY admite ON_ERROR para omitir filas incorrectas en lugar de fallar por completo
• La vista pg_stat_io proporciona estadísticas precisas de E/S por relación para el análisis de rendimiento
• La replicación lógica ahora admite secuencias, cerrando una brecha importante para las configuraciones activo-activo
• Las mejoras VACUUM reducen la acumulación de hinchazón: menos aspiradoras automáticas de emergencia

---

JSON_TABLE: Consultar JSON como datos relacionales

La característica SQL de mayor impacto para los desarrolladores de aplicaciones es JSON_TABLE. Las aplicaciones suelen almacenar matrices u objetos anidados en columnas JSONB. Antes de PostgreSQL 17, consultar estos datos requería llamadas a funciones jsonb_array_elements incómodas con uniones laterales.

Considere una columna JSONB que almacena las líneas de pedido de un pedido:

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

Antes de 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 con 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;

La versión JSON_TABLE es más legible, proporciona tipos de columnas explícitos y participa en la optimización de la unión del planificador de consultas. También maneja elegantemente las claves faltantes con las opciones de columna DEFAULT NULL ON EMPTY y DEFAULT 0 ON ERROR.

Ejemplo más complejo: JSON anidado con manejo de errores:

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;

---

FUSIONAR con REGRESO

La declaración MERGE (introducida en PostgreSQL 15) permite la lógica de inserción o actualización condicional en una sola declaración. PostgreSQL 17 agrega RETURNING, lo que antes era imposible:

-- 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;

El truco xmax = 0 identifica si la fila se insertó (xmax = 0) o se actualizó (xmax! = 0). Este patrón es esencial para canales de importación de datos idempotentes donde necesita saber qué cambió realmente.

Usando MERGE en Drizzle (a través de SQL sin formato hasta que Drizzle agregue soporte nativo para 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

Una frustración común de GROUP BY en PostgreSQL: desea agrupar por una columna pero también seleccionar una columna no agregada de la misma fila. Anteriormente tenías que agregarlo a GROUP BY (cambiando la semántica de agrupación) o usar MAX() como solución 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() es semánticamente correcto: dice explícitamente "No me importa qué valor del grupo, solo dame uno". Esto es útil para consultas de diagnóstico, depuración y casos en los que cualquier valor representativo es aceptable.

---

Mejoras incrementales en la clasificación

PostgreSQL 17 mejoró significativamente el rendimiento de clasificación incremental. Esto afecta cualquier consulta con ORDER BY en una columna de índice no 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 aplicaciones con mucha paginación (paneles, vistas de listas, informes), esta mejora reduce la presión de la memoria y mejora los tiempos de respuesta sin ningún cambio en las consultas.

---

COPIAR con ON_ERROR

Las canalizaciones de importación de datos frecuentemente encuentran filas con formato incorrecto. Antes de PostgreSQL 17, una sola fila incorrecta fallaba en toda la operación COPY. Ahora puedes omitir filas incorrectas:

-- 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
);

La opción LOG_VERBOSITY VERBOSE registra cada fila omitida en el registro de PostgreSQL, para que pueda auditar lo que se rechazó. Esto hace que la producción de oleoductos de importación a granel sea segura: una fila mal formada en la línea 47,832 ya no aborta una importación de un millón de filas.

Para importaciones programáticas a través de su ORM, puede usar el protocolo PostgreSQL COPY a través del controlador 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álisis de rendimiento de E/S

PostgreSQL 17 agrega pg_stat_io, una vista que desglosa las estadísticas de E/S por relación, tipo de bloque y contexto. Esto es invaluable para diagnosticar consultas lentas causadas por lecturas excesivas 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 tablas con recuentos de análisis secuenciales elevados y tasas de aciertos de caché bajas, necesita un índice o una configuración shared_buffers más grande.

---

Replicación lógica para secuencias

PostgreSQL 17 agrega soporte de replicación lógica para secuencias. Esto cierra una brecha crítica para las configuraciones de replicación activa-activa: anteriormente, las secuencias en los servidores en espera divergían, lo que provocaba conflictos de claves duplicadas al promover un servidor en espera.

Para los desarrolladores de aplicaciones, esto 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);

---

Puntos de referencia de rendimiento

Las mejoras de rendimiento de PostgreSQL 17 se pueden medir en aplicaciones reales:

Estos puntos de referencia provienen de cargas de trabajo reales en instancias EC2 r6g.xlarge. Los resultados varían según la distribución de datos y los patrones de consulta.

---

Actualización a PostgreSQL 17

Para entornos de desarrollo basados en Docker, actualice su archivo de redacción:

# 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 actualizaciones de producción, utilice 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

Ejecute siempre --check primero. Para realizar actualizaciones sin tiempo de inactividad en producción, utilice la replicación lógica para migrar a una nueva instancia de PostgreSQL 17 mientras la anterior permanece activa.

---

Preguntas frecuentes

¿JSON_TABLE es más rápido que jsonb_array_elements?

JSON_TABLE generalmente funciona de manera similar a jsonb_array_elements para consultas equivalentes, pero participa en los pasos de optimización del planificador de consultas de manera más efectiva. El beneficio real es la legibilidad, la facilidad de mantenimiento y el cumplimiento del estándar SQL: sus consultas parecen SQL, no una sopa de llamadas a funciones. Para consultas JSON críticas para el rendimiento a escala, asegúrese de que sus columnas JSONB tengan índices GIN adecuados.

¿Debo actualizar de PostgreSQL 16 a 17 inmediatamente?

Espere de 3 a 6 meses después de una versión importante de PostgreSQL antes de actualizar los sistemas de producción. Esto le da tiempo a su proveedor de alojamiento para admitir la nueva versión, para las extensiones de las que depende para actualizar y para corregir cualquier error inicial. Para nuevos proyectos o entornos de desarrollo, comience hoy con PostgreSQL 17. La ruta de actualización de 16 a 17 es sencilla usando pg_upgrade.

¿Cómo habilito la nueva vista pg_stat_io?

pg_stat_io se habilita automáticamente en PostgreSQL 17; no se necesita configuración. Consúltalo con SELECT * FROM pg_stat_io en cualquier base de datos PostgreSQL 17. La vista es acumulativa desde el último restablecimiento de estadísticas (pg_stat_reset()), así que establezca una línea de base antes de realizar cambios y compare después.

¿Puedo usar MERGE con Drizzle ORM?

Actualmente, Drizzle no tiene soporte nativo para MERGE; use db.execute(sql\MERGE ...`)con valores parametrizados. Nunca utilicesql.raw()` para datos proporcionados por el usuario. Mire los problemas de Drizzle GitHub para el seguimiento del soporte de MERGE, ya que es una característica comúnmente solicitada.

¿A qué versión de PostgreSQL debo apuntar para nuevos proyectos?

Apunte a PostgreSQL 17 para todos los proyectos nuevos. Tiene el mejor rendimiento, las funciones SQL más nuevas y recibirá actualizaciones de seguridad hasta noviembre de 2029. Evite apuntar a PostgreSQL 14 o anterior: llegarán al final de su vida útil en los próximos 2 años y necesitará actualizar de todos modos.

---

Próximos pasos

Las nuevas características de PostgreSQL 17, en particular JSON_TABLE, MERGE con RETURNING y las mejoras de clasificación incrementales, abren patrones arquitectónicos que antes no eran factibles. ECOSIRE ejecuta PostgreSQL 17 en producción para todas las cargas de trabajo de bases de datos, aprovechando estas mejoras en un esquema de más de 65 tablas.

Ya sea que necesite consultoría de arquitectura de bases de datos, optimización de consultas o desarrollo completo con herramientas modernas, explore nuestros servicios de ingeniería.