title: Postgresql: database is not accepting commands to avoid wraparound data loss
body: <p>Got the error upon create/delete/update queries:</p>
  
  <blockquote>
    <p>ERROR:  database is not accepting commands to avoid wraparound data
    loss in database "mydb" HINT:  Stop the postmaster and use a
    standalone backend to vacuum that database. You might also need to
    commit or roll back old prepared transactions.</p>
  </blockquote>
  
  <p>So, the database is blocked and it is only possible to perform SELECT queries.</p>
  
  <p>Database's size 350 GB. 1 table(<strong>my_table</strong>) has ~1 billion rows. </p>
  
  <p>system: "PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit"</p>
  
  <p>postgresq.conf some settings:</p>
  
  <pre><code>effective_io_concurrency = 15           # 1-1000; 0 disables prefetching
  autovacuum_vacuum_cost_delay = -1
  
  #vacuum_cost_delay = 0                  # 0-100 milliseconds
  #vacuum_cost_page_hit = 1               # 0-10000 credits
  #vacuum_cost_page_miss = 10             # 0-10000 credits
  #vacuum_cost_page_dirty = 20            # 0-10000 credits
  #vacuum_cost_limit = 200 
  </code></pre>
  
  <p>I do not use prepared transactions. But basic stored procedures are used(which means, automatic tranactions, right?) 50mln times per day.</p>
  
  <p>Сurrently "autovacuum: VACUUM ANALYZE public.<strong>my_table</strong> (to prevent wraparound)" is perforing, it is almost 12 hours of that query activity. </p>
  
  <p>As far as I understand, the problem with not-vacuumed dead touples, right?</p>
  
  <p>How to resolve this problem and prevent this in the future? Please, help :)</p>
  
  <p><strong>The end of story( ~one  month later)</strong>
  Now my big table is <strong>partitioned</strong> by thousands of tables. Each small table is vacuumed much faster. Autovacuum configuration was set more closer to default. If needed, i could be set to more agressive again, but so far database with billions of rows works pretty well.</p>
  
  <p>So, the problem of the topic should not appear again. </p>
  
  <p>ps now i'm looking at Postgres-XL as a next step of data scalability.</p>