Create query performance tests

[MarceloRobert]

We have had some times where the query performance was heavily hit. We should add some form of test to check the database health and which queries are being slower than expected.

[MarceloRobert]

@tales-aparecida I got some considerations for this task:

First of all, how are we trying to test things? Just checking how slow the queries are? Adding an execution limit per query and then tagging query runs as "slower than usual"? Depending on what we want to do, this task might be simpler or more complex. I had some paths for it though:

Firstly, I was thinking about adding automated tests to Github's CI or Actions testing the production database, but that would add a lot of strain to the database when the tests are running, so I guess it's best to be a manual run or running on a fixed interval, instead of on every PR push (assuming the tests will run in production db in the first place).

Secondly - still on Github's CI/Actions - even when some endpoints were returning timeouts on the dashboard, our integration tests were running without any problems; I'm not sure if running the queries on Github's environment is representative of the dashboard environment, perhaps we should take the results more as a suggestion rather than an evidence of how slow things are.

We could have a timed approach as a cron job and we could even have an sqlite storing the results, allowing us to show those results on an endpoint, as well as a history of the results.

[tales-aparecida]

First of all, how are we trying to test things? ...
...
Firstly... a lot of strain to the database...
Secondly ...integration tests were running without any problems...

The motivation for this ticket was exactly the fact that we had pages failing to load due to DB queries timing out. Therefore, we need to figure out a way to catch identify slow queries early and alert the team.

We can enable DB-level alerts for slow queries, like https://www.postgresql.org/docs/current/auto-explain.html or any other tool listed in https://wiki.postgresql.org/wiki/Logging_Difficult_Queries. That could've tipped us out about the problem and we'd react to it faster. However, that doesn't prevent us from merging new slow queries, though.

We don't need to cover every single page with every single filter in our test suite every time, like you've said, we could have a manual approach. I believe we need to define our own "framework" to assert that the queries we are changing in an MR are running smoothly, smoke test a few "critical queries", and allows adding and updating new assertions easily.

To avoid straining the production database, it could be equivalent to an user opening a few tabs in their browser.
So could select randomly 10 pages and their filters to load and all of them should take less than N seconds.
We could even size it according to the moment. Test 5 pages on MR push and 50 pre-merge MR.

[tales-aparecida]

To make it a bit more concise and achievable, I think we can set the goals to

1. enable the native alerts from https://wiki.postgresql.org/wiki/Logging_Difficult_Queries
2. Update the integration tests to cover specifically the case it was missing.

then based on (2) we might have some insight.

I think this makes this ticket into an epic (or at least a parent)

[tales-aparecida]

We could have a timed approach as a cron job and we could even have an sqlite storing the results, allowing us to show those results on an endpoint, as well as a history of the results.

I gotta say that I like the idea of historical records. Like I've said, psql has some of this already, but if it's possible to determine how long each REST Promise is taking in the frontend, that could be fun, as well.