How to query tabular data that has thousands of columns I'm working in a module that at some point we will need to make queries to a tabular data that has ~25K columns and ~70K rows. Right now this data is persisted as a CSV file.

We've tried a few approaches to address it, but all of them failed or became too slow because of the number of columns we have. When we tried these approaches with a different file that has much more rows but not as many columns (~50M rows vs. 40~ columns), it work perfectly.

Having said that, I'd like to know if some of you has gone through this problem or has any idea on how to address it. There's no restrictions on technology or anything. I'm not listing what we've tried so far intentionally, in order not to bias your ideas. But if you'd like to know, I'll be happy to share our experiments with you.
25k columns? Hmm, that smells like some serious denormalisation. Do you have many sets of repeating, numbered columns, perhaps something like purchase_date_1, purchase_date_2, purchase_date_3 ,, purchase_date_99 or value_january, value_february .. value_december? Maybe there are recurring structured blocks such as residential_address, delivery_address, postal_address etc.

The way to handle these is to declare a separate table for each block and put each value in its own row. The above then becomes e.g.

TABLE Purchases
  CSV_row_key
  Purchase_date
  Ordinal      -- to capture the 1..99 if required; probably should be omitted


Transforming columns to rows is sometimes referred to as an unpivot.

Declare indexes on the keys and other predicate columns. Effective indexing is a separate subject for which we'd need the precise table design and the query.

It may be possible to pre-calculate aggregate values from these normalised tables. Running a batch job after loading and writing aggregates to a further set of tables is one approach. Materialized views is another.

1.75 billion values is large but not outrageous by current standards. Any of the usual RDBMSs on reasonable hardware should be able to handle it. Unfortunately the devil is often in the detail. It is possible to defeat the best software with contorted queries and convoluted schema. To avoid that we would need much more specific information.