2013年8月16日金曜日

PostgreSQL 9.3 新機能の概要

お疲れ様です。稲垣です。

PostgreSQL 9.3 がこの秋に正式にリリースされる予定です。本ブログの執筆時点では Beta2 がリリースされています。今回は、現時点でリリースが確定している機能をご紹介します。

■PostgreSQL9.3 の新機能一覧

新しい機能や高速化された全ての機能を紹介することは難しい為、これらの機能の内の幾つかをピックアップして後ほど簡単に紹介します。

【追加機能】


【高速化】


【その他】


【互換性と関連する変更】

  • CREATE TABLE が暗黙的に作成されるインデックス、シークエンスのエラーを非表示
  • 管理者のみが commit_delay を設定可能
  • replication_timeout が wal_sender_timeout に名前変更
  • unix_socket_directory 設定が unix_socket_directries に変更(複数形に注意)
  • メモリ内ソートを 9.3 未満の動作で設定している場合、work_mem 設定の見直しが必要
  • WALファイル名が FF で終了

■マテリアライズドビュー

マテリアライズドビューとはテーブルのスナップショットのようなビューです。ビューが作成された時点からデータが更新されません。

データ更新は明示的に指定しなければなりません。PostgreSQL9.3 未満の場合、テーブルのスナップショットで操作したい場合、一時テーブルを作成する必要がありましたが、PostgreSQL9.3 からは一時テーブルではなくマテリアライズドビューを利用すると効率よく操作できます。

一時テーブルとは異なり、REFRESH する事により最新のデータに更新する事が可能です。ただし、差分だけリフレッシュする機能が無く、リフレッシュには排他ロックが必要な事に注意が必要です。

【マテリアライズドビューの作成】

CREATE MATERIALIZED VIEW ビュー名 AS 条件;

【マテリアライズドビューの更新】

REFRESH MATERIALIZED VIEW ビュー名;

【マテリアライズドビューの例】

user1@127 ~=# CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
txt text,
date TIMESTAMP DEFAULT now()
);
CREATE TABLE
時間: 186.190 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 18.538 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 9.469 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 21.099 ms
user1@127 ~=# CREATE MATERIALIZED VIEW my_mate_view AS SELECT * FROM my_table;
SELECT 3
時間: 132.381 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 19.026 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 2.894 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 18.058 ms
user1@127 ~=# SELECT * FROM my_mate_view;
 id | txt |            date            
----+-----+----------------------------
  1 | abc | 2013-07-25 07:39:32.099291
  2 | abc | 2013-07-25 07:39:32.851492
  3 | abc | 2013-07-25 07:39:35.835314
(3 行)

時間: 0.425 ms
user1@127 ~=# SELECT * FROM my_table;
 id | txt |            date            
----+-----+----------------------------
  1 | abc | 2013-07-25 07:39:32.099291
  2 | abc | 2013-07-25 07:39:32.851492
  3 | abc | 2013-07-25 07:39:35.835314
  4 | abc | 2013-07-25 07:41:07.430558
  5 | abc | 2013-07-25 07:41:08.278669
  6 | abc | 2013-07-25 07:41:10.959413
(6 行)

時間: 0.309 ms
user1@127 ~=# REFRESH MATERIALIZED VIEW my_mate_view;
REFRESH MATERIALIZED VIEW
時間: 149.641 ms
user1@127 ~=# SELECT * FROM my_mate_view;
 id | txt |            date            
----+-----+----------------------------
  1 | abc | 2013-07-25 07:39:32.099291
  2 | abc | 2013-07-25 07:39:32.851492
  3 | abc | 2013-07-25 07:39:35.835314
  4 | abc | 2013-07-25 07:41:07.430558
  5 | abc | 2013-07-25 07:41:08.278669
  6 | abc | 2013-07-25 07:41:10.959413
(6 行)

時間: 16.955 ms
user1@127 ~=# 

■更新可能ビュー

商用データベースでは更新可能なビューをサポートしているものがあります。特に MS SQL Server は古くから更新可能ビューをサポートしていました。

PostgreSQL9.3 からビュー定義が簡単な場合、自動的にビューを更新可能にするルール(PostgreSQLのRule)を作成し更新可能なビューが作成できるようになりました。

更新可能なビューを作成するには INSERT、UPDATE、DELETE に対するルールまたはトリガーを定義しなければなりませんでしたが、以下の条件を全て満たす場合、自動的にルールが作成され更新可能になります。

  • FROM 句に 1 つのテーブルまたは更新可能ビューが設定されている
  • WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET がトップレベルに含まれない
  • UNION、INTERSECT、EXCEPT がトップレベルに含まれない
  • 全てのカラムが単純な参照であること(表現、リテラル、関数でない)
  • ビューの SELECT リストにカラムの重複が無い
  • security_barrier プロパティが設定されていない

【更新可能ビューの例】

user1@127 ~=# CREATE TABLE my_table (
user1(# id SERIAL PRIMARY KEY,
user1(# txt text,
user1(# date TIMESTAMP DEFAULT now()
user1(# );
CREATE TABLE
時間: 227.072 ms

user1@127 ~=# CREATE VIEW my_view AS SELECT * FROM my_table WHERE id > 2;
CREATE VIEW
時間: 90.361 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 17.389 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 6.967 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 7.813 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 11.856 ms
user1@127 ~=# INSERT INTO my_table (txt) VALUES ('abc');
INSERT 0 1
時間: 15.031 ms
user1@127 ~=# SELECT * FROM my_table;
 id | txt |            date            
----+-----+----------------------------
  1 | abc | 2013-07-25 06:58:08.747109
  2 | abc | 2013-07-25 06:58:09.811324
  3 | abc | 2013-07-25 06:58:10.442833
  4 | abc | 2013-07-25 06:58:11.026806
  5 | abc | 2013-07-25 06:58:11.611632
(5 行)

時間: 0.351 ms
user1@127 ~=# SELECT * FROM my_view;
 id | txt |            date            
----+-----+----------------------------
  3 | abc | 2013-07-25 06:58:10.442833
  4 | abc | 2013-07-25 06:58:11.026806
  5 | abc | 2013-07-25 06:58:11.611632
(3 行)

時間: 0.523 ms
user1@127 ~=# INSERT INTO my_view (txt) VALUES ('xyz');
INSERT 0 1
時間: 20.490 ms
user1@127 ~=# INSERT INTO my_view (txt) VALUES ('xyz');
INSERT 0 1
時間: 9.702 ms
user1@127 ~=# INSERT INTO my_view (txt) VALUES ('xyz');
INSERT 0 1
時間: 7.500 ms
user1@127 ~=# SELECT * FROM my_view;
 id | txt |            date            
----+-----+----------------------------
  3 | abc | 2013-07-25 06:58:10.442833
  4 | abc | 2013-07-25 06:58:11.026806
  5 | abc | 2013-07-25 06:58:11.611632
  6 | xyz | 2013-07-25 06:58:44.644324
  7 | xyz | 2013-07-25 06:58:45.620243
  8 | xyz | 2013-07-25 06:58:46.188217
(6 行)

時間: 0.414 ms
user1@127 ~=# SELECT * FROM my_table;
 id | txt |            date            
----+-----+----------------------------
  1 | abc | 2013-07-25 06:58:08.747109
  2 | abc | 2013-07-25 06:58:09.811324
  3 | abc | 2013-07-25 06:58:10.442833
  4 | abc | 2013-07-25 06:58:11.026806
  5 | abc | 2013-07-25 06:58:11.611632
  6 | xyz | 2013-07-25 06:58:44.644324
  7 | xyz | 2013-07-25 06:58:45.620243
  8 | xyz | 2013-07-25 06:58:46.188217
(8 行)

時間: 0.307 ms
user1@127 ~=# DELETE FROM my_view WHERE id = 7;
DELETE 1
時間: 14.753 ms
user1@127 ~=# SELECT * FROM my_view;
 id | txt |            date            
----+-----+----------------------------
  3 | abc | 2013-07-25 06:58:10.442833
  4 | abc | 2013-07-25 06:58:11.026806
  5 | abc | 2013-07-25 06:58:11.611632
  6 | xyz | 2013-07-25 06:58:44.644324
  8 | xyz | 2013-07-25 06:58:46.188217
(5 行)

時間: 0.400 ms
user1@127 ~=# UPDATE my_view SET txt = 'ABC';
UPDATE 5
時間: 25.409 ms
user1@127 ~=# SELECT * FROM my_view;
 id | txt |            date            
----+-----+----------------------------
  3 | ABC | 2013-07-25 06:58:10.442833
  4 | ABC | 2013-07-25 06:58:11.026806
  5 | ABC | 2013-07-25 06:58:11.611632
  6 | ABC | 2013-07-25 06:58:44.644324
  8 | ABC | 2013-07-25 06:58:46.188217
(5 行)

時間: 0.371 ms
user1@127 ~=# 

ビューが更新可能かどうかはシステムカタログをクエリすると判別できます。

user1@127 ~=# SELECT table_name, is_insertable_into FROM information_schema.tables  WHERE table_name = 'my_view';
 table_name | is_insertable_into 
------------+--------------------
 my_view    | YES

■再帰ビュー

WITH RECURSIVE を利用した再帰ビューが可能になりました。自己参照するテーブルを再帰的にクエリする場合などには便利でしょう。

【50 以下のフィボナッチ数を表示】

user1@127 ~=# CREATE VIEW fib_up_to_50 AS
WITH RECURSIVE
fib AS (
  SELECT 0 AS a, 1 AS b
  UNION ALL
  SELECT b, a + b FROM fib WHERE b <= 50
)
SELECT a FROM fib;
CREATE VIEW
時間: 0.411 ms
user1@127 ~=# SELECT * FROM fib_up_to_50;
 a  
----
  0
  1
  1
  2
  3
  5
  8
 13
 21
 34
(10 行)

時間: 0.381 ms
user1@127 ~=# 

ビューを繰り返し利用しないのであれば、WITH RECURSIVE のみで次のように利用することもできます。

user1@127 ~=# WITH RECURSIVE
fib AS (
  SELECT 0 AS a, 1 AS b
  UNION ALL
  SELECT b, a + b FROM fib WHERE b <= 50
)                                       
SELECT a FROM fib;
 a  
----
  0
  1
  1
  2
  3
  5
  8
 13
 21
 34
(10 行)

時間: 0.385 ms
user1@127 ~=# 

実用的な例は PostgreSQL マニュアルの WITH クエリのページに記載されています。

7.8. WITH Queries (Common Table Expressions)
http://www.postgresql.org/docs/9.3/static/queries-with.html

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part
WITH RECURSIVE search_graph(id, link, data, depth) AS (
        SELECT g.id, g.link, g.data, 1
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1
        FROM graph g, search_graph sg
        WHERE g.id = sg.link
)
SELECT * FROM search_graph;

■まとめ

SQLite が MMAP を利用するようになり、数倍高速に動作するようになった事をご存知の方も多いと思います。

少し古いベンチマークですが、SysV 共有メモリから POSIX 共有メモリと MMAP の利用する変更が行われていますが、ベンチマーク結果からすると Linux ユーザにはほとんど関係ありません。

PostgreSQL 9.3 - System V shared memory vs mmap
http://marc.info/?l=dragonfly-kernel&m=134751798812787&q=p3
※PDF ファイルとなります。

マニュアルにも記載されている通り、危険な機能ですがカスタムバックグラウンドワーカーがロードできるようになり、カスタマイズされたログを取得、サーバの動作をモニタリング、定期的に実行するタスクを定義するなど、より高度な情報取得や操作を PostgreSQL 対して行う事も可能になります。

今回は紹介しませんでしたが、PostgreSQL 9.3 ではストリーミングレプリケーションをカスケードしている場合の管理がかなり簡易化され扱いやすくなっています。

カスケードしている場合にマスターが故障し、下位のサーバでレプリケーションを再開する場合、アーカイブログやバックアップが必要でしたが 9.3 から必要なくなりました。ストリーミングレプリケーションを利用されている方は特に注目すべき新機能です。

0 件のコメント:

コメントを投稿