Returns an index struct that can be given to create/1, drop/1, etc.

Expects the table name as the first argument and the index field(s) as the second. The fields can be atoms, representing columns, or strings, representing expressions that are sent as-is to the database.

Options

• :name - the name of the index. Defaults to "#{table}_#{column}_index".
• :unique - indicates whether the index should be unique. Defaults to false.
• :concurrently - indicates whether the index should be created/dropped concurrently.
• :using - configures the index type.
• :prefix - specify an optional prefix for the index.
• :where - specify conditions for a partial index.
• :include - specify fields for a covering index. This is not supported by all databases. For more information on PostgreSQL support, please read the official docs.

Adding/dropping indexes concurrently

PostgreSQL supports adding/dropping indexes concurrently (see the docs). However, this feature does not work well with the transactions used by Ecto to guarantee integrity during migrations.

Therefore, to migrate indexes concurrently, you need to set both @disable_ddl_transaction and @disable_migration_lock to true:

defmodule MyRepo.Migrations.CreateIndexes do
  use Ecto.Migration
  @disable_ddl_transaction true
  @disable_migration_lock true

  def change do
    create index("posts", [:slug], concurrently: true)
  end
end

Disabling DDL transactions removes the guarantee that all of the changes in the migration will happen at once. Disabling the migration lock removes the guarantee only a single node will run a given migration if multiple nodes are attempting to migrate at the same time.

Since running migrations outside a transaction and without locks can be dangerous, consider performing very few operations in migrations that add concurrent indexes. We recommend to run migrations with concurrent indexes in isolation and disable those features only temporarily.

Index types

When creating an index, the index type can be specified with the :using option. The :using option can be an atom or a string, and its value is passed to the generated USING clause as-is.

For example, PostgreSQL supports several index types like B-tree (the default), Hash, GIN, and GiST. More information on index types can be found in the PostgreSQL docs.

Partial indexes

Databases like PostgreSQL and MSSQL support partial indexes.

A partial index is an index built over a subset of a table. The subset is defined by a conditional expression using the :where option. The :where option can be an atom or a string; its value is passed to the generated WHERE clause as-is.

More information on partial indexes can be found in the PostgreSQL docs.

Examples

# With no name provided, the name of the below index defaults to
# products_category_id_sku_index
create index("products", [:category_id, :sku], unique: true)

# The name can also be set explicitly
create index("products", [:category_id, :sku], name: :my_special_name)

# Indexes can be added concurrently
create index("products", [:category_id, :sku], concurrently: true)

# The index type can be specified
create index("products", [:name], using: :hash)

# Partial indexes are created by specifying a :where option
create index("products", [:user_id], where: "price = 0", name: :free_products_index)

# Covering indexes are created by specifying a :include option
create index("products", [:user_id], include: [:category_id])

Indexes also support custom expressions. Some databases may require the index expression to be written between parentheses:

# Create an index on a custom expression
create index("products", ["(lower(name))"], name: :products_lower_name_index)

# Create a tsvector GIN index on PostgreSQL
create index("products", ["(to_tsvector('english', name))"],
             name: :products_name_vector, using: "GIN")