MergenDB

MergenDB is an embedded columnar database engine and query execution runtime designed to handle analytical workloads on resource-constrained hardware. It is written in pure Python with zero external dependencies, allowing it to run out of the box on low-end virtual servers, Raspberry Pis, embedded devices, and developer workstations without compiling C extensions.

Traditional embedded databases like SQLite store data row-by-row. When you query 2 columns out of a 30-column table across 10 million rows, a row-oriented database still reads all 30 columns off the disk. MergenDB stores data column-by-column in compressed blocks, pruning unrequested columns from disk reads and skipping entire blocks via ZoneMap indexing.

Key Features

• Strictly Bounded Memory Footprint: Streams data in configurable chunks (4,096–8,192 rows). Memory usage stays under 15–20 MB RAM regardless of whether your dataset is 100 MB or 100 GB.
• Adaptive Columnar Encodings: Automatically evaluates and applies the best encoding per block:
  • Bit-Packing: Packs 8 booleans into a single byte.
  • Delta / Frame-of-Reference (FoR): Compresses sequential IDs, integers, and timestamps.
  • Dictionary Encoding: Replaces repeated strings (status, city, category) with 1–2 byte integers.
  • Run-Length Encoding (RLE): Compresses contiguous identical values into (count, value) pairs.
  • Secondary Zlib Compression: Fast C-level streaming compression for cold data blocks.
• ZoneMap Indexing: Stores min_value, max_value, and null_count metadata per column chunk. If your query filters WHERE age > 65 and a block's maximum age is 40, MergenDB skips reading that block entirely.
• Robust SQL / phpMyAdmin Importer: Line-by-line streaming parser for raw MySQL/phpMyAdmin SQL dumps with multiline statements, escaped characters, and schema autodetection.
• Interactive REPL & Network Server: Comes with a MySQL-like CLI shell and a built-in HTTP query server for remote queries from any language.
• Zero Third-Party Dependencies: Only uses Python standard library modules (array, struct, zlib, csv, http.server, sqlite3).

Installation

pip install mergendb

Requires Python 3.8 or later.

Quickstart (Python API)

1. Creating and Querying Tables

from mergendb import MergenDB
from mergendb.core.schema import Schema, ColumnDef
from mergendb.core.types import DataType
from mergendb.storage.writer import FileWriter

schema = Schema([
    ColumnDef("id", DataType.INT64),
    ColumnDef("city", DataType.STRING),
    ColumnDef("temp", DataType.FLOAT64),
    ColumnDef("active", DataType.BOOL)
])

# Write columnar data
with FileWriter("telemetry.mgdb", schema, block_size=4096) as writer:
    writer.write_rows([
        [1, "Istanbul", 24.5, True],
        [2, "Ankara", 18.2, False],
        [3, "Izmir", 28.0, True],
        [4, "Istanbul", 26.1, True],
    ])

# Query using standard SQL
result = MergenDB.query('SELECT city, temp FROM "telemetry.mgdb" WHERE temp > 20.0;')
print(result.display())

2. Pipeline Queries (MergenQL)

MergenDB also supports a pipe-delimited query syntax inspired by Unix pipes:

query = '''
FROM "telemetry.mgdb"
| WHERE temp > 20.0 AND active == True
| COMPUTE temp_f = (temp * 1.8) + 32.0
| SELECT city, temp, temp_f
| SORT temp DESC
| LIMIT 5
'''

result = MergenDB.query(query)
print(result.display())

Streaming Import & Export

MergenDB provides streaming importers and exporters that feature a real-time progress bar with throughput (rows/sec) and ETA:

Import SQL Dumps (phpMyAdmin, mysqldump)

mergen
mergen> IMPORT SQL database_dump.sql mytable;

Or via Python:

from mergendb.io.importer import DataImporter

# Streams line-by-line under 15 MB RAM, regardless of dump size (tested on 10M+ rows)
DataImporter.from_sql_dump("huge_dump.sql", "mytable.mgdb")

Import SQLite Databases

DataImporter.from_sqlite("legacy.sqlite3", "users.mgdb", table_name="users")

Import CSV Files

DataImporter.from_csv("logs.csv", "logs.mgdb")

Export Tables to CSV, SQL, or JSONL

mergen> EXPORT mytable.mgdb TO CSV "backup.csv";
mergen> EXPORT mytable.mgdb TO JSON "backup.jsonl";
mergen> EXPORT mytable.mgdb TO SQL "backup.sql";

Command-Line Interface (REPL)

Launch the interactive shell:

mergen

mergen> SHOW TABLES;
+------------------+------------+------------+
| Table Name       | Total Rows | Size (KB)  |
+------------------+------------+------------+
| users.mgdb       | 1,250,000  | 8,412.30   |
| logs.mgdb        | 5,400,000  | 24,190.50  |
+------------------+------------+------------+

mergen> USE users;
mergen> SELECT id, name, city WHERE city = 'Istanbul' LIMIT 10;
mergen> EXPLAIN SELECT id, name WHERE age > 60;
mergen> DESCRIBE users;
mergen> BENCHMARK users;

HTTP Network Server

MergenDB includes an HTTP server that allows any application (Node.js, Go, PHP, Rust, C#) to execute queries over JSON:

# Start server on port 8765
mergendb-server --port 8765

Send a query using curl:

curl -X POST http://localhost:8765/query \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT id, city, temp FROM \"telemetry.mgdb\" WHERE city = \"Istanbul\" LIMIT 5;"}'

Response:

{
  "success": true,
  "columns": ["id", "city", "temp"],
  "rows": [
    [1, "Istanbul", 24.5],
    [4, "Istanbul", 26.1]
  ],
  "stats": {
    "execution_time_ms": 0.85,
    "rows_returned": 2,
    "blocks_scanned": 1,
    "blocks_skipped": 12,
    "bytes_read": 512
  }
}

Benchmarks & Technical Characteristics

Benchmarked on an Intel i7 machine with 100,000 telemetry records (12 mixed numeric/string columns):

• Exact Filter Throughput: ~50,000,000 rows/sec (single core)
• Substring (LIKE '%term%') Throughput: ~10,000,000 rows/sec (single core)
• SQL Import Throughput: ~70,000 rows/sec streaming parser on commodity hardware

Running Tests

MergenDB includes a self-contained test suite covering columnar storage, compression encodings, SQL/SQLite/CSV importers, and the query planner. You can run it from any directory:

# Using the CLI
mergen test

# Or via Python module
python -m mergendb.tests

License

MIT License. See LICENSE for details.

Developed by Uğur Türker Kebeci.