PartitionedSchema

Adds partition maintenance helpers to an Ecto schema module.

Usage

Add the PartitionedSchema definition to an Ecto.Schema you want to partition. The table name (including Postgres schema) is inferred from your existing module.

    defmodule YourApp.Voltage do
      use Ecto.Schema

      # Add this block to the schema you want to partition
      use PartitionedSchema,
        repo: MyApp.Repo,
        partition_column: :ts,
        partition_column_type: :timestamptz, # :date | :timestamptz | :naive_datetime
        partition_type: :weekly,             # :daily | :weekly | :monthly
        retention: 30,                       # days, or {:days, n}, {:weeks, n}, {:months, n}, or nil
        timezone: "Etc/UTC"                  # used for DateTime boundaries

      schema "voltages" do
        field :device_id, :id
        field :ts, :utc_datetime_usec
        field :voltage, :float
      end
    end

Note that if you set the :retention to nil no automatic cleanup will occur. New partitions will be added and old partitions remain untouched.

Then add the PartitionMaintainer to your applications supervisor as in the following, abbreviated example. The :interval option specifies how often the maintainer process should check for whether partitions need to be rotated.

The partitions option is a list of modules that use PartitionedSchema.

If you run in a clustered environment (ie Erlang distribution), the maintainer process will run on each node but for rotating the partitions a Postgres pg_try_advisory_xact_lockPostgres Docs is aqcuired so it can only happen once at a time evenif multiple PartitionMaintainer processes attempt it at the same exact time.

defmodule YourApp.Application do
  @moduledoc false

  use Application

  @impl true
  def start(_type, _args) do
    children = [
      …
      {PartitionedSchema.PartitionMaintainer, repo: YourApp.Repo, partitions: [YourApp.Voltage], interval: :timer.minutes(1)},
      …
    ] ++ maybe_include_push_processes()

    opts = [strategy: :one_for_one, name: YourApp.Supervisor]
    Supervisor.start_link(children, opts)
  end

NOTE: You might not want to start the PartitionMaintainer in all envs, especially :test as that can lead to Postgres errors when running your tests async.

The simplest approach is to only add the PartitionMaintainer when the not in the :test environment and then to run

setup do
  ensure_partitioned_schema_partitions([YourApp.YourPartitionedSchema])
  :ok
end

in the tests for your partitioned schemas to make sure the partitions are created.

Next you must make sure that your parent table already exists. Here is an example migration:

defmodule YourApp.Repo.Migrations.AddPartitionedTable do
  use Ecto.Migration

  def up do
    execute """
    CREATE TABLE voltages (
      device_id bigint NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
      ts timestamptz NOT NULL,
      voltage int NOT NULL,
      PRIMARY KEY (device_id, ts)
    ) PARTITION BY RANGE (ts);
    """
  end

  def down do
    execute "DROP TABLE voltages;"
  end
end

This module creates partitions with:

CREATE TABLE IF NOT EXISTS <child> PARTITION OF <parent>
FOR VALUES FROM ('...') TO ('...');

Notes:

• Partition ranges are inclusive on start and exclusive on end (Postgres semantics).
• By default, weekly boundaries are ISO weeks (week starts Monday) when using dates.
• For timestamptz partitions, boundaries are computed in timezone and then stored as UTC instants.

Installation

If available in Hex, the package can be installed by adding partitioned_schema to your list of dependencies in mix.exs:

def deps do
  [
    {:partitioned_schema, "~> 0.9.1"}
  ]
end

Tests

To run the tests, make sure to create the database first via MIX_ENV=test mix ecto.create

Documentation can be generated with ExDoc and published on HexDocs. Once published, the docs can be found at https://hexdocs.pm/partitioned_schema.