Choosing the ENGINE_PARTITION_KEY

Partitioning is not intended to speed up SELECT queries (the ENGINE_SORTING_KEY key is sufficient to make range queries fast), partitions are intended for data manipulation. If the number of partitions is huge, the SELECT query will run slower if it has to look in many partitions.

We recommend:

  • Leaving empty the ENGINE_PARTITION_KEY. If you don’t have it clear beforehand or the table is relatively small (a few gigabytes), leave it empty. The data will be placed in a single partition.

  • Using a date column. Depending on the filter, you can choose more or less granularity based on your needs. toYYYYMM(date_column) or toYear(date_column) should work fine most of the times

ENGINE_PARTITION_KEY using a date column
ENGINE_PARTITION_KEY "toYYYYMM(date_column)"
  • Using a column to handle a controlled amount of different values to partition depending on how you consume and insert data.

ENGINE_PARTITION_KEY for a dimensions table of events
ENGINE_PARTITION_KEY "event_type % 8"

If you have doubts about choosing a partition key for your use case, contact us at support@tinybird.co.

Using Materialized Views

When aggregating data, it’s useful to save the results in a Materialized View, and then use the Materialized View in your queries.