Skip to main content

​
What are Indexes?

Indexes are data structures that improve query performance by creating optimized access paths to your data:
  • Speed up queries - Find data faster instead of scanning entire tables
  • Enable advanced searches - Support complex search patterns and operations
  • Optimize specific use cases - Different index types for different needs
  • Trade-off storage for speed - Use extra storage to achieve faster queries

​
Index Types

Timbal supports several PostgreSQL index types, each optimized for different use cases:

B-tree (btree)

Best for equality and range queries on sortable data, e.g. IDs, dates, names, prices.

Hash

Optimized for exact equality comparisons, e.g. status codes, categories.

GIN (Generalized Inverted Index)

Good for full-text search, arrays, and JSON data, e.g. long texts, tags, metadata.

GiST (Generalized Search Tree)

Good for geometric data, full-text search, and nearest-neighbor searches, e.g. locations, nearest neighbors.

BRIN (Block Range Index)

Efficient for very large tables where data has natural clustering, e.g. time series, log data.

​
Creating Indexes

Only create indexes for columns you actually query - unnecessary indexes waste storage and slow down writes
from timbal.platform.indexes import create_index

# Create a basic B-tree index on a single column
await create_index(
    org_id="your-org-id",
    kb_id="your-kb-id",
    table_name="Users",
    name="idx_users_email",        # Index name
    column_names=["email"],        # Columns to index
    type="btree",                  # Index type
    is_unique=True                 # Enforce uniqueness
)
Index names must be unique across your entire knowledge base - two different tables cannot have indexes with the same name

​
Composite Indexes

# Create a composite index on multiple columns
await create_index(
    org_id="your-org-id",
    kb_id="your-kb-id",
    table_name="Orders",
    name="idx_orders_customer_date",
    column_names=["customer_id", "order_date"],  # Multiple columns
    type="btree",
    is_unique=False
)
Column order is critical: Put the most selective columns first (columns with many unique values) before less selective ones.
  • High selectivity = Many unique values (user_id, email, timestamps)
  • Low selectivity = Few unique values (status, boolean flags, categories)
  • Good Example
  • Poor Example
# user_id eliminates most rows immediately
["user_id", "status", "created_at"]
user_id has thousands of unique values, so the database can quickly narrow down to just a few rows.
Composite indexes can only be used from left to right - you can’t skip the first columns.Given an index ["customer_id", "order_date", "status"]:These queries work efficiently:
WHERE customer_id = 123 AND order_date > '2024-01-01' AND status = 'shipped'
WHERE customer_id = 123
WHERE customer_id = 123 AND order_date > '2024-01-01'
These queries won’t be as efficient:
WHERE order_date > '2024-01-01'  -- skips first column
WHERE order_date = '2024-01-01' AND customer_id = 123  -- wrong order
See the API Reference for endpoints to list, delete, monitor, and configure indexes to help you manage them as your data grows.