---
title: "HeliosDB Indexing Overview"
---

# HeliosDB Indexing Overview

---

## Introduction

HeliosDB provides a comprehensive indexing system designed for high-performance data access across diverse workloads. From traditional B-tree indexes for range queries to advanced vector indexes for AI/ML applications, HeliosDB supports the full spectrum of indexing requirements for modern data-intensive applications.

This document provides an overview of all index types available in HeliosDB, their use cases, and key characteristics.

---

## Index Types at a Glance

| Index Type | Best For | Access Pattern | Range Queries | Memory Footprint |
|------------|----------|----------------|---------------|------------------|
| **B-tree** | General purpose | O(log n) | Yes | Medium |
| **Hash** | Exact matches | O(1) | No | Low |
| **LSM-Tree** | Write-heavy workloads | O(log n) | Yes | Low (tiered) |
| **GIN** | Multi-valued columns | O(log n) | Partial | High |
| **GiST** | Spatial/geometric data | O(log n) | Yes | Medium |
| **HNSW** | Vector similarity | O(log n) approx | N/A | High |
| **IVF** | Large-scale vectors | O(n/k) approx | N/A | Medium |
| **Full-Text** | Text search | O(log n) | N/A | High |

---

## B-tree Indexes

B-tree indexes are the default and most versatile index type in HeliosDB. They maintain sorted data and support efficient equality and range queries.

### Key Characteristics

- **Balanced tree structure** with O(log n) lookup, insert, and delete
- **Supports range queries**: `<`, `<=`, `=`, `>=`, `>`, `BETWEEN`
- **Supports ORDER BY** operations efficiently
- **Ideal for columns with high cardinality**

### Use Cases

- Primary key lookups
- Foreign key joins
- Sorted result retrieval

### Example

```sql
-- Create a B-tree index (default)
CREATE INDEX idx_orders_date ON orders (order_date);

-- Composite B-tree index
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

-- Query using the index
SELECT * FROM orders
WHERE customer_id = 12345
  AND order_date BETWEEN '2025-01-01' AND '2025-12-31';
```

---

## Hash Indexes

Hash indexes provide O(1) constant-time lookups for exact equality comparisons. They are optimized for high-speed point queries.

### Key Characteristics

- **O(1) lookup time** for equality comparisons
- **Does not support range queries**
- **Lower memory overhead** than B-trees for equality-only access
- **Ideal for lookup tables** and exact-match scenarios

### Use Cases

- Session ID lookups
- Cache key lookups
- Unique identifier searches
- Configuration value retrieval

### Example

```sql
-- Create a hash index
CREATE INDEX idx_sessions_id ON sessions USING HASH (session_id);

-- Query using the hash index
SELECT user_id, created_at FROM sessions WHERE session_id = 'abc123xyz';
```

---

## LSM-Tree Indexes

LSM-Tree (Log-Structured Merge-Tree) indexes are optimized for write-heavy workloads. They buffer writes in memory and periodically flush to sorted disk structures.

### Key Characteristics

- **Optimized for high write throughput**
- **Writes buffered in memtable** before flushing to SSTables
- **Background compaction** merges and optimizes disk structures
- **Supports range queries** via sorted SSTables

### Use Cases

- Event logging and time-series data
- IoT sensor data ingestion
- High-velocity insert workloads
- Append-heavy access patterns

### Example

```sql
-- Create an LSM-Tree index for high-write workloads
CREATE INDEX idx_events_timestamp ON events USING LSMTREE (timestamp);

-- Configure memtable size
ALTER INDEX idx_events_timestamp SET (memtable_size_mb = 64);
```

---

## GIN (Generalized Inverted) Indexes

GIN indexes are designed for columns containing multiple values per row, such as arrays, JSONB documents, and full-text search vectors.

### Key Characteristics

- **Inverted index structure** mapping values to rows
- **Efficient for containment queries** (`@>`, `<@`, `&&`)
- **Supports arrays, JSONB, and tsvector**
- **Higher build and update cost** than B-tree

### Use Cases

- JSONB document queries
- Array containment searches
- Tag-based filtering
- Full-text search

### Example

```sql
-- Create a GIN index on JSONB column
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Query JSONB with the index
SELECT * FROM products
WHERE attributes @> '{"category": "electronics", "in_stock": true}';

-- Create GIN index on array column
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Query array containment
SELECT * FROM posts WHERE tags && ARRAY['database', 'performance'];
```

---

## GiST (Generalized Search Tree) Indexes

GiST indexes support complex data types like geometric shapes, ranges, and spatial data. They enable efficient spatial queries and nearest-neighbor searches.

### Key Characteristics

- **Supports geometric and range data types**
- **Enables spatial queries** (contains, overlaps, nearest-neighbor)
- **Balanced tree with pluggable operators**
- **Used for PostGIS-style geospatial queries**

### Use Cases

- Geospatial location queries
- Bounding box searches
- IP range lookups
- Temporal range overlaps

### Example

```sql
-- Create a GiST index for geometric data
CREATE INDEX idx_locations_geo ON locations USING GIST (coordinates);

-- Spatial containment query
SELECT name FROM locations
WHERE coordinates && ST_MakeEnvelope(-122.5, 37.5, -122.0, 38.0);

-- Create GiST index for IP ranges
CREATE INDEX idx_ip_ranges ON network_blocks USING GIST (ip_range);

-- Query IP containment
SELECT block_name FROM network_blocks WHERE ip_range >> '192.168.1.100';
```

---

## Vector Indexes (HNSW)

HNSW (Hierarchical Navigable Small World) indexes provide fast approximate nearest-neighbor search for high-dimensional vector data, essential for AI/ML applications.

### Key Characteristics

- **Graph-based structure** with navigable small-world properties
- **Sub-linear search time** for high-dimensional vectors
- **Configurable accuracy vs speed tradeoff**
- **Supports cosine, L2, and inner product distance metrics**

### Configuration Parameters

| Parameter | Description | Default |
|-----------|-------------|---------|
| `m` | Connections per layer | 32 |
| `ef_construction` | Build-time beam width | 40 |
| `ef_search` | Query-time beam width | 10 |

### Use Cases

- Semantic search
- Image similarity
- Recommendation systems
- RAG (Retrieval-Augmented Generation)

### Example

```sql
-- Create HNSW index for vector embeddings
CREATE INDEX idx_embeddings_hnsw ON documents USING HNSW (embedding vector_l2_ops)
WITH (m = 32, ef_construction = 64);

-- Similarity search
SELECT id, title, embedding <-> '[0.1, 0.2, 0.3, ...]'::vector AS distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, 0.3, ...]'::vector
LIMIT 10;
```

---

## Vector Indexes (IVF)

IVF (Inverted File) indexes partition vectors into clusters for efficient large-scale similarity search. They offer a good balance between speed and memory usage.

### Key Characteristics

- **Cluster-based partitioning** using k-means
- **Searches only nearby clusters** for efficiency
- **Configurable number of clusters (nlist)** and probes (nprobe)
- **Better for very large vector datasets** than HNSW

### Configuration Parameters

| Parameter | Description | Default |
|-----------|-------------|---------|
| `nlist` | Number of clusters | 100 |
| `nprobe` | Clusters to search | 10 |

### Use Cases

- Large-scale vector search (millions of vectors)
- Memory-constrained environments
- Batch similarity processing

### Example

```sql
-- Create IVF index with product quantization
CREATE INDEX idx_images_ivf ON images USING IVFPQ (feature_vector)
WITH (nlist = 1000, nprobe = 20);

-- Adjust probe count for accuracy
SET ivfpq.nprobe = 50;

-- Similarity search
SELECT id, image_url
FROM images
ORDER BY feature_vector <-> query_vector
LIMIT 20;
```

---

## Full-Text Search Indexes

Full-text search indexes enable efficient text search with support for stemming, stop words, ranking, and phrase matching.

### Key Characteristics

- **Tokenization and stemming** for natural language processing
- **Stop word filtering** to reduce noise
- **Relevance ranking** with ts_rank functions
- **Boolean and phrase queries**

### Use Cases

- Document search
- Product catalog search
- Knowledge base queries
- Log analysis

### Example

```sql
-- Create full-text index
CREATE INDEX idx_articles_fts ON articles USING FULLTEXT (title, content);

-- Basic search with ranking
SELECT id, title, ts_rank(document, query) AS relevance
FROM articles,
     websearch_to_tsquery('english', 'database performance') AS query
WHERE document @@ query
ORDER BY relevance DESC
LIMIT 10;

-- Phrase search
SELECT * FROM articles
WHERE content @@ phraseto_tsquery('english', 'query optimization');
```

---

## Partial Indexes

Partial indexes index only a subset of rows matching a WHERE condition, reducing index size and improving performance for filtered queries.

### Key Characteristics

- **Smaller index size** than full indexes
- **Faster maintenance** for INSERT/UPDATE/DELETE
- **Optimized for specific query patterns**

### Example

```sql
-- Index only active orders
CREATE INDEX idx_orders_active ON orders (customer_id, order_date)
WHERE status = 'active';

-- Index only recent data
CREATE INDEX idx_logs_recent ON logs (created_at, severity)
WHERE created_at > CURRENT_DATE - INTERVAL '30 days';
```

---

## Expression Indexes

Expression indexes index the result of an expression or function, enabling efficient queries on computed values.

### Key Characteristics

- **Indexes computed values** rather than raw column data
- **Supports functions, operators, and expressions**
- **Essential for case-insensitive searches** and JSON path queries

### Example

```sql
-- Case-insensitive search
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Query using the expression index
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- JSONB path expression index
CREATE INDEX idx_orders_customer_name ON orders ((data->>'customer_name'));

-- Date extraction index
CREATE INDEX idx_events_year_month ON events (EXTRACT(YEAR FROM event_date), EXTRACT(MONTH FROM event_date));
```

---

## Composite Indexes

Composite indexes index multiple columns together, optimizing queries that filter or sort on multiple columns.

### Key Characteristics

- **Column order matters**: leftmost prefix is used for lookups
- **Supports multiple access patterns** with one index
- **Reduces index count** and maintenance overhead

### Example

```sql
-- Composite index for common query pattern
CREATE INDEX idx_orders_cust_date_status ON orders (customer_id, order_date DESC, status);

-- This index supports:
-- WHERE customer_id = ?
-- WHERE customer_id = ? AND order_date = ?
-- WHERE customer_id = ? AND order_date BETWEEN ? AND ?
-- ORDER BY customer_id, order_date DESC
```

---

## Related Documentation

- **[User Guide](https://www.heliosdb.com/docs/full/features/indexing/user_guide/)** - Comprehensive guide to creating and managing indexes
- **[Quick Start](https://www.heliosdb.com/docs/full/features/indexing/quick_start/)** - Get started with indexing in 5 minutes
- **[Full-Text Search Tuning](https://www.heliosdb.com/docs/full/guides/user/full_text_search_tuning_guide/)** - Advanced full-text search configuration
- **[Vector Search](https://www.heliosdb.com/docs/full/guides/user/multimodal_vector_search_user_guide/)** - Multimodal vector search guide

---

## Performance Considerations

### Index Selection Guidelines

1. **Start with B-tree** for general-purpose indexing
2. **Use Hash** only for pure equality lookups with no range needs
3. **Choose LSM-Tree** for write-heavy workloads with eventual reads
4. **Select HNSW** for vector search with low latency requirements
5. **Pick IVF** for large-scale vector search with memory constraints
6. **Apply GIN** for JSONB and array columns
7. **Use GiST** for spatial and range data types

### Index Maintenance

> **`ANALYZE` in the current release:** `ANALYZE` (including `VACUUM ANALYZE`) is accepted for PostgreSQL compatibility and returns immediately; it does not collect planner statistics yet. Statistics collection is planned. `EXPLAIN ANALYZE`, which runs the query and reports actual rows and timing, works today.

- **Monitor index bloat** with `heliosdb_index_stats` view
- **Rebuild fragmented indexes** using online rebuild for zero downtime
- **Update statistics** regularly with `ANALYZE`
- **Remove unused indexes** to reduce write overhead

---

## Summary

HeliosDB's indexing system provides:

- **9 index types** covering all major access patterns
- **Online index rebuild** with zero downtime
- **WAL-based durability** for crash recovery
- **Autonomous index recommendations** via the Index Advisor
- **GPU-accelerated vector search** for AI/ML workloads

Choose the right index type for your workload, and leverage HeliosDB's advanced features like partial indexes, expression indexes, and online maintenance for optimal performance.