Projection Design for Alternate Sort Orders on Denormalized Tables

Projection design for denormalized tables is about giving different query patterns the physical layouts they need. One sort key cannot make every important read fast. Narrow, independently sorted projections resolve that conflict without duplicating the full table, but their read benefits justify the design only when storage, write cost, and ongoing maintenance are part of the decision.

Why a single sort key on a denormalized table is an architectural constraint, not just a performance nuisance

Consider a team that builds a wide denormalized table for a per-user dashboard, then adds an event-type query that forces a full scan. The first dashboard benefits from rows sorted by user_id. The second needs a different column first in the sort order. Pre-joining dimension data removed query-time joins, but it also concentrated several access patterns into one physical layout. Reads by user, event type, time window, and geography now compete for that layout.

The conflict is built into the storage design. In the MergeTree engine family, incoming parts are sorted by the table’s ORDER BY key, and background merges preserve that ordering within parts. A filter on the leading sort column can use a sparse index or search sorted data instead of scanning everything, but unrelated predicates cannot do the same. Secondary indexes offer an escape, but they can undermine compression and vectorized execution. The C-Store paper addresses this tension by storing overlapping projections in different orders.

What projections are and how they resolve the sort-order conflict

A projection is a physically materialized, independently sorted copy of a subset of a table’s columns. It is not simply a lookup structure. Each projection is a columnar data structure that has its own compression and can run vectorized scans. An alternate sort order therefore preserves the execution model that made the column store useful in the first place. The C-Store paper formalizes this as a sorted subset of table attributes, allowing multiple projections with different column sets and orders.

Vertica adapts that model so appropriately sorted projections can serve in place of traditional indexes. A user-oriented projection and an event-oriented projection can store the same logical data in different physical orders without being separate tables that applications have to query. The query planner chooses a suitable projection based on the query’s filters and GROUP BY structure. Query writers still address one logical table. They can use the alternate layout without routing queries by hand or rewriting them.

That separation also extends to maintenance in systems with native projection support. Writes to the base table update its projections automatically, while a separately refreshed materialized view waits for a scheduled refresh job. The engine still has to store and maintain each physical representation.

How sort-order choice inside a projection directly controls GROUP BY and join costs

LinkedIn engineer Patrice Borne wrote about a join that used two different fields, where one projection could not give the query both sort orders it needed. Without a second projection, the query took 19 seconds, about twice as long as the redesigned version that used two targeted projections. The missing layout had a measurable cost. This example shows why copying the base table’s key into another projection is not enough. The alternate order has to remove work from the query path that needs it.

When the GROUP BY columns come first in the projection’s sort order, other sort columns can follow and the groups still stay together. The engine can then aggregate in a single pass over sorted data, avoiding an intermediate sort and its potential spill to disk. Having the right columns is not sufficient. Putting an unrelated column first can prevent that same projection from providing the order the aggregation requires.

Distributed execution also calls for a choice of segmentation, which sets how rows are assigned to nodes. Segmentation and sorting solve different problems. A co-located join can avoid a network shuffle even when its local data needs sorting, while correctly sorted data may still require a network exchange if the inputs are segmented differently. In the projection model, segmentation is specified per projection and can differ from its sort key. Treating the two as interchangeable leaves either local processing or data movement unaddressed.

Which columns to include in a projection — and which to leave out

A useful alternate order rarely needs every column in the denormalized row. A narrow projection has the columns its query family needs and leaves out the other attributes. This is where duplication becomes more manageable. The C-Store and Vertica model distinguishes an all-column super-projection, which provides a fallback, from narrower projections tailored to particular reads. Narrow copies store fewer columns and can improve compression and make scans faster.

That subset should be derived from the workload. Frequent queries reveal the columns needed for WHERE predicates, GROUP BY clauses, and SELECT lists. Omitting a required column defeats the proposed coverage, while including unrelated columns increases the physical copy without serving that family. Vertica’s Automatic Vertical-Database Design patent uses the same foundation, taking schema and training queries as inputs to enumerate candidate column subsets and sort orders.

Encoding belongs in this decision, but as a supporting consideration. Low-cardinality, dictionary-encoded columns can compress more aggressively when identical values sit together, which a leading sort position provides. That links encoding choices to physical order. It does not replace the need to align the projection with its queries, because compact storage alone cannot supply a missing grouping or join order.

The real storage cost of multiple projections, and the conditions under which it is worth paying

Every projection stores a full physical copy of its selected columns. More projections and wider projections take more storage, and every write must keep those copies up to date. The cost is real and needs a budget. Column pruning reduces that burden, but it does not turn projections into metadata-only objects. A design that adds an alternate layout for every conceivable query can lose the economic advantage it sought.

The trade becomes favorable when repeated scans or runtime sorts cost more than maintaining a targeted layout. At hundreds of gigabytes or larger, scanning a table for a query that a narrow projection could serve in milliseconds can impose a much greater latency and resource burden. Frequency strengthens that case. An access pattern used all the time gets a different payoff from its physical copy than a query run now and then to explore the data, so table size alone cannot decide the choice.

Shared storage changes the cost of replication as well. Vertica’s Eon Mode makes projections stored on shared storage available across compute, removing the original architecture’s need for fault-tolerance-driven per-node buddy projection replication. Maintenance findings also need their proper scope. Bei et al.’s Live Aggregate Projections paper, published at IEEE Big Data 2019, reports dependency-discovery overhead orders of magnitude below the latency improvement from a single workload execution. The documented 90% individual query latency improvement for Live Aggregate Projections on flattened tables supports that specific trade.

Where full projection duplication is infeasible, space-filling curves offer a partial alternative. Z-Order, also called Morton order, and Hilbert curves encode several dimensions into one approximate sort key. DuckDB’s multi-column approximate sorting demonstrates the compromise, bringing related rows closer without guaranteeing the contiguity of a dedicated projection. Precision is the trade-off. This approach fits lake architectures that use columnar files and have no projection-style maintenance, while projections still have their place in engines that support them.

How to identify which access patterns on a denormalized table warrant their own projection

To apply these principles, start by collecting high-frequency, high-latency queries against the table. Group them by leading filter predicate and GROUP BY structure, then identify the minimum columns each group needs. The workload is the starting evidence. A customer-facing dashboard, real-time risk engine, or observability pipeline can justify a dedicated layout through sustained use, concurrency, and growing data. A one-off analytical query generally does not meet the same bar.

For a worked example, consider a denormalized event table containing user_id, event type, timestamp, and geography. One query family filters by user_id and groups by user, while another filters by event type and groups by event type. A third filters by event type but groups by event type and geography. The first family needs a user-leading layout. The other two can be candidates for one projection ordered by event type and then geography, because their grouping columns form compatible leading prefixes.

For each candidate, record the leading predicate, grouping columns, and required output columns. Then test whether the proposed sort order satisfies the grouping requirement. Shared filters do not guarantee shared layouts. If the grouping orders conflict, separate projections may be needed, and their read benefit must justify the additional writes. Likewise, the two-field join pattern calls for a choice between targeted projections and a runtime sort on the path with lower priority.

Projection maintenance on writes and the operational discipline that keeps projections honest

Every write to the base table must also reach its projections. Native maintenance makes this automatic, but each added projection consumes write capacity and can reduce throughput. MergeTree-family engines must also use background merges to keep the data in order within each projection’s parts. Vertica’s Live Aggregate Projections patent describes automatic maintenance and dependency tracking for insert and update paths, without requiring manual refresh invocations.

Flattened tables introduce a separate freshness obligation. When source dimension values change, the pre-joined columns need their own refresh before those changes can show up in projections. Vertica’s Flattened Tables patent specifies on-demand refresh of denormalized columns, followed by automatic responsive refresh of Live Aggregate Projections built on the flattened table. Keeping a projection synchronized with its base table is not the same as keeping that table synchronized with its underlying dimensions.

Projection count should be reviewed as part of schema design and write-capacity planning. Adding a projection to a large production table can be online yet resource-intensive, and its continuing maintenance may exceed previously provisioned throughput. Deployments commonly have one super-projection and between zero and three narrow projections. That range reflects a write-amplification budget. The discipline is to keep each alternate layout accountable to both its workload and its ongoing maintenance cost.

Translating projection design into a repeatable schema review process for denormalized tables

A repeatable review starts with the query workload, groups it into access patterns, and defines the minimum projection set that covers the important groups. It then validates grouping order, checks distribution needs, and sets a write-amplification budget before committing to projection count. Coverage is only half the decision. Each intended query shape should explicitly receive a dedicated layout, share a compatible one, or accept a scan because its frequency and scale do not justify more storage. Making that choice before production avoids having to rush to add projections over hundreds of terabytes of data already in place.

Vertica’s Database Designer does projection-design work automatically, but it still helps to understand how it ranks the choices. A Vertica patent describes ranking candidate sort or segmentation columns by the number and weight of queries that use them. This gives manual review a defensible basis. A column’s prominence in the schema is less useful evidence than its role in frequent, important queries, especially when automated recommendations are unavailable or unexpected.

Agentic and high-concurrency workloads make that evidence harder to establish before launch because agents can probe data in novel patterns at high parallelism. The response is not speculative projection growth. Teams should instrument queries from the first production period and add targeted layouts as access patterns stabilize. Complete logging of every agent query supplies the record needed for this analysis. Isolated, read-only compute paths and that logging let platform teams use evidence from the workload to judge the demand that agents create without having to guess.

A projection is a schema-level declaration that an access pattern deserves its own physical order. Treating it that way makes storage, query performance, and write capacity parts of the same design decision.