Questions d'entretien PostgreSQL
1. Quels sont les avantages de PostgreSQL par rapport aux autres SGBD ?
PostgreSQL est un SGBD relationnel open source reconnu pour : sa conformité SQL avancee, le support des types JSON/JSONB (document store), les extensions (PostGIS pour le géospatial, pg_trgm pour la recherche floue), les CTE et fonctions fenetre, le MVCC (Multi-Version Concurrency Control) performant, les types personnalisés, et la réplication logique. Il est souvent choisi pour les applications nécessitant des requêtes complexes et une fiabilité maximale.
2. Expliquez les différents types d'index dans PostgreSQL.
B-tree (par défaut) : idéal pour les comparaisons d'egalite et de plage. Hash : uniquement egalite, plus rapide que B-tree pour ce cas. GiST (Generalized Search Tree) : pour les données geometriques, texte plein texte, ranges. GIN (Generalized Inverted Index) : pour les tableaux, JSONB, recherche plein texte. BRIN (Block Range INdex) : pour les grandes tables avec des données naturellement ordonnees (timestamp). SP-GiST : pour les structures de données partitionnees (arbres, quadtrees).
3. Comment fonctionne MVCC dans PostgreSQL ?
MVCC permet à plusieurs transactions de lire et écrire simultanément sans se bloquer. Chaque transaction voit un snapshot cohérent des données. Quand une ligne est modifiée, PostgreSQL cree une nouvelle version de la ligne (l'ancienne reste visible pour les transactions en cours). Les anciennes versions sont nettoyées par VACUUM. MVCC evite les verrous en lecture et offre d'excellentes performances en concurrence, mais nécessite un VACUUM régulier pour récupérer l'espace.
4. Qu'est-ce que EXPLAIN ANALYZE et comment l'utiliser ?
EXPLAIN ANALYZE execute la requête et affiche le plan d'exécution réel avec les temps et les lignes réelles (vs estimees). Éléments à analyser : Seq Scan (scan sequentiel, potentiellement lent sur les grandes tables), Index Scan (utilisation d'un index), Nested Loop/Hash Join/Merge Join (stratégies de jointure), actual rows vs planned rows (écarts indiquant des statistiques obsolètes). Utilisez EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) pour des informations detaillees.
5. Expliquez la différence entre les niveaux d'isolation des transactions.
PostgreSQL supporte 4 niveaux : Read Uncommitted (identique à Read Committed dans PostgreSQL), Read Committed (défaut, chaque instruction voit les commits des autres transactions), Repeatable Read (la transaction voit un snapshot fige, erreur si conflit de sérialisation), Serializable (garantie que les transactions s'exécutent comme si elles étaient sequentielles, SSI — Serializable Snapshot Isolation). Plus le niveau est eleve, plus les garanties sont fortes mais les risques de rollback augmentent.
6. Comment gérer les performances sur les grandes tables ?
Techniques : partitionnement (PARTITION BY RANGE/LIST/HASH, divise une table en sous-tables), index partiels (CREATE INDEX ... WHERE condition, plus petits et rapides), index couvrants (INCLUDE pour éviter le heap lookup), materialized views (données precalculees, REFRESH MATERIALIZED VIEW), VACUUM et ANALYZE réguliers, pg_stat_statements pour identifier les requêtes lentes, connection pooling avec PgBouncer.
7. Qu'est-ce que les CTE et les fonctions fenetre ?
Les CTE (Common Table Expressions, WITH ... AS) permettent de définir des sous-requetes nommées et réutilisables. Elles améliorent la lisibilité et permettent les requêtes récursives (WITH RÉCURSIVE). Les fonctions fenetre (OVER()) effectuent des calculs sur un ensemble de lignes liées à la ligne courante : ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER, AVG() OVER. Elles sont essentielles pour les rapports, les classements et les calculs cumulatifs.
8. Comment fonctionne la réplication dans PostgreSQL ?
Réplication physique (streaming) : replique l'integralite de la base au niveau des WAL (Write-Ahead Log). Le primaire envoie les WAL au standby en continu. Utilisee pour la haute disponibilité et le load balancing en lecture. Réplication logique : replique les changements au niveau des tables (INSERT, UPDATE, DELETE). Plus flexible : permet la réplication selective, entre versions différentes, et les transformations. Outils : patroni pour le failover automatique, pgBouncer pour le connection pooling.
9. Expliquez les extensions les plus utiles de PostgreSQL.
pg_stat_statements : statistiques sur les requêtes exécutées (temps, appels, lignes). PostGIS : types et fonctions géospatiales. pg_trgm : recherche de similarite textuelle (LIKE rapide, recherche floue). uuid-ossp / pgcrypto : génération d'UUID et fonctions cryptographiques. pg_cron : planification de tâches. timescaledb : optimisation pour les séries temporelles. pgvector : recherche vectorielle pour les embeddings AI. Installez avec CREATE EXTENSION.
10. Comment sécuriser une base PostgreSQL en production ?
Configurez pg_hba.conf pour contrôler les méthodes d'authentification par source (md5/scram-sha-256 pour les connexions distantes). Activez SSL/TLS pour le chiffrement en transit. Utilisez des rôles granulaires (GRANT/REVOKE) et le principe du moindre privilege. Activez le logging des connexions et des requêtes lentes. Chiffrez les sauvegardes. Mettez à jour régulièrement vers la dernière version mineure. Configurez des row-level security policies pour un contrôle d'accès fin.