PostgreSQLの配列型を利用する

リレーショナル・データベースで、例えば１つの商品に対して複数のタグを紐付けるような場合、関連テーブルを作成すると思います。 以下のように、商品IDとタグを組み合わせたテーブルになります。

商品ID	タグ
1	スイーツ
1	抹茶
2	スイーツ
3	アイスクリーム

これをPostgreSQLの配列型で表現してみたいと思います。

配列型でカラムを追加する

今回は既存のテーブルに追加することにしたので、以下のようなSQLを実行します。 product_idが商品ID、tag_idsが複数のタグIDを紐付ける配列になります。

ALTER TABLE product ADD COLUMN tag_ids NUMERIC(13,0) ARRAY[1];

次に、このカラムを利用するためのインデックスを作成します。

CREATE INDEX idx_product on product USING GIN (tag_ids );

このカラムにデータを登録するために、サブクエリの結果をARRAY型で登録しました。

INSERT INTO product
SELECT ... , ARRAY(SELECT tag_id FROM tag WHERE ...)
FROM ...

以上で、配列型のカラムにデータを登録することが出来ます。

配列型のカラムを利用する

登録したデータを利用してみます。 関連テーブル(ここではrelation)を利用する場合は、以下のように検索したいタグIDを指定したSQLになります。

SELECT ...
FROM product p, relation r
WHERE p.product_id = r.product_id
AND r.tag_id = [検索したいタグID]

これを先程追加した配列型のカラムにおきかえてみます。

SELECT ...
FROM product p
WHERE p.tag_ids && '{[検索したいタグID]}'

配列型を指定する場合の演算子として「&&」を利用しています。 検索したいタグIDが複数あり、いずれかが含まれる条件の場合は「&&」を、すべて含む場合は「<@」を指定することが出来ます。

メリット・デメリット

配列型を利用するメリットとしては、今回導入するきっかけになりましたが、COUNTで件数を取得するコストが低くなりました。 データの量にもよると思いますが、以前関連テーブルとDISTINCTを組み合わせていた場合に比べてコストが半分以下になりました。

逆にデメリットとしては、今回の内容では複合インデックスを作ることが出来ませんでした。 このため、他の条件を含む場合は、関連テーブルを利用したほうがコストが低くなるかもしれません。

まとめ

今回は配列型を利用しましたが、他にもJSON型もあります。 要件にあわせて、関連テーブル・配列型・JSON型など検討すると良いと思います。