> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-resource.md).

# Optimizing for resource

Diagnostic guide for high CPU, memory, or destination-cost usage: symptom table, tuning order, and Iceberg-load memory/volume guidance.

Use this guide when a pipeline runs correctly but consumes too much CPU, memory, or destination capacity.

## Resource symptoms

| Symptom                            | Likely cause                                                                                       | First response                                                                                                                      |
| ---------------------------------- | -------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| Memory spikes during extraction    | Pages or batches are too large for the data shape.                                                 | Reduce `page_size`; inspect nested or wide columns.                                                                                 |
| Memory spikes during load          | Loader batches are too large or `DATA_WRITER__FILE_MAX_BYTES` is set too high for the table shape. | Reduce `loader_file_size` or lower `DATA_WRITER__FILE_MAX_BYTES`; review nested fields.                                             |
| CPU is high and throughput is high | The pipeline may simply be busy.                                                                   | Check whether runtime is acceptable before tuning.                                                                                  |
| CPU is high and throughput is low  | Backpressure, expensive normalization, or retries.                                                 | Check logs and stage-level metrics.                                                                                                 |
| Destination cost rises             | File count, partitions, clustering, or write strategy may be inefficient.                          | Review [Optimize sink datasets](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets.md). |

## Tuning order

1. Lower `page_size` before increasing memory limits.
2. Lower `extract_parallelism` if parallel reads overwhelm memory or source limits.
3. Lower `loader_file_size` or `DATA_WRITER__FILE_MAX_BYTES` if individual load batches are too large.
4. Reduce nested expansion with `max_table_nesting` when the source emits wide nested records.
5. Confirm `incremental_strategy` is appropriate. Rewriting a large table on every run is expensive.
6. Scale compute only after config-level changes are understood.

## Resource Sizing Checklist

* The configured memory limit should be higher than the observed peak with a practical buffer.
* CPU limits should not throttle a run that is otherwise healthy.
* Scheduled pipelines should not overlap unless concurrency is intended.
* Destination write capacity should match the configured load concurrency.

## Lakehouse / Iceberg loads: when resource optimization is safe

Running Lakehouse (Iceberg) loads entirely on Nilus defaults gives the lowest memory footprint, but the time penalty depends heavily on table shape:

* **Wide tables (100+ columns):** The default config is almost as fast as a fully time-tuned run. It is only \~3–11 minutes slower and saves meaningful memory. Resource optimization is a good default here.
* **Narrow tables (few columns, very high row count):** The penalty is severe. Defaults can run 34 minutes to nearly two hours slower than a time-tuned run. **Do not resource-optimize narrow tables unless time genuinely does not matter.**

If a Lakehouse load is being OOMKilled, attach a persistent volume for writable scratch space via `spec.use.volumes` rather than only shrinking buffers. See [Tuning Large Lakehouse (Iceberg) Loads](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-lakehouse-iceberg-loads) for benchmarked configurations.

## Knob reference

The following settings reduce peak memory and CPU usage. For throughput tuning, see [Optimizing for time](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-time.md).

#### `page_size` (source)

| Goes under       | Default                 | Type    |
| ---------------- | ----------------------- | ------- |
| `source.options` | `50000` (rows per page) | integer |

Controls the in-memory writer buffer (rows held before flush). The dominant memory consumer during extraction. Despite the name, it does not control source-side pagination for SQL sources. It controls the writer buffer.

| Symptom                                        | Direction                                  | Why                                                   |
| ---------------------------------------------- | ------------------------------------------ | ----------------------------------------------------- |
| Pipeline is OOMKilled                          | Lower `page_size` (try `25000` or `10000`) | Smaller buffer per page.                              |
| Source is hammered with too many small queries | Raise `page_size` (try `100000`+)          | Fewer, larger reads.                                  |
| Wide rows (1 KB+) blow the runtime memory      | Lower `page_size`                          | Page memory bound is roughly `page_size x row_bytes`. |

Tuning range: `10000` to `150000` is normal.

Raise `page_size` toward `100000` or more only when the source is chunky and memory is ample.

#### `extract_parallelism` (source)

| Goes under       | Default | Type    |
| ---------------- | ------- | ------- |
| `source.options` | `5`     | integer |

Number of concurrent extraction workers per pipeline. Each worker holds its own page buffer. Lower this first if memory spikes during extraction or the source throttles. For throughput tuning, see [Optimizing for time](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-time.md).

Tuning range: `2` to `8` for most pipelines.

#### `loader_file_size` (sink)

| Goes under     | Default                  | Type    |
| -------------- | ------------------------ | ------- |
| `sink.options` | `100000` (rows per file) | integer |

Very large files cut commit overhead but raise peak memory on the load side, especially for Lakehouse/Iceberg, where the runtime buffers each file before committing the snapshot. If load-phase memory spikes, lower it, but watch the small-file trade-off.

#### `LOAD__WORKERS` (env var)

| Goes under                                | Default                      | Type    |
| ----------------------------------------- | ---------------------------- | ------- |
| `spec.use.projection.projections.envVars` | engine default (typically 5) | integer |

More loader workers means more concurrent write buffers in memory. Lower it on tight pod budgets or shared runtimes. (Throughput angle in [Optimizing for Time](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-time.md).)

### Sampling to cut width and startup cost

For testing and validation, limiting the columns extracted can significantly reduce memory and startup time.

**`sql_exclude_columns`** is unset by default and accepts a comma-separated list. It drops specific columns during extraction. Use it to remove large blob or debug columns that are not needed downstream:

```yaml
spec:
  source:
    options:
      sql_exclude_columns: "debug_payload,raw_html"
```

{% hint style="info" %}
**`sql_exclude_columns` versus `type_hints`**

`sql_exclude_columns` removes columns before Nilus reads them, so they never reach the destination. Use it to shrink the dataset.

`type_hints` keeps columns and overrides their inferred type. Use it to correct a string timestamp or wide decimal that you need. It does not reduce extraction volume.
{% endhint %}

**`sql_reflection_level`**: controls how thoroughly Nilus reflects the source schema before extraction.

| Goes under       | Default | Valid values                             |
| ---------------- | ------- | ---------------------------------------- |
| `source.options` | `full`  | `minimal`, `full`, `full_with_precision` |

* `minimal` reflects column names and nullability. Nilus infers types from the data. Use it for the fastest startup and lowest schema-metadata memory.
* `full` reflects column names, nullability, and data types, but not precision or scale.
* `full_with_precision` also reflects type precision, scale, and length, such as `NUMERIC(10,2)` and `VARCHAR(255)`.

Use `minimal` for very wide source tables with 1,000 or more columns. It reduces startup work and memory, but type inference is less precise. Set `type_hints` for columns that need exact typing.

## Related docs

* [Pipeline optimization](https://v2.dataos.info/references/resources/nilus/pipeline-optimization.md)
* [Optimizing for time](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-time.md)
* [Tuning large Lakehouse (Iceberg) loads](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-lakehouse-iceberg-loads.md)
* [Observability](https://v2.dataos.info/references/resources/nilus/observability.md)
* [Checking logs](https://v2.dataos.info/references/resources/nilus/troubleshooting/checking-logs.md)


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-resource.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
