Enable detailed insights

You can configure the following options to get deeper insights into your PostgreSQL instances:

Enable TLS
Query plans for write statements

Enable TLS

The tls field is optional, but omitting it isn't the same as disabling TLS explicitly: the receiver's default configuration already sets insecure: false and insecure_skip_verify: true, so connections are encrypted (sslmode=require) but the server certificate isn't verified unless you set insecure_skip_verify: false and provide a ca_file. Most Amazon RDS and Aurora instances enforce SSL/TLS, so you'll typically want to add that verification for those environments.

receivers:
  nrpostgresql:
    endpoint: <YOUR_HOST>:<YOUR_PORT>
    username: <YOUR_DB_USERNAME>
    password: <YOUR_DB_PASSWORD>
    collection_interval: 30s
    tls:
      insecure: false
      insecure_skip_verify: false
      ca_file: <PATH_TO_CA_CERTIFICATE>
      cert_file: <PATH_TO_CLIENT_CERTIFICATE>
      key_file: <PATH_TO_CLIENT_KEY>

Tip

cert_file/key_file are only needed if your PostgreSQL server requires mutual TLS (client certificate authentication); most setups only need ca_file to verify the server. For Amazon RDS/Aurora, download the appropriate RDS CA certificate bundle and reference it in ca_file. Leave insecure_skip_verify: true (the default) only for testing against instances with self-signed certificates, never in production.

Query plans for write statements

By default, EXPLAIN runs directly as the monitoring user, and PostgreSQL checks table privileges at plan time. That means row-locking or write statements fail with permission denied unless the monitoring user has write access, which it should never have.

To collect query plans for locking or write statements without granting write access to the monitoring user, create the following function in each database:

CREATE OR REPLACE FUNCTION otel.explain_statement(l_query text)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_plan json;
    v_param_count int;
    v_nulls text := '';
    i int;
BEGIN
    SET TRANSACTION READ ONLY;
    SET plan_cache_mode = force_generic_plan;

    EXECUTE 'PREPARE otel_explain_stmt AS ' || l_query;

    SELECT COALESCE(array_length(parameter_types, 1), 0) INTO v_param_count
    FROM pg_prepared_statements WHERE name = 'otel_explain_stmt';

    IF v_param_count > 0 THEN
        FOR i IN 1..v_param_count LOOP
            v_nulls := v_nulls || CASE WHEN i > 1 THEN ', ' ELSE '' END || 'null';
        END LOOP;
        v_nulls := '(' || v_nulls || ')';
    END IF;

    EXECUTE 'EXPLAIN (FORMAT JSON) EXECUTE otel_explain_stmt' || v_nulls INTO v_plan;
    DEALLOCATE otel_explain_stmt;
    RETURN v_plan;
EXCEPTION WHEN OTHERS THEN
    IF EXISTS (SELECT 1 FROM pg_prepared_statements WHERE name = 'otel_explain_stmt') THEN
        DEALLOCATE otel_explain_stmt;
    END IF;
    RAISE;
END;
$$;

GRANT EXECUTE ON FUNCTION otel.explain_statement(text) TO <YOUR_DB_USERNAME>;

Execution plan collection mechanics

Rate limiting: top_query_collection.max_explain_each_interval (default 1000) caps the number of EXPLAIN calls per scrape.
Caching: query_plan_cache_size / query_plan_cache_ttl (default 1000 entries / 1h) cache a plan once obtained, keyed by query ID.

Instrumentation in self-hosted environments

Learn how to set up PostgreSQL monitoring in self-hosted environments with New Relic.

Metrics reference

Learn about the available metrics collected by the NRDOT Collector.

Troubleshooting guide

Learn how to troubleshoot common issues with PostgreSQL monitoring.

Enable TLS .css-21sua1{background:none;border:none;width:0;padding:0;}