Setup TimescaleDB on Elixir and Ecto

Timescale seems pretty popular, but it's not exactly clear how to configure PostgreSQL extensions in Elixir. So here's my take.

Prerequisites

• PostgreSQL 11
• TimescaleDB

I've already set up my postgre tables so I'll be continuing from that. For the basics, check out the official Ecto docs. I've pasted my table creation migration at the bottom also.

Load the extension into PostgreSQL

For Postgre to use an extension, it must first be loaded.

TimescaleDB has made a comfy automatic configurator timescaledb-tune. It does a bunch of performance enhancing things, but since I want to know exactly what's going on, I'm adding the extension manually.

Basically just add a line to the postgresql.conf with this command.

echo "shared_preload_libraries = 'timescaledb'" | sudo tee -a /var/lib/postgres/data/postgresql.conf

Now restart postgresql with sudo systemctl restart postgresql.

Create migrations

First create the migrations file in your app.

mix ecto.gen.migration create_extension_timescaledb

And in the new file we attach the TimescaleDB to Postgre:

# priv/repo/migrations/20200327131930_create_extension_timescale.exs

defmodule Bashboard.Repo.Migrations.CreateExtensionTimescale do
  use Ecto.Migration

  def up do
    execute("CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE")
    execute("SELECT create_hypertable('messages', 'inserted_at')")
  end

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

Thanks firehose@github for giving a clear example.

So first it attaches the extension to PostgreSQL, then it creates the tables it needs.

Run migrations

Just run the command

mix ecto.migrate

Now everything should be set up to use Timescale in your Elixir app.

Troubleshooting

cannot create a unique index

My migrations worked, but the SELECT command didn't, got an error:

16:23:49.343 [info]  execute "SELECT create_hypertable('messages', 'inserted_at')"
** (Postgrex.Error) ERROR TS103 (nil) cannot create a unique index without the column "inserted_at" (used in partitioning)
    (ecto_sql) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql) lib/ecto/adapters/sql.ex:699: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:166: :timer.tc/1

The solution to this was to make the timestamp() column primary key. Since I'm resetting my db anyway, here's my new table creation migration:

defmodule Bashboard.Repo.Migrations.CreateMessages do
  use Ecto.Migration

  def change do
    create table(:messages) do
      add(:user, :string, null: false)
      add(:dash, :string, null: false)
      add(:widget, :string, null: false)
      add(:body, :map)

      timestamps(
        type: :utc_datetime,
        updated_at: false,
        primary_key: true   # this was missing
      )
    end

    create(index(:messages, [:user, :dash, :widget]))
  end
end

People on the Timescale Slack have also explained further:

Flushing after table creation

I didn't stumble on this issue, but @camoz did, so if you're defining a table and making a hypertable in a single migration, then flush it in between:

def up do
  create table("foos", primary_key: false) do
    add :start_minute, :utc_datetime, primary_key: true
    # some more columns
    timestamps(type: :utc_datetime)
  end
  flush()
  execute("SELECT create_hypertable('foos', 'start_minute')")
end

Attribution

Thanks camonz@elixir-slack for a lot of help!