The metric statistics show the overall database usage and include information about SQL statements, tables, indexes, and background processes. The statistics are analogous to a physical examination report and show the problems and adjustment points. You can evaluate what can be optimized in your database based on the statistics.

pg_stat_database
Column | Example value | Description |
---|---|---|
datid | 13510 | The object identifier (OID) of a database. |
datname | postgres | The name of the database. |
numbackends | 98 | The number of backends that are connected to the database. |
xact_commit | 14291309 | The total number of transactions that are committed in the database. |
xact_rollback | 0 | The total number of transactions that are rolled back in the database. |
blks_read | 536888 | The total number of disk blocks that are physically read in the database. |
blks_hit | 261717850 | The number of blocks that are hit in shared_buffer. |
tup_returned | 58521416 | The total number of scanned rows in full table scans, or the number of index entries that are returned by using an index method. If the value is significantly greater than the value of the tup_fetched parameter, a large number of full table scans exist in the database. |
tup_fetched | 57193639 | The number of rows that are returned by indexes. |
tup_inserted | 14293061 | The number of inserted rows. |
tup_updated | 42868451 | The number of updated rows. |
tup_deleted | 98 | The number of deleted rows. |
conflicts | 0 | The number of queries that are canceled due to conflicts with recovery. The conflicts occur only on read-only nodes. |
temp_files | 0 | The number of generated temporary files. If the value is large, the value of the work_mem parameter needs to be increased. |
temp_bytes | 0 | The size of the generated temporary files. |
deadlocks | 0 | The number of deadlocks. If the value is large, the service logic is faulty. |
blk_read_time | 0 | The time spent in reading data files in the database. If the value is large, the memory is small and data files need to be frequently read from disks. |
blk_write_time | 0 | The time spent in writing data files in the database. |
stats_reset | 2019/11/9 14:06 | None |
The information about the TPS (Transactions per Second) and Scanned Rows metrics on the Monitoring page in the PolarDB console is retrieved from pg_stat_database. The following two figures show basic information about the database usage. You can locate obvious load changes. For more information about how to go to the Monitoring page, see Performance monitoring and alert configuration.


pg_stat_user_tables
Column | Example value | Description |
---|---|---|
relid | 16390 | The OID of a table. |
schemaname | public | The name of the schema. |
relname | pgbench_accounts | The name of the table. |
seq_scan | 0 | The number of full table scans on the table. |
seq_tup_read | 0 | The number of rows scanned in all the full table scans. If the value is large, all the SQL statements that are executed for this table may perform full table scans. |
idx_scan | 29606482 | The number of index scans. |
idx_tup_fetch | 29606482 | The number of rows returned by index scans. |
n_tup_ins | 0 | The number of inserted rows. |
n_tup_upd | 14803241 | The number of updated rows. |
n_tup_del | 0 | The number of deleted rows. |
n_tup_hot_upd | 14638544 | The number of rows that are hot updated. A value that is similar to the value of n_tup_upd indicates excellent update performance. The indexes are not updated when data is updated. |
n_live_tup | 100012319 | The number of live rows. |
n_dead_tup | 2403437 | The number of dead rows. |
n_mod_since_analyze | 0 | The estimated number of rows that are modified after the last analysis of the table. |
last_vacuum | None | The time at which the table is manually vacuumed last time. |
last_autovacuum | None | The time at which the table is vacuumed by the autovacuum daemon last time. |
last_analyze | None | The time at which the table is manually analyzed last time. |
last_autoanalyze | 2019/4/9 14:12 | The time at which the table is analyzed by the autovacuum daemon last time. |
vacuum_count | 0 | The number of times the table is manually vacuumed. |
autovacuum_count | 0 | The number of times the table is vacuumed by the autovacuum daemon. |
analyze_count | 0 | The number of times the table is manually analyzed. |
autoanalyze_count | 1 | The number of times the table is analyzed by the autovacuum daemon. |
- If the values of the seq_scan and seq_tup_read parameters are large, a large number of full table scans are performed on the table. In this case, you must locate the faulty SQL statements and optimize the SQL statements.
- If the value of the n_dead_tup parameter is large, a large number of UPDATE and DELETE operations are performed on the table and a large amount of junk data is generated. In this case, you must perform the VACUUM operation on the table and set the autovacuum parameter to a proper value. If you encounter a similar situation, you can manually perform the VACUUM operation on the table, or specify the autovacuum parameter to trigger an automatic VACUUM operation.
pg_stat_user_indexes
Column | Example value | Description |
---|---|---|
relid | 16390 | The OID of a table. |
indexrelid | 16404 | The OID of an index. |
schemaname | public | The name of the schema. |
relname | pgbench_accounts | The name of the table. |
indexrelname | pgbench_accounts_pkey | The name of the index. |
idx_scan | 29606482 | The number of index scans that are initiated on the index. If the value is small, the index is rarely used and can be deleted. |
idx_tup_read | 29949698 | The number of index entries that are returned by using an index method. |
idx_tup_fetch | 29606482 | The number of rows that are returned by using an index method. |
Based on the statistics of pg_stat_user_indexes, you can identify the frequently used indexes and invalid indexes in the database. You can delete invalid indexes to reduce the disk usage and improve the performance of INSERT, UPDATE, and DELETE operations.
pg_statio_user_tables
Column | Example value | Description |
---|---|---|
relid | 16390 | The OID of a table. |
schemaname | public | The name of the schema. |
relname | pgbench_accounts | The name of the table. |
heap_blks_read | 414012 | The number of disk blocks that are read from the table. |
idx_heap_blks_hit | 44710713 | The number of shared_buffer hits in the table. |
idx_blks_read | 67997 | The number of disk blocks that are read from all the indexes on the table. |
idx_blks_hit | 89424015 | The number of shared_buffer hits in all the indexes on the table. |
toast_blks_read | None | The number of disk blocks that are read from the TOAST table of the table. TOAST is the acronym of The Oversized-Attribute Storage Technique. |
toast_blks_hit | None | The number of shared_buffer hits in the TOAST table of the table. |
tidx_blks_read | None | The number of disk blocks that are read from the TOAST table indexes of the table. |
tidx_blks_hit | None | The number of shared_buffer hits in the TOAST table indexes. |
pg_stat_bgwriter
Column | Example value | Description |
---|---|---|
checkpoints_timed | 1050 | The number of checkpoints that are triggered after the time specified by checkpoint_timeout has elapsed. |
checkpoints_req | 1 | The number of checkpoints that are triggered manually or when the number of write-ahead logging (WAL) files reaches the value of the max_wal_size parameter. |
checkpoint_write_time | 659728 | The amount of time spent in writing data from shared_buffer to disks. |
checkpoint_sync_time | 549 | The amount of time spent in calling fsync to synchronize dirty data to disks during checkpoints. |
buffers_checkpoint | 122383 | The number of dirty blocks that are written during checkpoints. |
buffers_clean | 60723 | The number of blocks that are written by the bgwriter process. |
maxwritten_clean | 583 | The number of times the bgwriter process stops writing data because the amount of the data written at a time exceeds the value of the bgwriter_lru_maxpages parameter. |
buffers_backend | 306521 | The number of blocks that are written by a backend. |
buffers_backend_fsync | 0 | The number of times a backend needs to call fsync. |
buffers_alloc | 317113 | The number of allocated buffers. |
stats_reset | 2019-03-28 16:54:45 | The time at which the statistics are reset. |
The pg_stat_bgwriter view allows you to check whether the parameters relevant to checkpoints and max_wal_size are set to proper values. You can also check whether the parameters relevant to the bgwriter process are set to proper values.
pg_stat_statements
Column | Example value | Description |
---|---|---|
userid | 10 | The ID of the user who executes the statement. |
dbida | 12917 | The OID of the database in which the statement is executed. |
queryid | 4390283800491518311 | The hash value that is obtained after data normalization in the SQL query. |
query | select version() | The content that is obtained after data normalization in the SQL query. |
calls | 1 | The number of times the SQL statement is executed. |
total_time | 0.208 | The total time spent in executing the SQL statement. |
min_time | 0.208 | The minimum time spent in executing the SQL statement. |
max_time | 0.208 | The maximum time spent in executing the SQL statement. |
mean_time | 0.208 | The mean time spent in executing the SQL statement. |
stddev_time | 0 | The overall standard deviation of the time that is spent in executing the SQL statement. Unit: milliseconds. |
rows | 1 | The number of rows that are returned or affected by the SQL statement. |
shared_blks_hit | 0 | The number of blocks that are hit by the SQL statement in shared_buffer. |
shared_blks_read | 0 | The number of disk blocks that are read by the SQL statement. |
shared_blks_dirtied | 0 | The number of blocks that are dirtied by the SQL statement in shared_buffer. |
shared_blks_written | 0 | The number of blocks that are written by the SQL statement. |
local_blks_hit | 0 | The number of blocks that are hit by the SQL statement in a temporary table. |
local_blks_read | 0 | The number of blocks that need to be read by the SQL statement from a temporary table. |
local_blks_dirtied | 0 | The number of blocks that are dirtied by the SQL statement in a temporary table. |
local_blks_written | 0 | The number of blocks that are written by the SQL statement to a temporary table. |
temp_blks_read | 0 | The number of blocks that are read by the SQL statement from a temporary file. |
temp_blks_written | 0 | The number of blocks that are written by the SQL statement to a temporary file. |
blk_read_time | 0 | The total time spent in reading disk blocks. |
blk_write_time | 0 | The total time spent in writing disk blocks. |
pg_stat_activity
Column | Example value | Description |
---|---|---|
datid | 12630 | The OID of the database to which the backend is connected. |
datname | postgres | The name of the database to which the backend is connected. |
pid | 19239 | The process ID of the backend. |
usesysid | 10 | The OID of the user who logs on to the backend. |
usename | postgres | The name of the user who logs on to the backend. |
application_name | psql | The name of the application that is connected to the backend. |
client_addr | None | The IP address of the client that is connected to the backend. |
client_hostname | None | The host name of the connected client. |
client_port | -1 | The TCP port that is used for the communications between the client and the backend. |
backend_start | 2020-02-22 18:52:16 | The time when the process is started. |
xact_start | 2020-02-23 15:25:46 | The time when the current transaction of the process is started. |
query_start | 2020-02-23 15:25:46 | The time when the active query starts. |
state_change | 2020-02-23 15:25:46 | The time when the state is last changed. |
wait_event_type | None | The type of the event for which the backend is waiting in a session. |
wait_event | None | The name of the event for which the backend is waiting. |
state | active | The session status of the backend. Valid values: active, idle, idle in transaction, and idle in transaction (aborted). |
backend_xid | None | The transaction identifier of the backend. |
backend_xmin | 1089 | The xmin scope of the backend. |
query | select 1; | The SQL statement of the query. |
backend_type | client backend | The type of the backend. Valid values: autovacuum launcher, autovacuum worker, client backend, and checkpointer. |
pg_locks
Column | Example value | Description |
---|---|---|
locktype | relation | The type of lockable objects. Valid values: relation, extend, page, tuple, transactionid, virtualxid, object, userlock, and advisory. |
database | 12630 | The OID of the database where the lock target exists. The value is 0 if the target is a shared object. This column is empty if the target is a transaction ID. |
relation | 11645 | The OID of the relation that is targeted by the lock. This column is empty if the target is not a relation or is only part of a relation. |
page | None | The page number that is targeted by the lock within the relation. This column is empty if the target is not a relation page or a tuple. |
tuple | None | The tuple number that is targeted by the lock within the page. This column is empty if the target is not a tuple. |
virtualxid | None | The virtual ID of the transaction that is targeted by the lock. This column is empty if the target is not a virtual transaction ID. |
transactionid | None | The ID of the transaction that is targeted by the lock. This column is empty if the target is not a transaction ID. |
classid | None | The OID of the system catalog that contains the lock target. This column is empty if the target is not a general database object. |
objid | None | The OID of the lock target in its system catalog. This column is empty if the target is not a general database object. |
objsubid | None | The column number that is targeted by the lock. |
virtualtransaction | 3/220 | The virtual ID of the transaction that holds the lock or is waiting for the lock. |
pid | 19239 | The process ID (PID) of the server process that holds the lock or is waiting for the lock. |
mode | AccessShareLock | The lock mode that the process already holds or wants to hold. |
granted | t | Specifies whether the process holds the lock mode or wants to hold the lock mode. |
fastpath | t | Specifies whether the lock is obtained by using the fast path. If the lock is obtained by using the fast path, the value is t. If the lock is obtained by using the main lock table, the value is f. |