Nouveautés de PostgreSQL 9.6

Workshop 9.6

Dalibo & Contributors

Nouveautés de PostgreSQL 9.6

PostgreSQL

Introduction

  • Développement commencé en juin 2015
  • Version Beta1 sortie le 12 mai 2016
  • Sortie le 29 septembre 2016

Au menu

  • Performances
    • Administration
    • SQL
    • Monitoring
    • Régressions / changements
    • Et une petite ouverture vers le futur

Performances

  • Parallélisme :
    • parcours séquentiel
    • jointure
    • agrégation
  • Index bloom
  • Améliorations globales
    • checkpoint

Parallélisme

  • Avant la 9.6
    • une requête = un processus
    • pas de multithreading
  • À partir de la 9.6
    • toujours pas de multithreading
    • toujours un processus pour une session
    • mais il peut être aidé par d’autres processus appelés workers

Parallélisme : Opérations

  • Parcours séquentiel
  • Jointure
  • Agrégation
  • Uniquement les requêtes en lecture !

Parallélisme : Paramètres 1/2

  • max_worker_processes : Nombre max de background worker
  • max_parallel_workers_per_gather : Nombre max de worker par gather
  • min_parallel_relation_size : Taille min de la relation pour déclencher le parallélisme
    • Possibilité de forcer le nombre de worker possible pour une table :
    • SET (parallel_workers = x)

Parallélisme : Paramètres 2/2

  • parallel_setup_cost : Coût de la mise en place du parallélisme
  • parallel_tuple_cost : Coût pour passer une ligne d’un worker au gather.

Parcours séquentiel parallèle : explications 1

  • Traitement des blocs par processus
  • Compteur de blocs
    • Parallel Heap Scan Current Block
  • Fonctionne en mode partiel
    • impossible à utiliser avec des données à trier

Parcours séquentiel parallèle : exemple 1

  • Exemple d’une table test_parallel_little
    • une seule colonne
    • non indexée
    • contenant 500 000 lignes

Exemple 1 : jeu de tests

CREATE TABLE test_parallel_little (id int);

INSERT INTO test_parallel_little
  SELECT generate_series(0,500000);

SELECT n.nspname as "Schema",
       c.relname as "Name",
       pg_size_pretty(pg_table_size(c.oid)) as "Size"
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE c.relname = 'test_parallel_little';


 Schema |     Name      |  Size
--------+---------------+--------
 public | test_parallel | 17 MB

Exemple 1 : plan

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM test_parallel_little WHERE id = 12;

 Gather  (cost=1000.00..6889.58 rows=1 width=4)
         (actual time=30.300..30.385 rows=1 loops=1)
   Output: id
   Workers Planned: 1
   Workers Launched: 1
   Buffers: shared hit=2264
   ->  Parallel Seq Scan on public.test_parallel_little
       (cost=0.00..5889.48 rows=1 width=4)
       (actual time=18.568..27.957 rows=0 loops=2)
         Output: id
         Filter: (test_parallel_little.id = 124862)
         Rows Removed by Filter: 250000
         Buffers: shared hit=2213
         Worker 0: actual time=7.054..25.832 rows=1 loops=1
           Buffers: shared hit=978
 Planning time: 0.141 ms
 Execution time: 31.862 ms
(14 lignes)

Parcours séquentiel parallèle : explications 2

  • Taille de la table = T
  • min_parallel_relation_size = S
  • Si T < S, pas de worker
  • Si T > S, un worker
  • Si T > Sx3, un worker supplémentaire (2)
  • Si T > Sx3x3, deux workers supplémentaires (3)
  • Si T > Sx3^3, trois workers supplémentaires (4)

Parcours séquentiel parallèle : exemple 2

  • Exemple d’une table test_parallel
    • une seule colonne
    • non indéxée
    • contenant 5 000 000 lignes

Exemple 2 : jeu de tests

CREATE TABLE test_parallel_little (id int);

INSERT INTO test_parallel_little
  SELECT generate_series(0,5000000);

SELECT n.nspname as "Schema",
       c.relname as "Name",
       pg_size_pretty(pg_table_size(c.oid)) as "Size"
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE c.relname = 'test_parallel';

 Schema |     Name      |  Size
--------+---------------+--------
 public | test_parallel | 173 MB

Exemple 2 : plan

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM test_parallel WHERE id=12;
 Gather  [...]
   Output: id
   Workers Planned: 3
   Workers Launched: 3
   Buffers: shared hit=5465 read=16812
   ->  Parallel Seq Scan on public.test_parallel  [...]
         Output: id
         Filter: (test_parallel.id = 124862)
         Rows Removed by Filter: 1250000
         Buffers: shared hit=5312 read=16812
         Worker 0: actual time=3.766..139.729 rows=1...
           Buffers: shared hit=1748 read=5466
         Worker 1: actual time=138.532..138.532 rows=0...
           Buffers: shared hit=812 read=2669
         Worker 2: actual time=138.508..138.508 rows=0...
           Buffers: shared hit=816 read=2683
 Planning time: 0.120 ms
 Execution time: 151.700 ms
(18 lignes)

Parallélisme sur une jointure

EXPLAIN ANALYZE SELECT * FROM big b1 JOIN big b2 USING (id) WHERE b1.id < 400000;
 Gather  [...]
   Workers Planned: 2
   Workers Launched: 2
   ->  Hash Join  [...]
         Hash Cond: (b1.id = b2.id)
         Worker 0: [...]
         Worker 1: [...]
         ->  Parallel Seq Scan on b1  [...]
               Filter: (b1.id < 400000)
               Rows Removed by Filter: 16903334
               Worker 0: [...]
               Worker 1: [...]
         ->  Hash  [...]
               Buckets: 4194304  Batches: 1  Memory Usage: 141753kB
               Worker 0: [...]
               Worker 1: [...]
               ->  Seq Scan on public.t3  [...]
                     Worker 0: [...]
                     Worker 1: [...]

Parallélisme sur une agrégation

EXPLAIN (ANALYZE)
SELECT count(*), min(C1), max(C1) FROM t1;

                           QUERY PLAN
---------------------------------------------------------------------
 Finalize Aggregate
 (actual time=1766.820..1766.820 rows=1 loops=1)
   ->  Gather
       (actual time=1766.767..1766.799 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate
             (actual time=1765.236..1765.236 rows=1 loops=3)
               ->  Parallel Seq Scan on t1
                   (actual time=0.021..862.430 rows=6666667
                    loops=3)
 Planning time: 0.072 ms
 Execution time: 1769.164 ms
(8 rows)

Parallélisation : limitations

  • Les fonction doivent être Parallel Safe
  • Il faut indiquer au planificateur qu’une fonction peut être utilisée :
    • Dans un gather et worker : PARALLEL SAFE
    • Dans un gather uniquement RESTRICTED
    • Ne peut pas être utilisée pour la parallélisation : PARALLEL UNSAFE
  • Pas de parallélisation pour :
    • Requête effectuant des écritures ou posant des verrous
    • Une requête qui pourrait être suspendue pendant son exécution. Exemple CURSOR
    • Une sous requête exécutée dans une requête déjà parallélisée.
    • Niveau d’isolation serializable

Index bloom

Infrastructure pour de nouvelles méthodes d’accès sous forme d’extensions.

  • Premier exemple : les index bloom
    • extension
    • basés sur la probabilité de présence, avec perte
    • plus petits mais impliquent un accès à la table
    • permettent d’avoir un index pour des recherches multicritères si les btree sont impraticables ou trop gros
    • égalité seulement pour entiers et chaînes

Divers - Checkpoint

  • Avant
    • écriture aléatoire car blocs lus dans l’ordre du cache
  • Après
    • parcours intégral des blocs pour trier et mutualiser les écritures
  • checkpoint_flush_after : Flush les données sur disques tous les X octets
    • Permet d’éviter le tuning des options dirty_background_* du kernel

Divers - Tri

  • Gain sur les opérations de tris
    • replacement_sort_tuples
    • Meilleure utilisation du cache des CPU

Administration

  • Gestion des slots de réplication pour pg_basebackup
  • Sauvegardes à chaud concurrentes
  • Réplication synchrone sur plusieurs nœuds
  • Amélioration des Foreign Data Wrapper
  • Amélioration du freeze
    • et nouvelle extension : pg_visibility
  • Divers
    • pg_config, pg_control*

Slots de réplication pour pg_basebackup

  • Nouvelle option
    • -S slotname ou --slot=slotname
    • s’utilise obligatoirement avec -X stream
  • Plus besoin d’archivage pour créer un serveur secondaire
    • sans aucun risque

Sauvegardes à chaud concurrentes

  • Déjà possible avec pg_basebackup…
    • mais pas en manuel avec pg_start_backup()
  • Nouvelle API
  • pg_start_backup() et pg_stop_backup()
    • paramètre supplémentaire pour utilisation concurrente
    • par défaut à false
  • Contraintes lorsqu’on utilise l’option concurrente
    • maintien de la session
    • plus de backup_label
    • => Privilégier des outils supportant cette fonctionnalité.

Réplication synchrone, l’existant

  • 9.0, réplication asynchrone
  • 9.1, réplication synchrone
    • plusieurs esclaves potentiellement synchrones
    • mais un seul synchrone à un instant t

synchronous_commit

  • Positionné à ON par défaut
  • Soit le client, soit le maître
  • Les modifications sont écrites dans les WAL avant d’envoyer la confirmation
  • Nouvelle valeur :
    • remote_apply

Réplication synchrone sur plusieurs nœuds

  • Plusieurs serveurs synchrones
  • Configuration
    • synchronous_standby_names = ‘N (liste serveurs)’
    • N = nombre serveurs synchrones
  • Contraintes
    • latence induite par la validation des transactions sur plusieurs esclaves
    • requêtes en attente sur le primaire s’il y a moins de N serveurs synchrones disponibles

Foreign Data Wrapper

  • Norme SQL/MED
  • Interroger des données externes.
  • Données externes présentées comme des tables
  • Attention aux performances !

FDW : jeu de tests

CREATE TABLE t1 (id integer);
INSERT INTO t1 SELECT generate_series(1, 10000000);
CREATE INDEX ON t1(id);

CREATE EXTENSION postgres_fdw;

CREATE SERVER ailleurs FOREIGN DATA WRAPPER postgres_fdw;

CREATE USER MAPPING FOR postgres SERVER ailleurs;

CREATE FOREIGN TABLE ft1(id integer)
  SERVER ailleurs
  OPTIONS (table_name 't1');

FDW : Nouveautés

  • SORT pushdown
    • exécution du tri sur le serveur distant
  • JOIN pushdown
    • exécution de la jointure sur le serveur distant

FDW : tri en 9.5

EXPLAIN (ANALYZE, VERBOSE, COSTS off)
SELECT * FROM ft1 ORDER BY id ;

                          QUERY PLAN
--------------------------------------------------------------
 Sort (actual time=9452.057..10129.182 rows=10000000 loops=1)
   Output: id
   Sort Key: ft1.id
   Sort Method: external sort  Disk: 136856kB
   ->  Foreign Scan on public.ft1
       (actual time=1.064..5981.536 rows=10000000 loops=1)
         Output: id
         Remote SQL: SELECT id FROM public.t1
 Planning time: 0.131 ms
 Execution time: 10425.730 ms
(9 rows)

FDW : tri en 9.6

EXPLAIN (ANALYZE, VERBOSE, COSTS off)
SELECT * FROM ft1 ORDER BY id;

                      QUERY PLAN
-----------------------------------------------------
 Foreign Scan on public.ft1
 (actual time=2.092..7438.416 rows=10000000 loops=1)
   Output: id
   Remote SQL: SELECT id FROM public.t1
               ORDER BY id ASC NULLS LAST
 Planning time: 0.168 ms
 Execution time: 7748.122 ms
(5 rows)

FDW : jointure en 9.5

EXPLAIN (VERBOSE, ANALYZE)
SELECT * FROM table1_distante
JOIN table2_distante ON table1_distante.id = table2_distante.id
WHERE table2_distante.id = 1;

                      QUERY PLAN
-----------------------------------------------------------
 Nested Loop  [...]
   Output: table1_distante.id, table1_distante.bla,
           table2_distante.id, table2_distante.bli
   ->  Foreign Scan on public.table1_distante  [...]
         Output: table1_distante.id, table1_distante.bla
         Remote SQL: SELECT id, bla FROM public.table1
                     WHERE ((id = 1))
   ->  Foreign Scan on public.table2_distante  [...]
         Output: table2_distante.id, table2_distante.bli
         Remote SQL: SELECT id, bli FROM public.table2
                     WHERE ((id = 1))
 Planning time: 0.217 ms
 Execution time: 65.616 ms

FDW : jointure en 9.6

EXPLAIN (VERBOSE, ANALYZE)
SELECT * FROM table1_distante
JOIN table2_distante
  ON table1_distante.id=table2_distante.id
WHERE table2_distante.id = 1;

                      QUERY PLAN
--------------------------------------------------------
 Foreign Scan  [...]
   Output: table1_distante.id, table1_distante.bla,
           table2_distante.id, table2_distante.bli
   Relations: (public.table1_distante)
              INNER JOIN (public.table2_distante)
   Remote SQL: SELECT r1.id, r1.bla, r2.id, r2.bli
               FROM (public.table1 r1
               INNER JOIN public.table2 r2
                  ON (((r2.id = 1)) AND ((r1.id = 1))))
 Planning time: 0.170 ms
 Execution time: 2.299 ms

FREEZE

  • Amélioration de la fonction de gel des données
  • pg_visibility(oid)
    • blkno
    • all_visible
    • all_frozen
    • pd_all_visible

pg_config, pg_control

  • Outil pg_config
    • informations de compilation
  • Outil pg_controldata
    • informations du fichier pg_control
  • Informations uniquement disponibles sur la ligne de commande avant la 9.6
  • Maintenant disponibles aussi via l’interface SQL
    • vue pg_config
    • fonctions pg_control_system(), pg_control_init(), pg_control_checkpoint() et pg_control_recovery()

SQL

  • Recherche plein-texte pour les phrases
  • Option CASCADE pour les extensions
  • Pivotage des résultats
  • Autres nouveautés
    • fonction mathématiques
    • jsonb_insert()

Recherche plein-texte pour les phrases

  • FTS disponible dans PostgreSQL depuis la version 8.3
  • Opérateurs disponibles jusqu’à présent
    • ET logique : &
    • OU logique : |
    • NON logique : !
  • Nouvel opérateur :
    • Succession : <->
    • Position dans la phrase : (où N est un entier représentant le nombre de mot séparant les deux motifs)

Ajout de l’option CASCADE pour les extensions

  • Avant la version 9.6 postgres=# CREATE EXTENSION ltree_plpythonu; ERREUR: l'extension « ltree » requise n'est pas installée
  • Après la version 9.6 postgres=# CREATE EXTENSION ltree_plpythonu CASCADE; NOTICE: installing required extension "ltree" NOTICE: installing required extension "plpythonu" CREATE EXTENSION

Pivotage des résultats

  • \crosstabview [ colV [ colH [ colD [ sortcolH ] ] ] ]
  • Exécute le contenu du tampon de requête
    • la requête doit renvoyer au moins 3 colonnes
  • Affiche le résultat dans une grille croisée
    • colV, en-tête vertical
    • colH, en-tête horizontal
    • colD, contenu à afficher dans le tableau
    • sortColH, colonne de tri pour l’en-tête horizontal

Autres nouveautés

  • Amélioration des performances globales pour plusieurs fonctions mathématiques
    • ln() / log() / exp() / pow()
  • Ajout des fonctions
    • sind() / cosd() / tand()

Monitoring

  • Nouvelles colonnes dans pg_stat_activity
  • Nouvelle fonction pg_blocking_pid()
  • Nouvelle vue pg_stat_progress_vacuum

Vue pg_stat_activity, pg_blocking_pids()

  • Avant la 9.6
    • identification complexe d’une requête bloquante
    • et de la raison du blocage
  • Ajout de la fonction pg_blocking_pids()
  • modification de la colonne waiting en deux colonnes
    • wait_event
    • wait_event_type

Fonction pg_blocking_pids()

  • Avant la 9.6
    • requête complexe pour trouver la session bloquante
    • voire collection de requêtes
  • Depuis la 9.6
    • fonction pg_blocking_pids()

pg_stat_progress_vacuum

  • Intégration d’une API pour le suivi de progression d’une commande
  • Utilisation de cette API pour VACUUM
  • Vue pg_stat_progress_vacuum
    • nombreuses informations sur la progression d’une opération VACUUM

Régressions / changements

  • Il n’est plus possible de créer des rôles commençant par pg_*
  • L’option -c de psql (exécution de commande) n’active plus –no-psqlrc
  • L’option -t de pg_restore ne correspond plus seulement aux tables
    • mais à tous types de relations
  • Niveaux archive et hot_standby n’existent plus pour wal_level.
    • Remplacés par un niveau replica <=> hot_standby

Futur

  • Version 10, en septembre/octobre 2017
  • Beta 1 depuis le 18 mai
  • Fonctionnalités majeures
    • réplication logique
    • partitionnement natif de table (suite et liste)
    • amélioration du partitionnement (parcours d’index, de bitmap, jointure par tri)
    • quorum pour la réplication synchrone

Questions

Atelier