Data models and access paths

Suppose each run belongs to exactly one tenant, has a stable run identifier, and moves through a defined state machine. A composite uniqueness rule on tenant and run identifier prevents duplicate local identities. A foreign key can protect a relationship to an existing tenant; application validation alone races with concurrent changes. Separate an immutable creation time from a mutable completion time because sorting by one answers a different question from sorting by the other.

Choose normalization around ownership of facts. Duplicating a mutable tenant name into every run makes historical display easy but creates an update policy. Is the field a deliberate historical snapshot or an accidentally stale copy? A materialized read model can be valuable when its source, freshness bound, and rebuild procedure are clear. Denormalization removes joins from one read path while moving consistency work into writes or asynchronous projection maintenance.

For tenant equality followed by newest completion time, a B-tree beginning with tenant and then completion time can bound the tenant range and traverse it in the desired order. A state predicate changes the design: placing state before time helps an equality-constrained state query but changes ordering across states. A partial index for successful rows may fit a stable workload. Inspect the actual predicate and parameter behavior before assuming the planner can use it.

An index is a second maintained structure. Inserts and updates pay for its pages, logging, and cache footprint. Included payload columns can reduce table visits in suitable index-only scans, but visibility information and engine behavior still matter. PostgreSQL can sometimes use skip scans on later columns; therefore avoid the simplistic claim that a missing first-column predicate makes a composite index unusable. Ask how much of the structure is visited and why.

Use representative data and fresh statistics, then compare estimated and observed row counts with an execution plan. A sequential scan may win for a large matching fraction or a tiny cached table. Distinguish index entries examined from rows returned, and logical buffer hits from physical reads. Run repeated measurements under the expected concurrency; a fast isolated query can displace useful pages or amplify random reads when many tenants arrive together.

A page-count estimate is a useful starting bound, not a latency prediction. Assume fixed-size rows and ignore page headers, fragmentation, and compression for the calculation below. Real queries add tree navigation, row visibility checks, sorting, and possibly scattered table access. Preserve a baseline plan and test the slow tenant as well as the median tenant. Optimize an access path only after verifying that its output still obeys the required consistency and ordering rules.