slow Inserts PostgreSQL

[baggins2000]

I made a perl script which parses my log file and inserts it into a database. I have been doing this with MySQL and decided to try postgreSQL. The inserts are taking 15 to 20 times longer than what I was getting with MySQL. Since I am new to postgreSQL I was wondering if there is some obvious mistake I could be making with postgreSQL that would account for this. I'm using varchar for a lot of my data, could that account for it.

[[deleted]]

The default setup of postgres is not optimized, you may want to do some tuning in the performance parameters. Check the postgresql manual for the shared-memory setting.

You should also check your database setup, like you said, varchar could cause a slowdown, but only compared to a table setup that has no variable length fields at all.
Try using a transaction to do the updates;
start a transaction, insert your records, commit work.

[smarlowe]

There are two ways to do this:

One: build a bulk import file. To see what a bulk import file should look like, do the following to your database:

pg_dump dbname -t tablename >table.sql

and then look at table.sql, it should have the format of the import data.

Make a file that looks like the table.sql file between the "copy from stdin" part to the . part, and then do this:

psql dbname -e <importfile.sql

and it will read it in very fast, but with no constraint checking.

the other way is to insert all your rows in a transaction like so:

begin;
insert into ....
insert into ....
insert into ....
end;

That will make postgresql insert all the rows as one giant dataset, and will speed things up quite a bit. Plus, if one insert should fail due to data constraint issues, you won't have ANY rows inserted, so you just fix the one line of data and reinsert the whole thing again without having to delete the old rows or figure out which ones got imported.