Add support for exposing database state similar to pg_stat_database and pg_stat_activity

Is your feature request related to a problem? Please describe. It's currently not possible to get an overview of the system status beyond the min health monitoring which is an indicator of whether the system is operational or not.

Describe the solution you'd like Postgres has a dedicated subsystem for monitoring which provides insights into server activity. The information is shown in table-like views and can be accessed via SQL.

Activity stats

In Postgres, pg_stat_activity is useful for troubleshooting and diagnosing issues as a DBA as it contains details relating to currently-running queries and connections to the database:

SELECT * FROM pg_stat_activity WHERE pid = 3245;

returns results such as:

datid            │ 16388
datname          │ qdb
pid              │ 3245
usesysid         │ 1234
usename          │ my_client
application_name │ questdb
client_addr      │ 1.2.3.4
client_hostname  │ qdb1
client_port      │ 1234
backend_start    │ 2021-01-22 17:05:30.495627+01
xact_start       │ 2021-01-22 17:06:39.208258+01
query_start      │ 2021-01-22 17:06:42.535464+01
state_change     │ 2021-01-22 17:06:42.535467+01
waiting          │ f
state            │ active
query            │ SELECT something FROM my_table;

In this case, we have useful metrics such as:

• how long the database has been online
• which clients are connected to a database
• how long a query has been running
• the contents of the query

Database stats

In Postgres, pg_stat_database can be accessed by executing the following query:

SELECT * FROM pg_stat_database;

and returns results such as:

datid |  datname   | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted  | conflicts | temp_files   | temp_bytes | deadlocks | blk_read_time | blk_write_time | stats_reset
-------+------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+---------------+----------------+-------------------------------
     1 | template1  |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 |
 12061 | template0  |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 |
 12066 | postgres   |           2 |       77887 |            11 |       249 |  2142032 |     35291376 |      429228 |           59 |           4 |          58 |         0 |          0 |          0 |         0 |             0 |              0 | 2017-09-07 17:24:57.739225-04
 16394 | employees  |           0 |       66146 |             6 |       248 |  1822528 |     30345213 |      365608 |          176 |           6 |          62 |         0 |          0 |          0 |         0 |             0 |              0 | 2017-09-11 16:04:59.039319-04
 16450 | exampledb  |           0 |         350 |             0 |      2920 |    33853 |       517601 |        9341 |       173159 |         449 |          13 |         0 |          0 |          0 |         0 |             0 |              0 | 2017-10-04 14:13:35.125243-04

The important metrics that it is returning is:

• number of connections (numbackends)
• commits
• rollbacks
• rows/tuples fetched and returned

Additional context

• Postgres stat activity documentation
• Common commands using pg_stat_activity
• Providing these statistics via a REST /metrics entrypoint would satisfy https://github.com/questdb/questdb/issues/532