Indexes

Declare database indexes with [Index]: single-column and composite, unique, partial, covering, sorted, and with a portable index method.

updated 2 Sept 20267 min readv0.3.8View as Markdown

Overview

[Index] declares a database index over one or more columns. Put it on a property for a single-column index, or on the class and list the properties for a composite one, exactly as [Unique] works.

using Socigy.OpenSource.DB.Attributes;

[Table("users")]
[Index(nameof(TenantId), nameof(Email), Unique = true)]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public Guid TenantId { get; set; }

    [Index]
    public string Email { get; set; }

    public string? Status { get; set; }
}

CREATE INDEX IF NOT EXISTS "IX_users_email" ON "users" ("email");
CREATE UNIQUE INDEX IF NOT EXISTS "UX_users_tenant_id_email" ON "users" ("tenant_id", "email");

The attribute may be applied more than once, so a column can carry several indexes:

[Index]
[Index(Where = "status <> 'deleted'", Name = "ix_users_live_email")]
public string Email { get; set; }

An index is not a constraint. Use [Unique] when you want the database to enforce uniqueness as part of the table definition, and [Index(Unique = true)] when you want a unique index you can also make partial or sort.

Index names

Left unnamed, an index gets a deterministic name built from the table and its key columns: IX_ for a plain index, UX_ for a unique one.

[Index]                                     // IX_users_email
[Index(Unique = true)]                      // UX_users_email
[Index(Where = "archived = false")]         // IX_users_email_3f8a21c4

Two indexes over the same columns that differ only in their options get a short suffix derived from those options, so their names cannot collide. A name longer than the database engine's identifier limit is shortened deterministically rather than being silently truncated by the server, which would merge two distinct indexes into one.

Set Name to choose the name yourself:

[Index(nameof(TenantId), nameof(Email), Name = "ix_tenant_email")]

Options

Option	Type	Effect
`Name`	`string`	Index name. Derived from the table and key columns when unset.
`Unique`	`bool`	Enforces uniqueness across the key columns.
`Method`	`string`	What the index is for, as a `DbIndexMethods` constant.
`Where`	`string`	Restricts the index to the rows matching a raw SQL predicate.
`Include`	`string[]`	Non-key columns stored in the index (covering index).
`Descending`	`bool`	Sorts every key column descending.
`Nulls`	`string`	Where NULLs sort, as a `DbIndexNulls` constant, for every key column.
`DescendingColumns`	`string[]`	The key columns that sort descending.
`NullsFirstColumns`	`string[]`	The key columns that sort NULLs first.
`NullsLastColumns`	`string[]`	The key columns that sort NULLs last.

Unique indexes

[Table("users")]
[Index(nameof(TenantId), nameof(Email), Unique = true)]
public partial class User { /* ... */ }

CREATE UNIQUE INDEX IF NOT EXISTS "UX_users_tenant_id_email" ON "users" ("tenant_id", "email");

Partial indexes

Where restricts the index to the rows that match it, which keeps the index small when most rows are irrelevant to the queries using it. A unique partial index enforces uniqueness only among the matching rows, which is the usual way to say "at most one active row per user":

[Table("subscriptions")]
[Index(nameof(UserId), Unique = true, Where = "status = 'active'")]
public partial class Subscription { /* ... */ }

CREATE UNIQUE INDEX IF NOT EXISTS "UX_subscriptions_user_id_9b41e07f"
    ON "subscriptions" ("user_id") WHERE status = 'active';

The predicate is raw SQL written against database column names, and is passed through verbatim. See Portability.

Covering indexes

Include stores extra columns in the index without making them part of the key, so a query reading only those columns is answered from the index alone:

[Table("orders")]
[Index(nameof(CustomerId), Include = new[] { nameof(Status), nameof(Total) })]
public partial class Order { /* ... */ }

CREATE INDEX IF NOT EXISTS "IX_orders_customer_id_7c25ab90"
    ON "orders" ("customer_id") INCLUDE ("status", "total");

Sort order

An index scanned in the same direction as your ORDER BY avoids a sort step. Set the order for the whole index with Descending and Nulls, or per column with the array forms:

// Every key column descending
[Index(nameof(CreatedAt), Descending = true, Nulls = DbIndexNulls.Last)]

// Only CreatedAt descending, TenantId stays ascending
[Index(nameof(TenantId), nameof(CreatedAt), DescendingColumns = new[] { nameof(CreatedAt) })]

CREATE INDEX IF NOT EXISTS "IX_orders_created_at_1d4f9e33"
    ON "orders" ("created_at" DESC NULLS LAST);

The scalar options apply to every key column; the array options override them for the columns they name.

Index methods

Method says what the index is for rather than naming a specific database's access method, so the same model works on every engine. Each engine maps the intent to its own equivalent:

Constant	Use for	PostgreSQL
`DbIndexMethods.Default`	Equality, ranges, sorting. The default.	`btree`
`DbIndexMethods.Hash`	Equality only. Smaller, but useless for ranges and sorting.	`hash`
`DbIndexMethods.FullText`	Text search.	`gin`
`DbIndexMethods.Spatial`	Geometric containment and proximity.	`gist`
`DbIndexMethods.Contains`	Containment over arrays and JSON documents.	`gin`
`DbIndexMethods.BlockRange`	Very large tables already ordered by the indexed column, such as an append-only timestamp.	`brin`

[Index(Method = DbIndexMethods.Contains)]
public string Tags { get; set; }

CREATE INDEX IF NOT EXISTS "IX_documents_tags_5a70c1de"
    ON "documents" USING gin ("tags");

Portability

The attribute is engine-neutral, with two deliberate exceptions:

Where is raw SQL.
RawMethod takes the engine's own access method name ("spgist") and overrides Method.

A model using either is tied to one database engine. Everything else is described in portable terms and translated per engine.

When an engine cannot express an option at all, what happens depends on whether the option affects performance or meaning:

Option	On an engine that lacks it
`Method`, `Include`, sort order	Dropped, with a warning from the CLI. The index returns the same rows, less efficiently.
`Where` on a non-unique index	Dropped, with a warning. The index covers more rows than asked.
`Where` on a unique index	Error. Indexing every row would enforce uniqueness over rows the filter deliberately excludes.
`Unique`	Error. A silently non-unique index would stop the database enforcing what the model declares.

NOTE

PostgreSQL supports every option on this page, so none of the degradations above apply to it today.

Migrations

Indexes take part in migrations like any other schema element. Adding one to a model generates a CREATE INDEX; removing it generates a DROP INDEX. Because no database can alter an index in place, changing any option regenerates it as a drop followed by a create.

Statement order within a migration follows the dependencies: an index is created after the table and columns it covers exist, and dropped before them.

WARNING

Creating an index locks the table against writes until it has been built, which on a large table can be a long outage. CREATE INDEX CONCURRENTLY is not generated, because a migration's DDL and its bookkeeping row are applied in a single transaction and a concurrent build cannot run inside one. To build an index concurrently, do it outside the tool and add the [Index] afterwards, where the generated CREATE INDEX IF NOT EXISTS becomes a no-op.

Dropping an index raises a safety warning when the migration is generated, because rebuilding it later costs as much as building it did the first time. Rolling back a migration that dropped an index rebuilds it from scratch.

Scaffolding

Database-first scaffolding reads existing indexes and emits [Index] attributes for them, so scaffolding a database and then generating a migration does not propose dropping the indexes it already has. Index methods come back as portable DbIndexMethods constants; an access method with no portable equivalent is recovered as RawMethod.

Indexes that implement a primary key or a UNIQUE constraint are left out, since those are already modelled as constraints. Expression indexes (ON t ((lower(email)))) have no attribute form; they are reported and skipped, so you can re-create them by hand.

Diagnostics

A column named by a [Index] that the table does not have is a compile-time error (SCGDB026). Using nameof gets this from the compiler as well; the diagnostic covers string literals, which the compiler cannot check.

[Index("Emial")]   // SCGDB026: [Index] references an unknown property

← previousConstraints

next →JSON columns