Ce document tente de présenter les principaux changements apportés par PostgreSQL 9.0, par rapport à la version majeure précédente, la version 8.4. Dans la mesure du possible, chaque fonctionnalité sera expliquée et accompagnée d'une démonstration. Toutes les nouveautés ne sont bien sûr pas présentées (il y en a plus de 200).

La version 9.0, comme son nom l'indique, est une version capitale dans la progression de PostgreSQL. Même si les solutions de réplication pour PostgreSQL sont nombreuses et répondent à des problématiques variées, la version 9.0 apporte une réplication simple, robuste et intégrée au moteur, qui sera vraisemblablement utilisée par défaut dans la plupart des configurations de Haute Disponibilité reposant sur PostgreSQL.

Les changements ont été subdivisés en quatre parties:

  • Les deux nouveautés incontournables
  • Les nouveautés
  • Les changements pouvant entraîner des régressions
  • Les améliorations

Les incontournables

Ces deux nouveautés sont celles qui ont justifié à elles seules le renommage de 8.5 en 9.0.

Hot Standby

Cette nouvelle fonctionnalité est une des deux raisons du renommage en 9.0. Il s'agit de proposer une base de 'Standby', c'est-à-dire une baseappliquant les journaux binaires générés par la base de production, tout  en la rendant ouverte en lecture seule. Ceci est assez complexe car, pendant l'exécution de ces requêtes en lecture seule, la base en Standby doit aussi pouvoir appliquer les données binaires provenant de la base de production, être capable de décider si les modifications peuvent entrer en conflit avec les lectures et déterminer les actions à entreprendre en conséquence : mettre en pause la restauration ou tuer des requêtes en lecture seule. Ce patch est volumineux et complexe, il rajoute des informations dans la journalisation à l'intention de la base de Standby et un mécanisme de résolution des conflits. C'est donc une des fonctionnalités majeures, et une des principales fonctionnalités à aider à tester.

Pour mettre ceci en place, il suffit de paramétrer la base de production comme suit :

postgresql.conf Primaire:

wal_level = 'hot standby' # Génère les informations supplémentaires dans les journaux
# vacuum_defer_cleanup_age # Optionnellement, vous pourriez vouloir paramétrer ceci, mais son réglage pourrait être complexe

Ensuite, créer une base de standby (la procédure est la même que précédemment pour un Warm Standby : pg_start_backup sur la production, recopie des fichiers sur l'esclave, pg_end_backup sur la production).

Puis il suffit de recopier les journaux sur le secondaire et de rajouter ceci dans son postgresql.conf :

postgresql.conf Secondaire:

hot_standby=on
max_standby_delay=30s # -1= toujours attendre, 0= ne jamais attendre, sinon attendre cette durée

et d'utiliser un programme comme pg_standby sur le secondaire pour intégrer les journaux (à paramétrer dans le recovery.conf).

max_standby_delay permet de déterminer le comportement de la base de standby en cas de conflit entre l'application des journaux de transactions et les requêtes en lecture seule. En cas de conflit, la base de standby acceptera d'attendre au plus max_standby_delay avant de tuer les requêtes en lecture qui bloquent l'application des journaux.

Il est bien sûr vivement conseillé de lire la documentation avant de mettre en place cette fonctionnalité… Ne serait-ce que pour bien comprendre les conséquences du réglage de max_standby_delay et vacuum_defer_cleanup_age, qui ne sont pas simples à appréhender.

Streaming Replication

C'est la deuxième moitié de la raison du passage en 9.0. Il s'agit cette fois-ci de modifier le mécanisme d'archivage pour le rendre continu : les bases de standby peuvent donc se connecter au maître et récupérer à tout moment ce qui leur manque des journaux, non plus en termes de fichiers entiers, mais bien en termes d'enregistrements dans ces journaux (des fragments de ces fichiers donc). Il s'agit donc bien d'une réplication binaire, pas de la rééxécution dans le même ordre de requêtes SQL comme sur certains autres moteurs de bases de données, avec tous les risques de cohérence que cela impliquerait.

Les bases de production et de standby sont identiques au niveau binaire (enfin presque, on ne va pas rentrer dans les détails, mais ne vous en faites pas si les fichiers de données n'ont pas la même somme de contrôle).

wal_level devra valoir « archive » (ou « hot standby ») pour continuer à faire de l'archivage continu.

postgresql.conf primaire :

max_wal_senders = x # Nombre maximum de « wal_senders », les processus chargés de répondre à des serveurs de standby
wal_keep_segments # Nombre de fichiers de journaux de transactions à conserver en ligne quoi qu'il arrive (évite d'avoir à les recopier manuellement sur le(s) secondaires en cas de déconnection lente)

Sur le secondaire :

recovery.conf secondaire:

stanby_mode = on
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' # La chaîne de connexion pour une session sur le maître

postgresql.conf secondaire:

wal_level # à la même valeur que sur le maître (pour le retour de bascule…)
hot_standby=on/off # Suivant que vous voulez en même temps être en hot standby

fichier pg_hba.conf : On doit y créer une entrée pour les connexions de réplication. La base est « replication », l'utilisateur utilisé doit avoir l'attribut de superutilisateur. Attention à ne pas donner des droits trop importants ici, donner accès aux journaux de transactions en lecture à n'importe qui donne accès à des informations privilégiées.

pg_hba.conf primaire:

host    replication     foo             192.168.1.100/32        md5

Comme pour Hot Standby, cette fonctionnalité est suffisamment riche et complexe pour qu'il soit vivement conseillé de lire la doc. Et de faire des tests de bascule une fois l'ensemble mis en place.

Ce qui est très important avec ces deux fonctionnalités, c'est que vous pouvez les utiliser ensemble. Vous pouvez donc avoir donc une base de stanby répliquée de façon quasi-synchrone avec la production, et exécuter des requêtes en lecture seule sur cette base.

Les nouveautés

Contraintes d'exclusion

Il est maintenant possible de déclarer des contraintes d'unicité plus complexes que celles s'appuyant sur l'opérateur '=' (contrainte d'unicité, deux jeux de colonnes ne pouvant être identiques).

Nous allons, pour l'illustrer, utiliser l'exemple de l'auteur, en utilisant le type 'temporal' qu'il a aussi développé. Ce type de données permet de définir des 'plages de temps', c'est à dire par exemple 'la plage de 12h15 à 13h15'.

Il faut donc récupérer le module temporal à l'adresse suivante : http://pgfoundry.org/projects/temporal/ , le compiler et l'installer comme un contrib (exécuter le script SQL fourni).

CREATE TABLE reservation
(
salle TEXT,
professeur TEXT,
durant PERIOD);

ALTER TABLE reservation ADD CONSTRAINT test_exclude EXCLUDE USING gist (salle WITH =,durant WITH &&);

Par ceci, nous disons qu'un enregistrement doit être refusé (contrainte d'exclusion) s'il en existe déjà un vérifiant les deux conditions concerner la même salle, et être en intersection au niveau de l'intervalle de temps.

marc=# INSERT INTO reservation (professeur,salle,durant) VALUES ( 'marc', 'salle techno', period('2010-06-16 09:00:00', '2010-06-16 10:00:00'));
INSERT 0 1
marc=# INSERT INTO reservation (professeur,salle,durant) VALUES ( 'jean', 'salle chimie', period('2010-06-16 09:00:00', '2010-06-16 11:00:00'));
INSERT 0 1
marc=# INSERT INTO reservation (professor,room,during) VALUES ( 'marc', 'salle chimie', period('2010-06-16 10:00:00', '2010-06-16 11:00:00'));
ERROR: conflicting key value violates exclusion constraint "test_exclude"
DETAIL: Key (room, during)=(salle chimie, [2010-06-16 10:00:00+02, 2010-06-16 11:00:00+02)) conflicts with existing key (room, during)=(salle chimie, [2010-06-16 09:00:00+02, 2010-06-16 11:00:00+02)).

L'insertion est interdite, puisque la salle de chimie est déjà prise de 9h à 11h.

Triggers par colonne

Voici d'abord un trigger par colonne.

CREATE TRIGGER toto BEFORE UPDATE of a ON t1 FOR EACH ROW EXECUTE PROCEDURE mon_trigger();

Ce trigger ne se déclenche que si la colonne a de la table t1 a été modifiée.

Triggers WHEN

Voici maintenant des exemples tirés de la documentation officielle pour la clause WHEN des triggers:

CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE PROCEDURE check_account_update();

CREATE TRIGGER log_update
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE PROCEDURE log_account_update();

DEFERRABLE UNIQUE CONSTRAINTS

Cette fonctionnalité aussi promet d'être pratique. Voici un exemple avec une clé primaire au lieu d'une simple clé unique, mais cela revient au même :

marc=# CREATE TABLE test (a int primary key);
marc=# INSERT INTO test values (1), (2);
marc=# UPDATE test set a = a+1;
ERROR: duplicate key value violates unique constraint "test_pkey"
DETAIL: Key (a)=(2) already exists.

Normal, mais dommage : à la fin de la transaction, mes données auraient été cohérentes. D'autant plus que si la table avait été triée physiquement par ordre descendant, ça passait ! En 8.4, il n'y avait pas d'échappatoire simple, il fallait trouver une astuce pour mettre à jour les enregistrements dans le bon ordre.

Nous pouvons maintenant faire ceci :

marc=# CREATE TABLE test (a int primary key deferrable);
marc=# INSERT INTO test values (2),(1);
marc=# UPDATE test set a = a+1;
ERROR: duplicate key value violates unique constraint "test_pkey"
DETAIL: Key (a)=(2) already exists.

Ah zut, ça ne marche pas.

En fait, je l'ai fait exprès : j'en profite pour faire un petit rappel sur les contraintes deferrable/deferred : une contrainte 'deferrable' PEUT être vérifiée en fin de transaction (elle est 'retardable'). Il faut toutefois dire à PostgreSQL expressément qu'on veut vraiment faire ce contrôle en fin de transaction.

On peut, pour la session en cours demander à passer toutes les contraintes en 'DEFERRED' :

marc=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
marc=# UPDATE test set a = a+1;
UPDATE 2

Si on veut ne pas avoir à effectuer le SET CONSTRAINTS à chaque fois, il est aussi possible de déclarer la contrainte comme INITIALLY DEFERRED:

CREATE TABLE test (a int PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);

Un autre rappel s'impose : les contraintes DEFERRED sont plus lentes que les contraintes IMMEDIATE. Par ailleurs, il faut bien stocker la liste des enregistrements à vérifier en fin de transaction quelque part, et cela consomme de la mémoire. Attention à ne pas le faire sur des millions d'enregistrements d'un coup. C'est la raison pour laquelle les contraintes 'DEFERRABLE' ne sont pas 'INITIALLY DEFERRED' par défaut.

Fonctions anonymes

Cette nouvelle fonctionnalité permet de créer des fonctions à usage unique. Elles seront très pratiques dans des scripts de livraison de version applicative par exemple. Voici une version un peu différente du GRANT SELECT ON ALL TABLES qui sera présenté plus loin dans ce document, qui donne le droit de sélection à tout un jeu de tables, en fonction du propriétaire des tables, et en ignorant deux schémas :

DO language plpgsql $$ 
DECLARE
vr record;

BEGIN

FOR vr IN SELECT tablename FROM pg_tables WHERE tableowner = 'marc' AND schemaname NOT IN ('pg_catalog','information_schema')
LOOP
EXECUTE 'GRANT SELECT ON ' || vr.tablename || ' TO toto';
END LOOP;
END
$$
;

En 8.4, il aurait fallu créer une fonction (via CREATE FUNCTION), l'exécuter puis la supprimer (avec DROP FUNCTION). Le tout demandant d'avoir les droits pour ça. La 9.0 facilite donc ce type d'exécution rapide.

Paramètres nommés

La syntaxe retenue pour nommer les paramètres est la suivante:

CREATE FUNCTION test (a int, b text) RETURNS text AS $$
DECLARE
valeur text;
BEGIN
valeur := 'a vaut ' || a::text || ' et b vaut ' || b;
RETURN valeur;
END;
$$ LANGUAGE plpgsql;

Jusque là, on écrivait :

SELECT test(1,'toto');
test
-------------------------
a vaut 1 et b vaut toto
(1 row)

Maintenant, on peut utiliser cette syntaxe explicite:

SELECT test( b:='toto', a:=1);
test
-------------------------
a vaut 1 et b vaut toto
(1 row)

De nombreux langages permettent ce type de syntaxe d'appel de fonction, qui améliore fortement la lisibilité du code.

GRANT/REVOKE IN SCHEMA

C'est un problème idiot, et un peu frustrant, qui est déjà arrivé à beaucoup d'administrateurs de base de données : créer 400 tables, puis devoir attribuer des droits à un utilisateur sur ces 400 tables. Jusque là, on en était quitte pour créer un script. Plus maintenant :

GRANT SELECT ON ALL TABLES IN SCHEMA public TO toto;

Et la marche arrière :

REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM toto;

Bien sûr, cette commande ne vaut que pour les tables en place au moment de la commande. Il faudra toujours faire de nouveaux GRANT pour les futures tables du schéma.

ALTER DEFAULT PRIVILEGES

Encore une commande permettant de gagner du temps dans la gestion des droits.

ALTER DEFAULT PRIVILEGES FOR ROLE marc GRANT SELECT ON TABLES TO PUBLIC ;
CREATE TABLE test_priv (a int);
\z test_priv
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------------+-------+-------------------+--------------------------
public | test_priv | table | =r/marc +|
| | | marc=arwdDxt/marc |

Les informations sur les droits par défaut sont stockées dans pg_default_acl.

Les changements pouvant entraîner régression

Ces deux changements dans PL/pgSQL peuvent entraîner des régressions dans du code fonctionnant en 8.4. Si vous avez du code PL/pgSQL, vérifiez le avant de migrer en 9.0. Le moteur génère des erreurs à l'exécution, comme illustré ci-dessous.

PL/pgSQL : meilleur contrôle du nom des variables

marc=# DO LANGUAGE plpgsql
$$
DECLARE
a int;
BEGIN
SELECT a FROM test;
END
$$
ERROR: column reference "a" is ambiguous
LINE 1: select a from test
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: select a from test
CONTEXT: PL/pgSQL function "inline_code_block" line 4 at SQL statement

Si vous voulez modifier ce comportement, vous pouvez le faire globalement mais il est préférable de le faire par fonction, en exécutant une de ces commandes au début de votre fonction:

variable_conflict error        # mode par défaut
variable_conflict use_variable # choisir le nom de variable
variable_conflict use_column # choisir le nom de colonne

Protection des mots réservés en PL/pgSQL

marc=# DO LANGUAGE plpgsql
$$
DECLARE
table int;
BEGIN
table :=table+1;
END
$$
;
ERROR: syntax error at or near "table"
LINE 6: table :=table+1;
^
marc=# DO LANGUAGE plpgsql
$$
DECLARE
"table" int;
BEGIN
"table" :="table"+1;
END
$$
;
DO

Les améliorations

Le planificateur de requête a reçu un grand nombre d'améliorations dans cette version. Nous allons donc commencer par lui:

Join Removal

marc=# CREATE TABLE t1 (a int);
CREATE TABLE
marc=# CREATE TABLE t2 (b int);
CREATE TABLE
marc=# CREATE TABLE t3 (c int);
CREATE TABLE
On insère quelques données avec le generate_series…
marc=# EXPLAIN SELECT t1.a,t2.b from t1 join t2 on (t1.a=t2.b) left join t3 on (t1.a=t3.c);
QUERY PLAN
------------------------------------------------------------------------------
Merge Right Join (cost=506.24..6146.24 rows=345600 width=8)
Merge Cond: (t3.c = t1.a)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t3.c
-> Seq Scan on t3 (cost=0.00..34.00 rows=2400 width=4)
-> Materialize (cost=337.49..853.49 rows=28800 width=8)
-> Merge Join (cost=337.49..781.49 rows=28800 width=8)
Merge Cond: (t1.a = t2.b)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t1.a
-> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t2.b
-> Seq Scan on t2 (cost=0.00..34.00 rows=2400 width=4)
Pour l'instant, c'est normal, et on a le même comportement qu'en 8.4. Mais imaginons que sur la table t3, on ait une contrainte UNIQUE sur la colonne c. Dans ce cas, théoriquement, la jointure sur la table t3 ne sert à rien : le nombre d'enregistrements du résultat ne sera pas modifié, pas plus, bien sûr, que leur contenu. C'est lié au fait que la colonne est UNIQUE, que la jointure est un LEFT JOIN, et qu'aucune colonne de t3 n'est récupérée. Si la colonne n'était pas UNIQUE, la jointure pourrait augmenter le nombre d'enregistrements du résultat. Si ce n'était pas un LEFT JOIN, la jointure pourrait diminuer le nombre d'enregistrements du résultat.

En 9.0 :

marc=# ALTER TABLE t3 ADD UNIQUE (c);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "t3_c_key" for table "t3"
ALTER TABLE
marc=# EXPLAIN SELECT t1.a,t2.b from t1 join t2 on (t1.a=t2.b) left join t3 on (t1.a=t3.c);
QUERY PLAN
------------------------------------------------------------------
Merge Join (cost=337.49..781.49 rows=28800 width=8)
Merge Cond: (t1.a = t2.b)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t1.a
-> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t2.b
-> Seq Scan on t2 (cost=0.00..34.00 rows=2400 width=4)
(8 rows)
Cette optimisation devrait pouvoir être très rentable, entre autre quand les requêtes sont générées par un ORM (mapping objet-relationnel). Ces outils ont la fâcheuse tendance à exécuter des jointures inutiles. Ici on a réussi à diviser le coût estimé de la requête par 10.

C'est aussi une optimisation qui pourra être très utile pour les applications utilisant beaucoup de jointures et de vues imbriquées.

Cela constitue encore une raison supplémentaire de déclarer les contraintes dans la base : sans ces contraintes, impossible pour le moteur d'être sûr que ces réécritures sont possibles.

IS NOT NULL peut utiliser les index

Pour cette démonstration, nous allons comparer la version 8.4 et la 9.0 (la table que j'ai créée contient majoritairement des valeurs NULL) :

En 8.4 :

marc=# EXPLAIN ANALYZE SELECT max(a) from test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.03..0.04 rows=1 width=0) (actual time=281.320..281.321 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.03 rows=1 width=4) (actual time=281.311..281.313 rows=1 loops=1)
-> Index Scan Backward using idxa on test (cost=0.00..29447.36 rows=1001000 width=4) (actual time=281.307..281.307 rows=1 loops=1)
Filter: (a IS NOT NULL)
Total runtime: 281.360 ms
(6 rows)

En 9.0 :

marc=# EXPLAIN ANALYZE SELECT max(a) from test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.08..0.09 rows=1 width=0) (actual time=0.100..0.102 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.08 rows=1 width=4) (actual time=0.092..0.093 rows=1 loops=1)
-> Index Scan Backward using idxa on test (cost=0.00..84148.06 rows=1001164 width=4) (actual time=0.089..0.089 rows=1 loops=1)
Index Cond: (a IS NOT NULL)
Total runtime: 0.139 ms
(6 rows)

On constate que la 9.0 parcourt uniquement les clés non nulles de l'index (Index cond, au lieu d'un filtre à posteriori). Dans ce cas précis, le gain est très net.

Utilisation d'index pour générer des statistiques à la volée

Avant de commencer à expliquer la nouveauté, un petit rappel sur les histogrammes: PostgreSQL, comme d'autres moteurs de bases de données, utilise un optimiseur statistique. Cela signifie qu'au moment de la planification d'une requête il a (ou devrait) avoir une idée correcte de ce que chaque étape de la requête va lui ramener, en termes de nombres d'enregistrements. Pour cela, il utilise des statistiques, comme le nombre d'enregistrements de la table approximatif, sa taille, la corrélation physique entre valeurs voisines dans la table, les valeurs les plus fréquentes, et les histogrammes, qui permettent d'évaluer assez précisément le nombre d'enregistrements ramenés par une clause WHERE sur une colonne, suivant la valeur ou la plage demandée sur la clause WHERE.

Il arrive que les statistiques soient rapidement périmées, et donc posent problème, pour certains ordres SQL. Par exemple, une table de trace dans laquelle on insèrerait des enregistrements horodatés, et sur laquelle on voudrait presque toujours sélectionner les enregistrements des 5 dernières minutes.

Dans ce cas, il était impossible avant la 9.0 d'avoir des statistiques à jour. Maintenant, quand PostgreSQL détecte qu'une requête demande un «range scan» sur une valeur supérieure à la dernière valeur de l'histogramme (ou inférieure à la première valeur), c'est à dire la plus grande valeur connue au dernier calcul de statistiques, et que la colonne est indexée, il récupère le max (ou le min si c'est la première valeur) de cette colonne en interrogeant l'index AVANT d'exécuter la requête, afin d'obtenir des statistiques plus réalistes. Comme il utilise un index pour cela, il faut qu'un index existe, bien sûr.

Voici un exemple. La colonne "a" de la table test a déjà été remplie avec de nombreuses dates, antérieures. Elle a donc des statistiques à jour, avec des histogrammes lui donnant la répartition des valeurs de a.

Il est 13:37, et je n'ai encore rien inséré dans la table de date supérieure à 13:37.

marc=# EXPLAIN ANALYZE select * from test where a > '2010-06-03 13:37:00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Index Scan using idxtsta on test (cost=0.00..8.30 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (a > '2010-06-03 13:37:00'::timestamp without time zone)
Total runtime: 0.027 ms
(3 rows)
Tout est donc normal. La borne supérieure de mon histogramme est 2010-06-03 13:36:16.830007 (l'information se trouve dans pg_stats). Il n'a aucun moyen d'évaluer le nombre d'enregistrements supérieurs à 13:37, et en 8.4, il aurait continué à estimer '1' tant qu'un analyze n'aura pas été passé.
marc=# DO LANGUAGE plpgsql
$$
DECLARE
i int;
BEGIN
FOR i IN 1..10000 LOOP
   INSERT INTO test VALUES (clock_timestamp());
END LOOP;
END
$$
;
DO

(Décidément, j'aime bien les DO).

Nous venons d'insérer 10000 dates supérieures à 13:37.

marc=# EXPLAIN ANALYZE SELECT * FROM test WHERE a > '2010-06-03 13:37:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using idxtsta on test (cost=0.00..43.98 rows=1125 width=8) (actual time=0.012..13.590 rows=10000 loops=1)
Index Cond: (a > '2010-06-03 13:37:00'::timestamp without time zone)
Total runtime: 23.567 ms
(3 rows)

Le nombre d'enregistrements estimé n'est pas à 0 ou 1. Et pourtant les statistiques ne sont pas à jour :

marc=# SELECT last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'test';
last_autoanalyze
-------------------------------
2010-06-03 13:36:21.553477+02
(1 row)

Dans cet exemple, nous avons tout de même une erreur d'un facteur 10. Ce n'est pas si mal: sans cette optimisation, l'erreur aurait été d'un facteur 10 000. En tout cas, une erreur d'un facteur 10 nous donne de plus fortes chances de choisir un plan intelligent.

seq_page_cost/random_page_cost par tablespace

marc=# ALTER TABLESPACE pg_default SET ( random_page_cost = 10, seq_page_cost=5);
ALTER TABLESPACE

Nous venons de modifier random_page_cost et seq_page_cost pour tous les objets du tablespace pg_default. Quel peut être le cas d'utilisation ?

C'est pour le cas où vous avez des tablespaces de performances différentes : par exemple, vous avez quelques données essentielles sur un disque SSD, ou bien des données d'historique sur une vieille baie moins performante que la baie flambant neuf que vous avez décidé d'utiliser pour les données actives. Cela vous permet de signaler à PostgreSQL que tous vos tablespaces ne sont pas forcément équivalents en termes de performance. Cela ne s'applique, bien sûr, que sur de très grosses bases.

Permettre de forcer le nombre de valeurs distinctes d'une colonne

Ceci permet de forcer le nombre de valeurs différentes d'une colonne. Ce n'est pas à utiliser à la légère, mais uniquement quand l'ANALYZE sur la table n'arrive pas à obtenir une valeur raisonnable.

Voici comment procéder :

marc=# ALTER TABLE test ALTER COLUMN a SET (n_distinct = 2);
ALTER TABLE

Il faut repasser un ANALYZE pour que la modification soit prise en compte :

marc=# ANALYZE test;
ANALYZE

Essayons maintenant :

marc=# EXPLAIN SELECT distinct * from test;
QUERY PLAN
------------------------------------------------------------------
HashAggregate (cost=6263.00..6263.02 rows=2 width=8)
-> Seq Scan on test (cost=0.00..5338.00 rows=370000 width=8)
(2 rows)

C'est l'exemple même de ce qu'il ne faut pas faire : j'ai bien 370 000 valeurs distinctes dans ma table. Maintenant mes plans d'exécution seront très mauvais.

Si la valeur n_distinct est positive, il s'agit du nombre de valeurs distinctes. Si la valeure est négative (entre 0 et -1), il s'agit du coefficient multiplicateur par rapport au nombre d'enregistrements estimés de la table : par exemple -0.2 signifie qu'il y a 1 enregistrement distinct pour 5 enregistrements dans la table. 0 ramène le comportement à celui par défaut (c'est ANALYZE qui effectue la mesure).

Ne touchez à ceci que si vous êtes absolument sûr d'avoir correctement diagnostiqué votre problème. Sinon, vous pouvez être sûr d'empirer les performances.


De nombreuses autres fonctionnalités de la base ont été améliorées. En voici quelques exemples parmi les plus significatifs :

VACUUM FULL amélioré

La commande VACUUM FULL était jusque ici très lente. Cette commande permet de récupérer l'espace perdu dans une table, principalement quand la commande VACUUM n'a pas été passée très régulièrement. Ceci était du à son mode de fonctionnement : les enregistrements étaient lus et déplacés un par un d'un bloc de la table vers un bloc plus proche du début de la table. Une fois que la fin de la table était vide, l'enveloppe était réduite à sa taille minimale.

Le problème était donc que ce mécanisme était très inefficace : le déplacement des enregistrements un à un entraine beaucoup d'entrées/sorties aléatoires (non contigues). Par ailleurs, durant cette réorganisation, les index doivent être maintenus, ce qui rend l'opération encore plus couteuse, et fait qu'à la fin d'un vacuum full, les index sont fortement désorganisés. Il est d'ailleurs conseillé de réindexer une table juste après y avoir appliqué un VACUUM FULL.

La commande VACUUM FULL, dans cette nouvelle version, crée une nouvelle table à partir de la table actuelle, en y recopiant tous les enregistrements de façon séquentielle. Une fois tous les enregistrements recopiés, les index sont recréés, et l'ancienne table détruite.

Cette méthode présente l'avantage d'être très largement plus rapide. Toutefois, VACUUM FULL demande toujours un verrou complet sur la table durant le temps de son exécution. Le seul défaut de cette méthode par rapport à l'ancienne, c'est que pendant le temps de son exécution, le nouveau VACUUM FULL peut consommer jusqu'à 2 fois l'espace disque de la table, puisqu'il en crée une nouvelle version.

Mesurons maintenant le temps d'exécution suivant les deux méthodes. Dans les deux cas, on prépare le jeu de test comme suit (en 8.4 et en 9.0:)

marc=# CREATE TABLE test (a int);
CREATE TABLE
marc=# CREATE INDEX idxtsta on test (a);
CREATE INDEX
marc=# INSERT INTO test SELECT generate_series(1,1000000);
INSERT 0 1000000
marc=# DELETE FROM test where a%3=0;
DELETE 333333
marc=# VACUUM test;
VACUUM
En 8.4 :
marc=# \timing
Timing is on.
marc=# VACUUM FULL test;
VACUUM
Time: 6306,603 ms
marc=# REINDEX TABLE test ;
REINDEX
Time: 1799,998 ms

Soit environ 8 secondes.

En 9.0 :
marc=# \timing
Timing is on.
marc=# VACUUM FULL test;
VACUUM
Time: 2563,467 ms

Ça ne veut toujours pas dire que VACUUM FULL est une bonne idée en production. Si vous en avez besoin, c'est probablement que votre politique de VACUUM n'est pas appropriée.

64 bits sous windows.

Il y a maintenant une version 64 bits native pour Windows. Pour l'instant aucune mesure de performance n'a été effectuée (à ma connaissance) pour en connaître les gains : peut on maintenant dépasser la limite de shared_buffers aux alentours de 500Mo sous Windows sans dégradations de performances ?

PL/pgSQL par défaut

Vous n'aurez plus à ajouter le langage plpgsql dans chaque base où vous en avez besoin car il est installé par défaut.

Beaucoup d'améliorations sur les langages PL.

Beaucoup de langages ont vu leur support grandement amélioré, PLPerl par exemple. Consultez les release notes si vous voulez davantage de détails, les modifications étant nombreuses.

Mot clé ALIAS

Nous pouvons maintenant utiliser le mot clé ALIAS. Comme son nom l'indique, il permet de créer des alias de variables.

La syntaxe est « nouveau_nom ALIAS FOR ancien_nom ». Cela se place dans la section DECLARE d'un code PL/pgSQL.

C'est utilisable dans deux cas principalement :

  • pour donner des noms aux variables d'une fonction PL:
   monparam ALIAS FOR $0
  • pour renommer des variables qui pourraient être en conflit. Dans un trigger par exemple:
   nouvelle_valeur ALIAS FOR new
(on aurait risqué le conflit avec la variable new dans la fonction trigger).

Passage de message dans NOTIFY/pg_notify

On peut donc passer des messages dans NOTIFY. Voici la méthode:

  • On s'abonne dans la session 1 à la file d'attente « messagerie_instantanee »
    Session 1 :
    marc=# LISTEN messagerie_instantanee;
    LISTEN
  • On envoie une notification dans la file d'attente « messagerie_instantanee » d'une autre session
    Session 2 :
    marc=# NOTIFY messagerie_instantanee, 'Vous avez reçu un popup';
    NOTIFY
  • On vérifie le contenu de la file d'attente
    Session 1 :
    LISTEN
    Asynchronous notification "messagerie_instantanee" with payload "Vous avez reçu un popup" received from server process with PID 5943.
  • On peut donc maintenant associer des messages (payloads) aux notifications, ce qui rend le mécanisme beaucoup plus puissant.

Signalons aussi la présence d'une nouvelle fonction pg_notify.

Le code de la session 2 peut donc être:

SELECT pg_notify('messagerie_instantanee','Vous avez reçu un popup');

Cela peut simplifier l'écriture, dans le cas d'un programme devant piloter de nombreuses files de messages.

get_bit et set_bit pour les bit strings

En voici un exemple très synthétique. Cet outil permet de manipuler les bits indépendamment dans un bit().


marc=# SELECT set_bit('1111'::bit(4),2,0);
set_bit
---------
1101
(1 row)


marc=# SELECT get_bit('1101'::bit(4),2);
get_bit
---------
0
(1 row)

application_name pour pg_stat_activity

Dans la session de supervision :

marc=# SELECT * from pg_stat_activity where procpid= 5991;

datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port |         backend_start         | xact_start | query_start | waiting | current_query
------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+----------------
16384 | marc | 5991 | 10 | marc | psql | | -1 | 2010-05-16 13:48:10.154113+02 | | | f | <IDLE>

(1 row)

Dans la session '5991' :

marc=# SET application_name TO 'mon_appli'; SET

Dans la session de supervision :

marc=# SELECT * from pg_stat_activity where procpid= 5991;

datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port |         backend_start         | xact_start |          query_start          | waiting | current_query
------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+----------------
16384 | marc | 5991 | 10 | marc | mon_appli | | -1 | 2010-05-16 13:48:10.154113+02 | | 2010-05-16 13:49:13.107413+02 | f | <IDLE>

(1 row)

À vous de le positionner correctement dans votre application, ou vos sessions. Votre DBA vous dira merci, sachant enfin qui lance quoi sur son serveur facilement.

Configuration par base de données+rôle

marc=# ALTER ROLE marc IN database marc set log_statement to 'all';
ALTER ROLE

Pour savoir qui a quelles modifications de variables dans quels rôles de quelles bases de données, il y a une nouvelle commande psql :

marc=# \drds
         List of settings
role | database | settings
-----+----------+-----------------
marc | marc | log_statement=all
(1 row)

Il y a donc eu une modification du catalogue pour gérer cette nouvelle fonctionnalité :

Table "pg_catalog.pg_db_role_setting"

  Column    |  Type  | Modifier
------------+--------+----------
setdatabase | oid | not null
setrole | oid | not null
setconfig | text |

Tracer les parametres modifiés lors d'un rechargement du postgresql.conf

Voici un exemple, lors de la modification du paramètre log_line_prefix :
LOG:  received SIGHUP, reloading configuration files
<%> LOG:  parameter "log_line_prefix" changed to "<%u%%%d> "

Nouvelles options de frame dans les fonctions de fenêtrage

Si vous ne vous connaissez pas les fonctions de fenêtrage, lisez la présentation de la 8.4 ici : Nouveautés PostgreSQL 8.4

Il y a donc des nouveautés dans le paramétrage du 'frame' des fonctions de fenêtrage. Soit la table suivante (faute de mieux…)

marc=# SELECT * FROM salaire ;
entite | personne  | salaire | date_embauche
-------+-----------+---------+---------------
R&D    | marc      |  700.00 | 2010-02-15
Compta | etienne   |  800.00 | 2010-05-01
R&D    | maria     |  700.00 | 2009-01-01
R&D    | kevin     |  500.00 | 2009-05-01
R&D    | jean      | 1000.00 | 2008-07-01
R&D    | tom       | 1100.00 | 2005-01-01
Compta | stephanie |  850.00 | 2006-01-01

Voici un exemple de fonctions de fenêtrage, sans préciser le frame.

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire) OVER (PARTITION BY entite ORDER BY date_embauche) FROM salaire;
entite | personne  | salaire | date_embauche |          avg         
-------+-----------+---------+---------------+-----------------------
Compta | stephanie |  850.00 | 2006-01-01    |  850.0000000000000000
Compta | etienne   |  800.00 | 2010-05-01    |  825.0000000000000000
R&D    | tom       | 1100.00 | 2005-01-01    | 1100.0000000000000000
R&D    | jean      | 1000.00 | 2008-07-01    | 1050.0000000000000000
R&D    | maria     |  700.00 | 2009-01-01    |  933.3333333333333333
R&D    | kevin     |  500.00 | 2009-05-01    |  825.0000000000000000
R&D    | marc      |  700.00 | 2010-02-15    |  800.0000000000000000

Le frame est le groupe d'enregistrements sur lequel la fonctions de fenêtrage est appliquée. Évidemment, si on ne précise pas la frame, il met une valeur par défaut. Voici la même requête, écrite avec une frame explicite.

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire) OVER (PARTITION BY entite ORDER BY date_embauche RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM salaire;
entite | personne | salaire | date_embauche | avg
-------+-----------+---------+---------------+-----------------------
Compta | stephanie | 850.00 | 2006-01-01 | 850.0000000000000000
Compta | etienne | 800.00 | 2010-05-01 | 825.0000000000000000
R&D | tom | 1100.00 | 2005-01-01 | 1100.0000000000000000
R&D | jean | 1000.00 | 2008-07-01 | 1050.0000000000000000
R&D | maria | 700.00 | 2009-01-01 | 933.3333333333333333
R&D | kevin | 500.00 | 2009-05-01 | 825.0000000000000000
R&D | marc | 700.00 | 2010-02-15 | 800.0000000000000000

La frame est donc par 'range', entre le début du range et l'enregistrement courant (pas vraiment l'enregistrement courant en fait, mais laissons de côté les subtilités, allez lire la doc si vous voulez en savoir plus). On constate que la fonction de moyenne (avg) est appliquée entre le premier des enregistrements du frame (les enregistrements de la même entité) et l'enregistrement courant.

Première nouveauté : en 9.0, la frame peut se calculer entre l'enregistrement courant et la fin du groupe (au lieu d'entre le début du groupe et l'enregistrement courant):

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire)  OVER (PARTITION BY entite ORDER BY date_embauche RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM salaire;
entite | personne | salaire | date_embauche | avg
-------+-----------+---------+---------------+----------------------
Compta | stephanie | 850.00 | 2006-01-01 | 825.0000000000000000
Compta | etienne | 800.00 | 2010-05-01 | 800.0000000000000000
R&D | tom | 1100.00 | 2005-01-01 | 800.0000000000000000
R&D | jean | 1000.00 | 2008-07-01 | 725.0000000000000000
R&D | maria | 700.00 | 2009-01-01 | 633.3333333333333333
R&D | kevin | 500.00 | 2009-05-01 | 600.0000000000000000
R&D | marc | 700.00 | 2010-02-15 | 700.0000000000000000

Deuxième nouveauté, on peut calculer des frames sur les n enregistrements précédents et n enregistrements suivants. Aucun intérêt avec ce jeu de données, mais il faut bien que je vous donne un exemple :

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire)  OVER (PARTITION BY entite ORDER BY date_embauche RANGE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM salaire;
entite | personne | salaire | date_embauche | avg
-------+-----------+---------+---------------+-----------------------
Compta | stephanie | 850.00 | 2006-01-01 | 825.0000000000000000
Compta | etienne | 800.00 | 2010-05-01 | 825.0000000000000000
R&D | tom | 1100.00 | 2005-01-01 | 1050.0000000000000000
R&D | jean | 1000.00 | 2008-07-01 | 933.3333333333333333
R&D | maria | 700.00 | 2009-01-01 | 733.3333333333333333
R&D | kevin | 500.00 | 2009-05-01 | 633.3333333333333333
R&D | marc | 700.00 | 2010-02-15 | 600.0000000000000000

On reste bien sûr sur le groupe (voir l'enregistrement de tom par exemple, l'enregistrement d'etienne ne rentre pas dans le calcul de sa moyenne).

Si on voulait la même requête que précédemment, mais avec des moyennes sur 3 enregistrements glissants, sans réinitialiser à chaque entité (toujours aucun intérêt pratique dans l'exemple).

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire) OVER (ORDER BY entite, date_embauche ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM salaire;
entite | personne | salaire | date_embauche | avg
--------+-----------+---------+---------------+----------------------
Compta | stephanie | 850.00 | 2006-01-01 | 825.0000000000000000
Compta | etienne | 800.00 | 2010-05-01 | 916.6666666666666667
R&D | tom | 1100.00 | 2005-01-01 | 966.6666666666666667
R&D | jean | 1000.00 | 2008-07-01 | 933.3333333333333333
R&D | maria | 700.00 | 2009-01-01 | 733.3333333333333333
R&D | kevin | 500.00 | 2009-05-01 | 633.3333333333333333
R&D | marc | 700.00 | 2010-02-15 | 600.0000000000000000

Bref, un outil à maîtriser d'urgence, si ce n'est pas déjà le cas (même si je n'ai pas été capable de vous donner un exemple décent).

Tris dans les aggrégations

Celle-ci est un peu subtile : le résultat de certaines fonctions d'aggrégation dépend de l'ordre dans lequel on leur fournit les données.

Il ne s'agit évidemment pas de count, avg, mais plutôt de array_agg, xml_agg, string_agg…

Ce qui va me permettre de vous présenter deux nouvelles fonctionnalités d'un coup, string_agg étant une nouveauté de la 9.0.

Reprenons la table salaire. Je voudrais la liste des employés, concaténés dans un seul champ, par entité. C'est pour stocker dans mon tableur.

marc=# SELECT entite,string_agg(personne,', ') FROM salaire GROUP BY entite;
entite | string_agg
-------+-------------------------------
Compta | etienne, stephanie
R&D | marc, maria, kevin, jean, tom

C'est déjà bien. Mais j'aimerais bien les avoir par ordre alphabétique, parce que je ne sais pas écrire de macro dans mon tableur pour retrier les données.

marc=# SELECT entite,string_agg(personne,', ' ORDER BY personne) FROM salaire GROUP BY entite;
entite | string_agg
--------+-------------------------------
Compta | etienne, stephanie
R&D | jean, kevin, marc, maria, tom

Il suffit donc de rajouter une clause de tri à l'intérieur de la fonction d'agrégat, sans virgule à la fin.

Amélioration des erreurs sur contrainte : message plus clair

En 8.4:

marc=# INSERT INTO test VALUES (1);
ERROR: duplicate key value violates unique constraint "test_a_key"

En 9.0:

marc=# INSERT INTO test VALUES (1);
ERROR: duplicate key value violates unique constraint "test_a_key"
DETAIL: Key (a)=(1) already exists.

Cela devrait aider à trouver les causes des violations de contrainte.

Explain buffers/statistiques sur les hash, xml, json, yaml, nouvelle syntaxe optionnelle explain

Voici un EXPLAIN ANALYZE classique :

marc=# EXPLAIN ANALYZE SELECT a, sum(c) FROM pere JOIN fils ON (pere.a = fils.b) WHERE b BETWEEN 1000 AND 300000 GROUP BY a;                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=905.48..905.86 rows=31 width=8) (actual time=0.444..0.453 rows=6 loops=1)
-> Nested Loop (cost=10.70..905.32 rows=31 width=8) (actual time=0.104..0.423 rows=6 loops=1)
-> Bitmap Heap Scan on fils (cost=10.70..295.78 rows=31 width=8) (actual time=0.040..0.154 rows=30 loops=1)
Recheck Cond: ((b >= 1000) AND (b <= 300000))
-> Bitmap Index Scan on fils_pkey (cost=0.00..10.69 rows=31 width=0) (actual time=0.023..0.023 rows=30 loops=1)
Index Cond: ((b >= 1000) AND (b <= 300000))
-> Index Scan using pere_pkey on pere (cost=0.00..19.65 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=30)
Index Cond: (pere.a = fils.b)
Total runtime: 0.560 ms
(9 rows)

Si vous voulez avoir accès aux nouvelles informations, il faut opter pour la nouvelle syntaxe :

EXPLAIN [ ( { ANALYZE boolean | VERBOSE boolean | COSTS boolean | BUFFERS boolean | FORMAT { TEXT | XML | JSON | YAML } } [, ...] ) ] instruction

Par exemple :

marc=# EXPLAIN (ANALYZE true, VERBOSE true, BUFFERS true) SELECT a, sum(c) FROM pere JOIN fils ON (pere.a = fils.b) WHERE b BETWEEN 1000 AND 300000 GROUP BY a;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=905.48..905.86 rows=31 width=8) (actual time=1.326..1.336 rows=6 loops=1)
Output: pere.a, sum(fils.c)
Buffers: shared hit=58 read=40
-> Nested Loop (cost=10.70..905.32 rows=31 width=8) (actual time=0.278..1.288 rows=6 loops=1)
Output: pere.a, fils.c
Buffers: shared hit=58 read=40
-> Bitmap Heap Scan on public.fils (cost=10.70..295.78 rows=31 width=8) (actual time=0.073..0.737 rows=30 loops=1)
Output: fils.b, fils.c
Recheck Cond: ((fils.b >= 1000) AND (fils.b <= 300000))
Buffers: shared hit=4 read=28
-> Bitmap Index Scan on fils_pkey (cost=0.00..10.69 rows=31 width=0) (actual time=0.030..0.030 rows=30 loops=1)
Index Cond: ((fils.b >= 1000) AND (fils.b <= 300000))
Buffers: shared hit=3
-> Index Scan using pere_pkey on public.pere (cost=0.00..19.65 rows=1 width=4) (actual time=0.013..0.014 rows=0 loops=30)
Output: pere.a
Index Cond: (pere.a = fils.b)
Buffers: shared hit=54 read=12
Total runtime: 1.526 ms
(18 rows)
VERBOSE apporte les lignes 'Output' (l'option existait déjà en 8.4).

BUFFERS indique les opérations sur les buffers (les entrées sorties de la requête): hit correspond aux données lues en cache, read aux données demandées au système d'exploitation. Ici, peu de données étaient en cache.

Vous pouvez aussi demander une sortie dans un autre format que texte. Pour un utilisateur, cela n'a aucune importance. Pour les développeurs d'interfaces graphiques présentant le résultat d'explain, cela permettra de faire l'économie d'un analyseur sur le texte du EXPLAIN, et des bugs qui vont avec.

On peut aussi désactiver l'affichage des coûts avec COSTS false.

Dictionnaire de filtrage (unaccent)

Il est possible maintenant de paramétrer des dictionnaires de filtrage. On parle bien sûr des dictionnaires du Full Text Search.

Le but de ces dictionnaires de filtrage est d'appliquer un premier filtrage sur les mots avant de les indexer. Le module présenté ci-dessous (unaccent) est l'illustration de ce mécanisme. Le filtrage peut consister en la suppression de mots ou en leur modification.

Unaccent ne supprime pas les mots, il supprime les accents (tous les signes diacritiques en fait), en remplaçant les caractères accentués par leur version sans accent. Unaccent est un module contrib.

Pour l'installer, comme pour toutes les contribs, psql mabase < chemin_contribs/unaccent.sql.

Nous allons à peu près suivre la documentation d'unaccent, les auteurs ayant eu la gentillesse de donner leurs exemples en français.

Nous créons un nouveau dictionnaire fr (pour ne pas polluer le dictionnaire french 'standard') :

marc=# CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
CREATE TEXT SEARCH CONFIGURATION

Nous modifions le paramétrage de 'fr' pour les lexemes de type mot, en lui demandant de les faire passer par unaccent et french_stem (au lieu de seulement french_stem)

marc=# ALTER TEXT SEARCH CONFIGURATION fr
ALTER MAPPING FOR hword, hword_part, word
WITH unaccent, french_stem;
ALTER TEXT SEARCH CONFIGURATION
SELECT to_tsvector('fr','Hôtels de la Mer');
to_tsvector
-------------------
'hotel':1 'mer':4
(1 row)

marc=# SELECT to_tsvector('fr','Hôtel de la Mer') @@ to_tsquery('fr','Hotels');
?column?
----------
t
(1 row)
Cela vous permet donc, sans changer une ligne de code, et en gardant les caractères accentués, de rechercher maintenant sans accent.

vacuumdb --analyze-only

Comme son nom l'indique, on peut maintenant utiliser vacuumdb pour passer des analyses uniquement. Cela peut être pratique dans une crontab.

Amélioration du module contrib hstore

Ce contrib, déjà très pratique, devient encore plus puissant :

  • La limite de taille sur les clés et valeurs a été supprimée.
  • Il est maintenant possible d'utiliser GROUP BY et DISTINCT
  • De nombreux opérateurs et fonctions ont été ajoutés

Un exemple serait trop long, tellement ce module est riche. Lisez la documentation sans perdre de temps !

Texte requête dans auto_explain

Le module contrib auto_explain affichera maintenant le code de la requête en même temps que son plan, ce qui devrait en augmenter la lisibilité.

Compteurs sur buffers dans pg_stat_statements

Ce module contrib, déjà très utile, vient de rajouter des compteurs. Pour rappel, son intérêt est de stocker des statistiques sur les requêtes exécutées par le moteur. Jusque là, il donnait la requête, le nombre d'exécutions, son temps cumulé et le nombre d'enregistrements cumulés. Maintenant, il collecte aussi des informations sur les entrées sorties (en cache, et réelles).

marc=# SELECT * from pg_stat_statements order by total_time desc limit 2;
-[ RECORD 1 ]-------+---------------------
userid | 10
dbid | 16485
query | SELECT * from fils ;
calls | 2
total_time | 0.491229
rows | 420000
shared_blks_hit | 61
shared_blks_read | 2251
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
-[ RECORD 2 ]-------+---------------------
userid | 10
dbid | 16485
query | SELECT * from pere;
calls | 2
total_time | 0.141445
rows | 200000
shared_blks_hit | 443
shared_blks_read | 443
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0

On peut donc, une fois ce contrib installé, répondre aux questions suivantes :

  • Quelle est la requête la plus gourmande en temps d'exécution cumulé ?
  • Quelle est la requête qui génère le plus d'entrées sorties ? (attention, les données peuvent être tout de même dans le cache système)
  • Quelles requêtes utilisent principalement le cache (et ne gagneront donc pas à le voir augmenté)
  • Qui effectue beaucoup de mises à jour de bloc ?
  • Qui génère des tris ?

'local' et 'temp' correspondent aux buffers et entrées des tables temporaires et autres opérations locales (tris, hachages) à un backend.

passwordcheck

Ce module contrib permet de vérifier les mots de passe, et d'empêcher les plus mauvais de rentrer. Après l'avoir installé comme décrit dans la documentation, voici le résultat :

marc=# ALTER USER marc password 'marc12';
<marc%marc> ERROR: password is too short
<marc%marc> STATEMENT: ALTER USER marc password 'marc12';
ERROR: password is too short
marc=# ALTER USER marc password 'marc123456';
<marc%marc> ERROR: password must not contain user name
<marc%marc> STATEMENT: ALTER USER marc password 'marc123456';
ERROR: password must not contain user name

Ce module souffre de limitations, principalement dues au fait que PostgreSQL permet l'envoi d'un mot de passe déjà encrypté à la base au moment de la déclaration, ce qui l'empêche de le vérifier correctement. Néanmoins, c'est une avancée dans la bonne direction.

Par ailleurs, le code du module contrib est bien documenté, ce qui permet de l'adapter à vos besoins (entre autres, il est très facile d'y activer la cracklib, afin d'effectuer des contrôles plus complexes).

marc.cousin@dalibo.com