Incremental refresh taking long

Q&A
, ,
1

Hello,

My dataset incremental refresh is taking very long. Following are the specifics:

  1. My data-set is directly mapped to the table. No custom sql. The underlying RDS is postgresql.
  2. The incremental refresh is based on “create_ts” timestamp column which is in UTC. This is the footprint column so whenever a new row is added, this value gets populated.
  3. There is NO index on this column.
  4. Incremental refresh is set to run every 15 mins and it replaces data for last 1 hr.

Currently I have ~27 Million rows and the incremental refresh is taking upto 1hr. 15 mins to update the data. Can you please advise what could be going wrong?

Thank you.

2

Hi @harpar1808,

Thank you for your question.

Can you take a look at doing the following:

  • Adjusting the refresh frequency: replacing data for only 15 minutes instead of 1 hr
  • Adding an Index on ‘create_ts’ column in your RDS PostgreSQL database
  • Adjusting incremental refresh to targets rows within a specified time range without scanning irrelevant data
  • Partitioning of your table based on time intervals

Regards,
Demola

3

Hello,

Thanks for responding.

  1. I believe the replacing data duration cannot go down less than 1hr. Can you please confirm?
  2. I will try adding an index. Do you think it will help with the performance?
  3. I am already doing that unfortunately I am hard bound by QS limits here… i.e. refresh interval cannot be less than 15 mins and data replace duration cannot be less than 1hr.
  4. how does incremental refresh work with partitioning? any articles out there that I can take a look?

Thanks.

4

Thank for your response.

Yes, index can help with performance and also to your question 4, similar question was solved here

1 Like