What does a million-row pivot actually require?

Count the output as well as the input

A million source rows do not uniquely determine the cost of a pivot. The number of distinct row groups, distinct column combinations, measures and aggregate states can matter just as much. A million transactions summarized into a few dozen cells is a different workload from a pivot that creates thousands of columns. Describe both the input and the expected output before comparing implementations.

The supplied CompareGrids pivot scenario uses OwnAccount, Type and Channel as row dimensions, Year and Month as column dimensions, and AmountUSD and FeeUSD as measures. That is a concrete workload, not a universal definition of pivoting. An evaluation for another application should preserve its own cardinalities, aggregation rules and output shape rather than borrowing the row count alone.

Define the business calculation

Specify what each measure means and how it aggregates. Sum, count, minimum and average require different state and have different semantics around nulls. An average of subgroup averages is not generally the average of the underlying records unless weighted appropriately. Verify totals against an independent calculation before considering rendering speed.

Define filters at the correct stage. Filtering source transactions before aggregation differs from filtering aggregate groups afterward. A user interface can expose both, but it must make their meaning clear. Include the active filter definition in exported reports so another person can reproduce the result without guessing from the visible cells.

Clarify currency, units and dates. Summing amounts across currencies without a conversion rule can produce a fast but meaningless answer. Grouping timestamps into months requires an agreed time-zone interpretation. The benchmark's named fields describe its fixture; they do not resolve these domain choices for your application.

Estimate the potential shape

Let R be the number of distinct row-key combinations, C the number of distinct column-key combinations and M the number of measures. A dense result can contain roughly R × C × M measure cells, before totals and labels. This is a planning bound, not a prediction that every implementation allocates that exact matrix. Sparse representations can behave differently.

For an illustrative case, 500 row groups, 24 month columns and two measures imply 24,000 measure positions before totals. If a high-cardinality field expands the column combinations to 10,000, the same row groups and measures imply ten million positions. The source row count has not changed, but the potential output has changed dramatically.

Products can impose different limits or handle missing combinations differently. DuckDB's pivot documentation illustrates how combinations of pivot values can expand generated columns, while its internals describe the need to establish output columns for a query. ^[1] Treat this as a useful database example, not proof that every browser grid uses the same execution strategy.

Separate aggregation from presentation

Measure data acquisition, grouping, aggregate computation, pivot-column construction and visible rendering separately. Keep the complete elapsed time too. A fast grid may be displaying aggregates already computed by a service, while another performs the aggregation locally. Both architectures can be valid, but their timings answer different questions.

Document the data passed to the component. Is it a million raw records, a compact aggregate table or a partially expanded hierarchy? Does the service compute grand totals? Does the browser retain all source records for drill-through? These choices affect memory, transfer and interaction even when the initial pivot looks identical.

Test later actions. Expanding a group, changing a measure, moving a dimension and applying a filter may reuse cached work or trigger a complete recomputation. Initial creation alone cannot describe the analytical experience. Include the sequence analysts actually use and distinguish cached transitions from fresh calculations.

What the snapshot can support

At one million rows, the supplied pivot-creation field contains 19 numerical results, 34 unsupported codes, seven lock or excessive-duration codes, one failure and one dash across 62 entries. These counts describe the tested configurations. An unsupported code should not be generalized into a permanent claim about every edition, extension or server-backed implementation of a product. Inspect supplied results

For illustration, the snapshot records 4,900 ms for FastGrid 3.2 and 3,800 ms for AG Grid 36.1 at one million rows. At five million rows it records 25,000 ms and 94,000 ms respectively. The change in relative behavior across these two sizes is a reason to test the intended scale, not a basis for predicting every intermediate or larger workload.

These observations are not new measurements, and the source selects relatively fast runs rather than reporting distributions. The test harnesses were not supplied with the archive. Consequently, the figures support a bounded comparison within the supplied scenario; they do not establish statistical confidence, production latency or a causal explanation for the difference.

Avoid a universal extrapolation

Do not multiply a one-million-row result by five and present it as a measured five-million-row result. Algorithms, allocation behavior, caches and output shape can change the scaling relationship. Even when two measured points look approximately linear, the evidence does not establish that behavior beyond the observed range.

Likewise, do not infer memory from elapsed time. A fast aggregation can retain substantial intermediate state, and a slower implementation may use a different memory strategy. The supplied dataset does not include memory measurements. Add peak and retained-memory observations to a new evaluation if browser capacity is a selection criterion.

Use synthetic variations to identify sensitivity. Hold row count constant while changing the number of groups, then hold cardinality constant while increasing rows. Add skewed distributions in which a few groups contain most records. This helps distinguish sensitivity to input volume from sensitivity to output shape without pretending to reverse-engineer an undocumented implementation.

Decide where the pivot belongs

Client-side pivoting can support rapid exploration of a fully available dataset, including local changes. It also asks the browser to retain and process the required information. Evaluate that choice on the lowest supported device class and with the rest of the application present. A dedicated benchmark page can leave more resources available than a production workspace.

Server-side aggregation can keep the browser's result smaller and centralize query rules. It introduces request latency, service cost and a contract for dimension changes and drill-through. Define caching and consistency carefully. A cached subtotal and a newly fetched detail view should not silently refer to different snapshots when the workflow requires reconciliation.

A hybrid design may preaggregate common dimensions and retrieve detail on demand. Verify which questions the preaggregated data can answer without losing information. Some aggregates combine cleanly; others need additional state or access to source values. Document these limits so the interface does not offer operations whose apparent result has different semantics.

Build correctness fixtures first

Create a small fixture with manually verifiable totals, nulls, duplicate keys, negative values and boundary dates. Check row totals, column totals and grand totals independently. Include empty combinations and filtered groups. This makes it easier to catch semantic errors before a million-row test turns every discrepancy into a difficult investigation.

Then generate a larger deterministic fixture with known aggregate checks. Compare the component output with an independent reference computation. Record numeric precision and rounding rules. A displayed rounded total may differ from a sum of rounded displayed cells, so decide which interpretation the product promises and test it explicitly.

Exercise edits and refreshes. Change one source record and verify every affected aggregate, including collapsed groups and totals outside the viewport. Test removal and movement between groups. Incremental update correctness is a separate capability from initial aggregation, and a successful first render does not establish it.

Record the evidence in a compact workload manifest

For each pivot run, retain the source fixture identifier, row count, distinct counts for every dimension, measure definitions and expected totals. Record the number of output rows and columns after filtering, along with whether totals and collapsed groups were included. This lets another engineer distinguish a genuinely equivalent rerun from a superficially similar million-row demonstration.

Attach the configuration and the sequence of analytical actions. If a later test changes the order of dimension selection or benefits from cached aggregation, label that scenario separately. Keeping the manifest with both successful and failed runs makes capacity limits understandable and helps a vendor reproduce the exact workload that needs improvement.

Before sharing the recommendation, ask a colleague who did not build the evaluation to follow the evidence. They should be able to identify the workload, reproduce the important checks and understand why an alternative was rejected. Any missing assumption they uncover belongs in the record. This review is especially valuable when a compact score or headline has become more familiar than the measurements behind it. Preserve the detailed evidence even when the stakeholder summary is brief, so later changes can be assessed against the original decision rather than against memory.

Keep the result usable

A technically successful pivot can still produce an unusable table. Thousands of columns may be difficult to navigate, explain or export. Offer sensible dimension choices, limits or aggregation guidance based on the product's tasks. An explicit message about excessive output is better than allowing an operation to appear frozen without context.

Measure the time to a useful view and the time to complete all required work. If computation runs asynchronously, verify cancellation and progress. Users should be able to change their mind without an obsolete result replacing the current query. Make failures distinguishable from an empty but valid pivot.

A defensible pivot capacity claim therefore includes row count, dimension cardinalities, measures, output size, execution location, device class and supported interactions. The supplied snapshot provides one valuable scenario within that space. Use it to ask better questions and shortlist implementations, then validate the analytical workload your users will actually perform.