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</pre>
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</pre>
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 pgclass 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 --以上三行是關鍵，看大小！！！</pre>
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;</pre>
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
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！