Node.jsからPostgreSQLへコネクションプールを使った接続

はじめに

前回の記事（LinuxサーバーにPostgreSQL導入～外部サーバー接続まで）で、Linuxサーバに導入したPostgreSQLにNode.jsから接続するところまでをまとめました。
次はコネクションプールを理解して、それを使った接続方法をまとめていきます。

コネクションプールとは

クエリを発行する際、使用するコネクションを保持しておいて使いまわす仕組みのことです。

例えば、DBから値を取ってきて返却するAPIサーバがあるとします。

悪い例だと以下のようになります。

（1回目のAPI）
　　➀DBへ接続
　　➁SELECT文とかでデータ取得
　　➂コネクション切断
（2回目のAPI）
　　➃DBへ接続
　　➄SELECT文とかでデータ取得
　　➅コネクション切断

これだと毎回、接続／切断を繰り返しているので、多少なりともオーバーヘッドが生じます。
できれば、最初につないだコネクションを使いまわしたいですよね？

そこで登場する仕組みがコネクションプールです。
これによって、先ほどの悪い例が以下のように変わります。

（1回目のAPI）
　　➀DBへ接続
　　➁SELECT文とかでデータ取得
　　➂コネクションをプールに返却
（2回目のAPI）
　　➃プールからコネクションをチェックアウト
　　➄SELECT文とかでデータ取得
　　➅コネクションをプールに返却

実際にやってみる

Node.jsでAPIサーバを立てて、実際に動作を確認してみます。

pool.js

const { Pool } = require("pg");

// 接続先文字列
const connectionString = 'postgres://user:pass@DBのアドレス:ポート/DB名';

// DB情報をもったプールを生成
const pool = new Pool({
    connectionString: connectionString,
    max: 2          // 保持するコネクション数
});

module.exports = pool;

index.js

const express = require('express');
const app = express();
const pool = require('./pool.js');

app.get('/', async function(req, res, next) {
    console.log("=============================");

    // 取得前
    console.log('取得前 totalCount: ', pool.totalCount);
    console.log('取得前 idleCount: ', pool.idleCount);
    console.log('取得前 waitingCount: ', pool.waitingCount);

    // コネクション取得
    const connect = await pool.connect();

    // 取得後
    console.log('取得後 totalCount: ', pool.totalCount);
    console.log('取得後 idleCount: ', pool.idleCount);
    console.log('取得後 waitingCount: ', pool.waitingCount);

    // クエリ発行
    const result = await connect.query('SELECT NOW()');

    // コネクション返却
    connect.release();

    // 返却後
    console.log('返却後 totalCount: ', pool.totalCount);
    console.log('返却後 idleCount: ', pool.idleCount);
    console.log('返却後 waitingCount: ', pool.waitingCount);

    res.status(200).json({data: result.rows})
});

app.listen(3001, () => console.log(`listening on port 3001!`));

保持するコネクション数はとりあえず2としています。
設定しない場合はデフォルトで10となります。

ログ出力している値はこのようになります。
・totalCount　：　プールに存在するコネクション数
・idleCount　：　プールに存在する使用されていないコネクション数
・waitingCount　：　コネクションの取得待ち数

実行結果は以下になります。

>node index.js
listening on port 3001!
=============================
取得前 totalCount:  0
取得前 idleCount:  0
取得前 waitingCount:  0
取得後 totalCount:  1
取得後 idleCount:  0
取得後 waitingCount:  0
返却後 totalCount:  1
返却後 idleCount:  1
返却後 waitingCount:  0

コネクションを取得した後は忘れずに、返却しなければなりません。
それを忘れてしまうと、永遠に解決しないコネクション取得待ちが発生してしまいます。

試しに、発生させてみましょう。
コネクション返却をコメントアウトします。

index.js

    // コネクション返却
    // connect.release();

    // 返却後
    // console.log('返却後 totalCount: ', pool.totalCount);
    // console.log('返却後 idleCount: ', pool.idleCount);
    // console.log('返却後 waitingCount: ', pool.waitingCount);

4回続けて、APIを呼び出した結果が以下です。

>node index.js
listening on port 3001!
=============================
取得前 totalCount:  0
取得前 idleCount:  0
取得前 waitingCount:  0
取得後 totalCount:  1
取得後 idleCount:  0
取得後 waitingCount:  0
=============================
取得前 totalCount:  1
取得前 idleCount:  0
取得前 waitingCount:  0
取得後 totalCount:  2
取得後 idleCount:  0
取得後 waitingCount:  0
=============================
取得前 totalCount:  2
取得前 idleCount:  0
取得前 waitingCount:  0
=============================
取得前 totalCount:  2
取得前 idleCount:  0
取得前 waitingCount:  1

3回目は取得することができていないです。
4回目でwaitingCountの数が1（3回目の呼び出しのこと）となっていることが分かります。

プール内の最大コネクション数

コネクションプールの値をどのくらいにすればいいかわからない場合は、waitingCountを監視して、チューニングすることが推奨されます。

前述しましたが、コネクションの数はmaxプロパティで指定します。
指定しなかった場合、デフォルトの値は10となります。

const pool = new Pool({
connectionString: connectionString,
max: 2 // 保持するコネクション数
});


コネクションの接続時間数

1回目のコネクションを2回目もしっかり使っているのか確認してみたく、実際にDBのログを見て確認してみることにしました。

まずはDBの設定を下記の通り編集します。

/var/lib/pgsql/13/data/postgresql.conf

log_connections = on
log_disconnections = on
log_statement = 'all'                 # none, ddl, mod, all

これで、DB接続、切断、SQLの情報がログに出力されるようになります。

試しに2回APIを呼び出してみます。

/var/lib/pgsql/13/data/log/postgresql-XXX.log

2021-06-05 01:57:53.460 JST [unknown] [8788] LOG:  connection received: host=XXX.XXX.XXX.XXX port=XXXX
2021-06-05 01:57:53.719 JST first_db [8788] LOG:  connection authorized: user=test database=first_db
2021-06-05 01:57:53.739 JST first_db [8788] LOG:  statement: SELECT NOW()
2021-06-05 01:58:03.766 JST first_db [8788] LOG:  disconnection: session time: 0:00:10.307 user=test database=first_db host=XXX.XXX.XXX.XXX port=XXXX
2021-06-05 01:58:20.797 JST [unknown] [8792] LOG:  connection received: host=XXX.XXX.XXX.XXX port=XXXX
2021-06-05 01:58:20.909 JST first_db [8792] LOG:  connection authorized: user=test database=first_db
2021-06-05 01:58:20.921 JST first_db [8792] LOG:  statement: SELECT NOW()
2021-06-05 01:58:30.933 JST first_db [8792] LOG:  disconnection: session time: 0:00:10.136 user=test database=first_db host=XXX.XXX.XXX.XXX port=XXXX

disconnectionされてますね…

よく見るとどちらも約10秒後に切断されています。
調査してみると、デフォルトで10秒後に切断されるそうです。
ということは10秒に1回アクセスのあるアプリでそのままプールを使っても、何の意味がないことになります。

const pool = new Pool({
    connectionString: connectionString,
    max: 2,         // 保持するコネクション数
    idleTimeoutMillis: 600000    // 自動切断時間(ミリ秒)
});

これで1分はコネクションが保持されることになります。
また、この値を0にすれば、自動切断自体を無効化することができます。

DBのログから確認する作業と、確認する間隔を10秒以上開けていなかったら気付けなかったかもしれない…