11–20 of 31 posts
Would also like this with asyncpg. psycopg2 is kind of obsolete today.
Would also like this with asyncpg. psycopg2 is kind of obsolete today.
Bold claim, since psycopg2 is used overwhelmingly more than any other library. Though asyncpg (or something like it) is likely the future, it’s a long way to go before calling psycopg obsolete.
I 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…
I 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 mi…
There’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.
1 - Generate a static SQL to insert N rows (say 10 to 50): insert into blah (id, name) values ($1, $2), ($3, $4),
2 - Loop through the data in batch size
3 - Generate a dynamic sql for M rows where M is whatever's left over
Wrap it in a transaction. You can have an SQL builder where you give it the list of columns and how many rows you want to generate and use that for both steps 1 and 3. You can prepare statement 1 (and keep reusing it), but I'mnot sure how much that'll buy you.
If you need to deal with conflicts. Inserting the above into a temp table (on commit drop) and then doing an insert + update from the team into the real table works well.
Then there's a handful of postgresql configs to tweak, but if you're willing to risk a bit of data loss (but not corruption), asynchronous_commit=off.
Alternately, if you have an unlogged table already created, why not simply truncate the table if it exists instead of recreating it on every execution? Drop and truncate should take about the same amount of time, but creating a table is going to be slightly more expensive. If you're worried about making sure the db table schema matches the file format, I don't think I would be. File formats change, you're already going to be manually working on it. You can drop the table so the updated script recreates it.
I suppose it doesn't really matter, but I'm wondering if your choice was one of intent or one of convenience.
I 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…
Not an expert, but I'm curious - what use case is not sufficiently solved by savepoints?
Earlier quoted context omitted.
Bold claim, since psycopg2 is used overwhelmingly more than any other library. Though asyncpg (or something like it) is likely the future, it’s a long way to go before calling psycopg obsolete.
Asyncpg is not future, it is present. We use it in production and quite happy with it.