PostgreSQL 9.5 的新功能
（本页目前正在开发，以迎接 PostgreSQL 9.5 的发布）
本页概括了 PostgreSQL 版本 9.5 的功能，包括描述、测试和使用信息，以及包含进一步信息的博客文章的链接。另请参阅PostgreSQL 9.5 Open Items。
主要新特性
IMPORT FOREIGN SCHEMA
之前，要在 PostgreSQL 中创建一个外键表，需要定义该表，引用目标列和数据类型，如果有大量表，这可能变得繁琐且容易出错，并且当那些表改变时，需要再次执行全部操作...
CREATE FOREIGN TABLE remote.customers (
id int NOT NULL,
name text,
company text,
registered_date date,
expiry_date date,
active boolean,
status text,
account_level text) SERVER dest_server OPTIONS (schema_name 'public');
CREATE FOREIGN TABLE remote.purchases (
id int NOT NULL,
purchase_time timestamptz,
payment_time timestamptz,
itemid int,
volume int,
invoice_sent boolean) SERVER dest_server OPTIONS (schema_name 'public');
从 PostgreSQL 9.5 开始，可以批量导入表。
IMPORT FOREIGN SCHEMA public FROM SERVER dest_server INTO remote;
这将在“remote”模式中为外键服务器“dest_server”上 public 模式中出现的每张表创建外键表。
还可以过滤掉任何不希望的表。
IMPORT FOREIGN SCHEMA public EXCEPT (reports, audit) FROM SERVER dest_server INTO remote;
或将它限制为仅特定的一组表。
IMPORT FOREIGN SCHEMA public LIMIT TO (customers, purchases) FROM SERVER dest_server INTO remote;
链接
等待 9.5 – 实现 IMPORT FOREIGN SCHEMA。
PostgreSQL 9.5：IMPORT FOREIGN SCHEMA。
行级安全策略
可以向表添加额外的安全措施，以防止用户访问他们不应该看到的行。
假设有一张日志表，其中 username 列包含创建日志条目的数据库用户名。
CREATE TABLE log (
id serial primary key,
username text,
log_event text);
但是，不希望用户看到其他用户的日志条目，所以我们可以创建一个策略，规定如果 username 列匹配正在运行查询的当前用户，则允许用户看到该行。
CREATE POLICY policy_user_log ON log FOR ALL TO PUBLIC USING (username = current_user);
然后，我们对表启用行级安全。
ALTER TABLE log ENABLE ROW LEVEL SECURITY;
作为用户“report”，我们只能看到 username 列包含值“report”的行。
# SELECT * FROM log; id | username | log_event ----+----------+---------------- 1 | report | DELETE issued 4 | report | Reset accounts (2 rows)
作为用户“messaging”，可以看到不同的行集。
id | username | log_event ----+-----------+---------------------- 2 | messaging | Message queue purged 3 | messaging | Reset accounts (2 rows)
而作为超级用户的“postgres”用户可以得到
id | username | log_event ----+-----------+---------------------- 1 | report | DELETE issued 2 | messaging | Message queue purged 3 | messaging | Reset accounts 4 | report | Reset accounts (4 rows)
这是因为超级用户在超级用户角色上的 BYPASSRLS 属性默认情况下可以看到所有行。
如果有多个策略，它们都将通过 OR 运算符匹配在一起。例如，如果有以下 2 个策略
CREATE POLICY policy_user_log ON log FOR ALL TO PUBLIC USING (username = current_user);
CREATE POLICY policy_even_ids_only ON log FOR ALL TO PUBLIC USING (id % 2 = 0);
这会有效地导致在任何语句的 WHERE 子句中添加以下内容
WHERE ((username = current_user) OR (id % 2 = 0))
所以，只要匹配一个策略，就可以通过，就像上面提到的 report 用户，我们现在可以看到以下内容
id | username | log_event ----+-----------+---------------------- 1 | report | DELETE issued 2 | messaging | Message queue purged 4 | report | Reset accounts (3 rows)
额外的策略现在允许 id 为 2 的行，因为它与新策略匹配。第一行与新策略不匹配，但它被返回，因为它与第一个策略匹配。所以，策略是允许的，而不是限制的。
链接
BRIN 索引
BRIN 代表 Block Range INdexes（区块范围索引），在页面范围内存储元数据。目前，其含义是每个区块的最小值和最大值。
结果就是生成一个占用空间很少且价格低廉的索引，并能加快超大表中的查询速度。这可以让索引确定哪些区块是唯一值得检查的，而其他所有区块都可以跳过。因此，如果一个 10GB 的订单表包含按订单日期大致排好序的行，那么在 order_date 列上创建一个 BRIN 索引后，就无需执行完整的顺序扫描，而只需跳过表中的大部分。这仍然比在同一列上执行常规 BTREE 索引慢，但好处是它小得多且需要更少的维护。
例如
-- Create the table
CREATE TABLE orders (
id int,
order_date timestamptz,
item text);
-- Insert lots of data into it
INSERT INTO orders (order_date, item)
SELECT x, 'dfiojdso'
FROM generate_series('2000-01-01 00:00:00'::timestamptz, '2015-03-01 00:00:00'::timestamptz,'2 seconds'::interval) a(x);
-- Let's look at how much space the table occupies
# \dt+ orders
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+-------+-------+-------------
public | orders | table | thom | 13 GB |
(1 row)
-- What's involved in finding the orders between 2 dates
# EXPLAIN ANALYSE SELECT count(*) FROM orders WHERE order_date BETWEEN '2012-01-04 09:00:00' and '2014-01-04 14:30:00';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5425021.80..5425021.81 rows=1 width=0) (actual time=30172.428..30172.429 rows=1 loops=1)
-> Seq Scan on orders (cost=0.00..5347754.00 rows=30907121 width=0) (actual time=6050.015..28552.976 rows=31589101 loops=1)
Filter: ((order_date >= '2012-01-04 09:00:00+00'::timestamp with time zone) AND (order_date <= '2014-01-04 14:30:00+00'::timestamp with time zone))
Rows Removed by Filter: 207652500
Planning time: 0.140 ms
Execution time: 30172.482 ms
(6 rows)
-- Now let's create a BRIN index on the order_date column CREATE INDEX idx_order_date_brin ON orders USING BRIN (order_date);
-- And see how much space it takes up
# \di+ idx_order_date_brin
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+---------------------+-------+-------+--------+--------+-------------
public | idx_order_date_brin | index | thom | orders | 504 kB |
(1 row)
-- Now let's see how much faster the query is with this very small index
# EXPLAIN ANALYSE SELECT count(*) FROM orders WHERE order_date BETWEEN '2012-01-04 09:00:00' and '2014-01-04 14:30:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2616868.60..2616868.61 rows=1 width=0) (actual time=6347.651..6347.651 rows=1 loops=1)
-> Bitmap Heap Scan on orders (cost=316863.99..2539600.80 rows=30907121 width=0) (actual time=36.366..4686.634 rows=31589101 loops=1)
Recheck Cond: ((order_date >= '2012-01-04 09:00:00+00'::timestamp with time zone) AND (order_date <= '2014-01-04 14:30:00+00'::timestamp with time zone))
Rows Removed by Index Recheck: 6419
Heap Blocks: lossy=232320
-> Bitmap Index Scan on idx_order_date_brin (cost=0.00..309137.21 rows=30907121 width=0) (actual time=35.567..35.567 rows=2323200 loops=1)
Index Cond: ((order_date >= '2012-01-04 09:00:00+00'::timestamp with time zone) AND (order_date <= '2014-01-04 14:30:00+00'::timestamp with time zone))
Planning time: 0.108 ms
Execution time: 6347.701 ms
(9 rows)
这个示例在 SSD 驱动器上，因此，在 HDD 上其效果会更加明显。
默认情况下，区块大小为 128 个页面。可以使用 pages_per_range 增加或减少此分辨率
-- Create an index with 32 pages per block CREATE INDEX idx_order_date_brin_32 ON orders USING BRIN (order_date) WITH (pages_per_range = 32); -- Create an index with 512 pages per block CREATE INDEX idx_order_date_brin_512 ON orders USING BRIN (order_date) WITH (pages_per_range = 512);
每个区块中的页数越少，索引将占用的空间就越多，但损失的数据量也就越少，即它需要舍弃的行数就越少。
# \di+ idx_order_date_brin*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------------------+-------+-------+--------+---------+-------------
public | idx_order_date_brin | index | thom | orders | 504 kB |
public | idx_order_date_brin_32 | index | thom | orders | 1872 kB |
public | idx_order_date_brin_512 | index | thom | orders | 152 kB |
(3 rows)
链接
外部表继承
外部表现在既可以继承本地表，也可以被继承。
例如，本地表可以继承外部表
-- Create a new table which inherits from the foreign table
# CREATE TABLE local_customers () INHERITS (remote.customers);
-- Insert some data into it
# INSERT INTO local_customers VALUES (16, 'Bruce',$$Jo's Cupcakes$$, '2015-01-15', '2017-01-14', true, 'running', 'basic');
-- And if we query the parent foreign table...
# SELECT tableoid::regclass, * FROM remote.customers;
tableoid | id | name | company | registered_date | expiry_date | active | status | account_level
------------------+----+-------+---------------+-----------------+-------------+--------+---------+---------------
remote.customers | 1 | James | Hughson Corp | 2014-05-03 | 2016-05-02 | t | idle | premium
local_customers | 16 | Bruce | Jo's Cupcakes | 2015-01-15 | 2017-01-14 | t | running | basic
(2 rows)
或者，也可以让外部表继承自本地表
-- Create a new table that the foreign table will be a child of
# CREATE TABLE master_customers (LIKE remote.customers);
-- Insert a new row into this table
# INSERT INTO master_customers VALUES (99, 'Jolly',$$Cineplanet$$, '2014-10-30', '2016-10-29', true, 'running', 'premium');
-- Have the foreign table inherit from the new table
# ALTER TABLE remote.customers INHERIT master_customers;
-- Let's have a look at the contents of the new table now
# SELECT tableoid::regclass, * FROM master_customers;
tableoid | id | name | company | registered_date | expiry_date | active | status | account_level
------------------+----+-------+---------------+-----------------+-------------+--------+---------+---------------
master_customers | 99 | Jolly | Cineplanet | 2014-10-30 | 2016-10-29 | t | running | premium
remote.customers | 1 | James | Hughson Corp | 2014-05-03 | 2016-05-02 | t | idle | premium
local_customers | 16 | Bruce | Jo's Cupcakes | 2015-01-15 | 2017-01-14 | t | running | basic
(3 rows)
-- And the query plan...
# EXPLAIN ANALYSE SELECT tableoid::regclass, * FROM master_customers;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..140.80 rows=1012 width=145) (actual time=0.014..0.595 rows=3 loops=1)
-> Append (cost=0.00..140.80 rows=1012 width=145) (actual time=0.012..0.591 rows=3 loops=1)
-> Seq Scan on master_customers (cost=0.00..1.48 rows=48 width=145) (actual time=0.012..0.013 rows=1 loops=1)
-> Foreign Scan on customers (cost=100.00..124.52 rows=484 width=145) (actual time=0.567..0.567 rows=1 loops=1)
-> Seq Scan on local_customers (cost=0.00..14.80 rows=480 width=145) (actual time=0.007..0.008 rows=1 loops=1)
Planning time: 0.256 ms
Execution time: 1.040 ms
(7 rows)
链接
基于 9.5 的允许外部表参与继承。- 也称为 PostgreSQL 实现分片。
Postgres 9.5 特性亮点：使用外部表进行横向扩展，现已成为继承树的一部分
GROUPING SETS、CUBE 和 ROLLUP
这些特性允许将数据概括到各种集合中。
例如，如果我们有这些数据
# SELECT * FROM employees;
name | role | department | gender
----------+-----------------+------------+--------
Tim | Manager | Sales | Male
Sarah | Manager | Finance | Female
Neil | Accountant | Finance | Male
Joe | Project Manager | Sales | Male
Yvette | Project Manager | Finance | Female
Craig | Project Manager | IT | Male
Penelope | Manager | IT | Female
(7 rows)
如果我们想看到每个部门、角色和性别的数据概况，我们可以使用 GROUPING SETS
# SELECT department, role, gender, count(*)
FROM employees
GROUP BY GROUPING SETS (department, role, gender, ());
department | role | gender | count
------------+-----------------+-----------+-------
Finance | | | 3
IT | | | 2
Sales | | | 2
| | | 7
| | Female | 3
| | Male | 4
| Accountant | | 1
| Manager | | 3
| Project Manager | | 3
(9 rows)
在这里，我们可以看到每个部门、每个角色和每个性别的员工人数。我们还可以得到一个总计，其中 count 除外，所有列都为空。
如果我们想要计算这 3 个类别的每种组合的数量，我们可以使用 CUBE
# SELECT department, role, gender, count(*)
FROM employees
GROUP BY CUBE (department, role, gender);
department | role | gender | count
------------+-----------------+-----------+-------
Finance | Accountant | Male | 1
Finance | Accountant | | 1
Finance | Manager | Female | 1
Finance | Manager | | 1
Finance | Project Manager | Female | 1
Finance | Project Manager | | 1
Finance | | | 3
IT | Manager | Female | 1
IT | Manager | | 1
IT | Project Manager | Male | 1
IT | Project Manager | | 1
IT | | | 2
Sales | Manager | Male | 1
Sales | Manager | | 1
Sales | Project Manager | Male | 1
Sales | Project Manager | | 1
Sales | | | 2
| | | 7
Finance | | Female | 2
IT | | Female | 1
| | Female | 3
Finance | | Male | 1
IT | | Male | 1
Sales | | Male | 2
| | Male | 4
| Accountant | Male | 1
| Accountant | | 1
| Manager | Female | 2
| Manager | Male | 1
| Manager | | 3
| Project Manager | Female | 1
| Project Manager | Male | 2
| Project Manager | | 3
(33 rows)
然后，我们可以得到所有值的每一种组合的数量。如果我们想确保各列按顺序进行分组，只从左到右进行概括，我们可以使用 ROLLUP
# SELECT department, role, gender, count(*)
FROM employees
GROUP BY ROLLUP (department, role, gender);
department | role | gender | count
------------+-----------------+-----------+-------
Finance | Accountant | Male | 1
Finance | Accountant | | 1
Finance | Manager | Female | 1
Finance | Manager | | 1
Finance | Project Manager | Female | 1
Finance | Project Manager | | 1
Finance | | | 3
IT | Manager | Female | 1
IT | Manager | | 1
IT | Project Manager | Male | 1
IT | Project Manager | | 1
IT | | | 2
Sales | Manager | Male | 1
Sales | Manager | | 1
Sales | Project Manager | Male | 1
Sales | Project Manager | | 1
Sales | | | 2
| | | 7
(18 rows)
然后，我们不会得到每个角色或每个性别的概况，除非与前几列一起使用它们。
这些只是基本示例。还可以进行更复杂的配置。
链接
JSONB 修改运算符和函数
在 9.3（在 9.4 中更大程度上），JSONB 数据可以使用各种函数和运算符提取，但没有一个可以真正修改数据。从 9.5 开始，JSONB 数据现在可以被修改。
jsonb || jsonb（连接/覆盖）
|| 运算符允许我们组合 2 个 jsonb 对象。如果有重叠，最高级别的值将被替换。
例如，如果我们想向 jsonb 对象添加值
# SELECT '{"name": "Joe", "age": 30}'::jsonb || '{"town": "London"}'::jsonb;
?column?
----------------------------------------------
{"age": 30, "name": "Joe", "town": "London"}
(1 row)
或者我们可以覆盖现有值
# SELECT '{"town": "Dataville", "population": 4096}'::jsonb || '{"population": 8192}'::jsonb;
?column?
-------------------------------------------
{"town": "Dataville", "population": 8192}
(1 row)
请注意，这仅适用于最高级别，因此嵌套对象将从最高级别替换。例如
# SELECT '{"name": "Jane", "contact": {"phone": "01234 567890", "mobile": "07890 123456"}}'::jsonb || '{"contact": {"fax": "01987 654321"}}'::jsonb;
?column?
------------------------------------------------------
{"name": "Jane", "contact": {"fax": "01987 654321"}}
(1 row)
jsonb - 文本/整数（删除键/数组元素）
我们可以使用 - 运算符从 jsonb 对象中删除键
# SELECT '{"name": "James", "email": "james@localhost"}'::jsonb - 'email';
?column?
-------------------
{"name": "James"}
(1 row)
或从数组中删除值（从 0 开始）
# SELECT '["red","green","blue"]'::jsonb - 1;
?column?
-----------------
["red", "blue"]
(1 row)
jsonb #- 文本数组/整数（删除路径中的键/数组元素）
在前一个示例中，我们可以删除键或数组元素，但级别不能低于最高级别，因此我们可以使用文本数组提供要删除的值的路径。这里我们将从 contact 值中删除传真号码
# SELECT '{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb #- '{contact,fax}'::text[];
?column?
---------------------------------------------------------
{"name": "James", "contact": {"phone": "01234 567890"}}
(1 row)
或者我们可以删除数组值。这里我们将摆脱索引为 1（第二个值）的数组值
# SELECT '{"name": "James", "aliases": ["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];
?column?
--------------------------------------------------
{"name": "James", "aliases": ["Jamie", "J Man"]}
(1 row)
jsonb_set 函数
上述内容允许我们删除路径中的值，但不能更新它们，因此我们有 jsonb_set 函数来完成此操作。我们将在联系信息值中更新电话值
# SELECT jsonb_set('{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb,
'{contact,phone}',
'"07900 112233"'::jsonb);
jsonb_replace
--------------------------------------------------------------------------------
{"name": "James", "contact": {"fax": "01987 543210", "phone": "07900 112233"}}
(1 row)
此函数的语法为
jsonb_set(
target jsonb, # The jsonb value you're amending.
path text[], # The path to the value you wish to add to or change, represented as a text array.
new_value jsonb, # The new object, key : value pair or array value(s) to add to or change.
create_missing boolean # An optional field that, if true (default), creates the value if the key doesn't already exist.
# If false, the path must exist for the update to happen, or the value won't be updated.
)
如果目标键不存在，并且你将 true 指定为第四个参数（默认值），它将附加在路径指定的路径中
# SELECT jsonb_set('{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb,
'{contact,skype}',
'"myskypeid"'::jsonb,
true);
jsonb_set
------------------------------------------------------------------------------------------------------
{"name": "James", "contact": {"fax": "01987 543210", "phone": "01234 567890", "skype": "myskypeid"}}
(1 row)
在这种情况下，使用 false 代替 true 的相同函数调用将不会更新，因为没有要更新的预先存在的键。
# SELECT jsonb_set(
'{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb,
'{contact,skype}',
'"myskypeid"'::jsonb,
false); jsonb_set
--------------------------------------------------------------------------------
{"name": "James", "contact": {"fax": "01987 543210", "phone": "01234 567890"}}
(1 row)
或者，你可以通过指定数组元素的位置来设置其值。位置从 0 开始，因此第二个元素是 1。负值从后往前开始，因此最后一个元素是 -1，倒数第二个元素是 -2。
# SELECT jsonb_set('{"name": "James", "skills": ["design","snowboarding","mechnaicalengineering"]}',
'{skills,2}',
'"mechanical engineering"'::jsonb,
true);
jsonb_set
-----------------------------------------------------------------------------------
{"name": "James", "skills": ["design", "snowboarding", "mechanical engineering"]}
(1 row)
因此可以这样引用该数组中的位置
0 1 2 # positive values
-3 -2 -1 # negative values
["design", "snowboarding", "mechanical engineering"]
对于此数组，-4 或任何较低的值将附加到开头。3 或任何较高的值将附加到结尾。
jsonb_pretty
请注意，jsonb 不保留空格，因此无论你做出多少努力以使对象更容易阅读，最终它都将显示为一个长字符串。jsonb_pretty 将以一种适合阅读的格式显示它。如果我们使用之前的 jsonb 示例，并将其全部包装在一个 jsonb_pretty 函数中
# SELECT jsonb_pretty(jsonb_set('{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb, '{contact,phone}', '"07900 112233"'::jsonb));
jsonb_pretty
---------------------------------
{ +
"name": "James", +
"contact": { +
"fax": "01987 543210", +
"phone": "07900 112233"+
} +
}
(1 row)
阅读起来容易得多。
链接
INSERT ... ON CONFLICT DO NOTHING/UPDATE（“UPSERT”）
9.5 支持“UPSERT”操作。
INSERT 扩展为接受 ON CONFLICT DO UPDATE/IGNORE 子句。此子句指定在发生可能的重复违规时采取的替代操作。
例如，如果我们有一个用户帐户登录的简单表，其中我们希望跟踪用户登录的次数
# SELECT username, logins FROM user_logins; username | logins ----------+-------- James | 4 Lois | 2 (2 rows)
并且我们希望添加 2 个新的登录，通常如果违反了主键（或唯一约束），我们会遇到问题
# INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1);
ERROR: duplicate key value violates unique constraint "users_pkey"
DETAIL: Key (username)=(James) already exists.
与使用公用表表达式的方法不同，新命令没有竞争条件，保证插入或更新（只要没有附带错误）。ON CONFLICT DO UPDATE 安全地保证“插入或更新”语义，没有语句无法对提议插入的每一行执行这两个操作之一的风险（除非存在独立的错误）。与在其他数据库系统中实施的 SQL MERGE 不同，没有强制用户考虑因并发操作导致其 UPSERT 可能会失败的实施或语义限制。简而言之，新的“UPSERT”功能旨在尽可能简单且安全。
新语法的进一步示例
INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1)
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;
现在让我们看看发生了什么
# SELECT username, logins FROM user_logins; username | logins ----------+-------- Lois | 2 Naomi | 1 James | 5 (3 rows)
我们有了 Naomi 的新行，显示她登录过一次，但我们也有 James，他的登录值已根据该语句的 UPDATE 部分增加了一次。UPDATE 语句根据检查的列或唯一约束知道它正在更新哪些行。
当然，在某些情况下，您可能想要向表中插入一个值，但前提是它不存在。
假设我们有一个国家/地区列表，该列表将用于约束其他表中的值
# SELECT * FROM countries; country ----------- Australia Italy Japan UK USA (5 rows)
我们希望添加 2 个国家/地区。如果其中一个或多个已经存在并且违反主键（在此情况下为“country”列），我们会收到错误
# INSERT INTO countries (country) VALUES ('France'),('Japan');
ERROR: duplicate key value violates unique constraint "countries_pkey"
DETAIL: Key (country)=(Japan) already exists.
但现在我们可以告诉它冲突没关系，在那些情况下什么也不做
# INSERT INTO countries (country) VALUES ('France'),('Japan') ON CONFLICT DO NOTHING;
INSERT 0 1
现在我们的表中应该只有一个其他国家/地区
# SELECT * FROM countries; country ----------- Australia Italy Japan UK USA France (6 rows)
如果有其他也受到唯一约束的列，则可以指定我们要对其应用条件的约束或列，以便在另一列上产生“合法”冲突时产生错误。
因此，为了澄清我们的意图，我们可以将上一个示例写成
# INSERT INTO countries (country) VALUES ('France'),('Japan') ON CONFLICT ON CONSTRAINT countries_pkey DO NOTHING;
或
# INSERT INTO countries (country) VALUES ('France'),('Japan') ON CONFLICT (country) DO NOTHING;
然而，前者实际上并不完全等同于后者，因为基于“推理”的语法对可能导致基本上相同的约束/业务规则的新名称的架构迁移等问题更加健壮。用户应该更喜欢后者（除非它不可避免，就像排除约束一样）。
请注意，不支持提供多组冲突/更新条件，因此如果指定了特定冲突，但发生了其他冲突，它将像正常插入一样产生冲突错误。
链接
Youtube 视频 - PostgreSQL 9.5 的 Upsert 特性详解
pg_rewind
pg_rewind 允许高效地将旧的主服务器与新主服务器同步，而无需执行完全基础备份。通过查看预写日志来查看修改了哪些页面，然后只复制这些页面来实现此操作。
在本例中，我们有一个主服务器（在端口 5432 上运行），一个从属服务器正在订阅它（在端口 5433 上运行）
# SELECT * FROM pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 11609 usesysid | 16384 usename | rep_user application_name | standby1 client_addr | 127.0.0.1 client_hostname | client_port | 38434 backend_start | 2015-03-29 00:11:55.243319+00 backend_xmin | state | streaming sent_location | 0/C81BB40 write_location | 0/C81BB40 flush_location | 0/C81BB40 replay_location | 0/C81BB40 sync_priority | 0 sync_state | async
现在我们来提升从属服务器
$ pg_ctl promote -D standby1 server promoting
然后在此实例上进行一些更改
$ psql -p 5531 postgres # CREATE TABLE x (content text); CREATE TABLE # INSERT INTO x SELECT 'test' FROM generate_series(1,1000); INSERT 0 1000
现在我们来停止旧主服务器，并使用 pg_rewind 重新同步它
$ pg_ctl stop -D primary waiting for server to shut down.... done server stopped $ pg_rewind -D primary --source-server='host=localhost port=5531' -P connected to remote server The servers diverged at WAL position 0/C81BB40 on timeline 1. Rewinding from last common checkpoint at 0/2000060 on timeline 1 reading source file list reading target file list reading WAL in target Need to copy 274 MB (total source directory size is 290 MB) 281142/281142 kB (100%) copied creating backup label and updating control file Done!
然后进行一些更改，使其订阅新主服务器
$ cd primary
$ mv recovery.{done,conf}
$ vi recovery.conf # edited to set host info to point to port 5531 in this case
$ vi postgresql.conf # as our example instances are running on the same server, we'll just change the port so it doesn't conflict
然后启动新从属服务器（旧主服务器）
$ pg_ctl start -D primary
让我们看看它是否已成功赶上
$ psql -p 5531 postgres # connect to the new primary # SELECT * FROM pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 11837 usesysid | 16384 usename | rep_user application_name | standby1 client_addr | 127.0.0.1 client_hostname | client_port | 49148 backend_start | 2015-03-29 00:22:39.047657+00 backend_xmin | state | streaming sent_location | 0/C8559B0 write_location | 0/C8559B0 flush_location | 0/C8559B0 replay_location | 0/C855978 sync_priority | 0 sync_state | async
然后看看新主服务器的测试数据是否在新从属服务器上
$ psql -p 5530 postgres # connect to the new standby # SELECT COUNT(*) FROM x; count ------- 1000 (1 row)
全部同步。
链接
期待 9.5 – 新增 pg_rewind，用于在故障回退后重新同步主服务器。
其他新特性
ALTER TABLE ... SET LOGGED / UNLOGGED
PostgreSQL 允许创建不写入到预写日志的表，这意味着它们不会被复制或不会在崩溃中保存，但它们也没有其开销，因此很适合不需要常规表的保证的数据。但是，如果你决定不记录的表现在应该复制，或者常规表不应该再记录，你以前不得不创建表的副本并将数据复制过来。但在 9.5 中，你可以使用新命令在记录和不记录之间切换
将不记录的表设置为记录
ALTER TABLE <tablename> SET LOGGED;
将记录的表设置为不记录
ALTER TABLE <tablename> SET UNLOGGED;
例如
# CREATE UNLOGGED TABLE messages (id int PRIMARY KEY, message text);
# SELECT relname,
CASE relpersistence
WHEN 'u' THEN 'unlogged'
WHEN 'p' then 'logged'
ELSE 'unknown' END AS table_type
FROM pg_class
WHERE relname ~ 'messages*';
relname | table_type
---------------+------------
messages | unlogged
messages_pkey | unlogged
(2 rows)
请注意，将不记录的表设置为记录将生成 WAL，其中将包含表中的所有数据，因此这将导致大型表的复制流量激增。现在将它更改为记录的表
# ALTER TABLE messages SET LOGGED;
然后上一个查询的结果现在是
relname | table_type ---------------+------------ messages | logged messages_pkey | logged (2 rows)
SKIP LOCKED
如果你需要从表中选择并保护这些行不被更新，直到事务完成，你可以指定 FOR UPDATE，但是如果某些行被锁定，你可以指定 SKIP LOCKED 来告诉它忽略这些行，只对它可以访问的行执行操作。
例如
在会话 1 中
# BEGIN; BEGIN
# SELECT * FROM colours; id | name ----+------- 1 | red 2 | green 3 | blue (3 rows)
# UPDATE colours SET name = 'scarlet' WHERE name = 'red'; CREATE TABLE
在会话 2 中
# SELECT * FROM colours FOR UPDATE NOWAIT; ERROR: could not obtain lock on row in relation "colours"
# SELECT * FROM colours FOR UPDATE SKIP LOCKED; id | name ----+------- 2 | green 3 | blue (2 rows)
链接
并行 VACUUM
vacuumdb 实用程序现在支持并行作业。这通过使用 -j 选项指定，与使用 pg_dump 或 pg_restore 时一样。这意味着对数据库进行 vacuum 操作会很快完成，尤其是在表跨多个表空间这种情况中。它还会首先对最大的关系进行 vacuum 操作。
例如
vacuumdb -j4 productiondb
这会对名为“productiondb”的数据库进行 vacuum 操作，方法是生成 4 个 vacuum 作业同时运行。
链接
确认时间戳跟踪
名为 track_commit_timestamp 的布尔配置参数现已可用。“on”会启用确认时间戳跟踪。这样就可以看到何时确认了某个事务。（服务器时间的时间戳与时区）
要查询该信息，请使用 pg_xact_commit_timestamp(transaction_id) 函数找出何时插入/更新行，调用 pg_xact_commit_timestamp 函数并传递 xmin 系统列。请注意，你必须设置参数并重启后才会开始跟踪时间戳，因此在这样做之前不会显示时间戳
# SELECT pg_xact_commit_timestamp(xmin), * FROM colours;
pg_xact_commit_timestamp | id | name
--------------------------+----+-------
| 1 | red
| 2 | green
| 3 | blue
(3 rows)
然后编辑 postgresql.conf，使其 track_commit_timestamp = on，然后重启 PostgreSQL
# INSERT INTO colours VALUES ('mauve'),('cyan'),('indigo');
INSERT
# SELECT pg_xact_commit_timestamp(xmin), * FROM colours;
pg_xact_commit_timestamp | id | name
-------------------------------+----+--------
| 1 | red
| 2 | green
| 3 | blue
2015-10-02 11:16:34.678267+01 | 4 | mauve
2015-10-02 11:16:34.678267+01 | 5 | cyan
2015-10-02 11:16:34.678267+01 | 6 | indigo
(6 rows)
你可以看到在启用该参数之前插入的前 3 行没有返回时间戳，所以它仅在启用该参数并重启数据库集群后才开始跟踪插入/更新的时间戳。
还有一个 set-returning 函数 pg_last_committed_xact，它会告诉你最后确认的事务的时间戳
# SELECT * FROM pg_last_committed_xact(); xid | timestamp ------+------------------------------- 2039 | 2015-10-02 11:16:34.678267+01 (1 row)
cluster_name
postgresql.conf 中有一个名为 cluster_name 的新参数。这允许使用字符串在进程列表中标识集群，这在同一台服务器上运行多个集群时特别有用
$ ps -ef | grep checkpointer postgres 12181 12178 0 11:12 ? 00:00:00 postgres: personnel: checkpointer process postgres 12207 12204 0 11:12 ? 00:00:00 postgres: reportsdb: checkpointer process postgres 12233 12230 0 11:12 ? 00:00:00 postgres: management: checkpointer process
排序优化（简要键）
简要键优化有望极大提升 PostgreSQL 中的排序性能，包括用于 CREATE INDEX 的排序。据报道，在某些情况下，对文本列进行 CREATE INDEX 的速度提高了整个数量级（3 倍是一个更典型的提升）。数字排序也支持此优化。
简要键将非常好地补充分组集。报告查询在 PostgreSQL 9.5 中会显著提升速度。
链接
简要键：利用局部性提升 PostgreSQL 的文本排序性能
GiST 仅索引扫描
以前，唯一支持仅索引扫描的索引访问方法是 B-Tree 和 SP-GiST，但 PostgreSQL 9.5 中为 GiST 新增了支持
在此示例中，我们将使用 btree_gist 扩展
# CREATE EXTENSION btree_gist;
我们将设置一个保存会议室预订的简单表
# CREATE TABLE meetings ( id serial primary key, room int, reservation tstzrange);
然后添加一个排斥约束以确保任何一个房间的预订与同一房间的另一个预订不重叠，这会创建一个索引来强制执行此约束
# ALTER TABLE meetings ADD CONSTRAINT meeting_exclusion EXCLUDE USING GIST (room with =, reservation with &&);
我们用大量测试数据填充它
# WITH RECURSIVE ins AS (
SELECT
1 AS room,
'2000-01-01 08:00:00'::timestamptz AS reservation_start,
(ceil(random()*24)*5 || ' minutes')::interval as duration
UNION ALL
SELECT
CASE
WHEN ins.reservation_start > now() THEN ins.room + 1
ELSE ins.room
END AS room,
CASE
WHEN ins.reservation_start > now() THEN '2000-01-01 08:00:00'::timestamptz
ELSE ins.reservation_start + ins.duration
END AS reservation_start,
(ceil(random()*16)*15 || ' minutes')::interval AS duration
FROM ins
WHERE reservation_start < now() + '1 day'::interval
and room <= 200
)
INSERT INTO meetings (room, reservation)
SELECT room, tstzrange(reservation_start, reservation_start + duration) FROM ins
WHERE (reservation_start + duration)::time between '08:00' and '20:00';
运行一次这部分，结果为 640 万行。
如果我们在查询方案中统计 2014 年 5 月份每个会议室发生的会议数量
# EXPLAIN SELECT room, count(*) FROM meetings WHERE reservation && '[2014-05-01,2014-05-31]'::tstzrange GROUP BY room ORDER BY room;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Sort (cost=1294.20..1294.21 rows=2 width=4)
Sort Key: room
-> HashAggregate (cost=1294.17..1294.19 rows=2 width=4)
Group Key: room
-> Index Only Scan using meeting_exclusion on meetings (cost=0.41..1113.98 rows=36038 width=4)
Index Cond: (reservation && '["2014-05-01 00:00:00+01","2014-05-31 00:00:00+01"]'::tstzrange)
在 9.5 之前，我们会得到以下方案
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Sort (cost=28570.15..28570.16 rows=2 width=4)
Sort Key: room
-> HashAggregate (cost=28570.12..28570.14 rows=2 width=4)
Group Key: room
-> Bitmap Heap Scan on meetings (cost=778.49..28386.07 rows=36810 width=4)
Recheck Cond: (reservation && '["2014-05-01 00:00:00+01","2014-05-31 00:00:00+01"]'::tstzrange)
-> Bitmap Index Scan on meeting_exclusion (cost=0.00..769.29 rows=36810 width=0)
Index Cond: (reservation && '["2014-05-01 00:00:00+01","2014-05-31 00:00:00+01"]'::tstzrange)
TABLESAMPLE
TABLESAMPLE 子句允许返回表中随机选择的一组行。有两个抽样算法——SYSTEM 和 BERNOULLI。SYSTEM 会选择随机数据页并返回每一页的所有行，因此很快。BERNOULLI 会返回一组更随机的行，但速度较慢。
示例
SELECT * FROM profiles TABLESAMPLE SYSTEM ( 0.01 );
锁可伸缩性改进
用于 postgres 中大部分操作的锁无法很好地满足许多并发共享获取的需求。尽管只获取共享锁，但很多后端经常会阻止获取排他旋转锁。这种情况通常会导致大量会话的 CPU 使用率达到 100%。现在，共享锁不应该过多地互相干扰。这在有多个套接字的服务器上尤为有用。
改善缓冲区替换的可伸缩性
在 9.5 之前，每次缓冲区替换时都会持有全局锁。现在，热路径无锁，而慢路径使用每个缓冲区的旋转锁。
降低每个后端的内存使用率
在 9.5 之前，每个后端都用包含共享缓冲区中每个页面条目的数组管理页面引用计数。如果共享缓冲区很大，可能会为每个会话添加几十兆字节到几百兆字节。现在，改为使用一个较小的固定大小数组。
CustomScan 接口和联接下推基础架构
CustomScan 接口允许扩展实现扫描关系的自身逻辑，如果其估算成本更合理的话，也可以替换内建的逻辑。相关基础架构还允许替换内建的联接节点，利用外部扫描（如果两个子节点都是同一外部服务器管理）或自定义扫描，其中执行的操作如同在远程/预先联接的关系上进行扫描。
