SynSQL: Handling Billion-Row and Million-Column Tables

Handling "impossible tables"—either narrow ones with billions of rows or wide ones with a million columns—usually requires a massive infrastructure overhaul. SynSQL takes a different approach by orchestrating standard MariaDB servers into a massively parallel, resilient SQL service. Instead of reinventing the storage engine, it delegates physical storage to MariaDB data brokers and manages the distribution layer on top.

Technical Architecture

The system manages scale through a few specific mechanisms:

• Vertical and Horizontal Slicing: Wide tables are vertically sliced into chunks and mapped to specific brokers. All tables are partitioned across nodes using a primary hash key.
• Process-Based Query Execution: When a query hits the server, SynSQL spawns a tree of Linux processes via fork(). Leaf processes target only the specific brokers holding the required data, while other nodes handle the assembly.
• Memory Management: To prevent OOM (Out of Memory) crashes during massive row assembly, the system uses a strict 64K buffer for reading and sending data.

Resilience and High Availability

The cluster architecture is designed to avoid single points of failure:

• Node Redundancy: Up to 32 SynSQL servers can share a minimal global state. If one goes down, the client automatically reconnects to an active node.
• Metadata Mirroring: Dictionaries are mirrored across Master Brokers and resynced during cluster boot.
• Data Redundancy: Partitions are backed by 1 to 3 "reflects" (dedicated MariaDB instances).

Real-World Scale

If you're looking for a deep dive into how this handles extreme datasets, there are two live scenarios that demonstrate the capability:

• Wide Table Performance: A dataset with 585,010 columns and 10,000 rows to test extraction speed.
• Massive Joins: A real-time join across astronomical datasets (Gaia source_data and astrophysical_parameters) involving 1.8B x 1.5B rows.

For those building a custom AI workflow or LLM agent that needs to query massive structured datasets without the overhead of a traditional data warehouse, this distributed approach to MariaDB is a practical alternative.

https://synsql.com/multiomics_demo.html
https://synsql.com/gaia_demo.html