👟 🏈 🌓 PostgreSQL 9.4新機能 🕧 👩🏻‍🎨 🈴

良い一日！ PostgreSQL 9.4のリリースはそれほど遠くないので、このバージョンで導入されたいくつかの革新を経験することは有用です。この記事では、（ほとんどの場合、クライアントモデルに関連して）以下を考慮します。

AutoUpdateビューの変更

自動更新可能なビューは 、 DML操作を実行できるビューです。そのような提出の条件：

FROMリスト内の1つのエンティティ（テーブルまたはビュー）のみ
WITH 、 DISTINCT 、 GROUP BY 、 HAVING 、 LIMITおよびOFFSETステートメントの欠如
セットUNION 、 INTERSECTおよびEXCEPTに対する操作の欠如
関数と演算子はフィールドに適用されません

これらのビューは、PostgreSQL 9.3で提供されていました。バージョン9.4では、いくつかの追加が導入されています。それらの1つは、このビューでは関数または演算子が適用されるフィールドが存在できないという事実に関連する制限を削除します（9.3では、そのようなフィールドフィールドが少なくとも1つある場合、ビューは自動更新されなくなります）。 9.4では、残りのフィールドを操作できます。これを例で説明します。

ブックのディレクトリを作成し、その上に簡単な自動更新ビューを作成します。

CREATE TABLE book ( id serial NOT NULL, name text NOT NULL, author text NOT NULL, year integer NOT NULL, CONSTRAINT pk_book_id PRIMARY KEY ( id ), CONSTRAINT uk_book UNIQUE ( name, author ) ); INSERT INTO book ( name, author, year ) VALUES ( ' №1', ' . .', 2010 ), ( ' №2', ' . .', 2011 ), ( ' №3', ' . .', 2012 ); CREATE OR REPLACE VIEW vw_book AS SELECT b.id, b.name, b.author, b.year, ( b.year >= extract ( year FROM current_date ) - 2 ) AS is_new FROM book b;

データの更新：

 --   ,     UPDATE vw_book SET name = ' №10' WHERE name = ' №1'; -- ,  is_new   UPDATE vw_book SET is_new = false WHERE name = ' №3'; -- ,   SELECT * FROM vw_book b; --    ,     SELECT c.column_name, ( is_updatable = 'YES' ) AS is_updatable FROM information_schema.columns c WHERE c.table_name = 'vw_book' ORDER BY ordinal_position;

自動更新の場合、 WITH CHECK OPTIONオプションが利用可能になりました。その意味は、ビューのINSERTおよびUPDATEを使用して、追加または変更されるデータがビューのWHERE句に限定されているかどうかを確認するチェックが追加されることです。たとえば、古い本が選択されているビューがあり、このビューでは新しい本の追加を禁止する必要があります。

 --  ,         CREATE OR REPLACE VIEW vw_book_archive AS SELECT b.id, b.name, b.author, b.year FROM vw_book b WHERE b.is_new = false WITH CHECK OPTION; -- ,     year    INSERT INTO vw_book_archive ( name, author, year ) VALUES ( ' №100', ' . .', 2014 ); --   INSERT INTO vw_book_archive ( name, author, year ) VALUES ( ' №100', ' . .', 2010 );

さらに、 security_barrierオプションでアドバタイズされたビューは、自動更新が停止しなくなりました。 security_barrierをより詳細に検討してください。

本にプロモーションコードを入力するフィールドを追加します。

 --    DELETE FROM book; INSERT INTO book ( id, name, author, year ) VALUES ( 1, ' №1', ' . .', 2010 ), ( 2, ' №2', ' . .', 2011 ), ( 3, ' №3', ' . .', 2012 ); --   ALTER TABLE book ADD COLUMN promotion_code text; --    UPDATE book SET promotion_code = 'CODE_' || id;

本（一部の秘密の本を除く）とそのプロモーションコードを返すプレゼンテーション、およびRAISE NOTICEを通じて本の名前とコードを表示する関数を作成しましょう。

 CREATE OR REPLACE VIEW vw_book_list AS SELECT b.* FROM book b WHERE b.name != ' №1'; CREATE OR REPLACE FUNCTION fn_book_promotion_code ( p_name text, p_code text ) RETURNS boolean AS $BODY$ BEGIN RAISE NOTICE 'Book "%" has code "%"', p_name, p_code; RETURN true; END $BODY$ LANGUAGE plpgsql COST 100;

（ fn_book_promotion_codeを呼び出して本のプロモーションコードを表示することにより）このビューから選択を行い、クエリプランを見てみましょう。

 SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code ( l.name, l.promotion_code ); --   : --  №2 CODE_2 --  №3 CODE_3 --   : -- NOTICE: Book " №2" has code "CODE_2" -- NOTICE: Book " №3" has code "CODE_3" EXPLAIN ANALYZE SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code ( l.name, l.promotion_code ); -- Seq Scan on book b (cost=0.00..1.79 rows=1 width=23) (actual time=0.185..0.217 rows=2 loops=1) Filter: ((name <> ' №1'::text) AND fn_book_promotion_code(name, promotion_code)) Rows Removed by Filter: 1 Planning time: 0.064 ms Execution time: 0.229 ms

行は順番に読み取られ、フィルターでは、ブックの名前の対応が最初にチェックされ、次にfn_book_promotion_code関数が呼び出されることに注意してください。関数を呼び出すコストを削減し、クエリ結果とクエリプランの変更点を確認してみましょう。

 CREATE OR REPLACE FUNCTION fn_book_promotion_code ( p_name text, p_code text ) RETURNS boolean AS $BODY$ BEGIN RAISE NOTICE 'Book "%" has code "%"', p_name, p_code; RETURN true; END $BODY$ LANGUAGE plpgsql COST 0.01; SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code ( l.name, l.promotion_code ); --   : --  №2 CODE_2 --  №3 CODE_3 --      : -- NOTICE: Book " №1" has code "CODE_1" -- NOTICE: Book " №2" has code "CODE_2" -- NOTICE: Book " №3" has code "CODE_3" EXPLAIN ANALYZE SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code ( l.name, l.promotion_code ); -- Seq Scan on book b (cost=0.00..1.04 rows=1 width=23) (actual time=0.215..0.240 rows=2 loops=1) Filter: (fn_book_promotion_code(name, promotion_code) AND (name <> ' №1'::text)) Rows Removed by Filter: 1 Planning time: 0.064 ms Execution time: 0.254 ms

コストは悲惨な関数を呼び出すコストであるため、スケジューラーはまずそれを呼び出し、次に本の名前をチェックします。これにより、秘密の本のコードを見つけることができます。このような状況を防ぐには、vw_book_listビューをsecurity_barrierオプションで作成する必要があります。

 CREATE OR REPLACE VIEW vw_book_list WITH ( security_barrier = true ) AS SELECT b.* FROM book b WHERE b.name != ' №1'; SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code ( l.name, l.promotion_code ); --  : --  №2 CODE_2 --  №3 CODE_3 --  : -- NOTICE: Book " №2" has code "CODE_2" -- NOTICE: Book " №3" has code "CODE_3" EXPLAIN ANALYZE SELECT name, promotion_code FROM vw_book_list l WHERE fn_book_promotion_code ( l.name, l.promotion_code ); -- Subquery Scan on l (cost=0.00..1.06 rows=1 width=23) (actual time=0.078..0.106 rows=2 loops=1) Filter: fn_book_promotion_code(l.name, l.promotion_code) -> Seq Scan on book b (cost=0.00..1.04 rows=2 width=52) (actual time=0.009..0.010 rows=2 loops=1) Filter: (name <> ' №1'::text) Rows Removed by Filter: 1 Planning time: 0.069 ms Execution time: 0.122 ms

これで、スケジューラーは、個別のサブクエリとして関数呼び出しを使用してフィルターを割り当てました。 vw_book_listビューは自動更新のままです。

 UPDATE vw_book_list SET promotion_code = 'CODE_555' WHERE name = ' №2';

マテリアライズドビューの変更

PostgreSQL 9.3で導入されたマテリアライズドビューの主な問題は、排他（ ACCESS EXCLUSIVE ）ロックを使用して、ビューの更新時にビューを使用できないようにすることでした。 9.4では、マテリアライズドビューの更新にEXCLUSIVEロックを使用するREFRESH MATERIALIZED VIEW コマンドにCONCURRENTLYオプションが追加されました。これは、 SELECTクエリ中に発生する競合するACCESS SHAREロックと互換性があります。舞台裏では、このような更新により、このビューの一時バージョンが作成され、比較が行われ、相違点がある場合、対応するINSERTおよびDELETEコマンドが実行されます。このアプローチでは、マテリアライズドビューの1つ以上のフィールドでUNIQUE INDEXを作成する必要があります。実際に見てみましょう：

 --   CREATE TABLE author ( id serial NOT NULL, first_name text NOT NULL, last_name text NOT NULL, CONSTRAINT pk_author_id PRIMARY KEY ( id ), CONSTRAINT uk_author_name UNIQUE ( first_name, last_name ) ); --   CREATE TABLE publication ( id serial NOT NULL, author_id integer NOT NULL, name text NOT NULL, CONSTRAINT pk_publication_id PRIMARY KEY ( id ), CONSTRAINT fk_publication_author_id FOREIGN KEY ( author_id ) REFERENCES author ( id ), CONSTRAINT uk_publication_name UNIQUE ( author_id, name ) ); --   INSERT INTO author ( first_name, last_name ) VALUES ( '', '' ); --  id = 1 INSERT INTO author ( first_name, last_name ) VALUES ( '', '' ); --  id = 2 INSERT INTO publication ( author_id, name ) VALUES ( 1, ' №' || generate_series ( 1, 1000000 ) || ')' ); INSERT INTO publication ( author_id, name ) VALUES ( 2, ' ' ), ( 2, '  ' ); –-      CREATE MATERIALIZED VIEW mvw_publication AS SELECT p.id, a.first_name || ' ' || a.last_name AS author_name, p.name FROM publication p INNER JOIN author a ON a.id = p.author_id; CREATE UNIQUE INDEX idx_mvw_publication_id ON mvw_ publication ( id ); --    REFRESH MATERIALIZED VIEW CONCURRENTLY mvw_publication; –-   ... –-     (  ) SELECT * FROM mvw_publication; –-   SELECT l.mode FROM pg_locks l INNER JOIN pg_class c ON c.oid = l.relation WHERE .relname = 'mvw_publication';

構成パラメーターを設定するALTER SYSTEMコマンド

このコマンドを使用すると、SQLクエリを介してサーバー構成パラメーターを変更できます。セッション（またはトランザクション）に適用されるSETおよびset_configコマンドとは異なり、変更は永続的です。実際、パラメーターは$ PGDATA / postgresql.auto.confファイルに追加されます。このファイルは、postgresql.confの読み取り後に（起動時またはSIGHUPシグナルの受信時に）サーバーによって読み取られます。このコマンドの例：

 --   ALTER SYSTEM SET log_min_duration_statement = '1min'; --   ALTER SYSTEM SET log_min_duration_statement TO '2min'; --   ALTER SYSTEM SET log_min_duration_statement TO DEFAULT;

行戻り関数のWITH ORDINALITYオプション

ネスト解除関数（および文字列のセットを返す他の関数）には、 WITH ORDINALITYオプションが追加され、文字列の順序が表示されます。また、unnestでは、それぞれが個別の列になる複数の配列をリストできます。

 SELECT * FROM unnest ( ARRAY['cat', 'dog', 'mouse'], ARRAY['Tom', 'Jack', 'Lili'] ) WITH ORDINALITY AS t ( cat, dog, mouse ); -- : -- cat Tom 1 -- dog Jack 2 -- mouse Lili 3 --      SELECT *, row_number() OVER () AS i FROM unnest ( ARRAY['cat', 'dog', 'mouse'], ARRAY['Tom', 'Jack', 'Lili'] ) AS t ( cat, dog, mouse );

集計データの新機能

標準の集計関数（ sum 、 avg 、 corrなど）に、順序付きセットおよび仮説シリーズのセットの関数が追加されました。

 --   CREATE TABLE salary AS SELECT ( random ( ) * 100 + 2000 )::int AS value FROM generate_series ( 1, 100 ); --     SELECT mode() WITHIN GROUP ( ORDER BY value DESC ) FROM salary; --  ,       SELECT percentile_disc ( 0.5 ) WITHIN GROUP ( ORDER BY value ) FROM salary; -- ,     (       ) SELECT percentile_cont ( 0.5 ) WITHIN GROUP ( ORDER BY value ) FROM salary; --     ( 1 / n  1) SELECT s.value, cume_dist ( 2026 ) WITHIN GROUP ( ORDER BY value ) FROM salary s GROUP BY s.value;

集約関数に分類されるデータをフィルタリングするためのオプションも登場しました：

 --  ,    2050 SELECT avg ( s.value ) FILTER ( WHERE s.value >= 2050 ) FROM salary s; --    CASE WHEN … SELECT avg ( CASE WHEN s.value >= 2050 THEN s.value ELSE NULL END ) FROM salary s;

GINおよびGiSTインデックスの改善

全文検索に使用されるGINインデックス（複数のフィールド）がより高速になり、スペースを節約します。 GiSTはinetおよびcidrタイプのサポートを導入しました：

 --  IP- CREATE TABLE machine ( ip cidr ); INSERT INTO machine ( ip ) VALUES ( '192.168.1.1'::cidr ), ( '192.168.1.10'::cidr ), ( '192.168.2.11'::cidr ); --     CREATE INDEX idx_machine_ip ON machine USING GiST ( ip inet_ops ); --    192.168.1.0/24 SELECT * FROM machine WHERE ip && '192.168.1.0/24'::cidr;

EXPLAINコマンド出力の改善

EXPLAINコマンドの出力にグループ化がある場合、データがグループ化される列が表示されます。さらに、クエリプランの計画時間が表示されるようになりました。

 EXPLAIN ANALYZE SELECT s.value, count ( * ) FROM salary s GROUP BY s.value HAVING count ( * ) >= 2; -- HashAggregate (cost=2.75..3.60 rows=68 width=4) (actual time=0.045..0.053 rows=26 loops=1) Group Key: value Filter: (count(*) >= 2) Rows Removed by Filter: 42 -> Seq Scan on salary s (cost=0.00..2.00 rows=100 width=4) (actual time=0.007..0.015 rows=100 loops=1) Planning time: 0.042 ms Execution time: 0.082 ms

ビットマップヒープスキャンが使用される場合、一致するブロックの数（正確）と欠落しているブロックの数（ 非可逆 ）：

 --    INSERT INTO salary ( value ) SELECT ( random ( ) * 10000 + 1000 )::int AS value FROM generate_series ( 1, 1000000 ); CREATE INDEX idx_salary_value ON salary ( value ); SET work_mem = '64kB'; EXPLAIN ANALYZE SELECT * FROM salary s WHERE s.value BETWEEN 2010 AND 2020; -- Bitmap Heap Scan on salary s (cost=28.83..2739.72 rows=1210 width=4) (actual time=0.370..17.824 rows=1030 loops=1) Recheck Cond: ((value >= 2010) AND (value <= 2020)) Rows Removed by Index Recheck: 96457 Heap Blocks: exact=486 lossy=429 -> Bitmap Index Scan on idx_salary_value (cost=0.00..28.53 rows=1210 width=0) (actual time=0.286..0.286 rows=1030 loops=1) Index Cond: ((value >= 2010) AND (value <= 2020)) Planning time: 0.098 ms Execution time: 17.920 ms SET work_mem = '32MB'; EXPLAIN ANALYZE SELECT * FROM salary s WHERE s.value BETWEEN 2010 AND 2020; -- Bitmap Heap Scan on salary s (cost=28.83..2739.72 rows=1210 width=4) (actual time=0.283..1.214 rows=1030 loops=1) Recheck Cond: ((value >= 2010) AND (value <= 2020)) Heap Blocks: exact=915 -> Bitmap Index Scan on idx_salary_value (cost=0.00..28.53 rows=1210 width=0) (actual time=0.157..0.157 rows=1030 loops=1) Index Cond: ((value >= 2010) AND (value <= 2020)) Planning time: 0.076 ms Execution time: 1.269 ms

キャッシュを暖める

新しいpg_prewarm拡張機能では、同じ名前の関数を使用できます。これにより、必要なエンティティがキャッシュにロードされます（システムOSまたはPostgreSQL）。これがどのように起こるか見てみましょう。

最初に、拡張機能をインストールし、テストテーブルを作成します。

 CREATE EXTENSION pg_prewarm; CREATE TABLE big AS SELECT array_to_string ( array_agg ( tx ), '' ) || '#' || generate_series ( 1, 10000 ) AS value FROM ( SELECT 'a' || generate_series ( 1, 1000 ) AS x ) t; --     ALTER SYSTEM SET shared_buffers = '256MB';

次に、PostgreSQLサーバーを停止し、OSキャッシュをディスクにフラッシュして、サーバーを再起動します（OSには異なるコマンドがある場合があります）。

 /etc/init.d/postgresql-9.4 stop sync /etc/init.d/postgresql-9.4 start

クエリをテストテーブルに向けて、データの送信元を観察します。

 --   EXPLAIN ( ANALYZE, BUFFERS ) SELECT * FROM big; -- Seq Scan on big (cost=0.00..76047.00 rows=5000000 width=8) (actual time=0.013..448.978 rows=5000000 loops=1) Buffers: shared read=26047 Planning time: 0.081 ms Execution time: 689.083 ms --   EXPLAIN ( ANALYZE, BUFFERS ) SELECT * FROM big; -- Seq Scan on big (cost=0.00..76047.00 rows=5000000 width=8) (actual time=0.044..449.973 rows=5000000 loops=1) Buffers: shared hit=32 read=26015 Planning time: 0.027 ms Execution time: 692.045 ms --   EXPLAIN ( ANALYZE, BUFFERS ) SELECT * FROM big; -- Seq Scan on big (cost=0.00..76047.00 rows=5000000 width=8) (actual time=0.044..449.973 rows=5000000 loops=1) Buffers: shared hit=32 read=26015 Planning time: 0.027 ms Execution time: 692.045 ms

キャッシュにはまだ何もないため、データはディスクから読み取られます（ 共有読み取り ）が、後続のリクエストごとにキャッシュがいっぱいになる（ 共有ヒット ）ことがわかります。

PostgreSQLサーバーを再度停止し、OSキャッシュをリセットして、サーバーを起動します。繰り返しになりますが、 EXPLAINの結果を見てみましょうが、その前に、テストテーブルのデータでキャッシュを埋めます。

 --  26047  SELECT pg_prewarm ( 'big' ); EXPLAIN ( ANALYZE, BUFFERS ) SELECT * FROM big; -- Seq Scan on big (cost=0.00..76047.00 rows=5000000 width=8) (actual time=0.007..407.269 rows=5000000 loops=1) Buffers: shared hit=26047 Planning time: 0.129 ms Execution time: 642.834 ms

すべてのデータはすでにキャッシュにあります。

外部テーブルのトリガー

PostgreSQL 9.3はpostgres_fdw拡張を導入しました。これにより、別のデータベース（いわゆる外部テーブル）にあるテーブルへの読み取り/書き込みアクセスを取得できます。 9.4では、それらのトリガーを作成できるようになりました。

外部テーブルを作成します。

 --   test0 CREATE TABLE city ( country text NOT NULL, name text NOT NULL, CONSTRAINT uk_city_name UNIQUE ( country, name ) ); --   test1 CREATE EXTENSION postgres_fdw; --    CREATE SERVER fdb_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host 'localhost', port '5432', dbname 'test0' ); --   CREATE USER MAPPING FOR PUBLIC SERVER fdb_server OPTIONS ( password 'pwd' ); --    CREATE FOREIGN TABLE fdb_city ( country text, name text ) SERVER fdb_server OPTIONS ( table_name 'city' );

N / Aという名前の都市の追加を禁止するトリガーを作成して確認します。

 --   test1 CREATE OR REPLACE FUNCTION tfn_city_change ( ) RETURNS trigger AS $BODY$ BEGIN IF ( NEW.name = 'N/A' ) THEN RAISE EXCEPTION 'City with name "N/A" not allowed'; END IF; RETURN NEW; END $BODY$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER tr_city_change BEFORE INSERT ON fdb_city FOR EACH ROW EXECUTE PROCEDURE tfn_city_change ( ); --   INSERT INTO fdb_city ( country, name ) VALUES ( 'USA', 'New York' ); --     INSERT INTO fdb_city ( country, name ) VALUES ( 'USA', 'N/A' );

しかし、test0データベースでは、このトリガーは「表示されない」ため、任意の都市名を指定できます。

 --   test0 --   INSERT INTO city ( country, name ) VALUES ( 'Italy', 'N/A' );

jsonおよび新しいjsonbデータ型の変更

間違いなく、新しいjsonbタイプは、PostgreSQL 9.4で最も期待されていた革新でした。構文的には、 jsonと違いはありませんが、データは拡張バイナリ形式で保存されるため、新しいデータの追加は遅くなりますが、処理速度は高くなります。一般的に、 jsonbに JSONを保存する方が適切です。

jsonbには、インデックス（ GIN 、 btree 、およびhash ）を作成する機能があります。 GINには2つの演算子クラスがあります：

標準（jsonb_ops）-@>、？、？＆and？|をサポート
jsonb_path_ops-@>演算子をサポート

jsonb_path_ops演算子クラスは1つの演算子のみをサポートしますが、 jsonb_opsよりも効率的であり、同じデータに対して通常より少ないスペースを占有します。

JSONを操作するための新しい関数には、次のものがあります（それぞれjsonbタイプの場合、jsonb_ *）：

json_array_elements_text-JSON配列をtext型の値のセットに展開します
json_array_elements_text-値をJSON配列に折り畳む
json_object-テキスト配列からJSONオブジェクトを構築する
json_typeof-JSON値タイプ情報

JSONタイプとPostgreSQLタイプには次の関係があります（セマンティクスが異なるPostgreSQLではNULLであるため、 nullに相当するものはありません）：

JSON型	PostgreSQLタイプ
ひも	テキスト
数	数値
ブール値	ブール値

JSONを操作してみましょう。

 --   CREATE TABLE book ( id serial NOT NULL, name text NOT NULL, params jsonb NOT NULL DEFAULT '{}' ); --   INSERT INTO book ( name, params ) SELECT ' #' || tx, ( '{ "pages": ' || 500 + ( tx % 500 ) || CASE tx % 1000 WHEN 0 THEN ', "gold_edition": true' ELSE '' END || ' }' )::jsonb FROM ( SELECT generate_series ( 1, 1000000 ) x ) t; --  ""  SELECT count ( * ) FROM book WHERE params @> '{ "gold_edition": true }'::jsonb; --       SELECT count ( * ) > 0 FROM book WHERE name = ' #1' AND params ? 'pages'; --     SELECT params -> 'pages' FROM book WHERE name = ' #11'; --   "pages" SELECT jsonb_typeof ( params -> 'pages' ) FROM book WHERE name = ' #11'; --   CREATE INDEX idx_book_params ON book USING gin ( params jsonb_path_ops );

演算子@>、？他のいくつかはjsonbタイプに固有です。 jsonbとパフォーマンステストの詳細については、別の記事を参照してください。

サーバーの改善（特に、SSL、VACUUM、backgound_workersに関連するもの）は、この記事では考慮されていません。詳細については、変更ログを参照してください。結論として、PostgreSQLはリレーショナルとNoSQLの両方向に自信を持って動いていることに注意したいと思います。徐々に新しい機能が追加され、将来のバージョンで改善されますが、これは朗報です。

便利なリンク：

ご清聴ありがとうございました。

PostgreSQL 9.4新機能