Database Indexes

Indexes improve query performance by letting the database look up rows without scanning the entire table. SchemaStack lets you create, list, and delete indexes on any view through the Admin UI or the REST API.

How Indexes Work

An index is a separate data structure that the database maintains alongside your table. When you query or sort by an indexed column, the database uses the index to find matching rows quickly instead of reading every row.

When to add an index:

• Columns you frequently filter on (e.g., status, email, created_date)
• Columns you sort by in large tables
• Foreign key columns (often indexed automatically)
• Columns used in unique constraints

When indexes aren't needed:

• Small tables (under a few thousand rows) — full scans are fast enough
• Columns with very few distinct values (e.g., a boolean active flag)
• Columns you rarely query or filter on

TIP

Adding an index speeds up reads but adds a small overhead to writes (inserts, updates, deletes), since the database must keep the index up to date. For most workloads this is negligible.

Index Types

Type	Description	Use Case
Single-column	Index on one column	Simple lookups and sorts
Composite	Index on multiple columns	Queries that filter or sort by several columns together
Unique	Enforces uniqueness across indexed columns	Ensuring no duplicate values (e.g., email addresses)

Creating an Index

Via API

POST /api/indexes/view/{viewUuid}
Content-Type: application/json
Authorization: Bearer {token}

{
  "name": "idx_orders_customer_date",
  "columnNames": ["customer_id", "order_date"],
  "isUnique": false
}

Response: 201 Created

{
  "uuid": "a1b2c3d4-...",
  "name": "idx_orders_customer_date",
  "columnNames": ["customer_id", "order_date"],
  "isUnique": false
}

The metadata record is created immediately. The actual CREATE INDEX DDL runs asynchronously in the background and is typically applied within seconds.

Naming Conventions

Choose descriptive index names that indicate the table and columns:

• idx_orders_customer_id — single column
• idx_orders_customer_date — composite
• idx_users_email_unique — unique index

Listing Indexes

GET /api/indexes/view/{viewUuid}
Authorization: Bearer {token}

Response: 200 OK

[
  {
    "uuid": "a1b2c3d4-...",
    "name": "idx_orders_customer_date",
    "columnNames": ["customer_id", "order_date"],
    "isUnique": false
  }
]

Returns an empty array when no indexes exist on the view.

Deleting an Index

DELETE /api/indexes/{indexUuid}
Authorization: Bearer {token}

Response: 204 No Content

The index is dropped from the database asynchronously, similar to creation.

Schema Import

When you import an existing database schema, SchemaStack automatically detects and preserves all existing indexes — including composite and unique indexes. No manual setup is needed for tables that already have indexes.

Permissions

Action	Required Permission
List indexes	VIEW_DATA
Create index	CONFIGURE_VIEW
Delete index	CONFIGURE_VIEW

Limitations

• Index rename is not supported — delete and recreate instead
• Partial indexes (indexes with a WHERE clause) are not yet supported
• Expression indexes (indexes on computed expressions) are not yet supported
• Only B-tree indexes are created (the PostgreSQL default and most common type)