2014年11月26日水曜日

PostgreSQL 9.4 新機能の概要(2)

SIOS "OSSよろず"ブログ出張所は、新たにオープンしました「サイオス OSSポータルサイト」に移管されます。
最新の情報はこちらをご確認ください。

間もなくのリリースが予定されているPostgreSQLの新バージョン9.4より、前回は PostgreSQL9.4 の目玉となる新機能を紹介しました。今回はSQLに関連する新機能を紹介します。

リリース直前のPostgreSQL9.4、前回は目玉となる新機能を紹介しました。今回はSQLに関連する新機能を紹介します。

PostgreSQL 9.4新機能の一覧

重要な機能追加

  • レプリケーションスロット:レプリケーション元にレプリケートした位置を保存する機能
  • 論理デコーディング: レプリケーションデータを論理的な値として扱う機能

パフォーマンス改善

  • GINインデックス:小く&速く
  • pg_prewarm:事前ウォームアップ
  • JSONB型の追加:JSONデータをハッシュとして取り扱うデータ型

その他の変更

  • ALTER SYSTEM:SQLからサーバー設定を変更
  • REFRESH MATERIALIZED VIEW CONCURRENTLY:マテリアライズドビュー更新の改善
  • 更新可能ビュー改善:利用可能なクラス(テーブル)を追加
  • WITH CHECK OPTION:更新可能ビューで参照不可となる挿入/更新の制限
  • 更新可能なセキュリティバリアービュー:security_barrier = true も更新可能ビューに
  • WITH ORDINALITY:関数の戻り値に連番を付与

集合機能

  • オーダーセット集合:順序を考慮した集合機能
  • FILTER:集合関数のパラメーターをフィルター
  • 移動集合:前の集合関数の結果を再利用
  • state_data_size パラメーター:CREATE AGGRATEに渡すパラメーター
  • NUMERICの集合:NUMERIC型の集合関数の性能改善

ALTER SYSTEM

PostgreSQLの設定はpostgresql.confに記載するしか方法がありませんでした。PostgreSQL 9.4からpostgresql.confに記載する設定をSQL文で動的に変更可能になりました。

書式

ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

利用例

ALTER SYSTEM SET authentication_timeout = 10;

REFRESH MATERIALIZED VIEW CONCURRENTLY

マテリアライズドビューを更新(REFRESH)すると全体をロックし、クエリをブロックする問題がありました。一つ以上のユニークインデックスを持つマテリアライズドビューにCONCURRENTLYオプションを利用すると緩和されます。緩和されるにはコラム名とすべての行が含まれていなければなりません。

書式

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

利用例

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_data;

更新可能ビュー改善

PostgreSQL 9.3から導入された更新可能ビューの条件が緩和されました。PostgreSQL 9.4からはより多くのクラス(テーブル)とビュー定義による新しいデータのバリデーションに対応しました。

更新可能、更新不可能コラムの共存

利用例

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name TEXT NOT NULL,
  quantity INT,
  reserved INT DEFAULT 0);

CREATE VIEW products_view AS
 SELECT product_id,
        product_name,
        quantity,
        (quantity - reserved) AS available
   FROM products
  WHERE quantity IS NOT NULL;

WITH CHECK OPTIONのサポート

利用例

CREATE VIEW products_view AS
 SELECT product_id,
        product_name,
        quantity,
        (quantity - reserved) AS available
   FROM products
  WHERE quantity IS NOT NULL
   WITH CHECK OPTION;

更新可能なsecurity barrierビュー

利用例

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name TEXT NOT NULL,
 department TEXT,
 salary MONEY);

CREATE VIEW sales_employees WITH (security_barrier = true) AS
 SELECT employee_id,
        employee_name,
        department
   FROM employees
  WHERE department = 'Sales'
   WITH CHECK OPTION;

REVOKE ALL ON employees FROM bob;
GRANT SELECT, INSERT, UPDATE, DELETE ON sales_employees TO bob;

WITH ORDINALITY

セットを返す関数はFROM句で利用されることが多いです。FROM句で返した場合、順序がわかる方が便利であることが多くあります。WITH ORDINALITYを利用すると順序を付けて結果を返すことができます。

利用例

 postgres=# SELECT * FROM json_object_keys('{"mobile": 4234234232, "email": "x@me.com", "address": "1 Street Lane"}'::json) WITH ORDINALITY;
  json_object_keys | ordinality 
 ------------------+------------
  mobile           |          1
  email            |          2
  address          |          3
 (3 rows)

集合機能の強化

PostgreSQL 9.4は集合機能が強化されています。

オーダードセット集合

順序を考慮した集合を利用できます。集合関数とORDER BYを利用します。

利用例

 postgres=# SELECT mode() WITHIN GROUP (ORDER BY eye_colour) FROM population;
  mode  
 -------
  brown
 (1 row)

 postgres=# SELECT percentile_disc(0.2) WITHIN GROUP (ORDER BY age) FROM population;
  percentile_disc 
 -----------------
               31
 (1 row)

集合FILTER句

FILTER句はSQL標準の機能で、集合関数に与えるパラメーターを制御します。

利用例

SELECT array_agg(i) FILTER (WHERE i % 2 = 0) AS twos,
       array_agg(i) FILTER (WHERE i % 3 = 0) AS threes,
       array_agg(i) FILTER (WHERE i % 5 = 0) AS fives,
       array_agg(i) FILTER (WHERE i % 7 = 0) AS sevens
  FROM generate_series(1, 20) AS g(i);

            twos             |      threes      |    fives     | sevens
-----------------------------+------------------+--------------+--------
 {2,4,6,8,10,12,14,16,18,20} | {3,6,9,12,15,18} | {5,10,15,20} | {7,14}
(1 row)

移動集合

移動集合モード(moving-aggregate mode)と呼ばれる機能があります。

利用例

  SUM(x) OVER (ORDER BY y ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING)

PostgreSQL 9.4より前のバージョンでは、このクエリは出力それぞれにすべてのSUMを再計算する必要がありました。PostgreSQL 9.4からは以前の計算結果を利用するため、高速に計算できます。多くの集合関数がこの最適化に対応しています。

state_data_sizeパラメーター

PostgreSQL 9.4からsate_data_sizeと呼ばれるパラメーターがCREATE AGGREGATEで利用可能になりました。

書式

CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = sort_operator ]
)

CREATE AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]
                        ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , INITCOND = initial_condition ]
    [ , HYPOTHETICAL ]
)

これらのパラメータを利用するとオプティマイザがより効率的なクエリプランを作成できるようになります。

NUMERIC型データと集合関数

NUMERIC型のデータを集合関数に利用した場合の性能が改善しました。

- SUM(), AGV() : bigintとnumeric
- STDDEV_POP(), STDDEV_SAMP(), STDDEV(), VAR_POP(), VAR_SAMP(), VARIANCE() : smallint, int, bigintとnumeric

■ 参考URL

https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.4
http://www.postgresql.org/docs/9.4/static/sql-altersystem.html
http://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html
http://www.postgresql.org/docs/9.4/static/sql-createview.html
http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-AGGREGATES
http://www.postgresql.org/docs/9.4/static/sql-createaggregate.html
http://www.postgresql.org/docs/9.4/static/xaggr.html#XAGGR-MOVING-AGGREGATES

0 件のコメント:

コメントを投稿