Dumped on 2017-08-08

Index of database - pgtelemetry

View: autovacuum_stats

This provides basic metrics per table in the current database for when autovacuum and analyze were last run (as well as manual maintenance).

autovacuum_stats Structure
F-Key Name Type Description
schemaname name
relname name
last_vacuum timestamp with time zone
age_last_vacuum double precision
vacuum_count bigint
last_autovacuum timestamp with time zone
age_last_autovacuum double precision
autovacuum_count bigint
last_analyze timestamp with time zone
age_last_analyze double precision
analyze_count bigint
last_autoanalyze timestamp with time zone
age_last_autoanalyze double precision
autoanalyze_count bigint 
SELECT pg_stat_user_tables.schemaname
,
    pg_stat_user_tables.relname
,
    pg_stat_user_tables.last_vacuum
,
    date_part
('epoch'::text
     , age
     (now
           ()
           , pg_stat_user_tables.last_vacuum
     )
) AS age_last_vacuum
,
    pg_stat_user_tables.vacuum_count
,
    pg_stat_user_tables.last_autovacuum
,
    date_part
('epoch'::text
     , age
     (now
           ()
           , pg_stat_user_tables.last_autovacuum
     )
) AS age_last_autovacuum
,
    pg_stat_user_tables.autovacuum_count
,
    pg_stat_user_tables.last_analyze
,
    date_part
('epoch'::text
     , age
     (now
           ()
           , pg_stat_user_tables.last_analyze
     )
) AS age_last_analyze
,
    pg_stat_user_tables.analyze_count
,
    pg_stat_user_tables.last_autoanalyze
,
    date_part
('epoch'::text
     , age
     (now
           ()
           , pg_stat_user_tables.last_autoanalyze
     )
) AS age_last_autoanalyze
,
    pg_stat_user_tables.autoanalyze_count
   
FROM pg_stat_user_tables;

Index - Schema pgtelemetry

View: catalog_total_size

catalog_total_size Structure
F-Key Name Type Description
oid oid
relation regclass
inclusive_bytes bigint
inclusive_size text
exclusive_bytes bigint
exclusive_size text 
SELECT c.oid
,
    
(c.oid)::regclass AS relation
,
    pg_total_relation_size
(
     (c.oid)::regclass
) AS inclusive_bytes
,
    pg_size_pretty
(pg_total_relation_size
     (
           (c.oid)::regclass
     )
) AS inclusive_size
,
    pg_relation_size
(
     (c.oid)::regclass
) AS exclusive_bytes
,
    pg_size_pretty
(pg_relation_size
     (
           (c.oid)::regclass
     )
) AS exclusive_size
   
FROM (pg_class c
     
  JOIN pg_namespace n 
    ON (
           (c.relnamespace = n.oid)
     )
)
  
WHERE (
     (c.relkind = 'r'::"char")
   AND (n.nspname = ANY 
           (ARRAY['pg_catalog'::name
                 ,'information_schema'::name]
           )
     )
);

Index - Schema pgtelemetry

View: connections_by_application

This gives you the number of connections (cluster-wide) by application name. By default the application name is the program name that connected to the db.

connections_by_application Structure
F-Key Name Type Description
application_name text
count bigint 
SELECT pg_stat_activity.application_name
,
    count
(*) AS count
   
FROM pg_stat_activity
  
GROUP BY pg_stat_activity.application_name;

Index - Schema pgtelemetry

View: connections_by_ip_source

This is a cluster-wide breakdown of connections by IP source. Between this and the applicaiton_name it is a good indication of where server laod is coming from as well as porblems like connection handle leaks.

connections_by_ip_source Structure
F-Key Name Type Description
client_addr inet
count bigint 
SELECT pg_stat_activity.client_addr
,
    count
(*) AS count
   
FROM pg_stat_activity
  
GROUP BY pg_stat_activity.client_addr;

Index - Schema pgtelemetry

View: connections_by_state

This gives you the number of connections (cluster-wide) by state (active, idle, idle in transaction, etc). If the query is active but is waiting on a lock or latch, we change this to 'waiting.'

connections_by_state Structure
F-Key Name Type Description
state text
count bigint 
SELECT
        CASE
            WHEN 
(pg_stat_activity.wait_event IS NULL) THEN pg_stat_activity.state
            ELSE 'waiting'::text
        END AS state
,
    count
(*) AS count
   
FROM pg_stat_activity
  
GROUP BY
        CASE
            WHEN 
(pg_stat_activity.wait_event IS NULL) THEN pg_stat_activity.state
            ELSE 'waiting'::text
        END;

Index - Schema pgtelemetry

View: database_size

This provides cluser-wide size statistics of databases.

database_size Structure
F-Key Name Type Description
name name
bytes bigint
size text 
SELECT pg_database.datname AS name
,
    pg_database_size
(pg_database.oid) AS bytes
,
    pg_size_pretty
(pg_database_size
     (pg_database.oid)
) AS size
   
FROM pg_database;

Index - Schema pgtelemetry

View: index_size

This table is most useful in tracking down questions of bloat, fill factor, and performance of GIN indexes among other things.

index_size Structure
F-Key Name Type Description
oid oid
index regclass
bytes bigint
size text 
SELECT c.oid
,
    
(c.oid)::regclass AS index
,
    pg_relation_size
(
     (c.oid)::regclass
) AS bytes
,
    pg_size_pretty
(pg_relation_size
     (
           (c.oid)::regclass
     )
) AS size
   
FROM (pg_class c
     
  JOIN pg_namespace n 
    ON (
           (c.relnamespace = n.oid)
     )
)
  
WHERE (
     (c.relkind = 'i'::"char")
   AND (n.nspname <> ALL 
           (ARRAY['pg_toast'::name
                 ,'pg_catalog'::name
                 ,'information_schema'::name]
           )
     )
);

Index - Schema pgtelemetry

View: locks_by_mode

This view provides cluster-wide statistics on locks by lock mode (access share vs exclusive for example). Combined with the locks_by_type view, this view provides a some opportunities to spot locking problems.

locks_by_mode Structure
F-Key Name Type Description
mode text
count bigint 
SELECT pg_locks.mode
,
    count
(*) AS count
   
FROM pg_locks
  
GROUP BY pg_locks.mode;

Index - Schema pgtelemetry

View: locks_by_type

This view provides cluster-wide statistics on what sorts of locks are present. These incude advisory locks, relation, tuple, transaction id, etc. This can be helpful in determining where the locks are coming from.

locks_by_type Structure
F-Key Name Type Description
locktype text
count bigint 
SELECT pg_locks.locktype
,
    count
(*) AS count
   
FROM pg_locks
  
GROUP BY pg_locks.locktype;

Index - Schema pgtelemetry

View: longest_running_active_queries

This view is intended to be typically used by administrators in determining which queries to focus on. However it can be used for reporting and alerting as well.

longest_running_active_queries Structure
F-Key Name Type Description
application_name text
state text
wait_event_type text
wait_event text
query text
pid integer
client_addr inet
running_for interval 
SELECT pg_stat_activity.application_name
,
    pg_stat_activity.state
,
    pg_stat_activity.wait_event_type
,
    pg_stat_activity.wait_event
,
    pg_stat_activity.query
,
    pg_stat_activity.pid
,
    pg_stat_activity.client_addr
,
    age
(now
     ()
     , pg_stat_activity.query_start
) AS running_for
   
FROM pg_stat_activity
  
WHERE (pg_stat_activity.state = 'active'::text)
  
ORDER BY (age
     (now
           ()
           , pg_stat_activity.query_start
     )
) DESC;

Index - Schema pgtelemetry

Table: pg_telemetry_wal_log

This table logs the times and results of wal telemetry readings so that deltas can be calculated. At least one row must be present to get any useful data out of the wal_telemetry() function at all. If you get one telemetry entry a minute, over the course of a year you will get just over half a million entries. These are indexed on both epoch and timestamp so access is not impaired, but if you want ot purge, be careful to leave at least one entry at the end. You can also process these as a time series using WINDOW functions like lag.

pg_telemetry_wal_log Structure
F-Key Name Type Description
run_time numeric UNIQUE
timestamp timestamp without time zone UNIQUE
lsn pg_lsn

Index - Schema pgtelemetry

View: relation_toast_size

This measures the amount of space in a relation's TOAST tables. These are populated when data exceeds what can be reasonably stored inline in the main heap pages. You would expect to see this non-zero where you have large fields being stored, particularly arrays of composite types. Performance-wise moving data to TOAST improves sequential scans where the data is not required (count(*) for example) at the cost of making the data that has been moved far more expensive to retrieve and process.

relation_toast_size Structure
F-Key Name Type Description
oid oid
relation regclass
exclusive_bytes bigint
exclusive_size text 
SELECT c.oid
,
    
(c.oid)::regclass AS relation
,
    pg_relation_size
(
     (t.oid)::regclass
) AS exclusive_bytes
,
    pg_size_pretty
(pg_relation_size
     (
           (t.oid)::regclass
     )
) AS exclusive_size
   
FROM (
     (pg_class c
     
        JOIN pg_class t 
          ON (
                 (
                       (t.relname)::text = 
                       ('pg_toast_'::text || 
                             (c.oid)::text
                       )
                 )
           )
     )
     
  JOIN pg_namespace n 
    ON (
           (c.relnamespace = n.oid)
     )
);

Index - Schema pgtelemetry

View: relation_total_size

This view provides basic information on relation size in PostgreSQL system tables (those in pg_catalog and information_schema). The inclusive metrics show the relation along with indexes and TOAST. The exclusiove metrics show without these things. The bytes metrics are intended for graph drawing, while the sizes are there for administrators who want to quickly query this information and make decisions.

relation_total_size Structure
F-Key Name Type Description
oid oid
relation regclass
inclusive_bytes bigint
inclusive_size text
exclusive_bytes bigint
exclusive_size text 
SELECT c.oid
,
    
(c.oid)::regclass AS relation
,
    pg_total_relation_size
(
     (c.oid)::regclass
) AS inclusive_bytes
,
    pg_size_pretty
(pg_total_relation_size
     (
           (c.oid)::regclass
     )
) AS inclusive_size
,
    pg_relation_size
(
     (c.oid)::regclass
) AS exclusive_bytes
,
    pg_size_pretty
(pg_relation_size
     (
           (c.oid)::regclass
     )
) AS exclusive_size
   
FROM (pg_class c
     
  JOIN pg_namespace n 
    ON (
           (c.relnamespace = n.oid)
     )
)
  
WHERE (
     (c.relkind = 'r'::"char")
   AND (n.nspname <> ALL 
           (ARRAY['pg_toast'::name
                 ,'pg_catalog'::name
                 ,'information_schema'::name]
           )
     )
);

Index - Schema pgtelemetry

View: replication_slot_lag

This view monitors lag on downstream slots. It compares the last sent wal segment to the current known wal location. For master database, the current wal location is self-explanatory. For replicas we use the last received WAL location instead. Note that replicas can have replication slots for downstream replication tracking.

replication_slot_lag Structure
F-Key Name Type Description
slot_name name
slot_type text
active boolean
restart_lsn pg_lsn
full_data jsonb
querytime timestamp with time zone
pg_current_xlog_location pg_lsn
current_lag_bytes numeric 
SELECT s.slot_name
,
    s.slot_type
,
    s.active
,
    s.restart_lsn
,
    to_jsonb
(s.*) AS full_data
,
    now
() AS querytime
,
        CASE
            WHEN pg_is_in_recovery
() THEN pg_last_xlog_replay_location
()
            ELSE pg_current_xlog_location
()
        END AS pg_current_xlog_location
,
        CASE
            WHEN pg_is_in_recovery
() THEN 
(NULL::integer)::numeric
            ELSE 
(pg_current_xlog_location
     () - s.restart_lsn
)
        END AS current_lag_bytes
   
FROM pg_replication_slots s
  
ORDER BY s.slot_name;

Index - Schema pgtelemetry

View: statement_query_buffers

This gives aggregated of stats for a given query (cluster-wide) per query and database name. This view provides low-level IO statistics.

statement_query_buffers Structure
F-Key Name Type Description
datname name
queryid bigint
query text
sum numeric
shared_blks_hit numeric
shared_blks_read numeric
shared_blks_dirtied numeric
shared_blks_written numeric
tmp_blkd_read numeric
tmp_blkd_written numeric 
SELECT d.datname
,
    pg_stat_statements.queryid
,
    pg_stat_statements.query
,
    sum
(pg_stat_statements.calls) AS sum
,
    sum
(pg_stat_statements.shared_blks_hit) AS shared_blks_hit
,
    sum
(pg_stat_statements.shared_blks_read) AS shared_blks_read
,
    sum
(pg_stat_statements.shared_blks_dirtied) AS shared_blks_dirtied
,
    sum
(pg_stat_statements.shared_blks_written) AS shared_blks_written
,
    sum
(pg_stat_statements.temp_blks_read) AS tmp_blkd_read
,
    sum
(pg_stat_statements.temp_blks_written) AS tmp_blkd_written
   
FROM (pg_stat_statements
     
  JOIN pg_database d 
    ON (
           (d.oid = pg_stat_statements.dbid)
     )
)
  
GROUP BY d.datname
, pg_stat_statements.queryid
, pg_stat_statements.query;

Index - Schema pgtelemetry

View: statement_query_rows_time

This gives aggregated of stats for a given query (cluster-wide) per query and database name. This view provides high level timing and row statistics.

statement_query_rows_time Structure
F-Key Name Type Description
datname name
queryid bigint
query text
calls numeric
total_time double precision
rows numeric 
SELECT d.datname
,
    pg_stat_statements.queryid
,
    pg_stat_statements.query
,
    sum
(pg_stat_statements.calls) AS calls
,
    sum
(pg_stat_statements.total_time) AS total_time
,
    sum
(pg_stat_statements.rows) AS rows
   
FROM (pg_stat_statements
     
  JOIN pg_database d 
    ON (
           (d.oid = pg_stat_statements.dbid)
     )
)
  
GROUP BY d.datname
, pg_stat_statements.queryid
, pg_stat_statements.query;

Index - Schema pgtelemetry

View: tablespace_size

This provides database-cluster-wide statistics on disk usage by tablespace. Note that tablespaces and databases are orthogonal. Typically if you are running out of disk space, you want to check this one first, then database_size and then the size of the relations in the largest database in that order.

tablespace_size Structure
F-Key Name Type Description
name name
bytes bigint
size text 
SELECT pg_tablespace.spcname AS name
,
    pg_tablespace_size
(pg_tablespace.oid) AS bytes
,
    pg_size_pretty
(pg_tablespace_size
     (pg_tablespace.oid)
) AS size
   
FROM pg_tablespace;

Index - Schema pgtelemetry

View: tuple_access_stats

This view provides statistcs for scans (index and sequential) along with numbers of tuples updated through various means. It allows you to get a pretty good idea of where you may need indexes or where IO-related problems may be coming from.

tuple_access_stats Structure
F-Key Name Type Description
schemaname name
relname name
seq_scan bigint
seq_tup_read bigint
idx_scan bigint
idx_tup_fetch bigint
n_tup_ins bigint
n_tup_upd bigint
n_tup_del bigint
n_tup_hot_upd bigint
n_live_tup bigint
n_dead_tup bigint
n_mod_since_analyze bigint 
SELECT pg_stat_user_tables.schemaname
,
    pg_stat_user_tables.relname
,
    pg_stat_user_tables.seq_scan
,
    pg_stat_user_tables.seq_tup_read
,
    pg_stat_user_tables.idx_scan
,
    pg_stat_user_tables.idx_tup_fetch
,
    pg_stat_user_tables.n_tup_ins
,
    pg_stat_user_tables.n_tup_upd
,
    pg_stat_user_tables.n_tup_del
,
    pg_stat_user_tables.n_tup_hot_upd
,
    pg_stat_user_tables.n_live_tup
,
    pg_stat_user_tables.n_dead_tup
,
    pg_stat_user_tables.n_mod_since_analyze
   
FROM pg_stat_user_tables;

Index - Schema pgtelemetry

View: waiting_connections_by_event_type

This view provides basic, cluster-global, statistics on why queries are waiting on other queries.

waiting_connections_by_event_type Structure
F-Key Name Type Description
wait_event_type text
count bigint 
SELECT pg_stat_activity.wait_event_type
,
    count
(*) AS count
   
FROM pg_stat_activity
  
WHERE (pg_stat_activity.wait_event IS NOT NULL)
  
GROUP BY pg_stat_activity.wait_event_type;

Index - Schema pgtelemetry

Function: wal_telemetry()

Returns: SET OF record

Language: SQL

The wal_telemetry() function checks the current wal location and compares with the last entry in the pg_telemetry_wal_log. It then provides for you both current and last data, and the differences between them. These include bytes elapsed and seconds elapsed, and bytes per sec. The function is designed so that you can export delta information to a monitoring solution such as munin or prometheus without the latter having to know anything about lsn representation or losing information in the process. This function cnnnot be run on a replica though you can analyxe the wal logs. 

WITH current_record AS (
       insert into pg_telemetry_wal_log
       select extract('epoch' from now()), now(),
              case when pg_is_in_recovery()
                   then pg_last_xlog_replay_location()
                   else pg_current_xlog_location() end as wal_location
       returning *
   )
   select c.run_time as current_epoch, l.run_time as last_epoch,
          c.run_time - l.run_time as secs_elapsed,
          c.lsn as current_lsn, l.lsn as last_lsn,
          c.lsn - l.lsn as bytes_elapsed,
          (c.lsn - l.lsn)::numeric / (c.run_time - l.run_time) as bytes_per_sec
     FROM current_record c,
  lateral (select * from pg_telemetry_wal_log where run_time < c.run_time
            order by run_time desc limit 1) l;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict