TimescaleDB support in Elixir using Ecto

Most of the projects we have the pleasure of creating collect a lot of data. It usually means a heavy load on the database, which we use for data persistence. Often our data is time-series data. But what can we do to provide better request handling and lower access times?

I will present how you can integrate Elixir and Ecto with one, very popular extension for PostgreSQL database which is TimescaleDB.

TimescaleDB and time-series data

TimescaleDB is a very popular extension for PostgreSQL. It allows better handling of time-series data.

But what does time-series data itself mean concerning other data?

Time-series data collectively represents changes in a process, system, or behavior over time. Data should always has a time aspect by including timestamp. The vast majority of data is append-only. We are also most often interested in the latest data in the processing process. The frequency aspect of adding new data is not significant.

Time-series data can be found everywhere. Monitoring computer systems, container metrics (CPU, free memory, net/disk IOPs), and application metrics (requests) are time-series data. Data from Internet of Things sensors, data from the application (user login, clicks), or even financial trading systems generate data from this category.

Business use case

Technology and proposed solutions should match the business expectations and challenges of real projects. In one of the projects that I was developing, data were collected regarding, among others, the availability of games (yep, also Cyberpunk 2077 sales summary).

The original use of PostgreSQL alone has proved insufficient. Saving data typically time-series and operations on them away from the database (we need to obtain a large volume of data and processing in Elixir) did not work well.

TimescaleDB came to the rescue. It was possible to process the data inside the database better. The use of time-divided tables allowed for better access to the latest data. Let’s see below with code examples of how you can easily manage it from within Ecto.

Ecto and TimescaleDB

The considerable advantage of TimescaleDB is the full SQL interface for all SQL natively supported by PostgreSQL. So we can use Postgrex and Ecto to communicate with the database.

Let’s begin by ensuring the extension is available in the database.

1
  mix ecto.gen.migration create_timescaledb_extension
1
2
3
4
5
6
7
8
9
10
11
12
13
  # priv/repo/migrations/20210131105904_create_timescaledb_extension.exs

  defmodule Project.Repo.Migrations.CreateTimescaledbExtension do
    use Ecto.Migration

    def up do
      execute("CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE")
    end

    def down do
      execute("DROP EXTENSION IF EXISTS timescaledb CASCADE")
    end
  end

When you want to create a new table using the options introduced in the extension, use the code below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
  # priv/repo/migrations/20210131110504_create_conditions_table.exs

  defmodule Project.Repo.Migrations.CreateConditionsTable do
    use Ecto.Migration

    def up do
      create table(:conditions, primary_key: false) do
        add(:time, :naive_datetime, null: false)
        add(:location, :string, null: false)
        add(:temperature, :decimal, null: false)
        add(:humidity, :decimal, null: false)

        timestamps()
      end

      execute("SELECT create_hypertable('conditions', 'time')")
    end

    def down do
      drop(table(:conditions))
    end
  end

The table conditions will store the conditions (temperature and humidity) at a given location. Instead of the default SERIAL primary key, we will use the naive_datetime to split records based on that key. Executing the create_hypertable command requires execute, hence the separation into up and down to undo the migration.

Summary

As we can see, using TimescaleDB is not complicated at all. Depending on the project, we can gain a lot from its introduction to our system. I recommend you check out the TimescaleDB vs. Relational Databases comparison.

I have presented my adventure with this extension. What do you think about it? Maybe you have your insights that you want to share? The comments section below is for you!