MemCP January 2025: Parallel Sharding, Subselects, and MySQL Compatibility

Supercharging query performance with automatic parallel execution

After an intense month of development, MemCP has gained some powerful new capabilities that dramatically improve both performance and SQL compatibility. Here’s what’s new.

Parallel Sharding: From 1 Core to 19 Cores

The biggest change this month is automatic parallel sharding. Previously, bulk-inserted data could end up in a single shard, meaning queries ran on just one CPU core. Now MemCP automatically distributes data across multiple shards for parallel query execution.

The Numbers

On a 24-core machine with 100 million rows:

Query	Time	CPU Utilization
COUNT(*)	4.3s	1522%
SUM(col)	4.4s	1572%
ORDER BY LIMIT	0.9s	1858%
Full scan with filter	5.6s	1905%

That’s 0.04 microseconds per row for aggregations—approaching memory bandwidth limits.

How It Works

When you insert millions of rows at once, MemCP now:

Splits the insert into 60,000-row chunks
Creates a new shard for each chunk
Rebuilds full shards in parallel using background goroutines

During query execution, all shards are scanned concurrently. With 100 million rows split across ~48 shards, you get true parallel execution utilizing all available cores.

Even better: if you haven’t run any filtered queries yet (which normally guide partitioning decisions), MemCP now falls back to automatic round-robin sharding, ensuring parallel execution from the start.

Subselects: IN, EXISTS, and Scalar Queries

MemCP now supports subselects in expressions—a frequently requested feature.

IN and EXISTS

-- Find customers with orders
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders)

-- Find products in stock
SELECT * FROM products
WHERE EXISTS (SELECT 1 FROM inventory WHERE product_id = products.id)

Scalar Subselects

Use subqueries directly in your SELECT list:

SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count,
    (SELECT MAX(amount) FROM orders WHERE customer_id = customers.id) AS max_order
FROM customers

Scalar subselects return NULL if no rows match, or raise an error if multiple rows would be returned.

Derived Tables

Subselects in the FROM clause (derived tables) now work correctly with JOINs:

SELECT t.* FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) AS t
LEFT JOIN customers ON customers.id = t.customer_id
WHERE t.total > 1000

MySQL and PostgreSQL Compatibility

We’ve added several compatibility features that make migrating from MySQL or PostgreSQL easier.

INSERT IGNORE (MySQL)

INSERT IGNORE INTO users (id, email) VALUES (1, 'test@example.com')
-- Silently ignores if id=1 already exists

ON DUPLICATE KEY UPDATE (MySQL)

INSERT INTO counters (id, hits) VALUES (1, 1)
ON DUPLICATE KEY UPDATE hits = hits + 1

The VALUES(column) function lets you reference the would-be-inserted value:

INSERT INTO users (id, name, updated_at) VALUES (1, 'John', NOW())
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    updated_at = VALUES(updated_at)

ON CONFLICT (PostgreSQL)

INSERT INTO users (id, email) VALUES (1, 'test@example.com')
ON CONFLICT DO NOTHING

INSERT INTO users (id, email, name) VALUES (1, 'test@example.com', 'John')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name

ILIKE (PostgreSQL)

Case-insensitive pattern matching:

SELECT * FROM users WHERE name ILIKE '%john%'
-- Matches 'John', 'JOHN', 'johnny', etc.

Collation Support

Control case sensitivity at the query or column level:

-- Case-sensitive comparison
SELECT * FROM users WHERE name COLLATE 'bin' = 'John'

-- Column-level collation
CREATE TABLE t (name VARCHAR(100) COLLATE 'general_ci')

Performance Testing Framework

To catch performance regressions early, we’ve built an auto-calibrating benchmark framework into the test suite.

# Run performance tests
PERF_TEST=1 make test

# Calibrate for your machine
PERF_TEST=1 PERF_CALIBRATE=1 make test

The framework:

Auto-scales row counts to achieve 10-20 second query times
Stores machine-specific baselines in .perf_baseline.json
Measures CPU utilization alongside query time
Runs warmup queries before the measured execution

Output shows everything you need:

✅ Perf: COUNT (4.3s / 13s, 100M rows, 0.04µs/row, 25GB heap, 1522%/2400% CPU)

For bisecting regressions, freeze the row counts with PERF_NORECALIBRATE=1 and let git bisect find the problematic commit.

Internal Improvements

Under the hood, we’ve made significant changes:

Scmer type system rework: The internal value representation was refactored for better performance and GC behavior
FastDict optimization: Hash-based lookups are now transparent to the type system
RDF parser fixes: TTL files with multiple facts and prefix declarations now parse correctly
NonLockingReadMap fix: A subtle race condition in concurrent map access was fixed
LEFT JOIN semantics: Derived tables in LEFT JOINs now correctly preserve WHERE clause behavior

What’s Next

With parallel execution working well, our focus shifts to:

Distributed queries across multiple MemCP nodes
Cost-based query optimization using runtime statistics
Incremental view maintenance for real-time dashboards

Try It Out

git clone https://github.com/launix-de/memcp
cd memcp
go build
./memcp lib/main.scm

Then connect with any MySQL client on port 4321, or use the REST API directly.

MemCP is an open-source, high-performance in-memory database. Star us on GitHub or read the documentation.