发布于 2016-01-08 07:54:26 | 449 次阅读 | 评论: 0 | 来源: 网友投递

这里有新鲜出炉的PostgreSQL指南,程序狗速度看过来!

PostgreSQL关系型数据库管理系统

PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES,现在已经更名为PostgreSQL,版本 4.2为基础的对象关系型数据库管理系统(ORDBMS)。


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



历史版本 :
PostgreSQL 六版齐发,修复 3 个安全漏洞
PostgreSQL 10 正式版发布,显著的性能提升!
PostgreSQL 10 首个候选版本 RC1 发布,修复部分 bug
PostgreSQL 10 Beta 4 发布,以及全系列更新
PostgreSQL 10 Beta 4 发布,多版本更新
PostgreSQL 10 Beta 3 发布,数据库管理系统
PostgreSQL 10 Beta 3 发布,以及对所有支持版本的更新
PostgreSQL 10 Beta 2 发布,Bug 修复
PostgreSQL 10 首个测试版发布,新增特性一览
PostgreSQL 9.6.3, 9.5.7, 9.4.12, 9.3.17 和 9.2.21 发布
PostgreSQL 10 新增特性
PostgreSQL 五版齐发,修复了超过 75个 bug
最新网友评论  共有(0)条评论 发布评论 返回顶部

Copyright © 2007-2017 PHPERZ.COM All Rights Reserved   冀ICP备14009818号  版权声明  广告服务