title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/chdb/configuration/execution-engine.md)

DataStore can execute operations using different backends. This guide explains how to configure and optimize engine selection.

## Available Engines[​](#engines "Direct link to Available Engines")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Engine Description Best For|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `auto` Automatically selects best engine per operation General use (default)|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `chdb` Forces all operations through ClickHouse SQL Large datasets, aggregations|  |  |  | | --- | --- | --- | | `pandas` Forces all operations through pandas Compatibility testing, pandas-specific features | | | | | | | | | | | |

## Setting the Engine[​](#setting "Direct link to Setting the Engine")

### Global Configuration[​](#global "Direct link to Global Configuration")

```
from chdb.datastore.config import config

# Option 1: Using set method
config.set_execution_engine('auto')    # Default
config.set_execution_engine('chdb')    # Force ClickHouse
config.set_execution_engine('pandas')  # Force pandas

# Option 2: Using shortcuts
config.use_auto()     # Auto-select
config.use_chdb()     # Force ClickHouse
config.use_pandas()   # Force pandas
```

### Checking Current Engine[​](#checking "Direct link to Checking Current Engine")

```
print(config.execution_engine)  # 'auto', 'chdb', or 'pandas'
```

---

## Auto Mode[​](#auto-mode "Direct link to Auto Mode")

In `auto` mode (default), DataStore selects the optimal engine for each operation:

### Operations Executed in chDB[​](#auto-chdb "Direct link to Operations Executed in chDB")

- SQL-compatible filtering (`filter()`, `where()`)
- Column selection (`select()`)
- Sorting (`sort()`, `orderby()`)
- Grouping and aggregation (`groupby().agg()`)
- Joins (`join()`, `merge()`)
- Distinct (`distinct()`, `drop_duplicates()`)
- Limiting (`limit()`, `head()`, `tail()`)

### Operations Executed in pandas[​](#auto-pandas "Direct link to Operations Executed in pandas")

- Custom apply functions (`apply(custom_func)`)
- Complex pivot tables with custom aggregations
- Operations not expressible in SQL
- When input is already a pandas DataFrame

### Example[​](#auto-example "Direct link to Example")

```
from chdb import datastore as pd
from chdb.datastore.config import config

config.use_auto()  # Default

ds = pd.read_csv("data.csv")

# This uses chDB (SQL)
result = (ds
    .filter(ds['amount'] > 100)   # SQL: WHERE
    .groupby('region')            # SQL: GROUP BY
    .agg({'amount': 'sum'})       # SQL: SUM()
)

# This uses pandas (custom function)
result = ds.apply(lambda row: complex_calculation(row), axis=1)
```

---

## chDB Mode[​](#chdb-mode "Direct link to chDB Mode")

Force all operations through ClickHouse SQL:

```
config.use_chdb()
```

### When to Use[​](#chdb-when "Direct link to When to Use")

- Processing large datasets (millions of rows)
- Heavy aggregation workloads
- When you want maximum SQL optimization
- Consistent behavior across all operations

### Performance Characteristics[​](#chdb-performance "Direct link to Performance Characteristics")

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation Type Performance|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | GroupBy/Aggregation Excellent (up to 20x faster)|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Complex Filtering Excellent|  |  |  |  | | --- | --- | --- | --- | | Sorting Very Good|  |  | | --- | --- | | Simple Single Filters Good (slight overhead) | | | | | | | | | |

### Limitations[​](#chdb-limitations "Direct link to Limitations")

- Custom Python functions may not be supported
- Some pandas-specific features require conversion

---

## pandas Mode[​](#pandas-mode "Direct link to pandas Mode")

Force all operations through pandas:

```
config.use_pandas()
```

### When to Use[​](#pandas-when "Direct link to When to Use")

- Compatibility testing with pandas
- Using pandas-specific features
- Debugging pandas-related issues
- When data is already in pandas format

### Performance Characteristics[​](#pandas-performance "Direct link to Performance Characteristics")

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation Type Performance|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Simple Single Operations Good|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Custom Functions Excellent|  |  |  |  | | --- | --- | --- | --- | | Complex Aggregations Slower than chDB|  |  | | --- | --- | | Large Datasets Memory intensive | | | | | | | | | |

---

## Cross-DataStore Engine[​](#cross-datastore "Direct link to Cross-DataStore Engine")

Configure the engine for operations that combine columns from different DataStores:

```
# Set cross-DataStore engine
config.set_cross_datastore_engine('auto')
config.set_cross_datastore_engine('chdb')
config.set_cross_datastore_engine('pandas')
```

### Example[​](#cross-example "Direct link to Example")

```
ds1 = pd.read_csv("sales.csv")
ds2 = pd.read_csv("inventory.csv")

# This operation involves two DataStores
result = ds1.join(ds2, on='product_id')
# Uses cross_datastore_engine setting
```

---

## Engine Selection Logic[​](#selection-logic "Direct link to Engine Selection Logic")

### Auto Mode Decision Tree[​](#decision-tree "Direct link to Auto Mode Decision Tree")

```
Operation requested
    │
    ├─ Can be expressed in SQL?
    │      │
    │      ├─ Yes → Use chDB
    │      │
    │      └─ No → Use pandas
    │
    └─ Cross-DataStore operation?
           │
           └─ Use cross_datastore_engine setting
```

### Function-Level Override[​](#function-override "Direct link to Function-Level Override")

Some functions can have their engine explicitly configured:

```
from chdb.datastore.config import function_config

# Force specific functions to use specific engine
function_config.use_chdb('length', 'substring')
function_config.use_pandas('upper', 'lower')
```

See [Function Config](/docs/chdb/configuration/function-config) for details.

---

## Performance Comparison[​](#performance-comparison "Direct link to Performance Comparison")

Benchmark results on 10M rows:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation pandas (ms) chdb (ms) Speedup|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | GroupBy count 347 17 19.93x|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Combined ops 1,535 234 6.56x|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Complex pipeline 2,047 380 5.39x|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Filter+Sort+Head 1,537 350 4.40x|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | GroupBy agg 406 141 2.88x|  |  |  |  | | --- | --- | --- | --- | | Single filter 276 526 0.52x | | | | | | | | | | | | | | | | | | | | | | | | | | | |

**Key insights:**

- chDB excels at aggregations and complex pipelines
- pandas is slightly faster for simple single operations
- Use `auto` mode to get the best of both

---

## Best Practices[​](#best-practices "Direct link to Best Practices")

### 1. Start with Auto Mode[​](#start-with-auto-mode "Direct link to 1. Start with Auto Mode")