PostgreSQL 17 : Nouvelles fonctionnalités pour les développeurs d'applications

PostgreSQL 17 est doté d'un ensemble de fonctionnalités qui intègrent enfin plusieurs fonctionnalités SQL demandées depuis longtemps. Alors que les administrateurs de bases de données se concentreront sur des améliorations vides et des améliorations de la réplication logique, les développeurs d'applications bénéficient d'ajouts tout aussi intéressants : JSON_TABLE pour interroger JSON en tant que données relationnelles, des améliorations significatives de l'instruction MERGE, de nouvelles fonctions d'agrégation et des gains de performances qui affectent chaque requête.

Ce guide se concentre exclusivement sur les fonctionnalités qui modifient la façon dont vous écrivez le code d'une application : les modèles SQL, les points d'intégration ORM et les décisions architecturales qui deviennent possibles ou meilleures dans PostgreSQL 17.

Points clés à retenir

• JSON_TABLE est désormais standard — interrogez les tableaux JSON comme s'il s'agissait de tables sans fonctions personnalisées
• L'instruction MERGE a obtenu la clause RETURNING — obtient les lignes concernées sans une deuxième requête
• L'agrégat ANY_VALUE() simplifie le regroupement des requêtes sans listes GROUP BY complètes
• Des améliorations incrémentielles du tri réduisent le coût du plan de requête sur les données commandées
• La commande COPY prend en charge ON_ERROR pour ignorer les mauvaises lignes au lieu d'échouer complètement
• La vue pg_stat_io donne des statistiques d'E/S précises par relation pour l'analyse des performances
• La réplication logique prend désormais en charge les séquences, comblant ainsi une lacune majeure pour les configurations actives-actives
• Les améliorations VACUUM réduisent l'accumulation de ballonnements - moins d'aspirateurs automatiques d'urgence

---

JSON_TABLE : Interroger JSON en tant que données relationnelles

La fonctionnalité SQL la plus importante pour les développeurs d'applications est JSON_TABLE. Les applications stockent fréquemment des tableaux ou des objets imbriqués dans des colonnes JSONB. Avant PostgreSQL 17, l'interrogation de ces données nécessitait des appels de fonction jsonb_array_elements maladroits avec des jointures latérales.

Prenons l'exemple d'une colonne JSONB stockant les éléments de campagne d'une commande :

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

Avant PostgreSQL 17 (en utilisant 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 avec 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 version JSON_TABLE est plus lisible, donne des types de colonnes explicites et participe à l'optimisation des jointures du planificateur de requêtes. Il gère également les clés manquantes avec élégance avec les options de colonne DEFAULT NULL ON EMPTY et DEFAULT 0 ON ERROR.

Exemple plus complexe : JSON imbriqué avec gestion des erreurs :

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 avec RETOUR

L'instruction MERGE (introduite dans PostgreSQL 15) permet une logique d'insertion ou de mise à jour conditionnelle dans une seule instruction. PostgreSQL 17 ajoute RETURNING, ce qui était impossible auparavant :

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

L'astuce xmax = 0 identifie si la ligne a été insérée (xmax = 0) ou mise à jour (xmax != 0). Ce modèle est essentiel pour les pipelines d’importation de données idempotents où vous devez savoir ce qui a réellement changé.

Utilisation de MERGE dans Drizzle (via SQL brut jusqu'à ce que Drizzle ajoute le support natif de 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() Agrégat

Une frustration GROUP BY courante dans PostgreSQL : vous souhaitez regrouper par colonne mais également sélectionner une colonne non agrégée dans la même ligne. Auparavant, vous deviez soit l'ajouter à GROUP BY (en modifiant la sémantique de regroupement), soit utiliser MAX() comme solution de contournement.

-- 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() est sémantiquement correct - il dit explicitement "Je me fiche de la valeur du groupe, donnez-m'en une." Ceci est utile pour les requêtes de diagnostic, le débogage et les cas où toute valeur représentative est acceptable.

---

Améliorations incrémentielles du tri

PostgreSQL 17 a considérablement amélioré les performances du tri incrémentiel. Cela affecte toute requête avec ORDER BY sur une colonne d'index non principale :

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

Pour les applications gourmandes en pagination (tableaux de bord, vues de liste, rapports), cette amélioration réduit la pression de la mémoire et améliore les temps de réponse sans aucune modification des requêtes.

---

COPIER avec ON_ERROR

Les pipelines d’importation de données rencontrent fréquemment des lignes mal formées. Avant PostgreSQL 17, une seule mauvaise ligne faisait échouer toute l'opération COPY. Vous pouvez désormais ignorer les mauvaises lignes :

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

L'option LOG_VERBOSITY VERBOSE enregistre chaque ligne ignorée dans le journal PostgreSQL, afin que vous puissiez auditer ce qui a été rejeté. Cela rend la production des pipelines d’importation en vrac sécurisée : une ligne mal formée à la ligne 47 832 n’interrompt plus une importation d’un million de lignes.

Pour les importations programmatiques via votre ORM, vous pouvez utiliser le protocole PostgreSQL COPY via le pilote 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 : analyse des performances d'E/S

PostgreSQL 17 ajoute pg_stat_io, une vue qui décompose les statistiques d'E/S par relation, type de bloc et contexte. Ceci est inestimable pour diagnostiquer les requêtes lentes causées par des lectures excessives sur le disque :

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

Pour les tables avec un nombre élevé d'analyses séquentielles et de faibles taux de réussite du cache, vous avez besoin d'un index ou d'une configuration shared_buffers plus grande.

---

Réplication logique pour les séquences

PostgreSQL 17 ajoute la prise en charge de la réplication logique pour les séquences. Cela comble une lacune critique pour les configurations de réplication active-active : auparavant, les séquences sur les serveurs de secours divergeaient, provoquant des conflits de clés en double lors de la promotion d'un serveur de secours.

Pour les développeurs d’applications, cela signifie :

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

Les améliorations des performances de PostgreSQL 17 sont mesurables dans des applications réelles :

Ces tests proviennent de charges de travail réelles sur des instances EC2 r6g.xlarge. Les résultats varient selon la distribution des données et les modèles de requête.

---

Mise à niveau vers PostgreSQL 17

Pour les environnements de développement basés sur Docker, mettez à jour votre fichier de composition :

# 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

Pour les mises à niveau de production, utilisez 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

Exécutez toujours --check en premier. Pour des mises à niveau sans temps d'arrêt en production, utilisez la réplication logique pour migrer vers une nouvelle instance PostgreSQL 17 pendant que l'ancienne reste active.

---

Questions fréquemment posées

JSON_TABLE est-il plus rapide que jsonb_array_elements ?

JSON_TABLE fonctionne généralement de la même manière que jsonb_array_elements pour des requêtes équivalentes, mais il participe plus efficacement aux passes d'optimisation du planificateur de requêtes. Le véritable avantage est la lisibilité, la maintenabilité et la conformité au standard SQL : vos requêtes ressemblent à du SQL, pas à de la soupe d'appels de fonctions. Pour les requêtes JSON critiques en termes de performances à grande échelle, assurez-vous que vos colonnes JSONB disposent des index GIN appropriés.

Dois-je immédiatement passer de PostgreSQL 16 à 17 ?

Attendez 3 à 6 mois après une version majeure de PostgreSQL avant de mettre à niveau les systèmes de production. Cela donne le temps à votre fournisseur d'hébergement de prendre en charge la nouvelle version, de mettre à jour les extensions dont vous dépendez et de corriger les bogues initiaux. Pour les nouveaux projets ou environnements de développement, commencez dès aujourd'hui avec PostgreSQL 17. Le chemin de mise à niveau de 16 à 17 est simple en utilisant pg_upgrade.

Comment activer la nouvelle vue pg_stat_io ?

pg_stat_io est automatiquement activé dans PostgreSQL 17 — aucune configuration n'est nécessaire. Interrogez-le avec SELECT * FROM pg_stat_io dans n'importe quelle base de données PostgreSQL 17. La vue est cumulative depuis la dernière réinitialisation des statistiques (pg_stat_reset()), alors établissez une référence avant d'apporter des modifications et comparez par la suite.

Puis-je utiliser MERGE avec Drizzle ORM ?

Drizzle n'a actuellement pas de support natif de MERGE — utilisez db.execute(sql\MERGE ...`)avec des valeurs paramétrées. N'utilisez jamaissql.raw()` pour les données fournies par l'utilisateur. Surveillez les problèmes de Drizzle GitHub pour le suivi du support MERGE, car il s'agit d'une fonctionnalité fréquemment demandée.

Quelle version de PostgreSQL dois-je cibler pour les nouveaux projets ?

Ciblez PostgreSQL 17 pour tous les nouveaux projets. Il offre les meilleures performances, les fonctionnalités SQL les plus récentes et recevra des mises à jour de sécurité jusqu'en novembre 2029. Évitez de cibler PostgreSQL 14 ou une version antérieure : ils arriveront en fin de vie dans les 2 prochaines années et vous devrez de toute façon effectuer une mise à niveau.

---

Prochaines étapes

Les nouvelles fonctionnalités de PostgreSQL 17 — en particulier JSON_TABLE, MERGE avec RETURNING et les améliorations du tri incrémentiel — ouvrent des modèles architecturaux qui n'étaient pas réalisables auparavant. ECOSIRE exécute PostgreSQL 17 en production pour toutes les charges de travail de bases de données, profitant de ces améliorations sur un schéma de plus de 65 tables.

Que vous ayez besoin de conseils en architecture de base de données, d'optimisation de requêtes ou de développement full-stack avec des outils modernes, découvrez nos services d'ingénierie.