Indexes

All index methods, partial indexes, expression indexes, covering indexes, and CONCURRENTLY behaviour.

Indexes are declared in the INDICES { } block inside a table’s { }. By default, DPG emits plain CREATE INDEX for additions on existing tables. Write CONCURRENTLY on an individual index to make it CREATE INDEX CONCURRENTLY instead — emitted as a non-transactional step after COMMIT.

UNIQUE and CONCURRENTLY are both prefix keywords, written before the index name — mirroring real PostgreSQL’s own CREATE UNIQUE INDEX CONCURRENTLY name ON table order exactly. CONCURRENTLY is a bare presence keyword, same as in real PostgreSQL: there is no CONCURRENTLY false and no project-wide setting that changes the default — writing the keyword is the only way an index is ever created concurrently.

Standard btree index

TABLE users ( email TEXT NOT NULL, ... )
{
    INDICES { idx_users_email (email); }
}

-- emits (transactional)
CREATE INDEX IF NOT EXISTS "idx_users_email"
    ON "public"."users" ("email");

Unique index

{ INDICES { UNIQUE idx_unique_slug (slug); } }

CREATE UNIQUE INDEX IF NOT EXISTS "idx_unique_slug"
    ON "public"."users" ("slug");

Composite index with sort order

{ INDICES { idx_tenant_created (tenant_id ASC, created_at DESC); } }

CREATE INDEX IF NOT EXISTS "idx_tenant_created"
    ON "public"."events" ("tenant_id" ASC, "created_at" DESC);

Partial index

{ INDICES { idx_active_users (email) WHERE (status = 'active'); } }

CREATE INDEX IF NOT EXISTS "idx_active_users"
    ON "public"."users" ("email") WHERE (status = 'active');

Expression index

{ INDICES { idx_lower_email (lower(email)); } }

CREATE INDEX IF NOT EXISTS "idx_lower_email"
    ON "public"."users" (lower("email"));

Covering index (INCLUDE)

{ INDICES { idx_covering (user_id) INCLUDE (email, created_at); } }

CREATE INDEX IF NOT EXISTS "idx_covering"
    ON "public"."users" ("user_id") INCLUDE ("email", "created_at");

GIN index

{ INDICES {
    idx_tags USING gin (tags);
    idx_fts  USING gin (search_vec);
} }

CREATE INDEX IF NOT EXISTS "idx_tags"
    ON "public"."posts" USING gin ("tags");

CREATE INDEX IF NOT EXISTS "idx_fts"
    ON "public"."posts" USING gin ("search_vec");

GiST index

{ INDICES { idx_location USING gist (location); } }

CREATE INDEX IF NOT EXISTS "idx_location"
    ON "public"."places" USING gist ("location");

BRIN index with storage parameter

{ INDICES { idx_brin USING brin (created_at) WITH (pages_per_range = 128); } }

CREATE INDEX IF NOT EXISTS "idx_brin"
    ON "public"."events" USING brin ("created_at") WITH (pages_per_range = 128);

Index with tablespace

{ INDICES { idx_archived (archived_at) TABLESPACE archive_space; } }

CREATE INDEX IF NOT EXISTS "idx_archived"
    ON "public"."records" ("archived_at") TABLESPACE "archive_space";

Concurrent index creation

Write CONCURRENTLY to avoid locking the table during index creation on a large, live table:

{ INDICES { CONCURRENTLY idx_email (email); } }

-- emits (non-transactional, after COMMIT)
CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_email" ON "public"."users" ("email");

This has no effect on an index declared alongside its own brand-new table — PostgreSQL rejects CREATE INDEX CONCURRENTLY inside a transaction block, and a new table’s indexes are always emitted transactionally with it, so the compiler silently forces them non-concurrent regardless of this keyword.

Index removal

Removing an index from the INDICES block emits DROP INDEX — classified as CAUTION (acquires ACCESS EXCLUSIVE lock; no data loss, but blocks concurrent reads during the drop).

-- emits
DROP INDEX IF EXISTS "public"."idx_old_index";