Optimiser l’installation et la configuration PostgreSQL

Après l’installation, plusieurs paramètres de configuration peuvent être ajustés pour optimiser les performances. Les fichiers de configuration principaux sont situés dans le répertoire /etc/postgresql/{version}/main/.

Memory Settings

• shared_buffers : Définit la quantité de mémoire partagée utilisée par PostgreSQL. Une bonne règle de base est de le définir à environ 25% de la mémoire RAM disponible.

shared_buffers = 2GB

• work_mem : La mémoire utilisée pour les opérations internes de tri et de hachage. Augmentez cette valeur si vous effectuez beaucoup de tri.

work_mem = 64MB

• maintenance_work_mem : Mémoire utilisée pour les opérations de maintenance comme VACUUM, CREATE INDEX.

maintenance_work_mem = 512MB

Cache Settings

• effective_cache_size : Une estimation de la mémoire disponible pour la mise en cache des fichiers par le système d’exploitation et PostgreSQL. En général, cette valeur est définie à environ 50-75% de la RAM.

 effective_cache_size = 6GB

Checkpoint Settings

• checkpoint_segments : Augmentez cette valeur pour réduire la fréquence des checkpoints, ce qui peut améliorer les performances en écriture.

checkpoint_segments = 32

• checkpoint_completion_target : Ajustez pour répartir la charge des checkpoints de manière plus uniforme.

Exemple : checkpoint_completion_target = 0.9

WAL (Write-Ahead Logging) Settings

• wal_buffers : Taille des buffers pour le WAL (journal de transactions).

wal_buffers = 16MB

Autovacuum Settings

• autovacuum : Assurez-vous que l’autovacuum est activé

autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_cost_delay = 20ms

Configuration du fichier pg_hba.conf

Configurez les méthodes d’authentification pour permettre les connexions appropriées (sudo nano /etc/postgresql/{version}/main/pg_hba.conf).

Ajoutez des lignes pour permettre les connexions locales et distantes :

# Connexions locales
local all all md5

# Connexions distantes
host all all 0.0.0.0/0 md5

Maintenance et Surveillance

Utilisez des outils de monitoring pour surveiller les performances de PostgreSQL et ajuster les paramètres si nécessaire.

• pgAdmin : Une interface graphique pour gérer PostgreSQL.
• pg_stat_statements : Une extension pour collecter les statistiques d’exécution des requêtes.

CREATE EXTENSION pg_stat_statements;

• pgBadger : Un analyseur de logs de PostgreSQL pour générer des rapports de performance.

En suivant cet exemple, vous pourrez configurer PostgreSQL de manière optimale pour votre environnement spécifique. Les paramètres doivent être ajustés en fonction de la mémoire disponible, des caractéristiques du matériel, et des besoins spécifiques de l’application. N’oubliez pas de surveiller régulièrement les performances et de faire des ajustements en conséquence.