Postgres health check that checks buffer hit performance

healthCheck.kt

data class HealthCheck(val heapRead: Long, val heapHit: Long, val ratio: Float)

suspend fun DataSource.databaseHealth(): HealthCheck? =
  connection.use { conn ->
    conn.createStatement()
      .executeQuery(healthCheckQuery).use { rs ->
        if (rs.next()) HealthCheck(
          rs.getLong("heap_read"),
          rs.getLong("heap_hit"),
          rs.getFloat("ratio")
        )
        else null
      }
  }

/*
 * heap_blks_read: Number of disk blocks read from this table in Long
 * heap_blks_hit: Number of buffer hits in this table
 * pg_statio_user_tables: Same as pg_stat_all_tables, except that only user tables are shown.
 *
 * Reference: https://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/
 */
private const val healthCheckQuery: String =
  """
  SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
  FROM
    pg_statio_user_tables;
  """