Speeding Up Generating Data in Postgres

published at 17 Apr, 2014 by Szymon LipiƄski tags: database postgresql python

I had this stupid idea for quite a long time. PostgreSQL has the COPY command. It is really fast and it is the best way to load CSV like file to the database. So I had an idea: what about not creating an SQL query from an ORM object, but instead generate a CSV file and load it using the COPY?

Of course it doesn’t mean that creating the whole huge file and loading this later. You can always just generate one CSV row, and load it using a pipe to the psql process.

I described the idea at the End Point’s Blog. It seems that it can speed up loading things even 5 times.