Fast Way to Load Data into PostgreSQL Using Python
hakibenita.com
Fast Way to Load Data into PostgreSQL Using Python
1–10 of 31 posts
Re: Fast Way to Load Data into PostgreSQL Using Python
#2As a former DBA and closet python super fan I found this post to follow all the things I would have done. Kudos to the op.
Loading to a temp non-logged table is the right way to go and using built in bulk loading options is smart, too. The type annotations looked really good and kudos for using modern python here (although for the examples I’m not sure they were needed, but being that they could have been used in a larger context that this article was just a microcosm if I could be wrong). Very good article. I’ve bookmarked the blog to consume other such articles.
Re: Fast Way to Load Data into PostgreSQL Using Python
#3Very nice article! Explores all the tricks I know of, and a few more. Never heard of the execute_values before, only a bit (3x in the example) slower than using copy but much simpler code.
Re: Fast Way to Load Data into PostgreSQL Using Python
#4I concur - we load a fair amount of data into Postgres using Python and we've learned these lessons the hard way. We ended up feeding generators into execute_batch.
We don't use execute_values, because we want to generate dictionaries (since there are usually multiple generators along the way) and we don't use copy_from, because the cognitive overhead of data prep is just way too high for the benefit (for us! your mileage may vary).
Great stuff, I've already circulated it at work.
Re: Fast Way to Load Data into PostgreSQL Using Python
#5I am looking for ways to quickly load data into a PG database for local testing. An initial seed run is ok, but I want test suites to run on identical data sets and since PG doesn't (yet) support nested transactions, I a currently use my seed database as a TEMPLATE for suite-specific databases.
It works reasonably well but creating the database with a template takes several seconds, even though my test data is fairly simple. I imagine it should be faster than anything I can do from my programming language (nodejs in my case), or is creating with templates slow for some reason that I don't know of?
Re: Fast Way to Load Data into PostgreSQL Using Python
#6There’s also copy_expert in psycopg2. I believe csv with copy_expert should be the fastest but have not tested in as much detail as op. Great work on the article.
Re: Fast Way to Load Data into PostgreSQL Using Python
#7Seems like a pretty good overview, but I'm not surprised at all that in the end it's COPY TO/FROM that won out by a good margin.
Listing all the columns in order seems a bit superfluous and error prone, though. I'd recommend creating the list of columns to be inserted (or copied; copy_from has a 'columns' arg) and slicing the input hash with the same list (dict comprehension in Py).
Re: Fast Way to Load Data into PostgreSQL Using Python
#8Would also like this with asyncpg. psycopg2 is kind of obsolete today.
Re: Fast Way to Load Data into PostgreSQL Using Python
#9I am looking for ways to quickly load data into a PG database for local testing. An initial seed run is ok, but I want test suites to run on identical data sets and since PG doesn't (yet) support nested transactions, I a currently use my seed database as a TEMPLATE for suite-specific databases. It works reasonably well but creating the database with a template takes several seconds, even though my test data is fairly…
[deleted]
Re: Fast Way to Load Data into PostgreSQL Using Python
#10There’s also copy_expert in psycopg2. I believe csv with copy_expert should be the fastest but have not tested in as much detail as op. Great work on the article.
IIRC copy_expert just lets you enter the postgres COPY command that's used to start the transfer manually, which by itself doesn't change anything.
It does allow you to use the BINARY transfer format, though, and that's quite often the fastest version, at the cost of a more complicated data transformation step.