Snapshot Tables¶
This chapter is intended to give detailed information about several snapshot tables, where the performance snapshot to be stored.
Snapshot Table List¶
Performance snapshot is going to be stored in the following tables associated with each performance statistics available in PostgreSQL.
| Table Name | Description | Note |
|---|---|---|
| pgperf.snapshot | Stores snapshot id and timestamp of the performance snapshot. | |
| pgperf.snapshot_pg_stat_database | Stores a snapshot of the pg_stat_database system view. | |
| pgperf.snapshot_pg_database_size | Stores a snapshot of the database size. | |
| pgperf.snapshot_pg_stat_user_tables | Stores a snapshot of the pg_stat_user_tables system view. | |
| pgperf.snapshot_pg_statio_user_tables | Stores a snapshot of the pg_statio_user_tables system view. | |
| pgperf.snapshot_pg_stat_user_indexes | Stores a snapshot of the pg_stat_user_indexes system view. | |
| pgperf.snapshot_pg_statio_user_indexes | Stores a snapshot of the pg_statio_user_indexes system view. | |
| pgperf.snapshot_pg_statio_user_sequences | Stores a snapshot of the pg_statio_user_sequences system view. | |
| pgperf.snapshot_pg_stat_user_functions | Stores a snapshot of the pg_stat_user_functions system view. | |
| pgperf.snapshot_pg_relation_size | Stores a snaphsot of the table and index size. | |
| pgperf.snapshot_pg_current_xlog | Stores a snapshot of the current xlog location and the current insert location. | |
| pgperf.snapshot_pg_stat_bgwriter | Stores a snapshot of the pg_stat_bgwriter system view. | |
| pgperf.snapshot_pg_stat_activity | Stores a snapshot of the pg_stat_activity system view. | |
| pgperf.snapshot_pg_locks | Stores a snapshot of the pg_locks system view. | |
| pgperf.snapshot_pg_statistic | Stores a snapshot of the pg_statistics system table. | |
| pgperf.snapshot_pg_stat_statements | Stores a snapshot of the pg_stat_statements view. | 8.4 or later |
| pgperf.snapshot_pgstattuple | Stores a snapshot of the result of pgstattuple function. | |
| pgperf.snapshot_pgstatindex | Stores a snapshot of the result of pgstatindex function. |
pgperf.snapshot Table¶
This table stores snapshot id and timestamp of each snapshot taken by the snapshot function.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | Monotone increasing |
| ts | timestamp | Timestamp of the snapshot | |
| level | integer | Snapshot level |
pgperf.snapshot_pg_stat_database Table¶
This table stores snapshots of the pg_stat_database system view which contains the database access statistics.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| datid | oid | pg_stat_database.datid | |
| datname | name | pg_stat_database.datname | |
| numbackends | integer | pg_stat_database.numbackends | |
| xact_commit | bigint | pg_stat_database.xact_commit | |
| xact_rollback | bigint | pg_stat_database.xact_rollback | |
| blks_read | bigint | pg_stat_database.blks_read | |
| blks_hit | bigint | pg_stat_database.blks_hit | |
| tup_returned | bigint | pg_stat_database.tup_returned | |
| tup_fetched | bigint | pg_stat_database.tup_fetched | |
| tup_inserted | bigint | pg_stat_database.tup_inserted | |
| tup_updated | bigint | pg_stat_database.tup_updated | |
| tup_deleted | bigint | pg_stat_database.tup_deleted | |
| conflicts | bigint | pg_stat_database.conflicts | 9.1 or later |
| stats_reset | timestampz | pg_stat_database.stats_reset | 9.1 or later |
pgperf.snapshot_pg_database_size Table¶
This table stores snapshots of result of the pg_database_size() function which gets database size.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| datname | name | pg_database.datname | |
| pg_database_size | bigint | pg_database_size() |
pgperf.snapshot_pg_stat_user_tables Table¶
This table stores snapshots of the pg_stat_user_tables system view which contains the table access statistics.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| relid | oid | pg_stat_user_tables.relid | |
| schemaname | name | pg_stat_user_tables.schemaname | |
| relname | name | pg_stat_user_tables.relname | |
| seq_scan | bigint | pg_stat_user_tables.seq_scan | |
| seq_tup_read | bigint | pg_stat_user_tables.seq_tup_read | |
| idx_scan | bigint | pg_stat_user_tables.idx_scan | |
| idx_tup_fetch | bigint | pg_stat_user_tables.idx_tup_fetch | |
| n_tup_ins | bigint | pg_stat_user_tables.n_tup_ins | |
| n_tup_upd | bigint | pg_stat_user_tables.n_tup_upd | |
| n_tup_del | bigint | pg_stat_user_tables.n_tup_del | |
| n_tup_hot_upd | bigint | pg_stat_user_tables.n_tup_hot_upd | |
| n_live_tup | bigint | pg_stat_user_tables.n_live_tup | |
| n_dead_tup | bigint | pg_stat_user_tables.n_dead_tup | |
| last_vacuum | timestampz | pg_stat_user_tables.last_vacuum | |
| last_autovacuum | timestampz | pg_stat_user_tables.last_autovacuum | |
| last_analyze | timestampz | pg_stat_user_tables.last_analyze | |
| last_autoanalyze | timestampz | pg_stat_user_tables.last_autoanalyze | |
| vacuum_count | bigint | pg_stat_user_tables.vacuum_count | 9.1 or later |
| autovacuum_count | bigint | pg_stat_user_tables.autovacuum_count | 9.1 or later |
| analyze_count | bigint | pg_stat_user_tables.analyze_count | 9.1 or later |
| autoanalyze_count | bigint | pg_stat_user_tables.autoanalyze_count | 9.1 or later |
pgperf.snapshot_pg_statio_user_tables Table¶
This table stores snapshots of the pg_statio_user_tables system view which contains the table access statistics.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| relid | oid | pg_statio_user_tables.relid | |
| schemaname | name | pg_statio_user_tables.schemaname | |
| relname | name | pg_statio_user_tables.relname | |
| heap_blks_read | bigint | pg_statio_user_tables.heap_blks_read | |
| heap_blks_hit | bigint | pg_statio_user_tables.heap_blks_hit | |
| idx_blks_read | bigint | pg_statio_user_tables.idx_blks_read | |
| idx_blks_hit | bigint | pg_statio_user_tables.idx_blks_hit | |
| toast_blks_read | bigint | pg_statio_user_tables.toast_blks_read | |
| toast_blks_hit | bigint | pg_statio_user_tables.toast_blks_hit | |
| tidx_blks_read | bigint | pg_statio_user_tables.tidx_blks_read | |
| tidx_blks_hit | bigint | pg_statio_user_tables.tidx_blks_hit |
pgperf.snapshot_pg_stat_user_indexes Table¶
This table stores snapshots of the pg_stat_user_indexes system view which contains the index access statistics.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| relid | oid | pg_stat_user_indexes.relid | |
| indexrelid | oid | pg_stat_user_indexes.indexrelid | |
| schemaname | name | pg_stat_user_indexes.schemaname | |
| relname | name | pg_stat_user_indexes.relname | |
| indexrelname | name | pg_stat_user_indexes.indexrelname | |
| idx_scan | bigint | pg_stat_user_indexes.idx_scan | |
| idx_tup_read | bigint | pg_stat_user_indexes.idx_tup_read | |
| idx_tup_fetch | bigint | pg_stat_user_indexes.idx_tup_fetch |
pgperf.snapshot_pg_statio_user_indexes Table¶
This table stores snapshots of the pg_statio_user_indexes system view which contains the index access statistics.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| relid | oid | pg_statio_user_indexes.relid | |
| indexrelid | oid | pg_statio_user_indexes.indexrelid | |
| schemaname | name | pg_statio_user_indexes.schemaname | |
| relname | name | pg_statio_user_indexes.relname | |
| indexrelname | name | pg_statio_user_indexes.indexrelname | |
| idx_blks_read | bigint | pg_statio_user_indexes.idx_blks_read | |
| idx_blks_hit | bigint | pg_statio_user_indexes.idx_blks_hit |
pgperf.snapshot_pg_statio_user_sequences Table¶
This table stores snapshots of the pg_statio_user_sequences system view which contains the sequence access statistics.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| relid | oid | pg_statio_user_sequences.relid | |
| schemaname | name | pg_statio_user_sequences.schemaname | |
| relname | name | pg_statio_user_sequences.relname | |
| blks_read | int8 | pg_statio_user_sequences.blks_read | |
| blks_hit | int8 | pg_statio_user_sequences.blks_hit |
pgperf.snapshot_pg_stat_user_functions Table¶
This table stores snapshots of the pg_stat_user_functions system view which contains the function access statistics.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| funcid | oid | pg_stat_user_functions.funcid | |
| schemaname | name | pg_stat_user_functions.schemaname | |
| funcname | name | pg_stat_user_functions.funcname | |
| calls | int8 | pg_stat_user_functions.calls | |
| total_time | int8 | pg_stat_user_functions.total_time | |
| self_time | int8 | pg_stat_user_functions.self_time |
pgperf.snapshot_pg_relation_size Table¶
This table stores snapshots of the result of pg_relation_size() and pg_total_relation_size() function which gets table and/or index size.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| schemaname | name | pg_stat_user_tables.schemaname, pg_stat_user_indexes.schemaname | |
| relid | oid | pg_stat_user_tables.relid, pg_stat_user_indexes.indexrelid | |
| relname | name | pg_class.relname | |
| pg_relation_size | bigint | pg_relaion_size() | |
| pg_total_relation_size | bigint | pg_total_relaion_size() | Available only for tables |
pgperf.snapshot_pg_current_xlog Table¶
This table stores snapshots of the result of pg_current_xlog_location() and pg_current_xlog_insert_location() function which gets current WAL locations.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snaphsot ID | |
| location | text | pg_current_xlog_location() | |
| insert_location | text | pg_current_xlog_insert_location() |
pgperf.snapshot_pg_stat_bgwriter Table¶
This table stores snapshots of the pg_stat_bgwriter system view which contains the background writer statistics.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| checkpoints_timed | bigint | pg_stat_bgwriter.checkpoints_timed | |
| checkpoints_req | bigint | pg_stat_bgwriter.checkpoints_req | |
| checkpoint_write_time | double precision | pg_stat_bgwriter.checkpoint_write_time | 9.2 or later |
| checkpoint_sync_time | double precision | pg_stat_bgwriter.checkpoint_sync_time | 9.2 or later |
| buffers_checkpoint | bigint | pg_stat_bgwriter.buffers_checkpoint | |
| buffers_clean | bigint | pg_stat_bgwriter.buffers_clean | |
| maxwritten_clean | bigint | pg_stat_bgwriter.maxwritten_clean | |
| buffers_backend | bigint | pg_stat_bgwriter.buffers_backend | |
| buffers_backend_fsync | bigint | pg_stat_bgwriter.buffers_backend_fsync | 9.1 or later |
| buffers_alloc | bigint | pg_stat_bgwriter.buffers_alloc | |
| stats_reset | timestampz | pg_stat_bgwriter.stats_reset | 9.1 or later |
pgperf.snapshot_pg_stat_activity Table¶
This table stores snapshots of the pg_stat_activity system view which contains the session information.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| datid | oid | pg_stat_activity.datid | |
| datname | name | pg_stat_activity.datname | |
| procpid | int4 | pg_stat_activity.procpid | 9.1 or before |
| pid | int4 | pg_stat_activity.pid | 9.2 or later |
| usesysid | oid | pg_stat_activity.usesysid | |
| usename | name | pg_stat_activity.usename | |
| application_name | text | pg_stat_activity.application_name | 9.0 or later |
| client_addr | inet | pg_stat_activity.client_addr | |
| client_hostname | text | pg_stat_activity.client_hostname | 9.1 or later |
| client_port | int4 | pg_stat_activity.client_port | |
| backend_start | timestamptz | pg_stat_activity.backend_start | |
| xact_start | timestamptz | pg_stat_activity.xact_start | |
| query_start | timestamptz | pg_stat_activity.query_start | |
| state_change | timestamptz | pg_stat_activity.state_change | 9.2 or later |
| waiting | bool | pg_stat_activity.waiting | |
| state | text | pg_stat_activity.state | 9.2 or later |
| current_query | text | pg_stat_activity.current_query | 9.1 or before |
| query | text | pg_stat_activity.query | 9.2 or later |
pgperf.snapshot_pg_locks Table¶
This table stores snapshots of the pg_locks system view which contains the lock information.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| locktype | text | pg_locks.locktype | |
| database | oid | pg_locks.database | |
| relation | oid | pg_locks.relation | |
| page | int4 | pg_locks.page | |
| tuple | int2 | pg_locks.tuple | |
| virtualxid | text | pg_locks.virtualxid | |
| transactionid | xid | pg_locks.transactionid | |
| classid | oid | pg_locks.classid | |
| objid | oid | pg_locks.objid | |
| objsubid | int2 | pg_locks.objsubid | |
| virtualtransaction | text | pg_locks.virtualtransaction | |
| pid | int4 | pg_locks.pid | |
| mode | text | pg_locks.mode | |
| granted | bool | pg_locks.granted | |
| fastpath | bool | pg_locks.fastpath | 9.2 or later |
pgperf.snapshot_pg_statistic Table¶
This table stores snapshots of the pg_statistic system table which contains the optimizer statistics.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| starelid | oid | pg_statistic.starelid | |
| starelname | name | pg_class.relname | |
| staattnum | smallint | pg_statistic.staattnum | |
| staattname | name | pg_attribute.attname | |
| stainherit | boolean | pg_statistic.stainherit | 9.0 or later |
| stanullfrac | real | pg_statistic.stanullfrac | |
| stawidth | integer | pg_statistic.stawidth | |
| stadistinct | real | pg_statistic.stadistinct | |
| stakind1 | smallint | pg_statistic.stakind1 | |
| stakind2 | smallint | pg_statistic.stakind2 | |
| stakind3 | smallint | pg_statistic.stakind3 | |
| stakind4 | smallint | pg_statistic.stakind4 | |
| stakind5 | smallint | pg_statistic.stakind5 | 9.2 or later |
| staop1 | oid | pg_statistic.staop1 | |
| staop2 | oid | pg_statistic.staop2 | |
| staop3 | oid | pg_statistic.staop3 | |
| staop4 | oid | pg_statistic.staop4 | |
| staop5 | oid | pg_statistic.staop5 | 9.2 or later |
| stanumbers1 | real[] | pg_statistic.stanumbers1 | |
| stanumbers2 | real[] | pg_statistic.stanumbers2 | |
| stanumbers3 | real[] | pg_statistic.stanumbers3 | |
| stanumbers4 | real[] | pg_statistic.stanumbers4 | |
| stanumbers5 | real[] | pg_statistic.stanumbers5 | 9.2 or later |
| stavalues1 | text | pg_statistic.stavalues1 | |
| stavalues2 | text | pg_statistic.stavalues2 | |
| stavalues3 | text | pg_statistic.stavalues3 | |
| stavalues4 | text | pg_statistic.stavalues4 | |
| stavalues5 | text | pg_statistic.stavalues5 | 9.2 or later |
pgperf.snapshot_pg_stat_statements Table¶
This table stores snapshots of the pg_stat_statements view which contains the session statistics. This table is available only when the pg_stat_statements module has been installed and enabled.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| userid | oid | pg_stat_statements.userid | |
| dbid | oid | pg_stat_statements.dbid | |
| query | text | pg_stat_statements.query | |
| calls | bigint | pg_stat_statements.calls | |
| total_time | double precision | pg_stat_statements.total_time | |
| rows | bigint | pg_stat_statements.rows | |
| shared_blks_hit | bigint | pg_stat_statements.shared_blks_hit | |
| shared_blks_read | bigint | pg_stat_statements.shared_blks_read | |
| shared_blks_dirtied | bigint | pg_stat_statements.shared_blks_dirtied | 9.2 or later |
| shared_blks_written | bigint | pg_stat_statements.shared_blks_written | |
| local_blks_hit | bigint | pg_stat_statements.local_blks_hit | |
| local_blks_read | bigint | pg_stat_statements.local_blks_read | |
| local_blks_dirtied | bigint | pg_stat_statements.local_blks_dirtied | 9.2 or later |
| local_blks_written | bigint | pg_stat_statements.local_blks_written | |
| temp_blks_read | bigint | pg_stat_statements.temp_blks_read | |
| temp_blks_written | bigint | pg_stat_statements.temp_blks_written | |
| blk_read_time | double precision | pg_stat_statements.blk_read_time | 9.2 or later |
| blk_write_time | double precision | pg_stat_statements.blk_write_time | 9.2 or later |
pgperf.snapshot_pgstattuple Table¶
This table stores snapshots of the result of the pgstattuple() function which gets the table fragmentation statistics. This table is available only when the pgstattuple module has been installed.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| schemaname | name | pg_stat_user_tables.schemaname | |
| relname | name | pg_stat_user_tables.relname | |
| table_len | int8 | pgstattuple().table_len | |
| tuple_count | int8 | pgstattuple().tuple_count | |
| tuple_len | int8 | pgstattuple().tuple_len | |
| tuple_percent | float8 | pgstattuple().tuple_percent | |
| dead_tuple_count | int8 | pgstattuple().dead_tuple_count | |
| dead_tuple_len | int8 | pgstattuple().dead_tuple_len | |
| dead_tuple_percent | float8 | pgstattuple().dead_tuple_percent | |
| free_space | int8 | pgstattuple().free_space | |
| free_percent | float8 | pgstattuple().free_percent |
pgperf.snapshot_pgstatindex Table¶
This table stores snapshots of the result of the pgstatindex() function which gets the index fragmentation statistics. This table is available only when the pgstattuple module has been installed.
| Column Name | Type | Source | Note |
|---|---|---|---|
| sid | integer | Snapshot ID | |
| schemaname | name | pg_stat_user_indexes.schemaname | |
| relname | name | pg_stat_user_indexes.relname | |
| indexrelname | name | pg_stat_user_indexes.indexrelname | |
| version | int4 | pgstatindex().version | |
| tree_level | int4 | pgstatindex().tree_level | |
| index_size | int8 | pgstatindex().index_size | |
| root_block_no | int8 | pgstatindex().root_block_no | |
| internal_pages | int8 | pgstatindex().internal_pages | |
| leaf_pages | int8 | pgstatindex().leaf_pages | |
| empty_pages | int8 | pgstatindex().empty_pages | |
| deleted_pages | int8 | pgstatindex().deleted_pages | |
| avg_leaf_density | float8 | pgstatindex().avg_leaf_density | |
| leaf_fragmentation | float8 | pgstatindex().leaf_fragmentation |