I'm writing a large number of records to a postgres database, using psycopg2.extras.execute_values(cursor, query, data, page_size=100)
I get what the page_size parameter does, but don't really know what would be a sensible value to set it to. (Above uses the default value of 100.) What are the downsides of simply setting this to something ridiculously large?
Based on my understanding, the page_size gives the size of input values per sql statement. Give larger number means longer sql statement, and hence more memory usage for the query. If you do not need the query to return any values, it would be safe to use a smaller value such as 100 by default.
However, if you would like to insert/update certain table with returning statement, you may like to increate page_size to at least the same length as your data. You may set it at length(data) (your data should be a list of lists or a list of tuples), and the downside is that you have to introduce some limit to the number of data values per call. Postgresql allows very long sql, so if you have enough memory, millions of records should be acceptable.
