Forem: Jônatas Davi Paganini

Wow! This looks very useful for PostgreSQL users!

Jônatas Davi Paganini — Thu, 05 Mar 2026 18:48:30 +0000

Your Diesel Migrations Might Be Ticking Time Bombs

Alex Yarotsky ・ Dec 16 '25

How to Set Up a Dashboard for Global Energy Data Analytics (Real-World Use Case)

Jônatas Davi Paganini — Fri, 14 Feb 2025 17:00:24 +0000

TL;DR: 💡 A developer built a global energy dashboard tracking power data across 40+ countries to study wind patterns. After trying several databases, they landed on PostgreSQL with TimescaleDB for its ability to handle billions of data points and historical updates. The guide shows how to build a similar dashboard using Ruby and TimescaleDB. 👉 Jump to the full tutorial to learn more.

“PostgreSQL with TimescaleDB is great for my primary and only datastore. It supports updating historical data, as several data providers change their historical data,” said the developer and Rubyist, who has used SQL for decades.

Based on his use case, this guide will help you build a real-time analytics dashboard using publicly available energy data, Ruby, and TimescaleDB-enhanced PostgreSQL. The stack is suitable for energy traders, utility companies, and curious energy consumers looking to transform grid data into actionable insights.

The project’s source code is open source, with data hosted on Timescale Cloud, which proudly sponsors the intermittent energy server.

Building a Dashboard for Billions of Energy Data Points

With energy data from over 40 countries, including 48 U.S. states and five Australian regions, Christiansson acknowledges the scale and how overwhelming time-series data can be: “I'm a bit drowning in the data as there are billions of data points.”

TimescaleDB builds on PostgreSQL (a developer’s favorite) to deliver high-speed ingestion of new data while efficiently querying large datasets, enabling real-time analytics on time-series data. This capability allows organizations to forecast demand patterns and price trends, benchmark operations against market standards, and make data-driven decisions about energy procurement and usage.

This tutorial will use TimescaleDB—the core of our modern PostgreSQL data platform, Timescale Cloud—to build an efficient dashboard for analyzing global energy data in real time. We'll cover database architecture, schema design, and more.

Prerequisites

This is a hands-on tutorial, so you'll need to have some tools installed on your machine.

Clone the intermittent-importers repository.
Set up a local TimescaleDB database.
Clone the repository and check out the main branch.
Install the latest Ruby and the dependencies via bundler.

Database Architecture

Let's start with the system's core: the database schema that will power our real-time energy data analytics dashboard. This structure handles complex time-series relationships while maintaining query performance. “I opted for a narrow table, mainly because it works well with Grafana and is easy to query,” said Christiansson.

Schema Design

The database schema is designed to track and analyze global electricity markets through six core elements that work together to provide a complete picture of power systems worldwide:

The schema is designed around four core time-series tables (generation, load, transmission, prices) and several reference tables (areas, production_types, units). Each time-series table is implemented as a TimescaleDB hypertable with carefully tuned chunk intervals. By automatically partitioning your PostgreSQL tables into smaller, easier-to-scan tables, TimescaleDB’s hypertables help speed up queries.

Areas serve as the foundation, representing everything from entire countries to specific market zones or utility regions. Whether it's ENTSOE's European bidding zones or AEMO's Australian regions, areas provide the geographical context for all our data.

Production Types standardize how we classify power generation across different markets. This allows us to consistently track everything from traditional sources like coal and gas to renewables like wind and solar, even when markets use different terminology.

Generation tracks the actual power production over time, connecting areas with their production types to show how much electricity is being produced from each source at any given moment. This is crucial for understanding the energy mix in different regions.

Load represents electricity demand, helping us understand consumption patterns across different regions and timeframes. This data is essential for grid operators from Taipower in Taiwan to IESO in Ontario to manage their networks effectively.

Transmission monitors power flows between connected areas, tracking how electricity moves across borders and between regions. This is particularly important in interconnected markets like Europe's ENTSOE or Australia's National Electricity Market.

Prices captures the economic dimension of electricity markets, storing pricing data that varies by region and time. This helps us understand market dynamics from Europe’s ENTSOE and Australia’s AEMO spot prices to CAISO's locational marginal pricing.

Database stats: Reaching 5 billion rows

Before we dive into any details of how hypertables work, let’s share the current open stats of the project’s production database. Note how compression really shines for energy data. As you can see, the production database is almost five billion rows at this point.

Hypertable Configuration

Part of the mission to create a real-time dashboard is to have a fast database. Here's how the main time-series tables are configured:

-- Create the generation hypertable
SELECT create_hypertable('generation', 'time',
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);
-- Set up compression with specific ordering
ALTER TABLE generation SET (
    timescaledb.compress,
    timescaledb.compress_orderby = 'time DESC',
    timescaledb.compress_segmentby = 'area_id,production_type_id'
);
-- Create compression policy
SELECT add_compression_policy('generation', 
    compress_after => INTERVAL '7 days');

The code above creates a new chunk every day and compresses the data after seven days. This configuration balances compression efficiency with query performance. The compression parameters using time, area_id, and production_type_id columns provide optimal data organization for both historical analysis and backfilling operations.

Indexing Strategy

The indexing strategy is optimized for common query patterns:

-- Time-based lookups with area filtering
CREATE INDEX idx_generation_time_area 
ON generation(time DESC, area_id)
INCLUDE (value);
-- Production type aggregations
CREATE INDEX idx_generation_type 
ON generation(production_type_id, time DESC)
INCLUDE (value);

Getting Started: Building the Dashboard

Docker is a great way to get started with TimescaleDB. Here's how to run it on your local machine:

Install TimescaleDB.

docker run -d --rm -it \
    -e POSTGRES_HOST_AUTH_METHOD=trust \
    -e POSTGRES_USER=$USER \
    -e POSTGRES_DATABASE=$USER \
    -p 5432:5432 \
    timescale/timescaledb-ha:pg17

Note that timescale/timescaledb-ha:pg17 is the latest version of TimescaleDB for PostgreSQL. Check our docs for more information on installing TimescaleDB from a Docker container.

Set up the intermittent-importers database.

To import the data, you can follow the intermittent-importers repository depending on the data source you want to use.

Set up a dashboard

Let's create a real-time dashboard using Sinatra, the TimescaleDB gem, and Vega-Lite for visualization. We'll build a simple web application that displays energy generation data by source.

First, set up your project dependencies in a Gemfile:

source 'https://rubygems.org'
gem 'sinatra'
gem 'sinatra-contrib'  # For development reloading
gem 'timescaledb'
gem 'activerecord'
gem 'vega'
gem 'semantic_logger'

Create a basic Sinatra application (app.rb):

require 'bundler/setup'
require 'sinatra'
require 'sinatra/reloader' if development?
require 'json'
require 'vega'
require 'timescaledb'
require 'active_record'
require 'semantic_logger'

# Enable TimescaleDB extensions
ActiveSupport.on_load(:active_record) { extend Timescaledb::ActsAsHypertable }

# Load intermittent-importers models
Dir["../intermittent-importers/app/models/*.rb"].each { |file| require file }

Note that the intermittent-importers models are not exposed as a library, but we're loading them with the proper dependencies and reusing the models. Now, we'll just override the Generation model to fetch the data for the dashboard.

class Report < Generation
  belongs_to :area
  belongs_to :production_type
  self.table_name = "generation"
  scope :production_by_type, -> do
    joins(:production_type)
      .select("time_bucket('1 hour', time) as time, name, sum(value) as value")
      .group(1, 2)
  end
end

To fetch the data for the dashboard, we'll use the generation_by_source scope.

def get_energy_data
  data = Report.production_by_type

  {
    values: data.map do |record|
      {
        date: record.time,
        value: record.value,
        source: record.name
      }
    end
  }
end

Now we'll create the Vega-Lite chart configuration.

def generation_mix_chart
  Vega.lite
    .data(get_energy_data)
    .mark(type: "area", tooltip: true)
    .encoding(
      x: {field: "date", type: "temporal", title: "Date"},
      y: {field: "value", type: "quantitative", title: "Generation (MW)", stack: "zero"},
      color: {field: "source", type: "nominal", title: "Energy Source"}
    )
end

And then we'll create the routes for the dashboard and data fetching.

get '/' do
  erb :dashboard
end
get '/api/energy_data' do
  content_type :json
  get_energy_data.to_json
end

Create a dashboard view (views/dashboard.erb), including the Vega-Lite chart.

<!DOCTYPE html>
<html>
<head>
  <title>Energy Dashboard</title>
  <script src="https://cdn.jsdelivr.net/npm/vega@5"></script>
  <script src="https://cdn.jsdelivr.net/npm/vega-lite@5"></script>
  <script src="https://cdn.jsdelivr.net/npm/vega-embed@6"></script>
  <style>
    body { 
      margin: 20px;
      background: #f5f5f5;
    }
    .dashboard {
      max-width: 1200px;      padding: 20px;
    }
  </style>
</head>
<body>
  <div class="dashboard">
    <h1>Energy Generation Dashboard</h1>
    <%= generation_mix_chart %> 
  </div>
</body>
</html>

This setup creates a stacked area chart showing energy generation by source over time. The chart is interactive, with tooltips showing exact values when hovering over data points.

To run the dashboard:

bundle install
ruby app.rb

Visit http://localhost:4567 to see your dashboard in action.

The Result: A Real-Time Energy Data Analytics Dashboard

The chart will display real-time data from your TimescaleDB database, with automatic updates as new data arrives.

This is what the result looks like when you have data from a few countries.

More data visualization ideas for your energy analysis

For more inspiration to build your energy application, here are a few other dashboards and energy data visualizations on the intermittent energy project.

Dashboard showing an energy generation mix from all energy sources (Click here to see dashboard)

Maps are pretty helpful for understanding where the wind blows. Another great example from research in the dashboards is "the generation % of peak" filtering by wind. Click on the images to navigate to the official dashboards.

Map with the energy generation % of peak (Click here to see dashboard)

Prices are also very insightful on maps, especially over time. Check this pricing exposure to understand which regions are more expensive—it's incredible to see this key information displayed in clever data visualization.

(Click here to see dashboard)

Intermittent energy displays prices from Europe/ENTSOE and Australia/AEMO only. The U.S. uses nodal instead of zonal pricing, which is incompatible with the current data model. Here is the latest cool visualization of this blog to inspire you to animate prices correlating with energy demands.

Conclusion

TimescaleDB turns energy grid analytics into something any team can build, not just big utilities. Its speed with massive time-series datasets and features like hypertables, continuous aggregates, and a hybrid row-columnar storage engine provides the performance and flexibility needed for real-time energy market analysis.

More importantly, this open-source stack democratizes access to energy market intelligence. Companies of all sizes can now build sophisticated analytics capabilities that were once the exclusive domain of large utilities and trading houses. Installation instructions are on TimescaleDB’s GitHub repo.

If you need added scale, performance, and enterprise-ready features, Timescale Cloud is the obvious choice (there’s a 30-day free trial available).

⚙️ Building a better Ruby ORM for time series and analytics

Jônatas Davi Paganini — Thu, 06 Feb 2025 14:07:57 +0000

Better Time-Series Data Aggregations Using Ruby: The Inspiration
Hyperfunctions Integration for Faster Time-Series Analysis
Next steps

TL;DR: Turn 100+ lines of SQL into a single line of Ruby. Very useful TimescaleDB's continuous aggregates macro for real-time insights, right in the database—no background jobs needed. Enjoy Rails conventions with significantly faster data processing.

Rails developers know the joy of working with ActiveRecord. DHH didn’t just give us a framework; he gave us a philosophy, an intuitive way to manage data that feels delightful. But when it comes to time-series data, think metrics, logs, or events, ActiveRecord can start to feel a little stretched.

Handling huge volumes of time-stamped data efficiently for analytics?

That’s a challenge it wasn’t designed to solve (and neither was PostgreSQL).

This is where TimescaleDB comes in. Built on PostgreSQL (it’s an extension), TimescaleDB is purpose-built for time series and other demanding workloads, and thanks to the timescaledb gem, it integrates seamlessly into Rails. You don’t have to leave behind the conventions or patterns you love, it just works alongside them.

One of TimescaleDB’s standout features is continuous aggregates. Think of them as an upgrade to materialized views, automatically refreshing in the background so your data is always up-to-date and fast to query. With the new timescaledb gem continuous aggregates macro, you can define hierarchical time-based summaries in a single line of Ruby. It even reuses your existing ActiveRecord scopes, so you’re not duplicating logic you’ve already written.

Now, your Rails app can effortlessly handle real-time analytics dashboards or historical reports, scaling your time-series workloads while staying true to the Rails philosophy.

Better Time-Series Data Aggregations Using Ruby

The Inspiration

The following code snippet highlights the real-life use case that inspired me to build a continuous aggregates macro for better time-series data aggregations. It’s part of a RubyGems contribution I made, and it’s still a work in progress. However, it’s worth validating how this idea can reduce the Ruby code you’ll have to maintain.

Example model

class Download < ActiveRecord::Base
  extend Timescaledb::ActsAsHypertable
  include Timescaledb::ContinuousAggregatesHelper

  acts_as_hypertable time_column: 'ts'

  scope :total_downloads, -> { select("count(*) as total") }
  scope :downloads_by_gem, -> { select("gem_name, count(*) as total").group(:gem_name) }
  scope :downloads_by_version, -> { select("gem_name, gem_version, count(*) as total").group(:gem_name, :gem_version) }

  continuous_aggregates(
    timeframes: [:minute, :hour, :day, :month],
    scopes: [:total_downloads, :downloads_by_gem, :downloads_by_version],
    refresh_policy: {
      minute: { start_offset: "10 minutes", end_offset: "1 minute", schedule_interval: "1 minute" },
      hour:   { start_offset: "4 hour",     end_offset: "1 hour",   schedule_interval: "1 hour" },
      day:    { start_offset: "3 day",      end_offset: "1 day",    schedule_interval: "1 day" },
      month:  { start_offset: "3 month",    end_offset: "1 day",  schedule_interval: "1 day" }
  })
end

The refresh_policy work for all basic frames, but it is not mandatory and can be skipped. Now, remember that declaring the macro in the model has almost no effect until you run a migration that uses such metadata. The creation of the continuous aggregates needs to happen on a database migration through the call of migration helpers that can use the information. Let’s take a look at the helpers we have.

The migration helpers

The macro will create a continuous aggregate in the model, but for migration, it can generate the SQL code for all the views iterating on each timeframe and scope you specify.

The create_continuous_aggregates and drop_continuous_aggregates methods are designed to be invoked during the database migration step.

So, after saving your model with the new continuous_aggregate definition, you can use the create_continuous_aggregate method to invoke the creation of all materialized views in the database. If you use refresh_policy, it will also add all the policies along with the aggregation. Here’s what a migration file would look like:

class SetupMyAmazingCaggsMigration < ActiveRecord::Migration[7.0]
  def up
    Download.create_continuous_aggregates
  end

  def down
    Download.drop_continuous_aggregates
  end
end

It will automatically create all the continuous aggregates for all timeframes and scopes in the right dependency order. When the create_continuous_aggregates is called, 12 continuous aggregates will be created, starting from minute to month.

The migration output

Let’s take a deep look at what the SQL behind the scenes looks like when the method create_continuous_aggregates is called. From the first scope, it builds the continuous aggregates, fetching the data from the raw data.

CREATE MATERIALIZED VIEW IF NOT EXISTS total_downloads_per_minute
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) as ts, count(*) as total
FROM "downloads"
GROUP BY 1
WITH NO DATA;

Every materialization occurs independently, and to happen automatically, a refresh policy needs to be added. As it was specified generically by timeframe, it now incorporates the minute refresh for the policy.

SELECT add_continuous_aggregate_policy('total_downloads_per_minute',
  start_offset => INTERVAL '10 minutes',
  end_offset =>  INTERVAL '1 minute',
  schedule_interval => INTERVAL '1 minute');

Now, continuing the creation, it goes for the hourly level, already reusing the data from the previous materialized view.

CREATE MATERIALIZED VIEW IF NOT EXISTS total_downloads_per_hour
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) as ts, sum(total) as total FROM "total_downloads_per_minute" 
GROUP BY 1
WITH NO DATA;

An hourly policy is also established to guarantee that it will refresh automatically. The same iteration is repeated for daily and monthly timeframes. Later, the same process will repeat for the other timeframes.

SELECT add_continuous_aggregate_policy('total_downloads_per_hour',
  start_offset => INTERVAL '4 hour',
  end_offset =>  INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');


CREATE MATERIALIZED VIEW IF NOT EXISTS total_downloads_per_day
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', ts) as ts, sum(total) as total FROM "total_downloads_per_hour" GROUP BY 1
WITH NO DATA;

SELECT add_continuous_aggregate_policy('total_downloads_per_day',
  start_offset => INTERVAL '3 day',
  end_offset =>  INTERVAL '1 day',
  schedule_interval => INTERVAL '1 day');

CREATE MATERIALIZED VIEW IF NOT EXISTS total_downloads_per_month
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 month', ts) as ts, sum(total) as total FROM "total_downloads_per_day" GROUP BY 1
WITH NO DATA;

SELECT add_continuous_aggregate_policy('total_downloads_per_month',
  start_offset => INTERVAL '3 month',
  end_offset =>  INTERVAL '1 day',
  schedule_interval => INTERVAL '1 day');

CREATE MATERIALIZED VIEW IF NOT EXISTS downloads_by_gem_per_minute
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) as ts, gem_name, count(*) as total FROM "downloads" GROUP BY 1, gem_name
WITH NO DATA;

SELECT add_continuous_aggregate_policy('downloads_by_gem_per_minute',
  start_offset => INTERVAL '10 minutes',
  end_offset =>  INTERVAL '1 minute',
  schedule_interval => INTERVAL '1 minute');

CREATE MATERIALIZED VIEW IF NOT EXISTS downloads_by_gem_per_hour
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) as ts, gem_name, sum(total) as total FROM "downloads_by_gem_per_minute" GROUP BY 1, gem_name
WITH NO DATA;

SELECT add_continuous_aggregate_policy('downloads_by_gem_per_hour',
  start_offset => INTERVAL '4 hour',
  end_offset =>  INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

CREATE MATERIALIZED VIEW IF NOT EXISTS downloads_by_gem_per_day
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', ts) as ts, gem_name, sum(total) as total FROM "downloads_by_gem_per_hour" GROUP BY 1, gem_name
WITH NO DATA;

SELECT add_continuous_aggregate_policy('downloads_by_gem_per_day',
  start_offset => INTERVAL '3 day',
  end_offset =>  INTERVAL '1 day',
  schedule_interval => INTERVAL '1 day');

CREATE MATERIALIZED VIEW IF NOT EXISTS downloads_by_gem_per_month
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 month', ts) as ts, gem_name, sum(total) as total FROM "downloads_by_gem_per_day" GROUP BY 1, gem_name
WITH NO DATA;

SELECT add_continuous_aggregate_policy('downloads_by_gem_per_month',
  start_offset => INTERVAL '3 month',
  end_offset =>  INTERVAL '1 day',
  schedule_interval => INTERVAL '1 day');

CREATE MATERIALIZED VIEW IF NOT EXISTS downloads_by_version_per_minute
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) as ts, gem_name, gem_version, count(*) as total FROM "downloads" GROUP BY 1, gem_name, gem_version
WITH NO DATA;

SELECT add_continuous_aggregate_policy('downloads_by_version_per_minute',
  start_offset => INTERVAL '10 minutes',
  end_offset =>  INTERVAL '1 minute',
  schedule_interval => INTERVAL '1 minute');

CREATE MATERIALIZED VIEW IF NOT EXISTS downloads_by_version_per_hour
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) as ts, gem_name, gem_version, sum(total) as total FROM "downloads_by_version_per_minute" GROUP BY 1, gem_name, gem_version
WITH NO DATA;

SELECT add_continuous_aggregate_policy('downloads_by_version_per_hour',
  start_offset => INTERVAL '4 hour',
  end_offset =>  INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

CREATE MATERIALIZED VIEW IF NOT EXISTS downloads_by_version_per_day
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', ts) as ts, gem_name, gem_version, sum(total) as total FROM "downloads_by_version_per_hour" GROUP BY 1, gem_name, gem_version
WITH NO DATA;

SELECT add_continuous_aggregate_policy('downloads_by_version_per_day',
  start_offset => INTERVAL '3 day',
  end_offset =>  INTERVAL '1 day',
  schedule_interval => INTERVAL '1 day');

CREATE MATERIALIZED VIEW IF NOT EXISTS downloads_by_version_per_month
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 month', ts) as ts, gem_name, gem_version, sum(total) as total FROM "downloads_by_version_per_day" GROUP BY 1, gem_name, gem_version
WITH NO DATA;

SELECT add_continuous_aggregate_policy('downloads_by_version_per_month',
  start_offset => INTERVAL '3 month',
  end_offset =>  INTERVAL '1 day',
  schedule_interval => INTERVAL '1 day');

That’s massive, right?!

It’s probably too boring to read it all because it’s almost a repetitive structure, iterating over all the scopes. The continuous_aggregates leverages all logic by iterating over all the timeframes with all scopes. It reuses minute data in the hourly view and uses the same technique from hour to day, day to month, and so on.

In contrast, reusing the aggregations, if written all by hand, makes the process really error-prone. The Model.drop_continuous_aggregates method uses the reverse dependency path to call the drop materialized view from month to minute.

Continuously aggregating statistics can replace dozens of background jobs hosted by your application, avoiding serialization and deserialization efforts apart from bandwidth, I/O (input/output), and overuse of resources in general.

Reusing the previous timeframes makes it very fast and lightweight for the database to process. Adopting hierarchical processing also allows all processing to be done at a predictable speed because the number of rows will be static and only dependent on the cardinality of the data.

Processing aggregations in the database means there will only be calls between the database and the disk, releasing interactions between the application and the database and forcing network data trips to process it on application background jobs.

Now, let’s take a look at how the rollup works.

Hyperfunctions Integration for Faster Time-Series Analysis

Timescale also built a specialized extension for time-series data processing, the timescaledb-toolkit. It helps improve the developer experience and query performance, and most of its functions are called hyperfunctions.

Hyperfunctions are designed to reuse and make statistics fast for hypertables, allowing you to roll up granular aggregations into bigger timeframes. In the case of the Ruby library, it should work well with both regular statistics functions and also roll up the hyperfunctions already available.

The most important part of using multiple timeframes and scopes is to understand how the rollup scope works.

For example, if you have a scope called total_downloads and a timeframe of day, the rollup will rewrite the query to group by the day.

# Original query
SELECT count(*) FROM downloads;

# Rolled up query
SELECT time_bucket('1 day', created_at) AS day, count(*) FROM downloads GROUP BY day;

In Ruby, the rollup method will help to roll up such queries in a more efficient way. Let’s consider the total_downloads scope as an example:

Download.total_downloads.map(&:attributes) #  => [{"total"=>6175}
# SELECT count(*) as total FROM "downloads"

The rollup scope will help to group data by a specific timeframe. Let’s start with one minute:

Download.total_downloads.rollup("'1 min'").map(&:attributes)
# SELECT time_bucket('1 min', ts) as ts, count(*) as total FROM "downloads" GROUP BY 1
=> [{"ts"=>2024-04-26 00:10:00 UTC, "total"=>110},
 {"ts"=>2024-04-26 00:11:00 UTC, "total"=>1322},
 {"ts"=>2024-04-26 00:12:00 UTC, "total"=>1461},
 {"ts"=>2024-04-26 00:13:00 UTC, "total"=>1150},
 {"ts"=>2024-04-26 00:14:00 UTC, "total"=>1127},
 {"ts"=>2024-04-26 00:15:00 UTC, "total"=>1005}]

As you can see, the time_bucket function is introduced, and a group by clause is also added.

If the current query uses a component like candlestick_agg, it will be able to call the rollup SQL function, and that’s where the name of the function comes from.

What if I want to sum the counters from the materialized view behind the scenes and roll up to a bigger frame? That’s when the aggregated classes join the game.

Continuous aggregates are hypertables. They’re materialized views that are periodically being updated in the background according to the refresh policy. Every aggregation can be accessed and refreshed independently.

Aggregates classes

In the previous example, the rollup was done directly in the raw data. Now, let’s explore how the continuous_aggregates macro creates a class for each aggregated view that is in the database. The classes can be accessed as subclasses in the model and also inherit the model as they’re fully dependent on it.

So, to access the materialized data, instead of building the query from raw data, nested classes are created with the Model::ScopeNamePerTimeframe naming convention.

Download::TotalDownloadsPerMinute.all.map(&:attributes)
# SELECT "total_downloads_per_minute".* FROM "total_downloads_per_minute"
=> [{"ts"=>2024-04-26 00:10:00 UTC, "total"=>110},
 {"ts"=>2024-04-26 00:11:00 UTC, "total"=>1322},
 {"ts"=>2024-04-26 00:12:00 UTC, "total"=>1461},
 {"ts"=>2024-04-26 00:13:00 UTC, "total"=>1150},
 {"ts"=>2024-04-26 00:14:00 UTC, "total"=>1127},
 {"ts"=>2024-04-26 00:15:00 UTC, "total"=>1005}]

To roll up from the materialized data, we need to consider how the data was built. So, to have the counter, we need to count rows from the hypertable raw data, but for bigger timeframes, we can just sum the counters. Here’s what it looks like if you need to roll up any scope to other timeframes:

Download::TotalDownloadsPerMinute.select("sum(total) as total").rollup("'2 min'").map(&:attributes)
# SELECT time_bucket('2 min', ts) as ts, sum(total) as total FROM "total_downloads_per_minute" GROUP BY 1
=> [{"ts"=>2024-04-26 00:12:00 UTC, "total"=>2611}, {"ts"=>2024-04-26 00:14:00 UTC, "total"=>2132}, {"ts"=>2024-04-26 00:10:00 UTC, "total"=>1432}]

With the rollup scope, you can easily build custom scopes and regroup as you need. It supports a few statistic scenarios on rollup to automatically detect SQL statements that contain count(*) as total and transform them into sum(total) as total them. It can also get a min of min or max of max values when it’s rolling up into larger time frames.

Refresh aggregates

If you need to refresh all aggregates manually in the right order, you can also use the refresh_aggregates method:

Download.refresh_aggregates

Next steps

That’s all, folks! I posted a few more details in my blog during the development phase. If you have any questions or feedback, join the #ruby channel on the TimescaleDB Slack.

Also, GitHub ⭐s for our Ruby library are very much welcome!

To give it a try and use the continuous_aggregates macro on your project, install the timescaledb gem. Happy coding—but write fewer lines of code.