PostgreSQL tuning: ensuring that as many sorts as possible are done in memory and not on disk

(This post assumes a PostgreSQL installation located at /var/lib/pgsql on a Red Hat-type Linux system such as Red Hat Enterprise Linux or CentOS. If your system differs from this, you may need to modify some of the paths accordingly.)

In PostgreSQL, sorts larger than a certain size will get performed on disk instead of in memory, and this makes them much slower as a result. Ideally all sorts should be done in memory (except for the ones that are genuinely too big to fit into your available RAM, because swapping to virtual memory should be avoided at all costs).

Continue reading “PostgreSQL tuning: ensuring that as many sorts as possible are done in memory and not on disk”