DEV Community

Chiazam Ochiegbu
Chiazam Ochiegbu

Posted on

1

Posgresql Optimization Techniques

Effect of Cache Size

effective_cache_size is used for the query planner to determine how much memory is available for disk caching.

It actually does not allocate any memory to the database, but
based on this number; the planner will decide whether enough RAM is available if index is used to improve the performance.
Normally having this parameter to hold half 12 of the total RAM is a reasonable setting.

More than ¾ of the total memory would lead the query planner to have a wrong estimation.

For the case of query optimization, effect_cache_size is an important parameter to tune.

Next in the series is work_mem, another optimization parameter to consider.

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more