PostgreSQL statement metricset

This is the statement metricset of the PostgreSQL module.

This module collects information from the pg_stat_statements view, that keeps track of planning and execution statistics of all SQL statements executed by the server.

pg_stat_statements is included by an additional module in PostgreSQL. This module requires additional shared memory, and is disabled by default.

You can enable it by adding this module to the configuration as a shared preloaded library.

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Preloading this library in your server will increase the memory usage of your PostgreSQL server. Use it with care.

Once the server is started with this module, it starts collecting statistics about all statements executed. To make these statistics available in the pg_stat_statements view, the following statement needs to be executed in the server:

CREATE EXTENSION pg_stat_statements;

You can read more about the available options for this module in the official documentation.

The PostgreSQL module of Filebeat is also able to collect information about statements executed in the server from its logs. You may chose which one is better for your needings. An important difference is that the Metricbeat module collects aggregated information when the statement is executed several times, but cannot know when each statement was executed. This information can be obtained from logs.

Fields

For a description of each field in the metricset, see the exported fields section.

Here is an example document generated by this metricset:

{
    "@timestamp": "2017-10-12T08:05:34.853Z",
    "event": {
        "dataset": "postgresql.statement",
        "duration": 115000,
        "module": "postgresql"
    },
    "metricset": {
        "name": "statement",
        "period": 10000
    },
    "postgresql": {
        "statement": {
            "database": {
                "oid": 13395
            },
            "query": {
                "calls": 132,
                "id": -3489238739385425370,
                "memory": {
                    "local": {
                        "dirtied": 0,
                        "hit": 0,
                        "read": 0,
                        "written": 0
                    },
                    "shared": {
                        "dirtied": 0,
                        "hit": 924,
                        "read": 0,
                        "written": 0
                    },
                    "temp": {
                        "read": 0,
                        "written": 0
                    }
                },
                "rows": 396,
                "text": "SELECT d.datname as \"Name\",\n       pg_catalog.pg_get_userbyid(d.datdba) as \"Owner\",\n       pg_catalog.pg_encoding_to_char(d.encoding) as \"Encoding\",\n       d.datcollate as \"Collate\",\n       d.datctype as \"Ctype\",\n       pg_catalog.array_to_string(d.datacl, $1) AS \"Access privileges\"\nFROM pg_catalog.pg_database d\nORDER BY 1",
                "time": {
                    "max": {
                        "ms": 0.325369
                    },
                    "mean": {
                        "ms": 0.07867374242424244
                    },
                    "min": {
                        "ms": 0.053835
                    },
                    "stddev": {
                        "ms": 0.037920252272212004
                    },
                    "total": {
                        "ms": 10.384934000000003
                    }
                }
            },
            "user": {
                "id": 10
            }
        }
    },
    "service": {
        "address": "192.168.128.2:5432",
        "type": "postgresql"
    }
}

« PostgreSQL database metricset Prometheus module »