Skip to content

Prejoined Read Table

Precomputed read table — instantiates Access-Optimized Redundant Representation

Precomputes a specific multi-table join into one wide, flat table, so a hot read that used to join several tables becomes a single indexed scan against a fixed latency target.

A Prejoined Read Table takes one recurring, expensive join — the same tables stitched together the same way on every request — and materializes its result as a single wide, flat table, one row per output row with all the joined columns already denormalized in. The normalized schema keeps writes clean by spreading a listing across many tables; the prejoined table pays that stitching cost once, at write or refresh time, so the read path stops paying it on every request. Its defining feature is that it is row-preserving: unlike an aggregate rollup, it does not summarize — it reproduces the same rows a query would return, just pre-stitched — and it is built to hit an explicit read-latency target for one known, hot access path.

Example

A job board's listing page shows, for each opening, the job title and salary, the company name and logo, and the city and country — data normalized across jobs, companies, and locations. At the site's traffic, the three-way join on every page load and every filter is the bottleneck, and p99 latency drifts past the 200 ms target the team set for the page.

They build a job_listing_read table: one row per job, carrying the job's own fields plus the company name and logo URL and the resolved city and country, all flattened in. The listing query becomes a single indexed scan of one table with no joins — comfortably inside the latency target — and filtering by city is now a column predicate rather than a join-then-filter. The company name is duplicated into every one of that company's job rows; that redundancy is the deliberate price paid to retire the join, and it is why a company rename has to fan out to refresh the copies rather than update one row.

How it works

  • Fix the target access path. Identify the one hot read whose join dominates cost, and the latency or throughput target it must meet; the table is built for that path, not as a general store.
  • Materialize the join, preserving rows. Compute the join once and store its rows verbatim in a single wide table, denormalizing the borrowed columns inline so no join is needed at read time.
  • Map every copied column to its source. Record which base table and column each denormalized field came from, so refresh knows exactly what to update when a source row changes — the redundancy stays governed, not ad hoc.

Tuning parameters

  • Width — how many joined columns to carry inline. Wider tables answer more of the read without follow-up lookups but duplicate more data and cost more to refresh on source change.
  • Indexing of the flat table — which columns get indexes for the target filters and sorts. More indexes speed varied read shapes but slow refresh writes and grow storage.
  • Refresh coupling — whether copied columns update synchronously with the source write or asynchronously behind it. Synchronous keeps the table tight to source at the cost of write latency; asynchronous is cheaper but admits bounded staleness.
  • Scope of duplication — how many high-cardinality source columns to flatten in. Flattening a frequently-changing parent field (a company name) into millions of rows maximizes read speed but maximizes refresh fan-out on change.

When it helps, and when it misleads

Its strength is direct and narrow: it retires a specific, dominating join for a specific, high-volume read, converting a multi-table stitch into one flat scan that predictably meets a latency target. When one access path is both hot and join-heavy, nothing is simpler or more effective.

Its cost is the shadow of denormalization. Because a source value is duplicated across many rows, an update must fan out to all of them, and if that fan-out is incomplete or lags, the table exhibits an update anomaly[1] — the same company shows two different names across its listings. The tidy flat table also tempts scope creep: widen it to serve a second, different read and it becomes an unfocused mega-table that is expensive to refresh and fits no access path well. And it is easy to write to the flat table directly "just this once," quietly turning a subordinate read copy into a rogue second source. The discipline is to keep it single-purpose, driven by one target access path, refreshed by a governed mapping from the authoritative source, and never written to directly.

How it implements the components

Prejoined Read Table supplies the read-optimized artifact and its performance intent — the join-elimination side, not the freshness or aggregation machinery:

  • access_path_performance_target — it exists to hit a stated latency or throughput target for one hot read, and its whole design is justified by that target.
  • derivation_and_duplication_mapping — it records which source table and column each flattened field is copied from, the map refresh follows to keep the duplication correct.
  • redundant_representation_specification — the wide, row-preserving flat table is the specification of the redundant read form for that access path.

It does not aggregate or model the precompute economics (performance_benefit_and_cost_model) — that is Summary or Rollup Table — and it does not keep itself current (freshness_rule, synchronization_rule), which is Scheduled Incremental Refresh.

References

[1] An update anomaly is the classic hazard of denormalization from relational normalization theory: when a fact is duplicated across many rows, an incomplete update leaves copies disagreeing. A prejoined table accepts this hazard deliberately and must answer it with governed, complete refresh — not by pretending the duplication is free. ↩