Right, let’s talk about the one thing every process in a PostgreSQL cluster agrees on: shared memory. Think of it as the communal kitchen in a shared house. It’s where all the roommates (your backend processes) leave notes, stash commonly used food (data), and argue over who used the last of the milk (row locks). If this kitchen is too small, chaos ensues. If it’s too big, you’re wasting rent money. Let’s break down the main appliances in this kitchen.

The Communal Fridge: Shared Buffers

This is the big one. shared_buffers is the area of memory where PostgreSQL caches table and index data, trying to avoid the excruciatingly slow trip to the fridge in the garage (your disk). When a process needs a page (an 8KB chunk of your table), it first checks the Shared Buffers. If it’s there (a cache hit), it’s lightning fast. If not (a cache miss), it has to go to disk, grab it, and then load it into the Shared Buffers for next time.

So, how big should you make this glorious data fridge? The old default was pathetically small, something like 128MB, a holdover from when systems had 512MB of RAM total. These days, that’s barely enough for the milk and a few beers. A good starting rule of thumb is 25% of your total RAM, but don’t just set it and forget it. On a dedicated database server with 64GB of RAM, 16GB is a perfectly sane value. Going much beyond 40% usually has diminishing returns, as the OS will use the rest of the RAM for its own disk caching, which is actually helpful.

Here’s how you set it in postgresql.conf:

# 16GB of RAM dedicated to shared_buffers
shared_buffers = 16GB

And you can check what you’re running with:

SELECT name, setting, unit FROM pg_settings WHERE name = 'shared_buffers';

The most common pitfall? Setting it comically low. If your database is more than a few hundred megabytes, a 128MB shared buffer will mean your server is doing nothing but grinding its disk to dust. The other edge case is on Windows, where this value must be set lower due to how the OS handles shared memory.

The Emergency Notepad: WAL Buffers

While the Shared Buffers are the main fridge, the Write-Ahead Log (WAL) buffer is the small, urgent notepad on the counter where you scribble “WE’RE OUT OF MILK!” before you actually go to the store. PostgreSQL’s core integrity rule is that any change described in the WAL must already be safely on disk before the actual data page in Shared Buffers can be written to disk.

The wal_buffers setting controls the size of this notepad. By default, it’s a measly -1, which means “let me figure it out, I’m an adult.” PostgreSQL will automatically size it to 1/32 of your shared_buffers, with a maximum of 16MB. This is almost always fine. For the vast majority of workloads, you never need to touch this.

The only time you might need to increase it is if you have an extremely write-heavy workload with many concurrent transactions submitting small writes. A too-small WAL buffer can become a contention point. Bumping it to 16MB explicitly is a safe bet if you suspect this.

# Set it explicitly to the maximum typical value
wal_buffers = 16MB

The Chore Chart: Lock Tables

This is the whiteboard where the roommates coordinate who’s doing what to avoid conflicts. The max_locks_per_transaction setting (and its derived value, the size of the shared lock table) doesn’t control a fixed amount of memory, but it dictates how many objects (tables, rows, etc.) can be locked concurrently.

The default is usually 64, which is plenty… until it very suddenly isn’t. If you have a transaction that needs to touch thousands of tables (think: a massive batch operation on a partitioned table with thousands of child tables), you can blow right past this limit. When that happens, you get a dreaded “out of shared memory” error, which is PostgreSQL’s polite way of saying the chore chart is full and it refuses to believe you have that many dishes to wash.

The fix is to bump this value before you run your massive operation. You don’t need to set it sky-high permanently, just high enough for your biggest transaction.

# For a system with a 1000-table partition set
max_locks_per_transaction = 256

Remember, the total shared lock memory is calculated as max_locks_per_transaction * max_connections. So cranking it to 10,000 on a system with 500 connections is asking for a lot of dedicated memory. Be sensible.

The real insight here is that these three components are the foundation of PostgreSQL’s process architecture. They allow dozens of independent processes to collaborate on a single dataset without corrupting it or murdering each other. Size them wisely, and your database will hum along. Get them wrong, and you’ll be that house with the constantly flashing breaker box.