PostgreSQL: Execute queries in loop - performance issues

Question

I need to copy data from a file into a PostgreSQL database. For that purpose I parse that file using bash in a loop and generate the corresponding insert queries. The trouble is that it takes a lot of time in order to perform that loop.

1)What can I do to accelerate that loop? Should I open a kind of connection before the loop and close it after?

2)Should I use a temporary text file inside the loop in order to write there the unique values and search in it using the text utility instead of writing them to the database and perform a search there?

BillRobertson42 · Accepted Answer · 2016-03-19 16:24:19Z

1

Does whatever programming language you use commit after every insert? If so, the easiest thing you can do is commit after inserting all rows rather than after every row.

You might also be able to batch inserts, but using the PostgreSQL copy command is less work and also very fast.

answered Mar 19, 2016 at 16:24

BillRobertson42

12.9k4 gold badges44 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kostiamol Over a year ago

I use bash. Does it matter?

BillRobertson42 Over a year ago

bash doesn't talk to Postgres, at least not as far as I know. Are you calling psql from your bash script? Is the loop in your bash script? If so, then you're also adding overhead to connect and disconnect to the database for each row in the file. copy command is probably the way to go, will be worth the effort, even if you need to process the file to get it into an acceptable format.

johan855 · Accepted Answer · 2016-03-19 17:30:03Z

0

If you insist on using BASH you could split files by defined row numbers and then excecute paralel commands using & at the end of each line.

I strongly suggest you try a different approach or programing language since as Bill said, bash doesn't talk to postgres, you can also use the pg_dump funtionality if your file's source is another postgres database.

answered Mar 19, 2016 at 17:30

johan855

1,6265 gold badges29 silver badges54 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Execute queries in loop - performance issues

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related