KOF2015 PostgreSQL 9.5
-
Upload
toshi-harada -
Category
Technology
-
view
1.058 -
download
0
Transcript of KOF2015 PostgreSQL 9.5
3
自己紹介 ( こんなの作ってた )SELECT data, data + '拾 ' FROM ksj_sample; data | ?column? ------------+---------------- 五百壱拾六 | 五百弐拾六 零 | 壱拾 参万壱百壱 | 参万壱百壱拾壱(3 rows)
SELECT data, data * '拾 ' FROM ksj_sample; data | ?column? ------------+-------------- 五百壱拾六 | 五千壱百六拾 零 | 零 参万壱百壱 | 参拾壱千壱拾(3 rows)
SELECT sum(data) FROM ksj_sample; sum ---------------- 参万六百壱拾七(1 row)SELECT * FROM ksj_sample ORDER BY data; data ------------ 零 五百壱拾六 参万壱百壱(3 rows)
ksj (漢数字型)
漢数字で四則演算や比較演算できる、誰得データ型
4
自己紹介 ( こんなの作ってた )
SELECT * FROM foo WHERE data = 'エバンゲリオン'; id | data ----+------------ 3 | エヴァンゲリヲン(1 row)
SELECT * FROM foo WHERE data = 'センヌリティウス'; id | data ----+------(0 rows)
SELECT * FROM foo WHERE data /= 'センヌリティウス'; id | data ----+------------------ 4 | セリヌンティウス(1 row)
ntext( ゆるい文字型 )
日本語正規化比較ゆるい比較演算子 (/=)
類似度計算で typo を許容する誰得日本語テキスト型と
全文検索用パッチ
5
自己紹介 ( こんなの作ってた )
CREATE FOREIGN TABLE bar3 ( my_name text, my_gender text, follower_name text, follower_gender text) SERVER foo OPTIONS (query '{"query":"START n=node(*) MATCH p=fm<-[]-n<-[]-fm RETURN n.name as my_name, n.gender as my_gender, fm.name as follower_name, fm.gender as follower_gender" }');
SELECT my_name, my_gender, follower_name, follower_gender FROM bar3; my_name | my_gender | follower_name | follower_gender ---------+-----------+---------------+----------------- Akagi | Famale | Hiryu | Famale Nagato | Male | Mutsu | Male Mutsu | Male | Nagato | Male Hiryu | Famale | Soryu | Famale Hiryu | Famale | Akagi | Famale Soryu | Famale | Hiryu | Famale(6 rows)
new4j_fdw
グラフデータベース Neo4j をわざわざ PostgreSQL 経由でSQL を使って検索する誰得な
Foreign Data Wrapper
neo4j_fdw
何故か本家 Wiki にも登録されている・・・w
https://wiki.postgresql.org/wiki/FDW
6
自己紹介 ( こんなの作ってた )
postgres=# LISTEN HB_CL;LISTENpostgres=# NOTIFY HB_SV,'xxxx';;NOTIFYAsynchronous notification "hb_cl" with payload "Invalid data.(xxxx)" received from server process with PID 29520.postgres=# NOTIFY HB_SV,'0123';;NOTIFYAsynchronous notification "hb_cl" with payload "2 Hit / 1 Blow." received from server process with PID 29520.postgres=# NOTIFY HB_SV,'0813';;NOTIFYAsynchronous notification "hb_cl" with payload "4 Hit! Conguratulatoins!, next new game." received from server process with PID 29520.postgres=#
hb_worker
psql などから数当て (Hit & Blow) ができる誰得 Background Worker Process
10
PostgreSQL の概要
MySQL と並ぶ OSS RDBMSライセンスは BSD ライクなもの
高度なクエリにも対応性能面でも商用 DBMS とも遜色なし9.0 以降はレプリケーションも対応
多種多様なデータ型サポート非常に高い拡張性
活発な開発コミュニティ
25
BRIN の特徴 (1)インデックスサイズが非常に小さい
btree
BRIN
0.000 500.000 1,000.000 1,500.000 2,000.000 2,500.000
2,142.094
0.258
timestamp 型 1 億件のインデックスサイズ
インデックスサイズ (MB)
この例では BRIN のサイズは btree のわずか 0.01%
26
BRIN の特徴 (1)インデックス作成時間も短い
サイズの割には処理時間はそこそこかかるが、btree インデックス生成よりはかなり短い
btree
BRIN
0 20 40 60 80 100 120
95.82
22.07
timestamp 型 1 億件のインデックス作成時間
インデックス作成時間 (s)
30
BRIN の特徴 (4)
値の並びと物理配置が一致する例
レコード生成時のタイムスタンプSerial 型
頻繁に更新が発生しない表値の並びと物理配置の相関関係は
pg_stats ビューの correlation を参照する。1あるいは -1 に近ければ相関関係が強い。
0 に近ければ相関関係が弱い。
32
BRIN vs Partitionどちらも大規模テーブルの設計時に検討するもの
でも、目的は異なると考えたほうがいい
BRIN巨大な 1つの表+インデックス
巨大な表に対する範囲検索の性能向上構築・運用は容易
パーティーションキーにより分割された子テーブル子テーブルの TRUNCATE が得意
パーティションキーを含む検索が有利になることもある構築・運用は面倒・・・
35
UPSERT とは何か?
INSERT しようとして制約違反になったら
代わりに UPDATE する機能
PostgreSQL では UPSERT 文ではなくINSERT 文の拡張という形で
UPSERT を実装している
36
INSERT 文の拡張[ WITH [ RECURSIVE ] with_query [, ...] ]INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
( { column_name_index | ( expression_index ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
conflict_target の詳細
DO NOTHING DO UPDATE SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ WHERE condition ]
conflict_action の詳細
40
UPSERT の制約制約がない表へは UPSERT できない
CREATE TABLE test (id int, data text);CREATE TABLE Table "public.test" Column | Type | Modifiers --------+---------+----------- id | integer | data | text |
INSERT INTO test VALUES (1, 'aaa'),(2, 'bbb'),(3, 'ccc');INSERT 0 3TABLE test; id | data ----+------ 1 | aaa 2 | bbb 3 | ccc(3 rows)
INSERT INTO test VALUES (2, 'bbb_i') ON CONFLICT ON CONSTRAINT test_pkey DO UPDATE SET data = 'bbb_u';psql:upsert-01.sql:29: ERROR: constraint "test_pkey" for table "test" does not exist
41
UPSERT の代替手段
9.4 まででも UPSERT 相当を代替することは一応できた。
Common Table Expression(CTE)Trigger
Application で対応
49
RLS の簡単な例こんなロールを用意しておく
Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} user_a | | {} user_b | | {} user_m | | {}
こんなテーブルを用意してデータ投入CREATE TABLE test (id int primary key, name text, body text, acl text[]);
INSERT INTO test VALUES (1, 'Project-A document', 'dummy', ARRAY['A','M']), (2, 'Project-B document', 'dummy', ARRAY['B','M']), (3, 'Project-A black-history', 'dummy', ARRAY['A']), (4, 'Project list', 'dummy', ARRAY['M']) ;
59
その他の 9.5変更点PostgreSQL 9.5 の変更点は非常に多いので限られた時間ではとても説明しきれませんが
その中から 10個ばかり変更点を簡単に紹介します。
PostgreSQL 設定パラメータ稼働統計情報ビューの変更システムカタログの変更
SQL コマンドの追加text/numeric ソート性能改善ハッシュ生成の性能改善
wal圧縮JSON 型の改善
pg_rewindpsql の改善
62
PostgreSQL 設定パラメータチェックポイント間で保持できる WAL ファイルの量の設定パラメータが 9.4 と 9.5 で異なる。要注意。
9.4 まで: checkpoint_segments ( 最大ログファイル数:デフォルト =3)
↓9.5 から: max_wal_size/max_min_size(サイズによる設定 : デフォルト =1GB )
9.4 までの環境で使っていた checkpoint_segments のパラメータは、 9.5 からはどう設定すればいいの?
64
稼働統計情報ビューの変更PostgreSQL は実行中のさまざまな統計情報を収集し、
稼働統計情報ビュー経由や SQL 関数によって統計情報を監視できる。
PostgreSQL 9.5 でも若干変更があった。
ビュー名・関数名 変更内容
pg_stat_ssl ビュー SSL接続に関するビューが追加された。
pg_stat_get_snapshot_timestamp() 関数 統計情報のスナップショット取得時間を返却する関数が追加された。
66
SQL コマンドの追加
PostgreSQL 9.5 では、以下の SQL コマンドの追加や改善が予定されている。
INSERT(UPSERT 機能 )SELECT(GROUP SETS, SKIP LOCKED, TABLESAMPLE)
UPDATE( 複数カラム更新の構文追加 )
ちょっと面白い機能として、カラム名を間違えたときにカラム名を推定して HINT を出す機能が追加されている。
69
text/numeric ソート性能改善
text 型と numeric 型のソート性能が大きく改善された。
今までロケールを意識した比較をしていたけど、バイトオーダーで比較するように修正したっぽい。
Improve the speed of sorting character and numeric fields (Peter Geoghegan, Andrew Gierth, Robert Haas)
72
wal 圧縮機能
更新ログ (Write Ahead Log) を書き出すときに圧縮するかどうかを決める設定値が追加された。
Full Page Write 時に書き出すサイズが小さくなる代わりに圧縮 ( 書き出し時 )/ 伸長 ( リカバリ時など ) 時の
CPU コストが少し増大するので、利用すべきかどうかは環境や案件に合わせて検証が必要そう。
Add configuration parameter wal_compression to control compression of full page images stored in WAL
(Rahila Syed, Michael Paquier)
73
JSON 型の改善PostgreSQL 9.2 からデータ型として
JSON(JSONB) 型が扱えるようになっている。
9.2 から順調に進化。 9.5 では…?
●JSON 型の導入。●2 つの JSON 型構築関数。●JSON 内の値を使った条件検索はできなかった。
●JSON 型関数・演算子の大幅な強化。
●JSON データ型へのパスによるアクセス。
●JSON 内の値を使った条件検索が可能に。
●JSONB 型の導入。●検索の高速化●独自演算子の追加●GIN インデックス対応
9.29.3
9.4
80
PostgreSQL 9.5 の主な改善点Enterpise 用途に向けた改善→ BRIN, Row Level Security
性能改善→ソート性能 , Hash 生成性能 ,
開発者向け機能の改善→UPSERT, 各種クエリ改善 , JSONB 型改善 , psql 改善
データベース運用管理者向けの改善→ wal 圧縮 , pg_rewind