Your Analysts Need Billion-Row Queries in Seconds

The request comes in as a row count. It should come in as a byte count. A billion rows of trade identifiers is a laptop problem. A billion rows of policy documents is not.

When the business asks for sub-second analytics on a billion-row table, the instinct is to add an index to the transactional database. You can get away with this for a while, on the queries you already know about. Then the ninth composite index slows the overnight batch past its window, and the trading desk starts raising severity-one tickets because deal tickets will not save.

Forcing a row-oriented engine to aggregate a billion rows is an architectural failure, not a tuning problem. The unit of cost is not the row; it is the byte the engine had to read. A row store pulls entire records off the disk just to read two columns. A columnar engine reads only the columns in the query, compresses them because the values are homogeneous, and skips blocks of data using min-max statistics in the file headers.

The Four Options

There are four honest options for putting a billion-row query in front of an analyst. Each one has a different bill, a different failure mode, and a different opinion about how your analysts work.

Columnstore inside the existing database You add a columnar index to the SQL Server, Oracle, or Postgres instance you already run. There is no new vendor, no data movement, and no new governance review.

This works when the analytical workload is bounded, the queries touch a stable set of columns, and the OLTP side has headroom. It stops working when the analytical workload grows, when the batch window tightens, or when two teams want different refresh cadences on the same table. The index is cheap; the organizational argument about who owns the shared CPU and buffer pool is not.

A provisioned analytical warehouse You move the data to Snowflake, Redshift, or a similar platform and provision compute clusters. You pay for the capacity to be available, which means you pay for idle time so that queries are effectively free at the margin.

This is the right answer when you have unpredictable filters, high concurrency, mixed workloads, and the budget for a platform team. It allows workload isolation. You can put the actuary’s 15-billion-row stochastic batch job on a separate cluster from the client-service dashboard, ensuring the batch does not starve the dashboard at 08:00. The failure mode here is cost overruns from idle capacity or poorly sized clusters, and the political friction of managing shared warehouse resources.

A serverless query engine over object storage You keep the data in Parquet or Iceberg files in S3 or ADLS, and run queries through Athena, BigQuery on-demand, or a similar engine. You pay strictly for the bytes scanned.

This is the cheapest storage option and the most operationally demanding. It works well for intermittent investigation and data already held as files. The failure mode is the invoice. Pay-per-scan prices curiosity. If an analyst joins two large tables on the wrong key without partition filters, or runs a SELECT * on unpartitioned JSON files, the query succeeds and the invoice becomes the incident. You must enforce partition predicates, cap bytes billed per query, and materialize repeated dashboard queries.

Pre-aggregated tables and materialized views You run the heavy billion-row aggregation overnight and store the result as a physical table. When a user runs a matching query, the engine intercepts it and reads the small pre-calculated table.

This is the best answer for the twenty questions asked every day by the same people. It is a terrible answer for the long tail. Pre-aggregation looks like a performance decision but behaves like a product decision. A materialized view is a promise that the question will not change. If a dimension table gets a new row and nobody rebuilds the view, the number goes into a client report before anyone notices.

The Constraints That Decide the Architecture

Before picking an engine, you have to check the constraints that eliminate half the architectures before performance even enters the room.

Concurrency is the SLA nobody benchmarks Single-query latency is a vendor demo. Concurrency at month-end is the reality. Between 08:00 and 09:00, sixty portfolio managers open their dashboards to view overnight exposure. The dashboards fire identical billion-row aggregate queries. If your architecture cannot handle that concurrent load without queuing, the single-query benchmark is irrelevant. Provisioned warehouses and pre-aggregated tables handle this; serverless scan engines will just queue the requests or burn through the budget.

The reconciliation-to-ledger constraint If a number goes in a regulatory filing or a client statement, someone will ask it to tie to the general ledger to the cent, with a query hash the auditor can replay. This kills clever lakehouse designs that cannot prove lineage or handle late corrections cleanly. The reconciliation requirement usually pushes the architecture toward a managed warehouse and away from a raw object-storage lakehouse.

Partitioning is schema design for questions, not data You are predicting what filters next year’s analysts will use. If you partition by trade date, but the risk team queries by instrument ID across all time, the engine still scans every file. Every partition key is a prediction. Pick the one with the highest cardinality that appears in nearly every query, and accept that the others become full scans.

Retention policy is a performance feature The cheapest query is over data you deleted on schedule. Most shops keep everything forever and then pay for it on every scan. Tiering very large history to cold object storage and keeping the hot window in the warehouse is a standard pattern, provided you accept minutes for the cold path.

Making the Decision

The choice depends on the shape of the workload and the organization’s appetite for operational overhead.

• If the schema is stable, filters are predictable, the analytical load is bounded, and you have OLTP headroom, use a columnstore index inside the existing database. It is the shortest political distance.
• If the questions are stable, asked daily, and require high concurrency, use pre-aggregated tables or materialized views.
• If the data is wide, few columns are read, filters are unpredictable, and the data is already in object storage, use a serverless query engine over Parquet or Iceberg files. Budget for the engineering time to manage file layout, compaction, and metadata. If you do not have that team, buy a warehouse and pay the storage premium.
• If you have mixed workloads, high concurrency, and a platform team to manage it, use a provisioned warehouse with workload isolation.
• If the numbers must reconcile to the ledger for regulatory or client-facing reports, choose the architecture that can prove lineage and handle time-travel or corrections natively. This almost always means a warehouse.

The engines can deliver the sub-second response, which leaves the final architecture to be decided by the concurrency, cost, and reconciliation constraints of your specific workload.