sql query result and sql_exporter metrics are not equal

[ridvankorkmaz]

Describe the bug
sql query result and sql_exporter metrics are not equal.

To Reproduce

scraped results

postgres@host-1 ~ $ curl localhost:9399/metrics 2>/dev/null | grep 'pg_settings_source_cnt'
# HELP pg_settings_source_cnt Number of settings
# TYPE pg_settings_source_cnt gauge
pg_settings_source_cnt{job="postgresql_common_queries",slave="False",sourcefile="/etc/postgresql-15/postgresql.conf",target="database1"} 82
pg_settings_source_cnt{job="postgresql_common_queries",slave="False",sourcefile="default",target="database1"} 280

query use by sql_exporter to get metrics

database1=# select coalesce(sourcefile, 'default') as sourcefile, count(*) as cnt from pg_settings group by sourcefile;
             sourcefile             | cnt 
------------------------------------+-----
 default                            | 273
 /etc/postgresql-15/postgresql.conf |  84
(2 rows)

Expected behavior
Scraped results and query results are expected to be same.
scrape sourcefile.default is 280
query sourcefile.default is 273

scrape sourcefile."/etc/postgresql-15/postgresql.conf" is 82
query sourcefile."/etc/postgresql-15/postgresql.conf" is 84

Configuration
Metric definition

  - metric_name: pg_settings_source_cnt
    type: gauge
    help: Number of settings
    values: [cnt]
    static_labels:
      slave: False
    key_labels: [sourcefile]
    query_ref: pg_settings_source

.....
....
....

  - query_name: pg_settings_source
    query: |
      select
          coalesce(sourcefile, 'default') as sourcefile,
          count(*) as cnt
      from pg_settings group by sourcefile

sql_exporter.yml

global:
  # If scrape_timeout <= 0, no timeout is set unless Prometheus provides one. The default is 10s.
  scrape_timeout: 30s
  # Subtracted from Prometheus' scrape_timeout to give us some headroom and prevent Prometheus from timing out first.
  scrape_timeout_offset: 500ms
  # Minimum interval between collector runs: by default (0s) collectors are executed on every scrape.
  min_interval: 0s
  # Maximum number of open connections to any one target. Metric queries will run concurrently on multiple connections,
  # as will concurrent scrapes.
  max_connections: 3
  # Maximum number of idle connections to any one target. Unless you use very long collection intervals, this should
  # always be the same as max_connections.
  max_idle_connections: 3
  # Maximum number of maximum amount of time a connection may be reused. Expired connections may be closed lazily before reuse.
  # If 0, connections are not closed due to a connection's age.
  max_connection_lifetime: 5m

  # Data source name always has a URI schema that matches the driver name. In some cases (e.g. MySQL)
  # the schema gets dropped or replaced to match the driver expected DSN format.

jobs:
  - job_name: postgresql_common_queries
    collectors: ['postgresql_common_queries']
    static_configs:
      - targets:
          database1: 'postgresql://monuser@localhost:5432/database1?sslmode=disable'


  # Collectors (referenced by name) to execute on the target.
  # Glob patterns are supported (see <https://pkg.go.dev/path/filepath#Match> for syntax).
  # collectors: [postgresql_*]

collector_files: 
  - "*.collector.yml"

Additional context
sql_exporter info

sql_exporter, version 0.16.0 (branch: non-git, revision: non-git)
  build user:       portage@host-1
  build date:       20250213-14:28:45
  go version:       go1.23.5
  platform:         linux/amd64
  tags:             netgo

[burningalchemist]

Hey @ridvankorkmaz, thanks for your report. Interesting issue. There is no custom query handling on the sql_exporter side, but I'm happy to look into that. I believe I can reproduce the case on my end. 👍

In the meantime, there are some steps you might try:

• sql_exporter also has another driver for PostgreSQL embedded (pgx), so you can try send a query using this one (replace postgres:// schema with pgx://). I'm curious if using a different driver would help or not;
• try some other query to help me understand the scope better;
• it would be great if you could also test your query with xo/usql (https://github.com/xo/usql) cli sql client.

[ridvankorkmaz]

Interestingly just changing the driver to pgx:// affects the results and they are close to actual query results.

1. Actual values

psql (15.5)
Type "help" for help.

postgres=# select coalesce(sourcefile, 'default') as sourcefile, count(*) as cnt from pg_settings group by sourcefile;
             sourcefile             | cnt 
------------------------------------+-----
 default                            | 273
 /etc/postgresql-15/postgresql.conf |  84
(2 rows)

2. pgx:// driver values

curl localhost:9399/metrics 2>/dev/null | grep 'pg_settings_source_cnt'
# HELP pg_settings_source_cnt Number of settings
# TYPE pg_settings_source_cnt gauge
pg_settings_source_cnt{job="postgresql_common_queries",slave="False",sourcefile="/etc/postgresql-15/postgresql.conf",target="database1"} 83
pg_settings_source_cnt{job="postgresql_common_queries",slave="False",sourcefile="default",target="database1"} 274

3. postgresql:// driver values (default used)

curl localhost:9399/metrics 2>/dev/null | grep 'pg_settings_source_cnt'
# HELP pg_settings_source_cnt Number of settings
# TYPE pg_settings_source_cnt gauge
pg_settings_source_cnt{job="postgresql_common_queries",slave="False",sourcefile="/etc/postgresql-15/postgresql.conf",target="database1"} 82
pg_settings_source_cnt{job="postgresql_common_queries",slave="False",sourcefile="default",target="database1"} 280

[ridvankorkmaz]

kindly can you guide me how to do it?

it would be great if you could also test your query with xo/usql (https://github.com/xo/usql) cli sql client.

[burningalchemist]

@ridvankorkmaz as for usql you may find the instructions on their README.md (you might want to download and run a single binary from here - https://github.com/xo/usql/releases/tag/v0.19.21), but overall the goal is to connect to the database with the DSN (same as provided in the sql_exporter.yml) and run the query from the command line interface.

[burningalchemist]

Hey @ridvankorkmaz,

So far I'm unable to reproduce the case on my end (I've taken your provided configuration):

database_dev=>  select coalesce(sourcefile, 'default') as sourcefile, count(*) as cnt from pg_settings group by sourcefile;
 sourcefile | cnt
------------+-----
 default    | 392
(1 row)

database_dev=> select count(*) from pg_settings;
 count
-------
   392
(1 row)

root@database-dev:/root# curl localhost:9399/metrics 2>/dev/null | grep 'pg_settings_source_cnt'
# HELP pg_settings_source_cnt Number of settings
# TYPE pg_settings_source_cnt gauge
pg_settings_source_cnt{slave="False",sourcefile="default"} 392

It's PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.3.1, 64-bit on my end.

After inspecting your query, I also think it might be potentially related to permissions sql_exporter user is given. pg_settings is a view which displays the settings for the current session, and it contains configuration parameters, some of which are restricted based on the user querying them. Certain parameters are only visible to superusers because they might contain sensitive information or because they require superuser privileges to view or modify. So to debug further I'd try checking the permissions and maybe trying different users.

[burningalchemist]

Let's proceed in the discussion as it's unrelated to the exporter's functional part, at least so far.

[ridvankorkmaz]

Thank you @burningalchemist for looking into this.
I will test your suggestions and get back to here.

As I (I think you also) notice that only there is only one kind of setting (default) is in query result, yet for my case there are at least 2 kind of sourcefile

Yet I would like to suggest you to test with:
set following parameter in postgresql.conf

wal_keep_size = 64MB
wal_writer_delay = 300

and run this to set them in postgresql.auto.conf file

alter system set log_connections = on;
alter system set max_wal_size = 1GB;
alter system set log_destination = 'csvlog';
select pg_reload_conf();

Then run the query to see you at least 3 different sourcefile then could you please scrape with sql_exporter to verify results.

[burningalchemist]

Yeah, will do and let you know 👍

[burningalchemist]

@ridvankorkmaz btw, something from the PostgreSQL documentation with regards to postgresql.auto.conf:

In addition to postgresql.conf, a PostgreSQL data directory contains a file postgresql.auto.conf, which has the same format as postgresql.conf but is intended to be edited automatically, not manually. This file holds settings provided through the ALTER SYSTEM command. This file is read whenever postgresql.conf is, and its settings take effect in the same way. Settings in postgresql.auto.conf override those in postgresql.conf.

External tools may also modify postgresql.auto.conf. It is not recommended to do this while the server is running unless allow_alter_system is set to off, since a concurrent ALTER SYSTEM command could overwrite such changes. Such tools might simply append new settings to the end, or they might choose to remove duplicate settings and/or comments (as ALTER SYSTEM will).

The system view pg_file_settings can be helpful for pre-testing changes to the configuration files, or for diagnosing problems if a SIGHUP signal did not have the desired effects.

ref: https://www.postgresql.org/docs/current/config-setting.html

But yeah, I'll have a look. 👍

[burningalchemist]

Hey @ridvankorkmaz, I was able to partially reproduce the issue. In my case, though, the distribution while using pgx driver matches the pgsql client result. lib/pq seems to prone to that issue and it's possible it wasn't resolved (lib/pq#80 (comment)). But it might not be related to your issue directly, so we need more details.

What I've also learnt on my end is by adding source and context columns we may see how a particular driver adjusts or adds parameters. This is the listing that shows distribution differences between postgresql driver (libpq), pgx and psql client:

pgx(jackc/pgx)                      
                      sourcefile                      |       source       |      context      | cnt
------------------------------------------------------+--------------------+-------------------+-----
 /opt/homebrew/var/postgresql@16/postgresql.conf      | configuration file | user              |   6
 default                                              | default            | user              | 133


postgresql(lib/pq)                      
                      sourcefile                      |       source       |      context      | cnt
------------------------------------------------------+--------------------+-------------------+-----
 /opt/homebrew/var/postgresql@16/postgresql.conf      | configuration file | user              |   5
 default                                              | default            | user              | 131
 default                                              | client             | user              |   3

psql cli                      
                      sourcefile                      |       source       |      context      | cnt
------------------------------------------------------+--------------------+-------------------+-----
 /opt/homebrew/var/postgresql@16/postgresql.conf      | configuration file | user              |   6
 default                                              | client             | user              |   2
 default                                              | default            | user              | 131

It's better to compare them in pairs: postgres vs pgx, and postgres vs psql cli, as these result sets reflect the different parameters grouped by source. It's important to note the differences are only present in the user context, the other parameters demonstrate consistency.

Once we got the differences, we might be interested to check what these parameters are. Here is the list per each driver:

postgres:

pg:localhost/postgres=> select name, setting, source, context from pg_settings where source = 'client';
        name        | setting  | source | context
--------------------+----------+--------+---------
 client_encoding    | UTF8     | client | user
 DateStyle          | ISO, MDY | client | user
 extra_float_digits | 2        | client | user
(3 rows)

pg:localhost/postgres=> select name, setting, source, context from pg_settings where source = 'configuration file' and context = 'user';
            name            |      setting       |       source       | context
----------------------------+--------------------+--------------------+---------
 default_text_search_config | pg_catalog.english | configuration file | user
 lc_monetary                | C                  | configuration file | user
 lc_numeric                 | C                  | configuration file | user
 lc_time                    | C                  | configuration file | user
 TimeZone                   | Europe/Berlin      | configuration file | user
(5 rows)

pgx has no parameters registered as the client parameters (library), so we only check 'configuration file' source:

px:localhost/postgres=> select name, setting, source, context from pg_settings where source = 'configuration file' and context = 'user';
            name            |      setting       |       source       | context
----------------------------+--------------------+--------------------+---------
 DateStyle                  | ISO, MDY           | configuration file | user
 default_text_search_config | pg_catalog.english | configuration file | user
 lc_monetary                | C                  | configuration file | user
 lc_numeric                 | C                  | configuration file | user
 lc_time                    | C                  | configuration file | user
 TimeZone                   | Europe/Berlin      | configuration file | user
(6 rows)

And here is the difference: while DateStyle parameter on pgx is not provided by the client, and hence remains coming from the configuration file, on postgres(libpq) driver it's provided by the client (regardless of the setting, which is equal for both). For the client source - the sourcefile column would be empty as well, but in your original query you put in into default sourcefile category with coalesce function. Moreover, postgres(libpq) add extra_float_digits parameter as a client parameter, whereas the other clients don't do this.

On the psql cli side, we also get extra parameters from the client source:

postgres=# select name, setting, source, context from pg_settings where source = 'client';
       name       | setting | source | context
------------------+---------+--------+---------
 application_name | psql    | client | user
 client_encoding  | UTF8    | client | user
(2 rows)

pgx driver doesn't add application_name and client_encoding parameters (though it's possible to provide via the query string if needed).

---

Overall, I think that given different default parameters provided by the libraries and even the psql cli, it's not easy to identify the source of truth between these three, but you can declare one of them.

It could be that just relying upon sourcefile column won't help with getting clarity upon the parameter changes. As with pgx driver, I think if you pass extra parameters like application_name and client_encoding (by adding them via ?application_name=sql_exporter&client_encoding=UTF8 in the DSN string), you should match the values from psql cli if it matters.

Alternatively, you might want to have a more sophisticated logic on when to add empty sourcefile to default and when not (based on the source and context).

Ultimately, on my end, the number of parameters is generally the same, and is only affected by those parameters coming implicitly from the libpq driver under the client source or by psql cli client.

Please have a look, and try the queries which I posted above, I think it should help. 👍

ref: https://www.postgresql.org/docs/17/view-pg-settings.html (pg_settings columns for the source and context values)

[ridvankorkmaz]

It is now quite clear,
Thank you @burningalchemist