fait partie de la migration de système de base de données vers postgresql
Analyse
Avant toute optimisation, il est important d’analyser les performances : Les index et les statistiques prennent de la place et ne devraient pas être ajoutés si ils ne correspondent pas à un gain réel in-fine.
En plus du préfixe 
EXPLAIN ANALYZE, on peut activer le module pré-installé AUTO EXPLAIN:
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '100ms'; # log uniquement les requêtes plus longues que cette valeur
SET auto_explain.log_analyze = true; # EXPLAIN ANALYZE au lieu de EXPLAIN
SET auto_explain.log_nested_statements = true; #log les requêtes internes aux fonctions
Ou pour charger le module dans toutes les sessions :
ALTER SYSTEM SET session_preload_libraries = 'auto_explain';
ALTER SYSTEM SET auto_explain.log_min_duration = '100ms';
ALTER SYSTEM SET auto_explain.log_analyze = true;
SELECT pg_reload_conf();
Des outils comme explain.depesz.com ou pgmustard peuvent aider à mieux analyser les résultats obtenus.
Voir aussi pgBadger (apt install pgbadger) pour analyser les logs normaux d’utilisation et identifier les requêtes problématiques.
Compaction
VACUUM récupère l’espace de stockage libéré par des suppressions dans la base de données.
Devrait être appliqué après toute copie de table par les migrations concernées.
eCorpus supprime très peu de données: la plupart des éléments sont journalisés. Il n’est donc généralement pas nécessaire de lancer cette commande en dehors des migrations de schéma.
Peut être combiné avec ANALYZE: VACUUM ANALYZE ALL va faire un compactage complet de la base de données avec analyse des statistiques pour toutes les tables.
Statistiques
ANALYZE Collecte des statistiques sur les tables de la base de données afin de mieux informer le Query Planner.
Devrait être lancé après tout import de données massif. La commande ANALYZE devrait aussi être executée régulièrement sur toutes les bases de données (la fréquence applicable dépendant du rythme d’utilisation…)
Voir ALTER TABLE… SET STATISTICS
Un domaine intéressant d’optimisation: les statistiques multivariantes.
Par défaut, postgres considère les statistiques (most_common_size, n_distinct, etc…) comme indépendantes d’une colonne à l’autre. Or, ce n’est pas forcément vrai.
On peut créer des statistiques corrélant (par exemple) la propriété data IS NULL avec la valeur de mime ou couplant mime et name dans la table files.
Il est cependant nécessaire de bien vérifier l’impact de ces statistiques avant d’envisager leur déploiement.
TOAST
The Oversized-Attribute Storage Technique: stockage out-of-band pour les champs de longueur variable (JSON(B), BYTEA, TEXT).
TOAST permet de stocker des champs de longueur arbitraire et entre en fonction quand un enregistrement dépasse la taille de 2kB.
Ce mécanisme est utilisé presque systématiquement pour le stockage des champs « data » des documents de scène (scene.svx.json), dont la taille est généralement supérieure à 2kB.
analyse des blocs TOAST d'une table
On peut se faire une idée de la quantité de champs TOASTés par table :
SELECT
relname,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
pg_size_pretty(pg_total_relation_size(reltoastrelid)) AS toast_size
FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r' AND (
relname = 'files'OR relname = 'scenes' OR relname = 'scenes_search_terms'
);
Pour calculer par colonne, on utilise le fait que LENGTH() comptabilise les champs TOASTés, mais pas pg_column_size:
SELECT
pg_size_pretty(SUM(octet_length(name)+1)) as total_size,
pg_size_pretty(SUM(pg_column_size(name))) as inline_size
FROM files;
total_size | inline_size
------------+-------------
678 kB | 678 kB
(1 ligne)
Montre que la colonne name n’est jamais TOASTée. En revanche si on applique cette requête au champ data:
total_size | inline_size
------------+-------------
21 MB | 8585 kB
(1 ligne)
Monte qu’une grande partie de la colonne data est TOASTée
Plusieurs paramètres peuvent modifier leur comportement
Compression
via default_toast_compression ou la clause COMPRESSION de CREATE TABLE.
Ou au niveau de la colonne via ALTER TABLE … SET STORAGE.
Dans tous les cas l’interprêteur passera des colonnes en TOAST séquentiellement jusqu’à passer sous la limite des 2kB par ligne. Ces paramètres servent comme signalement de priorité.
On peut donc configurer une colonne sur STORAGE MAIN pour décourager sa conversion.
postgresql compresse les champs séquentiellement donc cette précision devrait s’appliquer uniquement si on souhaite qu’un champ soit compressé après un autre champ qui pourrait être plus petit.