--- title: "Collecting metrics with PostgreSQL monitoring tools" description: "Learn how to query and collect key PostgreSQL activity metrics." author: "Emily Chang" date: 2017-12-15 tags: ["database monitoring", "postgresql", "postgres", "sql"] blog_type_id: the-monitor locale: en --- As explained in [Part 1](https://www.datadoghq.com/blog/postgresql-monitoring.md) of this series, PostgreSQL provides a few categories of key metrics to help users track their databases' health and performance. PostgreSQL's built-in [statistics collector] automatically aggregates most of these metrics internally, so you'll simply need to query predefined [statistics views](#the-postgresql-statistics-collector) in order to start gaining more visibility into your databases. Because some of the metrics mentioned in Part 1 are not accessible through these statistics views, they will need to be collected through other sources, as explained in [a later section](#querying-other-postgresql-statistics). In this post, we will show you how to access key metrics from PostgreSQL (through the statistics collector and other native sources), and with an open source, dedicated monitoring tool. ## The PostgreSQL statistics collector The PostgreSQL statistics collector enables users to access most of the metrics described in [Part 1](https://www.datadoghq.com/blog/postgresql-monitoring.md), by querying several key [statistics views](https://www.postgresql.org/docs/current/static/tutorial-views.html), including: - [`pg_stat_database`](#pg_stat_database) (displays one row per database) - [`pg_stat_user_tables`](#pg_stat_user_tables) (one row per table in the current database) - [`pg_stat_user_indexes`](#pg_stat_user_indexes) (one row per index in the current database) - [`pg_stat_bgwriter`](#pg_stat_bgwriter) (only shows one row, since there is only one background writer process) - [`pg_statio_user_tables`](#pg_statio_user_tables) (one row per table in the current database) The collector aggregates statistics on a per-table, per-database, or per-index basis, depending on the metric. You can dig deeper into each statistics view's actual query language by looking at the [system views source code](https://github.com/postgres/postgres/blob/master/src/backend/catalog/system_views.sql#L507). For example, the [code for `pg_stat_database`](https://github.com/postgres/postgres/blob/master/src/backend/catalog/system_views.sql#L800) indicates that it queries the number of connections, deadlocks, and tuples/rows fetched, returned, updated, inserted, and deleted. ### Configuring the PostgreSQL statistics collector While some types of internal statistics are collected by default, others must be manually enabled, because of the additional load it would place on each query. By default, PostgreSQL's statistics collector should already be set up to collect most of the metrics covered in [Part 1](https://www.datadoghq.com/blog/postgresql-monitoring.md). To confirm this, you can check your **postgresql.conf** file to see what PostgreSQL is currently collecting, and specify any desired changes in the ["Runtime Statistics" section](https://www.postgresql.org/docs/current/runtime-config-statistics.html): *postgresql.conf* ```yaml #------------------------------------------------------------------------------ # RUNTIME STATISTICS #------------------------------------------------------------------------------ # - Query/Index Statistics Collector - #track_activities = on #track_counts = on #track_io_timing = off #track_functions = none # none, pl, all #track_activity_query_size = 1024 # (change requires restart) #update_process_title = on #stats_temp_directory = 'pg_stat_tmp' ``` In the default settings shown above, PostgreSQL will not track disk block I/O latency (`track_io_timing`), or user-defined functions ([`track_functions`](https://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-FUNCTIONS)), so if you want to collect these metrics, you'll need to enable them in the configuration file. The statistics collector process continuously aggregates data about the server activity, but it will only report the data as frequently as specified by the PGSTAT_STAT_INTERVAL (500 milliseconds, by default). Queries to the statistics collector views will not yield data about any queries or transactions that are currently in progress. Note that each time a query is issued to one of the statistics views, it will use the latest report to deliver information about the database's activity at that point in time, so it will be slightly delayed in comparison to real-time activity. Once you've configured the PostgreSQL statistics collector to collect the data you need, you can query these activity stats just like any data from your database. You'll need to create a user and grant that user with read-only access to the `pg_stat_database` table: ```sql create user with password ; grant SELECT ON pg_stat_database to ; ``` Once you initialize a `psql` session under that user, you should be able to start querying the database's activity statistics. ### pg_stat_database Let's take a look at the `pg_stat_database` statistics view in more detail: ```sql SELECT * FROM pg_stat_database; datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_read_time | blk_write_time | stats_reset -------+------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+---------------+----------------+------------------------------- 1 | template1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12061 | template0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12066 | postgres | 2 | 77887 | 11 | 249 | 2142032 | 35291376 | 429228 | 59 | 4 | 58 | 0 | 0 | 0 | 0 | 0 | 0 | 2017-09-07 17:24:57.739225-04 16394 | employees | 0 | 66146 | 6 | 248 | 1822528 | 30345213 | 365608 | 176 | 6 | 62 | 0 | 0 | 0 | 0 | 0 | 0 | 2017-09-11 16:04:59.039319-04 16450 | exampledb | 0 | 350 | 0 | 2920 | 33853 | 517601 | 9341 | 173159 | 449 | 13 | 0 | 0 | 0 | 0 | 0 | 0 | 2017-10-04 14:13:35.125243-04 ``` `pg_stat_database` collects statistics about each database in the cluster, including the number of connections (numbackends), commits, rollbacks, rows/tuples fetched and returned. Each row displays statistics for a different database, but you can also limit your query to a specific database as shown below: ```sql SELECT * FROM pg_stat_database WHERE datname = 'employees'; datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_read_time | blk_write_time | stats_reset -------+-----------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+---------------+----------------+------------------------------- 16394 | employees | 0 | 66286 | 6 | 248 | 1826378 | 30409473 | 366378 | 176 | 6 | 62 | 0 | 0 | 0 | 0 | 0 | 0 | 2017-09-11 16:04:59.039319-04 (1 row) ``` ### pg_stat_user_tables Whereas `pg_stat_database` collects and displays statistics for each database, `pg_stat_user_tables` displays statistics for each of the user tables in a particular database. Start a `psql` session, making sure to specify a database and a user that has read access to the database: ```bash psql -d exampledb -U someuser ``` Now you can access activity statistics for this database, broken down by each table: ```sql SELECT * FROM pg_stat_user_tables; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count -------+------------+------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------------------------+-----------------+-------------------------------+-------------------------------+--------------+------------------+---------------+------------------- 16463 | public | customers | 6 | 100500 | 0 | 0 | 20000 | 0 | 0 | 0 | 20000 | 0 | 2017-10-04 14:13:59.945161-04 | | 2017-10-04 14:14:00.942368-04 | 2017-10-04 14:13:59.17257-04 | 1 | 0 | 1 | 1 16478 | public | orders | 5 | 60000 | 0 | 0 | 12000 | 0 | 0 | 0 | 12000 | 0 | 2017-10-04 14:14:00.946525-04 | | 2017-10-04 14:14:00.977419-04 | 2017-10-04 14:13:59.221127-04 | 1 | 0 | 1 | 1 16484 | public | products | 3 | 30000 | 0 | 0 | 10000 | 0 | 0 | 0 | 10000 | 0 | 2017-10-04 14:13:59.768383-04 | | 2017-10-04 14:13:59.827651-04 | 2017-10-04 14:13:57.708035-04 | 1 | 0 | 1 | 1 16473 | public | orderlines | 2 | 120700 | 0 | 0 | 60350 | 0 | 0 | 0 | 60350 | 0 | 2017-10-04 14:13:59.845423-04 | | 2017-10-04 14:13:59.900862-04 | 2017-10-04 14:13:57.816999-04 | 1 | 0 | 1 | 1 16488 | public | reorder | 0 | 0 | | | 0 | 0 | 0 | 0 | 0 | 0 | 2017-10-04 14:13:59.828718-04 | | 2017-10-04 14:13:59.829075-04 | | 1 | 0 | 1 | 0 16454 | public | categories | 2 | 32 | 0 | 0 | 16 | 0 | 0 | 0 | 16 | 0 | 2017-10-04 14:13:59.589964-04 | | 2017-10-04 14:13:59.591064-04 | | 1 | 0 | 1 | 0 16470 | public | inventory | 1 | 10000 | 0 | 0 | 10000 | 0 | 0 | 0 | 10000 | 0 | 2017-10-04 14:13:59.593678-04 | | 2017-10-04 14:13:59.601726-04 | 2017-10-04 14:13:57.612466-04 | 1 | 0 | 1 | 1 16458 | public | cust_hist | 2 | 120700 | 0 | 0 | 60350 | 0 | 0 | 0 | 60350 | 0 | 2017-10-04 14:13:59.908188-04 | | 2017-10-04 14:13:59.94001-04 | 2017-10-04 14:13:57.885104-04 | 1 | 0 | 1 | 1 (8 rows) ``` The `exampledb` database contains eight tables. With `pg_stat_user_tables`, we can see a cumulative count of the sequential scans, index scans, and rows fetched/read/updated within each table. ### pg_statio_user_tables [pg_statio_user_tables](https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STATIO-ALL-TABLES-VIEW) helps you analyze how often your queries are utilizing the shared buffer cache. Just like `pg_stat_user_tables`, you'll need to start a `psql` session and specify a database and a user that has read access to the database. This view displays a cumulative count of blocks read, the number of blocks that were hits in the shared buffer cache, as well as other information about the types of blocks that were read from each table. ```sql SELECT * FROM pg_statio_user_tables; relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit -------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+--------------- 16463 | public | customers | 492 | 5382 | 137 | 4 | 0 | 0 | 0 | 0 16488 | public | reorder | 0 | 0 | | | | | | 16478 | public | orders | 104 | 1100 | 70 | 2 | | | | 16454 | public | categories | 5 | 7 | 2 | 0 | | | | 16484 | public | products | 105 | 909 | 87 | 0 | | | | 16473 | public | orderlines | 389 | 3080 | 167 | 0 | | | | 16458 | public | cust_hist | 331 | 2616 | 167 | 0 | | | | 16470 | public | inventory | 59 | 385 | 29 | 0 | | | | (8 rows) ``` You can also calculate a hit rate of blocks read from the shared buffer cache, using a query like this: ```sql SELECT sum(heap_blks_read) as blocks_read, sum(heap_blks_hit) as blocks_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as hit_ratio FROM pg_statio_user_tables; blocks_read | blocks_hit | hit_ratio -------------+------------+------------------------ 1485 | 13479 | 0.90076182838813151564 (1 row) ``` You may recall from [Part 1](https://www.datadoghq.com/blog/postgresql-monitoring.md) that the `heap_blks_hit` statistic only tracks the number of blocks that were hits in the shared buffer cache. Even if a block wasn't recorded as a hit in the shared buffer cache, it may still have been accessed from the OS cache rather than read from disk. Therefore, monitoring `pg_statio_user_tables` alongside system-level metrics will provide a more accurate assessment of how often data was queried without having to access the disk. ### pg_stat_user_indexes `pg_stat_user_indexes` shows us how often each index in any database is actually being used. Querying this view can help you determine if any indexes are underutilized, so that you can consider deleting them in order to make better use of resources. To query this view, start a `psql` session, making sure to specify the database you'd like to query, and a user that has read access to that database. Now you can query this view like any other table, like so: ```sql SELECT * FROM pg_stat_user_indexes; relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+------------+-------------------------+----------+--------------+--------------- 16454 | 16491 | public | categories | categories_pkey | 0 | 0 | 0 16463 | 16493 | public | customers | customers_pkey | 0 | 0 | 0 16470 | 16495 | public | inventory | inventory_pkey | 0 | 0 | 0 16478 | 16497 | public | orders | orders_pkey | 0 | 0 | 0 16484 | 16499 | public | products | products_pkey | 0 | 0 | 0 16458 | 16501 | public | cust_hist | ix_cust_hist_customerid | 0 | 0 | 0 16463 | 16502 | public | customers | ix_cust_username | 0 | 0 | 0 16478 | 16503 | public | orders | ix_order_custid | 0 | 0 | 0 16473 | 16504 | public | orderlines | ix_orderlines_orderid | 0 | 0 | 0 16484 | 16505 | public | products | ix_prod_category | 0 | 0 | 0 16484 | 16506 | public | products | ix_prod_special | 0 | 0 | 0 (11 rows) ``` To track how these statistics change over time, let's try querying data by using one of the indexes: ```sql SELECT * FROM customers WHERE customerid=55; customerid | firstname | lastname | address1 | address2 | city | state | zip | country | region | email | phone | creditcardtype | creditcard | creditcardexpiration | username | password | age | income | gender ------------+-----------+------------+---------------------+----------+---------+-------+-------+---------+--------+---------------------+------------+----------------+------------------+----------------------+----------+----------+-----+--------+-------- 55 | BHNECK | XVDQMTQHMH | 3574669247 Dell Way | | DHFGOUC | NE | 51427 | US | 1 | XVDQMTQHMH@dell.com | 3574669247 | 1 | 8774650241713972 | 2009/07 | user55 | password | 61 | 60000 | M (1 row) ``` Now, when we query `pg_stat_user_indexes`, we can see that the statistics collector detected that an index scan occurred on our `customers_pkey` index: ```sql SELECT * FROM pg_stat_user_indexes; relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+------------+-------------------------+----------+--------------+--------------- 16454 | 16491 | public | categories | categories_pkey | 0 | 0 | 0 16463 | 16493 | public | customers | customers_pkey | 1 | 1 | 1 16470 | 16495 | public | inventory | inventory_pkey | 0 | 0 | 0 16478 | 16497 | public | orders | orders_pkey | 0 | 0 | 0 16484 | 16499 | public | products | products_pkey | 0 | 0 | 0 16458 | 16501 | public | cust_hist | ix_cust_hist_customerid | 0 | 0 | 0 16463 | 16502 | public | customers | ix_cust_username | 0 | 0 | 0 16478 | 16503 | public | orders | ix_order_custid | 0 | 0 | 0 16473 | 16504 | public | orderlines | ix_orderlines_orderid | 0 | 0 | 0 16484 | 16505 | public | products | ix_prod_category | 0 | 0 | 0 16484 | 16506 | public | products | ix_prod_special | 0 | 0 | 0 (11 rows) ``` ### pg_stat_bgwriter As mentioned in [Part 1](https://www.datadoghq.com/blog/postgresql-monitoring.md), monitoring the checkpoint process can help you determine how much load is being placed on your databases. The `pg_stat_bgwriter` view will return one row of data that shows the number of total checkpoints occurring across all databases in your cluster, broken down by the type of checkpoint (timed or requested), and how they were executed—during a checkpoint process (buffers_checkpoint), by backends (buffers_backend), or by the background writer (buffers_clean): ```sql SELECT * FROM pg_stat_bgwriter; checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_backend_fsync | buffers_alloc | stats_reset -------------------+-----------------+-----------------------+----------------------+-------------------+--------------+------------------+-----------------+---------------------+--------------+------------------------------- 7768 | 12 | 321086 | 135 | 4064 | 0 | 0 | 368475 | 0 | 5221 | 2017-09-07 17:24:56.770953-04 (1 row) ``` ## Querying other PostgreSQL statistics Although most of the metrics covered in [Part 1](https://www.datadoghq.com/blog/postgresql-monitoring.md) are available through PostgreSQL's predefined statistics views, these three types of metrics need to be accessed through [system administration functions](https://www.postgresql.org/docs/current/static/functions-admin.html) and other native sources: - [replication delay](#tracking-replication-delay) - [connections](#connection-metrics) - [locks](#locks) - [disk space used by tables and indexes](#disk-usage) ### Tracking replication delay In order to track replication delay, you'll need to query [recovery information functions](https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE) on each standby server that is in continuous recovery mode (which simply means that it continuously receives and applies WAL updates from the primary server). Replication delay tracks the delay between applying the WAL update ("replaying" the update that already happened on the primary), and applying that WAL update to disk. To calculate replication lag (in bytes) on each standby, you'll need to find the difference between two recovery information functions: `pg_last_xlog_receive_location()` (the location in the WAL file that was most recently synced to disk on the standby), and `pg_last_xlog_replay_location()` (the location in the WAL file that was most recently applied/replayed on the standby). Note that these functions have been [renamed in PostgreSQL 10](https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE), to `pg_last_wal_receive_lsn()` and `pg_last_wal_replay_lsn()`. To calculate this lag in seconds instead of bytes, you can extract the timestamp from `pg_last_xlog_replay_location()` or `pg_last_wal_replay_lsn()`, depending on what version you're running. ### Connection metrics Although you can access the [number of active connections](https://www.datadoghq.com/blog/postgresql-monitoring.md#connections) through `pg_stat_database`, you'll need to query `pg_settings` to find the server's current setting for the maximum number of connections: ```sql SELECT setting::float FROM pg_settings WHERE name = 'max_connections'; ``` If you use a connection pool like PgBouncer to proxy connections between your applications and PostgreSQL backends, you can also monitor metrics from your connection pool in order to ensure that connections are functioning as expected. ### Locks You can also track the most recent status of locks granted across each of your databases, by querying the [pg_locks](https://www.postgresql.org/docs/current/static/view-pg-locks.html) view. The following query provides a breakdown of the type of lock (in the `mode` column), as well as the relevant database, relation, and process ID. ```sql SELECT locktype, database, relation::regclass, mode, pid FROM pg_locks; locktype | database | relation | mode | pid ---------------+----------+----------+------------------+----- relation | 12066 | pg_locks | AccessShareLock | 965 virtualxid | | | ExclusiveLock | 965 relation | 16611 | 16628 | AccessShareLock | 820 relation | 16611 | 16628 | RowExclusiveLock | 820 relation | 16611 | 16623 | AccessShareLock | 820 relation | 16611 | 16623 | RowExclusiveLock | 820 virtualxid | | | ExclusiveLock | 820 relation | 16611 | 16628 | AccessShareLock | 835 relation | 16611 | 16623 | AccessShareLock | 835 virtualxid | | | ExclusiveLock | 835 transactionid | | | ExclusiveLock | 820 (11 rows) ``` You'll see an [object identifier (OID)](https://www.postgresql.org/docs/current/static/datatype-oid.html) listed in the database and relation columns. To translate these OIDs into the actual names of each database and relation, you can query the database OID from [pg_database](https://www.postgresql.org/docs/current/static/catalog-pg-database.html), and the relation OID from [pg_class](https://www.postgresql.org/docs/current/static/catalog-pg-class.html). ### Disk usage PostgreSQL provides [database object size functions](https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE) to help users track the amount of disk space used by their tables and indexes. For example, below, we query the size of `mydb` using the `pg_database_size` function. We also use `pg_size_pretty()` to format the result into a human-readable format: ```sql SELECT pg_size_pretty(pg_database_size('mydb')) AS mydbsize; mydbsize ------------ 846 MB (1 row) ``` You can check the size of your tables by querying the object ID (OID) of each table in your database, and using that OID to query the size of each table from `pg_table_size`. The following query will show you how much disk space the top five tables are using (excluding indexes): ```sql SELECT relname AS "table_name", pg_size_pretty(pg_table_size(C.oid)) AS "table_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r') ORDER BY pg_table_size(C.oid) DESC LIMIT 5; table_name | table_size ------------------+------------ pgbench_accounts | 705 MB customers | 3944 kB orderlines | 3112 kB cust_hist | 2648 kB products | 840 kB (5 rows) ``` You can customize these queries to gain more granular views into disk usage across tables and indexes in your databases. For example, in the query above, you could replace `pg_table_size` with `pg_total_relation_size`, if you'd like to include indexes in your table_size metric (by default table_size shows disk space used by the tables, and excludes indexes). You can also use [regular expressions](https://www.postgresql.org/docs/current/static/functions-matching.html) to fine-tune your queries. ## Using an open source PostgreSQL monitoring tool As we've seen here, PostgreSQL's statistics collector tracks and reports a wide variety of metrics about your databases' activity. Rather than querying these statistics on an ad hoc basis, you may want to use a dedicated monitoring tool to automatically query these metrics for you. The open source community has developed several such tools to help PostgreSQL users monitor their database activity. In this section, we'll explore one such option and show you how it can help you automatically aggregate some key statistics from your databases and view them in an easily accessible web interface. ### PgHero PgHero is an open source PostgreSQL monitoring tool that was developed by Instacart. This project provides a dashboard that shows the health and performance of your PostgreSQL servers. It is available to install via a Docker image, Rails engine, or Linux package. In this example, we will install the Linux package for Ubuntu 14.04 by following the directions detailed [here](https://github.com/ankane/pghero/blob/master/guides/Linux.md#ubuntu-1404-trusty). Next, we need to provide PgHero with some information (make sure to replace the user, password, hostname, and name of your database, as specified below), and start the server: ```bash sudo pghero config:set DATABASE_URL=postgres://:@:5432/ sudo pghero config:set PORT=3001 sudo pghero config:set RAILS_LOG_TO_STDOUT=disabled sudo pghero scale web=1 ``` Now we can check that the server is running, by visiting `http://:3001/` in a browser (or `localhost:3001`, if you're on the server that is running PgHero). You should see an overview dashboard of your PostgreSQL database, including info about any long-running queries, the number of healthy connections, and invalid or duplicate indexes. You can also gather metrics from more than one database by following the instructions [specified here](https://github.com/ankane/pghero/blob/master/guides/Linux.md#multiple-databases). In the Maintenance tab, you can see the last time each table in the database completed a VACUUM and ANALYZE process. [As mentioned in Part 1](https://www.datadoghq.com/blog/postgresql-monitoring.md#exploring-the-vacuum-process), VACUUM processes help optimize reads by removing dead rows and updating the visibility map. ANALYZE processes are also important because they provide the query planner with updated statistics about the table. Both of these processes help ensure that queries are optimized and remain as efficient as possible. ![postgresql monitoring tools pghero maintenance vacuums analyze dashboard](https://web-assets.dd-static.net/42588/1776358277-postgresql-monitoring-tools-postgresql-monitoring-tools-pghero-maintenance-stat.png) PgHero will show you a visual breakdown of active connections, aggregated by database and user: ![postgresql monitoring tools: pghero connections dashboard](https://web-assets.dd-static.net/42588/1776358281-postgresql-monitoring-tools-postgresql-monitoring-tools-pghero-connections-stat.png) Monitoring disk and index usage is also an important way to gauge the health and performance of your databases. PgHero shows you how much space each relation (table or index) is using, as well as how many indexes are currently unused. The dashboard below shows us that four of our indexes are currently unused, and advises us to "remove them for faster writes." ![postgresql monitoring tools: pghero disk space dashboard](https://web-assets.dd-static.net/42588/1776358285-postgresql-monitoring-tools-postgresql-monitoring-tools-pghero-space-stat.png) ## Monitoring PostgreSQL in context In this post, we've walked through how to use native and open source PostgreSQL monitoring tools to query metrics from your databases. All metrics accessed from `pg_stat_*` views will be cumulative counters, so it's important to regularly collect the metrics and monitor how they change over time. If you're running PostgreSQL in production, you can use a more robust monitoring platform to automatically aggregate these metrics on your behalf, and to visualize and alert on potential issues in real time. In the [next part](https://www.datadoghq.com/blog/collect-postgresql-data-with-datadog.md) of this series, we'll show you how to use Datadog to automatically query your PostgreSQL statistics, visualize them in dashboards, and analyze health and performance in context with the rest of your systems. We'll also show you how to optimize and troubleshoot PostgreSQL performance with Datadog's distributed tracing and APM.