Skip to main content

Plan indexes

Indexes can make a selective filter or stable sort much faster, but each index also increases storage and write work. Add an index for an observed query pattern, not for every field.

Test index changes first

Creating or removing an index changes the table and can slow writes while it runs. Try the change in a development app, and apply it to production at a quiet time.

Start from the query​

Record these details before adding an index:

  • table and app;
  • filter fields and operators;
  • sort fields and directions;
  • expected result size;
  • current row count and growth rate; and
  • measured query latency with representative data.

For example, a work queue that repeatedly filters by status and sorts by created_at has a different index requirement from a lookup by unique external_id.

Indexes TaruviBase creates for you​

TaruviBase creates indexes automatically for:

  • primary keys;
  • unique constraints; and
  • search or vector indexes configured for those table capabilities.

Don't add another index on the same fields.

A foreign key doesn't automatically get an index. If you often filter by a foreign-key field, consider indexing it.

Add a custom index​

Declare custom indexes in the table's schema with an indexes array, then update the table as described in Plan schema changes:

{
  "indexes": [
    {
      "name": "idx_tasks_status_created_at",
      "fields": ["status", "created_at"],
      "unique": false,
      "method": "btree"
    }
  ]
}

List fields in the order your queries filter and then sort by. Give each index a name that is unique in the site. For partial or expression indexes, or if you're unsure which index to add, contact TaruviBase support with the query details above.

Verify an index change​

After an index is applied:

  1. Run the original query with representative parameters.
  2. Compare latency and the database query plan with the baseline.
  3. Exercise inserts and updates to measure write impact.
  4. Confirm unique behavior when the index enforces uniqueness.
  5. Monitor index size and unused-index statistics.
  6. Keep a removal plan if the index does not improve the target workload.

An index only changes how fast a query runs; your SDK and REST queries stay the same.

Continue with: