Primary Access Paths vs Secondary Indexes and the Cost of Maintaining Alternate Views

Learning outcomes

Treat every secondary index as maintained state with its own write path, freshness contract, selectivity, failure modes, rebuild cost, and operational evidence—not as a free query-speed switch.

1. Start from the access contract

AtlasMart's catalog authority stores each product under a stable product identifier. A primary access path is the mechanism the system naturally uses to locate a record by its primary key, partition key, or clustered key. A secondary index is an additional structure keyed by some other attribute—category, status, email, price band, timestamp, or search term—so a query can avoid scanning the authoritative data. The important mental model is not “indexes make reads fast.” It is “an index is another maintained representation of the same facts.” PostgreSQL 18's index chapter says indexes speed retrieval but add overall system overhead; distributed systems make that maintenance cost even more visible because the alternate structure may live on another shard or service.

2. A write now has more than one destination

Suppose product p1 changes category and price. The source record must change, every index containing the old keys must remove the old entry, and every index containing new keys must add an entry. Synchronous maintenance keeps those steps inside one acknowledgement boundary when the implementation can do so; this can reduce stale-index windows but adds CPU, memory, storage, locking, and potentially network latency to the write path. Asynchronous maintenance acknowledges the authority first and updates derived indexes later. That lowers coupling on the critical write path but creates a measurable freshness interval and requires retry, ordering, deduplication, and reconciliation.

3. AtlasMart lab: make the mechanism observable

Save the following as lesson1_secondary_indexes.py and run it with python lesson1_secondary_indexes.py. It uses only deterministic in-memory data and mutates no external service.

from collections import defaultdict, deque

source = {}
category_idx = defaultdict(set)
price_band_idx = defaultdict(set)
outbox = deque()

def band(price):
    if price < 50: return "budget"
    if price < 150: return "mid"
    return "premium"

def index_add(product):
    category_idx[product["category"]].add(product["id"])
    price_band_idx[band(product["price"])].add(product["id"])

def index_remove(product):
    category_idx[product["category"]].discard(product["id"])
    price_band_idx[band(product["price"])].discard(product["id"])

def sync_write(product):
    old = source.get(product["id"])
    writes = 1
    if old:
        index_remove(old); writes += 2
    source[product["id"]] = dict(product)
    index_add(product); writes += 2
    return writes

for p in [
    {"id":"p1","category":"footwear","price":120,"name":"Trail Runner"},
    {"id":"p2","category":"footwear","price":80,"name":"Road Runner"},
    {"id":"p3","category":"audio","price":220,"name":"Studio Headphones"},
    {"id":"p4","category":"audio","price":45,"name":"Pocket Earbuds"},
]:
    sync_write(p)

print("primary lookup p3:", source["p3"]["name"])
print("secondary category footwear:", sorted(category_idx["footwear"]))
print("premium band:", sorted(price_band_idx["premium"]))
print("selectivity footwear:", len(category_idx["footwear"]), "/", len(source))

# Synchronous update touches the authority plus two alternate views.
amp = sync_write({"id":"p2","category":"footwear","price":160,"name":"Road Runner Pro"})
print("sync logical writes for p2 update:", amp)
print("p2 now premium:", "p2" in price_band_idx["premium"])

# Deliberately broken dual write: authority commits, index mutation is lost.
old = dict(source["p1"])
source["p1"] = {"id":"p1","category":"clearance","price":99,"name":"Trail Runner"}
print("broken source category:", source["p1"]["category"])
print("stale index still says footwear:", "p1" in category_idx["footwear"])
print("clearance query misses p1:", "p1" not in category_idx["clearance"])

# Repair pattern: record a replayable index event and apply idempotently.
outbox.append((old, dict(source["p1"])))
while outbox:
    before, after = outbox.popleft()
    index_remove(before)
    index_add(after)
print("after replay footwear has p1:", "p1" in category_idx["footwear"])
print("after replay clearance has p1:", "p1" in category_idx["clearance"])

# Reconciliation/backfill derives index state from authority.
rebuilt_category = defaultdict(set)
for p in source.values():
    rebuilt_category[p["category"]].add(p["id"])
print("reconciliation matches:", dict(rebuilt_category) == dict(category_idx))
print("backfill rows read:", len(source))
print("backfill category entries written:", sum(len(v) for v in rebuilt_category.values()))

4. Selectivity and coverage determine usefulness

An index is useful only when its access path filters enough work for the intended query and when the index contains the attributes needed by the plan. Selectivity describes how narrowly an indexed value identifies rows. An index on a nearly unique email address can be highly selective; an index on a Boolean is_active may match most of the table. A low-selectivity index can still help with ordering, covering projections, or conjunctions, but “indexed” does not mean “cheap.” In distributed systems a low-cardinality secondary key can also become a hot index partition. Always inspect result cardinality, index size, write rate, and execution/routing evidence instead of counting index definitions.

5. Backfill is a production workload

Creating an index on existing data requires scanning some or all authoritative records, deriving index entries, writing them, and then catching up with changes that happened during the scan. That is a backfill. An online build therefore competes for disk bandwidth, cache, CPU, replication bandwidth, and compaction/merge capacity. Safe operations define a snapshot or high-water mark, capture concurrent changes, throttle work, measure lag, validate counts/checksums or sampled lookups, and retain rollback. The lab's “rows read” and “entries written” counts are tiny deterministic evidence of that mechanism, not a benchmark.

6. What failure looks like

The dangerous failure is not always an unavailable index. A stale index can return a plausible but wrong answer. If AtlasMart's product moves to clearance but the category index still says footwear, browsing clearance silently omits the item while the old category falsely includes it. The safest architecture assigns authority explicitly, makes index updates replayable, measures index freshness/version lag, and periodically reconciles derived state. If the index itself is authoritative, then its durability/backup/consistency obligations must match that role rather than being hand-waved as “just an index.”

7. Production judgment

Use secondary indexes for recurring alternate predicates with known selectivity and operational value. Budget their write/storage amplification, index-build capacity, backup/restore implications, and tenant isolation: a globally shared index key that omits tenant identity can leak or mix data. Under sharding, know whether the index is local to a partition or globally routable; Lesson 3 examines that distinction. Measure p95/p99 index write latency, query fan-out, index lag, hot keys, build progress, and stale-result incidents. A missing or damaged secondary index should have a defined rebuild/rollback path before the system depends on it for correctness-sensitive decisions.

Wrong approach: dual-write the source and index with no replay contract

Verification, cleanup, and production checklist

Verification is the deterministic program output plus the conceptual checks below. Cleanup is deleting the local lesson1_secondary_indexes.py file; the lab creates no sockets, databases, containers, credentials, indexes, or cloud resources. In production, additionally record authoritative-versus-derived ownership, source/index versions, refresh or projection lag, p95/p99 read/write latency, index size and write amplification, shard/index-key skew, rebuild throughput, ANN recall where applicable, tenant/authorization tests, backup/rebuild evidence, current security advisories, and edition/license constraints before relying on a product-specific feature.

References

Foundational statements use primary research or standards where appropriate. Version-sensitive implementation examples use current official documentation and remain explicitly scoped to the cited product/version.

• PostgreSQL 18 — Indexes — Current relational reference for index benefits and maintenance overhead.
• Elasticsearch — Refresh parameter — Current example of visibility/freshness choices and refresh cost.
• Amazon DynamoDB — Secondary indexes — Current distributed example distinguishing local and global secondary index behavior.
• Elasticsearch 9.5.2 download page — Current version snapshot: 9.5.2, released 2026-08-20; default distribution under Elastic License 2.0.