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

数据库 专栏收录该内容
136 篇文章 3 订阅

目录

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将引发错误。您可以分别为每个数据库打开一个连接,也可以使用dblinkForeign Data Wrappers从多个数据库中查询表。 

T-SQLPL/pgSQL  

SQL Server对标准SQL语言的过程扩展简称为Transact-SQLT-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, CUSTOMERScustomers都是相同的。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)

 

每张表的最大列数

1024non-wide)30000(wide

250 – 1600,取决于列类型

 

最大数据库大小

524,272 TB

无限

 

最大Varchar长度

Varchar(Max)

Varchar(10485760)将文本用于较长的字符串

 

调度器

SQL Server代理

pgAgent

 

SQL Server最大容量规格(v2012及更低版本) 

SQL Server最大容量规格(v2014 

数据类型 

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

Microsoft SQL Server

描述

PostgreSQL

Text

  

CHAR(n)

固定长度的字符字符串,1 <= n <= 8000

CHAR(n)

VARCHAR(n)

可变长度的char字符串,1 <= n <= 8000

VARCHAR(n)

VARCHAR(max)

可变长度字符字符串,<= 2GB

TEXT

NVARCHAR(n)

可变长度Unicode UCS-2字符串

VARCHAR(n)

NVARCHAR(max)

可变长度Unicode UCS-2数据,<= 2GB

TEXT

TEXT

可变长度字符数据,<= 2GB

TEXT

NTEXT

可变长度Unicode UCS-2数据,<= 2GB

TEXT

UNIQUEIDENTIFIER

16字节的GUIDUUID)数据

CHAR(16)

Numeric

  

BIGINT

64位整数

BIGINT

INTEGER

32位整数

INTEGER

TINYINT

8位无符号整数,0255

SMALLINT

DOUBLE PRECISION

双精度浮点数

DOUBLE PRECISION

FLOAT(p)

浮点数

DOUBLE PRECISION

NUMERIC(p,s)

定点数

NUMERIC(p,s)

SMALLMONEY

32位货币金额

SMALLMONEY

Date

  

DATE

日期包括年,月和日

DATE

DATETIME

带分数的日期和时间

TIMESTAMP(3)

DATETIME2(p)

带分数的日期和时间

TIMESTAMP(n)

DATETIMEOFFSET(p)

带有分数和时区的日期和时间

带时区的TIMESTAMP(p)

SMALLDATETIME

日期和时间

TIMESTAMP(0)

Boolean

  

BIT

10NULL

BOOLEAN

Binary

  

BINARY(n)

固定长度的字节字符串

BYTEA

VARBINARY(n)

可变长度的字节字符串,1 <= n <= 8000

BYTEA

VARBINARY(max)

可变长度的字节字符串,<= 2GB

BYTEA

ROWVERSION

自动更新二进制数据

BYTEA

IMAGE

可变长度的二进制数据,<= 2GB

BYTEA

该列表仅包含常用的数据类型,PostgreSQL支持多种数据类型,还支持自定义数据类型。有关PostgreSQL支持的主要数据类型的完整列表,请查看文章。 

内置功能和运算符 

PostgreSQL具有丰富的运算符和内置函数集,远远超出了SQL Server提供的功能。 

Microsoft SQL Server

PostgreSQL

评论

DATEPART

DATE_PART

 

ISNULL

COALESCE

 

SPACE

REPEAT

 

DATEADD

+

 

+

||

字符串串联

CHARINDEX

POSITION

 

GETDATE

NOW

 

LTRIM/RTRIM

TRIM

 

REPLACE

OVERLAY

 

LEN

OCTET_LENGTH

 

有关所有PostgreSQL函数和运算符的完整列表,请查看页面。 

SQL语言差异  

特征

Microsoft SQL Server

PostgreSQL

选择前N

TOP n

LIMIT n

语句终止符

(不需要)

(需要)

LIKE 运算符

默认情况下不区分大小写(由排序规则确定)

区分大小写,使用ILIKE进行不区分大小写的比较

正则表达式

LIKE运算符

SIMILAR TO运算符

CTE性能差异 

SQL Server中,此查询: 

WITH AllUsers AS (SELECT * FROM Users)
SELECT * FROM AllUsers WHERE Id = 100; 

一次生成整个查询的查询计划,并且WHERE子句过滤器传递到CTE中。生成的查询计划是高效的,仅执行单个聚集索引查找即可。 

PostgreSQL中,CTE是优化屏障(外部查询限制不传递给CTE),数据库在CTE内部评估查询并缓存结果(即物化结果),并且稍后在处理外部查询时应用外部WHERE子句,这意味着将执行全表扫描或全索引查找,这会导致大型表的糟糕性能。为了克服这个问题,您可以在PostgreSQL中将该查询重写为: 

WITH UserRecord AS (SELECT * FROM Users WHERE Id = 100)
SELECT * FROM UserRecord;  

另一个选择是使用子查询重写查询。在迁移涉及CTE的代码和查询时,请记住这一点。 

请注意,PostgreSQL 12通过引入查询优化器提示来解决此问题,以使我们能够控制CTE是否应物化:MATERIALIZEDNOT MATERIALIZED。因此,可以将查询重写如下,以获得更好的性能。

WITH AllUsers AS NOT MATERIALIZED (SELECT * FROM Users)
SELECT * FROM AllUsers WHERE Id = 100;  

Collation / Ordering 

SQL ServerPostgreSQL存储和比较数据的方式几乎没有根本差异。在SQL Server中,默认排序规则区分大小写,而在PostgreSQL中,默认排序规则区分大小写。因此,在联接或LIKE语句中的WHERE子句或ON子句中搜索文本的代码/查询可能会失败。PostgreSQL提供了一个不区分大小写的比较ILIKE语句。 

当删除不删除时 

SQL Server中,当对表发出DELETE语句时,行将从存储中永久删除。在PostgreSQL中,它们只是标记为删除(软删除),而不会立即从存储中删除。UPDATE语句的行为类似,将旧行标记为删除,并将新行与新行数据一起插入。 

在迁移定期进行大量删除和更新的表时,请记住这一点,因为在执行清理(VACUUM)之前,它们将呈指数增长。您将必须设置一个工作,以定期清理数据库中的表,该间隔可以每天,每周或每月一次,具体取决于每个表的增长率。 

自动清理后台程序在默认情况下开启,并确保清理定期自动完成的,因此您可能不会在大多数情况下担心。但是,它可以在安装过程中关闭,也可以稍后由数据库管理员关闭。如果将PostgreSQL数据库托管在AWS Aurora中,则即使在运行自动清理(AutoVacuum)守护程序时也要小心。查看帖子以获取更多信息。 

有关更多详细信息,请参见VACUUM语句。

PostgreSQL有优势的地方 

PostgreSQL提供了一组丰富的运算符和函数来处理不同类型的数据。 

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

PostgreSQLWindowsLinuxUnix等上运行,而SQL Server 2016和更早版本仅在Windows上运行。SQL Server 2017也可以在Linux上运行。 

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

此查询使用随机数据将一百万行插入到customers表中。 

INSERT INTO customers (id, name)
SELECT id, md5(random()::text) 
FROM generate_series(1, 1000000) AS id; 

SQL Server中,您将只能使用过程代码来执行此操作。 

PostgreSQL中的多语言支持 

当前在标准PostgreSQL发行版中有四种可用的过程语言:PL/pgSQLPL/TclPL/PerlPL/PythonPostgreSQL也支持许多其他过程语言,您只需要安装适当的扩展并将其启用即可。  

  • PL/pgSQL:此PostgreSQL的本地过程语言类似于SQL ServerT-SQL,但更加完善和功能齐全。 
  • PL/PythonPostgreSQLPython的支持为您的武器库增加了庞大的Python库生态系统。想象一下,在SQL查询中间使用Python函数Yayy !!! 
  • PL/PerlPostgreSQL完全支持Perl作为过程语言。  
  • PL/TclPL/TclPostgreSQL数据库系统的可加载过程语言,它使Tcl语言可用于编写PostgreSQL函数。 
  • PL/V8JavaScript引擎稳定,功能丰富且非常快速。PostgreSQL本身支持JSON数据类型,这一事实使其功能强大且灵活。PL/V8支持在内存中缓存数据,这使其成为数据密集型行级操作的理想语言。
  • PL/RPostgreSQL完全支持RR是一种统计编程语言,广泛用于数据科学中,具有一组强大的高质量插件和附加组件。  
    C
    :尽管PostgreSQL支持C,但它必须单独编译。当对内存管理的速度和精细控制,任务的资源使用和性能至关重要时,这非常有用。 

PostgreSQL还具有其他语言的扩展,例如JavaRubyPHPLuaTcl等。 

有人可以说SQL Server也支持多种语言,因为它充当.NET主机,并且可以运行以任何.NET语言(如C#,VB.NETF#等)编写的代码。是的,但是涉及到在.NET中编写代码。像Visual Studio这样的IDE,编译代码,在SQL Server中部署程序集,为C#函数编写包装T-SQL函数,然后从T-SQL代码中调用该函数。更不用说调试地狱了。您不能像PostgreSQL中的LANGUAGE关键字允许您用多种语言编写函数那样,在T-SQL代码内内联编写C#代码。 

SQL Server中亲自完成多语言编程之后,我可以保证PostgreSQL提供的多语言支持级别与SQL Server处于不同级别。

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

GREATEST函数与MAX函数相似,不同之处在于GREATEST函数可以跨列工作。而LEAST函数类似MIN函数,但它可以跨列。 

SELECT GREATEST(LastLoginDate, LastReportDate, LastInteractionDate) FROM Users;

您不能在SQL Server中执行此操作,除非有多个SELECT语句和过程结构。同样,PostgreSQL中有大量功能可用,而SQL Server中不可用。

PostgreSQLCSV有更好的支持 

每天处理数据的人经常会使用CSV来将数据从一种格式转换为另一种格式并进行分析处理等。PostgreSQLCOPY FROMCOPY TO命令在处理CSV方面做得更加干净:文本被截断,不再进行编码噩梦,不再引用/转义问题。

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

PostgreSQL中,您要做的就是执行DROP SCHEMA CASCADE。这在开发过程中非常有用,但同时又在过渡和生产环境中冒着地狱般的危险。 

SQL Server中,DROP SCHEMA不支持CASCADE子句。必须先删除架构中的所有对象,然后再删除架构,这可能是开发和原型制作过程中的噩梦。 

Unicode 和字符编码基础 

Unicode是字符编码标准,而UTF-8UTF-16是该标准的不同实现。 

UTF-8UTF-8使用1个字节表示低于128的字符代码,并使用234个字节表示超出此范围的字符。与ASCII兼容。 

UTF-16:始终使用两个或四个字节,与ASCII不兼容 

UCS-2:始终使用两个字节。

Unicode支持 

PostgreSQLUTF-8编码具有本机支持,默认情况下其CHARVARCHARTEXT类型为UTF-8。字符串操作和正则表达式均支持UTF-8PostgreSQL不支持UTF-16 

SQL Server2012年之前)仅支持UCS-2UTF-16的子集)。SQL Server 2012引入了对UTF-16的可选支持,但是您必须为数据库选择UTF-16排序规则才能使其工作。SQL Server不支持UTF-8 

SQL Server有优势的地方 

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

SQL Server不同,您不能直接在查询中引用两个数据库。但是有一种解决方法,您可以使用dblinkForeign Data Wrapper 

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

SQL Server中,可以在脚本中声明变量或直接使用条件逻辑: 

DECLARE @MeanAge = 100
IF EXISTS (SELECT * FROM Users WHERE Age > @MeanAge) 
    SELECT 'Yay'  
ELSE 
    SELECT 'Nay'; 

SQL Server将允许您使用客户端工具或在应用程序代码中的内联查询中运行此代码。在PostgreSQL中,您可以运行以下程序代码:

DO $$ 
-- declare
BEGIN
  /* pl/pgsql here */
END $$;

但是,您无法将任何结果返回给客户端。您可以运行过程代码并将结果返回给客户端的唯一方法是将其包装在存储过程或函数中,然后执行它。 

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

PostgreSQL 10.x及更低版本没有存储过程。但是,这并不是一件容易的事,因为您可以使用用户定义的函数来完成所有操作,唯一的例外是您不能在函数内部进行事务处理。

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

PostgreSQL 11.x及以下版本不支持计算列。但是,计划于2019年末发布的PostgreSQL 12引入了对'Generated column'的支持。看看这个文章以获取更多信息。

上一篇:将您的SQL Server工作负载迁移到PostgreSQL –第1部分

下一篇:将您的SQL Server工作负载迁移到PostgreSQL –第3部分

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值