Handling many relations

If your Postgres database has a large number of relations (in the thousands), Datadog recommends adding collect_database_size_metrics: false to your instance configuration for that database. When this setting is disabled, the Agent will not run the function pg_database_size() to collect database size statistics, which has worse performance on instances with a large number of tables.

instances:
  - dbm: true
    ...
    collect_database_size_metrics: false

Additionally, if you partition your data across tables, such that table definitions are identical except for the name, this can result in a large number or normalized queries:

SELECT * FROM daily_aggregates_001
SELECT * FROM daily_aggregates_002
SELECT * FROM daily_aggregates_003

In these cases, track these queries as a single normalized query using the replace_digits option, so all metrics for those queries are rolled up into a single query:

SELECT * FROM daily_aggregates_?

Add the replace_digits option to your database instance configuration in the Datadog Agent:

instances:
  - dbm: true
    ...
    obfuscator_options:
      replace_digits: true

Raising the sampling rate

If you have queries that are relatively infrequent or execute quickly, raise the sampling rate by lowering the collection_interval value to collect explain plans more frequently.

Set the collection_interval in your database instance configuration of the Datadog Agent. The default value is 1 second and can be seen in the postgres/conf.yaml.example.

Lower the value to a smaller interval:

instances:
  - dbm: true
    ...
    query_samples:
        collection_interval: 0.1

Configuring column statistics collection

Column statistics collection reads per-column statistics from pg_stats (n_distinct, null_frac, avg_width, correlation, most_common_freqs) on a periodic schedule. This requires the datadog.column_statistics() function to exist in every monitored database—see Setting Up Database Monitoring for Self-Hosted Postgres for the function definition.

Once the function exists, enable and tune collection in your Postgres instance config:

instances:
  - dbm: true
    ...
    collect_column_statistics:
      enabled: true
      collection_interval: 3600   # seconds between collection runs; default 3600 (hourly)
      max_tables: 500              # maximum tables to collect per run; default 500

For column statistics to populate, the underlying tables must have had ANALYZE (or autoanalyze) run against them at least once — pg_stats is empty for tables with no collected statistics.