c# - PostgreSQL: Inserting High Quantity in Multi Tables with Foreign Keys -

so, i'm working on project involves inserting huge amount of data on course of day 3 main tables. 3 tables linked each other.

here tables:

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) 

events can happen @ moment, , when do, need record details of subevents , insert them database. there can anywhere 5 500 subevents in 1 event. reason have relationship table , not foreign key column in subevents because there other processes add values subevents don't have parent events. confusing, maybe.

by end of 1 day, maybe have inserted many 10 million subevents , 250,000 events. speed 1 of important things me. 1 of best ways i've found insert them using do $$ declare ... end$$; command. can declare temp integer values , capture ids events , subevents insert , insert them event_relationship table.

here code i'm running, executes pl/pgsql

do $$ declare _new_event_id integer; _new_subevent_id integer; begin     insert event (user_id, event_time) values (@user_id, @event_time)     returning event_id _new_event_id;      insert subevent (subevent_type, subevent_value)     values (@subevent_type, @subevent_value)     returning subevent_id _new_subevent_id;      insert event_relationship values (_new_event_id, _new_subevent_id);  end$$; 

(the first insert once, last 2 inserts repeated each subevent. execute command using c# , npgsql , can build command dynamically process runs.)

however, on course of day, bogs down , data starts point can't insert fast enough. i'm wondering if i'm taking wrong approach here or if there's way i'm doing in faster manner.

you can have foreign key relationship , insert null in referencing table:

create table t (i int primary key); create table t2 (i int references t (i));  insert t2 (i) values (null); insert 0 1  insert t2 (i) values (1); error:  insert or update on table "t2" violates foreign key constraint "t2_i_fkey" detail:  key (i)=(1) not present in table "t". 

or have special value 0 or -1 in referenced table used "orphan" subevents.

to lessen load can insert subevents in single command:

insert subevent (subevent_type, subevent_value) values (@subevent1_type, @subevent1_value), (@subevent2_type, @subevent2_value);