Think se rend au PGDay Paris 2019

Cette année, Stéphane, David et Frédéric, 3 DBA de Think, se sont rendus au PGDay Paris 2019. Cet événement en langue anglaise a pour but de rassembler la communauté autour de conférences données par des intervenants venus de toute l’Europe.

 4 sujets nous ont particulièrement intéressés :

  • B-Tree include column
  • Breaking Postgres at scale
  • Bi-temporality in Postgres
  • « Le bon coin » use case

B-tree include column

 
Markus Winand (use-the-index-luke) a basé sa présentation sur la nouvelle cause INCLUDE dans les index B-tree apparue en Postgres 11. Cela permet d’avoir une colonne dans un index qui n’est pas une clef. Cette colonne ne pourra utiliser l’index que dans la projection, pas dans le filtre. Le gain d’espace disque n’est pas très important, on a un peu de mal à voir l’intérêt au premier abord, mais c’est conceptuellement que c’est intéressant. Quand un DBA voit ce genre de DDL, il peut tout de suite comprendre qu’il a été mis en place car on avait besoin d’un index couvrant sur plusieurs colonnes mais que la colonne référencée dans l’include n’est utilisée dans aucun filtre. Cela facilite grandement la réflexion sur la création ou non de nouveaux index. 
 

Breaking Postgres at scale

Christophe Pettus nous a présenté sa vision de l’infra/tunning, à rajouter au fur et à mesure de l’agrandissement des données sur un nouveau projet. Sans être une règle absolue, c’est un bon rappel de ce qu’il y a à prévoir avant chaque étape. 

10G:

  • Tout tient en mémoire, la base est rapide même si le code SQL n’est pas optimisé.
  • On peut laisser la plupart des paramètres par défaut sans trop de problème.
  • Les upgrades peuvent se faire par export/import.
  • Si une réplication est mise en place, le failover peut être manuel.

100G:

  • La base est trop grosse pour être contenue en mémoire mais l’effective_cache_size peut encore être supérieur au plus gros index.
  • Mise en place de sauvegarde physique avec PITR. Wal-E était un must, il se fait détroner par pgBackRest.
  • Tunning de paramètres (les pools mémoires, le random_page_cost et seq_page_cost selon le type de disque,…)
  • Automatic failover (pgpool, patroni, service managé)
  • upgrade par pgupgrade

1To:

  • Besoin de beaucoup de mémoire
  • Il devient nécessaire de mettre en place des backups incrémentaux
  • Taille des WAL en Go
  • Besoin de réplication pour soulager la base principale des requêtes en lecture seule.
  • Les jobs de vacuum commencent à poser problème
  • Le partitionnement peut s’envisager
  • L’utilisation du parallélisme apporte un confort supplémentaire
  • Une surveillance plus fine de l’utilisation des indexes et du calcul des statistiques est nécessaire.

10To:

  • plus de compute que peut soutenir 1 serveur, sharding des données.

1Po:

  • Data federation. Uniquement par foreign data wrapper pour l’instant 

Bi temporality in Postgres

La conférence la plus passionnante était présentée par Miroslav Sedivy. Le sujet était de faire du time-versionning des datas par l’utilisation du type RANGE et des index GIST.
 
Dans une table on peut facilement stocker la date d’insertion de la données en ajoutant une colonne « inserted » avec la date comme valeur par défaut. On pourrait avoir une colonne « updated » avec un trigger de mise à jour mais comment enregistrer une suppression?

On peut considérer chaque donnée comme ayant une date de validité avec 2 bornes par ex valid_since et valid_until 

TABLE customer
id | name | fee | valid_since | valid_until
———————————————-
1 | alice | 10 | 2019-01-01 | 2019-03-12
1 | alice | 15 | 2019-03-12 | NULL                 <- donnée modifiée
2 | bob | 20 | 2019-01-01 | 2019-03-12             <- donnée supprimée
3 | carol | 30 | 2019-03-12 | NULL                 <- donnée valide (sans date de fin)

Utilisation de RANGE

Au lieu d’utiliser 2 colonnes avec des dates de validité, on peut passer sur du type range.

Notre table deviendrait:

TABLE customer
id | name | fee | valid
———————————————-
1 | alice | 10 | [2019-01-01, 2019-03-12)
1 | alice | 15 | [2019-03-12, NULL)
2 | bob | 20 | [2019-01-01, 2019-03-12)
3 | carol | 30 | [2019-03-12, NULL)

On a des id customer en double, découpons la table en 2 pour pouvoir utiliser une PK. On va devoir faire une contrainte d’intégrité sur du range, pour cela il nous faut un type d’index disponible dans l’extension btree_gist.

CREATE TABLE customer (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE);
id | name
1 | alice
2 | bob
3 | carol

CREATE TABLE customer_rev (
id INTEGER REFERENCES customer(id),         <- foreign key sur l’id customer
name TEXT UNIQUE); fee INTEGER NOT NULL,
valid TSTZRANGE NOT NULL,
EXCLUDE USING GIST (id WITH =,              <- contrainte d’intégrité pour chaque id
valid WITH &&));                            les ranges ne doivent pas se chevaucher
 id | fee | valid

————— ————————————–
1 | 10 | [2019-01-01, 2019-03-12)
1 | 15 | [2019-03-12, NULL)
2 | 20 | [2019-01-01, 2019-03-12)
3 | 30 | [2019-03-12, NULL)

Exemple de requêtes

Les données en cours de validité

SELECT username
FROM customer JOIN customer_rev USING (id)
WHERE UPPER_INF(valid)                      <- l’enregistrement est en cours car la borne   supérieur est infinie (null)

Les données au 15 janvier

SELECT username
FROM customer JOIN customer_rev USING (id)
WHERE valid @> ‘2019-01-15’                 <- la plage de date contient la date ‘2019-01-15’

Avec ce modèle on peut facilement rajouter des dimensions supplémentaire. Par exemple :

  • Quand le fait décrit dans l’enregistrement s’est produit (date de l’événement)
  • Quand l’enregistrement a été pris en compte dans la base (numéro de révision)

« Le bon coin » use case

Et enfin, Flavio Gurgel nous a présenté l’infrastructure actuellement en place pour le site “Le Bon Coin”. Il nous a fait un rappel de l’évolution des installations de PostgreSQL.
Tout d’abord, Le Bon Coin est le 1er site français dans le top 10 mondial. Environ 28M d’utilisateurs uniques/mois! La base de données qui fait tourner le site: PostgreSQL.   

Aujourd’hui, c’est plus de 70 serveurs qui fournissent le service. Certains en primaire, la plupart en secondaire en lecture seule en utilisant la réplication intégrée de PostgreSQL (d’ailleurs Le Bon Coin utilisait la réplication Slony jusqu’à peu).     

Concernant les sauvegardes, c’est un mix pg_dump et barman qui est utilisé. Le prochain  snapshot d’instance est réalisé en fonction de la volumétrie de journaux générés par la base: peu de journaux -> 2 fois par mois, plus d’1To de journaux -> quotidien.   

Ensuite, Flavio est rentré plus dans les détails lors de la montée de version 9.3 vers 10. Cette version était très attendue car elle apportait son lot d’améliorations, notamment, les requêtes parallèles, les informations du lag de réplication, et les slots de réplications pour n’en citer que quelques unes.

=> En conclusion, les conférences suivies étaient très enrichissantes, merci aux organisateurs.

=> Présentations PG Day 2019

Share