PostGreSQL vs Microsoft SQL Server – Comparison part 1 : DBA command performances

This first paper speaks about some comparisons between PostGreSQL and SQL Server in pointing out the differences in terms of performances of somes administrative queries that’s ordinary DBA needs to execute.


Our test material is a HP Z840 workstation with:

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

The RDBMS are:

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

The client applications used for testing are:

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

Configuration of RDBMS:

  • SQL Server has no particular configuration. The server collation is French_100_BIN2;
  • PostGreSQL has a specific parametarization:

Configuration of the database:

SQL Server has a storage configuration as follow:

  • Data file : 12 Gb;
  • Transaction log file : 8 Gb.

PostGreSQL has a specific collation comming with the OS parameterization:

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

Our test protocol was:

  • running 10 times each queries;
  • eliminating best and worst response time;
  • computing the average response time of the 8 remaining elapsed time;
  • we stopped the other RDBMS service while testing one.

The SQL queries to reproduce the test can be download at:

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

  • The file « Benchmark test PostGreSQL.sql » contains the SQL script to create the table and populate it, and also all the queries we tested for PostGreSQL;
  • The file « Benchmark test SQL Server.sql » contains the SQL script to create the table and populate it, and also all the queries we tested for SQL Server;
  • The file « personnes.zip » contains the data to load into the table of our tests (table have two versions). It’s a ZIP file containing a text file of 239 820 Kb inside which you will find 10 million rows of persons.

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

We had to create a collation for PostGreSQL’s table 2 because PostGreSQL has very few internal collation unlike SQL Server which has over 5500.

The DBA queries are:

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

Some explanations about statistics: computing parameters are close from the one to the other. The entries are limited to 100 in PG and 200 in SQL Server. SQL Server uses a 0.5 % sample, while PostGreSQL uses a 0.4 % sample. So SQL Server does a little more extra jobs rather than PostGreSQL. For the complete database statistics (which includes system tables), SQL Server has 236 stats while PG has 423.

What we can say about the performance of the database administration queries, are the following:

  • Inserting data is about 5 times slower in PostGreSQL vs SQL Server;
  • recomputing stats is about 10 times slower in PostGreSQL vs SQL Server;
  • Creating indexes is about 14 times slower in PostGreSQL vs SQL Server;
  • Index maintenance is about 32 times slower in PostGreSQL vs SQL Server.

Creating indexes is a rare and non recurrent process. This difference will be problemtic only for big tables. The same for inserting data except if you have a sheduled import of data.

On the other hand, maintenance, like recomputing stats or defragmenting indexes is a very recurent process if you want to maintain a high level of performances. Unfortunately PosGreSQL will be nearly 30 times slower than SQL Server in this process. It is a huge difference when dealing with big databases…

Maintenance performances: PostGreSQL vs SQL Server

See also:


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

A french version of this paper can be read at URL : http://mssqlserver.fr/postgresql-vs-microsoft-sql-server-partie-1-performances-des-commandes-pour-le-dba/

Ce contenu a été publié dans Performances, PostGreSQL, SQL Server. Vous pouvez le mettre en favoris avec ce permalien.