タグ検索するならPostgreSQLで決まり！

Computer, Database 12月 13, 2013 #PostgreSQL
(Last Updated On: 2018年8月13日)

PostgreSQL Advent Calender 2013、13日目のエントリです。

表題の通り「タグ検索するならPostgreSQLで決まり！」です。

追記：JSONの場合はPostgreSQLのJSONB型を利用してタグ検索を行うを参照

RDBはタグが苦手

WebアプリではRDBでは取り扱いづらいデータを取り扱う事がよくあります。タグの管理・検索はその一つです。

RDBはタグ情報の管理・検索をしっかりやれますが、どちらかと言うと苦手な分野です。しかし、PostgreSQLの

  • 配列
  • GIN（Generalized Inverse Index – 転置インデックス）

を使うと簡単かつ高速に処理できます。

PostgreSQLを使うとタグ検索が簡単・高速に実現できますが、Googleで「タグ検索 PostgreSQL」と検索しても全く出てきません。「GIN タグ検索 PostgreSQL」とすると私が執筆協力させて頂いたページがやっと出てくるくらいです。PostgreSQLのGINと配列を使ってタグ検索が高速に行えることは非常にありがたい事なのですが、残念ながら今ひとつ広く認知されていないようです。

データ容量の変化、クエリプラン、ベンチマークなど詳しい事はSIOSさんのページに書いてあるので、できる限り単純に分り易く書きます。

 

配列を使ったタグの保存

PostgreSQL の配列型はデータ型名に”[]”を付けるだけです。

店（shop)テーブルに店名（name TEXT型）とその店のタグ（tags TEXT型配列）を定義します。
CREATE TABLE shop (
  name text,
  tags text[]
);

配列要素数は任意に設定できます。別テーブルを利用せずこれだけで、店に任意の数のタグを付ける事が可能になりました。

“PostgreSQLショップ”に”RDB”、”OSS”、”ORDB”、”データベース” タグを付けて保存する場合、

INSERT INTO shop ( 
  name, 
  tags
) 
VALUES (
  'PostgreSQLショップ',
  '{ "RDB", "OSS", "ORDB", "データベース" }'
);

とします。配列型はJSON型と同じような要領で、フォーマットされたテキストとして利用します。

データは保存できたけど配列の検索は遅いんじゃないの？と思うかも知れません。

 

転置インデックス（GIN）を使う

GINを使えば配列データのタグ情報を”高速に検索”できます。転置インデックスの詳しい解説は省略しますが、キーワードがどの文書に含まれるか検索する場合、効率的に検索できるインデックスです。

“キーワードがどの文書に含まれているか検索”と聞くと”タグがどのレコードに含まれているか検索”する為にも利用できるのでは？と考えますよね？勿論できます！

CREATE INDEX shop_tag_idx ON shop USING GIN (tags);

とするだけです。簡単ですね！

 

タグを検索する

ここまでで配列を使ってタグを保存し、GINを使ってタグ情報にインデックスを付けました。残りは検索だけです。

配列の比較に利用できるANY(配列内のどの要素にでも一致）があります。これがタグ検索に利用できます。例えば、”OSS”、”RDB”を含むレコードを検索する場合、

SELECT * FROM shop WHERE
  'OSS' = ANY(tags)
  AND
  'RDB' = ANY(tags);

と検索します。

yohgaki@[local] ~=# SELECT * FROM shop WHERE
yohgaki-#   'OSS' = ANY(tags)
yohgaki-#   AND
yohgaki-#   'RDB' = ANY(tags);
        name        |            tags             
--------------------+-----------------------------
 PostgreSQLショップ | {RDB,OSS,ORDB,データベース}
(1 行)

時間: 0.790 ms

とても簡単ですが、転置インデックスを利用しているので高速に検索できます。しかもデータベース容量も小さくなります。

解りやすいANYのみを紹介しましたが、PostgreSQLは色々な関数／オペレータや比較をサポートしています。それらの中でインデックスが利用できるものも多くあります。

 

まとめ

PostgreSQLを使うとWebアプリでよく利用するデータを効率的に保存・検索できます。タグデータの保存と検索もその一例です。GINに似たインデックスにGiSTがあります。違う特徴を持っているのでGiSTが便利な場合もあるでしょう。

PostgreSQL 9.2からJSON型がサポートされました。PostgreSQL 9.3からはJSON型サポートが強化されオペレータや関数が追加されました。PostgreSQLは関数インデックスもサポートしています。関数インデックスを利用すると、JSON型に保存された任意の要素が、通常のテーブルに保存＆インデックスされたコラムと同じ速度で検索できます。JSONデータ検索もPostgreSQLで決まり！です。

RDBはタグ検索が苦手と言っても木構造よりマシです。PostgreSQLの配列を利用すると木構造も割と簡単かつ解りやすく表現する事ができます。こちらも検索しても適切と思われるページが出てこないですね。解説は省略しますが、配列を使うと簡単に木構造も処理できます。

OSSでPostgreSQLのORDB機能をフル活用するライブラリ／フレームワークは見たことがありませんが、PostgreSQLはRDBではなくORDBです。クラス継承（テーブル継承）をサポートするオブジェクト指向型データベースとしても利用できます。ORDB機能を利用すると、継承関係を持つアプリのオブジェクトをPostgreSQLの継承テーブルを利用して素直にマップできます。

Webアプリを作る場合、PostgreSQLを使うと楽に作れる事があります。PostgreSQLを使ったことがない方、使うと様々な問題が簡単に解決できるかも知れません！

追記：

PostgreSQL 9.4からGINが高速化され、データサイズも小くなります。 JSONB型（文字列でなくハッシュ形式保存されるJSONデータ型）もサポートされます。JSONを利用した場合も配列を利用した場合と同様に高速検索できます。タグのようなデータを保存／検索するならPostgreSQLです。

投稿者: yohgaki