介绍

PostgreSQL 和 SQL Server 都是广泛被使用的关系数据库。尽管它们具有一些共同的核心特征，但它们之间也存在显著的差异。本文详细介绍了 PostgreSQL 和 SQL Server 之间的相似之处和不同之处。

最重要的一个区别是 PostgreSQL 是开源的，而 SQL Server 由 Microsoft 拥有和许可的。此外，您还将了解两个系统在许可和成本、易用性、SQL 语法和合规性、数据类型、可用功能、性能和安全性方面的差异，以及相互比较中会涵盖的其他几十个主题。

对于考虑从商业数据库切换到开源数据库的组织，这会很有用处，但也需要有关这两个系统可能的权衡和优势的更多信息。但是，它适用于任何想了解更多关于关系数据库的人。

服务器许可模式

PostgreSQL

PostgreSQL 是根据 PostgreSQL 许可证（开源促进会批准的许可证）发布的开源数据库。将 PostgreSQL 用于任何目的（包括商业目的）都是免费的。在 PostgreSQL 全球开发组下面，PostgreSQL 会永远以免费和开源软件的形式提供给用户。

Microsoft SQL Server 通过商业许可证提供，并且可以按 CPU 核数或者服务器和客户端访问级别（CAL）的模式进行许可。SQL Server 提供两个主要版本，企业版和标准版，以满足组织和个体的性能和价格要求。许可成本从标准版的 3,586 美元到企业版（双核）的 13,748 美元不等；服务器和 CAL 模式需为服务器运行支付 899 美元，外加每位用户 209 美元。学生和开发人员可以使用免费版本进行构建和测试。

数据类型

PostgreSQL 和 SQL Server 的数据类型对比表

数据类型	PostgreSQL	SQL Server
64 位整数	BIGINT	BIGINT
固定长度的字节串	BYTEA	BINARY(n)
1、0 或 NULL	BOOLEAN	BIT
固定长度的字符串，1 <= n <=8000	CHAR(n)	CHAR(n)
可变长度的字符串，1 <= n <=8000	VARCHAR(n)	VARCHAR(n)
可变长度的字符串，<= 2GB	TEXT	VARCHAR(max)
可变长度的字节串，1 <= n <=8000	BYTEA	VARBINARY(n)
可变长度的字节串，<= 2GB	BYTEA	VARBINARY(max)
可变长度的 Unicode UCS-2 字符串	VARCHAR(n)	NVARCHAR(n)
可变长度的 Unicode UCS-2 数据，<= 2GB	TEXT	NVARCHAR(max)
可变长度的字符数据，<= 2GB	TEXT	TEXT
可变长度的 Unicode UCS-2 数据，<= 2GB	TEXT	NTEXT
双精度浮点数	DOUBLE PRECISION	DOUBLE PRECISION
浮点数	DOUBLE PRECISION	FLOAT(p)
32 位整数	INTEGER	INTEGER
定点数	NUMERIC(p,s)	NUMERIC(p,s)
日期，包括年、月和日	DATE	DATE
日期和时间，带秒的小数部分	TIMESTAMP(p)	DATETIME, DATETIME2(p)
带时区的日期和时间	TIMESTAMP(p) WITH TIME ZONE	DATETIMEOFFSET(p)
日期和时间	TIMESTAMP(0)	SMALLDATETIME
无符号整数，0 到 255（8 位）	SMALLINT	TINYINT
UUID（16 字节）	CHAR(16)	UNIQUEIDENTIFIER
自动更新的二进制数据	BYTEA	ROWVERSION
货币金额（32 位）	MONEY	SMALLMONEY
可变长度的二进制数据，<= 2GB	BYTEA	IMAGE
几何类型	POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE	GEOMETRY

地理数据

PostgreSQL

PostgreSQL 没有用于地理数据的内置数据类型。开源的PostGIS资源提供了对地理对象的支持。

SQL Server 具有用于存储地理空间数据的 geography 数据类型。

大小写敏感性

PostgreSQL

PostgreSQL 在处理字符串时区分大小写。LOWER() 函数允许用户将字符串转换为全部小写以进行计算（也有类似的 UPPER() 函数）。默认情况下，PostgreSQL 会将表和列的名称转换为小写，除非您将这些名称放在引号中。context 模块提供了一个不区分大小写的字符串数据类型 context，用于比较值。

默认情况下，SQL Server 不区分大小写。调整 SQL Server 的排序规则设置，可以更改大小写的区分。可在数据库或列级别设置区分大小写的排序规则设置。

索引类型

PostgreSQL

PostgreSQL 为索引类型提供了多个选择，包括 B 树、哈希、通用搜索树（GiST）、空间分区的 GiST、通用倒排索引（GIN）和块范围索引（BRIN）。此外，它还支持表达式索引（使用表达式或函数而不是列值创建的索引）和部分索引（表的一部分的索引）。

SQL Server 提供了聚集索引和非聚集索引。聚集索引根据基本值（索引定义中的列）对表或视图中的数据行进行排序和存储。一个表只能有一个聚集索引。非聚集索引与表数据分开存储，并且每个键值条目都有一个指向数据的指针。当您在表列上定义 PRIMARY KEY 和 UNIQUE 约束时，SQL Server 会自动创建这些索引。UNIQUE 约束创建非聚集索引，而 PRIMARY KEY 会创建聚集索引，除非已存在聚集索引。

复制

PostgreSQL

PostgreSQL 支持主从复制。复制可以是同步的，也可以是异步的。异步复制使用预写式日志（WAL）向副本节点同步更改。流式复制在 WAL 产生时会流式地传输 WAL，而不是等待 WAL 文件填充完，从而可以更迅速地更新备用服务器。

逻辑复制遵循发布和订阅模型，其中的更改基于数据的复制标识（主键）而不是其物理位置，因此称为“逻辑复制”。物理复制处理文件和目录，而不考虑这些物理位置中的内容。PostgreSQL 本身不提供多主复制，但有一些第三方工具提供了多主复制解决方案。

SQL Server 复制将数据从发布服务器复制到订阅服务器，并提供三种类型的复制：

SQL Server 中的复制可以是同步提交或异步提交。企业版提供了对等复制，以作为多主复制的替代解决方案。

集群

PostgreSQL

PostgreSQL 支持服务器集群，但本身不支持多主集群或双主集群。repmgr等工具可用来轻松维护 PostgreSQL 集群。

SQL Server 提供了 Windows Server 故障转移集群，可针对主从和双主节点进行配置。标准版仅支持两个节点的集群；更多的节点需要升级到企业版。

高可用

PostgreSQL

PostgreSQL 提供了多种解决方案来满足用户的高可用需求，包括共享磁盘故障转移、预写式日志传输、数据分区和多种复制方法。Patroni等工具提供了自动故障转移，通过监控和识别数据库故障来达成高可用。

SQL Server 在其各个版本中包括了多个高可用工具。其中包括复制、日志传送和故障转移集群。其 Always On 可用性组（随企业版提供）可在满足特定条件时提供自动故障转移。

视图

PostgreSQL

PostgreSQL 支持视图 – 本身不存储数据的虚拟表。支持可更新的视图，但是需要满足以下条件，否则不会自动更新：

1.该视图的查询必须在 FROM 子句中刚好只有一个部分，该部分可以是表或其他可更新视图。

2.选择列表不得包含任何窗口函数、聚合函数或集合返回函数。

3.查询的顶层不得包含以下子句：HAVING、LIMIT、DISTINCT、WITH、INTERSECT、EXCEPT、OFFSET 和 LIMIT。

使用简单查询创建的视图可以更新；使用复杂查询创建的视图不能更新，但可以使用规则更新复杂视图。还支持物化视图，并且可以使用 REFRESH MATERIALIZED VIEW 语句更新物化视图中的数据。

SQL Server 视图可用于安全目的，限制用户对数据的访问。用户定义视图和系统定义视图均受支持。可以使用触发器自动更新视图。当对单个基础基表中的列进行修改并直接引用时，可以更新视图中的数据。物化视图在 SQL Server 中称为索引视图；与其他关系数据库中的物化视图不同，索引视图与底层数据同步，因此会自动更新。

触发器

PostgreSQL

PostgreSQL 支持高级触发器。支持的触发事件包括 AFTER、BEFORE 和 INSTEAD OF，它们可应用于 INSERT、UPDATE 和 DELETE 事件。当触发器触发时，它可以使用函数执行复杂的 SQL 语句。PostgreSQL 可以动态执行此操作。

SQL Server 为不同类型的数据库事件提供触发器：

登录触发器允许您响应用户的会话建立事件。这些触发器在身份验证成功后和建立用户会话之前触发。它们有助于审计和控制登录活动。

存储过程

PostgreSQL

PostgreSQL 支持以带有 RETURN VOID 子句的用户定义函数，来定义存储过程。PostgreSQL 支持以各种语言以及标准 SQL 语法，编写存储过程。

SQL Server 支持 Microsoft .NET Framework 支持的语言（普通运行时语言或 CLR，如 VB、C# 或 Python），编写存储过程。

全文检索

PostgreSQL

PostgreSQL 提供了用于全文搜索的高级功能。它使用全文索引和词典来加快搜索速度。PostgreSQL 将预处理的文本文档存储为向量数据类型，将处理后的查询存储为其专用类型。预处理将文本文档解析为称为词位的语言单位，这允许您以不区分大小写的形式查找单词。

SQL Server 以可选组件提供了全文搜索。全文索引支持基于特定语言规则的搜索。使用 T-SQL 命令 CONTAINS 匹配单词和短语，使用 FREETEXT 匹配含义，对列或文本数据类型（包括 char、varchar、nchar、nvarchar、text、ntext、image、xml 或 varbinary(max) 和 FILESTREAM）执行搜索。同义词库文件可用于帮助查找搜索词的同义词。SQL Server 中的全文搜索不区分大小写。

正则表达式

PostgreSQL

PostgreSQL 有三种方法来计算正则表达式：LIKE、SIMILAR TO 和 POSIX 正则表达式。

SQL Server 本身不支持正则表达式计算；使用 T-SQL 函数 LIKE、SUBSTRING 和 PATINDEX 可以获得类似但有限的结果。

分区

PostgreSQL

SQL Server 支持表分区和索引分区。数据水平分区，并将行组映射到各个分区。单个索引或表的所有分区必须驻留在同一个数据库中，并且该表或索引在查询和更新时被视为单个实体。

标识列

PostgreSQL

PostgreSQL 在版本 10 中引入了一个名为 GENERATED AS IDENTITY 的新约束功能。这种 SERIAL 列的 SQL 标准中的变体，允许您自动为标识列分配唯一值。

要使 SERIAL 列具有唯一约束或成为主键，现在必须要明确指定，就像其他数据类型一样。唯一标识符列是使用数据类型 smallserial、serial 和 bigserial 创建的，类似于其他数据库中的自增列特性。

SQL Server 的标识列属性为表创建一个标识列，以便为行生成关键值。创建时指定两个值： seed（第一行的初始值）和 increment（在上一行上增加的量）。默认情况下，种子值和增量值均为 1。每个表只能包含一个标识列。除非添加了 PRIMARY KEY 或 UNIQUE 约束，否则无法保证值的唯一性。

计算列

PostgreSQL

PostgreSQL 使用术语生成列来表示计算列。此功能是在版本 12 中引入的。生成列在标记为 STORED 时可以物理存储；否则，它们不会被存储，称为虚拟型。

   computed_column GENERATED ALWAYS  expression STORED

生成列不能带有标识定义或作为分区键的一部分；它们只能引用当前行，不能使用子查询。不能在 INSERT 或 UPDATE 语句指定生成列的值，但可以接受关键字 DEFAULT。

除非使用 PERSISTED 属性进行标记，否则 SQL Server 计算列不会以物理方式存储在表中；仅当值是确定性的或始终返回相同的结果时，才能存储该列。

   computed_column  expression PERSISTED

如果计算列是确定性的，并且是可接受的数据类型，则可以将其用作 PRIMARY KEY 或索引，但不能添加 DEFAULT 或 FOREIGN KEY 约束。不能使用 INSERT 或 UPDATE 指定值。

整型

PostgreSQL

PostgreSQL 中有三种整数：SMALLINT（小整数，2 字节类型，范围从 -32,768 到 32,767），INT（整数，4 字节类型，范围从 -2,147,483,648 到 2,147,483,647），BIGINT（大整数：-9223372036854775808 到 9223372036854775807）。

SQL SERVER 支持标准 SQL 整数类型 BIGINT、INT、SMALLINT 和 TINYINT。每种类型的范围和存储大小如下：

PostgreSQL 和 SQL Server 的整型对比表

数据类型	范围	存储
BIGINT	-263(-9,223,372,036,854,775,808) 至 263-1(9,223,372,036,854,775,807)	8 字节
INT	-231(-2,147,483,648) 至 231-1(2,147,483,647)	4 字节
SMALLINT	-215(-32,768) 至 215-1(32,767)	2 字节
TINYINT	0 至 255	1 字节

布尔类型

PostgreSQL

PostgreSQL 布尔数据类型可以有三种状态：

SQL SERVER 中的 BIT 数据类型表示 true/false 布尔值数据。BIT 字段的值为 1、0 或 null。

NoSQL 能力

PostgreSQL

与许多其他关系数据库一样，PostgreSQL 增加了对 JSON 数据的支持，JSON 数据是存储在 NoSQL 系统中的半结构化数据的最常用格式。但是，由于 SQL 是与 PostgreSQL 数据库交互的唯一方式，因此不应将其视为 NoSQL。

SQL Server 具有内置的 JSON 函数，使您能够使用标准 SQL 语言解析 JSON 文档。您可以将 JSON 文档存储在 SQL Server 中，并像在 NoSQL 数据库中一样查询 JSON 数据。不过，由于 SQL Server 是 SQL 数据库，因此不应将其视为 NoSQL。

分析函数

PostgreSQL

PostgreSQL 支持各种分析函数，这些函数对一组行执行聚合。有两种类型的分析函数：窗口函数和聚合函数。聚合函数执行聚合并返回一组行的单个聚合值（如 sum、avg、min 或 max）。窗口函数为每行返回一个聚合值。

PostgreSQL 支持以下窗口函数：

函数	描述
CUME_DIST	返回当前行的相对排名
DENSE_RANK	在其分区内对当前行进行无间隙排名
FIRST_VALUE	返回根据其分区中的第一行计算的值
LAG	返回在分区中的当前行之前指定物理偏移行的值
LAST_VALUE	返回根据其分区中的最后一行计算的值
LEAD	从分区中当前行之后偏移行的行中返回一个值
NTILE	尽可能均匀地划分分区中的行，然后为每行分配一个从 1 到参数值的整数
NTH_VALUE	返回根据有序分区中的第 n 行计算的值
PERCENT_RANK	返回当前行的相对排名
RANK	在分区中对当前行进行有间隙的排名
ROW_NUMBER	对分区中的当前行进行编号，从 1 开始。

SQL Server 的分析函数使您能够直接在组内计算移动平均值、运行总计、百分比或前 N 个结果。

SQL Server 支持以下分析函数：

函数	描述
CUME_DIST (Transact-SQL)	计算组内值的累积分布
FIRST_VALUE (Transact-SQL)	返回一组有序值中的第一个值
LAG (Transact-SQL)	返回前一行的值以比较值，而无需自连接
LAST_VALUE (Transact-SQL)	返回一组有序值中的最后一个值
LEAD (Transact-SQL)	返回后续行的值以比较值，而无需自连接
PERCENTILE_CONT (Transact-SQL)	根据列值的连续分布计算百分比
PERCENTILE_DISC (Transact-SQL)	根据列值的离散分布计算百分比
PERCENT_RANK (Transact-SQL)	计算组内一行的相对排名

管理和 GUI 工具

PostgreSQL

PostgreSQL 可以使用 Oracle SQL Developer、pgAdmin、OmniDB、DBeaver 和 Navicat，通过图形用户界面进行管理。其他用于监控运行状况和性能的 GUI 工具包括 Nagios、Zabbix、Cacti 和 Prometheus。SQLECTRON 是一个免费和开源的跨平台工具；它与多个 SQL 数据库兼容，包括 SQL Server。

SQL Server 可以使用 SQL Server Management Studio（SSMS），这是 Windows 上免费的图形用户界面管理工具。SQL Operations Studio 是一款适用于 Mac 的免费开源跨平台 GUI 工具。SQLECTRON 是一个免费的开源跨平台工具，与包括 PostgreSQL 在内的多个 SQL 数据库兼容。

性能

PostgreSQL

PostgreSQL 在各种规模的数据集上，都有优异的速度和性能，并且在在线事务处理（OLTP）和在线分析处理（OLAP）速度方面通常优于其他数据库。它提供了多版本并发控制 （MVCC），可同时处理多个事务，死锁比 SQL Server 少。PostgreSQL 提供了许多工具和参数，允许用户监控和优化数据库性能。

SQL Server 以其分析和事务处理的速度而出名。但是，由于 SQL Server 用户协议，禁止在未经 Microsoft 事先书面批准的情况下发布基准测试，因此很少与其他数据库系统进行直接比较。SQL Server 在优化性能和速度方面有一个突出的功能：In-Memory OLTP，它利用了内存数据表的优势，访问性能优于直接写入磁盘。SQL Server 标准版在内存、分区、索引方面存在一些性能限制，还有一些功能需要升级到企业版。

并发

PostgreSQL

PostgreSQL 具有完善的多版本并发控制（MVCC），可同时处理多个过程。MVCC 提供数据库信息的快照，以避免显示由并发事务或其他数据库系统中的数据锁定引起的不一致。它支持可序列化快照隔离（SSI）来保证事务隔离。

SQL Server 的多版本并发控制系统开发得不太成熟，默认情况下，它依赖于数据锁定来防止并发事务出错。为了提高性能，它实现了乐观并发。此方法会跳过行锁定，而是在假定冲突很少出现的情况下，根据缓存版本验证任何更改。

应用领域

PostgreSQL

PostgreSQL 是世界上最先进的开源数据库。全球企业都在使用 PostgreSQL 处理关键业务负载。PostgreSQL 社区和一些公司一直在确保 PostgreSQL 继续在全球范围内拓展应用。

SQL Server 在依赖 Microsoft 产品的企业中很受欢迎。在过去的二十年里，随着 Microsoft 通过其 Windows 服务器推动它，它的市场份额有所增加。然而，随着近年来越来越多的企业转向开源，SQL Server 的流行度曲线变得越来越平坦。

环境和技术栈

PostgreSQL

PostgreSQL 在 LAPP 技术栈（Linux、Apache、PostgreSQL 和 PHP/Python）中很受欢迎。LAPP 技术栈越来越受欢迎；Amazon 和 VMware 等大型平台服务提供商都使用现成的 LAPP 技术栈模块提供服务。

SQL Server 是 Microsoft 技术栈中的常用组件。它包括 Microsoft WPF、ASP.NET、SharePoint 和 Office 365 等 Microsoft 技术。

调度任务

PostgreSQL

PostgreSQL 不像其他 SQL 数据库那样提供内置的任务调度程序。定期任务需要外部工具，如 Linux 上的 pgAgent、cron 或 pg_cron，以及 Windows 上的 Task Scheduler 或 SQLBackupAndFTP。

SQL Server Management Studio 可在 SQL Server 中调度任务。

访问方法

PostgreSQL

PostgreSQL 可支持如下访问方法、协议和 API 以访问数据：ADO.NET、JDBC、ODBC 和原生 C 库等。它还支持用于二进制大对象（BLOB）的流式处理 API。

SQL Server 支持如下访问方法、协议和 API 以访问数据：ADO.NET、JDBC、ODBC、OLE DB 和 TDS 等。

批量收集和绑定

特性	PostgreSQL	SQL Server
批量收集	PostgreSQL 没有用于批量收集的语法，也没有任何接近的等效功能。相反，如果在单个 SQL 语句中工作，则可以使用 PL/PgSQL 代码创建临时表，或使用公共表表达式（CTE 或 WITH 查询）。	SQL Server 中没有用于批量收集的语法。一种替代方法是使用临时表和游标。
绑定	与 Oracle 等其他关系数据库不同，PostgreSQL 不支持绑定变量。相反，PostgreSQL 可使用 PREPARE 语句来实现类似的结果。	SQL Server 支持绑定变量。SQL 语句中的每个参数标记都必须使用SQLBindParameter函数绑定到一个变量，然后才能执行该语句。还可以将一个 SQL 语句的参数绑定到程序变量数组，以进行批量处理。SQL Server 还支持定义存储过程参数的名称。

同义词

SQL Server 支持同义词。同义词提供了一个抽象层，用于防止客户端应用程序对基础对象做出更改。同义词属于一个模式；与模式中的其他对象一样，其名称必须是唯一的。仅支持按名称绑定；如果修改、删除或替换基础对象，则只能在运行时发现缺失的引用。PostgreSQL 不支持同义词。