PostgreSQL vs SQL Server - A quick database comparison

PostgreSQL and SQL Server are two of the most popular and widely used relational database management systems to date. In this post, we are going to quickly compare some of the features from the two database systems and see the differences between them.

Model

PostgreSQL: Free and open source relational database management system, maintained by PostgreSQL Global Development Group and its prolific community.
SQL Server: Commercial database management system, built and maintained by Microsoft.

Supported server operating systems:

PostgreSQL seems to be more universal. It is widely available on multiple operating systems: FreeBSD, HP-UX, Linux, NetBSD, OpenBSD, OS X, Solaris, Unix, Windows.
SQL Server is limited to Windows, and recently Linux.

Stored Procedure

PostgreSQL has user-defined functions in proprietary language PL/pgSQL or with common languages like Perl, Python, Tcl etc.
SQL Server uses Transact SQL and .NET languages.

Partitioning methods

PostgreSQL: declarative partitioning (by range or by list) since PostgreSQL 10.0.
SQL Server: tables can be distributed across several files (horizontal partitioning); sharding through federation.

Concurrency

PostgreSQL has a better concurrency management system. It handles very well the case where multiple processes can access and modify shared data at the same time.
On the other hand, SQL Server has underdeveloped concurrency and you can easily get various locked, blocked, and deadlocked reports in the log. Thus the performance of the database, as well as the application, will be slower.

Scalability

The scalability feature is directly dependent on the ability of compression of the data.

MS SQL Server offers compression out of the box but you have to implement it manually.
On the other hand, PostgreSQL offers it for free and the entire process is automatic.

Scriptability

PostgreSQL can be driven entirely from the command line.
MS SQL Server is more driven through a GUI.

Replication

SQL Server supports snapshot replication, transactional replication, merge replication. PostgreSQL uses master-slave replication.

In-memory capabilities

SQL Server has it while Postgres doesn’t.

Ease of installing and updating

Installing MS SQL Server is slow. It involves immense downloads and lengthy processes.
Install PostgreSQL is sometimes as easy as typing a single command, like this:

sudo apt-get install postgresql

Updating

SQL Server releases a new version after a few years.
PostgreSQL releases updated version regularly.

But SSMS is better than PGAdmin though!

Need a good GUI Tool for PostgreSQL or SQL Server? Try TablePlus. It’s is a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server, CockroachDB… in a fast and easy way. 

TablePlus