PostgreSQLの統計情報を可視化（バックエンド編）

#はじめに
「PostgreSQLの統計情報を可視化」の2章です。この章では1章の「環境構築編」で作成した環境をベースに話を進めます。

では、PostgreSQLの統計情報をAPIで取得できるようにしてみましょう。

統計情報取得クエリ

今回取得する統計情報は以下の3つにしてみました。
・DBのサイズ
・クエリの種類と数
・スロークエリ

それぞれ以下のようなSQLでデータを取得します。

// DBのサイズ取得
SELECT pg_database_size( {DB名} )

// SELECT,INSERT,UPDATE,DELETEが実行された回数の取得
SELECT substring(lower(query) from 1 for 6) as sql
 SUM(calls) as call
 WHERE substring(lower(query) from 1 for 6) in ( 'select', 'insert', 'update', 'delete')
 GROUP BY substring(lower(query) from 1 for 6)

// 閾値を越えるスロークエリの取得
SELECT query, calls, mean_time, min_time, max_time
 FROM pg_stat_statements
 WHERE max_time > {閾値}
 ORDER BY max_time DESC
 LIMIT {検索数制限値}

簡単なSQLですね。

APIプログラムの中身

APIで呼び出すプログラムは"server/routers"フォルダの下に置きます。
ここではPostgreSQLの統計情報を取得するプログラムを"statistics.js"に記述します。

server/routers/statistics.js

const express = require('express')
const router = express.Router()
const { pool, dbConf } = require('../models/index')
// SQLを実行する共通処理
async function pgExec(sql, values, dbFlag) {
  var recs = []
  var error = null
  var query = sql
  for (let i = 0; i < pool.length; i ++) {
    const client = await pool[i].connect()
    if (dbFlag) {
      query = sql.replace('$db', dbConf[i].database)
    }
    try {
      const rec = await client.query(query, values)
      recs.push({host: dbConf[i].host, db: dbConf[i].database, result: rec.rows})
    } catch (e) {
      error = (e.messages) ? e.messages : 'internal error'
    }
    finally {
      client.release()
    }
  }
  if (! error) {
    return recs
  } else {
    return null
  }
}
// 数値チェック
function isNumeric(num) {
  return (0 === num.search(/^[0-9]+$/))
}
// DBサイズ取得
router.get('/getDbSize', async function(req, res) {
  const sql = `SELECT pg_database_size( '$db' )`
  const recs = await pgExec(sql, [], true)
  if (recs !== null) {
    res.send({value: recs})
  } else {
    res.status(500).json({message: 'internal error'})
  }
})
// クエリの種類と数取得
router.get('/getSqlCalls', async function(req, res) {
  const sql = `SELECT substring(lower(query) from 1 for 6) as sql,
    sum(calls) as call
    FROM pg_stat_statements
    WHERE substring(lower(query) from 1 for 6) in ( 'select', 'insert', 'update', 'delete')
    GROUP BY substring(lower(query) from 1 for 6)`
  const recs = await pgExec(sql, [], false)
  if (recs !== null) {
    res.send({date: Date.now(), value: recs})
  } else {
    res.status(500).json({message: 'internal error'})
  }
})
// スロークエリ取得
router.get('/getSlowQuery', async function(req, res) {
  if ((!('threshold' in req.query)) || (!('limit' in req.query))) {
    res.status(400).json({message: 'bad request'})
    return
  }
  const threshold = req.query.threshold
  const limit = req.query.limit
  if ((! isNumeric(threshold)) || (! isNumeric(limit))) {
    res.status(400).json({message: 'bad request'})
    return
  }
  const sql = `SELECT query, calls, mean_time, min_time, max_time
    FROM pg_stat_statements
    WHERE max_time > $1
    ORDER BY max_time DESC
    LIMIT $2`
  const recs = await pgExec(sql, [ threshold, limit ], false)
  if (recs !== null) {
    res.send({value: recs})
  } else {
    res.status(500).json({message: 'internal error'})
  }
})

module.exports = router

###ルーティング設定
APIでアクセスできるようにパスのAliasを"server/index.js"に追加します。

server/index.js

async function backend() {
　(省略）
  const statisticsRouter = require('./routers/statistics')
  app.use('/statistics', statisticsRouter)
　(省略）
}

このルーティング設定で"server/routers/statistics.js"の関数は、外部から
"[host:port]/statistics/[関数名]"でアクセス可能になります。

##まとめ
PostgreSQLの統計情報をAPIとして取得できるバックエンドを作成しました。
次は、いよいよ統計情報を可視化するフロントエンド作成です。
>>PostgreSQLの統計情報を可視化(フロントエンド編)

いや、先にテストしないと気持ち悪いでしょ！
という方には「PostgreSQLの統計情報を可視化(単体テスト編)」をご用意しております。

###参考
nuxt-community(https://github.com/nuxt-community/express-template)
API: Nuxt.js をプログラムで使う(https://ja.nuxtjs.org/api/nuxt/)