Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = 'effective_cache_size'; query. As far as I can understand these commands should be identical. For example

show effective_cache_size;

(1 row)


select name,setting from pg_settings where name = 'effective_cache_size';

name    | effective_cache_size

setting | 360448

I am trying to figure out the value used by PostgreSQL. I get the same results if I run this as a superuser. Which one do I trust?

share|improve this question
In your question you talk about shared buffers, yet your example shows effective_cache_size. – a_horse_with_no_name Feb 25 '14 at 17:40
Isn't "effective_cache_size" a parameter that can be set on cluster and on session level? – Colin 't Hart Feb 25 '14 at 17:50
@a_horse_with_no_name edited the question to match the example output, thanks. – sumit Feb 25 '14 at 18:10
@colin-t-hart no setting has been done to this param besides the config file – sumit Feb 25 '14 at 18:11
up vote 2 down vote accepted

I see the same difference on my test database:

show shared_buffers;

select name, setting from pg_settings where name = 'shared_buffers';
      name      │ setting 
 shared_buffers │ 98304

Then watching the numbers for a short while, I set up the following query:

SELECT 98304 * 8192 / (1024 * 1024);

So, to me it looks like that pg_settings reports these sizes in 8 kb pages (8192 refers to this fact).

Note that there is a third way to check settings, namely the current_setting() function:

SELECT current_setting('shared_buffer');
share|improve this answer
thanks @dezso for the explanation, same calculation applies for my sample as well – sumit Feb 25 '14 at 18:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.