PostGreSQL vs Microsoft SQL Server (comparaison) – Partie 1 : performances des commandes pour le DBA

Ce premier papier parle de quelques comparaisons entre PostGreSQL et SQL Server et pointe les différences en termes de performances pour quelques-unes des requêtes et commandes administratives qu’un DBA ordinaire doit exécuter.

Matériel de test utilisé :

  • 2 sockets (Intel Xeon E5-2680 v3);
  • 128 Gb of RAM;
  • OS Windows 10 Enterprise.

Versions des SGBDR :

  • SQL Server 2019 Developper’s edition;
  • PostGreSQL v 13.1 community edition.

Applications clientes :

  • SQL Server management Studio v 18.5 (for SQL Server);
  • PGadmin 4 v 4.29 (for PostGreSQL).

Configuration des SGBDR :

  • SQL Server n’a aucun paramétrage particuleir. La collation d’installation du serveur est French_100_BIN2;
  • PostGreSQL a une configuration particulière qui est la suivante :

Configuration de la base de données :

SQL Server est configuré comme suit au niveau du stockage :

  • fichier de données: 12 Go.
  • fichier du journal de transaction : 8 Go.

PostGreSQL a une collation spécifique du fait de l’OS utilisé :

  • Encoding = UTF8;
  • Collation = French_France.1252;
  • Character type = French_France.1252.

Protocole de test :

Le protocole utilisé est le suivant :

  • exécution de chaque requête 10 fois ;
  • élimination du meilleur et du moins bon temps de réponse ;
  • calcul de la moyenne sur les huit temps de réponse restants ;
  • arrêt du service d’un SGBDR pendant le test de l’autre.

Téléchargement des requêtes de test :

https://1drv.ms/u/s!AqvZfiQYoNpBihI4efgbhodhRZec?e=07igoa

  • Le fichier « Benchmark test PostGreSQL.sql » contient le script SQL pour créer les tables et les peupler, ainsi que les requêtes que nous avons testées pour PostGreSQL ;
  • Le fichier « Benchmark test SQL Server.sql » contient le script SQL pour créer les tables et les peupler, ainsi que les requêtes que nous avons testées pour SQL Server ;
  • Le fichier « personnes.zip » contient les données pour charger la table de notre test (la table est en deux versions). C’est un fichier ZIP contenant un fichier texte de 239 820 Ko dans lequel vous trouverez 10 millions de lignes de personnes.

The tables are:

SQL ServerPostGreSQL
CREATE TABLE T_PERSONNE_PRS
(PRS_ID INT IDENTITY PRIMARY KEY,
PRS_NOM VARCHAR(32),
PRS_PRENOM VARCHAR(25))
CREATE TABLE T_PERSONNE_PRS
(PRS_ID SERIAL PRIMARY KEY,
PRS_NOM VARCHAR(32),
PRS_PRENOM VARCHAR(25));
CREATE TABLE T_PERSONNE_CI_AI_PRS
(PRS_ID INT PRIMARY KEY,
PRS_NOM VARCHAR(32) COLLATE french_ci_ai,
PRS_PRENOM VARCHAR(25) COLLATE french_ci_ai);
CREATE COLLATION fr_ci_ai
(provider = icu,
locale = 'fr-u-ks-level1-kc-false',
deterministic=false);

CREATE TABLE t_personne_ci_ai_prs
(prs_id INT PRIMARY KEY,
prs_nom VARCHAR(32) COLLATE fr_ci_ai,
prs_prenom VARCHAR(25) COLLATE fr_ci_ai);

Nous avons dû créer une collation pour PostGreSQL pour la deuxième table, car PostGreSQL possède très peu de collations internes contrairement à SQL Server qui en a plus de 5 500.

Les requêtes à destination des DBA sont les suivantes :

 SQL Servertime msPostGreSQLtime msratio (MS/PG)
DBA 1BULK INSERT T_PERSONNE_PRS
FROM 'C:\tmp\Personnes.txt'
WITH (FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPIDENTITY,
BATCHSIZE = 10000000,
CODEPAGE = 'ACP');
19 729COPY T_PERSONNE_PRS
(PRS_ID, PRS_NOM , PRS_PRENOM)
FROM 'C:\tmp\Personnes.txt'
WITH (DELIMITER ',',
ENCODING 'WIN1252');
50 8502.58
DBA 2ALTER TABLE T_PERSONNE_PRS REBUILD;
GO
2 269VACUUM FULL t_personne_prs;
REINDEX TABLE t_personne_prs;
80 00035.26
DBA 3CREATE INDEX X_1
ON T_PERSONNE_PRS (PRS_NOM);
2 348CREATE INDEX X_1
ON T_PERSONNE_PRS (PRS_NOM);
37 71316.06
DBA 4CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);
2 312CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);
35 34115.29
DBA 5CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);
4 779CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);
57 82512.04
DBA 6ALTER TABLE T_PERSONNE_PRS REBUILD
WITH(DATA_COMPRESSION = PAGE);
2 338PG does not have any compression of relational data
DBA 7ALTER INDEX X_1 ON T_PERSONNE_PRS
REBUILD WITH(DATA_COMPRESSION = PAGE);
1 465PG does not have any compression of relational indexes
DBA 8ALTER INDEX X_2 ON T_PERSONNE_PRS
REBUILD WITH(DATA_COMPRESSION = PAGE);
1 632PG does not have any compression of relational indexes
DBA 9ALTER INDEX X_3 ON T_PERSONNE_PRS
REBUILD WITH(DATA_COMPRESSION = PAGE);
1 760PG does not have any compression of relational indexes
DBA 10ALTER TABLE T_PERSONNE_PRS REBUILD;
ALTER INDEX ALL ON T_PERSONNE_PRS REBUILD;
8 556VACUUM FULL t_personne_prs;
REINDEX TABLE t_personne_prs;
266 00031.09
DBA 11INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;
13 197INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;
102 0007.73
DBA 12UPDATE STATISTICS T_PERSONNE_PRS(X_1)173ANALYZE T_PERSONNE_PRS (prs_nom);1 3767.95
DBA 13UPDATE STATISTICS T_PERSONNE_PRS(X_3)338ANALYZE T_PERSONNE_PRS (prs_nom, prs_prenom);1 4924.41
DBA 14EXEC sp_updatestats316ANALYZE;5 72918.13

Quelques explications au sujet des statistiques : :les paramètres de calcul sont proches les uns des autres. Les entrées de statistiques sont limitées à 100 dans PostGreSQL et 200 dans SQL Server. SQL Server utilise un échantillon de 0,5 %, tandis que l’échantillon de PostGreSQL est de 0,4 %. Ainsi, SQL Server fait un peu plus de travail que PostGreSQL pour le calcul des statistiques. Pour le recalcul des statistiques de toute la base de données (ce qui inclut les tables systèmes), SQL Server a 236 statistiques tandis que PostGreSQL en a 423.

Ce que nous pouvons dire au sujet des performances comparées entre PostGreSQL et SQL Server concernant les requêtes d’administration, sont les éléments suivants :

  • l’insertion des données est environ 5 fois plus lente dans PostGreSQL par rapport à SQL Server ;
  • le recalcul des statistiques est environ 10 fois plus lent dans PostGreSQL par rapport à SQL Server ;
  • la création d’index est environ 14 fois plus lente dans PostGreSQL par rapport à SQL Server ;
  • la maintenance d’index est environ 32 fois plus lente dans PostGreSQL par rapport à SQL Server.

La création d’index est une opération rare et non récurrente. La différence de performances sera problématique en ce qui concerne de grandes tables. Même chose pour l’insertion des données, sauf si vous avez des processus récurrents comme un import planifié de données.

D’un autre côté, la maintenance, comme le recalcul des statistiques ou la défragmentation des index est un processus très récurrent si vous désirez maintenir un haut niveau de performances.(des tables ou index fragmentés occupent beaucoup plus de place en mémoire cache au détriment d’autres données). Malheureusement, PostGreSQL sera près de 30 fois plus lent globalement pour ces opérations que SQL Server.. C’est une différence très significative lorsqu’on doit faire face à de grosses bases de données…

Performances de la maintenance : PostGreSQL vs SQL Server

 

Vos commentaires et remarques, comme vos protestations sont les bienvenus sur le forum de developpez.com et peuvent être postés sur cette enfilade : PostGreSQL vs Microsoft SQL Server – Partie 1 : performances des commandes pour le DBA.

 


Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* Site d'entreprise : https://www.sqlspot.com -- email : SQLpro[@]SQLspot.com *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

NEXT :Article suivant (à paraître) : performances comparées de la fonction d’agrégation COUNT entre PostGreSQL et SQL Server. (à paraître vers le 2021-03-01)

Une version en anglais de ce document est disponible à l’URL : http://mssqlserver.fr/postgresql-vs-microsoft-part-1-dba-queries-performances/

Ce contenu a été publié dans Performances, PostGreSQL, SQL Server, avec comme mot(s)-clé(s) , , , , , , . Vous pouvez le mettre en favoris avec ce permalien.