What is effective_cache_size in PostgreSQL: Explanation and Usage

In PostgreSQL, effective_cache_size is a configuration setting that estimates the amount of memory available for disk caching by the operating system and PostgreSQL. It helps the query planner decide whether to use indexes or sequential scans by predicting how much data can be cached, improving query performance.

How It Works

The effective_cache_size setting in PostgreSQL acts like a hint to the query planner about how much memory is available for caching data. Imagine you have a bookshelf where you keep your favorite books handy. The bigger the shelf, the more books you can quickly grab without searching elsewhere. Similarly, PostgreSQL uses this setting to guess how much data can be quickly accessed from memory instead of slower disk reads.

This setting does not allocate memory but estimates the combined cache size of the operating system and PostgreSQL shared buffers. When the planner knows that a large cache is available, it is more likely to choose index scans because it expects the data to be in memory, making queries faster. If the cache size is small, the planner might prefer sequential scans to avoid random disk access.

Example

This example shows how to check and set the effective_cache_size in PostgreSQL. It also demonstrates how the planner's choice changes based on this setting.

sql

SHOW effective_cache_size;

-- Set effective_cache_size to 4GB
ALTER SYSTEM SET effective_cache_size = '4GB';
SELECT pg_reload_conf();

-- Check the new setting
SHOW effective_cache_size;

-- Explain a query to see planner's choice
EXPLAIN SELECT * FROM large_table WHERE indexed_column = 'value';

Output

 effective_cache_size 
---------------------
 4GB
(1 row)

                        QUERY PLAN                         
-----------------------------------------------------------
 Index Scan using large_table_index on large_table  (cost=0.29..8.31 rows=1 width=123)
   Index Cond: (indexed_column = 'value'::text)
(3 rows)

When to Use

Use effective_cache_size to help PostgreSQL make better decisions about query plans, especially on systems with large amounts of RAM. If your server has a lot of memory used for caching files and database buffers, setting this value closer to the actual available cache improves performance.

For example, on a dedicated database server with 16GB RAM and 8GB allocated to PostgreSQL shared buffers, you might set effective_cache_size to 12GB or more to reflect the OS cache plus shared buffers. This helps the planner prefer index scans when appropriate, speeding up queries on large tables.

Do not set it too high or too low; an inaccurate value can lead to inefficient query plans. Adjust it when you change memory allocation or observe slow queries due to poor plan choices.

Key Points

• effective_cache_size is an estimate, not a memory allocation.
• It guides the query planner on how much data can be cached in memory.
• Setting it correctly improves query plan choices and performance.
• It should reflect the combined cache of OS and PostgreSQL buffers.
• Adjust it when server memory or workload changes.

Key Takeaways

effective_cache_size tells PostgreSQL how much memory is available for caching data.

It helps the query planner decide between index scans and sequential scans.

Set it to reflect your system's actual cache size for better query performance.

It is an estimate and does not allocate memory itself.

Adjust this setting when you change memory or observe inefficient query plans.