title: PostgreSQL performance tuning and max connections (practically)
body: <p>For the past few weeks I've been tuning and messing with PostgreSQL which I'm going to use with my next project.</p>
  
  <p>My Specs are:</p>
  
  <ul>
  <li>DigitalOcean 8 Cores 16GB SSD x2 (One for DB, another for Web)</li>
  <li>Centos 7</li>
  <li>PHP5 , Nginx</li>
  </ul>
  
  <p>The things that I've tried:</p>
  
  <ol>
  <li><p>Pgtune</p></li>
  <li><p>PgBouncer &amp; Pgpool (connection pooling &amp; load balancing)</p></li>
  <li><p>Tuning php-fpm &amp; nginx (worker_processes, worker_connections,
      pm.max_children etc)</p></li>
  <li><p>Linux file handle limits and socket tweaking.</p></li>
  </ol>
  
  <p>I'm testing it by calling the webpage with ApacheBench to insert. Is it practical? </p>
  
  <pre><code>ab -n 17500 -c 1750 -r http://example.com/insert.php
  </code></pre>
  
  <p>So far I can only get it to handle 1700-2000 connections concurrently without dropping any transaction (usually <strong>prematurely closed connection</strong> or <strong>resource temporarily unavailable</strong> in nginx error log or <strong>Sorry, too many clients already</strong> return by PostgreSQL). </p>
  
  <p>I tried both TCP/IP and unix socket for php-fpm and TCP/IP seems to be more scalable than unix socket.</p>
  
  <p>Can PHP use connection pooling? Since the way I'm calling the DB from the web server are still the same(making alot of individual connections to pgpool or pgbouncer).</p>
  
  <p>My goal is to handle at least 10,000 transactions concurrently. What are the deciding factors? Is it a bottleneck between web server to db (php-fpm) or PostgreSQL itself? Usually, how do big companies (PHP web application) handle such volume? </p>