# Performance Tuning for Large Datasets in sdata: 9 Optimization Strategies

> Boost sdata performance with large datasets. Discover 9 optimization strategies for efficient data handling and faster queries. Learn WAL mode, indexing, and batch inserts now.

- Repository: [lepy/sdata](https://github.com/lepy/sdata)
- Tags: performance
- Published: 2026-03-06

---

**Enable WAL mode, index your JSON keys, and use batch inserts with pagination to handle millions of records efficiently in sdata.**

sdata is a scientific data management library that stores arbitrary JSON payloads and tabular data in SQLite-based backends (`JSONSQLiteStore`, `JSON1SQLiteStore`) and HDF5 (`FlatHDFDataStore`). When datasets grow into millions of records, specific architectural knobs in the source code become critical for maintaining query speed, write throughput, and system stability.

## Enable Write-Ahead Logging (WAL) for Concurrent Access

The SQLite backends in sdata enable **Write-Ahead Logging (WAL)** by default to avoid costly full-file locks and support concurrent readers during writes.

In [`sdata/iolib/jsonsqlitestore.py`](https://github.com/lepy/sdata/blob/main/sdata/iolib/jsonsqlitestore.py) (lines 82-84) and [`sdata/iolib/json1sqlitestore.py`](https://github.com/lepy/sdata/blob/main/sdata/iolib/json1sqlitestore.py) (lines 96-98), the connection initializes with:

```python
self.conn.execute("PRAGMA journal_mode = WAL")

```

Keep WAL enabled for large insert and update workloads. If your underlying filesystem does not support WAL (for example, NFS), fall back to `DELETE` mode and accept slower concurrency. Monitor the `-wal` file size periodically and prune it with `VACUUM` during idle windows:

```python
store.conn.execute("VACUUM")

```

## Optimize SQLite Pragmas for Speed vs Durability

sdata configures SQLite pragmas to trade a small amount of durability for significantly faster commits. The same initialization blocks in [`jsonsqlitestore.py`](https://github.com/lepy/sdata/blob/main/jsonsqlitestore.py) and [`json1sqlitestore.py`](https://github.com/lepy/sdata/blob/main/json1sqlitestore.py) set:

```python
self.conn.execute("PRAGMA synchronous = NORMAL")
self.conn.execute("PRAGMA temp_store = MEMORY")

```

**Synchronous = NORMAL** is acceptable for most scientific workloads where occasional crashes are tolerable; set to `FULL` only for mission-critical data. **Temp store in memory** prevents disk-based temporary files during large sorts and joins, which accelerates pagination queries (`fetch_page`) and complex `LIKE` operations.

## Index Generated Columns for O(log N) Lookups

SQLite 3.31+ supports generated columns that store extracted JSON fields as real columns, converting linear scans to **O(log N)** index lookups.

The `JSON1SQLiteStore._ensure_table` method (lines 10-15 in [`json1sqlitestore.py`](https://github.com/lepy/sdata/blob/main/json1sqlitestore.py)) defines these columns, while `create_index` (lines 242-248) builds the indexes:

```python
store = JSON1SQLiteStore(
    'big.db',
    index_keys=['_sdata_name', '_sdata_class'],
    unique_index_keys=['_sdata_sname']
)

```

Always index `_sdata_*` attributes (e.g., `_sdata_class`, `_sdata_name`). Use `unique=True` for natural keys to prevent duplicate scans during insertion.

## Batch Insert Records to Reduce Overhead

The `insert_many` method in both stores uses SQLite's `executemany` to reduce round-trip overhead. In `JSONSQLiteStore` (lines 102-110) and `JSON1SQLiteStore` (lines 81-87), this implementation allows thousands of rows to be inserted in a single transaction.

Wrap bulk operations in a transaction context for atomicity:

```python
with store.transaction():
    store.insert_many(huge_list_of_dicts)

```

This pattern minimizes journal file growth and maximizes write throughput compared to individual `insert` calls.

## Paginate Queries to Minimize Memory Usage

Loading millions of rows into memory crashes most workflows. The `fetch_page` method in `JSONSQLiteStore` (lines 332-373) and `JSON1SQLiteStore` (lines 52-78) implements **LIMIT/OFFSET** pagination to fetch only the required slice.

Combine pagination with indexed filters to keep the query planner efficient:

```python
page = store.fetch_page(
    limit=1000,
    offset=0,
    key='_sdata_class',
    op='=',
    value='Experiment'
)

```

Iterate through large result sets using a loop that increments `offset` by `limit` until the page returns empty.

## Implement Periodic Cleanup with TTL

The `delete_expired` method in `JSONSQLiteStore` (lines 890-915) and `JSON1SQLiteStore` (lines 19-24) removes old records without scanning the entire table. This prevents unbounded table growth in long-running scientific applications.

Run cleanup periodically (for example, nightly) to maintain manageable table sizes:

```python
deleted = store.delete_expired(datetime.utcnow() - timedelta(days=30))
print(f"Removed {deleted} old records")

```

## Use Compression for Large Text Blobs

`JSONSQLiteStore` supports optional zlib compression for payloads when disk I/O dominates over CPU cost. The `_serialize` and `_deserialize` methods (lines 152-160 in [`jsonsqlitestore.py`](https://github.com/lepy/sdata/blob/main/jsonsqlitestore.py)) check `self.compression` to compress large text blobs (for example, raw XML) before storage.

Enable compression for very large text payloads to reduce storage footprint and I/O time, at the cost of increased CPU usage during serialization.

## Choose HDF5 for Dense Tabular Data

When the dataset is primarily a table requiring efficient columnar slicing, switch to **FlatHDFDataStore**. According to [`sdata/iolib/hdf.py`](https://github.com/lepy/sdata/blob/main/sdata/iolib/hdf.py) (lines 40-49), the `put` method writes `metadata`, `table`, and `description` to separate HDF5 groups, storing pandas DataFrames column-wise.

Use this backend when you need fast random access for tabular data:

```python
hdf = FlatHDFDataStore('big.h5')
hdf.put(my_sdata_object)            # fast columnar write

data = hdf.get_data_by_uuid(uuid)   # efficient read

```

## Summary

- **Enable WAL mode** in SQLite backends to support concurrent readers and avoid file locks.
- **Set pragmas** to `synchronous = NORMAL` and `temp_store = MEMORY` for faster commits and sorts.
- **Index all searchable JSON keys**, especially `_sdata_class` and `_sdata_name`, using `JSON1SQLiteStore`.
- **Use `insert_many`** wrapped in a transaction for bulk loads.
- **Paginate with `fetch_page`** instead of loading millions of rows into memory.
- **Run `delete_expired`** periodically to prune stale records.
- **Enable compression** for large text blobs in `JSONSQLiteStore`.
- **Select `FlatHDFDataStore`** when storing massive pandas DataFrames requiring columnar access.

## Frequently Asked Questions

### How do I handle concurrent writes in sdata without locking errors?

Enable **Write-Ahead Logging (WAL)** mode, which is the default in both `JSONSQLiteStore` and `JSON1SQLiteStore`. This mode allows readers to access the database while writes are in progress, avoiding the "database is locked" errors common in standard SQLite DELETE journal mode. If your filesystem does not support WAL (such as NFS), you must fall back to `DELETE` mode and serialize access.

### What is the fastest way to insert millions of records into sdata?

Use the **`insert_many`** method wrapped in a transaction context. According to the implementation in [`sdata/iolib/json1sqlitestore.py`](https://github.com/lepy/sdata/blob/main/sdata/iolib/json1sqlitestore.py) (lines 81-87), this method uses SQLite's `executemany` to batch inserts in a single round-trip. Always wrap the call in `with store.transaction():` to ensure atomicity and minimize journal overhead.

### Should I use SQLite or HDF5 for large scientific datasets?

Use **SQLite** (specifically `JSON1SQLiteStore`) when you need to query individual JSON attributes or filter by metadata fields like `_sdata_class`. Use **HDF5** (`FlatHDFDataStore`) when your data is a dense pandas DataFrame that you read or write as whole-table slices, as HDF5 stores data column-wise for efficient array access. The choice depends on whether your workload is record-oriented or array-oriented.

### How can I prevent memory exhaustion when querying large sdata stores?

Use **`fetch_page`** with an indexed `key` parameter instead of `fetch_all`. This method implements SQL `LIMIT` and `OFFSET` clauses to return only the requested slice. Always ensure the filter column is indexed (for example, `_sdata_class`) so the query planner uses an O(log N) lookup rather than a full table scan.