Show HN: SQL service for tables with billions of rows or up to 1M columns
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.
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.
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
Thanks for checking it out, Stéphane