title_wip

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

DataStore has two compatibility modes that control whether output is shaped for pandas compatibility or optimized for raw SQL performance.

## Overview[​](#overview "Direct link to Overview")

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Mode `compat_mode` value Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | **Pandas** (default) `"pandas"` Full pandas behavior compatibility. Row order preserved, MultiIndex, set\_index, dtype corrections, stable sort tiebreakers, `-If`/`isNaN` wrappers.| **Performance** `"performance"` SQL-first execution. All pandas compatibility overhead removed. Maximum throughput, but results may differ structurally from pandas. | | | | | | | | |

### What Performance Mode Disables[​](#what-it-disables "Direct link to What Performance Mode Disables")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Overhead Pandas mode behavior Performance mode behavior|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Row-order preservation** `_row_id` injection, `rowNumberInAllBlocks()`, `__orig_row_num__` subqueries Disabled — row order not guaranteed|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Stable sort tiebreaker** `rowNumberInAllBlocks() ASC` appended to ORDER BY Disabled — ties may have arbitrary order|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Parquet preserve\_order** `input_format_parquet_preserve_order=1` Disabled — parallel Parquet reading allowed|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **GroupBy auto ORDER BY** `ORDER BY group_key` added (pandas default `sort=True`) Disabled — groups returned in arbitrary order|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **GroupBy dropna WHERE** `WHERE key IS NOT NULL` added (pandas default `dropna=True`) Disabled — NULL groups included|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **GroupBy set\_index** Group keys set as index Disabled — group keys stay as columns|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **MultiIndex columns** `agg({'col': ['sum','mean']})` returns MultiIndex columns Disabled — flat column names (`col_sum`, `col_mean`)| **`-If`/`isNaN` wrappers** `sumIf(col, NOT isNaN(col))` for skipna Disabled — plain `sum(col)` (ClickHouse natively skips NULL)| **`toInt64` on count** `toInt64(count())` to match pandas int64 Disabled — native SQL dtype returned|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **`fillna(0)` for all-NaN sum** Sum of all-NaN returns 0 (pandas behavior) Disabled — returns NULL|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Dtype corrections** `abs()` unsigned→signed, etc. Disabled — native SQL dtypes|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Index preservation** Restores original index after SQL execution Disabled|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | **`first()`/`last()`** `argMin/argMax(col, rowNumberInAllBlocks())` `any(col)` / `anyLast(col)` — faster but non-deterministic| **Single-SQL aggregation** ColumnExpr groupby materializes intermediate DataFrame Injects `LazyGroupByAgg` into lazy ops chain — single SQL query | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

---

## Enabling Performance Mode[​](#enabling "Direct link to Enabling Performance Mode")

### Using config object[​](#using-config "Direct link to Using config object")

```
from chdb.datastore.config import config

# Enable performance mode
config.use_performance_mode()

# Back to pandas compatibility
config.use_pandas_compat()

# Check current mode
print(config.compat_mode)  # 'pandas' or 'performance'
```

### Using module-level functions[​](#using-functions "Direct link to Using module-level functions")

```
from chdb.datastore.config import set_compat_mode, CompatMode, is_performance_mode

# Enable performance mode
set_compat_mode(CompatMode.PERFORMANCE)

# Check
print(is_performance_mode())  # True

# Back to default
set_compat_mode(CompatMode.PANDAS)
```

### Using convenience imports[​](#using-imports "Direct link to Using convenience imports")

```
from chdb import use_performance_mode, use_pandas_compat

use_performance_mode()
# ... high-performance operations ...
use_pandas_compat()
```

Note

Setting performance mode automatically sets the execution engine to `chdb`. You do not need to call `config.use_chdb()` separately.

---

## When to Use Performance Mode[​](#when-to-use "Direct link to When to Use Performance Mode")

**Use performance mode when:**

- Processing large datasets (hundreds of thousands to millions of rows)
- Running aggregation-heavy workloads (groupby, sum, mean, count)
- Row order does not matter (e.g., aggregated results, reports, dashboards)
- You want maximum SQL throughput and minimal overhead
- Memory usage is a concern (parallel Parquet reading, no intermediate DataFrames)

**Stay in pandas mode when:**

- You need exact pandas behavior (row order, MultiIndex, dtypes)
- You rely on `first()`/`last()` returning the true first/last row
- You use `shift()`, `diff()`, `cumsum()` that depend on row order
- You're writing tests that compare DataStore output with pandas

---

## Behavior Differences[​](#behavior-differences "Direct link to Behavior Differences")

### Row Order[​](#row-order "Direct link to Row Order")

In performance mode, row order is **not guaranteed** for any operation. This includes:

- Filter results
- GroupBy aggregation results
- `head()` / `tail()` without explicit `sort_values()`
- `first()` / `last()` aggregations

If you need ordered results, add an explicit `sort_values()`:

```
config.use_performance_mode()

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

# Unordered (fast)
result = ds.groupby("region")["revenue"].sum()