Forum Discussion

dwhansen-cbg's avatar
dwhansen-cbg
Contributor
6 years ago

Slow PostgreSQL Update Snap

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?

pattern
sample_pipeline

4 Replies

  • RogerSramkoski's avatar
    RogerSramkoski
    Employee
    6 years ago

    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's avatar
    dwhansen-cbg
    Contributor
    6 years ago

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

    • RogerSramkoski's avatar
      RogerSramkoski
      Employee
      6 years ago

      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.