Slow PostgreSQL Update Snap

[dwhansen-cbg]

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?

[RogerSramkoski]

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.

[dwhansen-cbg]

[dwhansen-cbg]

What are reasonable numbers to bump those up to without bogging the whole system down?

[RogerSramkoski]

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.