| 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. |
| |
| .. csv-table:: |
| :header-rows: 1 |
| |
| 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 |
| ===================== ================ ====================================== =========== |
| |
