{"id":8109,"date":"2026-01-26T00:51:46","date_gmt":"2026-01-25T23:51:46","guid":{"rendered":"https:\/\/launix.de\/launix\/?p=8109"},"modified":"2026-01-26T00:51:47","modified_gmt":"2026-01-25T23:51:47","slug":"memcp-january-2025-parallel-sharding-subselects-and-mysql-compatibility","status":"publish","type":"post","link":"https:\/\/launix.de\/launix\/en\/memcp-january-2025-parallel-sharding-subselects-and-mysql-compatibility\/","title":{"rendered":"MemCP January 2025: Parallel Sharding, Subselects, and MySQL Compatibility"},"content":{"rendered":"

Supercharging query performance with automatic parallel execution<\/em><\/p>\n\n\n\n

\n\n\n\n

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.<\/p>\n\n\n\n

Parallel Sharding: From 1 Core to 19 Cores<\/h2>\n\n\n\n

The biggest change this month is automatic parallel sharding<\/strong>. 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.<\/p>\n\n\n\n

The Numbers<\/h3>\n\n\n\n

On a 24-core machine with 100 million rows:<\/p>\n\n\n\n

Query<\/th>Time<\/th>CPU Utilization<\/th><\/tr><\/thead>
COUNT(*)<\/td>4.3s<\/td>1522%<\/td><\/tr>
SUM(col)<\/td>4.4s<\/td>1572%<\/td><\/tr>
ORDER BY LIMIT<\/td>0.9s<\/td>1858%<\/td><\/tr>
Full scan with filter<\/td>5.6s<\/td>1905%<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

That’s 0.04 microseconds per row<\/strong> for aggregations\u2014approaching memory bandwidth limits.<\/p>\n\n\n\n

How It Works<\/h3>\n\n\n\n

When you insert millions of rows at once, MemCP now:<\/p>\n\n\n\n

    \n
  1. Splits the insert into 60,000-row chunks<\/strong><\/li>\n\n\n\n
  2. Creates a new shard for each chunk<\/strong><\/li>\n\n\n\n
  3. Rebuilds full shards in parallel<\/strong> using background goroutines<\/li>\n<\/ol>\n\n\n\n

    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.<\/p>\n\n\n\n

    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<\/strong>, ensuring parallel execution from the start.<\/p>\n\n\n\n

    Subselects: IN, EXISTS, and Scalar Queries<\/h2>\n\n\n\n

    MemCP now supports subselects in expressions\u2014a frequently requested feature.<\/p>\n\n\n\n

    IN and EXISTS<\/h3>\n\n\n\n
    -- Find customers with orders\nSELECT * FROM customers\nWHERE id IN (SELECT customer_id FROM orders)\n\n-- Find products in stock\nSELECT * FROM products\nWHERE EXISTS (SELECT 1 FROM inventory WHERE product_id = products.id)<\/code><\/pre>\n\n\n\n
    Scalar Subselects<\/h3>\n\n\n\n
    Use subqueries directly in your SELECT list:<\/p>\n\n\n\n
    SELECT\n    name,\n    (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count,\n    (SELECT MAX(amount) FROM orders WHERE customer_id = customers.id) AS max_order\nFROM customers<\/code><\/pre>\n\n\n\n
    Scalar subselects return NULL if no rows match, or raise an error if multiple rows would be returned.<\/p>\n\n\n\n
    Derived Tables<\/h3>\n\n\n\n
    Subselects in the FROM clause (derived tables) now work correctly with JOINs:<\/p>\n\n\n\n
    SELECT t.* FROM (\n    SELECT customer_id, SUM(amount) AS total\n    FROM orders\n    GROUP BY customer_id\n) AS t\nLEFT JOIN customers ON customers.id = t.customer_id\nWHERE t.total > 1000<\/code><\/pre>\n\n\n\n
    MySQL and PostgreSQL Compatibility<\/h2>\n\n\n\n
    We’ve added several compatibility features that make migrating from MySQL or PostgreSQL easier.<\/p>\n\n\n\n
    INSERT IGNORE (MySQL)<\/h3>\n\n\n\n
    INSERT IGNORE INTO users (id, email) VALUES (1, 'test@example.com')\n-- Silently ignores if id=1 already exists<\/code><\/pre>\n\n\n\n
    ON DUPLICATE KEY UPDATE (MySQL)<\/h3>\n\n\n\n
    INSERT INTO counters (id, hits) VALUES (1, 1)\nON DUPLICATE KEY UPDATE hits = hits + 1<\/code><\/pre>\n\n\n\n
    The VALUES(column)<\/code> function lets you reference the would-be-inserted value:<\/p>\n\n\n\n
    INSERT INTO users (id, name, updated_at) VALUES (1, 'John', NOW())\nON DUPLICATE KEY UPDATE\n    name = VALUES(name),\n    updated_at = VALUES(updated_at)<\/code><\/pre>\n\n\n\n
    ON CONFLICT (PostgreSQL)<\/h3>\n\n\n\n
    INSERT INTO users (id, email) VALUES (1, 'test@example.com')\nON CONFLICT DO NOTHING\n\nINSERT INTO users (id, email, name) VALUES (1, 'test@example.com', 'John')\nON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name<\/code><\/pre>\n\n\n\n
    ILIKE (PostgreSQL)<\/h3>\n\n\n\n
    Case-insensitive pattern matching:<\/p>\n\n\n\n
    SELECT * FROM users WHERE name ILIKE '%john%'\n-- Matches 'John', 'JOHN', 'johnny', etc.<\/code><\/pre>\n\n\n\n
    Collation Support<\/h3>\n\n\n\n
    Control case sensitivity at the query or column level:<\/p>\n\n\n\n
    -- Case-sensitive comparison\nSELECT * FROM users WHERE name COLLATE 'bin' = 'John'\n\n-- Column-level collation\nCREATE TABLE t (name VARCHAR(100) COLLATE 'general_ci')<\/code><\/pre>\n\n\n\n
    Performance Testing Framework<\/h2>\n\n\n\n
    To catch performance regressions early, we’ve built an auto-calibrating benchmark framework<\/strong> into the test suite.<\/p>\n\n\n\n
    # Run performance tests\nPERF_TEST=1 make test\n\n# Calibrate for your machine\nPERF_TEST=1 PERF_CALIBRATE=1 make test<\/code><\/pre>\n\n\n\n
    The framework:<\/p>\n\n\n\n