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

Partitioning also affects schema collection. Tables partitioned with native declarative partitioning (PARTITION BY) count as a single table toward the max_tables limit for collect_schemas, regardless of partition count. Tables partitioned using table inheritance (INHERITS), such as the daily_aggregates_* tables above, count each partition individually, so databases using this pattern may need a higher max_tables limit for full coverage. See Tuning schema collection for details.

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.