Postgres table bloat having duplicate points and missing index bloat

[jeroenj]

Output of the info page

Getting the status from the agent.
===============
Agent (v7.31.1)
===============

  Status date: 2021-10-06 10:52:30.85 UTC (1633517550850)
  Agent start: 2021-10-05 14:56:13.594 UTC (1633445773594)
  Pid: 29109
  Go Version: go1.15.13
  Python Version: 3.8.11
  Build arch: amd64
  Agent flavor: agent
  Check Runners: 4
  Log File: /var/log/datadog/agent.log
  Log Level: INFO

  Paths
  =====
    Config File: /etc/datadog-agent/datadog.yaml
    conf.d: /etc/datadog-agent/conf.d
    checks.d: /etc/datadog-agent/checks.d

  Host Info
  =========
    bootTime: 2021-02-24 16:22:58 UTC (1614183778000)
    kernelArch: x86_64
    kernelVersion: 5.4.0-1036-gcp
    os: linux
    platform: ubuntu
    platformFamily: debian
    platformVersion: 18.04
    procs: 172
    uptime: 5350h33m19s
    virtualizationRole: guest

=========
Collector
=========

  Running Checks
  ==============

    postgres (9.0.2)
    ----------------
      Instance ID: postgres:84386d3b1a624349 [OK]
      Configuration Source: file:/etc/datadog-agent/conf.d/postgres.d/conf.yaml
      Total Runs: 4,785
      Metric Samples: Last Run: 5,942, Total: 28,395,305
      Events: Last Run: 0, Total: 0
      Service Checks: Last Run: 1, Total: 4,785
      Average Execution Time : 296ms
      Last Execution Date : 2021-10-06 10:52:18 UTC (1633517538000)
      Last Successful Execution Date : 2021-10-06 10:52:18 UTC (1633517538000)
      metadata:
        version.major: 13
        version.minor: 3
        version.patch: 0
        version.raw: 13.3 (Ubuntu 13.3-1.pgdg18.04+1)
        version.scheme: semver

Steps to reproduce the issue:

1. Create a table with multiple indexes
2. Run this query:

 current_database | schemaname |             tablename             | tbloat | wastedbytes |                             iname                              | ibloat | wastedibytes
------------------+------------+-----------------------------------+--------+-------------+----------------------------------------------------------------+--------+--------------
 report_prod      | public     | report_templates |    1.0 |           0 | report_templates_pkey                          |    2.0 |         8192
 report_prod      | public     | report_templates |    1.0 |           0 | index_report_templates_on_deleted_at           |    2.0 |         8192
 report_prod      | public     | report_templates |    1.0 |           0 | index_report_templates_on_original_template_id |    2.0 |         8192
# some irrelevant tables have been left out

3. Run datadog-agent check postgres

Describe the results you received:

For each index there's a postgresql.table_bloat entry using the tbloat value:

    {
      "metric": "postgresql.table_bloat",
      "points": [
        [
          1633517859,
          1
        ]
      ],
      "tags": [
        "db:report_prod",
        "index:report_templates_pkey",
        "port:5432",
        "replication_role:master",
        "schema:public",
        "server:localhost",
        "table:report_templates"
      ],
      "host": "pg-0",
      "type": "gauge",
      "interval": 0,
      "source_type_name": "System"
    },
    {
      "metric": "postgresql.table_bloat",
      "points": [
        [
          1633517859,
          1
        ]
      ],
      "tags": [
        "db:report_prod",
        "index:index_report_templates_on_deleted_at",
        "port:5432",
        "replication_role:master",
        "schema:public",
        "server:localhost",
        "table:report_templates"
      ],
      "host": "pg-0",
      "type": "gauge",
      "interval": 0,
      "source_type_name": "System"
    },
    {
      "metric": "postgresql.table_bloat",
      "points": [
        [
          1633517859,
          1
        ]
      ],
      "tags": [
        "db:report_prod",
        "index:index_report_templates_on_original_template_id",
        "port:5432",
        "replication_role:master",
        "schema:public",
        "server:localhost",
        "table:report_templates"
      ],
      "host": "pg-0.",
      "type": "gauge",
      "interval": 0,
      "source_type_name": "System"
    },

Describe the results you expected:
In #9786 postgres table bloat metrics were introduced based on this query.

That query however also includes index bloat (the idbloat column). It actually returns a row per index meaning that a single table with multiple indexes will have multiple entries.

Currently the agent ignores idbloat and creates a metric entry for each row in that table meaning that like in the example above a single table with three indexes will get three entries (with the same value).

I think it should be adapted to:

• have a single table_bloat entry per table
• have a index_bloat (based in idbloat) per index

The single table_bloat entry might not be a huge deal as I assume Datadog will merge them anyway. But mainly the index_bloat entry would be helpful to properly monitor bloat in the database (thus table bloat + index bloat).

[sarah-witt]

Hi @jeroenj, thanks for suggesting this. I’ve made a PR to include an index bloat metric and limit the table_bloat entries. Let me know if this is what you had in mind: #10431

[jeroenj]

Hi @sarah-witt, #10431 looks perfect. 👌 I've ran it against one of our instance and it indeed does what I expect it to do. 👌

Thanks a lot! 🙇