PGMon - database structure
This article contains brief description of database structure used for the PGMon project - tables, their structure, etc.
Database structure consists of four tables - Tables, Indexes, Databases and BgWriter, their meaning is obvious from their names. Let's look at their structure.
Tables
This table is used to store information from system catalogs pg_stat_all_tables, pg_statio_all_tables a pg_class, which are updated by internal processes. For availability of some values, the statistics collector has to be enabled.
| name | type | NULL | default value | comment |
|---|---|---|---|---|
| stat_time | timestamp | no | now() | primary key |
| dbname | varchar(64) | no | primary key | |
| schemaname | varchar(64) | no | primary key | |
| tablename | varchar(64) | no | primary key | |
| seq_scan | bigint | yes | ||
| seq_tup_read | bigint | yes | ||
| idx_scan | bigint | yes | ||
| idx_tup_fetch | bigint | yes | ||
| heap_blks_read | bigint | yes | ||
| heap_blks_hit | bigint | yes | ||
| idx_blks_read | bigint | yes | ||
| idx_blks_hit | bigint | yes | ||
| toast_blks_read | bigint | yes | ||
| toast_blks_hit | bigint | yes | ||
| tidx_blks_read | bigint | yes | ||
| tidx_blks_hit | bigint | yes | ||
| n_tup_ins | bigint | yes | ||
| n_tup_upd | bigint | yes | ||
| n_tup_del | bigint | yes | ||
| n_pages | integer | yes | ||
| n_tuples | integer | yes | ||
| last_vacuum | timestamp | yes | ||
| last_autovacuum | timestamp | yes | ||
| last_analyze | timestamp | yes | ||
| last_autoanalyze | timestamp | yes |
The table contains basic information about each table (number of rows / pages), infromation about it's usage (number of sequential scans, cache efficiency, number of blocks read, number of blocks read from the cache, infromation about TOAST usage, ...).
This table contains information about activity of the autovacuum / autoanalyze demon, i.e. date of last automatic or manual execution of VACUUM and ANALYZE on the table (and all indexes defined on it).
Indexes
This table is used to store information from system catalogs pg_stat_all_indexes, pg_statio_all_indexes and pg_class, updated by internal processes. For availability of some values, the statistics collector has to be enabled.
| name | type | NULL | default value | comment |
|---|---|---|---|---|
| stat_time | timestamp | no | now() | primary key |
| dbname | varchar(64) | no | primary key | |
| schemaname | varchar(64) | no | primary key | |
| tablename | varchar(64) | no | primary key | |
| indexname | varchar(64) | no | primary key | |
| idx_scan | bigint | yes | ||
| idx_tup_read | bigint | yes | ||
| idx_tup_fetch | bigint | yes | ||
| idx_blks_read | bigint | yes | ||
| idx_blks_hit | bigint | yes | ||
| n_pages | integer | yes | ||
| n_tuples | integer | yes | ||
| n_table_pages | integer | yes | ||
| n_table_tuples | integer | yes |
The table contains basic information about an index (number of tuples / pages), information about usage of the index (numbef of index scans, number of blocks read, number of blocks read from the cache, ...).
This table (unlike Tables table) does not contain infromation about activity of the autovacuum / autoanalyze demon, as the indexes are vacuumed / analyzed with the table.
databases
This table is just a snapshot of the pg_stat_database table. It contains information about each database in the system - number of commits, rollbacks, blocks read and block hits.
| name | type | NULL | default value | comment |
|---|---|---|---|---|
| stat_time | timestamp | no | now() | primary key |
| dbname | varchar(64) | no | 0 | |
| backends | integer | no | 0 | |
| n_commits | bigint | no | 0 | |
| n_rollbacks | bigint | no | 0 | |
| blks_read | bigint | no | 0 | |
| blks_hit | bigin | no | 0 |
BgWriter
This table is used to store information about the whole cluster, resp. about processes and parts common for the whole cluster (bgwriter, shared buffer pool, etc.).
| name | type | NULL | default value | comment |
|---|---|---|---|---|
| stat_time | timestamp | no | now() | primary key |
| hostname | varchar(64) | no | primary key | |
| checkpoints_timed | bigint | no | ||
| checkpoints_req | bigint | no | ||
| buffers_checkpoint | bigint | no | ||
| buffers_clean | bigint | no | ||
| maxwritten_clean | bigint | no | ||
| buffers_backend | bigint | no | ||
| buffers_alloc | bigint | no |
It contains information about checkpoins, number of executions / interruptions of "cleaning scans", statistics of shared buffer pool, etc.
Note: This table (resp. the pg_stat_bgwriter system catalog used as a data source) is not available for PostgreSQL before version 8.3.
