PostgreSQL 9.5 发布,带来 UPSERT 等新特性

jopen 6年前

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