Notes sur L'Art de PostgreSQL

Voici une sélection de citations et de notes sur le livre The Art of PostgreSQL.

Malgré quelques opinions arrêtées (mais on en est tous là, non ?) le livre est une très bonne synthèse de tout ce qu'il y a de cool dans PostgreSQL et de bonnes pratiques de conception.

Je n'ai pas réussi à charger le dump fourni (f1db.dump) dans un PostgreSQL 12 avec un pg_restore. J'ai perdu du temps pour le recréer moi-même à partir de la source avec un export MySQL compatible PostgreSQL et une conversion plus quelques opérations manuelles… L'auteur m'a assuré avoir mis à disposition des fichiers SQL pour simplifier l'import de ces données.

L'exercice a déjà été fait ailleurs : My summary of "The Art of PostgreSQL".

Parts I and II: Preface and Introduction

L'introduction illustre le propos du livre avec un premier exemple de simplification de code par une requête SQL.

La suite expose les raisons d'utiliser PostgreSQL.

So when designing your software architecture, think about PostgreSQL not as storage layer, but rather as a concurrent data access service.

On voit souvent as t() utilisé dans le livre sans explication.

Chap. 1 Structured Query Language

Le chapitre aborde les injections SQL. Vous pouvez en lire l'essentiel dans Preventing SQL Injections.

Il y est expliqué que PostgreSQL sait comment recevoir les chaînes de requête SQL séparément des paramètres SQL directement au niveau du protocole par le truchement de requêtes paramétrées (query parameterization) ou de requêtes préparées (prepared statements).

[Note perso] Mise en perspective avec Python et Django :

• l'ORM de Django utilise des requêtes paramétrées, pas encore des requêtes préparées
• la documentation de psycopg2 insiste dès le départ sur les requêtes paramétrées

Part III: Writing SQL Queries

Comme du bon code, une bonne requête SQL ne vient pas en 2 minutes, il faut parfois du temps pour la mettre en place et la peaufiner :

We often see the SQL query when it's fully formed, and rarely get to see the steps that led us there. It's the same with code, most often what you get to see is its final form, not the intermediary steps where the author tries things and refine their understanding of the problem at hand, or the environment in which to solve it.

Voir How to Write SQL.

Chap. 4 Business Logic

• une jointure LATERAL permet d'utiliser les données de la requête principale dans une sous-requête qui sera appliquée à chaque enregistrement retourné par la requête principale, comme un foreach
• voir :
  • SQL avancé pour le transactionnel
  • PostgreSQL's Powerful New Join Type: LATERAL

Chap. 5: A Small Application

• on peut définir des variables dans psql avec \set n 1 ou psql --variable 'n=1'
• aiosql permet d'organiser les instructions SQL dans des fichiers .sql, de les charger dans le code Python comme des méthodes à appeler sans perdre la possibilité de les utiliser comme n'importe quel autre fichier SQL

Chap. 6 The SQL REPL—An Interactive Setup

Met l'emphase sur psql (RTFM).

L'essentiel est dans Setting up psql, the PostgreSQL CLI.

New users of PostgreSQL often want to find an advanced visual query editing tool and are confused when psql is the answer.

• .psqlrc
• Export en HTML : psql --no-psqlrc -P format=html -d chinook -f report.sql
• Export en CSV : psql --no-psqlrc --csv -d chinook -f report.sql
• \e ouvre la dernière commande dans un éditeur
• \set ECHO_HIDDEN true montre les requêtes exécutées par des commandes slash
  • \set ECHO_HIDDEN true
  • \l+
  • \set ECHO_HIDDEN false

[Note perso] Pour la découverte et l'exploration des données, je trouve qu'une GUI c'est chouette. J'aime bien TablePlus. L'auteur cite pgAdmin et OmniDB.

Chap. 7: SQL is Code

Ce chapitre explique que le SQL est du code et que les bonnes pratiques de génie logiciel doivent s'appliquer aussi : style cohérent, tests, noms éloquents etc.

As with any programming language, comments are best used to note our intentions, which otherwise might be tricky to reverse engineer from the code alone.

S'en suit une présentation du coding style personnel de l'auteur.

Des outils facilitants l'implémentation de tests sont présentés comme pgTAP ou RegreSQL.

Chap. 8: Indexing Strategy

Présentation de l'extension pg_stat_statements qui permet de surveiller les statistiques d'exécution des requêtes SQL exécutés par un serveur.

C'est une bonne base pour commencer une analyse des besoins d'indexation en listant chaque requête dont la durée moyenne est supérieure à 10 millisecondes, ou tout autre seuil raisonnable pour votre cas d'usage.

Part IV: SQL Toolbox

Chap. 11 Structured Query Language

En tant que développeur, votre travail consiste à comprendre le problème suffisamment bien pour pouvoir le traduire en langage de programmation déclaratif.

Ensuite, le SGBDR va élaborer un plan puis l'exécuter.

Le fait que PostgreSQL soit open source facilite la confiance dans le plan d'exécution.

Chap. 13 Select, From, Where

• l'auteur présente une liste d'arguments contre select *
• format('%s %s', forename, surname) ou || pour la concatenation
• generate_series et utilisation de fonctions de calendrier directement en SQL, voir PostgreSQL and the calendar
• il y a un exemple d'utilisation d'une condition dans un JOIN plutôt que dans un WHERE dans la section Understanding Joins mais je n'ai pas encore bien compris l'avantage…
• tenter d'avoir des clauses WHERE aussi simples que possible afin de pouvoir utiliser les index pour les expressions de filtrage (attention aux OR)
• un anti-join exclut les lignes qui ne passent pas un test avec un where not exists (select 1 from subquery)

Chap. 14 Order By, Limit, No Offset

SQL doesn't guarantee any ordering of the result set of any query except when you use the order by clause.

• une clause order by accepte aussi des expressions complexes (case etc.) et des sous-requêtes
• order by position nulls last
• tri géographique kNN (k nearest neighbours) avec point() et <->
• extract et date_trunc :
  • select extract('year' from date_trunc('decade', date)) as decade from races group by decade;
• offset n'est pas recommandé pour la pagination, tout le résultat de la requête sera lu de toute façon avant d'en rejeter la plus grande partie jusqu'à atteindre la limite

Chap. 15 Group By, Having, With, Union All

La clause group by permet de faire des agrégats. L'auteur la compare à map/reduce : répartir les données en différents groupes, puis réduire chacun d'eux à une seule valeur.

Note that to avoid any ambiguity, the having clause is not allowed to reference select output aliases.

• lag et over (pour comparaison avec une ligne précédente)
• bool_and()
• count(*) filter(where …)
• grouping sets, cube, et rollup
• select repeat(text '■', ceil(100 * num_filtered / total)::int) as bar
• SQL ne permet pas de calculer un agrégat sur un agrégat, on peut y pallier en chaînant des Common Table Expressions dans un même with
• select distinct on

Chap. 16 Understanding Nulls

• voir The Three-Valued Logic of SQL
• toute comparaison booléenne avec null aura une valeur null, même null = null
• utiliser is null plutôt que = null

Chap. 17 Understanding Window Functions

[Note perso] Ça fait partie des trucs que je dois apprendre à nouveau chaque fois que j'en ai besoin.

• une Window Function se compose de :
  • la fonction à déclencher : avg, sum, count, row_number etc.
  • la définition d'un périmètre de lignes sur lesquelles va opérer la fonction : over
  • une restriction du périmètre à des groupes ayant une propriété commune avec la ligne courante : partition
  • comment trier les lignes du périmètre : order by
• voir Window Functions
• voir Why you need SQL Window Functions
• à propos de over :
  • array_agg(x) over (order by x) raccourci pour array_agg(x) over (order by x rows between unbounded preceding and current row)
  • array_agg(x) over (rows between current row and unbounded following)
  • array_agg(x) over ()
• une Window Function est toujours exécutée après la clause where

Chap. 18 Understanding Relations and Joins

L'auteur explique sa façon de comprendre les relations et les jointures dans :

• What is an SQL relation?
• What is an SQL JOIN?

En SQL, une relation est un sac d'objets qui partagent tous les mêmes caractéristiques : une liste d'attributs avec un type de données connu.

[Note perso] D'autres méthodes permettent de comprendre les jointures :

• la doc de PostgreSQL
• A Visual Explanation of SQL Joins, une approche visuelle avec croisements d'ensembles
• Joins In Steps, une approche visuelle avec croisements des tables

Et le cross join qui donnera toutes les combinaisons possibles de lignes provenant de T1 et T2 (c'est-à-dire un produit cartésien).

Part V: Data Types

Chap. 23 Denormalized Data Types

• jsonb > json
• 2 cas d'utilisation de JSON dans PostgreSQL :
  • l'application doit gérer un ensemble de documents qui sont formatés en JSON
  • le développeur n'est pas sûr de l'ensemble exact des champs nécessaires pour une partie du modèle de données, et souhaite que ce modèle de données soit très facilement extensible

Part VI: Data Modeling

Chapter 27: Tooling for Database Modeling

[Note perso] À propos des schema au sens PostgreSQL :

In PostgreSQL, the hierarchy is: host > cluster > database > schema > object

Chapter 28: Normalization

Voir les formes normales des bases de données relationnelles.

Chapter 30: Modelization Anti-Patterns

[Note perso] Je ne suis pas convaincu par l'argument présentant les clés primaires de type UUID comme un anti-pattern. Probablement parce que je le fais souvent :D

Chapter 31: Denormalization

Le chapitre donne un moyen d'utiliser les materialized views pour mettre en cache quelque chose qui est lu beaucoup plus souvent qu'il n'est mis à jour :

• create view
• create materialized view (au dessus de create view)
• refresh materialized view (pour invalider le cache)

Chap. 33 An interview with Álvaro Hernández Tortos

Stampede détecte automatiquement le schéma de vos données MongoDB et le présente sous forme de tableaux et de colonnes relationnels.

Part VII: Data Manipulation and Concurrency Control

Chap. 35 Insert, Update, Delete

• insert, update et delete acceptent une clause returning
• delete : l'élimination effective des tuples sur le disque est faite par vacuum (exécuté automatiquement en arrière-plan par un démon)

Chap. 39 Listen and Notify

Voir PostgreSQL LISTEN/NOTIFY.

La partie importante concerne les limitations du système :

It is crucial that an application using the PostgreSQL notification capabilities are capable of missing events. Notifications are only sent to connected client connections.

Any queueing mechanism requires that event accumulated when there's no worker connected are kept available until next connection, and replication is a special case of event queueing. It is not possible to implement queueing correctly with PostgreSQL listen/notify feature.

A cache maintenance service really is the perfect use case for this functionality, because it's easy to reset the cache at service start or restart.

Bon à savoir quand vous ne pouvez pas vous permettre de manquer des événements !

Chap. 40 Batch Update, MoMA Collection

Le chapitre propose une méthode basée sur quelques requêtes SQL pour faire un insert or update volumineux à partir d'un fichier CSV.

Charger un CSV dans une table PostgreSQL :

\copy moma.artist from 'artists/artists.2017-05-01.csv' with csv header delimiter ','

Chap. 41 An Interview with Kris Jenkins

The mindset of making data primary, and today's use-case secondary, is invaluable if you want a system to grow well.

I was lucky enough early in my career to get a job with a financial database company that really only existed because they had a better data model than all their competitors. The whole product fell out of the fact that they figured out a better schema than everyone else, so they could do many things their competitors struggled with, without breaking a sweat. They taught me early on that if you get your data model right, every feature is easier. You can get things right without trying while you competitors firefight their mistakes.

Parts VIII: PostgreSQL extensions

Chap. 42 What's a PostgreSQL Extension?

Lister les objets contenus dans l'extension pg_trgm :

create extension pg_trgm;
\dx+ pg_trgm

Vérifier si une extension a déjà été mise à disposition de votre instance PostgreSQL :

table pg_available_extensions;

Le reste des chapitres donne des exemples d'utilisation des extensions.