この連休の読書にオススメの一冊「SQLパフォーマンス詳解」（割引コードあり）

最近、久しぶりにPostgreSQLのクエリチューニングをしていたのですが、その過程で「この本はぜひもっと多くの人に読んでもらいたい」と改めて思い出した一冊がありました。

それは、「SQLパフォーマンス詳解（原題：SQL Performance Explained）」という本です。

• SQLパフォーマンス詳解
  http://sql-performance-explained.jp/

パフォーマンスチューニング、特にクエリチューニングについて説明する場合、その前提となる知識は広範なものになります。

そのため、自分が頑張って説明するよりも、優れたエキスパートのまとめたコンテンツを活用させてもらう方が、質・量ともに優れたインプットにしていただけるのではないか、と思うのです。

また、この「SQLパフォーマンス詳解」は非常に良い本であるにも関わらず、一般の出版社から出ているわけではないため、それほど積極的にプロモーションされているわけではなく、日本語版についても、（残念ながら）一般的な書籍ほど話題になることが無いように思います。

そういった理由により、本エントリではこの本について皆さんに知っていただくべくご紹介するとともに、著者のMarkus Winand氏から日本の読者の皆さんに「最大で半額」となる割引コードを提供いただけることになりましたので、その使い方についてご紹介したいと思います。

ゴールデンウィーク直前ですが、ぜひ連休中に読む一冊に加えていただければと思います。データベースのパフォーマンスについて、網羅的かつ本質的な理解が深まること、間違いのない一冊です。

■著者のMarkus Winand氏について

著者のMarkus Winand氏は、PostgreSQLを始めとするRDBMSのチューニングのエキスパート／コンサルタントとして有名な方で、「Use the Index, Luke!」というブログでお馴染みです。

JSONで1:Nのデータ構造をカジュアルに生成してみる

先日、第6回 PostgreSQLアンカンファレンスを開催したのですが、その際、Kuwata氏の以下のプレゼンを聴く機会がありました。

• Nippondanji氏に怒られても仕方ない、配列型とJSON型の使い方 (in PostgreSQL)
  http://kwatch.houkagoteatime.net/blog/2015/12/10/pg-array-and-json-types/

一言で言うと、PostgreSQLのJSONを使って



というようなデータ構造を生成したいのだがPostgreSQLでは実現が難しい、という内容でした。

その時は、「ふむふむ」と聞いていたのですが、最近、JSONの機能に興味を持っていたこともあり、「どうにか望むデータ構造をどうにか実現できないだろうか？」ということが気になっていました。その後、いくつか調査してみた結果、わりと簡単に実現できることが分かりましたので、本エントリではその方法を紹介します。

実現したいことは、

• 複数のテーブルをJOINして1:Nの構造を生成すること
• Nの部分にはJSONを使うこと
• JSONのキー名を任意に設定できること

です。

PostgreSQLでFizzBuzzを書く

先日、久しぶりにネットでFizzBuzzネタを目にしたので、PostgreSQLでFizzBuzzする方法をご紹介しようと思います。

今回は小ネタエントリです。

■FizzBuzzとは

詳細はWikipediaを参照してください。

• http://ja.wikipedia.org/wiki/Fizz_Buzz

要は、1から100まで表示して、3で割り切れる時にFizz、5で割り切れる時にBuzz、3でも5でも割り切れる時にFizzBuzzと表示する、というプログラムを書くことです。

■1から100まで数字を表示する

まず、PostgreSQLで1から100までの数字を表示してみます。

PostgreSQLで連番を表示するには、generate_series()関数を使います。

snaga=> select * from generate_series(1,100);
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
(snip)
              95
              96
              97
              98
              99
             100
(100 rows)

snaga=>

アクセス統計情報を使ってデータベースへのアクセスポリシーを自動生成する

アクセス統計情報を使ってアクセスポリシーを自動生成する PostgreSQLでは、テーブルへのアクセス権限をGRANT/REVOKE文を使って設定することが可能です。

この設定をきちんと行うことによってセキュリティのレベルを高めることができますが、粒度の細かな設定が必要となるため、手間がかかるのも現実です。

今回は、このテーブルへのアクセス権限の設定（アクセスポリシーの作成）を、アクセス統計情報を用いることで（半）自動化してみます。

■アクセス統計情報とは

PostgreSQLには、他のRDBMS製品と同様、アクセス統計情報を取得する仕組みがあります。

• 統計情報コレクタ
  http://www.postgresql.jp/document/9.2/html/monitoring-stats.html

アクセス統計情報とは、どのテーブルにシーケンシャルスキャンを何回実行したか、インデックススキャンを何回実行したか、あるいは何ブロックの読み取ったか、といった情報のことで、この情報を使うことによってデータベースにおけるアクセスの特徴やパターンを把握することができます。

もちろん、パフォーマンスチューニングの時には、この情報を活用することになります。

PL/pgSQLデバッガを使ってみよう

PostgreSQLのPL/pgSQLはプログラムロジックをデータベース側で実行させる非常に強力な機能です。今回は、PL/pgSQLとそのデバッガについてご紹介します。

■PostgreSQLの「PL/pgSQL」とは

PostgreSQLのPL/pgSQLは、データベース側にプログラムのロジックを埋め込むための仕組みで、通常のSQL（DDL、DML等）に制御のための構文が追加されてたような言語仕様になっています。

PL/pgSQL - SQL手続き言語
http://www.postgresql.jp/document/9.0/html/plpgsql.html

私自身、このPL/pgSQLをよく使うのですが、PL/pgSQLのひとつの難点は、ロジックが複雑になってくるとデバッグが難しい、ということでした。

そのため、伝統的なデバッグの方法、いわゆる「printfデバッグ」に頼ることになります。PL/pgSQLの場合、printfの代わりに（ログを出力する）RAISE NOTICEを使います。

エラーとメッセージ
http://www.postgresql.jp/document/9.0/html/plpgsql-errors-and-messages.html

「printfデバッグ」はどこでも使えて非常に便利なのですが、手間がかかるのが難点です。

■PL/pgSQLデバッガ by EnterpriseDB

PostgreSQLサポート企業の一社であるEnterpriseDB社は、PostgreSQLを独自に拡張したり、便利なツール類を開発して、そのコンサルティングやサポートを提供していますが、そのEnterpriseDB社の提供するツールの中に「PL/pgSQLデバッガ」があります。

【9.3新機能チェック】マテリアライズドビューを試してみる

昨日、PostgreSQLの次期リリースである9.3のソースコードに、マテリアライズドビューのコードが追加されました。

pgsql: Add a materialized view relations.
http://www.postgresql.org/message-id/[email protected]

PostgreSQLの開発者Wikiによると、マテリアライズドビューはもっとも要望の多かった機能のようです。

Materialized Views - PostgreSQL wiki
http://wiki.postgresql.org/wiki/Materialized_Views

今回は、このマテリアライズドビューがどのようなものなのか、そしてどのように使えるのかを見てみます。

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

「マテリアライズドビュー」とは、特に集約や集計系の処理をする際に使われる機能で、ビューから取得できるデータの実体を持つ（materialized）ビューです。

通常、ビューというのは「見え方を定義する」だけですので、ビューに対する参照処理を行うと、その都度、元のテーブルに対してSQLの参照処理が行われることになります。

テーブルパーティショニングツール「pg_part」を使ってみる

PostgreSQL Advent Calendar 2012（全部俺）のDay 24です。

前回のエントリでは、PostgreSQLのテーブルパーティショニングの基本的なしくみとその使い方を解説してきました。

前回解説した通り、PostgreSQLのパーティショニングの機能は「理屈としては」確かに動くのですが、実際にはそのためにいろいろなコマンドを実行したりしなければならず、なかなか手間がかかるのも事実です。

■テーブルパーティションを操作するpg_partパッケージ

テーブルパーティショニングは、特に分析系の処理をしている時には便利なのですが、PostgreSQLの場合、作成や管理に結構手間がかかるため、なかなか手を出せない、という方もいるのではないでしょうか。（私も以前はそうでした）

そのため、パーティション操作のための処理を一括して実施してくれる関数群を提供するpg_partパッケージを作成しました。

uptimejp/pg_part
https://github.com/uptimejp/pg_part

今回は、このpg_partパッケージに含まれるSQL関数の使い方を紹介します。

テーブルパーティショニングを使って実現するパフォーマンス向上

PostgreSQL Advent Calendar 2012（全部俺）のDay 23です。

今回は、PostgreSQLにおけるテーブルパーティショニングの機能を取り上げます。

BigDataやデータ分析といったキーワードが多く聞かれるようになってきましたが、PostgreSQLでも大きなデータに対する集計処理のパフォーマンスを向上させるための機能が提供されています。

■「テーブルパーティショニング」

RDBMSにおける「テーブルパーティショニング」と呼ばれる機能には、ディスクへのアクセスを「局所化させる」、または「分散させる」ために提供されている機能です。これらの機能を使うことによって、大規模なデータを対象とした集計系の処理のパフォーマンスを向上させることができます。

RDBMSにおけるパーティションの種類には大きく分けて3つの種類があります。（正確には、2つとそのコンビネーションですが）

・レンジパーティショニング

レンジパーティショニングというのは、連続的な値（と連続的な意味）を持つカラム（日付など）をパーティションキー（分割のキー）として指定することで、テーブルスキャンなどのアクセスを「局所化」させることを目的としたパーティショニングです。「垂直方向のパーティショニング」と呼ばれることもあります。

pgTAPを使ってPostgreSQL上でデータベースの単体テストを行う

PostgreSQL Advent Calendar 2012（全部俺）のDay 21です。

PostgreSQLはその拡張性の高さが大きな特徴となっており、「プロシージャ言語」、いわゆる「PL」として、一般的なSQLやPL/pgSQLだけではなく、PerlやPython、RubyやV8なども使うことができます。

これらのPLを使うと、自分の馴染んだ言語、特に広く一般的に使われているLLで簡単にロジックを書き、これをデータベース内で実行することができるようになります。このことが、最近PostgreSQLがアプリケーション開発プラットフォームとして注目を集めてきている大きな理由の一つでしょう。

一方で、ロジックを実装するということは、そのロジックが正しく動くことを確認するためのテストを行わなければなりません。

というわけで、今回はPostgreSQL上で開発を行う場合にユニットテストに使えるツール「pgTAP」を紹介します。

■単体テストツール「pgTAP」とは

pgTAPは、David E. Wheeler氏によって開発されているPostgreSQL用の単体テストツールです。

pgTAP: Unit Testing for PostgreSQL
http://pgtap.org/

単体テストを実行するのに必要なさまざまなSQL関数を提供しており、テストスクリプト内でこれらのSQL関数を使うことで、スキーマの構造やプロシージャ（ユーザ定義関数）のロジックの正しさなどをテストすることができます。

次期バージョンの9.3で実装された更新可能ビューを試してみる

PostgreSQL Advent Calendar 2012（全部俺）のDay 20です。

先日、ネット上でも少し話題になっていましたが、開発中のPostgreSQLの次バージョン（9.3）に「更新可能ビュー」をサポートするコードがコミットされました。

pgsql: Support automatically-updatable views.
http://archives.postgresql.org/pgsql-committers/2012-12/msg00154.php

今回はこの更新可能ビューについて、その制約なども含めてどのようなものなのかを見てみたいと思います。

なお、今回は開発中のバージョンで試しますので、試してみたい方はPostgreSQLのGitレポジトリからソースコードを取得して自身でビルドしてください。

Working with Git - PostgreSQL wiki
http://wiki.postgresql.org/wiki/Working_with_Git

■「更新可能ビュー」とは

「更新可能ビュー」とはどういった機能でしょうか。

RDBMSにおけるビューのしくみを理解している方は、それを思い浮かべていただければ分かるかと思いますが、ビューの定義が「十分に簡単」である場合、ビューに対するINSERTやUPDATE、DELETE処理は、元テーブルに対する更新処理に書き換えることが可能になるはずです。

この「ビューに対する更新処理を、元テーブルに対する更新処理へ自動的に書き換える」という機能が、「automatically updatable、自動的に更新できる」ビューと言われる機能になります。