Nouveautés de PostgreSQL 14

Workshop 14

Dalibo & Contributors

Nouveautés de PostgreSQL 14

Les nouveautés

  • Administration
  • Réplication
  • Développement et syntaxe SQL
  • Supervision
  • Performances

Administration et maintenance

  • Sécurité
  • Configuration
  • Outils clients
  • Partitionnement
  • Divers

Sécurité

Authentification SCRAM-SHA-256 par défaut

Défaut à présent : password_encryption = scram-sha-256

  • Utilisation conseillée depuis la version 10 !
  • Migration :
    • utilisateur par utilisateur
    • SET password_encryption TO "scram-sha-256" ;
    • ré-entrer le mot de passe
    • dans pg_hba.conf : md5 → scram-sha-256

Nouveaux rôles prédéfinis

  • pg_read_all_data
  • pg_write_all_data
  • pg_database_owner (template)

Nouveautés de configuration (GUC)

Nouveaux caractères d’échappement pour log_line_prefix

log_line_prefix : enrichit le préfixe des lignes de la sortie d’erreur

  • %P : identifiant du processus principal (parallel leader)
    • si l’entrée de journal provient d’un processus auxiliaire (parallel worker)
  • %Q : identifiant de la requête (nouveauté)
    • si le calcul interne de l’identifiant est actif

Temps d’attente maximal pour une session inactive

  • Nouveau paramètre idle_session_timeout
  • Temps d’attente avant d’interrompre une session inactive
    • Désactivé par défaut (valeur 0)
    • Comportement voisin de idle_in_transaction_session_timeout
    • Paramètre de session, ou globalement pour l’instance

Modification à chaud de la restore_command

  • Le paramètre restore_command ne nécessite plus de redémarrage
  • Applicable pour les instances secondaires

Détection des déconnexions pendant l’exécution d’une requête

  • Nouveau paramètre client_connection_check_interval
  • Détermine le délai entre deux contrôles de connexion
    • Désactivé par défaut (valeur 0)
    • Utile pour les très longues requêtes
    • Repose sur des appels système non standards (non définis par POSIX)
      • donc Linux uniquement

Changements mineurs de configuration

  • VACUUM
    • vacuum_cost_page_miss = 2 (autrefois : 10)
  • Checkpoint :
    • checkpoint_completion_target = 0.9 par défaut
  • Nouveaux paramètres :
    • huge_page_size
    • log_recovery_conflict_waits

Outils clients

pg_dump/pg_restore : Possibilité d’exporter et restaurer des partitions individuellement

  • pg_dump au format custom, directory ou tar
    • L’instruction CREATE TABLE ne dépend plus du résultat de la commande ATTACH
  • pg_restore et l’option --table
    • Restauration possible d’une partition en tant que simple table
    • Plus simple qu’une restauration avec un fichier liste (table of contents)

pg_dump : Nouvelle option pour exporter les extensions

  • pg_dump --extension=…
    • spécifier un sous-ensemble d’extensions à exporter
    • exporter les extensions même avec --schema

Partitionnement

ALTER TABLE … DETACH PARTITION … CONCURRENTLY

  • Détachement de partition non bloquant
  • Fonctionne en mode multi-transactions
  • Quelques restrictions :
    • Ne fonctionne pas dans un bloc de transactions
    • Impossible en cas de partition par défaut

Nouveautés sur REINDEX et reindexdb

  • REINDEX est maintenant disponible pour les tables et index partitionnés
  • Supporte la clause CONCURRENTLY
  • Fonctionne en mode multi-transactions

Divers

Compression des TOAST configurable en lz4 ou pglz

  • Historiquement : pglz
  • Nouveau : lz4, plus rapide
  • Définition :
    • SET default_toast_compression = …
    • ALTER TABLE … SET COMPRESSION …
  • Compatibilité : pg_dump --no-toast-compression
  • N’affecte pas le fonctionnement de la réplication

Nouvelle option pour VACUUM : PROCESS_TOAST

  • Traite les tables de débordement TOAST lors d’un VACUUM manuel
  • Activé par défaut
VACUUM (PROCESS_TOAST false) blog;

Nouvelle option pour REINDEX : TABLESPACE

  • Ajout de l’option TABLESPACE pour la commande REINDEX
  • Possibilité de déplacer des index vers un autre tablespace tout en les reconstruisant
  • Avec ou sans la clause CONCURRENTLY
  • Restrictions :
    • sur les tables et index partitionnés
    • sur les tables TOAST
    • sur le catalogue système

Nouvelle fonction pour attendre lorsque l’on arrête un backend

pg_terminate_backend ( pid integer, timeout bigint DEFAULT 0 )
  • Possibilité d’attendre l’arrêt du backend
  • Nouveau paramètre timeout

Réplication et Sharding

  • Réplication physique
  • Réplication logique
  • Évolutions pour les Foreign Data Wrapper
    • Vers une architecture distribuée (sharding)

Réplication physique

Autorise pg_rewind à utiliser un secondaire comme source

  • La source d’un rewind peut être une instance secondaire

Nouveau paramètre de connexion dans libpq

  • Nouvelles options pour le paramètre target_session_attrs
    • read-only, primary, standby, et prefer-standby

Réplication logique

  • Nouveau mode streaming in-progress pour la réplication logique
    • à activer
  • Informations supplémentaires pour les messages d’erreur de type columns are missing
  • Ajout de la syntaxe ALTER SUBSCRIPTION… ADD/DROP PUBLICATION…

Foreign Data Wrapper et Sharding

Support du TRUNCATE sur les tables distantes

  • Nouvelle routine dans l’API Foreign Data Wrapper pour la commande TRUNCATE
  • Supportée pour les serveurs distants PostgreSQL avec l’extension postgres_fdw
  • Valable pour les partitions distantes d’une table partitionnée
  • Option truncatable activée par défaut

Lecture asynchrone des tables distantes

  • Nouveau nÅ“ud d’exécution Async Foreign Scan
  • CREATE SERVER … OPTIONS (host …, port …, async_capable on) (pas par défaut !)
  • Lecture parallélisée pour les partitions distantes
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Append
   ->  Async Foreign Scan on public.async_p1 t1_1
         Output: t1_1.a, t1_1.b, t1_1.c
         Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE (((b % 100) = 0))
   ->  Async Foreign Scan on public.async_p2 t1_2
         Output: t1_2.a, t1_2.b, t1_2.c
         Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE (((b % 100) = 0))

Développement et syntaxe SQL

Fonction string_to_table

  • Nouvelle fonction pour subdiviser une chaîne de caractère et renvoyer le résultat dans une table :
SELECT string_to_table('une chaine à ignorer', ' ', 'ignorer');

string_to_table
-----------------
 une
 chaine
 à
 ¤
  • Alternative plus performante à regexp_split_to_table() et unnest(string_to_array()).

Nouvelle syntaxe OR REPLACE pour la modification d’un trigger

CREATE OR REPLACE TRIGGER check_update
  BEFORE UPDATE OF balance ON accounts
  FOR EACH ROW
  EXECUTE FUNCTION check_account_update();
  • Ne fonctionne pas pour les CONSTRAINT TRIGGER
  • Ne pas lancer dans une transaction qui a modifié la table du trigger

Support des paramètres OUT dans les procédures

CREATE PROCEDURE assign(IN a int, OUT b int)
  • paramètre initialisé à NULL en début de procédure

PL/pgSQL : assignation pour les types complexes

  • Évolution du parser de requêtes
  • Supporte l’assignation de valeurs pour les types complexes en PL/pgSQL
  a[2:3] := array[3,4];    -- slice de tableaux  int[]
  a[1].i := 2;             -- champ de record
  h['a'] := 'b';           -- hstore
  • et plus performants !

Manipulation du type JSONB

  • Nouvelle syntaxe d’accès aux éléments d’une colonne jsonb

  • Expressions avec indice, de style tableau

    SELECT ('{"a": 1}'::jsonb)['a'];
    SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';
    UPDATE table_name SET jsonb_field['key'] = '1';

Nouveaux types multirange et nouvelles fonctions d’agrégats

  • Nouveaux types multirange
    • permettent de créer des ensembles d’intervalles disjoints
    • fonctionnalités similaires aux types d’intervalles simples
  • Nouvelles fonctions pour agréger des intervalles :
    • range_agg()
    • range_intersect_agg()
  • Indexable avec btree, gist et hash

GROUP BY DISTINCT

  • Dédoublonnage des résultats d’agrégations multiples produit par un GROUP BY
  • Utile avec ROLLUP ou CUBE

Corps de routines respectant le standard SQL

  • Nouvelles syntaxes :
    • RETURN
    • BEGIN ATOMIC .. END;
  • Limitées au langage SQL
  • Impossible d’utiliser des paramètres polymorphiques (anyelement, etc.)
  • Dépendances avec les objets utilisés (DROP CASCADE)

Nouvelles clauses SEARCH et CYCLE

  • Génération d’une colonne de tri pour les requêtes récursives :
  [ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...]
      SET search_seq_col_name ]
  • Protection contre les cycles :
  [ CYCLE column_name [, ...]
      SET cycle_mark_col_name
      [ TO cycle_mark_value DEFAULT cycle_mark_default ]
      USING cycle_path_col_name ]

Nouvelle fonction date_bin

  • Nouvelle fonction pour répartir des timestamps dans des intervalles (buckets)
  • date_bin
    • interval : taille des buckets (unités month et year interdites)
    • timestamptz : valeur en entrée à traiter
    • timestamptz : timestamp correspondant au début du premier bucket

Possibilité d’attacher un alias à un JOIN .. USING

  • Permet de référencer les colonnes de jointures
  • Syntaxe : SELECT ... FROM t1 JOIN t2 USING (a, b, c) AS x

Supervision

Nouvelle vue pg_stat_wal

  • Permet de surveiller l’activité des WAL
  • Nouveau paramètre : track_wal_io_timing

Nouvelle vue pg_stat_progress_copy

  • Possibilité de suivre l’avancement d’un COPY avec la vue pg_stat_progress_copy

Nouvelle vue pg_stat_replication_slots

  • Donne des statistiques sur l’utilisation des slots de réplication logique
  • Ajout de la fonction pg_stat_reset_replication_slot

Nouveautées dans pg_stat_statements

  • Traçage des accès faits via CREATE TABLE AS, SELECT INTO, CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW et FETCH
  • Nouvelle vue pg_stat_statements_info
  • Nouvelle colonne toplevel dans la vue pg_stat_statements

Ajout de statistiques sur les sessions dans pg_stat_database

  • Ajout des colonnes suivantes à la vue système pg_stat_database :
    • session_time
    • active_time
    • idle_in_transaction_time
    • sessions
    • sessions_abandoned
    • sessions_fatal
    • sessions_killed

Identifiant pour les requêtes normalisées

  • Le query id est disponible globalement
    • valeur hachée sur 64 bits d’une requête normalisée
    • introduit avec pg_stat_statements en version 9.4
    • pg_stat_activity, log_line_prefix, EXPLAIN VERBOSE
  • nouveau paramètre compute_query_id (auto par défaut)
      query_id       |                           query
---------------------+---------------------------------------------------------
 2691537454541915536 | SELECT abalance FROM pgbench_accounts WHERE aid = 85694;
 2691537454541915536 | SELECT abalance FROM pgbench_accounts WHERE aid = 51222;
 2691537454541915536 | SELECT abalance FROM pgbench_accounts WHERE aid = 14006;
 2691537454541915536 | SELECT abalance FROM pgbench_accounts WHERE aid = 48639;

Nouveauté dans pg_locks

  • Ajout de la colonne waitstart
    • Heure à laquelle l’attente d’un verrou a commencé
            mode         | granted |      waitstart
    ---------------------+---------+---------------------
     AccessExclusiveLock | t       | 
     AccessShareLock     | f       | 2021-08-26 15:54:53

Performances

Améliorations de l’indexation GiST / SPGiST

  • Amélioration des performances de certains index GiST
    • plus rapides, plus petits
    • type : point
  • Support des index SPGiST couvrants

Nettoyage des index B-tree

  • Nettoyage des index B-tree « par le haut »
    • limite la fragmentation lorsque des lignes sont fréquemment modifiées

Nouvelles classes d’opérateurs pour les index BRIN

  • BRIN : index compact
    • jusque là : si corrélation ordres physique/logique
  • Nouvelles classes d’opérateurs
    • *_bloom_ops : permet d’utiliser les index BRIN pour des données dont l’ordre physique ne coïncide pas avec l’ordre logique
    • *_minmax_multi_ops : permet d’utiliser les index BRIN avec des prédicats de sélection de plage de données

Connexions simultanées en lecture seule

Ateliers

  • Découvrir les nouveaux rôles prédéfinis
  • Mise en place d’un sharding minimal
  • Outil pg_rewind

Découvrir les nouveaux rôles prédéfinis

  • Utiliser le rôle pg_database_owner dans une base template
  • Exporter avec le rôle pg_read_all_data
  • Importer avec le rôle pg_write_all_data

Mise en place d’un sharding minimal

  • Préparer le modèle et configurer les accès distants
  • Alimenter une table partitionnée répartie dans plusieurs bases de données
  • Étudier les différents cas d’usage

Outil pg_rewind

  • Création d’une instance primaire ;
  • Mise en place de la réplication sur deux secondaires ;
  • Promotion d’un secondaire pour réaliser des tests ;
  • Utilisation de pg_rewind pour raccrocher l’instance secondaire à la réplication.