Skip to main content

Refreshing Continuous Aggregates

This guide explains how to refresh the continuous aggregates for location history after running migrations.

Overview​

The Tracker GraphQL system uses TimescaleDB continuous aggregates to efficiently query location history data. When the structure of these continuous aggregates is modified (e.g., adding a new field like horizontal_accuracy), you need to manually refresh them to apply the changes.

Using the Refresh Script​

The system includes a script to refresh all continuous aggregates:

python scripts/refresh_continuous_aggregates.py

This script:

  1. Refreshes the location_history_hourly continuous aggregate
  2. Refreshes the location_history_daily continuous aggregate
  3. Refreshes the location_history materialized view

When to Run the Script​

You should run this script after:

  1. Running migrations that modify the structure of continuous aggregates
  2. Adding new fields to the location_reports table
  3. Changing the filtering criteria for location reports

For example, after running the migration to add the horizontal_accuracy field, you should run this script to ensure the field is properly propagated to all materialized views.

Technical Details​

The script uses a direct psycopg connection with autocommit=True because TimescaleDB's refresh_continuous_aggregate function cannot be run inside a transaction block.

The script refreshes the aggregates in the following order:

  1. Hourly data (finest granularity)
  2. Daily data (which depends on hourly data)
  3. The final materialized view (which combines both)

Troubleshooting​

If you encounter errors when running the script, check:

  1. Database connectivity - ensure the database is running and accessible
  2. Permissions - ensure the database user has the necessary permissions to refresh materialized views
  3. Transaction blocks - if you're trying to run the refresh commands manually, ensure you're not in a transaction block