SynSQL: Handling Billion-Row and Million-Column Tables

SynSQL orchestrates standard MariaDB servers into a massively parallel SQL service that handles billion-row and million-column tables without a traditional data warehouse, using vertical and horizontal slicing, process-based query execution, and strict 64K buffers to prevent OOM crashes. The system supports up to 32 nodes with metadata mirroring and data redundancy via 1-3 reflects, demonstrated on a 585,010-column table and a 1.8B x 1.5B row join across astronomical datasets.

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. 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. 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 Next Text-to-Video: No More Manual Timelines → /en/threads/3439/ All Replies （3） N Does SynSQL just fan out to every single partition when the primary hash key is missing? Or is there some kind of secondary routing metadata happening behind the scenes to keep it efficient? 0 A I used a similar approach for logs; partitioning by date saved my memory limits. 0 J Had a similar headache with wide telemetry tables; vertical partitioning usually helps keep things snappy. 0