Show HN: SQL service for tables with billions of rows or up to 1M columns

Hi All,

March 2023. I was in touch to join a "big data" IT service company. I didn't get the job... Before the team decided I was too old, they introduced me to products I wasn't previously aware of. Since that time, I’ve been building a distributed SQL data engine. I started from 2 previous personal projects. A distributed network filesystem and a peer-to-peer Linux cluster. The goal was to manage "impossible tables": narrow tables with billions of rows or wide tables with columns by the million.

My opinion was and remains this simple idea: you can orchestrate single MariaDB servers to build a global resilient and massively parallel SQL service.

How SynSQL works under the hood:

    Storage Delegation: Physical storage is delegated to MariaDB data brokers to store table data. The data dictionary is also delegated to MariaDB master brokers (mirror managed). 
    Wide tables are vertically sliced into chunks. Each chunk is mapped to a specific broker. You can consider a narrow table as a single chunk table.
    All tables are mandatorily partitioned across nodes using a primary hash key. 
    When a query hits a SynSQL server, it dynamically spawns a tree of Linux processes via fork(). Leaf processes query only the specific brokers holding the requested data and ignore unneeded chunks. Other node processes assemble rows.
    Memory control: Any node at any position in the process tree reads and sends data using a limited 64K buffer. Huge rows are assembled part by part to avoid OOM issues.

High Availability & Resilience:

    A SynSQL Cluster may present up to 32 SynSQL servers sharing a minimal global state. If a node fails, the resilient client automatically reconnects to another active node.
    Metadata: Dictionaries are mirrored across Master Brokers and out-of-sequence resynced at cluster boot. You can also add a master broker on the fly.
    Data layer: Partitions rely on 1 to 3 "reflects" (dedicated MariaDB instances) per partition.

Try the Live Workshops:

No slides. No benchmarks. Run your own queries on two live datasets:

    - Wide Table example multi_omics: 585,010 columns × 10,000 rows. Pick any set of columns and test the extraction speed. https://synsql.com/multiomics_demo.html
    - Deep Table Example: A real-time join across astronomical datasets: Gaia source_data and astrophysical_parameters. 1.8B x 1.5B row join. https://synsql.com/gaia_demo.html

You would like to try it on premise ? Let's get in touch.

Thanks for checking it out, Stéphane

Hi Islambaraka.

Thank you for your question. You have hit on an important design choice. SynSQL relies on a strict fan out across all partitions.

- Wide tables: As you can see on the multi_omic live demo, queries include the hash key. And the hash key is always used in an implicit "order by" clause. Each chunk involved executes a query focused on the columns it contains.

- Deep tables: All data brokers execute the incoming query on their data set (partition).

Why fan out even for 'where pk = 1' ? The engine broadcasts to all partitions. It delegates expression evaluation downstream. It is designed for parallel scans. The overhead is perfectly acceptable for its target: analytical workload.

Best, Stéphane.

Show HN: SQL service for tables with billions of rows or up to 1M columns

Comments