title: PostgreSQL: Inserting High Quantity in Multi Tables with Foreign Keys
body: <p>So, I'm working on a project that involves inserting a huge amount of data over the course of a day into three main tables. The three tables are all linked to each other.</p>
  
  <p>Here are the tables: </p>
  
  <pre><code>event
      user_id
      event_time
      event_id (PRIMARY) (Serial Int)
  
  subevent
      subevent_type
      subevent_value
      subevent_id (PRIMARY) (Serial Int)
  
  event_relationship
      event_id (1)
      subevent_id (MANY)
  </code></pre>
  
  <p><code>events</code> can happen at any moment, and when they do, I need to record the details of the <code>subevents</code> and insert them into the database. There can be anywhere from 5 to 500 <code>subevents</code> in one <code>event</code>. The reason I have a relationship table and not just a foreign key column in <code>subevents</code> is because there are other processes that add values to <code>subevents</code> that don't have parent <code>events</code>. Confusing, maybe.</p>
  
  <p>By the end of one day, I maybe have inserted as many as 10 million <code>subevents</code> and 250,000 <code>events</code>. So speed is one of the most important things to me. One of the best ways I've found to insert them all together is using a <code>DO $$ DECLARE ... END$$;</code> command. I can declare temp integer values and capture the ids for the <code>events</code> and <code>subevents</code> that I insert and then insert them together into the <code>event_relationship</code> table.</p>
  
  <p>Here is the code I'm currently running, which executes as PL/pgSql</p>
  
  <pre><code>DO $$ DECLARE _new_event_id INTEGER; _new_subevent_id INTEGER;
  BEGIN
      INSERT INTO event (user_id, event_time) VALUES (@user_id, @event_time)
      RETURNING event_id INTO _new_event_id;
  
      INSERT INTO subevent (subevent_type, subevent_value)
      VALUES (@subevent_type, @subevent_value)
      RETURNING subevent_id INTO _new_subevent_id;
  
      INSERT INTO event_relationship VALUES (_new_event_id, _new_subevent_id);
  
  END$$;
  </code></pre>
  
  <p>(The first insert is only once, the last two inserts are repeated for each subevent. I execute the command using C# and NpgSql and can build the command dynamically as the process runs.)</p>
  
  <p>However, over the course of the day, this bogs down and my data starts to back up to the point where I can't insert it all fast enough. I'm just wondering if I'm taking the wrong approach here OR if there's another way to do what I'm already doing but in a faster manner.</p>