Vous démarrez un nouveau projet – ou une nouvelle entreprise – et vous avez décidé d’utiliser Postgres. La partie la plus difficile (choisir votre base de données) est terminée, et maintenant la partie la plus amusante commence : s’assurer que vous n’aurez pas besoin d’y repenser avant quelques années.

Cet article présente quelques astuces moins connues pour configurer Postgres à long terme, de la mise en commun des connexions aux bases de la sécurité, en passant par les extensions et les indices.

Configurer le pooling de connexions pour Postgres avec PGBouncer

Par défaut, Postgres crée un processus distinct pour chaque connexion client à partir du processus principal du système d’exploitation. À faible volume, le temps nécessaire à la création et à la destruction de ces processus, ainsi que le fait qu’ils ne soient jamais réutilisés, n’ont pas d’importance. Vous pouvez définir manuellement un certain nombre de max_connections, mais au fur et à mesure que vous augmenterez votre volume, vous rencontrerez probablement des problèmes à ce niveau. La mise en commun des connexions vous permet de « mettre en cache » ces processus et de les réutiliser au fur et à mesure que les clients se connectent et se déconnectent de votre base de données.

Bien que vous puissiez intégrer la mise en commun des connexions dans votre logique applicative, la plupart des gens optent pour un outil tiers, et dans le cas de Postgres, il s’agit de PGBouncer. Il s’agit d’un pooler de connexion léger et open source que vous pouvez installer sur votre serveur de base de données ou votre serveur d’application. Vous avez le choix entre trois niveaux de pooling:

  • Pooling de session : il reste fidèle au modèle « les connexions client sont indéfinies » et maintient une connexion ouverte pendant toute la durée de la connexion du client.
  • Mise en commun des transactions : les connexions ne durent que le temps d’une transaction, après quoi elles sont renvoyées dans le pool.
  • Pooling de requêtes : les connexions ne durent que le temps d’une requête, donc si vous en avez plusieurs dans le cadre d’une transaction, cela ne fonctionnera pas du tout.

La plupart des utilisateurs choisissent la mise en commun de sessions – c’est la solution la plus conservatrice et la moins risquée pour l’abandon de connexions – mais chaque application est différente et vous devrez déterminer le mode qui convient à vos contraintes.

Impact de la mise en commun des connexions sur les performances

Mais la question à un million de dollars : cela fonctionne-t-il vraiment ? Percona a effectué une série de tests pour déterminer l’impact de PGBouncer sur les performances. Avec un petit nombre de clients simultanés (<60), PGBouncer dégrade considérablement le nombre de transactions par seconde (TPS) en raison des frais généraux liés à la mise en commun. Mais lorsque vous passez à plus de 100 clients, vous commencez à voir des avantages significatifs en termes de performances.

PGBouncer
PGBouncer

Avez-vous besoin d’un pooler de connexions dès maintenant pour prendre en charge vos premiers utilisateurs ? Probablement pas. Mais l’utilisation de PGBouncer vous aidera dès que vous atteindrez un trafic faible/modéré.

La sécurité Postgres pour les nuls

Pendant les premières semaines où vous travaillez sur votre projet, il n’y a généralement qu’un ou deux développeurs qui travaillent sur une base de données vide ; la sécurité n’est pas une priorité. Mais lorsque vous lancez votre application dans le monde entier, il va falloir y penser. Et avec les bases de données, on a parfois l’impression qu’il y a un million de façons différentes de verrouiller les choses.

Restreindre l’accès au niveau de l’hôte ou de l’utilisateur

Commençons par l’accès. Postgres restreint l’accès de deux manières :

  1. Au niveau de l’hôte – en définissant des adresses IP et des domaines avec des droits d’accès
  2. Au niveau de l’utilisateur – en définissant les utilisateurs de la base de données et leurs autorisations

Le fichier pg_hba.conf dans le répertoire PGDATA est l’endroit où vous définissez qui peut se connecter à quelles bases de données. Si vous n’avez pas d’entrée pour un client dans ce fichier, il ne pourra pas accéder à la base de données. En supposant que votre serveur d’application tourne ailleurs, voici comment vous pourriez l’autoriser à accéder à la base de données :

# Trust any connection via TCP/IP from this machine
host all 127.0.0.1 255.255.255.255 trust

En dehors du simple fait de « faire confiance à toutes les connexions à partir de cette machine », il existe des tonnes de façons différentes d’authentifier votre client auprès du serveur de base de données, du mot de passe à l’identifiant en passant par les certificats. Et si vous avez renoncé au confort de RDS (ou de Kinsta) et que vous exécutez votre backend sur le même serveur que votre base de données, vous pouvez vous connecter via des sockets Unix au lieu de TCP/IP.

Autorisation et privilèges

Une fois que votre client est authentifié, vous devez vous occuper de la question de l’autorisation. La norme SQL définit un système de privilèges, et chaque objet de Postgres (comme une table, une ligne, etc.) est associé à différents privilèges qui peuvent être attribués aux utilisateurs : des choses comme SELECT et UPDATE, mais aussi TRUNCATE, REFERENCES, TRIGGER, etc. Vous attribuez des privilèges aux utilisateurs à l’aide de la commande GRANT vous permet d’attribuer des privilèges aux utilisateurs.

La meilleure pratique consiste à suivre le principe du moindre privilège, de sorte que l’utilisateur de la base de données que vous créez pour votre ou vos clients ne doit pouvoir accéder qu’à ce dont il a besoin.

Sécurité au niveau des lignes

La dernière chose à couvrir ici est la la sécurité au niveau des lignes. La sécurité au niveau des lignes existe du point de vue de la table (et non de l’utilisateur) et limite les lignes auxquelles il est possible d’accéder, de mettre à jour, etc. Par défaut, la sécurité au niveau des lignes n’est pas activée pour les tables, de sorte que votre utilisateur pourra faire ce que sa politique d’accès lui dicte. Pour activer le RLS pour une table, vous devez commencer par :

ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY

Puis ajoutez une politique. Supposons que vous souhaitiez restreindre l’accès en lecture à votre table lightsaber_internals aux personnes de confiance, qui sont déjà définies dans le groupe d’utilisateurs jedi, de sorte que seul le propriétaire d’un sabre laser puisse en voir les détails internes. Voici comment procéder :

ALTER TABLE lightsaber_internals ENABLE ROW LEVEL SECURITY
CREATE POLICY jedi_only ON lightsaber_internals TO jedi
    USING (jedi = lightsaber_jedi);

Les politiques RLS de ce type sont utiles lorsque vous avez besoin d’une sécurité à un niveau plus granulaire que les seules tables (situations avec des informations confidentielles, etc.).

Pensez à l’avance aux problèmes de mise à l’échelle

Dans toutes les startups où j’ai travaillé, il y a eu un certain degré de mise à l’échelle manuelle en ce qui concerne la base de données. Un jour, vous vous réveillez et Datadog s’affole parce que votre serveur Postgres est complètement à court d’espace. Vous enquêterez, mettrez à jour la page des incidents et augmenterez éventuellement la taille du disque jusqu’à ce que cela se reproduise (bien que la prochaine fois, il s’agira peut-être d’un problème de RAM). Il peut être utile de prendre de l’avance sur ces problèmes ! Voici quelques suggestions :

1. Mettez en place une surveillance de la base de données

La plupart des entreprises dans lesquelles j’ai travaillé utilisent Datadog pour la surveillance de leur base de données. Si vous utilisez un service de base de données géré, vous pouvez probablement vous débrouiller un peu en utilisant leurs outils natifs. Datadog a publié un bon article sur son blog couvrant les principales mesures que vous devriez surveiller, comme le débit de lecture et d’écriture, les balayages séquentiels, les données écrites sur le disque, etc.

2. Élaborez des lignes directrices pour la mise à l’échelle verticale

Lorsque votre équipe est bipée – et cela arrivera – la dernière chose que vous souhaitez, c’est que tout le monde doive mettre la main à la pâte pour résoudre le problème, alors que dans la plupart des cas, une simple mise à l’échelle résout le problème. Il est bon d’élaborer un plan de base pour votre équipe sur ce qui est possible lorsque vous manquez d’espace ou de ressources informatiques.

3. Aspirez et réglez votre Autovacuum

Lorsque vous DELETE des données dans Postgres ou UPDATE des données (ce qui est fonctionnellement équivalent à la suppression et l’insertion), Postgres ne supprime pas réellement ces données immédiatement (😱). Au lieu de cela, elles sont « marquées » comme supprimées en stockant l’ID de transaction de la suppression dans un en-tête xmax; la raison en est que cela rend le MVCC dans Postgres plus simple. Mais si ces lignes ne sont pas réellement supprimées à terme, elles commenceront à gaspiller de l’espace disque et à vous poser des problèmes.

La façon la plus simple de se débarrasser de ces lignes est d’utiliser la commande VACUUM pour se débarrasser de ces lignes. Vous pouvez lancer un vacuum manuellement lorsque des lignes mortes s’accumulent ou même simplement le configurer pour qu’il s’exécute toutes les x minutes, mais une meilleure stratégie consiste à faire un autovacuum en fonction du nombre de lignes mortes accumulées. Le réglage de votre autovacuum est un sujet nuancé qui dépasse le cadre de cet article : Je vous recommande vivement de lire l’article de 2ndQuadrant à ce sujet.

4. Mettez en place une réplique de lecture (ou deux)

Celle-ci est facile à mettre en place. Si vous anticipez une augmentation significative du trafic (un lancement à venir, etc.), vous pouvez facilement créer des répliques en lecture seule (ou au moins une) ; elles vous aideront à décharger l’instance principale de la base de données.

Si vous optez pour plusieurs répliques, vous aurez l’avantage supplémentaire d’améliorer la disponibilité si l’une d’entre elles tombe en panne pour une raison quelconque. L’ajout de répliques est assez simple chez la plupart des fournisseurs de DBaaS ; surveillez simplement le coût : elles sont souvent facturées au même niveau qu’une instance de base de données principale, bien qu’elles soient en lecture seule.

Ajoutez des index à vos tables anticipées comme les plus volumineuses

Les index de base de données permettent d’accélérer les requêtes en lecture en créant des structures de données auxiliaires qui accélèrent vos analyses. Pour de nombreux cas d’utilisation, l’ajout d’un index à une ou deux tables est une évidence. Dans Postgres, vous pouvez créer un index avec la commande CREATE INDEX (duh). Lorsque vous interrogez une table, la base de données vérifie si un index existe et l’utilise si c’est le cas (vous pouvez vérifier que c’est bien le cas avec la commande EXPLAIN pour vérifier que c’est bien le cas).

Le type d’index le plus populaire dans Postgres – et celui par défaut lorsque vous utilisez CREATE INDEX – est un index B-Tree. Essentiellement, il prend la colonne sur laquelle vous voulez créer un index, la trie et stocke des pointeurs vers les lignes triées. De cette manière, vous pouvez obtenir une efficacité de recherche binaire sur n’importe quelle colonne, et pas seulement sur la colonne sur laquelle la table est triée (s’il y en a une). Vous pouvez lire plus en détail comment ces arbres sont implémentés dans la documentation de Postgres ici.

Bien qu’utiles, les index ne sont pas une partie de plaisir ; ils prennent de la place et si vous ne faites pas attention au nombre et au type d’index que vous créez, ils peuvent commencer à dégrader les performances de la base de données. Personne ne le dit mieux que la documentation Postgres elle-même :

« Les index sont principalement utilisés pour améliorer les performances de la base de données (bien qu’une utilisation inappropriée puisse entraîner un ralentissement des performances) »

Sous le capot, lorsque vous créez un index, Postgres matérialise une table de consultation contenant l’index et un pointeur vers l’enregistrement de l’index. Un trop grand nombre de ces tables consomme de l’espace disque, rend les requêtes INSERT plus longues et oblige le moteur de requêtes à considérer plus d’options avant de choisir comment exécuter une requête.

Bonus : Ajoutez quelques extensions Postgres

L’une des particularités de Postgres est la prise en charge native des extensions proposées par des tiers. Vous pouvez les créer à partir de SQL et de C, et elles peuvent être aussi petites que quelques instructions ou aussi grandes qu’une bibliothèque logicielle complète. L’utilisation d’extensions disponibles publiquement/open source vous aide de la même manière que l’utilisation d’un progiciel ; pourquoi écrire votre propre code quand vous pouvez utiliser celui de quelqu’un d’autre ? Voici quelques-unes des extensions Postgres les plus populaires :

Timescale

Timescale est une extension Postgres qui permet de travailler avec des données de séries temporelles. En bref, elle rend vos requêtes (beaucoup) plus rapides et stocke les données de séries temporelles de manière très efficace. Vous pouvez trouver les instructions d’installation ici, ou considérer l’option hébergée dans le cloud de Timescale si votre activité repose vraiment sur des données de séries temporelles (bien que vous en soyez probablement déjà conscient si c’est le cas).

PostGIS

PostGIS ajoute un support de Postgres pour le stockage, l’indexation et l’interrogation de données géographiques (lignes, polygones, lieux, etc.). Si vous utilisez un fournisseur de services en ligne, la plupart d’entre eux préinstallent PostGIS. Mais si vous devez l’installer vous-même, vous trouverez des instructions d’installation ici.

pg_stat_staements

pg_stat_statements crée une vue dans votre base de données Postgres avec des statistiques sur chaque requête exécutée sur la base de données. Vous pouvez voir des statistiques comme la durée d’exécution de la requête (moyenne, médiane, moyenne, etc.), qui a exécuté la requête, les accès au cache des blocs, le nombre de blocs écrits, et bien plus encore (44 colonnes au total dans cette vue). Pour l’installer, il suffit de l’ajouter à votre fichier .conf et de redémarrer le serveur.

pg_audit

pg_audit est utile aux entreprises susceptibles d’être soumises à des audits détaillés (par exemple, gouvernementaux, financiers, etc.). Vous pouvez faire en sorte que Postgres enregistre toutes les requêtes effectuées sur la base de données en configurant `log_statement=all`, mais cela ne signifie pas que l’information dont vous avez besoin sera facile à rechercher et à trouver. pg_audit utilise les fonctions d’enregistrement internes de Postgres pour faciliter la recherche et le travail avec les enregistrements dont un auditeur pourrait avoir besoin. Vous pouvez trouver les instructions d’installation ici.

Résumé

Postgres est une option formidable (et très populaire) sur laquelle vous pouvez construire votre entreprise et que nous sommes fiers de soutenir chez Kinsta. Nous espérons que ces conseils vous aideront à démarrer et à vous préparer à l’expansion. D’autres conseils ou réflexions issus de votre expérience ? Faites-nous en part ici.

Justin Gage

Justin is a technical writer and author of the popular Technically newsletter. He did his B.S. in Data Science before a stint in full-stack engineering and now focuses on making complex technical concepts accessible to everyone.