This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a workflow that needs to export data into my PostgreSQL database, however the write speeds are very slow. This seems to be a consistent problems, but I can find no clear tips for resolving it. This is almost certainly an Alteryx/ODBC issue as I have previously uploaded tens of millions of records from a text file in minutes using psql, however the output tool is currently taking about 4 hours to write c.2mil.
I am currently running the workflow with Performance profiling so I can give some more details and will update on Monday.
My workflow has information I can't disclose, so bear with me while I sanitise it. I have a relatively high end PC with 32GB of RAM so I doubt that would be the issue. I have also compared two workflows, one that outputs directly to Postgres and one that outputs to CSV then uses copy in a bat file to upload the data. The workflows are otherwise identical. The first ran in four hours 39 mins, the second in 16 mins 25 seconds with one error to resolve.
While the CSV may work in this instance, it is a huge inconvenience that the output tool is that inefficient.
I have attached a packaged workflow which has one of the slowest steps in my master workflow. The attached took 1 hour and 56 mins to run, whereas a master workflow with a sql copy in an executed bat file took 16 mins, including the time to pull data via API.
Postgres version 9.6, ODBC V11. Postgres hosted on linux Azure VM
The ODBC connection loads your data row by row as opposed to the COPY function you leveraged which is more efficient for loading data in Postgres. The bulk loader functionality for Postgres which would leverage the copy command is currently under review.