PostgreSQL Monitoring Integration
Use the Sematext PostgreSQL Monitoring integration to check on your PostgreSQL metrics. From number of rows and connections to index and function-specific metrics, all the relevant stats are collected and displayed in built-in dashboards:
Be sure to check out the PostgreSQL Logs integration as well, to get a complete view on PostgreSQL. For example, if you see a spike in number of rows fetched, you can check logs to see which queries fetch too many rows. A concrete example of doing that can be found in this article.
Agent Install¶
You can find the complete instructions in the Integrations screen of your Monitoring App, but the basic steps are:
- install the Sematext Agent
- create a user for the Sematext Agent to use
- configure Sematext Agent to connect to the PostgreSQL host and port (localhost:5432 by default), using your App token and the newly created user's credentials
Important PostgreSQL Metrics to Watch¶
You'd normally start from the Overview screen to check the higher-level metrics, such as number of rows or connections over time. Other high-level metrics can be found under Activity or Tables dashboards:
Then, if necessary, you can dive deeper into a specific direction. For example, if you think that indexes are over-used or not used enough, you can check their metrics in the Indexes dashboard. Like how often the index blocks are hit:
Finally, OS-level metrics can also point out bottlenecks, such as network or CPU:
You can find a full list of PostgreSQL-specific metrics that are collected below.
Metrics¶
| Metric Name Key (Type) (Unit) |
Description |
|---|---|
| active backends use transaction.open.count (long gauge) |
Number of backends executing a query |
| idle backends transaction.idle.count (long gauge) |
Number of backends in a transaction, but not currently executing a query |
| WAL files archiver.wal.success.count (counter) |
Number of WAL files that have been successfully archived |
| failed WAL files archiver.wal.failed.count (counter) |
Number of failed attempts to archive WAL files |
| scheduled checkpoints bgwriter.checkpoints.scheduled (counter) |
Number of scheduled checkpoints that have been performed |
| requested checkpoints bgwriter.checkpoints.requested (counter) |
Number of requested checkpoints that have been performed |
| checkpoint buffers bgwriter.buffers.written (counter) |
Number of buffers written during checkpoints |
| cleaned buffers bgwriter.buffers.clean (counter) |
Number of buffers written by the background writer |
| max written clean buffers bgwriter.buffers.cleaning.maxwritten.stopped (counter) |
Number of times the background writer stopped a cleaning scan because it had written too many buffers |
| backend buffers bgwriter.buffers.backend (counter) |
Number of buffers written directly by a backend |
| allocated buffers bgwriter.buffers.alloc (counter) |
Number of buffers allocated |
| active connections database.connections.active.count (long gauge) |
Number of backends currently connected to this database |
| committed transactions database.transactions.commit (counter) |
Number of transactions in this database that have been committed |
| rolled back transactions database.transactions.rollback (counter) |
Number of transactions in this database that have been rolled back |
| disk blocks read database.blocks.read (counter) |
Number of disk blocks read in this database |
| disk block cache hit database.blocks.hit (counter) |
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) |
| rows returned database.rows.returned (counter) |
Number of rows returned by queries in this database |
| rows fetched database.rows.fetched (counter) |
Number of rows fetched by queries in this database |
| rows inserted database.rows.inserted (counter) |
Number of rows inserted by queries in this database |
| rows updated database.rows.updated (counter) |
Number of rows updated by queries in this database |
| rows deleted database.rows.deleted (counter) |
Number of rows deleted by queries in this database |
| max connections database.connections.max (double gauge) |
Maximum number of concurrent connections to the database server. Parameter set at server startup |
| percent usage connections database.connections.usage (double gauge) |
Percentage of used connections (ratio between number of active backends and maximum allowed connections) |
| table count table.count (long gauge) |
Number of tables in this DB |
| function calls function.calls (counter) |
Number of times this function has been called |
| function total time function.time.total (counter) |
Total time spent in this function and all other functions called by it, in milliseconds |
| function self time function.time.self (counter) |
Total time spent in this function itself, not including other functions called by it, in milliseconds |
| index scan indexes.scan (counter) |
Number of index scans initiated on this index |
| returned index entries indexes.rows.read (counter) |
Number of index entries returned by scans on this index |
| fetched rows indexes.rows.feched (counter) |
Number of live rows fetched by index scans |
| lock count lock.count (long gauge) |
Number of active lockable objects |
| sequential scans tables.scan.sequential (counter) |
Number of sequential scans initiated on a table |
| sequential rows fetched tables.rows.fetched.sequential (counter) |
Number of live rows fetched by sequential scans |
| index rows fetched tables.rows.fetched.index (counter) |
Number of live rows fetched by index scans |
| inserted rows tables.rows.inserted (counter) |
Number of rows inserted |
| updated rows tables.rows.updated (counter) |
Number of rows updated |
| deleted rows tables.rows.deleted (counter) |
Number of rows deleted |
| hot updated rows tables.rows.hot.updated (counter) |
Number of rows hot updated (i.e., with no separate index update required) |
| live rows tables.rows.live (long gauge) |
Estimated number of live rows |
| dead rows tables.rows.dead (long gauge) |
Estimated number of dead rows |
| replication delay replication.delay.time (long gauge) |
Replication delay in milliseconds |
| replication delay bytes replication.delay.bytes (long gauge) |
Replication delay in bytes |
| table size table.disk.size (long gauge) |
Disk space used by the a table, excluding indexes (but including TOAST, free space map, and visibility map) |
| index size index.disk.size (long gauge) |
Total disk space used by indexes attached to the a table |
| total size table.total.disk.size (long gauge) |
Total disk space used by the a table, including all indexes and TOAST data |
| read disk blocks statio.heap.blocks.read (counter) |
Number of disk blocks read from a table |
| hit disk blocks statio.heap.blocks.hit (counter) |
Number of buffer hits in a table |
| read index blocks statio.index.blocks.read (tttttt) |
Number of disk blocks read from all indexes on a table |
| hit index blocks statio.index.blocks.hit (counter) |
Number of buffer hits in all indexes on a table |
| read TOAST blocks statio.toast.blocks.read (counter) |
Number of disk blocks read from a table's TOAST table (if any) |
| hit TOAST buffers statio.toast.blocks.hit (counter) |
Number of buffer hits in a table's TOAST table (if any) |
| read TOAST index statio.tidx.blocks.read (counter) |
Number of disk blocks read from a table's TOAST table index (if any) |
| hit TOAST buffer statio.tidx.blocks.hit (counter) |
Number of buffer hits in a table's TOAST table index (if any) |
Troubleshooting¶
If you are having issues with Sematext Monitoring, i.e. not seeing PostgreSQL metrics, see How do I create the diagnostics package.
For more troubleshooting information please look at the Troubleshooting section.