Lorsque nous écrivons du SQL, nous faisons de notre mieux et essayons d'écrire du code optimisé pour la base de données. Mais parfois, nous n'obtenons pas de bonnes performances, c'est pourquoi nous devons examiner la configuration et les paramètres du serveur PostgreSQL.

Voici les paramètres importants. Vous pouvez définir tous ces paramètres dans le fichier postgresql.conf.

max_connections

Nous pouvons configurer le nombre maximum de connexions client et ceci est très important car chaque connexion requiert de la mémoire et d'autres ressources. PostgreSQL peut gérer quelques centaines de connexions, mais si nous prévoyons des milliers de connexions, nous devrions utiliser un mécanisme de pool de connexions pour réduire la surcharge.

shared_buffers

Au lieu d'un grand pool de mémoire partagée, PostgreSQL a un processus séparé pour chaque connexion de base de données. PostgreSQL utilise cette zone de mémoire dédiée pour la mise en cache, ce qui permet un gain de performance important dans votre système. Si nous avons une charge importante, nous pouvons définir de grandes valeurs pour shared_buffers. En général, nous devrions fixer les valeurs de shared_buffers jusqu'à 1/4 de la mémoire principale.

wal_buffers

Ce paramètre définit la quantité d'espace nécessaire pour la mise en cache des entrées de journal en écriture anticipée. Il s'agit d'une valeur très petite, mais il est nécessaire de la modifier pour les serveurs très chargés. La valeur recommandée est : 8MB.

work_mem

C'est également l'un des paramètres les plus importants qui est utilisé pour définir un espace de travail privé pour chaque nouvelle connexion. Nous avons shared_buffers que nous utilisons comme tampon dédié pour toutes les connexions, mais en utilisant work_mem) nous pouvons définir la mémoire pour chaque requête. Nous devons configurer ce paramètre en fonction du nombre de connexions et du type de requêtes que nous exécutons sur le serveur. Nous pouvons calculer en utilisant la méthode Mémoire(MB / Max_connections). Si le résultat est très proche de max_connections, effectuez à nouveau la division par 2. Le résultat doit être d'au moins 16 Mo, sinon nous devons ajouter plus de RAM à notre serveur.

maintenance_work_mem

C'est également le même paramètre que work_mem, mais il est utilisé uniquement pour les requêtes liées à la maintenance, comme VACUUM, CLUSTER. Généralement, la maintenance est effectuée lorsque la charge n'est pas trop élevée. Définir une grande valeur pour ce paramètre permet de terminer rapidement la tâche de maintenance. La valeur par défaut est 16MB et la valeur recommandée est (Memory / 8).

effective_cache_size

Ce paramètre est principalement dédié au planificateur de requêtes PostgreSQL car le planificateur de requêtes est responsable de l'exécution d'une requête en choisissant un chemin d'exécution optimisé. Le planificateur de requêtes a également besoin d'un certain espace pour effectuer son travail. La valeur par défaut est 65536 et la valeur recommandée est (Mémoire (Mo) * 0,75).

autovacuum

C'est également l'un des paramètres importants car PostgreSQL supporte la nature MVCC (Multi-Version Concurrency Control). Il est très important de réaliser les opérations de nettoyage (suppression les tuples morts). Cela résout beaucoup de problèmes de performance . Chaque fois qu'une table est libre, autovacuum effectue un nettoyage sur cette table, de sorte que les autres nettoyages individuels s'exécute rapidement car il a très peu à supprimer.

commit_synchrone

Vous pouvez définir le nombre de 'transactions commit' par seconde. Ce paramètre doit être activé (ON) ou désactivé (OFF). Si la valeur est à OFF, l'insertion en masse est plus rapide, mais il y a un risque d'échec, et si la valeur est à ON, l'insertion en masse peut être ralentie, mais le risque d'échec des données sera très faible.

Voir le l'article suivant pour la gestion des paramètres dans postgreSQL [Liste et modification des paramètres d'un cluster PostgreSQL].