Raising query limits applied by the product analytics schemas

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Problem to solve

Once we integrate the WITH FILL modifier with our Cube implementation we will increase the number of rows returned whenever data is requested. This is because WITH FILL will fill the entirety of the date range provided with 0 values in the event that we don't have data for particular date.

Our current query limits are applied by the defined visualizations and are all currently set to a limit of 100. This limit is arbitrary and was the limit set up when developing the product analytics PoC rather than anything set by performance calculations.

The issue is that the limit is very low, especially if a user opts to receive data for an entire year (returning 365 rows). This could increase as we integrate smaller granularities like seconds or hours.

Proposal

I propose that we remove the limit from our built-in visualization schema definitions entirely. Cube.js has a default limit of 10,000 rows; which is documented for the export process. Cube.js has a maximum limit of 50,000 rows and so does Clickhouse by default.

In its place, I suggest the following process:

  1. If a user sets a limit in their custom visualization, always respect it and us that for the query.
  2. If the query doesn't define a limit and the query defines a date range to search:
    1. Take the date range and calculate the number of days in the range.
    2. Apply this limit to the query before sending the request to Cube.
    3. This can be applied through the Cube proxy and will be applied to the export functionality since it uses the same endpoint.
  3. If the query doesn't define a limit and the query does not define a date range:
    1. Use the default 10,000 limit and return the full range.
    2. Export users can use the offset property to paginate through their data every 10,000 rows.

In addition, we should add the same limit/offset text from the exports documentation and add it the wider product analytics documentation.

As a follow-up, we could also add an alert to the dashboard letting users know that the visualizations they are seeing have hit the limit, and they should reduce the date range.

Open-questions/concerns

  1. What performance impact does this have on different visualizations?
    1. Query times
      1. 10,000 rows is very minor for Clickhouse, might need to test by setting a granularity to seconds over a long date range.
      2. Our least performant queries are already “Sessions over time” and “Sessions per browser”. These will probably need rewriting/pre-aggregation anyway.
    2. Rendering times
      1. 10,000 rows needs to be looked at for rendering. E-charts will reduce the number of points on a chart to make sure the points fit and group points to help visually.
Edited by 🤖 GitLab Bot 🤖