NOTE: Phoenix and TimescaleDB

TimescaleDB is a postgres compatible DB with time series extension

1. Container for development

To run up an instance:

docker run --rm --name dev-postgre -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d timescale/timescaledb-postgis:latest-pg12

2. Modification in Phoenix project

First, create a migration

mix ecto.gen.migration create_timescaledb_extension

Modify generated migration file:

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

Now, for any new table you're going to make it can be queried in time-series mode, add

execute("SELECT create_hypertable(<table name>, <time-series field>)")

For example:

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

  def change do
    create table(:hello, primary_key: false) do
      add :time, :naive_datetime, null: false
      add :avg, :float, default: 0.0
      add :max, :float, default: 0.0

      timestamps()
    end

    execute("SELECT create_hypertable('hello','time');")
  end
end