PostgreSQL Shared Buffers Calculator

The solution is to not expect shared buffers to be a silver bullet for all slow queries in PostgreSQL. You may have some slowness, bump up the setting and hope for the best, but that won’t work too many times. Why? Because memory management are a team sport where both the operating system and the database engine share the burden.

A calculator helps determine how much of your available RAM will go towards cache, OS reserve, connections under memory pressure, and how much of your set will be covered by hot sets. In other words, the calculator makes you think about what else in your RAM gets affected when you give it to Postgres so you can pick a starting point that’s safe-ish enough to test out in prod.

How to Set Memory for PostgreSQL

So why do people screw up? They forget about the working set. Even if your database is a terabyte (or two), maybe only 20% of it are active at peak time. Storing all of it in shared buffers is inefficient. So the calculator prompts you for how big your database is, and then reduces it based on what percentage is actualy being used.

And this does matter: Memory is limited. Too much for shared buffers mean starving the Linux page cache. That’s the OS cache which kicks in for reads that don’t hit the database buffer pool. Killing that tends to make sequential scans slow instead of fast.

Another issue is the work_mem setting, which determine the maximum amount of memory that will be used by a query during a hash or sort operation. This is a per-connection setting (and also a per-operation setting). That means if you’re serving 50 connections with complex reports at once and each report does several sorts, your memory usage go through the roof.

Our tool takes into account the number of simultaneous users you expect and shows what all those connection mean for the overall pressure they exert on your system. That lets you know whether it’s time to reduce work_mem or instead deploy a connection pooler such as PgBouncer. You’ll avoid OOM errors that come just as traffic spike.

There’s also the added wrinkle of huge pages. If enabled, PostgreSQL will use bigger memory blocks that has lower overhead (and may perform better with big buffer pools). But this means reserving a fixed amount of RAM and setting things up carefuly. We tweak our recommendations depending on if you’re trying to use huge pages or keep them turned off. That’s especially important for dedicated servers where you’ll want everything optimized down to the last cycle vs Shared hosts is places where flexibility is most important.

A shared server require a shift in perspective. For example, if you run a web proxy or application server on the same box as your database, then you can’t assume that all of the system budget belongs solely to PostgreSQL. You’ll want to account for how much RAM you’re really dedicating to PostgreSQL. Leaving some headroom allows the operating system and any other processes to operate without swapping, avoiding thrashing that slows everything down enough to cause a complete outage. That balance is the difference between a responsive app and one that hangs while waiting for disk I/O.

When it comes to tuning the memory of your PostgreSQL instance, there isn’t necessarily “the right” amount of anything (instead), it’s about preventing resources from being fought over. Here are some common starting points based off workload, ranging from light-weight home lab instances through to high-usage analytics replica servers. Think of them as a place to start, but nothing definitive.

Once restarted with updated values, monitor your busiest times. Watch for swap usage and monitor your cache hit ratio. High hit ratios combined with slow performance could indicates that you’ve starved the OS cache. Tweak accordingly. Memory planning involves watching and waiting. Go in conservative, observe carefully and use real-world performance numbers to determine your next step; you should of don’t chase theoretical maxes.