Database Performance Tuning and Scaling Questions

Direct answer

An index speeds up finding rows that match a condition; it doesn't reduce the work of
aggregating across however many matching rows there are. If the query is slow because
it aggregates a lot of data on every request, that's the question's own premise, an index
alone usually isn't the fix. What actually helps is precomputing the aggregate itself,
which is what a materialized view does: it stores the result of a query and serves reads
from that stored result until refreshed.

The difference, and when each applies

An index is a separate, ordered structure the database maintains alongside a table so
it can locate matching rows without scanning the whole table, it cuts down rows read.
A materialized view (unlike a plain view, which just stores the SQL and re-runs it on
every read) physically stores the query's result on disk; reading from it means reading
precomputed rows, not redoing the aggregation.

When an index helps this kind of query: if the aggregation is over a filtered subset,
for example "sum sales where region = X and date > Y", and the filtered subset is much
smaller than the whole table, an index on (region, date) lets the database skip
straight to the relevant rows before aggregating only that smaller set. Real speedup, real
limit: it only helps the scanning part. If the filtered subset is itself still large,
the aggregation work on that subset happens on every request regardless.

When to reach for a materialized view instead: when the query aggregates over most or
all of a large table on every request, with little or no selective filter to index
against, or when the same expensive aggregate is requested repeatedly by many users or
page loads (a dashboard being the classic case). Paying the aggregation cost once per
refresh interval and serving many reads from the stored result is cheaper overall than
paying it on every single page load.

Operational costs to explain to stakeholders: refresh cadence, the view is only as
fresh as its last refresh, and the business has to accept some staleness window, five
minutes or a day, which is a product decision, not just a technical one; extra storage,
the stored result is separate from the source data, usually much smaller than the source
table but not zero; and refresh cost, the refresh itself re-runs the full aggregation,
carrying its own load on the database, so it needs its own schedule chosen to avoid
competing with peak traffic.

Worked example

A dashboard aggregates total revenue per region across a 50-million-row orders table,
scanning nearly the whole table on every load regardless of which region is shown. An
index on (region) doesn't materially help here: the query already needs to touch nearly
every row to produce the totals, there's no small, selective subset to jump to. A
materialized view that stores the precomputed per-region totals, refreshed every 15
minutes, turns a full-table aggregation into a lookup of a handful of pre-summed rows: the
dashboard's read becomes fast and roughly constant-cost, at the price of the numbers being
up to 15 minutes old, and the refresh job itself still doing that same 50-million-row scan
once per interval instead of once per page load.

Trade-offs and pitfalls

• Reaching for an index reflexively, because it's the familiar, lower-ceremony fix, then
  being surprised it didn't help; check whether the slow part is finding rows or
  aggregating them (a plan showing a large aggregate or group step dominating time even
  with a fast index scan feeding it is the tell).
• A materialized view's staleness is invisible at the storage layer; nothing stops a
  stakeholder from assuming a dashboard number is live. Surface a refreshed-at timestamp
  on the dashboard itself, not just a note in a runbook.
• Refreshing too frequently defeats the purpose, if a refresh takes nearly as long as the
  interval between them, the system is back to near-continuous full aggregation load,
  while refreshing too rarely erodes trust in the number; size the cadence against both
  the aggregation's real cost and how fresh stakeholders actually need it, not a default
  picked without checking either.

Direct answer

Route writes to the primary and reads to replicas behind a layer the application does not have to think about on every call, a proxy, a smart client, or an explicit read and write connection pool (a fixed-size set of already-open database connections that requests borrow and return instead of opening a new one each time), and decide per read, explicitly, whether staleness up to replica lag is acceptable or whether that specific read needs the primary's guaranteed-current data.

How to think about it

Routing. A middleware or proxy layer, or a smart driver, inspects each query: writes (INSERT, UPDATE, DELETE, and usually anything inside an explicit transaction) go to the primary, plain SELECTs go to a replica, often load-balanced or chosen by lowest observed lag. Many teams instead do this explicitly in application code with separate read and write connection pools, less magic and easier to debug, at the cost of every call site needing to know which pool applies.

Reading your own recent write. The core problem: a client that just wrote to the primary and then immediately reads from a lagging replica can momentarily see its own write disappear. Three mitigations, in increasing cost: route that specific read back to the primary for a short window after a write from the same session, the simplest and always correct option, at the cost of some read load landing on the primary; track a position marker from the write (a log sequence number in Postgres) and have the client wait for, or select, a replica that has caught up to at least that position, correct without forcing the primary, at the cost of a wait or extra replica-selection logic; or accept the staleness where the product genuinely does not need read-your-write consistency, a public view counter, for example.

Connection-pooling implications. Read and write pools must be sized separately: a service pointed at one primary and three replicas needs pool sizing arithmetic done per target, not once for "the database," since each replica is its own distinct connection budget.

Replica lag and primary unavailability. If a chosen replica's lag exceeds a defined threshold, the router should stop sending it reads rather than silently serve increasingly stale data. If the primary itself becomes unavailable, writes must fail or queue, there is no way to read-write-split around losing the writer, until a replica is promoted through the normal failover path; reads can often keep flowing from the surviving replicas the whole time, which is one of read-write splitting's genuine availability benefits, not just a performance one.

Worked example

The real replication-lag columns a router's health check should poll, from a live physical streaming-replication setup (a primary plus a hot-standby read replica built with pg_basebackup, the standard shape behind a read-write-splitting read replica, not a table-scoped logical subscription):

SELECT application_name, state, write_lag, replay_lag
FROM pg_stat_replication;

 application_name | state     | write_lag       | replay_lag
 walreceiver       | streaming | 00:00:00.01818  | 00:00:00.01957

walreceiver is the default application_name a physical standby reports unless it sets its own; a real multi-replica deployment typically names each one (replica_us_east_1, and so on) so the router can tell them apart in this same view. This particular reading, about 18 to 20 milliseconds, came from one write immediately followed by one poll on an otherwise idle pair of containers, not a production benchmark; the numeric thresholds for "too far behind, pull it from rotation" have to be set from real observed lag under real load, not from a quiet-period reading like this one. What is worth keeping from the number itself: it is not zero, confirming replication is genuinely asynchronous even on an idle system, so a read landing on the replica in the same instant as a write can still observe stale data, exactly the read-your-own-write hazard described above.

Trade-offs and pitfalls

Routing every read to a replica, including read-your-own-write-sensitive ones, produces a classic and very visible bug: a user updates their profile, reloads immediately, and sees the old value. Replica lag is not constant, it grows under load exactly when the replicas are needed most, so a fixed threshold picked during a quiet period can be wrong precisely during a traffic spike. Read-write splitting adds a new failure mode, the router or the lag-tracking logic itself being wrong, in exchange for its scaling benefit; it is not a free win.

Direct answer

Investigate before touching anything: recompute the summary from the source table and
compare, because the shape of the drift (a clean offset versus scattered small
differences) tells you what kind of bug caused it. Reconcile the data by fixing it toward
the recomputed truth and verifying zero drift remains with the same comparison query. Then
close the loop structurally, make it impossible for the source to change without the
summary changing too, rather than trusting a convention that already failed once.

Investigate, reconcile, prevent

Investigate. Run a read-only comparison: recompute the aggregate directly from the
source table and diff it against the stored summary, before writing anything. The
pattern of the difference is diagnostic: a consistent offset across many rows suggests
a bulk operation (a backfill, a data migration, a manual correction) that bypassed the
code path responsible for keeping the summary in sync. Scattered, small, inconsistent
differences suggest a race condition between concurrent updates to the same row.

Reconcile. Once you understand the scope (how many rows, how large a gap), apply a
targeted update setting the drifted rows to the recomputed truth, inside a transaction,
during low-traffic hours if it touches a large number of rows. Re-run the exact same
comparison query afterward and confirm zero rows remain drifted, not a weaker or
different check that might miss the same class of bug.

Prevent recurrence. Find every code path that can change the source data, not only
the one you expect, and make the summary update either impossible to skip or independent
of application code entirely: a database trigger that fires in the same transaction as
the source write (so there is no path that can write source data without touching the
summary, by construction), or a change-data-capture (CDC) pipeline that reads the
database's write-ahead log and reliably emits every change regardless of which code path
made it, rather than relying on an application convention of "remember to also update
this," which is precisely the convention that failed here. Add a standing, scheduled
reconciliation job that alerts rather than silently auto-fixes, so a new drift is caught
in hours, not discovered again in six months.

Worked example (executed against a real PostgreSQL 16 instance)

CREATE TABLE orders (order_id bigserial PRIMARY KEY, user_id bigint NOT NULL, status text NOT NULL);
INSERT INTO orders (user_id, status)
  SELECT (g % 1000) + 1, 'completed' FROM generate_series(1, 50000) g;

CREATE TABLE user_order_counts (user_id bigint PRIMARY KEY, completed_orders int NOT NULL);
INSERT INTO user_order_counts
  SELECT user_id, count(*) FROM orders WHERE status='completed' GROUP BY user_id;

-- simulate the missed-update bug: a bulk correction that bypassed the
-- code path responsible for keeping user_order_counts in sync
UPDATE orders SET status = 'refunded' WHERE order_id % 37 = 0;   -- UPDATE 1351

Reconciliation query, reusing the summary's own definition exactly (status='completed')
so the comparison can't manufacture false drift:

SELECT count(*) AS drifted_users
FROM user_order_counts c
JOIN (SELECT user_id, count(*) AS actual FROM orders
      WHERE status='completed' GROUP BY user_id) t
  ON t.user_id = c.user_id
WHERE c.completed_orders <> t.actual;
-- drifted_users = 1000   (every user in this 1,000-user demo was touched)

The fix, and re-verification with the identical query:

UPDATE user_order_counts c
SET completed_orders = t.actual
FROM (SELECT user_id, count(*) AS actual FROM orders
      WHERE status='completed' GROUP BY user_id) t
WHERE t.user_id = c.user_id AND c.completed_orders <> t.actual;
-- UPDATE 1000

-- re-run the same comparison query:
-- drifted_users_after_fix = 0

Zero drift confirmed after the fix, not assumed.

Trade-offs and pitfalls

• Fixing today's numbers without also fixing the code path guarantees the same bulk
  operation drifts the numbers again the next time anyone runs something similar.
• A synchronous trigger is the most airtight prevention, no code path can skip it, but
  adds write latency and lock contention directly on the source table's hot path. CDC-
  based materialization decouples that cost (it runs asynchronously) at the price of a
  short, bounded staleness window and the operational overhead of running a CDC pipeline.
• Calling it done once the counts are fixed, without adding a standing monitor, means the
  next drift again goes unnoticed for months.
• A reconciliation query whose filter doesn't exactly match the summary's own definition,
  for example forgetting a soft-delete condition the summary already accounts for, can
  manufacture false drift or, worse, mask real drift; reuse the exact same definition, as
  the example above does.

Direct answer

Capacity planning is forecasting a real growth curve from actual trend data, provisioning ahead of it with a safety margin sized to how long it takes you to actually add capacity, and wiring alerts so the response is scheduled work rather than a page. For a projected 5x in 12 months, the model you pick matters more than the headline number: a smooth compound curve behaves very differently from a step change tied to a known launch, and your alert thresholds should be set from your own provisioning lead time, not a round number borrowed from a blog post.

How to think about it

Model the growth. Compound growth is the safe default without better information: solve for the monthly rate that gets you from current usage to 5x over 12 months.

so $r = 5^{1/12} - 1$. If you instead know about a specific step (a big customer signing, a feature launch), layer that as a discrete jump on top of the smooth trend rather than smoothing it away.

Safety margin sized to lead time. WARN and PAGE thresholds should not be arbitrary round numbers. WARN needs to fire early enough that the provisioning action (a resize request, an approval, the resize itself) finishes before usage reaches PAGE. If it cannot, lower WARN, do not accept the risk.

Cost projection. Project the same curve onto the billing metric, storage dollars per GB-month, provisioned input/output operations per second (IOPS, how many read or write operations the storage layer can do each second) dollars per IOPS-month, so finance sees the number rising before the invoice does, not after.

Forecasting inputs people forget. Compute and storage do not always grow at the same rate and should be modeled separately. Scheduled scaling events (a known launch, a marketing campaign) should be layered on top of the organic trend, not baked into it. Write-ahead log (WAL, the durability log a database writes before committing a change) volume and retention grow faster than data size on write-heavy workloads, and if WAL is retained for a replication window or point-in-time recovery, that retention is its own growing volume. Backup and archival storage across your retention policy is the item most often missed entirely: a 5x growth in live data usually means 5x-or-more growth in backups, especially under a multi-year retention requirement, since old backups do not shrink just because live data changed shape.

Automation and alerting. Alert on the trend, not just the level; a sudden change in growth rate is often the earliest real signal something changed, before any threshold is crossed. Tie WARN to a ticket and runbook, PAGE to on-call, and where the platform allows it, wire the resize itself to fire automatically once a threshold is crossed and a change window is open, instead of requiring a human to notice a graph.

Scaling triggers as a companion to alerting. A trigger is a pre-agreed threshold that fires a specific scaling ACTION, not just a notification: "at 1,000,000 queries per second (QPS) sustained for 15 minutes, add a read replica" is a trigger. "Storage at 85%, page a human who decides what to do" is an alert. A complete capacity plan has both.

Worked example

Pinned inputs, reproducible on re-run:

current_storage_gb = 800
current_iops = 6000
growth_multiple = 5.0
horizon_months = 12
alert_warn_pct = 0.70
alert_page_pct = 0.85
lead_time_months = 1.0

r = growth_multiple ** (1 / horizon_months) - 1

Real output from running it:

implied monthly compound growth rate: 14.3530%

month  storage_gb  iops
    0         800  6000
    6        1789 13416
   12        4000 30000

provision AT LEAST 4706 GB / 35294 IOPS so month 12's actual usage
(4000 GB / 30000 IOPS) lands at the 85% page threshold, not above it
alert thresholds: WARN at 3294 GB (70%), PAGE at 4000 GB (85%)

at this growth rate, WARN fires roughly 1.4 months before PAGE would,
against a 1-month provisioning lead time (sufficient)

The 1.4-month runway against a 1-month lead time is the check that matters: if that number had come out below the lead time, the fix is to lower WARN, not hope the resize finishes in time.

A second, QPS-based variant shows the scaling-triggers framing on a different metric: 100,000 QPS growing to 1,000,000 QPS over 18 months instead of 12. Same method, different multiple and horizon: $r = 10^{1/18} - 1 \approx 13.65\%$ a month, giving 100,000 at month 0, about 215,000 at month 6, about 464,000 at month 12, and 1,000,000 at month 18 (each value is $100000 \times (1+r)^m$, computed directly from the pinned rate above). The companion trigger here would be concrete and actionable, for example "add a read replica once sustained QPS crosses 500,000 for 15 minutes," not just a dashboard threshold.

The same method scales to a longer horizon too, for example a 10x-over-3-years warehouse case (multiple 10, horizon 36 months): the growth-rate formula is unchanged, but at that horizon the WAL retention and backup and archival line items above stop being a footnote and start dominating, since backup windows and cross-region replication bandwidth become material costs at multi-terabyte, multi-year scale in a way they are not at 12 months.

Trade-offs and pitfalls

Setting WARN too close to PAGE leaves no time to act, exactly what the runway check above exists to catch. Alerting on absolute usage only, and missing rate-of-change, means the earliest real signal gets ignored until a static threshold trips. Treating compute and storage as one number hides real divergence: a read-heavy service can 5x its IOPS while barely growing on disk, and a write-heavy audit log can do the reverse. The single most common gap is forgetting backups and WAL retention, teams size the live database carefully and then get surprised when the backup bill, or the backup window and recovery time objective, breaks first.

Direct answer

Denormalize when a specific, measured read path is too slow because of a join, and the write complexity and consistency cost of maintaining a duplicate copy is smaller than the cost of that slow read continuing. It is a targeted fix for one query pattern, not a general schema philosophy, propose it with the specific slow query and its measured cost in hand, not as a blanket "let's denormalize for performance."

How to think about it

Write complexity. Every write that touches the source of truth now potentially has to update the denormalized copy too; miss one write path and the two copies diverge silently.

Data-consistency risk. The denormalized copy is a cache living inside your own schema, so the sync strategy has to be chosen explicitly. Same-transaction updates give the strongest consistency (both writes commit together or neither does) but couple the two tables' write paths and can add lock contention. Change-data-capture or streaming propagation is looser and eventually consistent, and needs its own lag monitoring. An accepted-stale batch job is the simplest option, with freshness equal to whatever the batch interval is.

Storage overhead. Proportional to how much is duplicated. Denormalizing a few narrow columns, a name, a price, onto a high-cardinality table is usually a small percentage overhead; denormalizing whole nested structures is not.

Ongoing burden. A denormalized copy is a second thing that can be wrong. Whoever changes the schema later has to remember it exists and keep both write paths in sync, and that cost compounds over the schema's whole lifetime, not just at build time, which is exactly why being selective about where to apply it matters.

The textbook trigger is a product listing page needing product, price, inventory, category, and seller name in one read, a five-table join collapsed to one row per item. A useful middle ground, rather than choosing fully normalized or fully denormalized for a whole schema: keep the normalized tables as the system of record and add one denormalized read table kept in sync through change-data-capture or a streaming job. Writes still go through the clean normalized schema, and only the read path gets the flattened copy, capturing most of the latency win with a narrower blast radius than denormalizing the primary write tables themselves.

Worked example

A real, measured comparison: an order-line-item read that needs the customer's name and the product's name alongside the line item itself, first as a four-table join, then against a single flattened copy of the same data.

-- normalized: 4-way join for one order's line items
Hash Join
  -> Nested Loop
       -> Nested Loop
            -> Index Scan on cust_orders (Index Cond: id = 555)
            -> Index Scan on customers (Index Cond: id = o.customer_id)
       -> Bitmap Heap Scan on order_items (Index Cond: order_id = 555)
  -> Hash (Seq Scan on products)
Execution Time: 0.897 ms

-- denormalized: single-table read
Bitmap Heap Scan on order_items_denorm (Index Cond: order_id = 555)
Execution Time: 0.068 ms

The normalized plan is an 18-line, four-node plan touching all of order_items, cust_orders, customers, and products; the denormalized plan is a 7-line single bitmap index scan. The measured storage delta for this specific, narrow denormalization (two short text columns duplicated onto every line item, at 40,000 rows): 3,760 kB for the normalized line-item table versus 3,888 kB for the denormalized copy, about a 3.4% overhead. That number is specific to duplicating two short columns; denormalizing wider or more redundant data scales the storage cost up proportionally, it is not a universal figure.

Trade-offs and pitfalls

Denormalizing before a slow query has actually been measured pays the write-complexity and consistency cost for a problem that may not exist. Missing a write path when the sync logic gets added is the single most common way denormalized data silently goes stale. The most damaging failure mode: a later engineer reads from the denormalized copy to make a write decision, treating it as the source of truth by accident, which compounds any existing drift into a real data-correctness bug.