I have used SQL Server extensively over the past 20+ years. More recently, over the past few years, I have also been using Postgres. From my experience, I had an idea of what I liked and didn’t like about each database platform, but wanted to look into it more to determine the strengths and weaknesses of each. I wasn’t as concerned about comparing features that were similar, but wanted to focus on positives or negatives that stood out for future evaluation of what to choose for a given application/client.
Price
Postgres has become a very popular database system in part due to it being open source with no fee licensing whereas SQL Server is available via a per-core or client access level licensing model at various costs. SQL Server does have a product for developers for no charge, but is throttled in memory and storage size.
Concurrency, Locks, and Deadlocks
One of the strengths of Postgres is its ability to handle concurrency making it commonplace for web applications. Locks for reads and writes are separate. Multiple versions of the same record, with only one being flagged as active, allows many concurrent users to read the data while writing is taking place. Postgres has a lower chance of deadlocking than SQL Server because of this.
Postgres’s flavor of MVCC (Multi-Version Concurrency Control), used to handle concurrency, uses an append-only storage methodology where delete operations mark rows for later deletion and updates mark rows for deletion and append the new versions of the records. While this allows for robust concurrency, it can also be an Achilles heel causing degraded performance, table bloating, and other issues. This is especially a problem when processes update (or delete) a lot of records. For example, with extra non-active records in the tables, the table index statistics can become stale causing the use of less optimal indexes.
Garbage Collection and Defragmentation
Both SQL Server and Postgres require garbage collection and defragmentation to free space from deleted records and reorganize data in tables and indexes for more efficient access.
The SQL Server garbage collection is more efficient than that of Postgres, allowing it to be running all the time with less of a performance hit. Postgres garbage collection is more CPU intensive and can lag behind when many rows have been marked for deletion.
Performance
SQL Server is known for high performance and is often used in large enterprise-wide systems for this reason. Postgres is often used with web based applications that have a high number of transactions.
I haven't done side-by-side comparisons for performance, but we at Mynd Core Partners have noticed for certain queries with a high volume of underlying data that Postgres performance can be lacking.
Security
Both Postgres and SQL Server incorporate a high level of security in their databases. Transport Layer Security (TLS) should be enabled for both causing communication between client and server or other database servers to be encrypted. SQL Server uses dynamic data masking to hide sensitive data from unauthorized users and offers row-level security to aid systems in protecting data at the database level. This feature allows application design and development of the code base to be simpler. Postgres also allows row level security and column level encryption although with more effort.
Miscellaneous
Postgres supports more platforms (Linux, Windows, Mac, Unix, etc.) than does SQL Server (Linux, Windows). Both can be deployed on Docker containers or Kubernetes.
Postgres supports object-oriented classes, inheritance, and more data types.
SQL Server allows for large data storage using sharding to split the data between multiple servers. Large data storage can be accomplished in Postgres using remote servers and partitioning. The setup effort to accomplish this is greater than with SQL Server.
SQL Server has easy-to-use backup, recovery, and scheduling features. Installing and configuring SQL Server are supported with more straightforward user interfaces.
Summary
In summary, Postgres is a great option for many scenarios, the price tag is right, and has the appeal of being open source. It would be well worth it to delve deeper into the configuration when performance issues are encountered. For the most part, it handles a high work load with many users well.
On the other hand, Microsoft has put a lot of time into optimizing SQL Server and out-of-the-box functionality in general performs well and it is easier to install and configure. Its cost may be prohibitive to some.
Feel free to contact me with comments or questions: tim@myndcorepartners.com
About the author, Tim Millar:
I am a senior software engineer with over 20 years of experience designing, developing and implementing applications primarily in the financial industry. The tech stack I've used has included C# and SQL Server on Azure and Go and Postgres on AWS.
Comments