Database Performance Tuning and Scaling Questions

Direct answer

A materialized view (a database object that stores a query's computed result as an actual table,
rather than recomputing it on every read, until it is explicitly refreshed) that summarizes daily user
metrics:

CREATE MATERIALIZED VIEW daily_user_metrics AS
SELECT
  date_trunc('day', occurred_at) AS day,
  user_id,
  count(*) FILTER (WHERE event_type = 'login')    AS logins,
  count(*) FILTER (WHERE event_type = 'purchase')  AS purchases,
  count(*) FILTER (WHERE event_type = 'click')     AS clicks
FROM user_events
GROUP BY 1, 2
WITH DATA;

-- required before CONCURRENTLY will work at all:
CREATE UNIQUE INDEX daily_user_metrics_day_user_uidx
  ON daily_user_metrics (day, user_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_user_metrics;

A plain REFRESH MATERIALIZED VIEW rebuilds the view under an exclusive lock that blocks
every reader for the whole refresh. CONCURRENTLY instead computes the new result into a
separate temporary structure, diffs it row by row against the current contents, and
applies only what changed, so readers keep querying the old data throughout and are
never blocked, at the cost of needing a unique index to match old rows to new ones, and
doing roughly double the work.

Locking implications and the unique-index requirement

Executed against a real PostgreSQL 16 instance: attempting CONCURRENTLY without the
unique index fails immediately, before doing any work:

ERROR:  cannot refresh materialized view "public.daily_user_metrics" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the
materialized view.

The index is required because CONCURRENTLY needs a way to match each row in the newly
computed result to a row in the existing one, to know which rows are unchanged, which
changed, and which were added or removed, so it can apply a targeted diff instead of a
wholesale replace. After adding the index, the same command succeeds.

Demonstrating the actual blocking difference, on a 4.2 million row source table so
the refresh takes long enough to observe: a plain (non-concurrent) REFRESH ran for
about 2.0 seconds; a SELECT count(*) issued against the view 0.3 seconds into that
refresh, from a second session, didn't return until the refresh finished, waiting roughly
1.7 seconds for a lock it couldn't acquire. Repeating the same test with REFRESH MATERIALIZED VIEW CONCURRENTLY (about 2.7 seconds to run, more total work, as expected):
the same SELECT, issued 0.3 seconds in, returned in about 0.09 seconds, effectively
unblocked. Absolute timings will vary by hardware; the qualitative result, blocked versus
not, follows directly from Postgres's documented lock rules for each command and isn't a
timing artifact.

Scheduling refreshes to minimize impact

Match the cadence to how stale the dashboard can tolerably be, for example every 15
minutes via a scheduler like pg_cron or an external job runner, and avoid overlapping
refreshes with periods that are already I/O-sensitive: nightly backup windows, known
traffic peaks. CONCURRENTLY protects readers from being blocked, but it doesn't make
the refresh free: it's still a full recompute plus a diff, real CPU and I/O work. A view
refreshed every few seconds can become the very bottleneck it was meant to solve if the
cadence isn't matched to how long a refresh actually takes and how much the underlying
data actually changes between refreshes.

Trade-offs and pitfalls

• Forgetting the unique index and only discovering the CONCURRENTLY requirement when the
  first scheduled refresh fails in production; add the index in the same migration that
  creates the view.
• CONCURRENTLY costs more work per refresh (build a full new copy, then diff) than a
  plain refresh of the same data; on a view with no natural unique key, for example a
  single aggregate row, you may need to add a synthetic key purely to satisfy the
  constraint.
• CONCURRENTLY isn't lock-free, just weaker-locked; it can still conflict with, for
  instance, concurrent DDL on the view, and refreshes scheduled more frequently than one
  can complete will pile up behind each other.

Direct answer

Don't start by changing anything. Measure where the 8 seconds actually goes, apply the
cheap low-risk fixes first, then the structural ones, and re-measure against the same
baseline metric before calling it done, with every step independently revertible. A
concrete example: on a comparably-shaped dashboard I traced a slow aggregate to two
co-occurring symptoms, table bloat and a skewed partition, that each looked fine in
isolation but compounded together.

The remediation plan

1. Measure first. Run EXPLAIN (ANALYZE, BUFFERS) (the command that actually
executes the query and reports the real plan, row counts, and buffer usage, as opposed to
plain EXPLAIN's estimate) on the dashboard's queries. Check the database's query-stats
extension for which statement accounts for the most total time. Check the dead-tuple
ratio (rows a DELETE or UPDATE left behind that haven't been reclaimed yet) in
pg_stat_user_tables. If the table is partitioned, confirm partition pruning is actually
limiting the scan to the relevant partition, and don't assume that alone means the query
is healthy.

2. Quick wins (hours, low risk). Add a missing index the plan reveals is needed.
Rewrite an obviously wasteful query, SELECT * pulling unused columns, an N+1 loop
issuing one query per row instead of one query for all of them. Add a statement_timeout
so one slow request can't starve the rest. If slight staleness is acceptable, add a short
server-side cache in front of the aggregate.

3. Medium-term (days to weeks). A materialized view (a query's result saved as its own table and refreshed on a schedule, instead of recomputed on every read) or precomputed rollup refreshed
on a schedule. Connection-pool tuning. VACUUM (reclaims space left behind by deleted or updated rows so it can be reused, though it does not shrink the file on disk) or reindex maintenance if bloat turns out to
be structural rather than a one-off. Moving the report off the primary database to a read
replica.

4. Long-term / architecture (weeks+). A real incremental pre-aggregation pipeline
that updates rollups at write time instead of computing them at read time. A partitioning
redesign if one partition has become the effective bottleneck. Moving the analytical
workload off the OLTP (online transaction processing, the primary system handling live
reads and writes) primary entirely, onto an OLAP (online analytical processing, a system
built for scanning and aggregating large amounts of historical data) engine designed for
this shape of query.

5. Knowing if a change made things worse. Track the same metric before and after,
ideally the 99th-percentile dashboard load time from real traffic, not a single manual
test run. Watch the query's estimated row count versus actual in the plan (a growing gap
signals stale statistics or a regression). Watch for new lock contention or replication
lag the fix itself introduced. Ship each change behind something independently revertible
(a config value, a feature flag) rather than as one combined deploy.

Worked example: index bloat and partition skew as co-occurring symptoms

I reproduced the bloat side of this on a real PostgreSQL 16 instance to ground the
numbers rather than assert them. A 500,000-row, 78 MB table had half of its rows deleted, executed against a real
PostgreSQL 16 instance:

DELETE FROM bench WHERE id % 2 = 0;   -- 250,000 rows removed, half the table
-- after the stats collector flushes:
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='bench';
--  n_live_tup | n_dead_tup
-- ------------+------------
--      250000 |     250000

A second delete removed half of the remaining rows, checked after the stats collector
caught up, and shows the cumulative dead-tuple count, not just this delete's share:

 relname | n_live_tup | n_dead_tup | n_tup_del
---------+------------+------------+-----------
 bench   |     125000 |     375000 |    375000

n_dead_tup (375,000) counts every row version deleted since the last vacuum or
analyze, from both deletes combined, so it is not the same number as "how many dead
row versions are still physically sitting in the table right now": PostgreSQL prunes
some dead line pointers from a page opportunistically whenever it revisits that page
for any reason, including the second DELETE's own scan, not only during an explicit
VACUUM. A direct on-disk check with the pgstattuple extension right before running
VACUUM confirms only about 125,000 tuples were still physically dead at that point,
matching what VACUUM actually finds:

VACUUM (VERBOSE) bench;

tuples: 125020 removed, 125000 remain, 0 are dead but not yet removable

VACUUM removed 125,020 tuples, not 375,000, because roughly 250,000 of the
cumulative dead-tuple count had already been reclaimed by that opportunistic pruning
before VACUUM ever ran; a second VACUUM immediately after confirmed there was
nothing left to reclaim (0 removed, 125000 remain, where the "remain" figure is
VACUUM's report of the table's current live row count, not a dead-tuple count, so it
reads 125,000, matching the live rows still in the table, rather than 0; 0 remain
would only be printed if the table were actually empty). Despite reclaiming that space, the table's
physical size barely moved: 78 MB before and 78 MB after (81,928,192 bytes before,
81,936,384 bytes after, a difference of exactly one 8 KB page, an unmeasurable change
at the table's scale, not literally the same size the rounded MB figures suggest).
VACUUM without FULL
marks space reusable for future inserts; it does not return it to the operating
system. The table now holds 125,000 live rows, one quarter of the 500,000 rows it was
originally sized for, inside that same 78 MB file, so every scan of that table,
including the dashboard's aggregate, was reading roughly four times the pages the live
data needed, not twice.

Now add the partition angle: if this table is partitioned by month and the query filters
by date, pruning correctly narrows the scan to one partition, say the current month's,
which is also the one absorbing most of the day's writes and deletes (a "hot" partition).
Pruning working correctly (touching only 1 of 12 partitions) doesn't mean that partition
is healthy; if it's carrying a disproportionate share of the table's churn, it can be
bloated exactly like the example above while its 11 siblings are fine. That's why "the
plan shows pruning is working" is not the same conclusion as "the query is fast", you
still have to check the one partition doing all the work.

Trade-offs and pitfalls

• Reaching for an index before checking EXPLAIN ANALYZE risks fixing the wrong problem;
  bloat and stale statistics both produce slow plans that look, at a glance, like a
  missing-index problem.
• A cache "fixes" the symptom without addressing what staleness stakeholders can actually
  accept; a silently stale number on a business dashboard can be worse than a slow but
  correct one, so that's a product conversation, not just a technical one.
• Precomputing the aggregate shifts cost to the write path and introduces a staleness
  window; decide the acceptable window with the business rather than assuming.
• Validating against a single idle-system test run and calling it done misses whether the
  fix holds under real concurrent load and ongoing bloat regrowth; a fix that looks great
  in staging can still fail the p99 (99th-percentile) target in production a week later.

Direct answer

Distinguish CPU-, memory-, or I/O-bound by looking at where time is actually being spent, not by guessing from symptoms. The database's own stats views, buffer cache hit ratio, pg_stat_io, and wait events in pg_stat_activity, together with operating-system tools, triangulate which resource is actually saturated. High write latency specifically points first at input and output (I/O), because a write, unlike a read, always eventually has to reach durable storage through the write-ahead log; a write-latency problem that is not I/O-bound is usually lock contention or unexpected CPU cost (an expensive trigger or constraint check) rather than the storage layer itself.

How to think about it

CPU-bound signals. Active backends in pg_stat_activity show wait_event_type mostly null, meaning they are actually running, not waiting. Operating-system tools show high user CPU with load average tracking the core count. EXPLAIN ANALYZE (Postgres's command for showing a query's actual execution plan and per-step timing) shows expensive in-memory work, sorts, hash joins, function evaluation, rather than I/O waits. Remediation: query and index tuning to reduce work per row, cutting per-row trigger or function overhead, and only as a last resort, more or faster cores.

Memory-bound signals. Buffer cache hit ratio (blks_hit over blks_hit plus blks_read) trending down as the working set outgrows available memory. Operating-system level page-cache thrashing, swap activity specifically, is a hard memory signal distinct from a buffer-cache one. Remediation: more memory or a larger buffer cache, or shrinking the working set through better indexing or partitioning so a given query touches less resident data at once.

I/O-bound signals. wait_event_type = 'IO' on active backends. pg_stat_io showing nonzero read and write time on the relevant backend type. pg_stat_bgwriter.buffers_backend climbing relative to buffers_checkpoint and buffers_clean, a strong I/O-bound write signal specifically: it means backend processes are being forced to write out dirty buffers themselves because the checkpointer and background writer are not keeping up, adding that write's full latency directly onto the foreground query instead of it happening asynchronously. Operating-system level: high device utilization and growing queue depth.

Worked example

Real pg_stat_io rows from a running instance, illustrating the shape of this evidence (columns: backend type, object, I/O context, reads, writes, extends):

     backend_type     |  object  | context | reads | writes | extends
 autovacuum worker     | relation | normal  |   267 |      0 |      29
 checkpointer          | relation | normal  |       |     39 |
 client backend        | relation | normal  |   738 |      0 |    2659

And the real pg_stat_bgwriter snapshot from the same instance: checkpoints_timed=0, checkpoints_req=4, buffers_checkpoint=1989, buffers_clean=0, buffers_backend=2854. Backend-driven writes (2854) exceeding checkpointer-driven writes (1989) here is exactly the I/O-bound-write pattern described above, backends are doing more of the dirty-buffer writing than the checkpointer is. On a sustained production workload showing this same ratio, that translates directly into elevated write latency; this demo's numbers come from a short burst of heavy update activity in one session, not sustained load, but the metric and how to read it are the real evidence to look for.

Two concrete triage patterns worth having memorized. First, the 99th percentile (p99, the latency value slower than 99% of requests) spikes at the same time the buffer cache hit ratio drops: that combination points at memory pressure specifically, not pure I/O or CPU, the working set stopped fitting in the buffer cache, so reads that used to be free cache hits now cost a real disk read; the fix path is finding out why the working set grew, data growth, a new query pattern, a lost index, before reaching for more memory. Second, p99 latency roughly doubles specifically while a concurrent batch or extract-transform-load (ETL) job is running: this is resource contention, the batch job is competing for the same CPU, I/O, or lock budget as the transactional queries at the same time; the fix is workload isolation, a replica or separate store for the batch job, off-peak scheduling, or resource governance limiting its input/output operations per second (IOPS) and CPU share, rather than tuning the transactional queries, which may not have changed at all, only their competition for resources did.

Trade-offs and pitfalls

Jumping straight to a bigger instance without checking which resource is actually saturated wastes money on the wrong dimension, a memory-bound workload gets no benefit from more CPU cores. Trusting a single snapshot instead of a time series over the incident window cannot distinguish a brief spike from sustained saturation. Conflating a lock wait (wait_event_type = 'Lock', a concurrency problem) with an I/O wait looks similar from the outside, "the query is slow," but needs a completely different fix.

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

Pick one real incident with one measurable starting symptom, latency or throughput are
the usual candidates, name specifically what you tried and why each step was chosen over
the alternatives, and close with how you validated the improvement and what it cost you
elsewhere. A grab-bag of "things I've optimized over the years" reads as unfocused; one
incident, narrated end to end, reads as judgment.

A scaffold for the story

Situation. State the symptom in a measurable way: "the checkout endpoint's p95
(95th-percentile) database time was regularly exceeding 400 ms during traffic peaks" beats "the database
was slow." Latency and throughput are the two most common starting metrics, latency
because users feel it directly, throughput because it caps how much load the system can
absorb before latency degrades for everyone.

Task. What target made the incident worth fixing: an SLA being missed, a
capacity ceiling being approached, a specific complaint.

Action. Narrate three concrete techniques in the order you tried them, not a list.
The available categories a real answer usually draws from are indexing, query rewrites,
partitioning, denormalization (storing redundant, precomputed copies of data to avoid recomputing it
on every read), or connection/configuration tuning; pick whichever three
actually applied to your incident. A representative shape:

1. Confirmed the query's plan with EXPLAIN ANALYZE (the command that runs the query and
   reports the real plan and timing, not an estimate) and added an index matching the
   filter and sort the query actually used, verifying the plan flipped from a sequential
   scan to an index scan.
2. Found the query was also pulling unused joined columns (SELECT * where three columns
   were used); rewrote it to select only what was needed and pushed a filter earlier in
   the plan.
3. Noticed via the database's activity view that many requests were queued waiting for a
   pooled connection, not for query execution; resized the pool and added a
   statement_timeout so one runaway query couldn't starve it.

Result. Validate against the same metric you started with, measured under
comparable load, not a different, easier one. Watch it hold up over days in production,
not just at deploy time, since delayed effects (maintenance overhead, data growth) can
erode a fix that looked clean on day one.

Trade-off accepted. A senior answer names a real cost. For example: the new index
sped up reads but added overhead to a high-insert table's write path; that was monitored
afterward and accepted as a small, budgeted latency increase on writes rather than
blocking the read fix on eliminating it entirely.

Trade-offs and pitfalls

• Listing several unrelated wins instead of one incident reads as a highlight reel, not
  evidence of how you reason through a problem under pressure.
• Skipping the "why this technique, in this order" reasoning makes it sound like guessing
  that happened to work, which is the opposite of what the question is testing for.
• Claiming an improvement without describing how it was validated, and against what
  baseline, is the single most common gap; interviewers will ask.
• Not naming a trade-off accepted reads as either inexperience or a curated story; real
  fixes cost something somewhere.