PostgreSQL vs MSSQL Server: A Comparison

Web Development, Internet
PostgreSQL vs MSSQL Server: A Comparison
Page content

Know the Players

Before we rush in to make a comparison of PostgreSQL and MSSQL Server, it is good to know just exactly what they are. Both of these products are database management systems. MSSQL Server is a propriety database server developed and maintained by Microsoft Corporation, while on the other hand, PostgreSQL is developed and maintained by a global community of open source developers and companies. They both have the same general area of interest and target market.

Hardware and Licensing

MSSQL Server is provided using the Microsoft user license agreement while PostgreSQL is available using the PostgreSQL license. PostgreSQL is a cross platform database server capable of running on Windows, Mac, Solaris, FreeBSD and Linux while MSSQL Server only runs on Windows OS. PostgreSQL is available for free while MSSQL Server comes at a cost except for the Express edition. The license model for MSSQL Server depends on the users and table size.

SQL Syntax

Each database has its own extensions to the SQL language. Most notable is the SELECT TOP modifier in MSSQL Server while PostgreSQL uses the LIMIT clause to restrict the number of results. The LIKE SQL operator in PostgreSQL is case sensitive and therefore provides ILIKE to make searches case insensitive. All MSSQL Server searches are case insensitive. The + (plus) operator can be used in MSSQL Server to concatenate strings, but not in PostgreSQL. Query plans read from right to left in MSSQL Server, and in PostgreSQL they read from left to right. NATURAL and USING joins are not supported by MSSQL the way they are supported in PostgreSQL.

To insert data into a table, the MSSQL Server syntax is as follows:

INSERT table VALUES (…)

PostgreSQL uses the following format

INSERT INTO table VALUES (…)

Internals

MSSQL Server has a limited file data file size of 16 TB and can hold 32,767 files while the maximum table size is 524,258 TB and also supports materialized views. PostgreSQL has unlimited file capacity with maximum table size being 32 TB and does not support materialized views.

Timezones are supported on PostgrSQL while MSSQL Server does not have complete support for it. The GUI tools for MSSQL Server are easier to use, more user friendly and more feature rich than those of PostgreSQL. With MSSQL Server, you can completely alter views without having to drop them while this is not the case with PostgrSQL.

Auto number indexed columns are handled by sequencers in PostgreSQL while in MSSQL Server, it is handled by the IDENTITY property of the int field. MSSQL listens on port 1433 while PostgreSQL listens on port 5432. Table inheritance is supported by PostgreSQL while MSSQL Server does not support it.

MSSQL Server is quite greedy on memory even when idle while PostgreSQL is relatively lightweight.

Miscellaneous

In addition to the internal differences above, there is a myriad of differences that affect performance and speed, which are generally attibuted to the differences in drivers and the database architecture. Both database servers are appealing and they have their place. If cost is an issue, then naturally you would want to go with PostgreSQL. If you are looking to power your .NET applications, then you would go with MSSQL Server. All in all, with the right hardware, both MSSQL Server and PostgreSQL get the thumbs up from me.

References