将您的SQL Server工作负载迁移到PostgreSQL –第2部分

目录

SQL Server vs PostgreSQL

并发控制

SQL Server并发控制

PostgreSQL并发控制

数据库结构 

T-SQL与PL/pgSQL  

PostgreSQL具有更完善的SQL语法 

对象名称区分大小写 

数据库对象/特征

数据类型 

内置功能和运算符 

SQL语言差异  

CTE性能差异 

Collation / Ordering 

当删除不删除时 

PostgreSQL有优势的地方 

PostgreSQL实际上可以在任何地方运行 

将测试数据插入PostgreSQL表很容易 

PostgreSQL中的多语言支持 

PostgreSQL具有丰富的函数和运算符集

PostgreSQL对CSV有更好的支持 

您可以使用单个语句在PostgreSQL中删除整个模式 

Unicode 和字符编码基础 

Unicode支持 

SQL Server有优势的地方 

您不能在单个查询中直接查询多个数据库 

您不能直接在PostgreSQL中执行过程代码 

PostgreSQL 不支持版本11之前的存储过程 

PostgreSQL在版本12之前不支持计算列

SQL Server vs PostgreSQL

要进行高层次的比较，请在此处查看此数据库引擎页面。 

并发控制

行数据的并发控制是数据库系统的重要方面，它决定了行数据的维护和更新效率。不同的数据库系统具有不同的并发控制机制，这些机制直接影响其性能。

SQL Server并发控制

传统上，2005之前的SQL Server版本使用行级独占锁，该锁在写操作期间锁定整个行。这样可以防止其他读取和写入线程访问该行，并且它们必须等到该行上的当前写入操作完成（提交或回退）。在大量并发写入和读取期间，这会对数据库的性能产生重大影响。

SQL Server 2005通过引入“行版本隔离级别”改善了这种情况。请参阅锁定和行版本控制。请注意，有两种单独的MVCC实现，使用行版本控制（RCSI）的读取提交隔离和快照隔离（SI）

SQL Server查询可以返回脏（未提交）行，具体取决于事务的隔离级别。

PostgreSQL并发控制

PostgreSQL使用多版本模型（多版本并发控制，MVCC）维护数据一致性，这意味着每个SQL语句都可以像以前一样看到数据快照（一个数据库版本），而不管基础数据的当前状态如何。这样可以防止语句查看由并发事务对同一数据行执行更新而产生的不一致数据，从而为每个数据库会话提供事务隔离。MVCC避免了锁定行，从而最大程度地减少了锁定争用并提高了整体性能。

使用并发控制的MVCC模型而不是锁定的主要优点是，在MVCC中，为查询（读取）数据而获取的锁与为写入数据而获取的锁不冲突，因此读取永远不会阻止写入，而写入永远不会阻止读取。PostgreSQL即使通过使用可序列化快照隔离（SSI）级别提供最严格的事务隔离级别，也保持这种保证。

PostgreSQL绝不会返回脏行，无论事务的隔离级别如何。

数据库结构 

与SQL Server一样，PostgreSQL可以在一个实例中包含多个数据库，并且每个数据库可以包含多个模式。这些模式中的每一个都可以包含其他数据库对象，例如表、视图、存储过程、函数等。 

在PostgreSQL中打开与数据库的连接时，只能在该连接中引用该数据库。如果您对连接的查询引用了在同一实例上运行的其他数据库，则PostgreSQL将引发错误。您可以分别为每个数据库打开一个连接，也可以使用dblink或Foreign Data Wrappers从多个数据库中查询表。 

T-SQL与PL/pgSQL  

SQL Server对标准SQL语言的过程扩展简称为Transact-SQL或T-SQL，并提供其他功能，例如过程编程、循环构造、条件构造、会话管理、存储过程等。 

PostgreSQL有多种过程语言，默认语言称为PL/pgSQL。

PostgreSQL具有更完善的SQL语法 

一旦开始使用PostgreSQL，您会发现与SQL Server相比，它具有更加简洁和完善的语言语法。比较以下语句： 

SQL Server

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[customers]') AND type IN (N'U')) 
BEGIN
    CREATE TABLE [dbo].[customers]( .... ) 
END

PostgreSQL

CREATE TABLE IF NOT EXISTS customers ( .... ) 

PostgreSQL代码更加简单，简洁和完善。SQL Server代码绝对是维护的噩梦。 

几个例子：

SQL Server

-- This throws an error if the table doesn’t exist
DROP TABLE [dbo].[customers]

-- This is how you do it in SQL Server 2014 and older
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[customers]') AND type IN (N'U')) 
BEGIN
    DROP TABLE [dbo].[customers] 
END

-- This works in SQL Server 2016 and newer
DROP TABLE IF EXISTS [dbo].[customers]

PostgreSQL

ROP TABLE IF EXISTS customers;
-- No error if the table doesn’t exist

同样，PostgreSQL支持CREATE OR REPLACE存储过程、函数、视图等的语法。但是在SQL Server 2014及更低版本中，您必须检查对象是否存在，然后将其删除，然后再尝试再次创建它。您可以使用ALTER语句，但是如果该对象尚不存在，它将引发错误。SQL Server 2016 SP1引入了CREATE OR ALTER存储过程、函数、触发器和视图的语法。

SQL Server 2016及更高版本支持DROP IF EXISTS表/视图，但仍不支持CREATE IF NOT EXISTS语法。

对象名称区分大小写 

在SQL Server中；对象名Customers, CUSTOMERS和customers都是相同的。SQL Server不会在对象名称中强加区分大小写，并且会创建与CREATE 语句中指定的大小写相同的对象。 

在PostgreSQL中，对象名称被静默转换为小写。当名称用双引号引起来时，该名称区分大小写，并且在查询中必须与引号一起使用。因此，上述名称将转换为customers，但“Customers”（带引号）将被视为不同的对象，在引用该对象的查询中必须将带引号的对象也视为不同的对象。此规则也适用于表中的列名。 

 

数据库对象/特征

将数据库对象从SQL Server迁移到PostgreSQL非常简单，因为大多数对象在目标平台中均受支持。

对象/功能

SQL Server

PostgreSQL

评论

表

是

是

 

索引

是

是

 

触发器

是

是

 

视图

是

是

 

可更新的视图

是

是

 

物化视图

是

是的，从v9.3开始

 

计算列

是

是的，在v12使用视图的早期版本中可用

计算列在PostgreSQL中称为生成列

存储过程

是

是的，从v11开始

在PostgreSQL 11之前，开发人员使用函数而不是存储过程

用户定义功能

是

是

 

重载功能

没有

是

在PostgreSQL中，您可以具有名称相同但参数不同的多个函数。

公用表表达式（CTE）

是

是

PostgreSQL 11及更早版本：CTE总是物化，并且可能性能不佳。PostgreSQL 12：查询提示允许CTE物化或不物化。

函数索引

没有

是

 

最大表大小

无限

在PostgreSQL 9.6或更早版本中为32 TB（32TB）在PostgreSQL 10中为2 Exabytes（2EB）

 

每张表的最大列数

1024（non-wide）30000（wide）

250 – 1600，取决于列类型

 

最大数据库大小

524,272 TB

无限

 

最大Varchar长度

Varchar（Max）

Varchar（10485760）将文本用于较长的字符串

 

调度器

SQL Server代理

pgAgent

 

SQL Server最大容量规格（v2012及更低版本） 

SQL Server最大容量规格（v2014） 

数据类型 

无论使用工具还是使用手动方法，在进行迁移时，请在SQL Server和PostgreSQL数据类型之间保留以下数据类型映射。一些工具使您可以研究数据类型映射，以微调迁移过程。 

Microsoft SQL Server

描述

PostgreSQL

Text

  

CHAR(n)

固定长度的字符字符串，1