Index Strategy

Guide for creating efficient database indexes in ObjectStack.

Default Behavior

ObjectStack automatically creates indexes for:

Primary keys (id)
Foreign keys (lookup/master_detail fields)
Unique constraints

Only declare non-default values. type defaults to 'btree' and unique defaults to false — omit them when using defaults.

Index Types

Type	Default?	When to Use	Performance
`btree`	✅ Yes	Equality and range queries (`=`, `<`, `>`, `BETWEEN`)	Excellent
`hash`	No	Exact equality only (`=`) — rare use case	Fast for `=`, poor for ranges
`fulltext`	No	Text search columns (descriptions, notes)	Text search only
`gin`	No	Array / JSONB containment, full-text search	JSONB, arrays, tags
`gist`	No	Geospatial / range types	Location, geometry

Syntax

indexes: [
  { fields: ['status', 'created_at'] },              // btree (default)
  { fields: ['email'], unique: true },                // btree + unique
  { fields: ['description'], type: 'fulltext' },      // non-default type
  { fields: ['tags'], type: 'gin' },                  // non-default type
  { fields: ['location'], type: 'gist' },             // non-default type
]

When to Add Indexes

✅ Always Index

Foreign keys — Automatic, but verify
Filter fields — Columns used in WHERE clauses
Sort fields — Columns used in ORDER BY
Unique constraints — Enforce uniqueness at DB level
Composite filters — Fields commonly filtered together

⚠️ Consider Indexing

Join columns — Non-foreign-key join fields
Frequent aggregations — GROUP BY columns
Range queries — Date ranges, numeric ranges
Partial data — Use partial indexes for subset queries

❌ Avoid Indexing

Low cardinality — Boolean fields (unless combined with others)
Rarely queried — Fields almost never filtered/sorted
High write volume — Every insert/update maintains indexes
Large text — Full-text index only when needed
Calculated fields — Index source fields instead

Examples

Composite Index (Multi-Column)

indexes: [
  // Most specific first (status), then sort key
  { fields: ['status', 'created_at'] },

  // Can satisfy queries like:
  // - WHERE status = 'active'
  // - WHERE status = 'active' ORDER BY created_at DESC
  // - WHERE status = 'active' AND created_at > '2026-01-01'
]

Unique Index

indexes: [
  // Single column uniqueness
  { fields: ['email'], unique: true },

  // Composite uniqueness
  { fields: ['tenant_id', 'username'], unique: true },
]

Partial Index

indexes: [
  // Only index active records
  {
    fields: ['created_at'],
    partial: "status = 'active'",
  },

  // Only index non-deleted records
  {
    fields: ['email'],
    unique: true,
    partial: "deleted_at IS NULL",
  },
]

Full-Text Index

indexes: [
  {
    fields: ['description', 'notes'],
    type: 'fulltext',
  },
]

GIN Index (JSONB/Array)

indexes: [
  // JSONB field
  {
    fields: ['metadata'],
    type: 'gin',
  },

  // Array field
  {
    fields: ['tags'],
    type: 'gin',
  },
]

Geospatial Index (GIST)

indexes: [
  {
    fields: ['location'],
    type: 'gist',
  },
]

Incorrect vs Correct

❌ Incorrect — Redundant Default Values

indexes: [
  { fields: ['status'], type: 'btree', unique: false },  // ❌ Redundant defaults
  { fields: ['email'], type: 'btree', unique: true },    // ❌ Redundant type
]

✅ Correct — Omit Defaults

indexes: [
  { fields: ['status'] },               // ✅ btree and unique: false are defaults
  { fields: ['email'], unique: true },  // ✅ btree is default, only specify unique
]

❌ Incorrect — Over-Indexing

indexes: [
  { fields: ['is_active'] },        // ❌ Boolean, low cardinality
  { fields: ['is_deleted'] },       // ❌ Boolean, low cardinality
  { fields: ['is_verified'] },      // ❌ Boolean, low cardinality
  { fields: ['status'] },           // ❌ Already indexed elsewhere
  { fields: ['created_at'] },       // ❌ Already indexed elsewhere
]

✅ Correct — Strategic Indexing

indexes: [
  // Composite for common query pattern
  { fields: ['is_active', 'created_at'] },

  // Single index covers multiple queries
  { fields: ['status', 'priority'] },
]

❌ Incorrect — Wrong Order in Composite

indexes: [
  // Querying by created_at with status filter
  { fields: ['created_at', 'status'] },  // ❌ Wrong order
]

✅ Correct — Most Selective First

indexes: [
  // Status is more selective (filters more), goes first
  { fields: ['status', 'created_at'] },  // ✅ Correct order
]

Composite Index Strategy

Order Matters

// Index: ['status', 'priority', 'created_at']

// ✅ Can use index
WHERE status = 'active'
WHERE status = 'active' AND priority = 'high'
WHERE status = 'active' AND priority = 'high' ORDER BY created_at

// ❌ Cannot use index efficiently
WHERE priority = 'high'  // Skips first column
WHERE created_at > '2026-01-01'  // Skips first two columns

Left-to-Right Rule

Composite indexes are used left-to-right. Querying only the second or third column doesn't use the index.

Selectivity Rule

Place most selective (unique) fields first, then range/sort fields last.

// Good order: selective → range
{ fields: ['tenant_id', 'status', 'created_at'] }

// Bad order: range → selective
{ fields: ['created_at', 'status', 'tenant_id'] }

Partial Indexes

Use partial indexes to index only a subset of rows:

// Only index active records (common query)
{
  fields: ['created_at'],
  partial: "status = 'active'",
}

// Only index high-value accounts
{
  fields: ['annual_revenue'],
  partial: "annual_revenue > 1000000",
}

// Only index non-deleted records
{
  fields: ['email'],
  unique: true,
  partial: "deleted_at IS NULL",
}

Benefits:

Smaller index size
Faster writes (fewer rows to maintain)
Faster queries (focused data subset)

Performance Trade-offs

Index Benefits

✅ Faster SELECT queries
✅ Faster ORDER BY operations
✅ Faster JOIN operations
✅ Enforce uniqueness at DB level

Index Costs

❌ Slower INSERT/UPDATE/DELETE (index maintenance)
❌ Increased storage (each index duplicates data)
❌ Query planner overhead (more indexes = more choices)

General Guidelines

Table Size	Max Indexes	Reasoning
< 1K rows	2-3	Low volume, indexes may not help
1K - 100K rows	3-5	Balance read/write performance
100K - 1M rows	5-8	Read optimization critical
> 1M rows	8-12	Consider partitioning + indexes

Index Naming Convention

ObjectStack auto-generates index names. To specify custom names:

{
  name: 'idx_account_status_created',  // Custom name
  fields: ['status', 'created_at'],
}

Auto-generated pattern: idx_{object}_{field1}_{field2}_{...}

Monitoring Index Usage

Use database tools to monitor index usage:

-- PostgreSQL: Find unused indexes
SELECT
  schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

-- MySQL: Check index cardinality
SHOW INDEX FROM your_table;

Best Practices

Index foreign keys — Always (automatic in ObjectStack)
Composite for common queries — Combine frequently filtered columns
Order matters — Most selective field first
Partial for subsets — Index only relevant rows
Unique for constraints — Enforce at DB level
Monitor usage — Remove unused indexes
Limit total indexes — Balance read/write performance
Avoid over-indexing — More indexes ≠ better performance
Test with production data — Index effectiveness depends on data volume
Use EXPLAIN — Verify query plans before deploying indexes

Common Query Patterns

Filter by Status + Sort by Date

// Query: WHERE status = 'active' ORDER BY created_at DESC LIMIT 50
indexes: [
  { fields: ['status', 'created_at'] },
]

Multi-Tenant Queries

// Query: WHERE tenant_id = X AND ...
indexes: [
  { fields: ['tenant_id', 'status', 'created_at'] },
]

Text Search

// Query: WHERE description ILIKE '%keyword%'
indexes: [
  { fields: ['description'], type: 'fulltext' },
]

Array/JSONB Containment

// Query: WHERE tags @> ['urgent']
indexes: [
  { fields: ['tags'], type: 'gin' },
]

Location-Based Queries

// Query: WHERE ST_DWithin(location, point, distance)
indexes: [
  { fields: ['location'], type: 'gist' },
]

Uh oh!

FilesExpand file tree

indexing.md

Latest commit

History