# With \>100k records, things are breaking 😕

**URL:** <https://community.fibery.io/t/with-100k-records-things-are-breaking/5237>\
**Category:** Get Help\
**Created:** [October 3, 2023, 12:42am UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237 "2023-10-03T00:42:15Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [October 3, 2023, 12:42am UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/1 "2023-10-03T00:42:15Z")

</div>

Working with a DB having ~160k records, I am consistently having problems like:

- Views will not load; `"Canceling statement due to statement timeout"`
- Cannot perform even fairly simple graphQL queries with a limit of 20-100 records; `"The upstream server is timing out"`
- Loading a DB setup page or a Rule page can take minutes, causing browser to repeatedly prompt that _“page is unresponsive”_ before eventually loading… maybe.
- The UI for all Fibery pages grinds to a halt and uses up all memory

* * *

I am faced with the _simple task_ of deleting a few thousand records, but can find no way to easily accomplish this, because everything I try simply times out or gets canceled due to the above issues.

Additionally, when these errors occur, there is _no feedback_ about whether the aborted operation was completely or partially executed, or rolled-back, or …?

E.g. the graphQL API call below might succeed if the limit is \<80, but otherwise will consistently return “upstream server is timing out”:

```auto
mutation {
    callStats(
        period: { name: {is:"Day"} }
        limit: 100)
    { delete { message } }
}

```

**Deleting a few thousand records defined by such a simple filter should be easy.** But It is impossible without resorting to complex API-call loops.

The obvious thing would be to use a Rule to delete old records, but this does not work because:

- Rules also suffer from the same timeout issues.
- There is no way to use `LIMIT` with Rules (unless in a script)
- Rules only run max once per hour - not enough if each API call is limited to deleting 50 records and there are tens of thousands to delete.

I have resorted to looping a local script to repeatedly send a graphQL query to delete 50-70 records at a time - each query can take 20-60 seconds to finish, or it might just return an error, with no indication of what (if anything) if actually accomplished.

Is this _really_ the best we can do in a modern “no code” platform?  
It should not take days to delete a few thousand records.

* * *

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/5/5db93d857ad1560ca8acbcb3df4f2f24838d980c.png)

* * *

P.S. I did ask for help with this issue via Chat, but that was 3 days ago and I have received no help.  
So, posting here.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 3, 2023, 6:58am UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/2 "2023-10-03T06:58:12Z")

</div>

I can’t see an unanswered chat from you, but I can see a chat where we are still digging into the problem. I expect @Kseniya_Piotuh will get back to you when there is news.  
It certainly sounds like there is something funny going on, beyond what should be expected for a large database.

---

<div class="post-metadata">

**Author:** ![Yuri\_BC](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/yuri_bc/32/8803_2.png) [@Yuri\_BC](https://community.fibery.io/u/Yuri_BC)\
**Post date:** [October 3, 2023, 12:29pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/3 "2023-10-03T12:29:15Z")

</div>

I’m also keen to understand the architectural decisions behind Fibery. When a system like fibery struggles with rendering pages or performing tasks already with 100k records, its a serious scalability or performance optimization issue.  
Specifically, how have you designed and optimized the system to efficiently handle large datasets and address potential performance bottlenecks? For example:

1. **Challenges** :

- Handling performance slowdowns associated with data growth.
- Addressing physical storage limitations.
- Managing resource-intensive complex queries.

1. **Data Management Strategies** :

- Techniques to optimize database queries.
- Implementation of connection pools.
- Use of database indexing and caching.
- Approaches to avoid complex joins and reduce the number of database queries.
- Methods for data compression to reduce storage space and improve retrieval times.

1. **User Experience and Data Retrieval** :

- Use of pagination to manage data display.
- Implementation of lazy loading for on-demand data retrieval.

1. **Scaling Approaches** :

- Vertical scaling strategies to enhance a single server’s capabilities.
- Horizontal scaling methods, including the use of replication and sharding.

1. **Scaling Complexities** :

- How you handle query routing in sharded systems.
- Ensuring data consistency in replicated databases.
- Managing the increased administrative tasks associated with scaling.

1. **Infrastructure and Hardware** :

- Strategies for hardware and infrastructure scaling, including cloud solutions.
- Techniques for archiving old, infrequently accessed data.

1. **Monitoring** :

- Techniques to pinpoint performance bottlenecks.
- Strategies to ensure optimal resource usage.

Thank you in advance for shedding light on these aspects.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 3, 2023, 1:04pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/4 "2023-10-03T13:04:04Z")

</div>

I don’t think you can expect to get an in-depth answer to this.  
Fibery has a team of experienced developers, and the system has been designed with various performance criteria in mind, including with large datasets.  
We are considering making some constraints/limitations explicit, so that users can set their expectations at the correct level.  
We are actively reviewing the case that @Matt_Blais has raised.

---

<div class="post-metadata">

**Author:** ![Oleg](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/oleg/32/1887_2.png) [@Oleg](https://community.fibery.io/u/Oleg)\
**Post date:** [October 5, 2023, 9:38am UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/5 "2023-10-05T09:38:17Z")

</div>

Hello, @Matt_Blais

Thanks for feedback. Regarding the issue related to GraphQL upstreaming errors, I would like to notify you that we are working on functionality allowing to start and monitor background jobs for mentioned tasks. Looks like it is an only way in the nearest future to execute long running processes without failures. Will keep you informed about that. Sorry for inconvenience.

Thanks,  
Oleg

---

<div class="post-metadata">

**Author:** ![Oleg](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/oleg/32/1887_2.png) [@Oleg](https://community.fibery.io/u/Oleg)\
**Post date:** [October 12, 2023, 7:22am UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/6 "2023-10-12T07:22:17Z")

</div>

Hi, @Matt_Blais

We implemented the way to execute GraphQL mutations as background job to avoid timeouts.  
You can find documentation [here](https://api.fibery.io/graphql.html#use-background-jobs): [Fibery GraphQL API](https://api.fibery.io/graphql.html#use-background-jobs)  
Please let me know how it goes if you will have a chance to try.

I know, it doesn’t solve all of your issues, but we don’t give up and will proceed with automations performance tuning to improve things there.

Thanks,  
Oleg

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [October 12, 2023, 4:27pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/7 "2023-10-12T16:27:35Z")

</div>

Thanks @Oleg - that is a great step forward. 😀

This is what I need to do to clean up and delete older entities:

- I have ~586k “Call” records which are linked to ~35k “Call Stats” records.
- I will use a long-running graphQL call to **unlink** 450k Call records from Stats.
- Every unlink triggers a Stats Rule which adds the unlinked Call’s values to the Stats.
- When I do this manually, I can see the Stats values increasing as the Rule processes through all the unlinked Calls (about 10-20 per second).

I have no problem waiting a long time for everything to finish, but this will create a very deep queue of unlinked Calls for the Rule to process, and if the Rules do not _all eventually finish successfully_, that would be a problem (data inconsistency).

**Should I wait to start this process?** Or do you believe the system can currently handle this scenario correctly?

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [October 12, 2023, 5:32pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/8 "2023-10-12T17:32:13Z")

</div>

## NVM, I think I figured it out – see below.

@Oleg I’m having trouble understanding the syntax for the new graphQL capability - it’s not clear how to specify the filter/search criteria for selecting the records to be mutated.

Could you please translate this “normal” graphQL query to work in the extended mode?  
_Note that the filtered Calls are first updated, then unlinked:_

```auto
mutation {
  calls(
    callStartTime: { less:	"2023-10-11" }
    callStats: { isEmpty: false }
    status: { isNull: true }
    limit: 1 offset: 0
  )
 	{ update( status: "Freeze") { message entities{id} }
    unlinkCallStats { message }
  }
}

```

* * *

## Solution:

```auto
mutation {
  calls (
    callStartTime:	{ less:	"2023-10-11" }
    callStats: { isEmpty: false }
    status: { isNull: true }
    limit: 1 offset: 0
  )
  { executeAsBackgroundJob {
      jobId actions {
        update( status: "Freeze" ) {message entities{id}}
        unlinkCallStats {message}
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [October 12, 2023, 8:46pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/9 "2023-10-12T20:46:36Z")

</div>

**Comment on `executeAsBackgroundJob`** :

It appears that the results ultimately returned from a completed background job can only return the **id** of affected entities, but no other fields.

Because I cannot see the Name, Public Id, etc, of affected entities, it is difficult to _verify which entities_ were actually mutated. This makes testing/verifying a batch query difficult.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 12, 2023, 9:34pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/10 "2023-10-12T21:34:57Z")

</div>

Could you add a query to find the entities (based on the IDs returned)?

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [October 12, 2023, 9:36pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/11 "2023-10-12T21:36:45Z")

</div>

Can a mutation also query on the returned Ids?

Or do you mean just do a separate query after the mutation is done, to get related entity info for the Id’s returned by the mutation?

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 12, 2023, 9:50pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/12 "2023-10-12T21:50:10Z")

</div>

I meant the latter

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [October 12, 2023, 10:17pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/13 "2023-10-12T22:17:05Z")

</div>

If (1) the entities were only mutated and not deleted, and (2) I wasn’t so lazy,

then Yes, I could do that 😜

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 12, 2023, 10:40pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/14 "2023-10-12T22:40:05Z")

</div>

Well I suppose I wonder why you think you need to

[quote=“Matt\_Blais, post:9, topic:5237”]  
_verify which entities_ were actually mutated  
[/quote]?

Are you concerned that the API call is not doing what you ask it to do? Or are you concerned that you’ve written the filter criteria incorrectly?

And just for info, the full IDs of entities are available on the GUI if you did want to check a sample of them.

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [October 12, 2023, 10:42pm UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/15 "2023-10-12T22:42:04Z")

</div>

> [@Chr1sG](#):
>
> Are you concerned that the API call is not doing what you ask it to do? Or are you concerned that you’ve written the filter criteria incorrectly?

Yes, both.

---

<div class="post-metadata">

**Author:** ![Oleg](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/oleg/32/1887_2.png) [@Oleg](https://community.fibery.io/u/Oleg)\
**Post date:** [October 13, 2023, 6:40am UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/16 "2023-10-13T06:40:07Z")

</div>

Hello, @Matt_Blais

> It appears that the results ultimately returned from a completed background job can only return the **id** of affected entities, but no other fields.

It works in the same way for regular mutations. Unfortunately due to high memory consumption we can return only ids since hundreds of entities can have place.

Thanks,  
Oleg

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [October 14, 2023, 3:11am UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/17 "2023-10-14T03:11:30Z")

</div>

@Oleg, in reference to my post above:

> [@Matt\_Blais](#):
>
> I have no problem waiting a long time for everything to finish, but this will create a very deep queue of unlinked Calls for the Rule to process, and if the Rules do not _all eventually finish successfully_, that would be a problem (data inconsistency).
> 
> **Should I wait to start this process?** Or do you believe the system can currently handle this scenario correctly?

I am seeing that the Unlink Rule is having some trouble when 1000 entities are unlinked:

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/3/3a2a1172730d63c49178efa77a9a07b5b3cabf81.png)

How can I determine what is necessary for this Rule to work smoothly when many entities are unlinked at once (via graphQL or a different Rule)?

---

<div class="post-metadata">

**Author:** ![Oleg](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/oleg/32/1887_2.png) [@Oleg](https://community.fibery.io/u/Oleg)\
**Post date:** [October 16, 2023, 6:10am UTC](https://community.fibery.io/t/with-100k-records-things-are-breaking/5237/18 "2023-10-16T06:10:19Z")

</div>

Hi, @Matt_Blais

> I am seeing that the Unlink Rule is having some trouble when 1000 entities are unlinked

We are working on tuning automations rules for large amounts of data. Will publish fixes in nearest future and hope it will help to avoid having such issues.

Thanks,  
Oleg