ClickStack - Materialized Views

### Choose dimensions carefully[​](#choose-dimensions-carefully "Direct link to Choose dimensions carefully")

Include only dimensions that are commonly used for grouping or filtering:

- Every additional grouping column increases the size of the view.
- Balance query flexibility against storage and insert-time cost.
- Filters on columns not present in the view will cause ClickStack to fall back to the source table.

Tip

A common and almost always useful baseline is a materialized view grouped by **service name with a count metric**, which enables fast histograms and service-level overviews in search and dashboards.

### Naming conventions for aggregation columns[​](#naming-conventions-for-aggregation-columns "Direct link to Naming conventions for aggregation columns")

Materialized view aggregation columns must follow a strict naming convention to enable automatic inference:

- Pattern: `<aggFn>__<sourceColumn>`
- Examples:
  - `avg__Duration`
  - `max__Duration`
  - `count__` for row counts

ClickStack relies on this convention to correctly map queries to materialized view columns.

### Quantiles and sketch selection[​](#quantiles-and-sketch-selection "Direct link to Quantiles and sketch selection")

Different quantile functions have different performance and storage characteristics:

- `quantiles` produces larger sketches on disk but are cheaper to compute at insert time.
- `quantileTDigest` is more expensive to compute at insert time but produces smaller sketches, often resulting in faster view queries.

You can specify a sketch size (for example, `quantile(0.5)` at insert time for both functions. The resulting sketch can still be queried for other quantile values later e.g. `quantile(0.95)`. Experimenting is recommended to find the best balance for your workload.

### Validate effectiveness continuously[​](#validate-effectiveness-continously "Direct link to Validate effectiveness continuously")

Always verify that materialized views are delivering real benefits:

- Confirm usage via the UI acceleration indicators.
- Compare query performance before and after enabling the view.
- Monitor resource usage and merge behavior.

Materialized views should be treated as performance optimizations that require periodic review and adjustment as query patterns evolve.

### Advanced configurations[​](#advanced-configurations "Direct link to Advanced configurations")

For more complex workloads, multiple materialized views can be used to support different access patterns. Examples include:

- **High-resolution recent data with coarse historical views**
- **Service-level views for overviews and endpoint-level views for deep diagnostics**

These patterns can significantly improve performance when applied selectively, but should be introduced only after validating simpler configurations.

Following these recommendations will help ensure materialized views remain effective, maintainable, and aligned with ClickStack's execution model.

## Limitations[​](#limitations "Direct link to Limitations")

### Common incompatibility reasons[​](#common-incompatibility-reasons "Direct link to Common incompatibility reasons")

A materialized view **won't** be used if any of the following conditions apply:

- **Query time range**
  The start of the query's time range occurs before the materialized view's minimum timestamp. Because views aren't automatically backfilled, they can only satisfy queries for time ranges they fully cover.
- **Granularity mismatch**
  The visualization's effective granularity must be an exact multiple of the materialized view's granularity. Specifically:

  - For **time charts** (line or bar charts with time on the x-axis), the chart's selected granularity must be a multiple of the view's granularity. For example, a 10-minute chart can use 10, 5, 2, or 1-minute materialized views, but not 20-minute, or 3-minute views.
  - For **non-time charts** (number or table charts), the effective granularity is calculated as `(time range / 80)`, rounded up to the nearest HyperDX-supported granularity, and must also be a multiple of the view's granularity.
- **Unsupported aggregation functions**
  The query requests an aggregation that's not present in the materialized view. Only aggregations explicitly computed and stored in the view can be used.
- **Custom count expressions**
  Queries using expressions such as `count(if(...))` or other conditional counts can't be derived from standard aggregation states and therefore can't use materialized views.

### Design and operational constraints[​](#design-and-operational-constraints "Direct link to Design and operational constraints")

- **No automatic backfilling**
  Incremental materialized views only contain data inserted after creation. Historical acceleration requires explicit backfilling, which may be expensive or impractical for large datasets.
- **granularity trade-offs**
  Views with very fine granularity increase storage size and insert-time overhead, while coarse-grained views reduce flexibility. Granularity must be chosen carefully to match expected query patterns.
- **dimension explosion**
  Adding many grouping dimensions significantly increases view size and can reduce effectiveness. Views should include only commonly used grouping and filtering columns.
- **limited scalability of the view count**
  Each materialized view adds insert-time overhead and contributes to merge pressure. Creating too many views can negatively impact ingestion and background merges.

Being aware of these limitations helps ensure materialized views are applied where they provide real benefit and avoid configurations that silently fall back to slower source-table queries.

## Troubleshooting[​](#troubleshooting "Direct link to Troubleshooting")

### Materialized view not being used[​](#materialied-view-not-being-used "Direct link to Materialized view not being used")

**Check 1: date range**

- Open optimization modal to see if "Date range not supported."
- Ensure the query date range is after the materialized view's minimum date.
- Remove the minimum date if the materialized view contains all historical data.

**Check 2: granularity**

- Verify chart granularity is a multiple of MV granularity.
- Try setting chart to "Auto" or manually select compatible granularity.

**Check 3: aggregations**

- Check if the chart uses aggregations that are in the MV.
- Review "Available aggregated columns" in optimization modal.

**Check 4: dimensions**

- Ensure group by columns are in MV's dimension columns.
- Check "Available group/filter columns" in optimization modal.

### Slow materialized view queries[​](#slow-mv-queries "Direct link to Slow materialized view queries")

**Issue 1: materialized view granularity too fine**

- MV has too many rows due to low granularity (e.g., 1 second).
- Solution: Create a coarser MV (e.g., 1 minute or 1 hour).

**Issue 2: too many dimensions**

- MV has high cardinality due to many dimension columns.
- Solution: Reduce dimension columns to most commonly used ones.

**Issue 3: multiple MVs with high row counts**

- The System is running `EXPLAIN` on each MV.
- Solution: Remove MVs that are rarely used or always skipped.

### Configuration errors[​](#config-errors "Direct link to Configuration errors")

**Error: "At least one aggregated column is required"**

- Add at least one aggregated column to the MV configuration.

**Error: "Source column is required for non-count aggregations"**

- Specify which column to aggregate (only count can omit source column).

**Error: "Invalid granularity format"**

- Use one of the preset granularities from the dropdown.
- Format must be a valid SQL interval (e.g., `1 hour`, not `1 h`).