title: Simple postgresql Insert/Update queries taking *seconds* to respond every 5-7 minutes
body: <p>I have a Ruby on Rails app that is using a Postgresql database. I've noticed that my database performance has huge spikes every 5-7 minutes.  </p>
  
  <p><img src="https://i.stack.imgur.com/1oLuE.png" alt="newrelic request response time - database spikes"></p>
  
  <p>I'm seeing 1+ second response times for simple queries like:</p>
  
  <pre><code>UPDATE users SET last_seen_at = ? where id = ?
  </code></pre>
  
  <p>or</p>
  
  <pre><code>INSERT INTO emails (email, created_at, updated_at) VALUES (?, ?, ?)
  </code></pre>
  
  <p>The VPS is an AWS EC2 instance (m2.2xlarge) with a 4 core Xeon 2.4ghz and 34gb of memory.</p>
  
  <p><a href="https://gist.github.com/joshm1/3170215b77898583e4ce" rel="nofollow noreferrer">Here is my postgresql.conf</a></p>
  
  <p>I made to following changes to the conf to try to figure it out (like reducing the # of checkpoint timeouts) to no avail.</p>
  
  <pre><code>root:/etc/postgresql/9.2/main# diff postgresql.conf.bck postgresql.conf
  176,178c176,178
  &lt; #checkpoint_segments = 3    # in logfile segments, min 1, 16MB each
  &lt; #checkpoint_timeout = 5min    # range 30s-1h
  &lt; #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
  ---
  &gt; checkpoint_segments = 10    # in logfile segments, min 1, 16MB each
  &gt; checkpoint_timeout = 30min    # range 30s-1h
  &gt; checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
  361c361
  &lt; #log_min_duration_statement = -1  # -1 is disabled, 0 logs all statements
  ---
  &gt; log_min_duration_statement = 2s  # -1 is disabled, 0 logs all statements
  370,371c370,371
  &lt; #debug_print_rewritten = off
  &lt; #debug_print_plan = off
  ---
  &gt; #debug_print_rewritten = on
  &gt; #debug_print_plan = on
  376c376
  &lt; #log_duration = off
  ---
  &gt; #log_duration = on
  378c378
  &lt; #log_hostname = off
  ---
  &gt; #log_hostname = on
  399c399
  &lt; #log_lock_waits = off     # log lock waits &gt;= deadlock_timeout
  ---
  &gt; log_lock_waits = on     # log lock waits &gt;= deadlock_timeout
  </code></pre>