Watt's Festival

byAndrew Vogelon2 avril 2019

La semaine dernière, j’ai été appelé à sauter sur un projet Rails qui nécessitait des optimisations de performances. Notre approche choisie a consisté à retirer un processus de recherche et de mise à jour en bloc de ActiveRecord land et à tout transférer dans Postgres. L’amélioration des performances a été énorme pour les grands ensembles de disques. En haut de gamme, nous avons vu les temps de demande passer de > 5 minutes à moins de 2 secondes.

J’avais déjà utilisé SQL et Postgres, mais je n’étais pas totalement familier avec son ensemble complet de fonctionnalités. Au début, je me suis associé à un collègue qui a une connaissance plus approfondie de Postgres. Après quelques jours d’écriture de requêtes et de refactorisation de la suite de tests, tout était vert et j’étais prêt à être déployé en staging pour les tests.

Voici quelques choses SQL et PostgreSQL intéressantes que j’ai apprises en cours de route.

Les CTE

Bien qu’elles ne soient pas exclusives à PostgreSQL, les CTE ou les expressions de table courantes sont un moyen d’écrire des requêtes réutilisables dans un système de base de données.

Disons que nous avons 2 tables – books et authors.

Table de livres

id	titre	id de l’auteur	genre
1	Forteresse Numérique	1	thriller
2	Le Code Da Vinci	1	thriller
3	Harry Potter et La Chambre des Secrets	2	fantaisie

Tableau Des Auteurs

id	nom	âge
1	Dan Brun	54
2	J. K. Loulou	53

Nous pouvons écrire un CTE très simple pour obtenir les livres avec les auteurs:

with authors_and_books as ( SELECT b.id as book_id, b.title, a.name as author_name FROM books b JOIN authors a on b.author_id = a.id; ); 

Ensuite, en sélectionnant dans notre CTE, nous obtenons ce qui suit:

SELECT * FROM authors_and_books; 

id du livre	titre	nom de l’auteur
1	Forteresse numérique	Dan Brown
2	Le Code Da Vinci	Dan Brown
3	Harry Potter et la Chambre des Secrets	J. K. Loulou

Cet exemple est simple mais vous pouvez voir comment cela peut vraiment être utile lorsque vous devez réutiliser des requêtes complexes.

Tables temporaires (PostgreSQL)

Une autre fonctionnalité de base de données très utile est la possibilité de créer des tables temporaires. Dans mon cas, cela était vraiment utile pour mettre en scène des données « non traitées » qui ont été publiées à partir du frontal. Pour créer une table temporaire, vous devrez définir son schéma comme votre instruction create table .. normale.

CREATE TEMPORARY TABLE temp_isbns ( title varchar(255) NOT NULL, isbn varchar(255) NOT NULL, author_name varchar(255) NOT NULL ) ON COMMIT DROP; 

La partie importante ici est la ON COMMIT à la fin. Vous devrez indiquer à Postgres comment gérer la table temporaire à la fin du bloc de transaction. DROP indique à Postgres de supprimer la table temporaire à la fin du bloc de transaction. Les documents Postgres décrivent plus d’options de VALIDATION.

COALESCE

La fonction COALESCE renvoie la première valeur non nulle qui lui a été transmise. Cette fonction accepte un nombre illimité d’arguments et renvoie le premier argument non nul, évalué de gauche à droite.

Voici quelques exemples de ce à quoi cela pourrait ressembler:

Exemple de coalescence 1

select coalesce(null, 1); 

fusion

Exemple de Fusion 2

select coalesce(null, null, 1, null); 

fusion

Exemple de Fusion 3

select coalesce(2, null, 1); 

fusion

Upserts

Upserter, ou mise à jour et insertion, est une fonctionnalité superful dans Postgres. Il vous permet de gérer les insertions avec résolution de conflit si un enregistrement existe déjà dans la base de données.

Rappelez-vous notre table de livres?

Table de livres

id	titre	id de l’auteur	genre
1	Forteresse Numérique	1	thriller
2	Le Code Da Vinci	1	thriller
3	Harry Potter et La Chambre des Secrets	2	fantaisie

Upserts Exemple 1

Nous allons insérer des enregistrements dans des livres mais si le livre existe, nous choisirons de ne rien faire:

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Chamber of Secrets', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT DO NOTHING; 

Maintenant, regardons notre table:

SELECT * FROM books; 

id	titre	id de l’auteur	genre
1	Forteresse Numérique	1	thriller
2	Le Code Da Vinci	1	thriller
3	Harry Potter et La Chambre des Secrets	2	fantaisie
4	Harry Potter et le Prince de Sang-Mêlé	2	fantaisie

Exemple Upserts 2

D’autre part, insérons des enregistrements dans des livres et mettons à jour le enregistrez avec l’ID correspondant. Selon les documents Postgres, vous devrez utiliser la table EXCLUDED pour référencer les valeurs proposées pour l’insertion.

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Goblet of Fire', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title; 

En regardant notre table une dernière fois:

SELECT * FROM books; 

id	titre	id de l’auteur	genre
1	Forteresse Numérique	1	thriller
2	Le Code Da Vinci	1	thriller
3	Harry Potter et La Coupe de Feu	2	fantaisie
4	Harry Potter et le Prince de Sang-Mêlé	2	fantaisie

Merci d’avoir suivi cet article de blog. S’il y a une fonctionnalité particulière de Postgres dont vous aimeriez entendre parler, n’hésitez pas à contacter [email protected]