从数据分析的角度比较两个关系数据库

0.  这是怎么回事？

我在一家全球专业服务公司（你肯定听说过）做数据分析师。我这样做已经有十年了。我花了十年时间处理数据、数据库软件、数据库硬件、数据库用户、数据库程序员和数据分析方法，所以我对这些事情有了相当的了解。我经常与那些对这些事情知之甚少的人接触，尽管他们中的一些人并不知道这一点。

多年来，我已经多次讨论过PostgreSQL与MS SQL Server的问题。其中一个众所周知的原则是：如果你要做不止一次，就自动化它。这个文档是我自动进行对话的方式。

除非另有说明，否则我指的是PostgreSQL 9.3和MS SQL Server 2014，尽管我在MS SQL Server方面的经验是2008 R2和2012版-为了公平性和相关性，我想将PostgreSQL的最新版本与MS SQL Server的最新版本进行比较。在我对MS SQL Server发表声明的地方，我已经通过查阅微软自己的文档尽了最大努力来检查它们是否适用于2014版——不过，出于我将要了解的原因，我还不得不在很大程度上依赖Google、Stack Overflow和互联网用户。我知道，当我对两个数据库都没有同等经验时，进行这样的比较在科学上并不严格，但这不是一项学术活动，而是一项现实世界的比较。我已经尽了我最大的努力让我的事实对MS SQL Server正确-我们都知道，这是不可能胡扯整个互联网。如果我发现我出了问题，我会解决的。

我是从数据分析师的角度比较这两个数据库。也许mssqlserver将PostgreSQL的arse作为OLTP后端（尽管我对此表示怀疑），但这不是我在这里写的，因为我不是OLTP开发人员/DBA/sysadmin。

最后，在右上角有一个电子邮件地址。如果你愿意的话，请使用它；我会尽我所能去回应。

免责声明：这里所有的主观意见都是我自己的。

1.  为什么PostgreSQL比MS SQL Server好得多

哦，扰流警报。本节是两个数据库在数据分析相关功能方面的比较。

1.1  CSV支持

CSV实际上是移动结构化（即表格）数据的标准方式。所有rdbmse都可以将数据转储为其他任何文件都无法读取的专有格式，这对于备份、复制等都很好，但对于将数据从system X迁移到system Y却毫无用处。

数据分析平台必须能够查看来自各种系统的数据，并生成可由各种系统读取的输出。实际上，这意味着它需要能够快速、可靠、可重复且无痛苦地摄取和排出CSV。让我们不要轻描淡写：一个数据分析平台，不能处理CSV的强大是一个破碎，无用的责任。

PostgreSQL的CSV支持是一流的。COPY TO和COPY FROM命令支持RFC4180中概述的规范（这是最接近官方CSV标准的东西）以及许多常见和不常见的变体和方言。这些命令快速而强大。当错误发生时，它们会给出有用的错误消息。重要的是，他们不会默默地破坏、误解或改变数据。如果PostgreSQL说你的导入成功了，那么它就正常工作了。稍有一点问题，它就会放弃导入并抛出一条有用的错误消息。

（这听起来可能很麻烦，但实际上是一个很好的设计原则的例子。这是有道理的：你宁愿现在就发现你的导入出错，还是一个月后你的客户抱怨你的结果不合格？）

MS SQL Server既不能导入也不能导出CSV。当我告诉他们这些的时候，大多数人都不相信我。然后，在某个时刻，他们自己看。通常他们会观察到如下现象：

• MS SQL Server无提示截断文本字段
• MS SQL Server的文本编码处理出错
• MS SQL Server抛出错误消息是因为它不理解引用或转义（与流行的观点相反，引用和转义不是CSV的外来扩展）。它们是每个人类可读数据序列化规范中的基本概念。不要相信那些不知道这些东西是什么的人）
• MS SQL Server导出损坏的无用CSV
• 微软可怕的文档。他们是如何设法将CSV这样简单的东西过度复杂化的？

这尤其令人费解，因为CSV解析器非常容易编写（我在一两年前用C编写了一个并将其插入PHP，因为我对它的本地CSV处理函数不满意。整个过程大概花了100行代码和3个小时——其中两个小时花在了SWIG上，这在当时对我来说是新的）。

如果您不相信我的话，请下载这个格式正确、符合标准的UTF-8csv文件，并使用MS SQL Server计算该文件最后一列的平均字符串长度（即字符数）（它有50列）。去吧，试试看。

（你要找的答案正好是183.895。）

当然，在PostgreSQL中确定这一点非常简单——事实上，最耗时的部分是创建一个包含50列的表来保存数据。对CSV的理解不足似乎是微软的通病；这个文件也会破坏Access和Excel。

可悲但却是事实：我认识的一些数据库程序员最近花了很多时间和精力编写Python代码来“清理”CSV，以允许MS SQL Server导入它。不过，他们无法避免在这个过程中更改实际数据。这就像在Photoshop上花了一大笔钱，然后不得不编写一些自定义代码来打开JPEG一样疯狂，结果发现图像被稍微修改了一下。

1.2 人体工程学

每一个值得一提的数据分析平台都是图灵完备的，这意味着，无论付出还是索取，它们中的任何一个都可以做任何其他人可以做的事情。没有“在软件A中可以做X，但在软件B中不能做X”这样的说法。你可以在任何事情上做任何事-所有不同的是它有多难。好的工具使你需要做的事情变得容易；坏的工具使它们变得困难。这就是它的由来。

（这都是概念上的正确，如果不是字面上的正确——例如，我所知道的任何关系数据库都不能呈现三维图形。但它们中的任何一个都可以模拟GPU可以执行的任何计算。）

PostgreSQL显然是由那些真正关心完成任务的人编写的。MS SQL Server让人觉得它是由那些从来不需要真正使用mssqlserver来实现任何事情的人编写的。下面是几个例子来支持这一点：

PostgreSQL支持DROP TABLE IF EXISTS，这是“如果这个表不存在，就什么也不做，如果存在，就把它去掉”的一种聪明而明显的说法。像这样的：

如果存在我的表，则删除表；

以下是在MS SQL Server中执行此操作的方法：

如果对象ID（N'dbo.my_表“，N'U”）不为空

升降台dbo.my_表;

是的，这只是额外的一行代码，但请注意OBJECT_ID函数的第二个神秘参数。你需要用N'V'替换它来删除视图。对存储过程来说是不可能的。我还没有学会所有不同类型数据库对象的所有不同字母（为什么我必须这样做？）还要注意，表名是不必要地重复的。如果你的注意力暂时不集中，很容易做到这一点：

如果对象ID（N'dbo.some_表“，N'U”）不为空

升降台dbo.some_other_表;

看到那里发生了什么吗？这是烦人、浪费时间的错误的可靠来源。

PostgreSQL支持DROP SCHEMA CASCADE，它将删除一个模式及其内部的所有数据库对象。对于一个健壮的分析交付方法来说，这是非常非常重要的，其中分解和重建是可重复、可审计、协作分析工作的基本原则。

在MS SQL Server中没有这样的工具。必须以正确的顺序手动删除架构中的所有对象，因为如果尝试删除另一个对象所依赖的对象，MS SQL Server只会抛出一个错误。这说明了这个过程有多麻烦。

PostgreSQL支持将表创建为。一个小例子：

制作桌上好电影

CREATE TABLE good_films AS
SELECT
  *
FROM
  all_films
WHERE
  imdb_rating >= 8;

这意味着您可以突出显示除第一行之外的所有内容并执行它，这在开发SQL代码时是一项有用且常见的任务。

在MS SQL Server中，表的创建过程如下：

SELECT
  *
INTO
  good_films
FROM
  all_films
WHERE
  imdb_rating >= 8;

因此，要执行普通的SELECT语句，必须注释掉或删除INTO位。是的，注释两行很容易，这不是重点。重点在于，在PostgreSQL中，您可以在不修改代码的情况下执行这个简单的任务，而在MS SQL Server中，您不能执行这个任务，这就引入了另一个潜在的bug和烦恼源。

在PostgreSQL中，可以在一个批处理中执行任意多个SQL语句；只要用分号结束每个语句，就可以执行任意组合的语句。对于执行自动化批处理过程或可重复的数据生成或输出任务，这是非常重要的功能。

在MS SQL Server中，CREATE PROCEDURE语句不能出现在一批SQL语句的中间。这没什么好理由，只是一个任意的限制。这意味着执行大量SQL常常需要额外的手动步骤。手动步骤会增加风险并降低效率。

PostgreSQL支持RETURNING子句，允许UPDATE、INSERT和DELETE语句从受影响的行返回值。这是优雅和有用的。MS SQL Server有OUTPUT子句，它需要单独的表变量定义才能运行。这是笨重和不方便的，并迫使程序员创建和维护不必要的样板代码。

PostgreSQL支持$$string引用，如下所示：

选择$$Hello，World$$作为问候语；

这对于生成动态SQL非常有用，因为（a）它允许用户在嵌套文本字符串时避免冗长和不可靠的手动引用和转义；（b）由于文本编辑器和ide倾向于不将$$识别为字符串分隔符，因此即使在动态SQL代码中，语法突出显示也仍然有效。

PostgreSQL让您只需向数据库引擎提交代码就可以使用过程性语言；您可以用Python、Perl、R或JavaScript或其他任何受支持的语言（见下文）编写过程性代码，就在您的SQL旁边，在同一个脚本中。它具有方便、快捷、易维护、易审查、易重用等特点。

在MS SQL Server中，可以使用笨重、缓慢、笨拙的T-SQL过程语言，也可以使用.NET语言生成程序集并将其加载到数据库中。这意味着您的代码位于两个不同的位置，您必须通过一系列基于GUI的手动步骤来更改它。它使得把你所有的东西打包到一个地方变得更加困难和容易出错。

还有更多的例子。孤立地说，每一件事似乎都是一个相对较小的麻烦；然而，总的影响是，在MS SQL Server中完成真正的工作比在PostgreSQL中要困难得多，更容易出错，而且数据分析人员将宝贵的时间和精力花在工作区和手动过程上，而不是集中在实际的问题上。

更新：据我所知，MS SQL Server有一个非常有用的特性，PostgreSQL没有这个特性，那就是在SQL脚本中声明变量的能力。这样地：

声明@thing INT=1；

选择@thing+6；--返回7

PostgreSQL不能这样做。我希望它可以，因为这样的功能有很多用途。

1.3 您可以在Linux、BSD等（当然还有Windows）中运行PostgreSQL

任何关注IT发展的人都知道，跨平台现在是一件事。跨平台支持可以说是Java的杀手级特性，Java实际上是一种有点笨重、难看的编程语言，但仍然非常成功、有影响力和广泛应用。由于Linux和苹果的崛起，微软不再拥有它曾经在桌面上享有的垄断地位。由于云服务的灵活性和对高性能虚拟化技术的易访问性，IT基础设施越来越异构。跨平台软件就是让用户控制他们的基础设施。（在工作中，我目前管理着几个PostgreSQL数据库，有些在Windows中，有些在Ubuntu Linux中。我和我的同事可以在他们之间自由移动代码和数据库转储。我们使用Python和PHP，因为它们也可以在两个操作系统中工作。一切都很好。）

微软的政策一直是供应商锁定。他们不开放源代码；他们不提供跨平台版本的软件；他们甚至发明了一个完整的生态系统，.NET，旨在在微软用户和非微软用户之间划清界限。这对他们有好处，因为它保障了他们的收入。这对用户来说是不好的，因为它限制了您的选择，并为您创建了不必要的工作。

（更新：在我发表这篇文章的几天后，微软宣布它是开源的.NET，这让我看起来像个prat。这是一个很好的步骤，但我们现在还不能打开布林格的大门。）

现在，这不是一个Linux对Windows的文档，尽管我相信我会在某个时候写一个这样的文档。可以这么说，对于真正的it工作来说，Linux（以及类似UNIX的系列：Solaris、BSD等）让Windows蒙在鼓里。类UNIX操作系统在服务器市场、云服务、超级计算（在这个领域几乎是垄断）和技术计算领域占据主导地位，而且有充分的理由——这些系统是由技术人员为技术人员设计的。因此，他们用用户友好来换取巨大的力量和灵活性。一个合适的类UNIX操作系统不仅仅是一个好的命令行，它是一个由程序、实用程序、功能和支持组成的生态系统，使真正的工作变得高效和愉快。一个称职的Linux黑客只需一行Bash脚本就可以完成一项在Windows下既费时又费力的任务。

（例句：前几天我在看一个朋友的电影集，他说，考虑到他有多少部电影，他认为文件系统中的文件总数很高，他想知道他是不是不小心把一个大文件夹结构复制到了他的一个电影文件夹中。我为他对每个文件夹的文件进行了递归计数，如下所示：

找到。-键入f | awk'BEGIN{FS=“/”；}{print$2；}'| sort | uniq-c | sort-rn | less

整个过程大约花了一分钟来写，一秒钟来跑。它证实了他的一些文件夹有问题，并告诉他是哪些文件夹。你会在Windows下怎么做呢？）

对于数据分析，RDBMS并不存在于真空中；它是工具堆栈的一部分。因此，它的环境很重要。MS SQL Server仅限于Windows，Windows只是一个糟糕的分析环境。

 

原文：https://www.linkedin.com/pulse/postgresql-vs-ms-sql-server-girish-chander/

本文：

讨论：请加入知识星球【首席架构师圈】或者小号【jiagoushi_pro】