| 【译文】PostgreSQL9.5 给开发人员带来了什么？ 原作者：PETAR PARTLOV 创作时间：2016-04-11 22:29:55+08 |
doudou586 发布于2016-04-14 22:29:55
 评论: 3
 浏览: 9334
 顶: 837
 踩: 848
|
PostgreSQL 9.5版本发布已有一段时间了，新版本带给开发人员很多有意思的新功能。我们也发现新功能让我们的生活更轻松，代码写得也更安全了。其中的UPSERT和SELECT...SKIP LOCKED两项功能也是我们期待了较长时间的功能。
UPSERT
UPSERT可能是PostgreSQL中大家最想使用的功能。经常开发高并发应用软件的开发人员会用到这项功能。它让我们可以在一定条件下同时执行插入新记录或是更新记录的操作。
让我们做准备一下如下的数据表来测试UPSERT功能：
CREATE TABLE upsert_test (
id SERIAL NOT NULL PRIMARY KEY,
type int4 NOT NULL,
number int4 NOT NULL,
name text NOT NULL
);
ALTER TABLE upsert_test ADD CONSTRAINT upsert_test_unique UNIQUE (type, number);
现在我们可以试一些UPSERT的各种操作选项。假定我们要往这个表导入两条记录，就像如下内容：
id | type | number | name ----+------+--------+--------- 1 | 1 | 1 | Name 1 2 | 1 | 2 | Name 2
根据文档，我们可以在插入数据发生键值冲突时按以下两种方式处理：一种是对冲突记录不做处理（即跳过）或是对发生冲突的记录执行更新操作。最重要的是PostgreSQL 来为我们保证在包含约束情况下的更新都是事务完整的（即不会发生另一事务因增加记录而发生约束冲突的情况）。 这对开发人员来说是非常棒的功能，因为以前开发人员不得不自己通过SQL来处理这些问题。 让我们看一下最常使用的场景。我们想插入一些新的记录到表中，但有一些约束冲突，我们可以跳过有冲突的记录：
INSERT INTO upsert_test (type, number, name) VALUES (1, 1, 'Name 3') ON CONFLICT DO NOTHING; INSERT 0 0
我们可以看到，插入操作没有任何报错提示，但实际上没有任何新记录生成。这一点很重要，有时我们需要在代码中处理（其他功能）时必需是基于数据插入操作要有无错误返回时才能进行下一步。 现在我们再看看如果我们想改变有冲突的记录，如何处理：
-- 示例为如果唯一约束冲突，将涉及的记录的名称改为新的数据 INSERT INTO upsert_test (type, number, name) VALUES (1, 1, 'Name 3') ON CONFLICT (type, number) DO UPDATE SET name = EXCLUDED.name; INSERT 0 1
从示例中我们可以清楚地看到唯一键冲突，这些记录的名称会被更新为新的数据。另外，在这个场景中type和number列必须有唯一约束，如果这个索引不存在的话，系统会报错。 我们也可以在上述SQL中指定约束名称，不过这并不是一个很好的选择，最好还是如示例中一样指定字段名称，因为将来也许我们会改变约束的名称，或是按不同的排列顺序创建其他约束。 我们也可以给冲突的列增加限定条件，如下例：
-- 示例为如果唯一约束冲突，将涉及的记录的名称改为新的数据 INSERT INTO upsert_test (type, number, name) VALUES (1, 1, 'Name 3') ON CONFLICT (type, number) WHERE type = 1 DO UPDATE SET name = EXCLUDED.name;
如果我们增加了一个限定条件(本例中为WHERE type = 1)，而我们并没有这一条件的索引，PostgreSQL会试着使用一个不含限定条件的索引（不是局部索引）并使用它。 这也意谓着系统会检查所有type列和number列所有可能发生冲突的情况，而不仅仅是在type=1的情况下！我们也可以对更新操作增加WHERE条件：
-- 示例为如果唯一约束冲突，将涉及的记录的名称改为新的数据 INSERT INTO upsert_test (type, number, name) VALUES (1, 1, 'Name 3') ON CONFLICT (type, number) DO UPDATE SET name = EXCLUDED.name WHERE type = 1;
在这个示例中，只有type = 1的记录会被更新，不满足这个条件的记录会被忽略。同时也要注意，所有有约束冲突的记录会被锁定，即使是因为它们的WHERE条件不满足时不会被更新。
新的GROUP BY选项
在PostgreSQL 9.5版本中，我们对GROUP BY增加了新的选项，我们可以使用新增加的三种选项来创建更灵活的数据集，三种选项是：GROUPING SETS、ROLLUP和CUBE。 假定我们有如下的一个表：
SELECT * FROM employee; id | full_name | age | salary | gender | role ----+------------------+-----+-----------+--------+----------------- 1 | Barry Sutton | 31 | 60000.00 | Male | Manager 2 | Austin Jensen | 26 | 52500.00 | Male | Developer 3 | Charles Robbins | 48 | 80400.00 | Male | Project Manager 4 | Sylvester Carson | 22 | 45800.00 | Male | Developer 5 | Julius Gonzales | 38 | 71000.00 | Male | Sales 6 | Lana Lewis | 34 | 63700.00 | Female | Manager 7 | Gail Ingram | 30 | 54300.00 | Female | Developer 8 | Delores Brock | 24 | 48250.00 | Female | Sales 9 | Whitney Mcguire | 51 | 79800.00 | Female | Project Manager 10 | Kristy Romero | 55 | 102800.00 | Female | CEO (10 rows)
如果我们想看一下按gender、role和age(即性别、角色和年龄）三个字段在各种分组条件下的平均工资，我们可以这样做：
SELECT role, gender, (age / 10)::text || 'x' AS age_group, avg(salary) FROM employee GROUP BY GROUPING SETS (role, gender, age / 10, ());
role | gender | age_group | avg
-----------------+--------+-----------+--------
CEO | | | 102800
Developer | | | 50867
Manager | | | 61850
Project Manager | | | 80100
Sales | | | 59625
| | | 65855
| | 2x | 48850
| | 3x | 62250
| | 4x | 80400
| | 5x | 91300
| Female | | 69770
| Male | | 61940
(12 rows)
我们可以看到每一种分组下的平均工资和总的平均工资（即其中role, gender 和age三个字段都是空值的一行）。 除了这样操作，我们也可以看一下三个字段排列组合后作为分组条件下的平均工资（这是要将数学不好的我绕晕吗）：
SELECT role, gender, (age / 10)::text || 'x' AS age_group, avg(salary) FROM employee GROUP BY CUBE (role, gender, age / 10);
role | gender | age_group | avg
-----------------+--------+-----------+--------
CEO | Female | 5x | 102800
CEO | Female | | 102800
CEO | | | 102800
Developer | Female | 3x | 54300
Developer | Female | | 54300
Developer | Male | 2x | 49150
Developer | Male | | 49150
Developer | | | 50867
Manager | Female | 3x | 63700
Manager | Female | | 63700
Manager | Male | 3x | 60000
Manager | Male | | 60000
Manager | | | 61850
Project Manager | Female | 5x | 79800
Project Manager | Female | | 79800
Project Manager | Male | 4x | 80400
Project Manager | Male | | 80400
Project Manager | | | 80100
Sales | Female | 2x | 48250
Sales | Female | | 48250
Sales | Male | 3x | 71000
Sales | Male | | 71000
Sales | | | 59625
| | | 65855
Developer | | 2x | 49150
Sales | | 2x | 48250
| | 2x | 48850
Developer | | 3x | 54300
Manager | | 3x | 61850
Sales | | 3x | 71000
| | 3x | 62250
Project Manager | | 4x | 80400
| | 4x | 80400
CEO | | 5x | 102800
Project Manager | | 5x | 79800
| | 5x | 91300
| Female | 2x | 48250
| Female | 3x | 59000
| Female | 5x | 91300
| Female | | 69770
| Male | 2x | 49150
| Male | 3x | 65500
| Male | 4x | 80400
| Male | | 61940
(44 rows)
我们可以看到各种可能的排列组合情况下的平均工资，强！最后，我们再来看一下如果我们想从左向右进行分组，如何使用选项来实现：
SELECT role, gender, (age / 10)::text || 'x' AS age_group, avg(salary) FROM employee GROUP BY ROLLUP (role, gender, age / 10);
role | gender | age_group | avg
-----------------+--------+-----------+--------
CEO | Female | 5x | 102800
CEO | Female | | 102800
CEO | | | 102800
Developer | Female | 3x | 54300
Developer | Female | | 54300
Developer | Male | 2x | 49150
Developer | Male | | 49150
Developer | | | 50867
Manager | Female | 3x | 63700
Manager | Female | | 63700
Manager | Male | 3x | 60000
Manager | Male | | 60000
Manager | | | 61850
Project Manager | Female | 5x | 79800
Project Manager | Female | | 79800
Project Manager | Male | 4x | 80400
Project Manager | Male | | 80400
Project Manager | | | 80100
Sales | Female | 2x | 48250
Sales | Female | | 48250
Sales | Male | 3x | 71000
Sales | Male | | 71000
Sales | | | 59625
| | | 65855
(24 rows)
SELECT ... SKIP LOCKED
与NOWAIT选项不同的是，SKIP LOCKED是作为SELECT查询的一个选项加入的功能。如果一条记录不能被锁定，则NOWAIT选项（老版本PostgreSQL一直存在的选项）会简单地返回错误，而新的 SKIP LOCKED选项则会跳过这条记录。这在一些高并发的场景下会非常有用，比如我们需要选择一部分记录，同时也可以接受少量记录无法查询（如一些应用给用户显示一些随机的记录，随机 跳过一条锁定的记录是可以接受的）。
SELECT ... TABLESAMPLE
TABLESAMPLE选项允许我们从表中随机提取1%的记录。这在对一些大表生成统计分析报表时特别有用。缺省情况下，有两种随机抽样方法：SYSTEM 和 BERNOULLI。 SYSTEM 方式是从一个表（磁盘上的页）提取一部分物理数据然后返回。如果我们的插入操作是相对平均地分布，这种方式就较好，但如果不是，则建议使用BERNOULLI方式。 这种方式是扫描整个表后，然后再返回随机行记录。这种方式略慢，特别是我们从一个表中只返回很少一部分数据时。例如，我们有一个100万行记录的表，需要提取1%的记录， SYSTEM方式（个人测试环境）需要7ms，而BERNOULLI方式则需约43 ms。如果我们需要50%的记录，SYSTEM方式需要292ms，而BERNOULLI方式需要331ms。 我们也可以增加一个随机数作为参数，这样如果是表未发生改变，则每次会返回相同的数据。如果我们省略这个随机数，每次调用时会返回不同的记录，如下例：
SELECT * FROM employees TABLESAMPLE BERNOULLI (1);
UPDATE multiple columns from SELECT sub-query（按子查询更新多列数据 ）
要根据一个子查询所返回的对应相同number和types字段的值来更新一个表中记录，可以这样：
UPDATE executors ex SET (full_name, role) = ( SELECT em.name, em.role FROM employee em WHERE em.employee_id = ex.employee_id );
在上例中，两列(full_name 和role) 会按子查询的结果被更新。这样就没有必要来创建两个独立的脚本，来提取数据、保存子查询结果在临时变量中，或是使用WITH功能（一个更高级和复杂的功能） 来实现类似的效果。
Suggest misspelled column names（对误拼写列名的自动建议）
这个小小的功能也很有用，特别是在使用缺省的autocomplete(即自动完成功能)功能不强的psql客户端时，我们经常会对一些列名的拼写搞错，系统就会提示，如：
SELECT id, nam FROM employees;
ERROR: column "nam" does not exist
LINE 1: select id, nam from employees;
^
HINT: Perhaps you meant to reference the column "employees.name". （提示，你录入的列名是否应是的“employees.name”）
不过有一点不完美的是，如果我们拼写了多列错误名称，系统只会提示第一个错误，这样我们(在修改了第一个错误后)需要再次提交查询时，系统才会提示下一次错误。
DDL 改变
最重要的是IF NOT EXISTS选项同时加入至CREATE TABLE AS, CREATE INDEX, CREATE SEQUENCE 和 CREATE MATERIALIZED VIEW 等语法中， IF EXISTS选项加入至ALTER TABLE...RENAME CONSTRAINT语法中。
索引的改变
PostgreSQL 9.5版本中新增了BRIN索引，BRIN索引对列的值含有在表中位置的情况下特别有用（指在磁盘上页中物理位置）。例如， 我们有时在新增记录时，如果大部分具有相同时间值的记录在相似的时间内保存，则这些记录会保存在表的相同页面中。这对一些大表使用很大的B-TREE索引时尤其有用。 在BRIN索引中如果有时在相关页面中未包含搜索值时，B-TREE索引还是要快一些。其他情况下BRIN索引占用空间是很小的，执行也是很快的。 针对一个具体的表使用何种索引方式，是一个需要折中考虑的问题。我们可以对一个6千万行的表比较一些索引数据：
BRIN B-Tree Index size 136 KB 1414 MB Duration 4.5 ms 0.374 ms
这是一个对表中符合一定条件的行记录进行统计的数据。这个查询没有索引时需要11.6秒。两种索引方式在占用空间和性能上有较大差异。根据这些数据，BRIN索引适用于大表 且是不经常进行查询的场景下。需要注意的是，索引需要定期维护，如是带有新的数据相似性的记录加入后，则索引会自动维护。另一方面，如果是新的无相似性记录加入后， 索引需要手工维护，这可以通过调用表的VACUUM来实现，或是调用brin_summarize_new_pages(regclass)函数也可以。
排序优化
根据PostgreSQL 9.5版本的发布说明，排序现在比以前要快很多。我做了一些测试也的确如此，特别是对未索引的表。在一个特定的例子中，我对一个32字节并且有100万行记录的字符串进行排序， 在9.4版本中需要6.2秒，而在9.5版本中只需3.4秒。对已索引的列进行排序，提升则很有限，只有大约5%。
其他
最值得一试的改变是现在 vacuumdb 功能有了一个 -j 的选项，它表示这项任务可以同时几个进程并发处理：
vacuumdb -j 8
注意，每一个任务会创建独立的数据库连接，因此数据库的最大连接数至少应大于这个选项中的数据。
请在登录后发表评论，否则无法保存。
