PostgreSQL vs SQL Server

SQL Server is a popular relational DBMS developed by Microsoft. It is supplied with wide range of database management, data analytic and business intelligence tools. SQL Server is highly integrated with .NET development framework.

PostgreSQL is an open-source object-relational DBMS that supports both relational and non-relational methods of data management. Unlike SQL Server, PostgreSQL is fully complied with SQL92 standard.

Licensing

SQL Server is delivered as a free Express and Developer editions and commercial core-based license as a Standard and Enterprise editions. Developer edition can be used for non-production workloads only and Express edition comes with limited features.

PostgreSQL is open source, released under the PostgreSQL license (similar to the BSD or MIT). This license requires no fee for any purpose of using the DBMS , including commercial use.

Platform support

SQL Server supports only Microsoft Windows and Linux operating systems.

PostgreSQL supports all popular operating systems including Linux, macOS, Windows, BSD and Solaris. Also, it can be deployed in Docker containers or Kubernetes.

Data Types

SQL Server supports data types such as string, numeric, date/time and others. It also has limited support for user-defined data types derived from base types:

CREATE TYPE zipcode FROM varchar(5) NOT NULL

On the other hand, PostgreSQL supports similar set of generic data types extended by arrays, json, range types and provides comrehensive support for user-defined types:

CREATE TYPE product AS (
    title           text,
    supplier_id     integer,
    price           numeric
);

Dialects of SQL

SQL Server uses Transact-SQL, or T-SQL, that is Microsoft specific variation of standard SQL extended with a few proprietary extensions.

PostgreSQL uses standard SQL along with its own procedural language PL/pgSQL that allows to create stored procedures, functions and triggers.

More details about differences between SQL dialects of SQL Server and PostgreSQL can be found in these articles:

Convert SQL Functions from SQL Server to PostgreSQL
Convert SQL Server queries into PostgreSQL format

Advantages of SQL Server:

• High performance of the database engine
• Capabilities of in-memory database
• Easy to install and configure through intuitive user interface
• Automatic updates
• Convenient implementation of backup and data recovery
• SQL Server Management Studio simplifies a lot of database administration routines
• Integration with wise range of data analytics and development tools provided by Microsoft

Disadvantages of SQL Server:

• Expensive cost of licenses and support
• Risk of vendor lock-in (customer depends on single vendor's products, making it too hard to switch to alternatives, that often leads to higher costs and limited flexibility)
• Multi-version concurrency (MVCC) is not supported
• Hardware restrictions of new versions may force an upgrade

Advantages of PostgreSQL:

• Highly extensible, advanced database management system
• Sophisticated security (data encryption, SSL certificates, etc)
• High rates of transactions without deadlock
• Support for multi-version concurrency (MVCC)
• Huge open source community is constantly improving the system

Disadvantages of PostgreSQL:

• Installation and administration require deep skills on the product
• Slower performance compared to SQL Server

Reasons to Migrate

Previous section proves that SQL Server and PostgreSQL have their own cons and pros. Consequently, it is important to chose the most suitable DBMS for particular database and to arrange migration if the current system is not enough for changing requirements.

Reasons to migrate from SQL Server to PostgreSQL:

• the organization switches to another OS that is not supported by SQL Server (such as Mac, FreeBSD or Solaris)
• the corporate database requires support for a wider range of data types or exceptional scalability
• growing restrictive licensing and maintenance costs of ownership exceeds acceptable margin as the database size or the number of connections increases

Reasons to migrate from PostgreSQL to SQL Server:

• the organization needs to reduce complexity of database development and administration
• the database has to be integrated with other Microsoft products