PostgreSQL, c'est quoi ?

PostgreSQL est un système de gestion de base de données relationnel-objet (SGBDRO) open source typiquement utilisé comme base de données de production et base de données analytique. Tendis que MySQL fait passer l'évolutivité et la performance avant tout, Postgres accorde la priorité à l'extensibilité et la conformité SQL.

En général, les organisations qui souhaitent préserver un haut niveau d'intégrité et de personnalisation des données optent pour Postgres. Cela est dû à la compatibilité de PostgreSQL avec les transactions ACID et les procédures personnalisées.

Contrairement aux autres bases de données transactionnelles open source, Postgres accorde aussi la priorité à la conformité ANSI SQL, contenant une bibliothèque SQL complète et de nombreuses fonctions à utiliser pour les tâches analytiques de pointe.

Le système Postgres peut être téléchargé gratuitement et installé sur du matériel ordinaire, ou il peut tourner dans le cloud via divers fournisseurs. Bien que Postgres offre de nombreuses fonctions et soit développé pour les tâches OLAP, la performance du système a tendance à être limitée lorsque les volumes de données dépassent plusieurs téraoctets.

Choisir PostgreSQL comme base de données

Extensibilité

En tant que base de données relationnel-objet, l'un des principaux avantages de Postgres est le caractère extensible de son architecture. Ce système permet à la base de données d'utiliser des fonctions définies par l'utilisateur et des bibliothèques de tiers afin de personnaliser et d'automatiser les procédures au sein de la base de données.

Conformité ANSI SQL

PostgreSQL possède une syntaxe SQL élégante et complète, ce qui est utile pour les organisations désireuses d'utiliser Postgres pour les tâches OLAP. Au moment de la rédaction de cet article, Postgres est conforme à la norme ANSI-SQL: 2008.

Haut niveau de simultanéité

Postgres utilise un contrôle d'accès simultané multi-version afin de pouvoir rédiger et lire sur les mêmes bases de données sans que l'utilisateur ne doive verrouiller la table.

Dans quel cadre PostgreSQL est-il idéal ?

Personnalisation par l'utilisateur des procédures enregistrées

En tant que base de données relationnel-objet, le point fort de PostgreSQL est que le système permet plutôt facilement aux utilisateurs de modifier les procédures et fonctions enregistrées. La capacité d'ajouter du code généré par l'utilisateur dans la base de données en temps réel fait de Postgres un candidat idéal pour le développement de prototypes et le déploiement de procédures et processus personnalisés.

Nombreuses fonctions

Pour les organisations à la recherche de nombreuses fonctions, Postgres constitue une excellente alternative aux bases de données relationnelles propriétaires populaires, comme Oracle et MSSQL, car son installation est beaucoup moins coûteuse. Cependant, en fonction de la tarification sélectionnée, ces options peuvent offrir un niveau d'assistance plus élevé que celui de Postgres.

Considérations de prix

Le logiciel PostgreSQL est open source et peut être téléchargé gratuitement. Il convient toutefois de noter que le montant total des opérations dépendra énormément du matériel alloué : plus d'espace disque, de CPU et de mémoire signifie plus de frais. Les frais liés au stockage de serveurs et à la maintenance sont des frais supplémentaires associés avec la solution PostGreSQL auto hébergée.

Si vous souhaitez exploiter un système PostgreSQL dans le cloud, le service est également offert par divers fournisseurs tels qu'Amazon Relational Database Service, Google Cloud SQL, EnterpriseDB et Heroku.

Architecture de base de données

Contrairement aux autres bases de données transactionnelles, le système PostgreSQL est déployé sur un seul serveur et il n'est généralement pas conçu pour distribuer ses fonctions de stockage ou de calcul entre plusieurs nœuds. Bien qu'il soit possible d'utiliser des techniques de partitionnement, de réplication et de rassemblement pour adapter la performance de votre cluster Postgres et améliorer sa capacité, ces solutions sont complexes et peu courantes. La plupart des organisations améliorent leur solution Postgres via l'évolutivité verticale de la base de données, ce qui implique l'achat d'un serveur plus puissant. 

L'architecture de PostgreSQL dispose de deux capacités uniques qu'il convient de mentionner.

Système de gestion de base de données relationnel-objet

PostgreSQL est catégorisé comme système de gestion de base de données relationnel-objet (SGBDRO). Cela signifie que contrairement aux systèmes de gestion de base de données relationnelle (SGBDR) traditionnels, l'architecture de Postgres est orientée objet.

La plupart des SGBDR stockent les informations sur les données contenues dans la base de données, comme les tables. Cependant, les SGBDRO comme Postgres stockent bien plus d'informations que cela, comme les informations sur les différents types de données, les fonctions SQL et bien plus encore. Postgres permet même aux utilisateurs de modifier ces tables, afin d'ajouter des nouveaux types de données et des fonctions SQL pour personnaliser le stockage de leurs données et leur interaction avec la base de données.

C'est cette architecture qui permet à Postgres d'être extensible et qui laisse les utilisateurs expérimenter avec des nouvelles applications portant sur les données stockées sans devoir modifier le code source.

Contrôle d'accès simultané multi-version

La simultanéité est l'un des autres points forts de l'architecture de PostgreSQL, ou la capacité de rédiger des données dans la base de données tout en lisant des données de la base de données.

Les autres bases de données transactionnelles ne permettent pas de rédiger et de lire les données de la même base de données. Cela est dû au fait que si la lecture se produit durant l'écriture, l'utilisateur pourrait lire des données en cours de modification et ainsi lire des données erronées.

Pour résoudre ce problème, les bases de données comme MySQL ont appliqué une politique de verrouillage de la base de données durant l'écriture des données à stocker. Cette mesure oblige les organisations à créer une réplique physique de la base de données afin de rédiger et de lire les données sur le même système.

Postgres utilise un système de contrôle d'accès simultané multi-version (MVCC), qui capture l'ensemble de la base de données afin de pouvoir la lire. Lors du chargement des données dans la base de données, des captures récentes des tables sont réalisées afin que l'utilisateur puisse les lire. Ce système permet de profiter d'une base de données sans verrouillage.

Types de données

Tout comme la plupart des bases de données transactionnelles, la compétence principale de PostgreSQL est le stockage des données relationnelles. Toutefois, Postgres est efficace avec une quantité modérée de format JSON et le système est compatible avec le parsage JSON et même avec l'index JSON via HStore. Il ne s'agit pas d'un stockage en colonnes au niveau natif. Il existe une extension gratuit (en fonction de l'hébergement) qui permet d'utiliser un stockage en colonnes car Postgres est open source.

La liste complète des types de données pris en charge par PostgreSQL est disponible ici, mais il convient aussi de noter que les utilisateurs peuvent aussi créer leurs propres types de données.

Étendue de la ligne (quantité)

La performance de PostgreSQL est affectée lorsque le volume de stockage approche les plusieurs téraoctets de données. Tout comme avec n'importe quelle base de données, ce problème peut être réglé par diverses optimisations.

Cependant, après l'usage de toutes les techniques d'optimisation disponibles, si l'on n'obtient pas une durée de requête interactive (mesurée en secondes plutôt qu'en minutes) lors de la rédaction de requêtes analytiques, il est alors temps de transférer les requêtes OLAP vers une base de données MPP.

Utiliser PostgreSQL

Installation de PostgreSQL

Comme le système est open souce, il y a très peu de choses à faire pour installer Postgres sur un système local. Outre la documentation Postgres (qui est plutôt verbeuse), il existe plusieurs guides simples pour l'installation de Postgres sur différents systèmes d'exploitation :

L'installation d'un système hébergé/cloud implique la création d'un compte et d'un cluster auprès d'Amazon AWS.

Remarque : Postgres créera un nom d'utilisateur par défaut, « postgres », qui, pour des raisons de sécurité, ne doit être utilisé que pour accéder au logiciel de la base de données.

Processus pour nouvelles données

La création de nouvelles tables dans PostgreSQL est simple avec la commande CREATE, comme avec MySQL.

Postgres utilise une fonction INSERT standard, comme MySQL. Cependant, Postgres n'exploite pas l'extension REPLACE, ce qui signifie qu'il faut utiliser des approches un peu plus laborieuses pour imiter l'opération MySQL dans Postgres.

Postgres et Python fonctionnent bien ensemble, particulièrement avec le modèle Python pyscopg2, qui permet de programmer les opérations INSERT et UPDATE.

Maintenance

Il existe énormément de ressources en ligne pour surveiller et ajuster la performance de votre système PostgreSQL.

Index

L'un des principaux facteurs affectant la performance de Postgres est la capacité à dresser l'index de vos tables au sein de votre système. Un index est l'équivalent d'une table des matières pour une base de données et qui sert à localiser les données. Tout comme la table des matières d'un livre vous empêche de devoir parcourir toutes les pages, l'index d'une base de données vous empêche de devoir examiner l'ensemble de la table d'une base de données pour localiser vos données.

L'efficacité des index dépend généralement de la bonne compréhension des différents types d'index et de leur usage efficace. Cet article explique très bien les différents types d'index et quand les utiliser.

Plans de surveillance / EXPLAIN

L'un des autres éléments importants pour assurer la performance de Postgres, c'est la rédaction efficace du langage SQL et la bonne compréhension de l'interprétation du langage SQL dans Postgres.

Le meilleur moyen de comprendre comment Postgres interprète une requête SQL déterminée est d'exécuter un plan EXPLAIN de votre requête. Ce guide Postgres explique plutôt bien la commande EXPLAIN, mais ce guide se penche sur les éléments les plus complexes de manière plus facile à comprendre.

Faits concernant PostgreSQL

  • Les utilisateurs de MySQL qui passent sur Postgres ne se rendent pas toujours compte du nombre supérieur de fonctions de requête offert par Postgres, ce qui est dommage car elles permettent parfois d'atteindre une analyse plus poussée et un langage de requête plus élégant.
  • Postgres utilise une convention de nomenclature des types de données qui est légèrement différente de MySQL. Consultez cette grille pratique pour découvrir les différences entre les types de données dans MySQL et Postgres.

Prenez goût à l'analytique

Business intelligence, analyse de big data ou vue client à 360° :
quels que soient vos besoins, Looker peut vous aider. Parlez à nos experts en données.

Demander une démo