PostgreSQL：配列フィールドタイプとテーブルの性能比較：JSONとhstoreとの比較も含めて

2024-04-08

PostgreSQLで配列フィールドタイプとテーブルのどちらを使うべきか？

配列フィールドタイプを使うメリット:

データ構造がシンプル: 複数の関連するデータを1つの列にまとめられるため、データ構造がシンプルになります。
クエリが簡単: 配列要素へのアクセスや操作が専用の関数や演算子によって簡単に行えます。
ストレージ効率: 50要素以下の場合、配列の方がテーブルよりもストレージ効率が優れている可能性があります。

データの変更が難しい: 配列要素の追加や削除は、既存の要素をすべてコピーする必要があるため、複雑で時間がかかる場合があります。
データの可視性が低い: 配列要素はテーブルの行のように個別に表示できないため、データの可視性が低くなります。
複雑なクエリが難しい: 配列要素を複数結合するような複雑なクエリは、テーブルよりも難しくなる可能性があります。

テーブルを使うメリット:

データの変更が簡単: 行の追加や削除が簡単に行えます。
データの可視性が高い: 各行が個別に表示できるため、データの可視性が高いです。
複雑なクエリが簡単: 複数のテーブルを結合するような複雑なクエリが簡単に行えます。

クエリが複雑: 配列要素へのアクセスや操作は、複数の列を結合する必要があるため、複雑になる場合があります。

以下のような状況では、テーブルを使うことを検討する必要があります:

配列要素の数が50を超える
配列要素を頻繁に変更する必要がある
配列要素を複数結合するような複雑なクエリを実行する必要がある

データ構造をシンプルに保ちたい
配列要素へのアクセスや操作が単純である
ストレージ効率を重視したい

どちらを使うべきかは、データ構造、データの変更頻度、クエリのパターン、ストレージ効率などを考慮して決定する必要があります。

配列フィールドタイプを使う例

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  hobbies TEXT[],
);

INSERT INTO users (name, hobbies) VALUES ('John Doe', ARRAY['hiking', 'swimming', 'reading']);

SELECT * FROM users;

-- 結果:

-- id | name | hobbies
-- --- | --- | ---
-- 1 | John Doe | ["hiking", "swimming", "reading"]

テーブルを使う例

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
);

CREATE TABLE hobbies (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  hobby VARCHAR(255),
);

INSERT INTO users (name) VALUES ('John Doe');

INSERT INTO hobbies (user_id, hobby) VALUES (1, 'hiking');
INSERT INTO hobbies (user_id, hobby) VALUES (1, 'swimming');
INSERT INTO hobbies (user_id, hobby) VALUES (1, 'reading');

SELECT users.name, hobbies.hobby
FROM users
JOIN hobbies ON users.id = hobbies.user_id;

-- 結果:

-- name | hobby
-- --- | ---
-- John Doe | hiking
-- John Doe | swimming
-- John Doe | reading