title: Bulk insert of hundreds of millions of records
body: <p>What is the fastest way to insert 237 million records into a table that has rules (for distributing data across child tables)?</p>
  
  <p>I have tried or considered:</p>
  
  <ol>
  <li>Insert statements.</li>
  <li>Transactional inserts (<code>BEGIN</code> and <code>COMMIT</code>).</li>
  <li>The <code>COPY FROM</code> command.</li>
  <li><a href="http://pgbulkload.projects.postgresql.org/" rel="nofollow noreferrer">http://pgbulkload.projects.postgresql.org/</a></li>
  </ol>
  
  <p>Inserts are too slow (four days) and <code>COPY FROM</code> ignores rules (and has other issues).</p>
  
  <p>Example data:</p>
  
  <pre><code>station_id,taken,amount,category_id,flag
  1,'1984-07-1',0,4,
  1,'1984-07-2',0,4,
  1,'1984-07-3',0,4,
  1,'1984-07-4',0,4,T
  </code></pre>
  
  <p>Table structure (with one rule included):</p>
  
  <pre><code>CREATE TABLE climate.measurement
  (
    id bigserial NOT NULL,
    station_id integer NOT NULL,
    taken date NOT NULL,
    amount numeric(8,2) NOT NULL,
    category_id smallint NOT NULL,
    flag character varying(1) NOT NULL DEFAULT ' '::character varying
  )
  WITH (
    OIDS=FALSE
  );
  ALTER TABLE climate.measurement OWNER TO postgres;
  
  CREATE OR REPLACE RULE i_measurement_01_001 AS
      ON INSERT TO climate.measurement
     WHERE date_part('month'::text, new.taken)::integer = 1 AND new.category_id = 1 DO INSTEAD  INSERT INTO climate.measurement_01_001 (id, station_id, taken, amount, category_id, flag)
    VALUES (new.id, new.station_id, new.taken, new.amount, new.category_id, new.flag);
  </code></pre>
  
  <p>The data was originally in MySQL, but must be switched to PostgreSQL for performance reasons (and to leverage the PL/R extension).</p>
  
  <p>Thank you!</p>