Databases

	MSSQL	PosgreSQL	MYSQL

About	Start to the latest one.	Start to the latest one.	Start to the latest one.

Installation

Backup & Recovery
Scheduling Tasks	The SQL Server Management Studio schedules tasks in the SQL Server.	SchPostgreSQL doesn’t provide a built-in job scheduler like other SQL databases do. Recurring tasks require external tools like pgAgent, cron, or pg_cron on Linux and Task Scheduler or  SQLBackupAndFTP on Windows.
Performance
Indexes	SQL Server offers clustered and nonclustered indexes. Clustered indexes sort and store data rows in the table or view based on fundamental values (columns in the index definition). A table can have only one clustered index. Nonclustered indexes are stored separately from table data, and each key value entry has a pointer to the data. MSSQL automatically creates these indexes when you define PRIMARY KEY and UNIQUE constraints on table columns. The UNIQUE constraint creates a nonclustered index, while the PRIMARY KEY creates a clustered index unless one already exists.	PostgreSQL offers several options for index types, including B-tree, hash, Generalized Search Tree (GiST), Space Partitioned GiST, Generalized Inverted Index (GIN), and Block Range Index (BRIN). In addition, it supports expression indexes (indexes created with an expression or function rather than a column value) and partial indexes (indexes of part of a table).
Performance	SQL Server prides itself on the speed of its analytical and transaction processing. However, because the SQL Server user agreement prohibits the publication of benchmark testing without Microsoft’s prior written approval, head-to-head comparisons with other database systems are rare. Among the features SQL Server highlights for optimizing performance and speed is its In-Memory OLTP, which takes advantage of in-memory data tables that perform better than writing directly to disk. The SQL Server Standard edition has some performance limitations for memory, partitioning, indexing and other functionalities that require upgrading to the Enterprise version.
		PostgreSQL offers speed and performance across data sets of all sizes and it regularly outperforms other databases in both online transaction processing (OLTP) and online analytical processing (OLAP) speeds. It offers multi-version concurrency control (MVCC), processing multiple transactions simultaneously, with fewer deadlocks than SQL Server. PostgreSQL offers many tools and parameters that allow users to monitor and optimize database performance.
Concurrency	SQL Server has a less fully developed multi-version concurrency control system and, by default, relies on data locking to prevent errors from simultaneous transactions. To improve performance, it implements optimistic concurrency. This approach skips row locking and instead verifies any changes against a cached version, assuming conflicts are rare.	PostgreSQL has a well-developed multi-version concurrency control (MVCC) that handles multiple procedures simultaneously. MVCC provides snapshots of database info to avoid showing inconsistencies caused by simultaneous transactions or the locking of data that occurs in other database systems. It uses Serializable Snapshot Isolation (SSI) to guarantee transaction isolation.

High Availability
	SQL Server replication duplicates data from a Publisher server to a Subscriber and offers three types of replication: Transactional replication for server-to-server environments, where changes are delivered from the publisher to the subscriber as they occur. Merge replication for server-to-client environments or in situations where conflicts might occur, where data can be changed and tracked on either the publisher or subscriber and later synchronized. Snapshot replication is when data is updated infrequently or does not need to be changed incrementally, where data is duplicated precisely as it appears at a specific moment. Replication in SQL Server can be a synchronous commit or asynchronous commit. The Enterprise edition offers peer-to-peer replication as an alternative solution to multi-master replication.	PostgreSQL has Primary-Secondary replication. Replication can be synchronous or asynchronous. Asynchronous replication uses write-ahead logs (WALs) to share changes with the replica nodes. Streaming replication updates standby servers more immediately by streaming the WALs as they are created rather than waiting for the file to be filled. Logical replication follows a publish and subscribe model, where changes are based on the data’s replication identity (a primary key) rather than its physical location, hence the name “logical replication.” Physical replication deals with files and directories without regard for the contents within those physical locations. PostgreSQL does not natively offer multi-master replication, but some third-party tools offer multi-master replication solutions.
Clustering	SQL Server offers Windows Server Failover Clustering, which can be configured for both active/passive and active/active nodes. The Standard edition only supports two nodes for clusters; additional nodes require an upgrade to the Enterprise edition.	PostgreSQL allows clusters of servers but does not natively support multi-master or active/active clusters. Tools such as repmgr allow for easy maintenance of PostgreSQL clusters.
Disaster Recovery

Patch

Upgrade

Migration to cloud

Tools

Utilities

Users, Role, Login etc.

Important files

Important tables/view etc.
Views	SQL Server views can be used to restrict user access to data for security purposes. Both user-defined and system-defined views are supported. Views can be automatically updated using triggers. The data in a view can be updated when the modifications are made to a column from a single underlying base table and are referenced directly. Materialized views are known in SQL Server as Indexed Views; unlike materialized views in other relational databases, indexed views are synched to the underlying data and are thus updated automatically.	PostgreSQL supports views – virtual tables that do not store data themselves. Updatable views are supported, but updates do not occur automatically unless they meet the following conditions: The query of that view must have precisely one section in the FROM clause, which can be a table or another updatable view. The selection list must not contain any window function, aggregate function or set-returning function. The query must not contain one of the following clauses at the top level: HAVING, LIMIT, DISTINCT, WITH, INTERSECT, EXCEPT, OFFSET AND LIMIT. Views created with simple queries can be updated; ones made with complex queries cannot, but complex views can be updated using rules. Materialized views are also supported and the data in materialized views can be updated using the REFRESH MATERIALIZED VIEW statement.
Triggers	SQL Server offers triggers for different types of database events: DML Triggers: for a data manipulation language (DML) specific event, such as inserting, updating or deleting records. These triggers fire on events irrespective of the number of rows affected. DDL Triggers: for data definition language (DDL) events, such as CREATE, DROP, or ALTER statements. These are useful for preventing or auditing changes to the database schema. Logon Triggers allow you to respond to user session establishment events. These triggers fire after successful authentication and before establishing the user session. They are helpful for auditing and controlling login activity.	PostgreSQL has advanced triggers. Supported triggering events include AFTER, BEFORE, and INSTEAD OF, which apply to INSERT, UPDATE and DELETE events. When a trigger fires, it can execute complex SQL statements using functions. PostgreSQL can execute this dynamically.
Stored Procedures	SQL Server supports stored procedures for languages supported by Microsoft .NET framework (common runtime languages or CLR, like VB, C#, or Python).	PostgreSQL supports stored procedures as user-defined functions with a RETURN VOID clause. SQL Server supports stored procedures written in various languages alongside standard SQL syntax.
Partitioning	SQL Server supports table and index partitioning. The data is partitioned horizontally and maps groups of rows into individual partitions. All partitions of a single index or table must reside in the same database and the table or index is treated as a single entity for queries and updates.	PostgreSQL offers built-in support for range, list and hash partitioning. Range partitioning groups a table into ranges defined by a partition key column or set of columns – for example, by date range. List partitioning breaks a table into groups by explicitly listing predefined fundamental values in each partition. EDB Postgres Advanced Server also supports Interval Partitioning, which automatically creates the interval partitions as data arrives without causing deadlocks.
Table Scalability	SQL Server contains scalability enhancements to the on-disk storage for memory-optimized tables. The current versions offer multiple concurrent threads to persist memory-optimized tables, multithreaded recovery and merge operations and dynamic management views. Scaling in SQL Server can be easily achieved through sharding.	PostgreSQL has a handful of indexing and two types of partitioning options to improve data operations and query performance on a scalable table. Table partitions and Indexes can be placed in separate tablespaces on different disk file systems, significantly improving table scalability. Postgres does not support horizontal table partitioning, but several commercially developed products are available.

NoSQL Capabilities	SQL Server has native JSON functions that enable you to parse JSON documents using standard SQL language. You can store JSON documents in an SQL Server and query JSON data as in a NoSQL database. Still, because SQL Server is an SQL database, it should not be considered NoSQL.	Like many other relational databases, PostgreSQL has added support for JSON data, the most common format for semi-structured data stored in NoSQL systems. However, because SQL is the only way to interact with a PostgreSQL database, it should not be considered NoSQL.

Security
Security	SQL Server offers a range of features and functions to prevent security threats because each application has unique security needs. The SQL Server security framework manages access to securable entities through authentication and authorization. SQL Server supports a hierarchy of encryption options and supports TLS (transport layer security) for encrypting network traffic.	PostgreSQL supports SSL (Secure Sockets Layer) connections to encrypt client-server communications. You can enable SSL by setting the SSL parameter in the postgresql.conf file. To meet enterprise needs, the EDB Postgres Advanced Server includes: Additional built-in auditing features that capture detailed data Integrated password policy management capabilities Data redaction
Data Redaction	SQL Server offers dynamic data masking (DDM). DDM limits sensitive data exposure by hiding it from users without the proper privileges. It complements other SQL Server security features like auditing, encryption and row-level security.	PostgreSQL does not support data redaction to limit the display of sensitive data for specific users. Enterprises looking for data redaction features to add more security can use EDB Postgres Advanced Server, an Oracle-compatible fork of PostgreSQL.
Access Methods	SQL Server is compatible with the following access methods, protocols, and APIs for accessing its data: ADO.NET, JDBC, ODBC, OLE DB and TDS.	PostgreSQL is compatible with the following access methods, protocols and APIs for accessing its data: ADO.NET, JDBC, ODBC and the native C library. It also supports a streaming API for binary large objects (BLOBs).

GUI Tools
	SQL Server can be administered through a GUI on Windows using SQL Server Management Studio (SSMS), which is free. SQL Operations Studio is a free, open source, cross-platform GUI for Mac. SQLECTRON is a free, open source, cross-platform option compatible with several SQL databases, including PostgreSQL.	PostgreSQL can be administered through a GUI using Oracle’s SQL Developer, pgAdmin, OmniDB, DBeaver, and Postgres Enterprise Manager. Other GUI tools for monitoring health and performance include Nagios, Zabbix, Cacti and EDB Postgres. SQLECTRON is a cross-platform option that is free and open source; it is compatible with several SQL databases, including SQL Server.
High Availability


Limitations
Limitations	In addition to T-SQL, SQL Server supports languages compatible with the Microsoft .NET framework, including C#, Java, PHP and Python. SQL Server must run on Linux or Windows operating systems. It can be deployed on Docker containers and Kubernetes with Microsoft’s Azure Kubernetes Services.	In addition to SQL and PL/pgSQL, PostgreSQL supports the procedural languages PL/Tcl, PL/Perl, and PL/Python in its distribution and supports the external procedural languages PL/Java, PL/Lua, PL/R, PL/sh (Unix shell) and PL/JavaScript. It also supports user-defined functions in C languages. It runs on various server operating systems, including Linux, Mac, Windows, BSD and Solaris. It is easily deployed using Docker containers or Kubernetes.