I am trying to do what should be a pretty straightforward insert statement in a postgres database. It is not working, but it's also not erroring out, so I don't know how to troubleshoot. This is the statement:
INSERT INTO my_table (col1, col2) select col1,col2 FROM my_table_temp;
There are around 200m entries in the temp table, and 50m entries in my_table. The temp table has no index or constraints, but both columns in my_table have btree indexes, and col1 has a foreign key constraint.
I ran the first query for about 20 days. Last time I tried a similar insert of around 50m, it took 3 days, so I expected it to take a while, but not a month. Moreover, my_table isn't getting longer. Queried 1 day apart, the following produces the same exact number.
select count(*) from my_table;
So it isn't inserting at all. But it also didn't error out. And looking at system resource usage, it doesn't seem to be doing much of anything at all, the process isn't drawing resources.
Looking at other running queries, nothing else that I have permissions to view is touching either table, and I'm the only one who uses them.
I'm not sure how to troubleshoot since there's no error. It's just not doing anything. Any thoughts about things that might be going wrong, or things to check, would be very helpful.
For the sake of anyone stumbling onto this question in the future:
After a lengthy discussion (see linked discussion from the comments above), the issue turned out to be related to psycopg2 buffering the query in memory.
Another useful note: inserting into a table with indices is slow, so it can help to remove them before bulk loads, and then add them again after.