PostgreSQL, aussi connu sous le nom de Postgres, est un système de gestion de base de données relationnelle et objet (SGBDRO). C'est un outil libre disponible selon les termes d'une licence de type BSD mettant l’accent sur l’extensibilité et la conformité avec SQL. Il prend en charge les transactions avec les propriétés ACID (Atomicité, Cohérence, Isolation, Durabilité) et offre des fonctionnalités avancées telles que les vues automatiquement mises à jour, les vues matérialisées (materialized views), les déclencheurs (trigger), les clés étrangères (foreign keys) et les procédures stockées (stored procedures). PostgreSQL est compatible avec les principaux systèmes d’exploitation, notamment Windows, Linux, macOS, FreeBSD et OpenBSD. Il peut gérer une grande variété de charges de travail, allant des bases de données sur une seule machine aux entrepôts de données, aux data lakes et aux services web avec de nombreux utilisateurs simultanés.
PostgreSQL est comparable à d'autres systèmes de gestion de base de données, qu'ils soient libres (comme MariaDB et Firebird), ou propriétaires (comme Oracle, MySQL, Sybase, DB2, Informix et Microsoft SQL Server). Comme les projets libres Apache et Linux, PostgreSQL n'est pas contrôlé par une seule entreprise, mais est fondé sur une communauté mondiale de développeurs et d'entreprises.
Le PostgreSQL Global Development Group se concentre exclusivement sur le développement du moteur de base de données et des composants étroitement liés. Ce noyau constitue techniquement PostgreSQL lui-même, mais une vaste communauté de développeurs et un écosystème fournissent de nombreuses fonctionnalités supplémentaires qui, traditionnellement, seraient proposées par un éditeur de logiciel propriétaire. Cela inclut des fonctionnalités spécifiques, comme le support des bases de données géospatiales ou temporelles, ainsi que des extensions permettant d’émuler d’autres systèmes de bases de données. De nombreux outils tiers proposent également des interfaces utilisateur graphiques ainsi que des solutions d’équilibrage de charge et de haute disponibilité. Ces contributions jouent un rôle essentiel dans l’adoption et l’utilisation de PostgreSQL, constituant ainsi un vaste écosystème autour du moteur de base de données.
Histoire L'histoire de PostgreSQL remonte à la base de données Ingres, développée à Berkeley par Michael Stonebraker. Lorsque ce dernier décida en 1985 de recommencer le développement de zéro, il nomma le logiciel Postgres, comme raccourci de post-Ingres. Lors de l'ajout des fonctionnalités SQL en 1995, Postgres fut renommé Postgres95. Ce nom fut changé à la fin de 1996 en PostgreSQL.
Le projet est organisé de manière à maintenir plusieurs versions stables en parallèle, ainsi qu'un dépôt mis à jour en continu.. Cette pratique est extrêmement appréciable pour les productions des systèmes informatiques car elle leur permet de lisser les besoins de migration obligée de leurs applications qui utilisent des bases de données sur les moments de faible charge et de disponibilités de leurs ressources.
Principales caractéristiques Ce SGBDRO utilise des types de données modernes, dits composés ou enrichis suivant les terminologies utilisées dans le vocable informatique usuel. Ceci signifie que PostgreSQL peut stocker plus de types de données que les types simples traditionnels entiers, caractères, etc. L'utilisateur peut créer des types, des fonctions, utiliser l'héritage de type, etc.
PostgreSQL est plus avancé que ses concurrents dans la conformité aux standards SQL (énoncé vrai entre 2012 et 2015). PostgreSQL est pratiquement conforme (de plus en plus conforme) aux normes ANSI SQL 89, SQL 92 (SQL 2), SQL 99 (SQL 3), SQL:2003 et SQL:2008 Documentation PostgreSQL 9.0 - Conformité SQL : « PostgreSQL supporte la plupart des fonctionnalités majeures de SQL:2008. Sur les 179 fonctionnalités requises pour une conformité « centrale » complète (full Core conformance), PostgreSQL se conforme à plus de 160. ». Il fonctionne sur diverses plates-formes matérielles et sous différents systèmes d'exploitation.
PostgreSQL fonctionne sur Solaris, SunOS, macOS, HP-UX, AIX, Linux, IRIX, Digital Unix, BSD, NetBSD, FreeBSD, OpenBSD, SCO unix, NeXTSTEP, UnixWare et toutes sortes d'Unix. Depuis la version 8.0, PostgreSQL fonctionne également nativement sur Windows. Avant la version 8, il fallait une couche de compatibilité POSIX (par exemple cygwin) pour faire fonctionner PostgreSQL sur ce système d'exploitation.
PostgreSQL est largement reconnu pour son comportement stable, proche de Oracle, mais aussi pour ses possibilités de programmation étendues, directement dans le moteur de la base de données, via PL/pgSQL. Le traitement interne des données peut aussi être couplé à d'autres modules externes compilés dans d'autres langages.
Outre sa capacité à gérer des bases de données volumineuses : «La base la plus volumineuse pèse environ 30 Tera-octets», PostgreSQL est souvent utilisé pour sa capacité à gérer des bases de données spatiales (SIG), grâce à son extension PostGIS qui intègre une riche bibliothèque de fonctions géographiques.
Multiversion concurrency control (MCC ou MVCC) PostgreSQL gère la concurrence grâce au contrôle de concurrence multiversion ou Multiversion concurrency control (MVCC), en attribuant à chaque transaction une vue cohérente et isolée de l'état de la base de données à un instant donné, ce qui permet d'apporter des modifications sans affecter les autres transactions. Cela élimine en grande partie la nécessité d'utiliser des verrous en lecture et garantit que la base de données respecte les principes ACID (Atomicité, Cohérence, Isolation, Durabilité). PostgreSQL propose quatre niveaux d'isolation des transactions : Read Uncommitted, Read Committed, Repeatable Read et Serializable. Étant donné que PostgreSQL est protégé contre les lectures sales, demander un niveau d'isolation des transactions Read Uncommitted renvoie en réalité un niveau Read Committed. PostgreSQL supporte un ordonnancement complet (ou Serializability) grâce à la méthode de serializable snapshot isolation (SSI). L'implémentation du MVCC dans PostgreSQL peut rencontrer des problèmes de performance nécessitant un réglage lorsqu'elle est soumise à une charge d'écriture importante, particulièrement lors de la mise à jour de lignes existantes.
Stockage et réplication
Réplication PostgreSQL intègre une réplication binaire native basée sur l’envoi des modifications, que l'on nomme des journaux de reprise anticipée ou Write-Ahead Logs (WAL), vers des nœuds répliqués de manière asynchrone. Ces nœuds permettent d’exécuter des requêtes en lecture seule, ce qui facilite la répartition du trafic de lecture entre plusieurs serveurs. Auparavant, les solutions de réplication offrant une mise à l’échelle similaire des lectures s’appuyaient généralement sur l’ajout de déclencheurs de réplication sur le serveur principal, augmentant ainsi sa charge.
PostgreSQL inclut également une réplication synchrone intégrée qui garantit que, pour chaque transaction d’écriture, le serveur principal attend qu’au moins un nœud répliqué ait écrit les données dans son journal des transactions. Contrairement à d’autres systèmes de bases de données, PostgreSQL permet de spécifier le niveau de durabilité d’une transaction (asynchrone ou synchrone) selon plusieurs critères : par base de données, par utilisateur, par session ou même par transaction. Cela s’avère utile pour certaines charges de travail ne nécessitant pas de telles garanties, car la réplication synchrone peut affecter les performances en raison de l'attente de confirmation du serveur de secours.
Les serveurs de secours, également appelés serveurs en attente, peuvent être synchrones ou asynchrones. Dans le cadre de la réplication synchrone, la configuration permet de spécifier les serveurs candidats. Le premier serveur de la liste qui assure un flux actif devient le serveur synchrone principal. En cas de panne, le système bascule automatiquement vers le serveur suivant dans la liste. Le terme serveur de secours est généralement utilisé lorsqu'il s'agit de haute disponibilité et de basculement automatique en cas de panne, tandis que serveur d'attente (standby server) désigne plutôt un serveur qui reste passif et ne prend le relais qu'en cas de besoin.
La réplication synchrone multi-maître n’est pas incluse dans le cœur de PostgreSQL. Cependant, Postgres-XC, basé sur PostgreSQL, fournit une réplication multi-maître synchrone évolutive et est distribué sous la même licence que PostgreSQL. Un projet associé, Postgres-XL, propose des fonctionnalités similaires, tandis que Postgres-R est un autre fork axé sur la réplication. La réplication bidirectionnelle (BDR) est un système de réplication multi-maître asynchrone pour PostgreSQL.
Des outils comme repmgr simplifient la gestion des clusters de réplication.
Plusieurs solutions de réplication asynchrone basées sur des déclencheurs restent pertinentes malgré l’introduction des capacités natives de réplication binaire, notamment dans les cas où la réplication complète d’un cluster de bases de données n’est pas appropriée. Parmi ces solutions :
Slony-I Londiste, intégré dans SkyTools Bucardo, solution de réplication multi-maître SymmetricDS, solution de réplication multi-maître et multi-niveaux
Indexation PostgreSQL intègre en natif la prise en charge des index classiques de type B-tree et table de hachage (hash table), ainsi que quatre méthodes d'accès aux index :
GiST (Generalized Search Trees) : une structure d'indexation flexible permettant des recherches avancées, notamment pour les types de données géospatiaux et textuels. GIN (Generalized Inverted Indexes) : conçu pour indexer des valeurs contenant plusieurs éléments, comme des tableaux ou du texte en recherche full-text. SP-GiST (Space-Partitioned GiST) : adapté aux structures de données permettant un partitionnement spatial, comme les arbres quaternaires (quad-trees) ou les arbres de trie. BRIN (Block Range Indexes) : optimisé pour les très grandes tables en indexant uniquement des plages de blocs de données, plutôt que chaque ligne individuellement. En plus de ces types d'indexation, PostgreSQL permet la création de méthodes d'indexation définies par l'utilisateur, bien que ce processus soit complexe. PostgreSQL prend également en charge plusieurs fonctionnalités avancées en matière d'indexation :
Index d'expression (Expression Indexes): permet de créer un index sur le résultat d'une expression ou d'une fonction, plutôt que sur la valeur brute d'une colonne. Index partiels (Partial indexes) : indexe uniquement une partie d'une table grâce à l'ajout d'une clause WHERE dans la commande CREATE INDEX. Cela permet de limiter la taille de l'index et d'améliorer les performances. Combinaison d'index multiples : le planificateur de requêtes (query planner) peut exploiter plusieurs index simultanément pour optimiser l'exécution de requêtes complexes. Cela est particulièrement utile pour les entrepôts de données (data warehouses) utilisant des schémas en étoile (star schema). Indexation des k plus proches voisins (k-nearest neighbors ou k-NN) : grâce à la méthode KNN-GiST, PostgreSQL peut rechercher efficacement les valeurs les plus proches d'un point donné. Cela est particulièrement utile pour les recherches de similarité (fuzzy search), les bases de données textuelles ou les données géospatiales. Scans uniquement sur index (Index-Only Scans) : dans certains cas, PostgreSQL peut récupérer les données directement depuis un index sans accéder à la table principale, réduisant ainsi le nombre d'opérations disque et accélérant les requêtes.