Optimizations 201: Fix common mistakes

Use the top 5 questions in this guide to identify and fix common pitfalls in your data project.

Prerequisites

First, read Optimizations 101: Detect inefficiencies. It identifies the worst-performing Pipe and the characteristics of that performance so you can investigate common causes.

It's also a really good idea to read Best practices for faster SQL and the Thinking in Tinybird blog post.

This guide covers 5 common questions (the "usual suspects"). If you find a poorly performing Pipe, ask yourself these 5 questions before exploring other, more nuanced problem areas.

1. Are you aggregating or transforming data at query time?

Calculating the count(), sum(), or avg(), or casting to another data type is common in a published API Endpoint. As your data scales, you may be processing more data than necessary, as you run the same query each time there is a request to that Endpoint. If this is the case, you should create a Materialized View.

In a traditional database, you have to schedule Materialized Views to run on a regular cadence. Although this helps pre-process large amounts of data, the batch nature renders your data stale.

In Tinybird, Materialized Views let you incrementally pre-aggregate, transform, and filter large Data Sources upon ingestion. By shifting the computational load from query time to ingestion time, you scan less data and keep your Endpoints fast.

Read the docs to create a Materialized View.

2. Are you filtering by the fields in the sorting key?

The sorting key is important. It determines the way data is indexed and stored in your Data Source, and is crucial for the performance of your Endpoints. Set the right sorting key to keep all the data for any given query as close as possible.

In all databases (including Tinybird), use indexes to avoid reading unnecessary data and significantly speed up operations such as filtering.

The goal of sorting keys (SKs) is to reduce scan size and discard as much data as possible when examining the WHERE clauses in the queries. In short, a good Sort Key helps you avoid expensive, time-consuming full scans.

Some good rules of thumb for setting Sorting Keys:

  • Order matters: Data is stored in the order of the Sorting Key.
  • Filter priority: Put equality or highly selective filters first, time/range filters next, and grouping/reporting dimensions last.
  • Keep it short: 3–5 columns is usually enough; more can hurt performance.
  • Avoid timestamps first: timestamp (or similar) is rarely a good first key.
  • Multi-tenant tip: Use customer_id (or similar) first to cluster tenant data.

One common mistake is to use the partition key for filtering. Use the sorting key, not the partition key.

3. Are you using the best data types?

If you do need to read data, you should try to use the smallest types that can get the job done. A common examples are timestamps. Do you really need millisecond precision?

Often when users start doing data analytics, they aren't sure what the data looks like or how to query it in their app. After creating your Endpoint or Pipe, review whether your schema best supports the resulting use case.

It's common to begin with simple types, such as String, Int, and DateTime, but review the selected data types as you continue implementing the app.

When reviewing your data types, focus on the following points:

  • Downsizing types, to select a different data type with a lower size. For instance, UUID fields can be typed as UUID fields instead of string types, you can use unsigned integers (UInt) instead of integers (Int) where there aren't negative values or you could use a Date instead of DateTime.
  • Examine string cardinality to perhaps use LowCardinality() if there are less than 100k uniques.
  • Nullable columns are bigger and slower and can't be sorting keys, so use coalesce().

Sorting key and data type changes are done by changing your schema, which means iterating the Data Source. See an example of these types of changes in the thinking-in-tinybird demo repo.

4. Are you doing complex operations early in the processing pipeline?

Operations such as joins or aggregations get increasingly expensive as your data grows.

Filter your data first to reduce the number of rows, then perform the more complex operations later in the pipeline.

Follow this example: Rule 5 for faster SQL.

5. Are you joining two or more data sources?

You might want to enrich your events with dimension tables by materializing a JOIN. This approach could process more data than necessary. Follow these tips to reduce the amount of processed data:

  • Try to switch out JOINs and replace them with a subquery: WHERE column IN (SELECT column FROM dimensions).
  • If the join is needed, try to filter the right table first (better if you can use a field in the sorting key).
  • Remember that the Materialization is only triggered when you ingest data in the left Data Source (the one you use to do a SELECT … FROM datasource). So, if you need to recalculate data from the past, creating a Materialized View isn't the right approach. Instead, check this guide about Copy Pipes.

Understanding the Materialized JOIN issue

The issue

There's a common pitfall when working with Materialized Views:

Materialized Views generated using JOIN clauses require care. The resulting Data Source is automatically updated only if and when a new operation is performed on the Data Source in the FROM clause.

Since Materialized Views work with the result of a SQL query, you can use JOINs and any other SQL feature. But JOINs should be used with caution.

SELECT a.id, a.value, b.value
FROM a 
LEFT JOIN b USING id

If you insert data in a (LEFT SIDE), the data is processed as expected. But what happens if you add data to b (RIGHT SIDE)?

It isn't processed. A Materialized View is triggered only when its source table receives inserts. The trigger on the source table knows nothing about the joined table. This behavior applies beyond JOIN queries whenever you introduce any table external to the Materialized View's SELECT statement, for example by using an IN SELECT.

It can become more complex if you need to deal with stream joins. However, this guide focuses on the basic setup as doing JOINs implies something most people don't realize.

These JOINs can be expensive because you read the small number of rows being ingested (LEFT SIDE) plus a full scan of the joined table (RIGHT SIDE), which has no associated indexing information.

The optimization

Sometimes, to easily detect these cases, it's useful to review the read_bytes/write_bytes ratio. If you're reading way more than writing, most likely you're doing some JOINs within the MV.

You can easily change this by adding a filter in the right side, rewriting the previous query as follows:

SELECT a.id, a.value, b.value
FROM a 
LEFT JOIN (
  SELECT id, value
  FROM b
  WHERE b.id IN (SELECT id FROM a)
) b USING id

This might sound counterintuitive when writing a query for the first time because you read a twice. However, a is usually smaller than b because you read only the block of rows you're ingesting. For a meaningful improvement, include the fields used to filter in the sorting key of b. Most of the time, use the "joining key", but you can use any other field that hits the index in b and filters the right side of the JOIN.

Next steps

Updated