The Harmless Whale Query That Will Eat Your Database

In the world of relational database architecture, few performance killers are as insidious or as misunderstood as data skew. A recent empirical study utilizing ExoBench—an MCP server that provisions real Postgres and Azure SQL instances to execute synthetic workloads—has revealed a stark, often overlooked reality of enterprise software engineering: every major database engine hates data skew, and each one hates it in a fundamentally different way.

The findings dismantle a common assumption among developers—that a query optimized on uniform, synthetic test data will scale predictably into production. In a benchmark featuring 50,000 customers, 500,000 orders, and 900,000 order items, a skewed foreign key relationship (where 10 enterprise "whale" customers own 80% of all orders) caused an identical three-table join to run roughly 1,000 times slower than it did under uniform data distribution.

Yet, the true insight of the benchmark lies not in the performance degradation itself, but in the remedies. The fixes that rescue Postgres are completely ineffective on SQL Server, and vice-versa, proving that "bad estimates" do not share universal cures.

Chronology of the Benchmark: From Naive Test to Production Shock

1. The Baseline Setup and the Naive Control

The investigation began with a simple, innocuous e-commerce schema mirroring a typical multi-tenant architecture:

-- Schema
CREATE TABLE customers (id INT PRIMARY KEY, name TEXT, tier TEXT);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, total NUMERIC);
CREATE TABLE order_items (id INT PRIMARY KEY, order_id INT, quantity INT, unit_price NUMERIC);

The standard reporting query was equally unremarkable:

SELECT c.tier, COUNT(*), SUM(oi.quantity * oi.unit_price)
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE c.tier = 'enterprise'
GROUP BY c.tier;

When evaluated against a uniform test dataset—where orders were spread evenly across all 50,000 customers—the query executed in roughly 1 millisecond on both Postgres and SQL Server. The optimizer chose a clean cascading nested loop, and cardinality estimates were accurate to within a factor of two.

However, when reality intervened—assigning 80% of orders to just 10 enterprise "whale" customers while splitting the remaining 20% among the other 49,990 users—the identical query blew out to 1,572ms on Postgres and 2,340ms on SQL Server. The schema, the indexes, and the row counts remained completely unchanged; only the distribution shifted.

2. The Shared Diagnosis: Join-Crossing Correlation

ExoBench’s plan analysis revealed why both engines walked into the same architectural wall. Both query planners consult single-column histograms to estimate row counts. For orders.customer_id, the histogram showed 500,000 orders spread across ~50,000 customers, averaging roughly 10 orders per customer.

When the query filtered for the 10 enterprise customers, the planners calculated: 10 customers × 15; orders = ~150 rows. Acting on this premise, both planners selected a cascading nested loop—an optimal strategy for 150 rows, but a disaster when those 10 specific customers actually owned 400,000 orders, driving the top join volume past 720,000 rows.

Crucially, neither engine’s statistics engine could detect the correlation between a customer’s tier and their order volume because that correlation lives across a join boundary. The estimators were not buggy; they were faithfully extrapolating from per-table column statistics that physically lacked the capacity to see join-crossing skew. Both planners underestimated the top join by a factor of roughly 3,000x.

Supporting Data: The Divergence of Remedies

Faced with identical diagnostic profiles, the two engines demanded completely disparate intervention strategies.

Postgres: The Power of a Free One-Liner

On Postgres, the bottleneck was entirely algorithmic—the choice of a nested-loop strategy operating over a massive fan-out. By simply toggling a single session-level setting to revoke the nested loop (SET enable_nestloop = off), Postgres dropped execution time from 1,572ms down to 309ms—a fivefold speedup requiring zero schema alterations.

The optimizer’s row estimates remained just as incorrect, but the forced hash join scanned order_items sequentially across parallel workers, rendering the cardinality miscalculation harmless. Buffer hits collapsed by a factor of 230.

SQL Server: The Quagmire of Access Paths

Applying similar hint-based strategies on SQL Server proved futile. Forcing a naive hash join pushed execution time up to 5,266ms, as the bad row estimate poisoned the memory grant, triggering severe TempDB spills. Furthermore, the hint left a 720,000-row key lookup intact.

A battery of optimizer hints (DISABLE_OPTIMIZER_ROWGOAL, FORCE ORDER, MERGE JOIN, and legacy cardinality estimation) hit a stubborn 2,000ms performance floor. SQL Server’s primary bottleneck was not the join algorithm itself, but an access-path constraint: fetching columns via per-row key lookups on a non-covering index.

To break the floor, SQL Server required three interlocking levers simultaneously:

1. A covering index (order_items(order_id) INCLUDE (quantity, unit_price)) to eliminate key lookups.
2. Batch mode execution to maximize scan efficiency.
3. A hash join to process the stream without row-by-row probing.

Stacking these three elements brought SQL Server down to 330ms.

Official Responses and Theoretical Perspectives

Database architects have long debated the purity of "fixing the cause" versus applying tactical workarounds. The textbook structural solution to data skew is table partitioning—splitting high-volume rows into dedicated tables (e.g., enterprise_orders and regular_orders) so that per-table statistics reflect honest distributions.

When researchers tested this structural split using a UNION ALL construction:

• On SQL Server, the structural split became the ultimate winner at 193ms, outperforming even the multi-lever indexing strategy because honest statistics allowed the optimizer to naturally eliminate lookups and derive a clean parallel batch-mode plan.
• On Postgres, the structural split yielded 391ms, landing behind the free one-line session setting (309ms). For Postgres, undertaking a complex schema redesign to split tables represented unnecessary "cargo-culting" of a SQL Server solution onto an engine whose only true flaw was a suboptimal default join operator.

Implications for Enterprise Architecture

The empirical evidence yielded by ExoBench underscores several critical takeaways for enterprise engineering teams:

1. Synthetic Test Data is Dangerous: Relying on Faker-generated uniform datasets during staging and QA provides a false sense of security, masking structural performance cliffs that only manifest when production whale accounts enter the system.
2. Asymmetric Engine Behavior: Diagnostic parity does not imply remediation parity. Postgres suffered from an algorithmic bottleneck correctable via execution parameters, whereas SQL Server suffered from an access-path bottleneck requiring physical indexing and batch-mode processing.
3. The Cost of Schema Redesign: While eliminating statistical lies via table splitting is theoretically pure, engineers must weigh the operational overhead of schema partitioning against the simplicity of engine-specific execution tuning.

Ultimately, data skew remains one of the final frontiers of automated query optimization. Until cost-based optimizers natively account for join-crossing correlation, database administrators must abandon universal heuristics and measure their workloads live across multiple engines to understand the true nature of their performance walls.