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 conformite SQL avancee, le support des types JSON/JSONB (document store), les extensions (PostGIS pour le geospatial, pg_trgm pour la recherche floue), les CTE et fonctions fenetre, le MVCC (Multi-Version Concurrency Control) performant, les types personnalises, et la replication logique. Il est souvent choisi pour les applications necessitant des requetes complexes et une fiabilite maximale.
2. Expliquez les differents types d'index dans PostgreSQL.
B-tree (par defaut) : ideal 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 donnees 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 donnees naturellement ordonnees (timestamp). SP-GiST : pour les structures de donnees partitionnees (arbres, quadtrees).
3. Comment fonctionne MVCC dans PostgreSQL ?
MVCC permet a plusieurs transactions de lire et ecrire simultanement sans se bloquer. Chaque transaction voit un snapshot coherent des donnees. Quand une ligne est modifiee, PostgreSQL cree une nouvelle version de la ligne (l'ancienne reste visible pour les transactions en cours). Les anciennes versions sont nettoyees par VACUUM. MVCC evite les verrous en lecture et offre d'excellentes performances en concurrence, mais necessite un VACUUM regulier pour recuperer l'espace.
4. Qu'est-ce que EXPLAIN ANALYZE et comment l'utiliser ?
EXPLAIN ANALYZE execute la requete et affiche le plan d'execution reel avec les temps et les lignes reelles (vs estimees). Elements a analyser : Seq Scan (scan sequentiel, potentiellement lent sur les grandes tables), Index Scan (utilisation d'un index), Nested Loop/Hash Join/Merge Join (strategies de jointure), actual rows vs planned rows (ecarts indiquant des statistiques obsoletes). Utilisez EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) pour des informations detaillees.
5. Expliquez la difference entre les niveaux d'isolation des transactions.
PostgreSQL supporte 4 niveaux : Read Uncommitted (identique a Read Committed dans PostgreSQL), Read Committed (defaut, chaque instruction voit les commits des autres transactions), Repeatable Read (la transaction voit un snapshot fige, erreur si conflit de serialisation), Serializable (garantie que les transactions s'executent comme si elles etaient sequentielles, SSI — Serializable Snapshot Isolation). Plus le niveau est eleve, plus les garanties sont fortes mais les risques de rollback augmentent.
6. Comment gerer 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 eviter le heap lookup), materialized views (donnees precalculees, REFRESH MATERIALIZED VIEW), VACUUM et ANALYZE reguliers, pg_stat_statements pour identifier les requetes 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 definir des sous-requetes nommees et reutilisables. Elles ameliorent la lisibilite et permettent les requetes recursives (WITH RECURSIVE). Les fonctions fenetre (OVER()) effectuent des calculs sur un ensemble de lignes liees a 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 replication dans PostgreSQL ?
Replication 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 disponibilite et le load balancing en lecture. Replication logique : replique les changements au niveau des tables (INSERT, UPDATE, DELETE). Plus flexible : permet la replication selective, entre versions differentes, 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 requetes executees (temps, appels, lignes). PostGIS : types et fonctions geospatiales. pg_trgm : recherche de similarite textuelle (LIKE rapide, recherche floue). uuid-ossp / pgcrypto : generation d'UUID et fonctions cryptographiques. pg_cron : planification de taches. timescaledb : optimisation pour les series temporelles. pgvector : recherche vectorielle pour les embeddings AI. Installez avec CREATE EXTENSION.
10. Comment securiser une base PostgreSQL en production ?
Configurez pg_hba.conf pour controler les methodes d'authentification par source (md5/scram-sha-256 pour les connexions distantes). Activez SSL/TLS pour le chiffrement en transit. Utilisez des roles granulaires (GRANT/REVOKE) et le principe du moindre privilege. Activez le logging des connexions et des requetes lentes. Chiffrez les sauvegardes. Mettez a jour regulierement vers la derniere version mineure. Configurez des row-level security policies pour un controle d'acces fin.