I am using a PostgreSQL database and a big pandas DataFrame (~3500 records) that has to be uploaded into one of the tables in the database. First, I have to test for existing records in the database and, afterwards, upload non existing ones. The problem is that the CPU gets heavily loaded and sometimes my old "server" crashes. So I divided the queries into smaller ones. In the case of the uploading, I use the df.to_sql() method with the option chunksize=150, so that the upload takes place in chunks of 150 records. In the case of the testing for existing records, I use the column in the dataframe that is defined as primary key in the database table (it is of type datetime, or Timestamp). So I have a long where clause like this: SELECT datetime FROM my_table WHERE datetime = datetime_row1_df OR datetime = datetime_row2_df ... OR datetime = datetime_row3500_df In order to reduce the CPU load, I divided this query into smaller ones, so that the first one tests for the existence in the database of the dataframe rows from 1 to 150, the second query tests for the existence of the dataframe rows from 151 to 300, and so on. So I have two questions: Regarding the df.to_sql() method, is it a good method to reduce the CPU load? Regarding the partition of the where clause in the SQL query, I'm wondering if what I'm doing is putting even more strain on the CPU. I think that with the first chunk, the server seeks the entire datatable, with the second one, again an entire search, and so on. Would it be better to keep it as a single and long query? If the answer is affirmative, what alternatives do I have here in order to reduce CPU load for this query? Continue reading...
