Postgres memory calculation effective_cache_size

Question

I am verifying some of the configuration in my production Postgres instance. Our DB server has 32 GB RAM. From pg_settings, I see that effective_cache_size is set to:

postgres=> select name, setting, unit from pg_settings where name like 'effective_cache_size'; 
         name         | setting | unit 
----------------------+---------+------
 effective_cache_size | 7851762 | 8kB
(1 row)

As per my understanding, this value accounts to 7851762 X 8 KB = 62.8 GB. If my calculation is right, we are basically telling the optimizer that we have 62 GB for this parameter whereas we have only 32 GB of physical RAM.

Please correct me if I am calculating this parameter wrong. I always get confused with calculating parameter allocations for units with 8 KB.

Laurenz Albe · Accepted Answer · 2019-05-27 17:18:59Z

5

7851762 times 8 kB is approximately 60 GB.

I would configure the setting to 30 GB if the machine is dedicated to the PostgreSQL database.

This parameter tells PostgreSQL how much memory there is available for caching its files. If the value is high, PostgreSQL will estimate nested loop joins with an index scan on the inner side cheaper, because it assumes that the index will probably be cached.

answered May 27, 2019 at 17:18

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Amin Shojaei Over a year ago

How much this can be effective on speed? can we give lot of ram(assume 20% of database size) to Postgresql and expect it to cache indexes + data into ram?

Laurenz Albe Over a year ago

It does not influence the memory utilization of PostgreSQL at all. That is determined by the memory available on your machine, the concurrent processes and settings like shared_buffers, work_mem and max_connections. All that effective_cache_size influences is how much memory PostgreSQL thinks is available for caching. So it influences the estimated cost of index scans.

vrms Over a year ago

just curious ... the calculation would be 7851762 * 8 * 1024 / 1024 / 1024 / 1024 ~ 59.90 GiB, right?

Laurenz Albe Over a year ago

@vrms That is right.

Collectives™ on Stack Overflow

Postgres memory calculation effective_cache_size

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related