PERF 4. How do you design data access for the actual query pattern?

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 Model for the queries the workload actually issues
• PERF 4.2 Index deliberately, and account for what each index costs
• PERF 4.3 Choose the storage tier from the access pattern
• PERF 4.4 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. 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 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 product documentation lists what is managed.

Where an analytical query pattern sits on top of data held elsewhere, Dremio 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, 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 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 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: observability metrics for PostgreSQL Flex 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 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. Kubernetes Engine storage classes 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.

For managed databases the equivalent choice is the performance class, which states maximum IOPS and throughput per class and, as PERF 3.2 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.

Object Storage has a different performance model again, driven by object size, request rate and parallelism rather than by a tier, and there is specific guidance 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.

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 sets a timeout on.

Both are invisible at development data volumes, which is why PERF 8.1 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 Observability shows a request that made eight hundred calls in a way that a latency metric does not, which is why OPS 7.1 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 Selection and sizing, whose ceiling this question answers when reached
• PERF 6 Reducing work, which caching belongs to
• PERF 8 Load testing, which is where unbounded results surface
• PERF 9.2 Lifecycle, where indexes and query plans are re-examined
• REL 5.1 Timeouts, which per-row round trips multiply