The official documentation for PostgreSQL features an entire section on Populating a Database. However, using execute_values we got results ~20% faster compared to the same page size using execute_batch. Here as well, the sweet spot is around page size 1000.
Just like execute_batch, we see a tradeoff between memory and speed.
> insert_execute_values_iterator ( connection, iter ( beers ), page_size = 1 ) insert_execute_values_iterator(page_size=1) Time 127.4 Memory 0.0 > insert_execute_values_iterator ( connection, iter ( beers ), page_size = 100 ) insert_execute_values_iterator(page_size=100) Time 3.677 Memory 0.0 > insert_execute_values_iterator ( connection, iter ( beers ), page_size = 1000 ) insert_execute_values_iterator(page_size=1000) Time 1.468 Memory 0.0 > insert_execute_values_iterator ( connection, iter ( beers ), page_size = 10000 ) insert_execute_values_iterator(page_size=10000) Time 1.503 Memory 2.25 The function execute_values works by generating a huge VALUES list to the query. While strolling through the documentation, another function called execute_values caught my eye:Įxecute a statement using VALUES with a sequence of parameters. The gems in psycopg's documentation does not end with execute_batch. In this case, it seems that the sweet spot is page size of 1000. The results show that there is a tradeoff between memory and speed.
10000: Timing is not much faster than with a page size of 1000, but the memory is significantly higher. 1000: The timing here is about 40% faster, and the memory is low. 100: This is the default page_size, so the results are similar to our previous benchmark. 1: The results are similar to the results we got inserting rows one by one. We got some interesting results, let's break it down: > insert_execute_batch_iterator ( connection, iter ( beers ), page_size = 1 ) insert_execute_batch_iterator(page_size=1) Time 130.2 Memory 0.0 > insert_execute_batch_iterator ( connection, iter ( beers ), page_size = 100 ) insert_execute_batch_iterator(page_size=100) Time 4.333 Memory 0.0 > insert_execute_batch_iterator ( connection, iter ( beers ), page_size = 1000 ) insert_execute_batch_iterator(page_size=1000) Time 2.537 Memory 0.2265625 > insert_execute_batch_iterator ( connection, iter ( beers ), page_size = 10000 ) insert_execute_batch_iterator(page_size=10000) Time 2.585 Memory 25.4453125 The Data is dirty and needs to be transformed. The Data is fetched from a remote source. To provide a real life, workable solution, we set the following ground roles: In this article we explore the best way to import messy data from remote source into PostgreSQL. Modern services might provide a decent API, but more often that not we need to fetch a file from an FTP, SFTP, S3 or some proprietary vault that works only on Windows. When we are less fortunate, we get an Excel spreadsheet or a CSV file which is always broken in some way, can't explain it.ĭata from large companies or old systems is somehow always encoded in a weird way, and the Sysadmins always think they do us a favour by zipping the files (please gzip) or break them into smaller files with random names. If we are lucky, the data is serialized as JSON or YAML. Fastest Way to Load Data Into PostgreSQL Using Python From two minutes to less than half a second!Īs glorified data plumbers, we are often tasked with loading data fetched from a remote source into our systems.
0 kommentar(er)
