PostgreSQLでボトルネックになっていた処理の一部をNode.jsに置き換えてパフォーマンスを改善した話

## はじめに

SQLで集約処理を行うと、パフォーマンスがネックになるケースがよくあります。  
とくに、`GROUP BY` + `SUM` + `ARRAY_AGG(JSON)` のような構成では、**DBに負荷が集中**し、画面表示の遅延やタイムアウトが起きることも。

この記事では、**SQLからNode.jsへ処理を一部移管することで得られた改善効果と具体的な実装手法**を紹介します。

---

## 問題の発見：SQLでの集計が重い

当初は以下のようなSQLで、ユーザー情報を集計していました。（一部情報を伏せてあります。）

- `JSON_BUILD_OBJECT` 引数をjsonに変換
- `ARRAY_AGG` 引数を配列に変換

つまり、日付ごとのデータを横持ちしにし、全ての日付のデータの合計を集計していました。

```sql:sql
SELECT 
  user_id,
  user_name,
  ARRAY_AGG(JSON_BUILD_OBJECT(
      'date', date,
      'status1', status1,
      'status2', status2,
      'status3', status3,
    ) ORDER BY date
  ) AS status_array,
  SUM(status1) AS total1,
  SUM(status2) AS total2,
  SUM(status3) AS total3
FROM data
GROUP BY user_id, user_name
ORDER BY user_id;
```

このとき、以下のような負荷要因が確認されました：

- 全件読み取り
- `GROUP BY` による内部ソートとメモリ確保
- `status`の合計演算
- JSON配列の生成 (`ARRAY_AGG`, `JSON_BUILD_OBJECT`)
- 集計に`INTERVAL`型もあって処理が重い
- これに加えて再帰的CTE`WITH RECURSIVE`も使っていた

**結果：画面描画に約5秒以上かかるケースも発生。**

## なぜこんなにも処理に時間がかかるのか？
### 重くなる4大要因

#### 1.ARRAY_AGG(JSON_BUILD_OBJECT(...)) のコストが高い

- 各行に対してJSONを構築（JSON_BUILD_OBJECT）
- さらにそのJSONを 配列化（ARRAY_AGG）
- 内部で 一時メモリ領域（tuplestore）を大量に使用

大量データ（例：365日×100人＝36,500行）でこれをやると、全データを一旦バッファに載せてからソート＋整形されるため非常に重い

#### 2.GROUP BY + ORDER BY のソートが競合
- GROUP BY 自体は内部で ソートかハッシュ構築を使います
- ARRAY_AGG(... ORDER BY date) が さらに別のソートを強制します

→ 同時に2種類のソートが走る

#### 3.interval 型の SUM() はCPU負荷が高め
- PostgreSQLの interval は 複合型（time + dateのような内部構造）
- 単なる数値の SUM() より 演算処理が複雑
- 加算のたびに 型変換やオーバーフロー制御が走る

#### 4.再帰CTEやJOINの依存構造

クエリ全体 WITH RECURSIVE や LEFT JOIN などを含んでいるため
- 事前に多段構造の仮想テーブルを構築
- その後このクエリで 全件をまとめて集約処理

つまり、「大量行 × ネスト構造 × JSON化 × ソート × 集約」のコンボが一気に走る構造になっている

#### わかりやすく
PostgreSQL（SQL）

WITH RECURSIVE（CTE展開）
      ↓
GROUP BY + SUM(interval)
      ↓
ARRAY_AGG(JSON_BUILD_OBJECT)
      ↓
クライアントへ返却（巨大なJSON）

- すべてDB側で処理・集約して返却
- 重い処理が1回のクエリに集中
- 並列化できない
- JSON生成とソートを同時にやっており遅い

#### まとめ このクエリが重いのは

「大量の明細データを、JSON形式で集約・整形・合計しつつ、日付順に並べる」という 複合タスクを一つのSQLで同時にやっているから。

---

## アプローチ変更：Node.jsで集計処理を行う

| 処理 | 変更前（SQL） | 変更後（Node.js） |
|------|----------------|-------------------|
| 集計 | SQLで `GROUP BY` | JavaScript `Map` で集計 |
| 表形式加工 | SQLの `ARRAY_AGG` | JSで日付ごとに分解 |
| 合計・平均計算 | SQLの `SUM()` | JSで手動加算 |
| 実行負荷 | PostgreSQLに集中 | アプリケーション層へ分散 |

---

## 実装ステップ

### 1. SQLからは「明細行だけ」取得

```sql:sql
SELECT
  user_id,
  user_name,
  date,
  status1,
  status2,
  status3,
  ...
FROM data;
```

### 2. Node.jsでグループ化と集計

```js:js
const grouped = new Map();

//グループ化
for (const row of resultFromDB) {
  if (!grouped.has(row.user_id)) {
    grouped.set(row.user_id, { user_name: row.user_name, rows: [] });
  }
  grouped.get(row.user_id).rows.push(row);
}
```

### 3. Mapで日付別・合計値を加工

```js:js
const status1Map = new Map();
//合計値を初期化
status1Map.set('total1', 0);

for (const row of group.rows) {
  const status1 = row.status1);
  //各日付のデータを追加
  status1Map.set(date, status1);
  //合計値に加算
  status1Map.set('total1', status1Map.get('total1') + status1);
}
```
#### わかりやすく
PostgreSQL（SQL）
JOIN + CTE済の平坦なデータ
↓
SELECT *（1行ずつの明細を返す）
      ↓
Node.js（アプリ）
Mapでグループ
↓
  status配列生成（Array.push）
      ↓
  各種SUM（+=）
      ↓
  最終的にJSON構築（Object.fromEntries）

---

## 改善結果

| 項目 | Before（SQL集計） | After（Node.js集計） |
|------|---------------------|------------------------|
| 初回表示速度 | 約5秒以上 | 約1秒未満 |
| DB CPU使用率 | 高 | 低 |
| フロント柔軟性 | 低 | 高 |
| 保守性 | SQLに依存 | JavaScript内で完結 |


---

## 🧩 パフォーマンス調査のヒント

- `EXPLAIN ANALYZE` でSQLのボトルネック確認
- SQL内での `JOIN`・`CTE`・`GROUP BY` の重なりは要注意
- 一時テーブルやビューよりも「アプリ層で加工」のほうが早いケースも多い

---

## ✅ まとめ

SQL集計からNode.jsへ処理を移管することで、

- DB負荷の軽減
- レスポンスの高速化
- 表現の柔軟性

を実現できました。