title: Postgres performance for a table with more than Billion rows
body: <p>I am doing a PoC to check if Postgres is the right candidate for our use cases.</p>
  
  <p>I have the following workload:</p>
  
  <p><strong>Data query:</strong>
  Presentation layer will retrieve data every 15 mins for last 2 weeks</p>
  
  <p><strong>Data load:</strong>
  Every 15 mins, 5 Million rows of data is loaded into a table and I have observed that it is consuming 375MB for that load.
  Per day, it would be 480 Million rows with table size as 36GB.</p>
  
  <p>After I loaded data for couple of days (approx. 1Billion rows in a table), I ran few queries and I observe that select queries are not responding for hours. e.g. select count(*) .. and select * .. simple but heavy queries. no joins.</p>
  
  <p>My requirement is to load the data every 15min and store it for couple of months but I have not yet reached that far. Even with couple of days of data for above work load, I observe that select queries are not responding. </p>
  
  <p><strong>I wonder if postgres has any limitation with this kind of work load or if I have not tuned it right! Did I miss to configure any key parameter?</strong></p>
  
  <p>I have gone through postgres official documentation (<a href="https://www.postgresql.org/about/" rel="nofollow noreferrer">https://www.postgresql.org/about/</a>) on the limits and my requirement has not really reached the theoretical limits specified in postgres.</p>
  
  <p><strong>Postgres configuration:</strong>
  Below are the postgres parameters that I have configured.</p>
  
  <pre><code>checkpoint_completion_target | 0.9
  default_statistics_target    | 500
  effective_cache_size         | 135GB
  maintenance_work_mem         | 2GB
  max_connections              | 50
  max_stack_depth              | 2MB
  max_wal_size                 | 8GB
  min_wal_size                 | 4GB
  shared_buffers               | 45GB
  wal_buffers                  | 16MB
  work_mem                     | 471859kB
  </code></pre>
  
  <p><strong>Server configuration:</strong></p>
  
  <p>Virtualized Hardware!</p>
  
  <p>vCPUs: 32</p>
  
  <p>RAM: 200GB</p>
  
  <p><strong>I wonder if postgres needs a physical dedicated hardware.</strong> Perhaps it can't handle this load on Virtualized Hardware!</p>
  
  <p>Appreciate if you have comments or suggestions on this.
  BR/Nag</p>