title: Importing multiple large data to AWS RDS Postgres making it slow
body: <p>In our current Application we take day wise backup to our s3 bucket. These backups are per day and per table in a CSV format.</p>
  
  <p>Our requirement is to import data from s3 for some date range, lets say we needed to see data from 1st Jan, 2015 to 31st July 2015, we download data from s3 to out ec2 instance and import to the newly created postgres DB.</p>
  
  <p>But as time goes the import becomes horribly slow. Following are steps I have doing during import:</p>
  
  <ol>
  <li>Create RDS postgres-9.6 instance with m3-XLarge and 600GB space and General SSD Storage type.</li>
  <li>Switched off replica and multi AZ instances.</li>
  <li>Import schema with no foreign key and no index.</li>
  <li>Set tables to UNLOGGED and switched off autovacuum.</li>
  <li><p>Use COPY command for import.</p>
  
  <p><code>psql -h &lt;HOST_NAME&gt; -d &lt;database_name&gt; -U &lt;user_name&gt; -c "\COPY TABLE_NAME FROM '&lt;exact_path_of_the_file&gt;' CSV DELIMITER ',' HEADER"</code></p></li>
  </ol>
  
  <p>When the import starts it easily consumes file of 2GB within 2 mins. But after importing data for a month the import becomes very slow. For importing file of same size the time increases to 30 mins (I'm comparing file size for the same table for 2 different date).</p>
  
  <p>I have tried splitting file into smaller chunks and do the importing, but didn't see any improvement in the import timing.</p>
  
  <p>What I Have observed from RDS monitoring is that the swap space has increased from 0 to 50... Don't know if thats normal. Any input for improving import will be helpful.</p>
  
  <p>Thanks in advance.</p>
  
  <p><strong>[PS: All steps are in shell command]</strong></p>