Adjusting memory parameters
After we find the slow queries, we can do something about them. The first step is always to fix indexing and make sure that sane requests are sent to the database. If you are requesting stupid things from PostgreSQL, you can expect trouble. Once the basic steps have been performed, we can move on to the PostgreSQL memory parameters, which need some tuning.
Optimizing shared buffers
One of the most essential memory parameters is
shared_buffers. What are shared buffers? Let's assume we are about to read a table consisting of 8,000 blocks. PostgreSQL will check if the buffer is already in cache (
shared_buffers), and if it is not, it will ask the underlying operating system to provide the database with the missing 8,000 blocks. ...