TOC 本講演の内容 PostgreSQL 開発とリリースの概要 PostgreSQL 9.5 新機能の検証報告講演者について高塚遙 SRA OSS, Inc. 日本支社にて PostgreSQL サポート各種クラスタ構築技術支援マイグレーション技術支援などを担当 Copyright 20

Size: px

Start display at page:

Download "TOC 本講演の内容 PostgreSQL 開発とリリースの概要 PostgreSQL 9.5 新機能の検証報告講演者について高塚遙 SRA OSS, Inc. 日本支社にて PostgreSQL サポート各種クラスタ構築技術支援マイグレーション技術支援などを担当 Copyright 20"

えつみちとく
5 years ago
Views:

~ 徹底検証報告 ~ 次期メジャーバージョン PostgreSQL 9.5 の実力 SRA OSS, Inc.

PostgreSQL 開発とリリース (1) 開発体制 - 特定のオーナー企業を持たない方式支援企業

Harada Hitoshi Ishii Tatsuo Kaigai Kouhei Suzuki Koichi :

Hagander Bruce Momjian Dave Page 開発コミュニティ etc... www.

3 PostgreSQL 開発とリリース (1) 開発体制 - 特定のオーナー企業を持たない方式支援企業 Contributors Major Contributors Core Team Fujii Masao Harada Hitoshi Ishii Tatsuo Kaigai Kouhei Suzuki Koichi : 30~40 名 Josh Berkus Tom Lane Peter Eisentraut Magnus Hagander Bruce Momjian Dave Page 開発コミュニティ etc... 記載より Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 3

4 PostgreSQL 開発とリリース (2) 4,500,000 4,000,000 リリースの歴史 PostgreSQL ソースコード行数?! 3,500,000 3,000,000 2,500,000 2,000,000 ビットマップスキャン更新の高速化レプリケーション Windows 64bit 多 CPU スケールマテビュー更新ビュー更新 FDW データチェックサム行数 1,500,000 1,000, ,000 PITR Windows 4 CPU スケールウィンドウ関数再帰 SQL 同期レプリケーション外部テーブル高速 JSON レプリケーションスロット ? Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 4

5 PostgreSQL 開発とリリース (3) いつリリースされる? 9.5.x は仕様確定済み 9.5 alpha2 がリリース済みリリース予定は? 2014/6/ /7/1 master 5/15 9.4beta1 リリース 12/ リリース 8/6 9.4.x 9.5.x 9.5alpha2 リリース Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 5

6 PostgreSQL 9.5 の新機能主要な新機能行単位セキュリティ BRIN インデックストランザクションの巻き戻し pg_rewind コマンド挿入または更新に対応 INSERT... ON CONFLICT... 外部テーブルのインポート / 継承集計構文に対応 GROUPING SETS CUBE ROLLUP JSONB 型むけの関数演算子の拡充 Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 6

7 行単位セキュリティ (1) Row Level Security (RLS) と呼ばれるポリシー定義に基づきテーブルの行アクセスを制御 ROW LEVEL SECURITY 属性 : enable / disable BYPASSRLS 属性 : 有 / 無 DB ロール ( ユーザ ) ポリシー定義に基づきアクセス制限テーブルポリシー定義 force で所有者スーパーユーザに強制アクセス制御を実現設定パラメータ (postgresql.conf) row_security = on / off / force Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 7

8 行単位セキュリティ (2) GRANT ALL on t1 to user1; ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; 従来のアクセス権限は全て可に設定しテーブルに対する RLS を有効に設定 CREATE POLICY policy1 ON t1 FOR SELECT TO user1 USING (lv <= 2); テーブル :t1 id dat lv 101 データ1 ( 機密 ) データデータ3 ( 公開 ) データデータ5 ( 機密 ) 5 CREATE POLICY policy2 ON t1 FOR INSERT TO user1 WITH CHECK (lv = 1); USING 指定条件が暗黙に適用 db1=> SELECT * FROM t1 WHERE id <= 103; id dat lv データデータ3 ( 公開 ) 0 (2 rows) CHECK 指定条件を満たさないのでエラーになる db1=> INSERT INTO t1 VALUES (106, ' データ 6', 0); ERROR: new row violates row level security policy for "t1" Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 8

dt >= '2015-10-01'; インデックスからスキャンするとき読み飛ばせるブロックが分かる本 SELECT ではブロック

9 BRIN インデックス (1) 新しいインデックス方式 brin が追加されたいくつかのブロック (= ページ ) の塊ごとに最大値最小値を保持する最大 : ' ' 最小 : ' ' SELECT * FROM t2 WHERE dt >= ' '; インデックスからスキャンするとき読み飛ばせるブロックが分かる本 SELECT ではブロックは読み飛ばし可能! Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 9

10 BRIN インデックス (2) Btree インデックスとの比較作成時間とインデックスサイズを 10 万行程度のデータで実測作成時間 [ms] 所要時間 6 分の 1 単調増加データランダム順データ 2500 インデックス作成時オプション pages_per_range にて何ブロックをインデックス上の 1 項目とするかを指定 128 はデフォルト 10 万件データの整数カラムにてインデックスをサイズを比較 0 B-Tree BRIN 2000 インデックス作成時間を比較データ量 [kb] サイズ 40 分の 1 0 B-Tree BRIN(pages_per_range=128) Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 10

11 BRIN インデックス (2) Btree インデックスとの比較 - 検索所要時間 1 から 100 万の昇順整数データを持つテーブルで条件検索 WHERE c = WHERE c BETWEEN AND WHERE c BETWEEN AND Btree BRIN ともビットマップインデックススキャンの実行プランになる BRIN が若干速い検索時間 [m s ] 件抽出は Btree が最も速いインデックスなし B-Tree BRIN 0 between and = between and 検索条件ただし行の物理配置がランダムなカラムだと BRIN は効果的に働かない Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 11

12 トランザクション巻き戻し - pg_rewind (1) 昇格時点より先に進んだデータベースクラスタの問題サーバ 1 プライマリサーバ 2 スタンバイサーバ 3 スタンバイ LSN (xlog location) 0/ / / 非同期ストリーミングレプリケーションサーバ1 障害停止プライマリサーバ2 昇格新プライマリ 0/ 時点でタイムライン切り替えサーバ 3 スタンバイ昇格時点よりも先に進んだデータは新たなプライマリのスタンバイとして使用することとができない 0/ / / 再度ベースバックアップ取得をするしかなかった Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 12

13 トランザクション巻き戻し - pg_rewind (2) pg_rewind コマンドプライマリとしたいサーバのタイムライン切り替え時点までローカル側のデータベースクラスタのトランザクションを巻き戻す [server1]$ pg_ctl start [server1]$ pg_ctl stop [server1]$ pg_rewind -D $PGDATA \ クラッシュ終了後なら起動停止をしておく --source-server='host=server2 port=5432 user=postgres' [server1]$ vi $PGDATA/recovery.conf [server1]$ pg_ctl start [server3]$ pg_ctl stop [server3]$ pg_rewind -D $PGDATA \ --source-server='host=server2 port=5432 user=postgres' [server3]$ vi $PGDATA/recovery.conf [server3]$ pg_ctl start 必要な設定パラメータ (postgresql.conf) full_page_writes = on ( デフォルト ) wal_log_hints = on ( デフォルトは off) Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 13

14 挿入または更新 - INSERT... ON CONFLICT (1) よくある処理パターン新データ [id=101 hn='taro' ml='taro@example.org'] につき id = 101 のレコードが無いなら INSERT id = 101 のレコードが在るなら UPDATE 以下のように記述できる INSERT INTO t3 VALUES (101, 'TARO', 'taro@example.org') ON CONFLICT (id) DO UPDATE SET hn = EXCLUDED.hn, ml = EXCLUDED.ml; 従来はアプリ側で場合分けを実装あるいは以下のような WITH 構文による技巧により実現 WITH val AS (SELECT ((101, 'TARO', 'taro@example.org')::t3).*), upd AS (UPDATE t3 SET hn = val.hn, ml='taro@example.org' FROM val RETURNING t3.id) INSERT INTO t3 SELECT * FROM val WHERE id NOT IN (SELECT id FROM upd); Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 14

15 挿入または更新 - INSERT... ON CONFLICT (2) 3 つの方式を pgbench で比較 INSERT...ON CONFLICT WITH...INSERT PL/pgSQL プロシージャ最も好成績挿入または更新各方式の性能比較同時 500 接続で挿入または更新を実行 pgbench -f test1.sql -c 500 -t 100 -n db1 まず UPDATE して 0 件なら INSERT ユニーク制約違反なら ROLLBACK してリトライする方式デッドロックが多発するため TPS=1.0 程度 tps INSERT...ON CONFLICT WITH...INSERT PL/pgSQL プロシージャ Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 15

16 外部テーブルのインポート IMPORT FOREIGN SCHEMA 命令が追加外部テーブルをスキーマ単位で一括登録できる db1=> CREATE SCHEMA remote; db1=> IMPORT FOREIGN SCHEMA apscm FROM SERVER foreign_server INTO remote; db1=> \de remote.* List of relations Schema Name Type Owner remote customers foreign table apuser remote products foreign table apuser... 従来はテーブル毎に CREATE FOREIGN TABLE が必要であった各カラムの対応するデータ型をデフォルト通りでなく個別に設定するなら引き続き CREATE FOREIGN TABLE を使うことになる Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 16

17 外部テーブルの継承外部テーブルとローカルテーブルで継承が可能にパーティショニングと分散格納の組み合わせが可能になる従来からテーブル継承はテーブルパーティショニング実現に利用されていたサーバ 1 親テーブル pt1 並列問い合わせ機能は未だ実装されていないことに注意サーバ 2 子テーブル ct2 サーバ 3 子テーブル ct3 サーバ 4 子テーブル ct4 コマンド例 : (s1db1 と s2db1 は各々リモートテーブル用のスキーマ ) s2db1=> CREATE TABLE ct2 () INHERITS (s1db1.pt1); s1db1=> ALTER TABLE s2db1.ct2 INHERIT pt1; Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 17

18 集計構文以下の集計構文に対応 GROUP BY GROUPING SETS (... ) GROUP BY ROLLUP (... ) GROUP BY CUBE (... ) 各カテゴリ組み合わせ毎の小計を一度に出力させることができる SQL99 に規定されており他 DBMS の実装と大きな違いはないテーブル :t_sales customer product num Tom egg 10 Tom apple 20 Jhon egg 15 Jhon egg 5 CUBE (c1, c2,..) : 指定カラムの全組み合わせ ROLLUP (c1, c2,...) : 指定カラム順に包含する組み合わせ db1=> SELECT customer,product,sum(num) FROM t_sales GROUP BY GROUPING SETS ((customer), (customer, product), ()); customer product sum John egg 20 John 20 Tom apple 20 Tom egg 10 Tom (6 rows) Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 18

19 JSONB 型むけ関数演算子の拡充 (1) JSONB データの部分更新が容易になる項目が無ければ追加あれば右辺値で上書きする db1=> SELECT '{"name": "John", "age": 25}'::jsonb '{"age": 26}'::jsonb;?column? {"age": 26, "name": "John"} JSONB のオブジェクト要素配列要素を削除 db1=> SELECT '{"name": "John", "age": 25}'::jsonb - 'age';?column? {"name": "John"} db1=> SELECT '["apple", "orange", "melon"]'::jsonb - 1;?column? ["apple", "melon"] Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 19

20 JSONB 型むけ関数演算子の拡充 (2) 深い位置にある要素の削除 db1=> SELECT '{"name": "John", "contact": {"phone": "0123", #- '{contact, }'::text[];?column? {"name": "John", "contact": {"phone": "0123"}} 指定パスに値があるかに応じた動作第 4 引数 true ならあれば値の更新無ければ挿入第 4 引数 false ならあれば値の更新無ければ何もしない db1=> SELECT jsonb_set('{"name": "James", "contact": {"phone": " ", "fax": " "}}'::jsonb, '{contact,skype}', '"myskypeid"'::jsonb, true); Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 20

21 まとめ PostgreSQL 開発の概要についてお話いたしました PostgreSQL 9.5 の新機能について紹介いたしました各機能の詳細については 9.5 alpha2 版のマニュアル ( 英語 ) を参照いただくことができます SRA OSS, Inc. Web サイトの技術情報ページで弊社による検証レポートも今月中には公開予定ですご清聴ありがとうございましたご質問を承ります Copyright 2015 SRA OSS, Inc. Japan All rights reserved. 21

PostgreSQL 9.4 評価検証報告 SRA OSS, Inc. 日本支社高塚遙 :55 ~ 16:30 PostgreSQL 9.4 最新情報セミナー Copyright 2014 SRA OSS, Inc. Japan All rights reserved. 1

PostgreSQL 9.4 評価検証報告 SRA OSS, Inc. 日本支社高塚遙 2014-09-11 15:55 ~ 16:30 PostgreSQL 9.4 最新情報セミナー Copyright 2014 SRA OSS, Inc. Japan All rights reserved. 1 はじめに本講演の構成 Part 1 性能アップってどのくらいですか Part 2 この新機能は何ですか

TOC 本講演の内容 PostgreSQL 開発とリリースの概要 PostgreSQL 9.5 新機能の検証報告 講演者について 高塚遙 SRA OSS, Inc. 日本支社にて PostgreSQL サポート 各種クラスタ構築技術支援 マイグレーション技術支援 などを担当 Copyright 20

TOC 本講演の内容 PostgreSQL 開発とリリースの概要 PostgreSQL 9.5 新機能の検証報告講演者について高塚遙 SRA OSS, Inc. 日本支社にて PostgreSQL サポート各種クラスタ構築技術支援マイグレーション技術支援などを担当 Copyright 20