こんにちは、hachi8833です。今回は「Rails開発者のためのPostgreSQLの便利技」のCraig Kerstiens氏の別の記事からの翻訳をお送りいたします。元記事が公開されたのは2016年初頭ですが、今も通用する部分が多くあると思います。
なお、翻訳時点でのPostgreSQLの最新バージョンは9.6.3です。
概要
原著者より許諾を得て翻訳・公開いたします。
- 元記事: My Top 10 Postgres Features and Tips for 2016
- 著者: Craig Kerstiens: PostgreSQLのクラウドスケーリングサービスで知られるCitus Dataのメンバーであり、Herokuにも在籍したことがあります。
PostgreSQLの機能トップ10（2016年版）（翻訳）
この休暇中は、新しい本を見つけたり、新しい言語を学んだり、他のいろんなスキルをブラッシュアップしたりしていました。皆さんのPostgreSQLについての新しい知識や、理想的な使いこなしのためにこの記事がお役に立てばと思います。このトップ10リストでは、必要なときにとても役立つ「PostgreSQLの便利技トップ10」と呼ぶほど多くの便利技を紹介しているわけではありませんが、PostgreSQL Weeklyをご購読いただければPostgreSQL関連の興味深いニュースレターを毎週お届けいたしますので、PostgreSQLに関心がおありの方はぜひどうぞ。
1. CTE（共通テーブル式: Common Table Expression）
CTEを使うと、たとえば再帰的クエリのような離れ技をもっともシンプルな方法で行えます。私はもうCTEなしではやっていけません。CTEは、クエリ実行中の時間内を対象とするビューを表すWITH句としても知られています。これについて考えてみましょう。
CTEを使うと読みやすいクエリを作成できます。クエリが100行にも達するような状況になれば、20行のクエリよりもCTEで書かれた4、5行のクエリの方が初めて読む人にとって間違いなく読みやすく、理解しやすいものになります、SQLを書くのが好きで好きでしょうがない人は少ないながらもいますが、他人の書いたSQLを読むのが好きな人はまずいません。ぜひCTEについての記事をお読みになり、CTEを積極的に使ってみましょう。
2. .psqlrcの設定
.bashrcや.vimrcを設定するように、PostgreSQLでも設定ファイルを使わない手はありません。たとえば次のようなことができます。
\x autoとするとデフォルトで出力を読みやすくフォーマットできる\pset null ¤でnullの表示を変更できる\timing onでクエリの実行時間表示機能をデフォルトでオンにできる- プロンプトを変更できる:
\set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%など - よく実行するクエリに名前をつけて保存できる
例として私の.psqlrcをご紹介いたします。
\set QUIET 1
\pset null '¤'
-- プロンプトのカスタマイズ
\set PROMPT1 '%[%033[1m%][%/] # '
\set PROMPT2 '... # '
-- 各クエリの実行に要した時間を表示
\timing
-- 出力をベストの状態にフォーマット
\x auto
\set VERBOSITY verbose
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\unset QUIET
3. インデックスすべき箇所をpg_stat_statementsで特定
pg_stat_statementsは、単独のデータベースパフォーマンス改善ツールとしてはもっとも有用なものでしょう。create extension pg_stat_statementsでツールをオンにすれば、データベースに対して行われる全クエリを自動で記録します。クエリの頻度や実行時間も同様に記録します。単純な1つのクエリから得た全実行時間を以下のように記録し、そこから最適化が必要な部分を見つけることができます。
SELECT
(total_time / 1000 / 60) as total_minutes,
(total_time/calls) as average_time,
query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 100;
お気付きのとおり、このツールを常にオンにするとパフォーマンス上の代償が多少発生しますが、そのコストはかなり小さい方です。クエリを常に記録するコストと比べて、パフォーマンスを大きく改善できるメリットの方がずっと大きいと言えるでしょう。
詳しくは私の前回の記事「More on Postgres Performance」をご覧ください。
4. ETLで遅くなったらFDW（外部データラッパー）を使う
さまざまなマイクロサービスやアプリを運営していると、その背後にもさまざまな種類のデータベースが増えてきます。そうなったら、何らかのデータウェアハウスを作成してそこに全部ETL（Extract/Transform/Load）したくなるでしょう。しかし何もかも一か所に集約するのは少々やりすぎの場合もあります。
そのような場合、PostgreSQLデータベースから一度何かのデータを取得したら、たまにしか使わないデータはその後外部データラッパー（FDW: foreign data wrapper）でPostgreSQL以外の別のデータベースから取得できれば十分です。可能性としては、クエリ取得元をPostgreSQLからMongoDBやRedisなどあらゆるデータベースにFDWで振り替えられます。
5. 配列とarray_agg
開発中のアプリで配列を使わないことはほぼありません。データベースで同じように配列を使ってはいけない理由もありません。PostgreSQLでは配列もデータ型のひとつなので、1つのカラム内でブログの投稿に直接タグを追加するなど、いくつもの便利なユースケースがあります。
しかし配列をデータ型として使わない場合であっても、クエリの中で配列的なものをまとめてからカンマで区切りたくなることはよくあります。以下のようなクエリを書けば、ユーザーごとのプロジェクトを簡単にカンマ区切りリストにまとめあげることができるでしょう。
SELECT
users.email,
array_to_string(array_agg(projects.name), ',')) as projects
FROM
projects,
tasks,
users
WHERE projects.id = tasks.project_id
AND tasks.due_at > tasks.completed_at
AND tasks.due_at > now()
AND users.id = projects.user_id
GROUP BY
users.email
6. マテリアライズド・ビューの利用は慎重に
マテリアライズド・ビューをあまりよくご存じない方のために説明します。マテリアライズド・ビューはクエリですが、実際にはテーブルとして作成されます。したがって文字どおり「マテリアライズ」（具現化）したクエリであり、あるクエリ（すなわちビュー）のスナップショット版です。マテリアライズド・ビューはPostgreSQLでは長い間待ち望まれていたのですが、最初のバージョンは使い物になりませんでした。トランザクションをロックしていたために、ビューに対する読み出しなどの操作が待ち状態になってしまうことがあったのです。
その後マテリアライズド・ビューはめざましく改善されましたが、マテリアライズド・ビューを手軽にリフレッシュするしくみはまだありません（訳注: 記事執筆時点）。つまり、何らかのスケジューラジョブやcronジョブでマテリアライズド・ビューを定期的にリフレッシュするよう設定しなければなりません。レポート作成アプリやBI（ビジネス・インテリジェンス）アプリを開発しているのであれば、このしくみはどうしても必要になります。しかし、PostgreSQLが自動でビューをリフレッシュしやすくするための設定は、まだ難易度が高いのが現状です。
PostgreSQL 9.3には、前述の読み出し待ち問題が今も存在しています。
7. ウィンドウ関数
ウィンドウ関数は、おそらくSQLの中でも未だに理解が難しいもののひとつです。一言で言うと、ウィンドウ関数はクエリ結果を順に並べ、ある行（row）から何かを計算して次の行を算出します。こうした処理は一般に、手続き的なSQLなしには実行が難しいものです。ウィンドウ関数を使うと、ある値の順に並んでいる各結果に現れるランクのような基本的な結果、あるいはSQLから前月比（month over month growth）を直接算出するなどのより複雑な結果を得ることができます。
8. ピボットテーブルをもっと簡単に使う
PostgreSQLのtablefuncは、ピボットテーブルを計算する方法として頻繁に参照されます。しかし残念なことに使いこなしはかなり難しくなります。しかし実は、生のSQLを使えばもっと基本的な方法でもピボットテーブルを実現できます。PostgreSQL 9.5になったらこの方法はもっとやりやすくなる予定ですが、それまでの間も、次のようにtrue/falseを取る条件を足し上げて合計を簡単に得ることができます。
select date,
sum(case when type = 'OSX' then val end) as osx,
sum(case when type = 'Windows' then val end) as windows,
sum(case when type = 'Linux' then val end) as linux
from daily_visits_per_os
group by date
order by date
limit 4;
このサンプルクエリはDimitri Fontaineの協力を得てDimitriのブログ記事から引用いたしました。
9. PostGIS
惜しいことに、私はPostGISについては経験豊富ではありません。PostGISは、選択可能なGIS（地理情報システム: Geographic Information System）データベースの中では間違いなく最良と言えます。PostGISは、標準のPostgreSQLで使う場合でもすべてのメリットを得られるので、非常に強力です。最近のPostgreSQLで利用できるGiSTという空間インデックスはその良い例で、PostGISから高パフォーマンスを引き出すことができます。
地理空間データ関連のプロジェクトに従事していて、earth_distance拡張をもっと簡単に使いたいのであれば、PostGISを使いましょう。
10. JSONB
JSONBについてはあらかた議論は尽くされているので、PostgreSQL 9.2以降JSON機能が目玉機能の一つとなってからはリストから外していました。JSON機能にはいろいろと宣伝が過剰だった部分がありましたが、JSONBによってPostgreSQLにおける初期の過剰な宣伝内容が満たされ、ドキュメントデータベースとして真の競争力を付け始めています。JSONBは、改良されたライブラリの利用によってひたすら強力になり続けており、リリースのたびに関数が強化されています。
JSONで何かしたい方、あるいは他のドキュメントデータベースを使っていてJSONBに着目していなかった方は、JSONBの真の力を発揮するためにぜひGINおよびGiSTインデックスを使いましょう。
2016年の見通し
今後数年の間に、PostgreSQL 9.5と9.6では引き続き改良と多くの機能の導入が行われるでしょう。PostgreSQLにまだ存在しないけれど欲しい機能がいろいろと皆さまにもおありだと思います。@craigkerstiensまでお知らせください。