---
id: PERF04
pillar: performance-efficiency
title: PERF 4. How do you design data access for the actual query pattern?
description: Data access is where the real constraint usually sits, and where a wrong decision is most expensive to correct. Model for the queries the workload issues.
status: draft
services: [postgresql-flex, object-storage, kubernetes-engine]
sidebar:
  order: 13
  label: Data design
source_url: "https://framework.stackit.cloud/architecture/pillars/performance-efficiency/perf-04-data-design/"
source_file: "docs/architecture/pillars/performance-efficiency/perf-04-data-design.mdx"
---

Most performance problems that survive a round of optimization are data access problems. The
application is waiting, and what it is waiting for is a query, a volume or an object store.

This question is also where the most expensive mistakes live, because a data model is the hardest
thing in a system to change once it has data in it and consumers depending on its shape.

## Best practices

- [`PERF 4.1`](/architecture/pillars/performance-efficiency/perf-04-data-design/#perf-41-model-for-the-queries-the-workload-actually-issues) Model for the queries the workload actually issues
- [`PERF 4.2`](/architecture/pillars/performance-efficiency/perf-04-data-design/#perf-42-index-deliberately-and-account-for-what-each-index-costs) Index deliberately, and account for what each index costs
- [`PERF 4.3`](/architecture/pillars/performance-efficiency/perf-04-data-design/#perf-43-choose-the-storage-tier-from-the-access-pattern) Choose the storage tier from the access pattern
- [`PERF 4.4`](/architecture/pillars/performance-efficiency/perf-04-data-design/#perf-44-bound-every-result-set-and-eliminate-per-row-round-trips) Bound every result set and eliminate per-row round trips

---

## PERF 4.1 Model for the queries the workload actually issues

**Risk if not established:** Medium

A model designed for conceptual tidiness and a model designed for the access pattern are different
models. The first produces elegant schemas that require six joins to answer the most common
question.

Start from the queries. Which questions does the workload ask, how often, and which of them sit on
a ranked flow from [`REL 2.2`](/architecture/pillars/reliability/rel-02-critical-flows/#rel-22-rank-flows-by-the-consequence-of-failure-rather-than-by-traffic-volume). Design so that the frequent ones are cheap, and accept that the rare
ones may be expensive.

Denormalization is a legitimate tool and a permanent cost. A duplicated value is faster to read
and is now two places that can disagree, which is a correctness obligation rather than a
performance detail. Where you take it, write down why, so that [`PERF 9.3`](/architecture/pillars/performance-efficiency/perf-09-performance-lifecycle/#perf-93-remove-optimizations-whose-justification-has-expired) can revisit it when the
justification expires.

Partitioning and sharding decisions belong here rather than later. Both are chosen at creation,
both are difficult to change afterwards, and both are wrong if the key does not match how the data
is actually queried. A partition key that produces one hot partition has added complexity and no
throughput.

**On STACKIT.** The choice of data service is the first decision this question makes, and it is
worth making on the access pattern rather than on familiarity. A relational engine, a document
store, a key-value store and an object store answer different question shapes, and the
<LinkChip href="https://docs.stackit.cloud/products/">product documentation</LinkChip> lists what is managed.

Where an analytical query pattern sits on top of data held elsewhere,
<LinkChip href="https://docs.stackit.cloud/products/data-and-ai/dremio/">Dremio</LinkChip> is the managed SQL engine, which
is a different answer from adding capacity to a transactional database that was never shaped for
those queries.

The platform does not model your data. What it constrains is the ceiling of the option you picked,
which is [`PERF 3.4`](/architecture/pillars/performance-efficiency/perf-03-service-selection-and-sizing/#perf-34-know-where-the-ceiling-of-the-option-you-chose-is), and the point at which a larger instance stops being the answer and the model
has to change.

**Tradeoffs.** **Operational Excellence.** Denormalized and partitioned models are harder to
reason about, harder to migrate and harder to keep consistent. **Reliability.** Duplicated data is
duplicated state, with the failure modes [`REL 4.2`](/architecture/pillars/reliability/rel-04-redundancy/#rel-42-make-state-redundant-and-know-where-each-data-set-is-anchored) describes.

**Verify.** List the five most frequent queries on your critical flow. For each, how many joins,
how many rows examined, and was the model designed with that query in mind?

---

## PERF 4.2 Index deliberately, and account for what each index costs

**Risk if not established:** Medium

Indexes are the cheapest large performance win available and they are not free. Each one is
maintained on every write, occupies storage, and consumes memory that would otherwise cache data.

The two failure modes are opposite and both common. **Missing indexes** produce full scans that
get slower as the table grows, which is the classic case of a system that was fine at ten thousand
rows. **Accumulated indexes** are added one per incident, never removed, and eventually the write
path carries a dozen of them, most unused.

Derive them from the query plans rather than from intuition about which columns look important.
Composite index column order matters and is not obvious; an index that is not used by the query it
was created for is pure cost.

Review usage periodically. Most engines can report which indexes are never used, and those are
straightforwardly removable. [`PERF 9.2`](/architecture/pillars/performance-efficiency/perf-09-performance-lifecycle/#perf-92-re-examine-data-access-as-the-data-grows) is where this belongs in the lifecycle.

Remember that indexes change behaviour as data grows. A query plan chosen when a table was small
may be wrong when it is large, and the engine may not re-plan without help.

**On STACKIT.** Index design is a property of your schema rather than of the platform. What the
platform supplies is visibility: <LinkChip href="https://docs.stackit.cloud/products/databases/postgresql-flex/reference/observability-metrics-in-postgresql-flex/">observability metrics for PostgreSQL
Flex</LinkChip>
documents what the service exposes, which is what turns index review from guesswork into a
measurement.

The engine's own tooling for query plans and index usage is the other half, and it is the same
tooling as anywhere else that engine runs. That portability is worth noting in the other direction
too: it is one of the things [`SOV 10`](/architecture/pillars/sovereignty/sov-10-open-interfaces/) counts as reversibility.

**Tradeoffs.** **Performance Efficiency**, against itself: every index trades write throughput for
read latency. On a write-heavy path that trade can be net negative, which is why it is measured
rather than assumed. **Cost Optimization.** Indexes consume storage and the memory that would have
cached data.

**Verify.** How many indexes exist on your largest table, and how many were used in the last week?
When was that last checked?

---

## PERF 4.3 Choose the storage tier from the access pattern

**Risk if not established:** Medium

Storage performance is a dimension that gets chosen by default more often than any other, because
it is not visible in the vCPU and RAM figures that dominate a sizing conversation.

Three properties distinguish the choice, and workloads care about them differently. **IOPS** for
many small operations, which is what a transactional database consumes. **Throughput** for large
sequential transfers, which is what a batch job or a backup consumes. **Latency** for
synchronous single operations, which is what a user-facing write consumes.

A workload sized on capacity alone gets whichever performance the default tier provides, and that
is the right answer only by accident.

Separate the tiers by use where it pays. Transaction logs, data files, backups and temporary space
have different profiles, and putting all of them on one tier means over-provisioning for the
average.

**On STACKIT.** <LinkChip href="https://docs.stackit.cloud/products/runtime/kubernetes-engine/basics/storage/storage-classes/">Kubernetes Engine storage
classes</LinkChip>
expose several performance tiers, with a default that applies when a claim does not name one. That
default is the case worth checking: a workload that never specified a class is running on whatever
the default provides, which may be more or less than it needs in either direction.

Volume expansion is supported, so capacity is adjustable. Whether the performance tier of an
existing volume can be changed is a separate question and belongs in the reversibility sort from
[`PERF 3.2`](/architecture/pillars/performance-efficiency/perf-03-service-selection-and-sizing/#perf-32-establish-which-sizing-decisions-are-reversible-before-you-make-them).

For managed databases the equivalent choice is the performance class, which states maximum IOPS
and throughput per class and, as [`PERF 3.2`](/architecture/pillars/performance-efficiency/perf-03-service-selection-and-sizing/#perf-32-establish-which-sizing-decisions-are-reversible-before-you-make-them) records, requires a clone to change. Choosing it on
the access pattern rather than on price is therefore worth more here than in places where the
decision is adjustable.

<LinkChip href="https://docs.stackit.cloud/products/storage/object-storage/">Object Storage</LinkChip> has a different
performance model again, driven by object size, request rate and parallelism rather than by a
tier, and there is <LinkChip href="https://docs.stackit.cloud/products/storage/object-storage/tutorials/optimize-object-storage-performance/">specific
guidance</LinkChip>
for it.

**Tradeoffs.** **Cost Optimization.** Higher tiers cost more per unit and the difference is
continuous. **Sustainability.** Over-provisioned I/O is allocated capacity doing no work, per
[`SUS 2`](/architecture/pillars/sustainability/sus-02-right-sizing/).

**Verify.** For each volume on your critical flow, which tier is it on and who chose it? Which are
running on the default because nobody specified?

---

## PERF 4.4 Bound every result set and eliminate per-row round trips

**Risk if not established:** High

Two patterns account for a large share of data access problems, and both work perfectly in
development.

**The unbounded result** is a query with no limit, written when the table had a hundred rows. It
grows with the data, and the failure is not gradual: it works, then it is slow, then it exhausts
memory. Every query that can return a variable number of rows needs a bound, and every interface
that returns a list needs pagination.

**The per-row round trip**, where fetching a list is one query and each element then costs
another. The application looks fine in a profile because no single query is slow. What is slow is
that there are eight hundred of them, and each pays the network latency that [`REL 5.1`](/architecture/pillars/reliability/rel-05-resilient-interactions/#rel-51-set-an-explicit-timeout-on-every-call-that-crosses-a-process-boundary) sets a
timeout on.

Both are invisible at development data volumes, which is why [`PERF 8.1`](/architecture/pillars/performance-efficiency/perf-08-load-testing/#perf-81-test-with-realistic-data-volume-distribution-and-concurrency) insists on realistic data
rather than realistic code paths.

Watch the same pattern outside databases. An object store listed key by key, an API called once
per item, a cache checked in a loop. The shape is identical and so is the fix: ask for what you
need in one request.

**On STACKIT.** This is application design throughout. The platform's contribution is making the
pattern visible: distributed tracing through
<LinkChip href="https://docs.stackit.cloud/products/logging-and-monitoring/observability/">Observability</LinkChip> shows a
request that made eight hundred calls in a way that a latency metric does not, which is why
[`OPS 7.1`](/architecture/pillars/operational-excellence/ops-07-observability/#ops-71-emit-metrics-logs-and-traces-and-make-them-joinable) treats traces as a distinct signal type rather than a nicety.

**Tradeoffs.** **Operational Excellence.** Pagination is more code and a more complex interface,
and it is one of the places where the correct design is more work than the naive one for the whole
life of the system.

**Verify.** Take the most complex request on your critical flow. How many database queries and
external calls does it make, and how does that number change with the size of the result?

---

## Related

- [`PERF 3`](/architecture/pillars/performance-efficiency/perf-03-service-selection-and-sizing/) Selection and sizing, whose ceiling this question answers when reached
- [`PERF 6`](/architecture/pillars/performance-efficiency/perf-06-reduce-work/) Reducing work, which caching belongs to
- [`PERF 8`](/architecture/pillars/performance-efficiency/perf-08-load-testing/) Load testing, which is where unbounded results surface
- [`PERF 9.2`](/architecture/pillars/performance-efficiency/perf-09-performance-lifecycle/#perf-92-re-examine-data-access-as-the-data-grows) Lifecycle, where indexes and query plans are re-examined
- [`REL 5.1`](/architecture/pillars/reliability/rel-05-resilient-interactions/#rel-51-set-an-explicit-timeout-on-every-call-that-crosses-a-process-boundary) Timeouts, which per-row round trips multiply