Alright, let’s talk about effective_cache_size. This is the parameter where we, the application developers and DBAs, get to tap the PostgreSQL query planner on the shoulder and whisper a little secret about the operating system into its ear. It’s not a hard limit or a memory allocation. Think of it less as a directive and more as a crucial piece of intelligence you’re giving to the optimizer.

Here’s the secret you’re sharing: “Hey, just so you know, the operating system has this much RAM available to use as a disk cache.” Why does the planner care? Because its eternal, core mission is to choose the cheapest possible plan for your query. The cost of reading data is a huge part of that calculation. If it thinks a dataset is likely to be in the OS’s page cache (i.e., in RAM), it will be more inclined to use an index scan, since grabbing those random pages will be lightning fast. If it thinks the data isn’t cached, it might pessimistically decide that a sequential scan is cheaper, because reading a large chunk of the table from disk sequentially is faster than doing thousands of random I/O operations. By setting effective_cache_size, you’re calibrating its expectations about the I/O cost of random page accesses.

What Value Should You Actually Set?

This is the million-dollar question. The goal is to set it to the sum of your shared_buffers and the freeable OS cache. Yes, PostgreSQL’s own cache in shared_buffers is part of the “effective” cache the OS can use, because if the data is in shared_buffers, it’s definitively in RAM and doesn’t need a disk read.

On a dedicated database server, a good starting point is about 70-80% of your total system RAM. If you have 64 GB of RAM, set it to something like 50GB (if your shared_buffers is 16GB, that leaves ~34GB for the OS cache, which is a sane ratio).

Want to be more precise? On a Linux system, you can ask the OS itself. This one-liner will get you pretty close:

free -b | awk '/^Mem:/ {print int(($3 + $6) * 0.8)}'
# This adds 'used' memory (~$3) and 'cached' memory (~$6),
# then takes 80% of that. It's an aggressive but often accurate estimate.

Let’s say that command returns 53687091200 (which is 50 GB in bytes). You’d then set it in your postgresql.conf:

effective_cache_size = '50GB'

You can also check and set it on the fly for testing without a full restart (this is a PGOPTION; it doesn’t require a reload either):

SHOW effective_cache_size; -- See the current setting
SET effective_cache_size TO '50GB'; -- Change it for the current session

Common Pitfalls and the “It Does Nothing!” Fallacy

The biggest mistake is setting this ridiculously low or, worse, leaving it at the pathetic default. If you have 128GB of RAM and this is set to 4GB, the query planner is going to assume every index lookup is a slow, random disk I/O. It will develop a crippling fear of indexes and start sequential scanning everything, like a myopic librarian who insists on reading every book in the library to find a single quote.

The second biggest mistake is thinking this parameter doesn’t work because you don’t see an immediate change. It doesn’t force anything to be cached. It doesn’t allocate memory. Its effect is subtle but profound: it changes the planner’s cost calculations. You won’t see a night-and-day difference on a single query. Its value is revealed over time and across the entire workload, as the planner makes consistently better decisions about when to use indexes versus sequential scans. Run EXPLAIN on a large, complex query before and after changing this value; you might see the chosen plan change, which is the smoking gun.

Why This Parameter is a Necessary Hack

Let’s be honest: this setting is a bit absurd. The database shouldn’t need to be told this information. The optimizer should just know how much RAM the OS has available for caching. But we live in the real world, not a perfect one. The PostgreSQL process can’t easily and portably ask every operating system kernel “hey, how much freeable page cache do you have right now?” without introducing significant overhead. So, we give it a hint. A very, very important hint. Get it right, and your planner becomes a savvy strategist. Get it wrong, and it’s making decisions with a blindfold on. Don’t let it be blindfolded.