PostGreSQL vs Microsoft SQL Server

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.

• CPU : 2 sockets (Intel Xeon E5-2680 v3).
• 128 Go de RAM.
• OS Windows 10 Enterprise.

• SQL Server 2019 édition Developper.
• PostGreSQL v 13.1 édition communautaire.

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

• SQL Server n’a aucune configuration particulière. La collation d’installation du serveur est French_100_BIN2.
• PostGreSQL a une configuration particulière qui est la suivante :

ALTER SYSTEM SET effective_cache_size = 12000000; -- 12 000 000 x 8 192 o :  96 Go
ALTER SYSTEM SET shared_buffers = 2560000;        --  2 560 000 x 8 192 o :  20 Go
ALTER SYSTEM SET work_mem = 500000;               --    500 000 x 1 024 o : 500 Mo
ALTER SYSTEM SET temp_buffers = 512000;           --    512 000 x 8 192 o :   4 Go
ALTER SYSTEM SET max_worker_processes = 48;
ALTER SYSTEM SET max_parallel_workers = 48;
ALTER SYSTEM SET max_parallel_workers_per_gather = 8;

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

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

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

• encoding = UTF8 ;
• collation = French_France.1252 ;
• character type = French_France.1252.

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.

Les requêtes permettant de reproduire nos tests peuvent être téléchargées ici :

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

• 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.

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 5500.

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

BULK INSERT T_PERSONNE_PRS 
FROM 'C:\tmp\Personnes.txt' 
WITH (FIELDTERMINATOR = ',', 
ROWTERMINATOR = '\n',
KEEPIDENTITY,
BATCHSIZE = 10000000, 
CODEPAGE = 'ACP');

COPY T_PERSONNE_PRS 
(PRS_ID, PRS_NOM , PRS_PRENOM) 
FROM 'C:\tmp\Personnes.txt' 
WITH (DELIMITER ',',
ENCODING 'WIN1252');

ALTER TABLE T_PERSONNE_PRS REBUILD;
GO

VACUUM FULL t_personne_prs;
REINDEX TABLE t_personne_prs;

CREATE INDEX X_1 
ON T_PERSONNE_PRS (PRS_NOM);

CREATE INDEX X_1 
ON T_PERSONNE_PRS (PRS_NOM);

CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);

CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);

CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);

CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);

ALTER TABLE T_PERSONNE_PRS REBUILD
WITH(DATA_COMPRESSION = PAGE);

ALTER INDEX X_1 ON T_PERSONNE_PRS 
REBUILD WITH(DATA_COMPRESSION = PAGE);

ALTER INDEX X_2 ON T_PERSONNE_PRS 
REBUILD WITH(DATA_COMPRESSION = PAGE);

ALTER INDEX X_3 ON T_PERSONNE_PRS 
REBUILD WITH(DATA_COMPRESSION = PAGE);

ALTER TABLE T_PERSONNE_PRS REBUILD; 
ALTER INDEX ALL ON T_PERSONNE_PRS REBUILD;

VACUUM FULL t_personne_prs;
REINDEX TABLE t_personne_prs;

INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;

INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;

UPDATE STATISTICS T_PERSONNE_PRS(X_1)

ANALYZE T_PERSONNE_PRS (prs_nom);

UPDATE STATISTICS T_PERSONNE_PRS(X_3)

ANALYZE T_PERSONNE_PRS (prs_nom, prs_prenom);

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…

Article suivant (à paraître) : performances comparées de la fonction d’agrégation COUNT entre PostGreSQL et 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.

Nous tenons à remercier escartefigue pour la relecture orthographique de cet article.