> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need. # LMDB Loader The LMDB loader writes query results into [LMDB](http://www.lmdb.tech/doc/), a key-value database stored in a directory on your disk and read through memory mapping. It needs no server, and reading a key takes microseconds. Choose it when one program, on one machine, needs to look rows up by key quickly, like a block by its number. Each row is one entry, under a key you choose. ## Prerequisites * **The Python SDK**, [installed](/sdks/python#installation), and the LMDB library it uses, `lmdb`: :::code-group ```bash [pip] pip install lmdb ``` ```bash [uv] uv add lmdb ``` ::: * **A running Amp with the Quickstart's dataset**, `acme/eth_mainnet`. [Quickstart](/get-started/quickstart) sets it up. ## Load data This script names a connection to an LMDB database in `./blocks.lmdb`, `local_lmdb`, then loads the Quickstart's 100 blocks into it, keyed by block number: ```python [load_lmdb.py] from amp import Client client = Client('grpc://localhost:1602') client.configure_connection( 'local_lmdb', 'lmdb', {'db_path': './blocks.lmdb', 'key_column': 'block_num'}, ) result = client.sql( 'SELECT block_num, timestamp, hash, gas_used FROM "acme/eth_mainnet".blocks' ).load(connection='local_lmdb', destination='blocks', read_all=True) print(result) ``` ```bash python load_lmdb.py ``` * `db_path` is the directory of the database. The loader creates it if it doesn't exist. * `key_column` makes each row's `block_num` its key: block 24,000,099 is under the key `24000099`. * `read_all=True` loads the whole result at once and returns one `LoadResult`. [Loaders](/sdks/python/loaders) covers the other ways to load, and the options `load()` takes. Each entry's value is its row, in Arrow's IPC stream format, with the row's schema. Read a block back: ```python [read_lmdb.py] import lmdb import pyarrow as pa env = lmdb.open('./blocks.lmdb', readonly=True) with env.begin() as txn: print(f'{txn.stat()["entries"]} entries') value = txn.get(b'24000099') row = pa.ipc.open_stream(value).read_all() print(row.select(['block_num', 'timestamp', 'gas_used']).to_pylist()) ``` :::terminal ```bash python read_lmdb.py ``` ``` 100 entries [{'block_num': 24000099, 'timestamp': datetime.datetime(2025, 12, 13, 0, 25, 59, tzinfo=datetime.timezone.utc), 'gas_used': 10751338}] ``` ::: Storing each row with its schema takes more space than the row's values alone, a few kilobytes per row. Keep the columns you load to the ones you need. ## Keys The loader builds each row's key in the first of these ways that the configuration sets: 1. **`key_column`**: the row's value in that column, as text, or as it is for binary columns. `'block_num'` gives `24000099`. 2. **`composite_key_columns`**: the row's values in those columns, as text, joined with `:`. `['block_num', 'gas_used']` gives `24000099:10751338`. 3. **`key_pattern`**: a pattern, where `{table}` is replaced with the destination, `{index}` with the row's position in its batch, and `{column}`, for any column of your query, with the row's value in it. `'{table}:{block_num}'` gives `blocks:24000099`. The default pattern, `{id}`, needs an `id` column. When a pattern names a column your query doesn't return, the loader logs `Key pattern failed: 'id'. Using hash-based key.` for every row, and keys it with `{destination}:` and an MD5 hash of the row's values instead. `destination` only appears in keys: every destination of a connection shares one database. To keep them apart, put them in [named databases](/sdks/python/loaders/lmdb#configuration) with `database_name`, or use a key pattern starting with `{table}:`. ## Configuration The third argument of `configure_connection()`: | Key | Type | Default | Description | | ----------------------- | --------------- | --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `db_path` | string | `'./lmdb_data'` | The directory of the database, like `'./blocks.lmdb'`. | | `map_size` | integer | 10 GiB | The most bytes the database can hold, like `100 * 1024**3` for 100 GiB. Loads fail once it's full. The database's file may show this size, but only uses what's written. | | `database_name` | string | none | A named database to load into, like `'blocks'`, instead of the main one. The loader creates it if it doesn't exist. | | `create_if_missing` | boolean | `True` | Whether to create the directory and the named database if they don't exist. | | `key_column` | string | none | The column whose value is each row's key (see [Keys](/sdks/python/loaders/lmdb#keys)), like `'block_num'`. | | `composite_key_columns` | list of strings | none | The columns whose values, joined with `:`, are each row's key, like `['block_num', 'log_index']`. | | `key_pattern` | string | `'{id}'` | The pattern of each row's key, when neither `key_column` nor `composite_key_columns` is set, like `'{table}:{block_num}'`. | | `transaction_size` | integer | `10000` | How many rows the loader writes in each write transaction, like `1000`. | | `sync` | boolean | `True` | Whether to write to disk at the end of each transaction. `False` loads faster, but a crash can lose the last transactions. | | `writemap` | boolean | `True` | Whether to write through the memory map, which is faster. | | `readahead` | boolean | `False` | Whether the operating system reads ahead of what's requested. Off suits lookups by key. | | `max_dbs` | integer | `100` | The most named databases the directory can hold. | | `max_readers` | integer | `126` | The most threads and processes that can read the database at once. | | `max_spare_txns` | integer | `1` | How many read transactions LMDB keeps ready for reuse. | | `lock` | boolean | `True` | Whether LMDB locks the database. Leave it on unless nothing else accesses the database. | | `compression` | string | none | Not used. | The `state` and `resilience` keys, which every loader takes, are on [Loaders](/sdks/python/loaders). LMDB lets one program write to a database at a time, and many read it at once. Don't load into the same database from two programs. ## Load modes Pass `mode` to `load()` ([Loaders](/sdks/python/loaders#load-modes) explains the modes every loader shares): * **`append`** (the default) adds the rows, without replacing existing keys. A row whose key already exists isn't loaded: the loader logs `Completed with 100 errors`, for example, and when no row of a batch could be loaded, the load fails with `Failed to load any rows. Errors: Key already exists: 24000000; …`. * **`overwrite`** deletes every entry of the database, then writes the rows, replacing existing keys. It deletes the entries of every destination in the database, not only this one's. Use it only with `read_all=True`: without it, the loader deletes the entries before each batch, so only the last batch stays, and the last batch is often empty. `upsert` and `merge` aren't supported: they fail with `Unsupported mode LoadMode.UPSERT`. ## Streaming and reorgs (Unstable) With `stream=True`, `load()` runs a [streaming query](/querying/streaming-queries) and keeps loading new rows as Amp extracts new blocks (see [Streaming](/sdks/python/streaming)). Streamed rows get one more column, `_amp_batch_id`, which identifies the block range each row came from. This stream loads into a new database, `./live.lmdb`, since `./blocks.lmdb` already has these blocks' keys: ```python client.configure_connection('live_lmdb', 'lmdb', {'db_path': './live.lmdb', 'key_column': 'block_num'}) results = client.sql( 'SELECT block_num, timestamp, hash, gas_used FROM "acme/eth_mainnet".blocks' ).load(connection='live_lmdb', destination='blocks', stream=True, with_reorg_detection=False) for result in results: print(result) ``` `with_reorg_detection=False` works around a problem with reorg detection, explained in [Known issues](/sdks/python/streaming#known-issues). When Amp reports a [reorg](/get-started/concepts/reorgs), the loader looks up the batches it loaded from the reorged blocks onward, then reads every entry of the database, in one write transaction, and deletes those whose `_amp_batch_id` matches. That takes longer the more entries the database has. The loader remembers which batches it loaded in memory, for as long as the `load()` call runs. When a stream restarts, it starts from the beginning of the query, and can't undo reorgs of the batches it loaded before. In `append` mode, the rows it loaded before already have their keys, so the restarted stream fails with `Key already exists`. ## Parallel loads (Unstable) :::warning In a [parallel load](/sdks/python/parallel-streaming) into LMDB, set `num_workers=1`. With several workers, each opens the same database, which LMDB allows only once in a program. One partition usually loads, and the others fail with `The environment '…' is already open in this process` or `Too many errors (101), aborting`. Their rows are missing, and the loop gets a failed result for each, not an exception. Loading the first blocks with one worker doesn't help. The partitions that succeeded are already in the database, and loading their keys again fails, so delete the database's directory before you load the range again with one worker. ::: ## Troubleshooting ### `Failed to load any rows. Errors: Key already exists: 24000000; …` Every row of a batch has a key the database already has, like when you load the same blocks twice. Load other rows, choose keys that tell rows apart, or replace the entries with `mode='overwrite'` and `read_all=True`. ### `Key pattern failed: 'id'. Using hash-based key.` A warning: the key pattern names a column your query doesn't return, here `id`, which the default pattern uses. Set `key_column`, `composite_key_columns`, or a `key_pattern` with your query's columns (see [Keys](/sdks/python/loaders/lmdb#keys)). ### `MDB_MAP_FULL: Environment mapsize limit reached` The database holds as many bytes as `map_size` allows. Set a larger `map_size`. Every later row of the same transaction then fails with `MDB_BAD_TXN: Transaction must abort, has a child, or is invalid`. ### `The environment '…' is already open in this process` Several workers of a parallel load opened the same database. Delete the database's directory, then load with one worker, as [Parallel loads](/sdks/python/loaders/lmdb#parallel-loads) says. ### `Too many errors (101), aborting` More than 100 rows of a batch failed, and the loader logs why as `Error processing row`. With `MDB_BAD_TXN: Transaction must abort, has a child, or is invalid` or `MDB_PAGE_NOTFOUND: Requested page not found`, in a parallel load, several workers used the database at once: see [Parallel loads](/sdks/python/loaders/lmdb#parallel-loads). With `'utf-8' codec can't decode byte`, your keys are binary, and the database already has them: load other rows, or delete the database's directory.