โ02-18-2020 03:19 PM
I have a PostgreSQL Update snap at the end of a pipeline. Itโs job is to update a column in a row if nothing in an inbound row (matching that data) has been modified. This means that most of the time I will need to update all rows in my table everyday since the data is usually mostly unchanged from one day to the next.
Iโve noticed that running a PostgreSQL Update step using a WHERE clause โprimary_key = $primary_keyโ makes it run very slow. I have also left the WHERE clause empty to see if that speeds things up but I always end up getting a primary key error.
Is there a way to speed up Update snaps?
โ02-19-2020 07:52 AM
Could you share the batch/fetch size settings for the account and the total number of records youโre checking with the Update snap? If you have not already increased those values in the PosgreSQL account that may be a good first step, especially if youโre working with larger data sets.
โ02-19-2020 07:56 AM
โ02-19-2020 07:56 AM
What are reasonable numbers to bump those up to without bogging the whole system down?
โ02-19-2020 08:06 AM
Try a batch size of 500 and fetch size of 1000 first then observe performance of the pipeline, resource usage on the Snaplex, and if it is an on-premise database, keep an eye on that as well. If that jump doesnโt add impact to either size then you can try to increase it again. Depending on the size of your individual records you might want to only increase in increments of 500 or 1000 to start.