PostgreSQL 9.5于2016年1月7日正式发布，此版本主要带来了以下几个方面的特性: UPSERT, Row Level Security, and Big Data 1）UPSERT
UPSERT是INSERT, ON CONFLICT UPDATE的简写，简而言之就是：插入数据，正常时写入，主键冲突时更新。以下给个简单的例子： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE customer (cust_id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customer VALUES (100, ’Big customer’);
INSERT INTO customer VALUES (100, ’Non-paying customer’);
ERROR: duplicate key value violates unique constraint
"customer_pkey"
DETAIL: Key (cust_id)=(100) already exists.
INSERT INTO customer VALUES (100, ’Non-paying customer’)
ON CONFLICT (cust_id) DO UPDATE SET name = EXCLUDED.name;
SELECT * FROM customer;
cust_id | name
100 | Non-paying customer
|
2）Row Level Security
行级安全控制，看代码：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | CREATE TABLE orders (id INTEGER, product TEXT,
entered_by TEXT);
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY orders_control ON orders FOR ALL TO PUBLIC
USING (entered_by = CURRENT_USER);
GRANT ALL ON TABLE orders TO PUBLIC;
CREATE USER emp1;
CREATE USER emp2;
SET SESSION AUTHORIZATION emp1;
INSERT INTO orders VALUES (101, ’fuse’, CURRENT_USER);
SET SESSION AUTHORIZATION emp2;
INSERT INTO orders VALUES (102, ’bolt’, CURRENT_USER);
SET SESSION AUTHORIZATION postgres;
SELECT * FROM orders;
id | product | entered_by
101 | fuse | emp1
102 | bolt | emp2
SET SESSION AUTHORIZATION emp1;
SELECT * FROM orders;
id | product | entered_by
101 | fuse | emp1
SET SESSION AUTHORIZATION emp2;
SELECT * FROM orders;
id | product | entered_by
102 | bolt | emp2
|
3）Big Data
3.1）BRIN Indexing
一种占用空间特别小的索引，适合超大数据量且自然排序（如：id递增）的表。
看例子：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE brin_example AS
SELECT generate_series(1,100000000) AS id;
CREATE INDEX btree_index ON brin_example(id);
CREATE INDEX brin_index ON brin_example USING brin(id);
SELECT relname, pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relname LIKE ’brin_%’ OR relname = ’btree_index’
ORDER BY relname;
relname | pg_size_pretty
brin_example | 3457 MB
btree_index | 2142 MB
brin_index | 104 kB
|
3.2）Faster Sorts
使用了一种叫做“abbreviated keys”的算法，使得对varchar(),text和NUMERIC()几种类型排序更快，查询提速2～12倍，索引创建提速20倍。
3.3）CUBE, ROLLUP and GROUPING SETS 聚集函数类sql更好写了，OLAP更方便。代码例子太长，就不贴了。 3.4) Foreign Data Wrappers (FDWs)
外部数据包装，
上代码：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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');
IMPORT FOREIGN SCHEMA public
FROM SERVER dest_server INTO remote;
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;
|
3.5）TABLESAMPLE
方便对数据进行抽样。
上代码：
1 2 | select * from ts_test tablesample system(10);
|
PostgreSQL越来越强大了。种种迹象来看（BRUCE MOMJIAN说的），PostgreSQL正在朝着多方向发展：
1）Big data大数据：BRIN的支持，Foreign Data Wrappers支持。
2）Data analytics 数据分析：grouping sets, cube, rollup的支持
3）Large servers ：Faster Sorts，Hashing性能改善，多核大内存支持更好，比如：IBM POWER-8, having 24 cores, 192 hardware threads, and 492GB RAM环境下，TPS达到40万。
4）NoSQL：JSONB相关支持，单机性能是MongoDB的好几倍。
以上示例代码来自以下： Gulcin Yildirim: Tablesample In PostgreSQL 9.5 PostgreSQL 9.5 有哪些新特性？ Major Features: Postgres 9.5 | 
