What became obvious to me in reading the thread - and probably should have been obvious to me all along - is that this advice only makes sense if you're running a 64-bit build of PostgreSQL, because, on a 32-bit build, each process is limited to 4GB of address space, of which (at least on Linux) 1GB is reserved for the kernel. That means that no matter how much physical memory the machine has, each PostgreSQL backend will be able to address at most 3GB of data. That includes (most significantly) shared_buffers, but also the process executable text, stack, and heap, including backend-local memory allocations for sorting and hashing, various internal caches that each backend process uses, local buffers for accessing temporary relations, and so on. And 3GB is really a theoretical upper limit, if everything is packed optimally into the available address space: I'm not sure how close you can get to that limit in practice before things start breaking.

So, if you're running a 32-bit PostgreSQL on UNIX-like operating system, you probably need to limit shared_buffers to at most 2GB or 2.5GB. If that is less than about 25% of your system memory, you should seriously consider an upgrade to a 64-bit PostgreSQL.

5 comments:

There's also the option of running a 32-bit userspace with a 64-bit kernel, which at least lets you get close to the full 4GB limit, and is much less inconvenient for an existing system than a full 32 to 64 bit upgrade.