PostgreSQL 9.5，带来 UPSERT 等新特性

PostgreSQL 9.5于2016年1月7日正式发布，此版本主要带来了以下几个方面的特性: UPSERT, Row Level Security, and Big Data

1）UPSERT
UPSERT是INSERT, ON CONFLICT UPDATE的简写，简而言之就是：插入数据，正常时写入，主键冲突时更新。以下给个简单的例子：

--创建测试表，并插入一条数据。
CREATE TABLE customer (cust_id INTEGER PRIMARY KEY, name TEXT); INSERT INTO customer VALUES (100, ’Big customer’); --常规INSERT语句，主键冲突，报错。 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
行级安全控制，看代码：

--创建测试表，并开启行级别安全策略
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递增）的表。
看例子：

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)
外部数据包装，
上代码：

--以前得这个写
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'); --9.5可以这么写 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
方便对数据进行抽样。
上代码：

--抽样10%数据
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