PostgreSQL Tuning

If queries start to fail, such as with automated exporting of hours and jobs, it is possible that you need to tun out PostgreSQL a bit. Depending on how PostgreSQL is compiled and configured and how large the database grows, changing some of the values for postmaster may increase performance and prevent the backend from dying on large queries.

More specifically, postmaster can be passed two values which impact it's performance. One is the number of shared-memory buffers the postmaster is to allocate for backend server processes. The value is in 8kb blocks and is passed to postmaster with a -B flag when starting the server. As an example, we have a database with about 12,000 hours records (about 1/3 as many jobs records). On certain multi-select statements postmaster will die with the default value for shared-memory buffers, so we have it allocating 128 8kb blocks on a machine with about 200 megs of ram.

The second value has to do with memory to use before resorting to using disk to do sorting. In that same example above we allocated 1024 kilobytes for each process without resorting to swapping on disk. This value is passed along to postmaster with a -S flag when starting the server.