Service Instance Metrics

This page describes the metrics used to monitor state of an a9s PostgreSQL. Service Instance. For further information on how to monitor an a9s Service Instance, see the Set up Monitoring section of the Application Developer's documentation.

Metric groups

Custom parameter name	Suggested (interval in seconds)	Default value (interval in seconds)	Description
metric_pg_database_size	300	300	This group lets you see how quickly your database is growing.
metric_pg_stat_all_indexes	30	0	This group provides status information on all indexes.
metric_pg_statio_all_indexes	60	0	This group provides information on index usage for all indexes.
metric_pg_statio_all_tables	30	0	This group provides information on table usage for all tables.
metric_pg_stat_all_tables	120	0	This group provides the status of all tables.
metric_pg_stat_archiver_table	300	0	This group provides the archival status. Useful with continuous archiving enabled.
metric_pg_stat_database	120	0	This group provides the general status of each database.
metric_pg_stat_database_conflicts	300	0	This group provides insights on database conflicts.
metric_pg_replication	10	10	This group provides the status of replication. Useful when the Service Instance is a replica set.
metric_pg_node_status	10	10	This group provides metrics based on node state

Metric group patterns

Each metric group has a set of patterns that describe the metrics contained in each group.

note

These metrics are dynamic between the PostgreSQL versions, therefore some of them might differ based on the PostgreSQL version that is currently running on the Service Instance. In order to be sure, search into the documentation link provided in the metric item and confirm the information based on PostgreSQL metric and PostgreSQL version.

metric_pg_database_size

Metric Id	Type	Description
pg_database_size	Bigint (Bytes)	Database size.

*.*.postgres.*.*.*.*.database.*.pg_database_size

metric_pg_stat_all_indexes

The pg_stat_all_indexes view will contain one row for each index in the current database, showing statistics about accesses to that specific index. The pg_stat_user_indexes and pg_stat_sys_indexes views contain the same information, but filtered to only show user and system indexes respectively.

Metric Id	Type	Description
idx_scan	Bigint	Number of index scans initiated on this index. documentation
idx_tup_read	Bigint	Number of index entries returned by scans on this index. documentation
idx_tup_fetch	Bigint	Number of live table rows fetched by simple index scans using this index. documentation

Metrics

*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_scan
*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_tup_read
*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_tup_fetch

metric_pg_statio_all_indexes

The pg_statio_all_indexes view will contain one row for each index in the current database, showing statistics about I/O on that specific index. The pg_statio_user_indexes and pg_statio_sys_indexes views contain the same information, but filtered to only show user and system indexes respectively.

Metric Id	type	Description
idx_blks_read	Bigint	Number of disk blocks read from all indexes on this table. documentation
idx_blks_hit	Bigint	Number of buffer hits in all indexes on this table. documentation

Metrics

*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_blks_read
*.*.postgres.*.*.*.*.database.*.table.*.*.index.*.*.*.idx_blks_hit

metric_pg_statio_all_tables

The pg_statio_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about I/O on that specific table. The pg_statio_user_tables and pg_statio_sys_tables views contain the same information, but filtered to only show user and system tables respectively.

Metric Id	type	Description
heap_blks_read	Bigint	Number of disk blocks read from this table.documentation
heap_blks_hit	Bigint	Number of buffer hits in this table. documentation
idx_blks_read	Bigint	Number of disk blocks read from all indexes on this table. documentation
idx_blks_hit	Bigint	Number of buffer hits in all indexes on this table. documentation
toast_blks_read	Bigint	Number of disk blocks read from this table's TOAST table (if any). documentation
toast_blks_hit	Bigint	Number of buffer hits in this table's TOAST table (if any). documentation
tidx_blks_read	Bigint	Number of disk blocks read from this table's TOAST table indexes (if any). documentation
tidx_blks_hit	Bigint	Number of buffer hits in this table's TOAST table indexes (if any). documentation

Metrics

*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.heap_blks_read
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.heap_blks_hit
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.idx_blks_read
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.idx_blks_hit
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.toast_blks_read
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.toast_blks_hit
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.tidx_blks_read
*.*.postgres.*.*.*.*.database.*.io_table.*.*.*.tidx_blks_hit

metric_pg_stat_all_tables

The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about accesses to that specific table. The pg_stat_user_tables and pg_stat_sys_tables views contain the same information, but filtered to only show user and system tables respectively.

Metric Id	type	Description
seq_scan	Bigint	Number of sequential scans initiated on this table. documentation
seq_tup_read	Bigint	Number of live rows fetched by sequential scans. documentation
idx_scan	Bigint	Number of index scans initiated on this table. documentation
idx_tup_fetch	Bigint	Number of live rows fetched by index scans. documentation
n_tup_ins	Bigint	Number of rows inserted. documentation
n_tup_upd	Bigint	Number of rows updated (includes HOT updated rows for < 17 and includes row updates counted in n_tup_hot_upd and n_tup_newpage_upd, and remaining non-HOT updates for ≥ 17). documentation
n_tup_del	Bigint	Number of rows deleted. documentation
n_tup_hot_upd	Bigint	Number of rows HOT updated (i.e., with no separate index update required). documentation
n_live_tup	Bigint	Estimated number of live rows. documentation
n_dead_tup	Bigint	Estimated number of dead rows. documentation
last_vacuum	Timestamp With Time Zone	Last time at which this table was manually vacuumed (not counting VACUUM FULL). documentation
last_autovacuum	Timestamp With Time Zone	Last time at which this table was vacuumed by the autovacuum daemon. documentation
last_analyze	Timestamp With Time Zone	Last time at which this table was manually analyzed. documentation
last_autoanalyze	Timestamp With Time Zone	Last time at which this table was analyzed by the autovacuum daemon. documentation
vacuum_count	Bigint	Number of times this table has been manually vacuumed (not counting VACUUM FULL). documentation
autovacuum_count	Bigint	Number of times this table has been vacuumed by the autovacuum daemon. documentation
analyze_count	Bigint	Number of times this table has been manually analyzed. documentation
autoanalyze_count	Bigint	Number of times this table has been analyzed by the autovacuum daemon. documentation

Metrics

*.*.postgres.*.*.*.*.database.*.table.*.*.*.seq_scan
*.*.postgres.*.*.*.*.database.*.table.*.*.*.seq_tup_read
*.*.postgres.*.*.*.*.database.*.table.*.*.*.idx_scan
*.*.postgres.*.*.*.*.database.*.table.*.*.*.idx_tup_fetch
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_tup_ins
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_tup_upd
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_tup_del
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_tup_hot_upd
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_live_tup
*.*.postgres.*.*.*.*.database.*.table.*.*.*.n_dead_tup
*.*.postgres.*.*.*.*.database.*.table.*.*.*.last_vacuum
*.*.postgres.*.*.*.*.database.*.table.*.*.*.last_autovacuum
*.*.postgres.*.*.*.*.database.*.table.*.*.*.last_analyze
*.*.postgres.*.*.*.*.database.*.table.*.*.*.last_autoanalyze
*.*.postgres.*.*.*.*.database.*.table.*.*.*.vacuum_count
*.*.postgres.*.*.*.*.database.*.table.*.*.*.autovacuum_count
*.*.postgres.*.*.*.*.database.*.table.*.*.*.analyze_count
*.*.postgres.*.*.*.*.database.*.table.*.*.*.autoanalyze_count

metric_pg_stat_archiver_table

The pg_stat_archiver view will always have a single row, containing data about the archiver process of the cluster.

Metric Id	type	Description
failed_count	Bigint	Number of failed attempts for archiving WAL files. documentation
archived_count	Text	Number of WAL files that have been successfully archived. documentation
last_archived_time_in_seconds	Integer (seconds)	Time of the last successful archive operation. documentation
wal_files_count	Integer	Amount of WAL files.
last_archive_successful	Boolean (0 = success/1 = failed)`	Indicate if the last archive operation was successful.

Metrics

*.*.postgres.*.*.*.*.stat_archiver.failed_count
*.*.postgres.*.*.*.*.stat_archiver.archived_count
*.*.postgres.*.*.*.*.stat_archiver.last_archived_time_in_seconds
*.*.postgres.*.*.*.*.stat_archiver.wal_files_count
*.*.postgres.*.*.*.*.stat_archiver.last_archive_successful

metric_pg_stat_database

The pg_stat_database view will contain one row for each database in the cluster, showing database-wide statistics.

Metric Id	type	Description	Versions
numbackends	Integer	Number of backends currently connected to this database. This is the only column in this view that returns a value reflecting current state; all other columns return the accumulated values since the last reset. documentation	all
xact_commit	Bigint	Number of transactions in this database that have been committed. documentation	all
xact_rollback	Bigint	Number of transactions in this database that have been rolled back. documentation	all
blks_read	Bigint	Number of disk blocks read in this database. documentation	all
blks_hit	Bigint	Number of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache). documentation	all
tup_returned	Bigint	Number of rows returned by queries in this database. documentation	all
tup_fetched	Bigint	Number of rows fetched by queries in this database. documentation	all
tup_inserted	Bigint	Number of rows inserted by queries in this database. documentation	all
tup_updated	Bigint	Number of rows updated by queries in this database. documentation	all
tup_deleted	Bigint	Number of rows deleted by queries in this database. documentation	all
conflicts	Bigint	Number of queries canceled due to conflicts with recovery in this database. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.) documentation	all
temp_files	Bigint	Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting. documentation	all
temp_bytes	Bigint	Total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting. documentation	all
deadlocks	Bigint	Number of deadlocks detected in this database. documentation	all
blk_read_time	Double Precision	Time spent reading data file blocks by backends in this database, in milliseconds. documentation	all
blk_write_time	Double Precision	Time spent writing data file blocks by backends in this database, in milliseconds. documentation	all
session_time	Double Precision	Time spent by database sessions in this database, in milliseconds (note that statistics are only updated when the state of a session changes, so if sessions have been idle for a long time, this idle time won't be included).	documentation
active_time	Double Precision	Time spent executing SQL statements in this database, in milliseconds (this corresponds to the states active and fastpath function call in pg_stat_activity).	documentation
idle_in_transaction_time	Double Precision	Time spent idling while in a transaction in this database, in milliseconds (this corresponds to the states idle in transaction and idle in transaction (aborted) in pg_stat_activity).	documentation
sessions	Bigint	Total number of sessions established to this database.	documentation
sessions_abandoned	Bigint	Number of database sessions to this database that were terminated because connection to the client was lost.	documentation
sessions_fatal	Bigint	Number of database sessions to this database that were terminated by fatal errors.	documentation
sessions_killed	Bigint	Number of database sessions to this database that were terminated by operator intervention.	documentation
stats_reset	Timestamp With Time Zone	Time at which these statistics were last reset. documentation	≥ 15

Metrics

*.*.postgres.*.*.*.*.database.*.*.numbackends
*.*.postgres.*.*.*.*.database.*.*.xact_commit
*.*.postgres.*.*.*.*.database.*.*.xact_rollback
*.*.postgres.*.*.*.*.database.*.*.blks_read
*.*.postgres.*.*.*.*.database.*.*.blks_hit
*.*.postgres.*.*.*.*.database.*.*.tup_returned
*.*.postgres.*.*.*.*.database.*.*.tup_fetched
*.*.postgres.*.*.*.*.database.*.*.tup_inserted
*.*.postgres.*.*.*.*.database.*.*.tup_updated
*.*.postgres.*.*.*.*.database.*.*.tup_deleted
*.*.postgres.*.*.*.*.database.*.*.conflicts
*.*.postgres.*.*.*.*.database.*.*.temp_files
*.*.postgres.*.*.*.*.database.*.*.temp_bytes
*.*.postgres.*.*.*.*.database.*.*.deadlocks
*.*.postgres.*.*.*.*.database.*.*.blk_read_time
*.*.postgres.*.*.*.*.database.*.*.blk_write_time
*.*.postgres.*.*.*.*.database.*.*.session_time
*.*.postgres.*.*.*.*.database.*.*.active_time
*.*.postgres.*.*.*.*.database.*.*.idle_in_transaction_time
*.*.postgres.*.*.*.*.database.*.*.sessions
*.*.postgres.*.*.*.*.database.*.*.sessions_abandoned
*.*.postgres.*.*.*.*.database.*.*.sessions_fatal
*.*.postgres.*.*.*.*.database.*.*.sessions_killed
*.*.postgres.*.*.*.*.database.*.*.stats_reset

metric_pg_stat_database_conflicts

The pg_stat_database_conflicts view will contain one row per database, showing database-wide statistics about query cancels occurring due to conflicts with recovery on standby servers. This view will only contain information on standby servers, since conflicts do not occur on master servers.

Metric Id	type	Description
confl_tablespace	Bigint	Number of queries in this database that have been canceled due to dropped tablespaces. documentation
confl_lock	Bigint	Number of queries in this database that have been canceled due to lock timeouts. documentation
confl_snapshot	Bigint	Number of queries in this database that have been canceled due to old snapshots. documentation
confl_bufferpin	Bigint	Number of queries in this database that have been canceled due to pinned buffers. documentation
confl_deadlock	Bigint	Number of queries in this database that have been canceled due to deadlocks. documentation

Metrics

*.*.postgres.*.*.*.*.conflicts.*.*.confl_tablespace
*.*.postgres.*.*.*.*.conflicts.*.*.confl_lock
*.*.postgres.*.*.*.*.conflicts.*.*.confl_snapshot
*.*.postgres.*.*.*.*.conflicts.*.*.confl_bufferpin
*.*.postgres.*.*.*.*.conflicts.*.*.confl_deadlock

metric_pg_replication

Metric Id	type	Description
replication.wal_receiver_count	Integer	Amount WAL receiver process running the master what sends data to the slave.
replication.wal_sender_count	Integer	Amount of WAL sender process that run at the primary node. Each replication has its own.
replication.replication_lag	Integer (seconds)	Amount of time since the pg_last_xact_replay_timestamp, that is the last transaction replayed during recovery.
replication.replication_slots_count	Integer	The same as pg_replication_slots PostgreSQL property that this view provides a listing of all replication slots that currently exist on the database cluster. documentation
replication.inactive_replication_slots_count	Integer	The amount of replication_slots_count in inactive state.
replication.cluster_replication_slots_count	Integer	The amount of replication_slots_count that marches with the regex /^repmgr_slot_[0-9]+$/.
ha.master	Integer (1 = true/0 = false)	If it is a master node.
ha.standby	Integer (1 = true/0 = false)	If it is a slave node.
ha.wal_dir_size	Integer (Bytes)	Size of the WAL directory.

Metrics

*.*.postgres.*.*.*.*.replication.wal_receiver_count
*.*.postgres.*.*.*.*.replication.wal_sender_count
*.*.postgres.*.*.*.*.replication.replication_lag
*.*.postgres.*.*.*.*.replication.replication_slots_count
*.*.postgres.*.*.*.*.replication.inactive_replication_slots_count
*.*.postgres.*.*.*.*.replication.cluster_replication_slots_count
*.*.postgres.*.*.*.*.ha.master
*.*.postgres.*.*.*.*.ha.standby
*.*.postgres.*.*.*.*.ha.wal_dir_size

metric_pg_node_status

*.*.postgres.*.*.*.*.postgresql_info_status.blocked

Description of Metrics:

postgresql_info_status.blocked indicates if any of the nodes has blocked status that means that this node doesn't participate in cluster life. This metric can be equal:

• 1 - blocked
• 0 - unblocked

Configuring metrics databases

By setting the metric_databases custom parameter on a Service Instance it is possible to control which database metrics are generated. The metric_databases parameter expects an array of database names. If the parameter is not set or the array is set to empty, then metrics will be collected for all databases.

PostgreSQL Extensions

Create or Drop Extensions

It is possible to install PostgreSQL extensions with the Cloud Foundry CLI and additional configuration parameters:

Commands

cf create-service a9s-postgresql PLAN_NAME INSTANCE_NAME -c '{"install_plugins": ["<plugin_name>"]}'

cf update-service INSTANCE_NAME -c '{"install_plugins": ["<plugin_name>"]}'

cf update-service INSTANCE_NAME -c '{"delete_plugins": ["<plugin_name>"]}'

Available Extensions

The following PostgreSQL extensions are available:

• citext
• fuzzystrmatch
• hstore
• ltree
• pg_stat_statements
• pg_squeeze
• pg_trgm
• pgcrypto
• postgis
• uuid-ossp
• vector

Default Extensions

There are PostgreSQL extensions that are always installed and it's also not possible to delete them. The following PostgreSQL extensions are set as default extensions:

• pgcrypto
• citext

These two extensions are needed to have a functional replication.

pg_squeeze Requirements and Usage

info

a9s PostgreSQL 13 version does not support pg_squeeze extension.

Usage of the pg_squeeze extension demands logical decoding permissions, and at least one available replication slot. To ensure that it functions correctly, the a9s PostgreSQL Service Instance must be created with the following Custom Parameters:

• max_replication_slots: To ensure this plugin functions correctly, it is recommended to increase the current value of the max_replication_slots parameter by 1. For additional information, see max_replication_slots. To find the current value:
  • If the max_replication_slots value was previously set, please refer to the Getting a Custom Parameter for a Service Instance documentation to retrieve this information.
  • If the max_replication_slots value was not set, the a9s PostgreSQL Service Instance will be using the default value. For more information, see Custom Parameters - max_replication_slots.
• wal_level_logical: This parameter must be set to true. For more information, see Custom Parameters - wal_level_logical.

caution

If these custom parameters are not set as above, the pg_squeeze extension will not work as expected.

The custom parameters should be set at instance creation following the instructions above. For example:

# If the `max_replication_slots` was 0.

cf cs <service-type> <service-plan> <service-name> \
  -c '{"install_plugins": ["pg_squeeze"], "max_replication_slots": 1, "wal_level_logical": true }'

To use the squeeze feature, it is necessary to call the squeeze.fn_squeezer function, like in the following this:

"SELECT squeeze.fn_squeezer('<SCHEMA>','<TABLE_NAME>');"

Where <TABLE_NAME> is the name of the table to be compressed and the <SCHEMA> is the schema when of the table.

Example:

"SELECT squeeze.fn_squeezer((SELECT SPLIT_PART(setting, ', ',2) FROM pg_settings WHERE name = 'search_path'), 'table');"

The fn_squeezer function is a wrapper on the squeeze_table function provided by pg_squeeze where you can provide the schema and the table. The squeeze_table function should not be called directly. Use the fn_squeezer function instead.

The extension uses the squeeze schema to store the fn_squeezer function and some tables with internal data like usage and error logs. This schema should not be used by the user.

PostgreSQL statistics collector

You can enable support for the statistics collector feature (please note that this feature is PostgreSQL version specific). This feature allows you to retrieve information about the current status of the PostgreSQL cluster. It includes information about currently active queries and other useful informations. For each statistics collector view another one is created by a PostgreSQL super user (with the super users' access privileges) that has the same name with a prefix prepended.

Example: For pg_stat_activity the associated view is called ds_pg_stat_activity. You can set the value of the prefix as you prefer. Read-only access to the views created by this feature is given to every user allowed to login on the cluster via the public schema.

You need to connect to the postgres database with your service bindings in order to use those views.

This feature is disabled by default. You can ask your Platform Operator to enable this feature for your Service Instance.

Enable AppDynamics support

Enable AppDynamics support to create a view called pg_stat_activity_allusers which is accessible to all users as described here.

This feature is disabled by default. You can ask your Platform Operator to enable this feature for your Service Instance.