Using original values for settings

[bukem]

Hello,

I have a question about the way how numeric settings are collected. As of now for most of them current_settings function is used which converts the numbers to strings which does not allow to display the value on the dashboard or to use it in calculations.
Is there any reason to use current_settings results instead of the original value?

The query that shows the difference for some settings:

postgres=# with qs as (
  select name, setting from pg_settings
)
select
  (extract(epoch from now()) * 1e9)::int8 as epoch_ns,
  (select setting from qs where name = 'block_size') as block_size_raw,
  current_setting('block_size')::int as block_size,
  (select setting from qs where name = 'shared_buffers') as shared_buffers_raw,
  current_setting('shared_buffers') as shared_buffers,
  (select setting from qs where name = 'work_mem') as work_mem_raw,
  current_setting('work_mem') as work_mem,
  (select setting from qs where name = 'maintenance_work_mem') as maintenance_work_mem_raw,
  current_setting('maintenance_work_mem') as maintenance_work_mem,
  (select setting from qs where name = 'effective_cache_size') as effective_cache_size_raw,
  current_setting('effective_cache_size') as effective_cache_size,
  (select setting from qs where name = 'statement_timeout') as statement_timeout_raw,
  current_setting('statement_timeout') as statement_timeout,
  (select setting from qs where name = 'deadlock_timeout') as deadlock_timeout_raw,
  current_setting('deadlock_timeout') as deadlock_timeout,
  (select setting::int8 from qs where name = 'lock_timeout') || ' (ms)' as lock_timeout,
  (select setting from qs where name = 'checkpoint_timeout') as checkpoint_timeout_raw,
  current_setting('checkpoint_timeout') as checkpoint_timeout,
  (select setting::int8 from qs where name = 'max_wal_senders') as max_wal_senders
;
-[ RECORD 1 ]------------+--------------------
epoch_ns                 | 1668410116889529088
block_size_raw           | 8192
block_size               | 8192
shared_buffers_raw       | 2112640
shared_buffers           | 16505MB
work_mem_raw             | 33792
work_mem                 | 33MB
maintenance_work_mem_raw | 2097152
maintenance_work_mem     | 2GB
effective_cache_size_raw | 6338176
effective_cache_size     | 49517MB
statement_timeout_raw    | 600000
statement_timeout        | 10min
deadlock_timeout_raw     | 1000
deadlock_timeout         | 1s
lock_timeout             | 30000 (ms)
checkpoint_timeout_raw   | 1800
checkpoint_timeout       | 30min
max_wal_senders          | 10

Thanks.

Mikhail

[bukem]

Another idea is to use one metric with setting name and unit as label in it. Kind of:

select (extract(epoch from now()) * 1e9)::int8 as epoch_ns,
       name tag_name,
       setting,
       unit tag_unit,
       vartype
  from pg_settings
 where name in
('server_version',
'server_version_num',
'block_size',
'max_connections',
'hot_standby',
'hot_standby_feedback',
'fsync',
'full_page_writes',
'synchronous_commit',
'wal_compression',
'wal_log_hints',
'synchronous_standby_names',
'shared_buffers',
'work_mem',
'maintenance_work_mem',
'effective_cache_size',
'default_statistics_target',
'random_page_cost',
'min_wal_size',
'max_wal_size',
'checkpoint_segments',
'checkpoint_timeout',
'checkpoint_completion_target',
'max_worker_processes',
'max_parallel_workers',
'max_parallel_workers_per_gather',
'jit',
'ssl',
'statement_timeout',
'deadlock_timeout',
'data_checksums',
'max_connections',
'max_wal_senders',
'max_replication_slots',
'max_prepared_transactions',
'lock_timeout',
'archive_mode',
'archive_command',
'archive_timeout',
'shared_preload_libraries',
'listen_addresses',
'ssl',
'autovacuum',
'autovacuum_max_workers',
'autovacuum_vacuum_scale_factor',
'autovacuum_vacuum_threshold',
'autovacuum_analyze_scale_factor',
'autovacuum_analyze_scale_factor')
where vartype in ('real', 'integer', 'bool');

[pashagolub]

Would you please clarify what exactly metric is it and how do you want to use those values in dashboards.

Thanks in advance

[bukem]

Let's say shared_buffers or statement_timeout.
That will allow us to have a panel with shared_buffers size and also it's changes in time
Generally it is something similar to what you provide now with Postgresql Version overview , but for all numeric parameters