Partitioned Tables

PostgreSQL supports table partitioning, which allows you to split a large table into smaller, more manageable pieces. Partitioning can improve query performance, simplify maintenance, and enable better data management strategies.

For more information on PostgreSQL partitioning, see the PostgreSQL partitioning documentation.

Setting Up a Partitioned Table

To create a partitioned table in AshPostgres, you'll use the create_table_options DSL option to specify the partitioning strategy. This option passes configuration directly to Ecto's create table/2 function.

Range Partitioning Example

Here's an example of setting up a range-partitioned table by date:

defmodule MyApp.SensorReading do
  use Ash.Resource,
    domain: MyApp.Domain,
    data_layer: AshPostgres.DataLayer

  attributes do
    uuid_primary_key :id
    attribute :sensor_id, :integer
    attribute :reading_value, :float
    create_timestamp :inserted_at
  end

  postgres do
    table "sensor_readings"
    repo MyApp.Repo

    # Configure the table as a partitioned table
    create_table_options "PARTITION BY RANGE (inserted_at)"

    # Create a default partition to catch any data that doesn't fit into specific partitions
    custom_statements do
      statement :default_partition do
        up """
        CREATE TABLE IF NOT EXISTS sensor_readings_default
        PARTITION OF sensor_readings DEFAULT;
        """
        down """
        DROP TABLE IF EXISTS sensor_readings_default;
        """
      end
    end
  end
end

List Partitioning Example

Here's an example of list partitioning by region:

defmodule MyApp.Order do
  use Ash.Resource,
    domain: MyApp.Domain,
    data_layer: AshPostgres.DataLayer

  attributes do
    uuid_primary_key :id
    attribute :order_number, :string
    attribute :region, :string
    attribute :total, :decimal
    create_timestamp :inserted_at
  end

  postgres do
    table "orders"
    repo MyApp.Repo

    # Configure the table as a list-partitioned table
    create_table_options "PARTITION BY LIST (region)"

    # Create a default partition
    custom_statements do
      statement :default_partition do
        up """
        CREATE TABLE IF NOT EXISTS orders_default
        PARTITION OF orders DEFAULT;
        """
        down """
        DROP TABLE IF EXISTS orders_default;
        """
      end
    end
  end
end

Hash Partitioning Example

Here's an example of hash partitioning:

defmodule MyApp.LogEntry do
  use Ash.Resource,
    domain: MyApp.Domain,
    data_layer: AshPostgres.DataLayer

  attributes do
    uuid_primary_key :id
    attribute :user_id, :integer
    attribute :message, :string
    create_timestamp :inserted_at
  end

  postgres do
    table "log_entries"
    repo MyApp.Repo

    # Configure the table as a hash-partitioned table
    create_table_options "PARTITION BY HASH (user_id)"

    # Create a default partition
    custom_statements do
      statement :default_partition do
        up """
        CREATE TABLE IF NOT EXISTS log_entries_default
        PARTITION OF log_entries DEFAULT;
        """
        down """
        DROP TABLE IF EXISTS log_entries_default;
        """
      end
    end
  end
end

Creating Additional Partitions

After the initial migration, you can create additional partitions as needed using custom statements. For example, to create monthly partitions for a range-partitioned table:

postgres do
  table "sensor_readings"
  repo MyApp.Repo

  create_table_options "PARTITION BY RANGE (inserted_at)"

  custom_statements do
    statement :default_partition do
      up """
      CREATE TABLE IF NOT EXISTS sensor_readings_default
      PARTITION OF sensor_readings DEFAULT;
      """
      down """
      DROP TABLE IF EXISTS sensor_readings_default;
      """
    end

    # Example: Create a partition for January 2024
    statement :january_2024_partition do
      up """
      CREATE TABLE IF NOT EXISTS sensor_readings_2024_01
      PARTITION OF sensor_readings
      FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
      """
      down """
      DROP TABLE IF EXISTS sensor_readings_2024_01;
      """
    end

    # Example: Create a partition for February 2024
    statement :february_2024_partition do
      up """
      CREATE TABLE IF NOT EXISTS sensor_readings_2024_02
      PARTITION OF sensor_readings
      FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
      """
      down """
      DROP TABLE IF EXISTS sensor_readings_2024_02;
      """
    end
  end
end

Dynamically Creating Partitions

For list-partitioned tables, you may want to create partitions dynamically as part of a action. Here's an example helper function for creating partitions:

def create_partition(resource, partition_name, list_value) do
  repo = AshPostgres.DataLayer.Info.repo(resource)
  table_name = AshPostgres.DataLayer.Info.table(resource)
  schema = AshPostgres.DataLayer.Info.schema(resource) || "public"

  sql = """
  CREATE TABLE IF NOT EXISTS "#{schema}"."#{partition_name}"
  PARTITION OF "#{schema}"."#{table_name}"
  FOR VALUES IN ('#{list_value}')
  """

  case Ecto.Adapters.SQL.query(repo, sql, []) do
    {:ok, _} ->
      :ok

    {:error, %{postgres: %{code: :duplicate_table}}} ->
      :ok

    {:error, error} ->
      {:error, "Failed to create partition for #{table_name}: #{inspect(error)}"}
  end
end

Similarly, you'll want to dynamically drop partitions when they're no longer needed.

See Also

• Ecto.Migration.table/2 documentation for more information on table options
• PostgreSQL Partitioning documentation for detailed information on partitioning strategies
• Custom Statements documentation for more information on using custom statements in migrations