CODEWITHSUNDEEP
sqlpostgresql
Siva
Siva

Reputation: 75

Optimization required for multiple insertions in PostgreSQL

I basically have 3 tables. One being the core table and the other 2 depend on the 1st. I have the requirement to add upto 70000 records in all the tables. I do have constraints (primary & foreign keys, index, unique etc) set for the tables. I can't go for bulk import (using COPY command) as there is no standard .csv file in requirement, and the mapping is explicitly required plus few validations are externally applied in a C based programming file. Each record details (upto 70000) will be passed from .pgc (an ECPG based C Programming file) to postgresql file. It takes less time for the 1st few records and the performance is turning bad to the latter records! The result is very sad that it takes days to cover upto 20000! What are the performance measures could I step in into this? Please guide me.

My master table's schema is 

CREATE TABLE contacts 
( contact_id SERIAL PRIMARY KEY
, contact_type INTEGER DEFAULT 0
, display_name TEXT NOT NULL DEFAULT ''
, first_name TEXT DEFAULT ''
, last_name TEXT DEFAULT ''
, company_name TEXT DEFAULT ''
, last_updated TIMESTAMP NOT NULL DEFAULT current_timestamp
, UNIQUE(display_name)
) WITHOUT OIDS;

Upvotes: 0

Views: 164

Answers (1)

Frank Farmer
Frank Farmer

Reputation: 39356

Drop/disable indexes/triggers, and use a COPY. We use this to import millions of rows and gigabytes of data in a matter of minutes.

The docs cover this in depth here: http://www.postgresql.org/docs/9.1/static/populate.html

Postgres is great at bulk loading data, if you do it the right way.

Upvotes: 1

Related Questions