PostgreSQL 9.4 新功能
本页面包含 PostgreSQL 版本 9.4 功能的概述，包括描述、测试和使用信息，以及指向包含更多信息的博客文章的链接。另请参见 PostgreSQL 9.4 待办事项。
主要新功能
JSONB 二进制 JSON 存储
使用 PostgreSQL 的新 JSONB 数据类型，用户不再需要在关系型和非关系型数据存储之间做出选择：他们可以同时拥有两者。JSONB 支持使用广义倒排索引 (GIN) 进行快速查找和简单的表达式搜索查询。多个新的支持函数使用户能够提取和操作 JSON 数据，其性能与最流行的文档数据库相匹配甚至超过它们。使用 JSONB，表数据可以轻松地与文档数据集成，以创建完全集成的数据库环境。
例如，假设我们有一组包含有关各种书籍的出版数据的 JSON 文档。我们可以像这样将它们存储在 JSON 列中
table booksdata (
title citext not null,
isbn isbn not null primary key,
pubinfo jsonb not null
)
然后，我们可以像这样在 JSONB 上创建一个通用索引
CREATE INDEX ON booksdata USING GIN (pubinfo);
或者
CREATE INDEX ON booksdata USING GIN (pubinfo json_path_ops);
根据您希望运行的操作，有两个不同的版本。标准 GIN 索引支持我们对 JSONB 支持的每种操作。path_ops 索引仅支持搜索路径运算符 "@>"（见下文），但为这些类型的搜索生成更小、更快的索引。
一旦我们有了索引，我们就可以对 JSONB 进行任意路径搜索，这些搜索将是索引搜索，具体取决于计划程序的选择。JSONB 查询示例
计算所有由 "It Books" 出版书籍的数量
SELECT count(*) FROM booksdata
WHERE pubinfo @> '{ "publisher" : "It Books" }';
获取 ISBN，并提取所有书籍的成本作为字符串。
SELECT isbn, pubinfo #>> '{"cost"}'
FROM booksdata;
给我所有同时包含 "publisher" 和 "cost" 键的书籍的数量
SELECT count(*) FROM booksdata WHERE pubinfo ?& array['publisher','cost'];
JSON 操作可以与标准 PostgreSQL 聚合和其他查询操作相结合。这使我们能够提供比非关系型数据库更多的功能。例如
给我所有来自 "It Books" 的书籍的平均成本
SELECT avg((pubinfo #>> '{"cost"}')::NUMERIC)
FROM booksdata
WHERE pubinfo @> '{ "publisher" : "It Books" }';
返回出版商名称作为字符串，以及按每个出版商名称分组的所有书籍的平均成本
SELECT pubinfo #>> '{"publisher"}' as publisher,
avg((pubinfo #>> '{"cost"}')::NUMERIC)
FROM booksdata
GROUP BY 1
ORDER BY publisher;
此外，JSONB 是可排序的。它以一种合理的方式排序，首先按键排序，然后按值排序，作为它们的原始 JSON 类型（整数、数字、布尔值和文本）。这允许比较 JSONB 值，甚至进行分页。例如，此查询从所有 "It Books" 标题中提取完整的 pubinfo，对它们进行排序，并限制为 25 个
SELECT pubinfo
FROM booksdata
WHERE pubinfo @> '{ "publisher" : "It Books" }'
ORDER BY pubinfo LIMIT 25 OFFSET 0;
此查询获取所有 "It Books" 标题的 pubinfo，然后按出版日期的 JSON 对它们进行排序。由于 JSON 没有内部日期类型，您需要小心使用 Unix 格式的日期
SELECT pubinfo
FROM booksdata
WHERE pubinfo @> '{ "publisher" : "It Books" }'
ORDER BY pubinfo #> '{"published_on"}'
LIMIT 25 OFFSET 0
链接
复制改进
复制槽
待办事项：博客文章，wal_keep_segments 等的冗余性
复制槽允许备用服务器向主服务器或上游级联备用服务器提供有关它们在预写日志中达到的点的信息。即使备用服务器处于离线或断开连接状态，此信息也对主服务器可用。这消除了对 wal_keep_segments 的需要，后者要求管理员估计需要保留多少个额外的 WAL 文件以确保在复制延迟过大时能够向备用服务器提供文件。
以下是一个示例
在主服务器上，将 max_replication_slots 设置为允许备用服务器注册到槽中
max_replication_slots = 1
wal_level 也需要至少设置为 "archive"，但由于此示例将使用热备用，因此我们将将其设置为 "hot_standby"
wal_level = hot_standby
重新启动主服务器以使这些设置生效，然后连接到主服务器并创建一个复制槽
SELECT * FROM pg_create_physical_replication_slot('standby_replication_slot');
备用服务器随后可以通过在 recovery.conf 中指定 primary_slotname 参数来使用此复制槽
primary_slotname = 'standby_replication_slot'
在主服务器上，您可以看到它正在被使用
postgres=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
--------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------
standby_replication_slot | | physical | | | t | | | 0/3000420
(1 row)
现在，主服务器将保留 WAL，直到所有备用服务器都报告它们已超过该点为止。但是请注意，如果备用服务器脱机，并且未删除复制槽，WAL 将在主服务器上累积。因此，如果需要停用备用服务器，应将其槽删除，如下所示
# SELECT pg_drop_replication_slot('standby_replication_slot');
pg_drop_replication_slot
--------------------------
(1 row)
否则，请确保有足够的磁盘容量来保留主服务器上的 WAL，直到备用服务器返回。
将来，逻辑复制也将利用复制槽。
变更集流式传输/逻辑解码
在 9.4 中，变更集流式传输（也称为 "逻辑解码"）提供了一个新的 API 用于读取、过滤和操作 PostgreSQL 复制流。此接口是新复制工具的基础，例如支持创建多主 PostgreSQL 集群的双向复制。
待办事项：博客文章，解释它是为哪些东西奠定基础
性能改进
GIN 索引现在更快，更小
待办事项：博客文章，基准测试结果
pg_prewarm
当数据库实例重新启动时，其共享缓冲区将再次清空，这意味着所有查询最初都必须从磁盘直接读取数据。pg_prewarm 可以将关系数据加载回缓冲区，以再次 "预热" 缓冲区。这意味着那些原本需要逐位加载表部分的查询，现在可以在共享缓冲区中获得准备好的数据。
待办事项：示例
其他新功能
ALTER SYSTEM
通常，集群范围的设置需要在 postgresql.conf 中进行编辑，但现在可以通过 ALTER SYSTEM 命令进行更改。
示例
postgres=# ALTER SYSTEM SET log_min_duration_statement = '5s';
这实际上并没有更改 postgresql.conf。而是将设置写入一个名为 postgresql.auto.conf 的文件。该文件**始终**最后读取，因此它将始终覆盖 postgresql.conf 中的任何设置。将其设置为 DEFAULT 会从 postgresql.auto.conf 中删除该行
postgres=# ALTER SYSTEM SET log_min_duration_statement = DEFAULT;
以这种方式进行更改的一个优势是，设置更有可能正确，因为在添加它们之前会对其进行验证
postgres=# ALTER SYSTEM SET wal_level = 'like the legend of the phoenix'; ERROR: invalid value for parameter "wal_level": "like the legend of the phoenix" HINT: Available values: minimal, archive, hot_standby, logical.
如果它是在 postgresql.conf 中设置的，而有人试图重新启动集群，它将失败。当然，永远不要手动编辑 postgresql.auto.conf。
REFRESH MATERIALIZED VIEW CONCURRENTLY
在 PostgreSQL 9.4 之前，刷新物化视图意味着锁定整个表，从而阻止任何对该表的查询，如果刷新需要很长时间才能获取排他锁（因为它等待使用该表的查询完成），那么它反过来会阻碍后续的查询。现在可以使用 CONCURRENTLY 关键字来缓解这种情况
postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY mv_data;
但是，需要在物化视图上存在一个唯一索引。它不是锁定物化视图，而是创建一个临时更新版本，比较两个版本，然后对物化视图应用 INSERT 和 DELETE 以应用差异。这意味着查询在更新物化视图时仍然可以使用它。
与非并发形式不同，元组不会被冻结，并且由于上述 DELETE 会留下死元组，因此需要进行 VACUUM。
可更新视图改进
在 PostgreSQL 9.3 中引入了自动可更新视图，允许以与普通表相同的方式更新简单视图中的数据，而无需定义触发器或规则。在 PostgreSQL 9.4 中，此功能已得到增强，使其适用于更广泛的视图类别，并允许对新数据进行验证以符合视图定义。
支持混合可更新和不可更新列的视图
以前，只有当视图的所有列都是可更新的（即，对基础关系的列的简单引用）时，视图才是自动可更新的。任何不可更新列的存在，例如表达式、字面量、函数调用或子查询，都会阻止自动更新。现在，即使视图包含一些不可更新列，它也可能是自动可更新的，前提是INSERT或者UPDATE命令不会尝试为任何不可更新列分配新值。
这允许具有计算列的视图支持自动更新
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name TEXT NOT NULL,
quantity INT,
reserved INT DEFAULT 0);
CREATE VIEW products_view AS
SELECT product_id,
product_name,
quantity,
(quantity - reserved) AS available
FROM products
WHERE quantity IS NOT NULL;
postgres=# INSERT INTO products_view (product_name, quantity) VALUES
('Budget laptop', 100),
('Premium laptop', 10);
INSERT 0 2
postgres=# SELECT * FROM products;
product_id | product_name | quantity | reserved
------------+----------------+----------+----------
1 | Budget laptop | 100 | 0
2 | Premium laptop | 10 | 0
(2 rows)
在此视图中，available 列是不可更新的，但所有其他列都可以更新
postgres=# UPDATE products_view SET quantity = quantity - 10 WHERE product_id = 1; UPDATE 1 postgres=# UPDATE products_view SET available = available - 10 WHERE product_id = 1; ERROR: cannot update column "available" of view "products_view" DETAIL: View columns that are not columns of their base relation are not updatable.
链接
WITH CHECK OPTION
默认情况下，当INSERT或者UPDATE命令修改视图中的数据时，不会根据视图的WHERE子句检查数据。因此，新数据可能在视图中不可见。例如，使用上面的视图，将产品的数量设置为 NULL 将导致它从视图中消失。为了防止这种情况，在定义视图时指定WITH CHECK OPTION
CREATE VIEW products_view AS
SELECT product_id,
product_name,
quantity,
(quantity - reserved) AS available
FROM products
WHERE quantity IS NOT NULL
WITH CHECK OPTION;
现在，任何以不匹配视图定义的方式插入或更新数据的尝试都将被拒绝
postgres=# UPDATE products_view SET quantity = NULL WHERE product_id = 1; ERROR: new row violates WITH CHECK OPTION for view "products_view" DETAIL: Failing row contains (1, Budget laptop, null, 0).
链接
可更新安全屏障视图
以前，使用 security_barrier 选项标记视图会阻止它自动可更新。在 PostgreSQL 9.4 中，此限制已删除。
这允许使用视图来实现行级和列级安全。例如
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name TEXT NOT NULL,
department TEXT,
salary MONEY);
CREATE VIEW sales_employees WITH (security_barrier = true) AS
SELECT employee_id,
employee_name,
department
FROM employees
WHERE department = 'Sales'
WITH CHECK OPTION;
REVOKE ALL ON employees FROM bob;
GRANT SELECT, INSERT, UPDATE, DELETE ON sales_employees TO bob;
这允许用户 bob 读取和更新任何销售员工的详细信息，但阻止访问工资信息和来自其他部门的任何员工。此外，检查选项可以防止 bob 将员工转移到其他部门。
链接
WITH ORDINALITY
返回集合的函数最常用于查询的 FROM 子句中，现在如果在后面添加 WITH ORDINALITY，则会添加一个包含有序数字序列的列。
例如
postgres=# SELECT * FROM json_object_keys('{"mobile": 4234234232, "email": "[email protected]", "address": "1 Street Lane"}'::json) WITH ORDINALITY;
json_object_keys | ordinality
------------------+------------
mobile | 1
email | 2
address | 3
(3 rows)
以下是一个不太简单的示例。假设您有一些类似这样的时间序列数据
ts │ speed │ temperature
────────────────────────┼─────────────────────────────────┼─────────────────────────────────
2014-10-11 12:00:00-07 │ {11.1,11.1,11.3,11.4,12.2,11.2} │ {38.1,38.0,38.5,38.7,39.1,39.7}
2014-10-11 12:01:00-07 │ {11.1,10.5,10.1,10.1,10.2,10.3} │ {39.5,39.2,39.2,39.0,38.9,38.7}
2014-10-11 12:02:00-07 │ {10.3,10.1,9.9,9.8,9.7,9.7} │ {38.6,38.6,38.3,38.2,38.2,38.1}
将数组扩展为可以进行计算的列的值，如下所示
select o.ts, u.* from observations o, unnest(speed,temperature) with ordinality as u(speed,temperature,ord);
这将为您提供
ts │ speed │ temperature │ ord ────────────────────────┼───────┼─────────────┼───── 2014-10-11 12:00:00-07 │ 11.1 │ 38.1 │ 1 2014-10-11 12:00:00-07 │ 11.1 │ 38.0 │ 2 2014-10-11 12:00:00-07 │ 11.3 │ 38.5 │ 3 2014-10-11 12:00:00-07 │ 11.4 │ 38.7 │ 4 2014-10-11 12:00:00-07 │ 12.2 │ 39.1 │ 5 2014-10-11 12:00:00-07 │ 11.2 │ 39.7 │ 6 2014-10-11 12:01:00-07 │ 11.1 │ 39.5 │ 1 2014-10-11 12:01:00-07 │ 10.5 │ 39.2 │ 2 2014-10-11 12:01:00-07 │ 10.1 │ 39.2 │ 3 2014-10-11 12:01:00-07 │ 10.1 │ 39.0 │ 4 2014-10-11 12:01:00-07 │ 10.2 │ 38.9 │ 5 2014-10-11 12:01:00-07 │ 10.3 │ 38.7 │ 6 2014-10-11 12:02:00-07 │ 10.3 │ 38.6 │ 1 2014-10-11 12:02:00-07 │ 10.1 │ 38.6 │ 2 2014-10-11 12:02:00-07 │ 9.9 │ 38.3 │ 3 2014-10-11 12:02:00-07 │ 9.8 │ 38.2 │ 4 2014-10-11 12:02:00-07 │ 9.7 │ 38.2 │ 5 2014-10-11 12:02:00-07 │ 9.7 │ 38.1 │ 6
聚合功能
有序集聚合
现在支持有序集聚合，它提供有关列的顺序敏感信息。一个例子是众数平均数
postgres=# SELECT mode() WITHIN GROUP (ORDER BY eye_colour) FROM population; mode ------- brown (1 row)
或者获取列在指定百分比（例如 20%）的值
postgres=# SELECT percentile_disc(0.2) WITHIN GROUP (ORDER BY age) FROM population;
percentile_disc
-----------------
31
(1 row)
聚合 FILTER 子句
该FILTERclause 是 SQL 标准语法，用于控制查询中哪些行传递给聚合函数。
SELECT agg_fn(val) FILTER (WHERE condition) FROM ...
只有条件计算结果为真的行才会传递给聚合函数。例如
SELECT array_agg(i) FILTER (WHERE i % 2 = 0) AS twos,
array_agg(i) FILTER (WHERE i % 3 = 0) AS threes,
array_agg(i) FILTER (WHERE i % 5 = 0) AS fives,
array_agg(i) FILTER (WHERE i % 7 = 0) AS sevens
FROM generate_series(1, 20) AS g(i);
twos | threes | fives | sevens
-----------------------------+------------------+--------------+--------
{2,4,6,8,10,12,14,16,18,20} | {3,6,9,12,15,18} | {5,10,15,20} | {7,14}
(1 row)
链接
移动聚合支持
如果聚合函数在带有移动帧起始的窗口中使用，称为*移动聚合模式*，现在可以定义额外的聚合支持函数来优化性能，使其无需在每次帧起始移动时都重新计算整个聚合。
例如，考虑一个查询，计算从当前行开始并包括其后 10 行的值的总和。
SUM(x) OVER (ORDER BY y ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING)
在以前版本的 PostgreSQL 中，这将涉及为每个输出行重新计算整个总和（每行 10 次加法）。在 PostgreSQL 9.4 中，现在可以通过从前一行的聚合中减去第一个添加的值并添加新值（每行 1 次加法和 1 次减法）来计算每行的聚合。这可以极大地提高此类查询的性能，尤其是在大型窗口帧中。
许多内置聚合函数支持这种优化，用户定义的聚合也可以通过在适当的情况下定义合适的*反向转换函数*来支持它。
链接
state_data_size 参数
此参数可以传递给CREATE AGGREGATE以允许聚合函数提供其状态数据的尺寸（以字节为单位）的估计值。PostgreSQL 在计划分组聚合查询时会使用此估计值。例如，查询
SELECT department, AVG(salary) FROM employees GROUP BY department;
可以使用散列聚合执行，同时计算所有聚合组
QUERY PLAN ----------------------------- HashAggregate Group Key: department -> Seq Scan on employees
或通过对数据进行排序，然后依次计算每个组的聚合
QUERY PLAN
-----------------------------------
GroupAggregate
Group Key: department
-> Sort
Sort Key: department
-> Seq Scan on employees
使用散列聚合的计划可能更有效，但也可能使用更多内存，因此只有在估计它适合于时才会考虑它。work_mem，这将取决于聚合状态数据的估计尺寸。
链接
NUMERIC 聚合的性能
在 PostgreSQL 9.4 中，使用NUMERIC类型在内部的各种聚合函数的性能得到了提升。这包括以下聚合
- SUM()和AVG()超过bigint和numeric值。
- STDDEV_POP(), STDDEV_SAMP(), STDDEV(), VAR_POP(), VAR_SAMP()和VARIANCE()超过smallint, int, bigint和numeric值。
