Check the Vortex file format (https://vortex.dev/), if you are interested in a distributed SQL engine then you can check SpiralDB (https://spiraldb.com/), I haven’t used this one personally but they created Vortex.
With genomics, your data is probably write ~once, almost entirely numeric, and is most likely used for single-client offline analysis. This differs a lot from what most SQL databases are optimizing for.
My best experience has been ignoring SQL and using (sparse) matrix formats for the genomic data itself, possibly combined with some small metadata tables that can fit easily in existing solutions (often even in memory). Sparse matrix formats like CSC/CSR can store numeric data at ~12 bytes per non-zero entry, so a single one of your servers should handle 10B data points in RAM and another 10x that comfortably on a local SSD. Maybe no need to pay the cost of going distributed?
Self plug: if you're in the single cell space, I wrote a paper on my project BPCells which has some storage format benchmarks up to a 60k column, 44M row RNA-seq matrix.
Yeah, this is a hard problem, in special because Standard SQL databases only partially implement the relational model, have not good recurse for deal with relations-in-relations and lack of ways to (in user space) build your own storage (all stuff that I dream to tackle).
I think the possible answer is to try to "compress" columns with custom datatypes, it could require to touch part of the innards of sql (like in postgreSQL you need to solve it with c) but is a viable option in many cases where you noted that what you could express in json, for example, is in fact a custom type that could be stored efficiently if there is a way to translate it to more primitive types, then solved that the indexes will work.
The second option is to hide part of the join complexity with views.
Is there really a market for these kinds of relational tables?
I created a system to support my custom object store where the metadata tags are stored within key-value stores. I can use them to create relational tables and query them just like conventional row stores used by many popular database engines.
My 'columnar store database' can handle many thousands of columns within a single table. So far, I have only tested it out to 10,000 columns, but it should handle many more.
I can get sub-second query times against it running on a single desktop. I haven't promoted this feature since everyone I have talked to about it, never had a compelling use for it.
ClickHouse and Scuba address this. The core idea is the data layout on disk only requires the scan to open files or otherwise access data for the columns referenced in that query.
ClickHouse and Scuba are extremely good at what they’re designed for: fast OLAP over relatively narrow schemas (dozens to hundreds of columns) with heavy aggregation.
The issue I kept running into was extreme width: tens or hundreds of thousands of columns per row, where metadata handling, query planning, and even column enumeration start to dominate.
In those cases, I found that pushing width this far forces very different tradeoffs (e.g. giving up joins and transactions, distributing columns instead of rows, and making SELECT projection part of the contract).
If you’ve seen ClickHouse or Scuba used successfully at that kind of width, I’d genuinely be interested in the details.
Scuba could handle 100,000 columns, probably more. But yes, the model is that you have one table and you can only do self-joins and it’s more or less append only and you were only accessing maybe dozens of columns in a single query.
What engine and data format were you using for your experiment?
You mention parquet and spark, but I’m wondering if you tried any of the “Lakehouse” formats that are basically parquet + a metadata layer (ie iceberg). I’d probably at least give Trino or Presto a shot, although I suspect that you’ll have similar metadata issues with those engines.
> With this design, it’s possible to run native SQL selects on tables with hundreds of thousands to millions of columns, with predictable (sub-second) latency when accessing a subset of columns.
In a few words: table data is stored on hundreds of MariaDB servers. Each table is user designed hash key columns(1->32) to manage automatic partitioning.
Wide tables are split in chunks. 1 chunk = the hash key + columns = one MariaDB server. The data dictionary is stored on mirrored dedicated MariaDB servers.
The engine in itself uses a massive fork policy.
In my lab, the k1000 table is stored on 500 chunks. I used a small trick : where I say 1 MariaDB server you can use one database in a MariaDB server. So I have only 20 VmWare Linux servers with 25 database each containing 25 databases.
What are the columns and why are there so many of them? The standard approach is to explode into many tables and introduce joins as you said. Why don’t you want joins?
If they are exploding categorical variables using OHE and storing the columns - that is the wrong thing to do. You should only ever store untransformed feature data in tables. You apply the feature transformations, like OHE, on reading from the tables, as those transformations are parameterized by the data you read (the training data subset you select).
I am speculating here but as it genomics data I assume it's information such as: gene count, epigenetic information (methylation, histones etc)
Once you do 20k times a few post translational modifications you can come to a few columns quickly.
Usually this would be stored in a sparse long form though. So I might be wrong.
From what I understand, Exasol is a very fast analytical database for traditional data warehouses.
My engine doesn't replace a data warehouse; it solves a type of table that data warehouses simply can't handle: tables with hundreds of thousands or millions of columns with an access model that guarantees interactive response times even in these extreme cases.
Check the Vortex file format (https://vortex.dev/), if you are interested in a distributed SQL engine then you can check SpiralDB (https://spiraldb.com/), I haven’t used this one personally but they created Vortex.
If you can drop the “distributed” part, then plug DuckDB (https://duckdb.org/) and query Parquet (out of the box) or Vortex (https://duckdb.org/docs/stable/core_extensions/vortex.html) with it.
With genomics, your data is probably write ~once, almost entirely numeric, and is most likely used for single-client offline analysis. This differs a lot from what most SQL databases are optimizing for.
My best experience has been ignoring SQL and using (sparse) matrix formats for the genomic data itself, possibly combined with some small metadata tables that can fit easily in existing solutions (often even in memory). Sparse matrix formats like CSC/CSR can store numeric data at ~12 bytes per non-zero entry, so a single one of your servers should handle 10B data points in RAM and another 10x that comfortably on a local SSD. Maybe no need to pay the cost of going distributed?
Self plug: if you're in the single cell space, I wrote a paper on my project BPCells which has some storage format benchmarks up to a 60k column, 44M row RNA-seq matrix.
Yeah, this is a hard problem, in special because Standard SQL databases only partially implement the relational model, have not good recurse for deal with relations-in-relations and lack of ways to (in user space) build your own storage (all stuff that I dream to tackle).
I think the possible answer is to try to "compress" columns with custom datatypes, it could require to touch part of the innards of sql (like in postgreSQL you need to solve it with c) but is a viable option in many cases where you noted that what you could express in json, for example, is in fact a custom type that could be stored efficiently if there is a way to translate it to more primitive types, then solved that the indexes will work.
The second option is to hide part of the join complexity with views.
I think this is where Array Databases shine, like https://github.com/TileDB-Inc/TileDB
Is there really a market for these kinds of relational tables?
I created a system to support my custom object store where the metadata tags are stored within key-value stores. I can use them to create relational tables and query them just like conventional row stores used by many popular database engines.
My 'columnar store database' can handle many thousands of columns within a single table. So far, I have only tested it out to 10,000 columns, but it should handle many more.
I can get sub-second query times against it running on a single desktop. I haven't promoted this feature since everyone I have talked to about it, never had a compelling use for it.
ClickHouse and Scuba address this. The core idea is the data layout on disk only requires the scan to open files or otherwise access data for the columns referenced in that query.
Thanks — both are great systems.
ClickHouse and Scuba are extremely good at what they’re designed for: fast OLAP over relatively narrow schemas (dozens to hundreds of columns) with heavy aggregation.
The issue I kept running into was extreme width: tens or hundreds of thousands of columns per row, where metadata handling, query planning, and even column enumeration start to dominate.
In those cases, I found that pushing width this far forces very different tradeoffs (e.g. giving up joins and transactions, distributing columns instead of rows, and making SELECT projection part of the contract).
If you’ve seen ClickHouse or Scuba used successfully at that kind of width, I’d genuinely be interested in the details.
Scuba could handle 100,000 columns, probably more. But yes, the model is that you have one table and you can only do self-joins and it’s more or less append only and you were only accessing maybe dozens of columns in a single query.
Feel free to email if you want to chat more.
What engine and data format were you using for your experiment?
You mention parquet and spark, but I’m wondering if you tried any of the “Lakehouse” formats that are basically parquet + a metadata layer (ie iceberg). I’d probably at least give Trino or Presto a shot, although I suspect that you’ll have similar metadata issues with those engines.
> With this design, it’s possible to run native SQL selects on tables with hundreds of thousands to millions of columns, with predictable (sub-second) latency when accessing a subset of columns.
What is the design?
In a few words: table data is stored on hundreds of MariaDB servers. Each table is user designed hash key columns(1->32) to manage automatic partitioning. Wide tables are split in chunks. 1 chunk = the hash key + columns = one MariaDB server. The data dictionary is stored on mirrored dedicated MariaDB servers. The engine in itself uses a massive fork policy. In my lab, the k1000 table is stored on 500 chunks. I used a small trick : where I say 1 MariaDB server you can use one database in a MariaDB server. So I have only 20 VmWare Linux servers with 25 database each containing 25 databases.
What are the columns and why are there so many of them? The standard approach is to explode into many tables and introduce joins as you said. Why don’t you want joins?
If they are exploding categorical variables using OHE and storing the columns - that is the wrong thing to do. You should only ever store untransformed feature data in tables. You apply the feature transformations, like OHE, on reading from the tables, as those transformations are parameterized by the data you read (the training data subset you select).
Reference: https://www.hopsworks.ai/post/a-taxonomy-for-data-transforma...
I am speculating here but as it genomics data I assume it's information such as: gene count, epigenetic information (methylation, histones etc) Once you do 20k times a few post translational modifications you can come to a few columns quickly.
Usually this would be stored in a sparse long form though. So I might be wrong.
If you want to do that why not just do an EVA pattern or something else that can translate rows to columns?
Try StarRocks. I am totally not affiliated with them but I have investigated them deeply in the past.
That said, I have never seen 1 million columns.
Exasol is another MPP database that easily handles super-wide tables, and does all the distribution across nodes for you.
It used to only be available for big enterprises, but now there is a totally free version you can try out: https://www.exasol.com/personal
From what I understand, Exasol is a very fast analytical database for traditional data warehouses. My engine doesn't replace a data warehouse; it solves a type of table that data warehouses simply can't handle: tables with hundreds of thousands or millions of columns with an access model that guarantees interactive response times even in these extreme cases.