Troubleshooting Postgres Performance ProblemsFeeding the Cloud
<a href="https://creativecommons.org/licenses/by-sa/4.0/">Creative Commons Attribution-ShareAlike 4.0 International License</a>
https://feeding.cloud.geek.nz/posts/troubleshooting-postgres-performance/Feeding the Cloudikiwiki2012-11-04T04:30:26Zhttps://feeding.cloud.geek.nz/posts/troubleshooting-postgres-performance/comment_1_0790e5494697a1b95e6231a40628ccfd/Robe2012-11-04T04:30:26Z2009-05-31T00:25:02Z
<p>The <a href="http://wiki.postgresql.org/wiki/Performance_Optimization">PostgreSQL wiki</a> has an abundance of further information on this topic.</p>
<p>As rule of thumb:</p>
<p>If you're CPU-bound (backends consuming all available CPU) you need to reduce the complexity and/or amount of the queries hitting the database.</p>
<p>If you're disk-bound (backends waiting for disk IO)</p>
<p>on writes:</p>
<p>Get a RAID-Controller with battery backed write cache</p>
<p>on reads:</p>
<p>You need to either increase your RAM to match the size of your working set or reduce the size of the working set.</p>
<p>Most often people mix <a href="http://en.wikipedia.org/wiki/OLTP">OLTP</a> (live/fresh/regularly used) and <a href="http://en.wikipedia.org/wiki/Data_warehouse">DWH</a> (old/not regularly used/historical) data which causes the working set to grow indefinitely. Partitioning/Archiving historical data usually fixes these issues.</p>