PostGreSQL vs Microsoft SQL Server

Partie 1 : performances des commandes pour le DBA

Frédéric BROUARD

Pour réagir au contenu de cet article, un espace de dialogue vous est proposé sur le forum. Commentez

- Titre : PostGreSQL vs Microsoft SQL Server

- Auteur : Frédéric BROUARD

- Parution : 31 janvier 2021

- Mise à jour : 2 février 2021

- Public visé :

- Licence : Copyright ® 2020 Frédéric BROUARD. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. L’auteur

- II. Introduction

- III. Matériel de test utilisé

- IV. Versions des SGBDR

- V. Applications clientes

- VI. Configuration des SGBDR

- VII. Configuration de la base de données

- VIII. Protocole de test

- IX. Téléchargement des requêtes de test

- X. Script de définition des tables

- XI. Requêtes DBA

- XII. Remerciements Developpez.com

I - L’auteur

Blog :
https://blog.developpez.com/sqlpro
Le site :
https://sqlpro.developpez.com

II - Introduction

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.

III - Matériel de test utilisé

- CPU : 2 sockets (Intel Xeon E5-2680 v3).

- 128 Go de RAM.

- OS Windows 10 Enterprise.

IV - Versions des SGBDR

- SQL Server 2019 édition Developper.

- PostGreSQL v 13.1 édition communautaire.

V - Applications clientes

- SQL Server management Studio v 18.5 (pour SQL Server).

- PGadmin 4 v 4.29 (pour PostGreSQL).

VI - Configuration des SGBDR

- 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;

VII - Configuration de la base de données

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.

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

IX - Téléchargement des requêtes de test

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.

X - Script de définition des tables

SQL Server

PostGreSQL

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.

XI - Requêtes DBA

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

SQL Server

temps ms

PostGreSQL

temps ms

ratio (MS/PG)

DBA 1

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

19 729

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

50 850

2.58

DBA 2

ALTER TABLE T_PERSONNE_PRS REBUILD;
GO

2 269

VACUUM FULL t_personne_prs;
REINDEX TABLE t_personne_prs;

80 000

35.26

DBA 3

CREATE INDEX X_1
ON T_PERSONNE_PRS (PRS_NOM);

2 348

CREATE INDEX X_1
ON T_PERSONNE_PRS (PRS_NOM);

37 713

16.06

DBA 4

CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);

2 312

CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);

35 341

15.29

DBA 5

CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);

4 779

CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);

57 825

12.04

DBA 6

ALTER TABLE T_PERSONNE_PRS REBUILD
WITH(DATA_COMPRESSION = PAGE);

2 338

PostGreSQL n'a pas la possibilité de faire de la compression sur les index relationnels.

DBA 7

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

1 465

PostGreSQL n'a pas la possibilité de faire de la compression sur les index relationnels.

DBA 8

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

1 632

PostGreSQL n'a pas la possibilité de faire de la compression sur les index relationnels.

DBA 9

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

1 760

PostGreSQL n'a pas la possibilité de faire de la compression sur les index relationnels.

DBA 10

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

8 556

VACUUM FULL t_personne_prs;
REINDEX TABLE t_personne_prs;

266 000

31.09

DBA 11

INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;

13 197

INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;

102 000

7.73

DBA 12

UPDATE STATISTICS T_PERSONNE_PRS(X_1)

173

ANALYZE T_PERSONNE_PRS (prs_nom);

1 376

7.95

DBA 13

UPDATE STATISTICS T_PERSONNE_PRS(X_3)

338

ANALYZE T_PERSONNE_PRS (prs_nom, prs_prenom);

1 492

4.41

DBA 14

EXEC sp_updatestats

316

ANALYZE;

5 729

18.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…

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.

XII - Remerciements Developpez.com

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