Database Performance Tuning and Scaling Questions

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

A production database monitoring dashboard needs metrics from four categories, resource utilization, replication, query performance, and capacity, because a healthy reading in one category can hide a real problem in another. Nine metrics cover the ground well: CPU utilization, buffer cache hit ratio, disk input/output operations per second (IOPS) utilization, disk space used, active connections against the configured maximum, replication lag, replica connectivity state, the 95th and 99th percentile (p95 and p99) query latency, and lock wait time or blocked query count.

How to think about it

This list holds whether the instance is standalone or serving a mixed workload of online transaction processing (OLTP, many small read and write transactions) and online analytical processing (OLAP, fewer, larger queries that scan and aggregate data); a mixed workload just means watching the query-performance metrics separately per workload type, since an analytical query's normal duration and a transactional query's normal duration are entirely different baselines, and one threshold cannot serve both honestly.

Worked example

Real output pulled from a running instance, illustrating what these look like in practice:

-- buffer cache hit ratio
 cache_hit_pct | blks_hit | blks_read
         99.95 |  1562989 |       718

-- per-table scan pattern (a missing-index signal when seq_scan dominates)
      relname       | seq_scan | idx_scan
 order_items_denorm  |        1 |        1
 orders               |        5 |        0

And the replication query returning correctly empty on an instance with no replica attached:

SELECT application_name, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;
-- (0 rows)

Zero rows here is the correct, real answer for this specific instance, not a broken query, a genuine replication setup returns the lag columns shown in a read-replica health check instead.

Trade-offs and pitfalls

Alerting on every metric at the same severity causes alert fatigue, and real pages get ignored along with the noise. Thresholds copied from someone else's blog post instead of your own baseline will page every day if your normal steady state already runs at that level. Missing the replica connectivity state specifically, because a lag graph can look fine while actually frozen at its last reading. Monitoring only resource metrics or only query-performance metrics, never both, hides real incidents either way: high CPU with no p99 alarm can be an entirely healthy scheduled batch job, and a p99 spike with normal CPU is often a lock or an external dependency, not the database's own fault at all. Connections being rejected outright rather than degrading gracefully once the cap is hit is exactly why that threshold should warn well before 100%, not at it.

Direct answer

Backpressure has to be visible to the producer, not silently absorbed by an ever-growing
queue or an ever-slower database, applied incrementally so throttling kicks in before
outright rejection and rejection before the system falls over, and calibrated against the
freshness SLA (service-level agreement) producers actually need, not just the database's
raw physical write ceiling.

Design

Surfacing backpressure to producers, in increasing strength: a signal in the response
that the system is under load and the producer should slow down voluntarily; an explicit
rate-limit response (an error the producer must respect, with a suggested retry delay);
or, for a synchronous write path, simply making the acknowledgment take longer, which
naturally throttles a single producer's rate but doesn't coordinate across many
independent producers.

Incremental throttling policy. A token bucket is the standard shape: tokens refill at
the sustained rate the database can absorb; each write consumes a token; a short burst
above the sustained rate is allowed up to the bucket's capacity, after which throttling
engages smoothly rather than as a hard cliff the instant sustained capacity is technically
exceeded for one second.

Balancing against SLA. The ingestion SLA (for example, "events are queryable within
60 seconds of being sent") sets how much lag is tolerable before throttling needs to get
more aggressive. A queue allowed to grow unbounded during a downstream slowdown can blow
that SLA for everyone even if no single write was ever rejected, so the trigger for
tightening throttling has to include queue depth or message age, not raw throughput (the volume of writes the system processes per second) alone.

Worked example: sizing the token bucket

Suppose the database's write path, including index maintenance, can sustainably absorb
8,000 rows/sec:

$\text{refill\_rate} = 8{,}000 \text{ tokens/sec}$

Allowing a 1.5x burst above sustained capacity:

$\text{bucket\_capacity} = 1.5 \times 8{,}000 = 12{,}000 \text{ tokens}$

$\text{burst\_seconds} = \frac{\text{bucket\_capacity}}{\text{refill\_rate}} = \frac{12{,}000}{8{,}000} = 1.5\text{s}$

The bucket holds the equivalent of 1.5 seconds of sustained-rate traffic as burst
headroom: producers can spike well above 8,000/sec briefly, but a burst sustained longer
than that drains the bucket and throttling engages at the sustained refill rate.

Trade-offs and pitfalls

• An unbounded queue in front of the database "solves" backpressure from the database's
  point of view, writes never get rejected, while silently blowing the freshness SLA; the
  queue itself needs its own depth- or age-based backpressure trigger, not just the
  database.
• Throttling protects the shared database but pushes cost onto the producer, retries,
  buffering, possible data loss if the producer can't buffer indefinitely; the contract
  needs to specify what a throttled producer is expected to do, buffer, drop, or alert a
  human, rather than leaving it implicit.
• A burst allowance sized "generously to be safe" defeats its own purpose: too large an
  allowance just delays the moment throttling engages until the underlying database is
  already in trouble, instead of smoothing load before it gets there.

Direct answer

Reduce connection count without hurting throughput (how much work the system gets done per second) or latency by putting a pooling layer between the serverless functions and the database, so many short-lived invocations share a small, bounded set of real database connections instead of each one opening, and often barely using, its own. Which specific pattern to use, an external pooler, a managed proxy, or connection reuse across invocations, depends on how much control exists over the runtime and how tolerant the workload is of each option's trade-offs.

How to think about it

External pooler in transaction-pooling mode (PgBouncer or equivalent): multiplexes many function-invocation connections onto a small real connection pool at the database, the mode that actually absorbs a connection storm (a burst of many new connections opening at once, faster than they can be closed and reused), at the cost of session-level features not surviving between transactions, prepared statements and session-level SET in particular.

Managed proxy, verified against AWS's current documentation for Amazon RDS (Relational Database Service) Proxy: pools and reuses connections to the database, queues or throttles connection requests it cannot serve immediately rather than letting them hit the database directly, and can shed load by rejecting requests once configured limits are reached, protecting the database from an oversubscription spike instead of passing it straight through. A specific, easy-to-hit trade-off: RDS Proxy pins a client's session to one specific underlying database connection once that session does something that cannot safely be shared, a prepared statement, certain session-level state changes, or any single statement over 16 KB of text, and once pinned, that connection stops being multiplexed for the rest of the session. A serverless workload issuing many prepared statements can silently defeat the pooling benefit it was added for, check the driver's prepared-statement behavior specifically. Worth knowing for Postgres specifically: RDS Proxy does not currently support a session-pinning filter mechanism some other engines get, so there is less fine control over what triggers pinning on Postgres than on some other engines.

Connection reuse within the execution environment. Many serverless runtimes reuse the same execution environment, and therefore the same already-open connection, across consecutive invocations if the connection is initialized outside the per-invocation handler and the environment is not cold-started fresh each time. This needs no proxy at all for moderate volume, but provides no protection against a genuine concurrency spike, many simultaneous cold starts each still open their own connection at once, so it complements pooling rather than replacing it at real scale.

A billing gotcha worth knowing on usage-billed engines, verified against AWS's own guidance: a connection pool's own health-check or keep-alive queries, a lightweight query sent periodically to verify a pooled connection is still alive, are themselves billable queries on a per-query-billed engine. Tune health-check frequency down rather than assuming pooling is automatically cost-neutral just because it reduces connection count.

Worked example

The real connection-storm demonstration, executed against a live PostgreSQL 16 instance: Postgres configured with max_connections = 8 (a small cap chosen to make the failure reproducible quickly) and the default superuser_reserved_connections = 3, a non-superuser application role connecting, since that is what a real serverless function's database credential actually is, five long-running connections opened concurrently to simulate a burst of serverless invocations, then one more attempted:

FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

That is the database's own real error text for this exact setup: a non-superuser role can use at most max_connections minus the reserved pool, five connections here, so the sixth fails immediately with this specific message rather than the server's raw eight-connection cap being reached yet. If every slot including the reserved three is exhausted instead, for example by superuser connections filling all eight, the server reports the more generic FATAL: sorry, too many clients already. Either message is precisely the failure mode a pooling proxy in front of the database prevents, by queueing or shedding the storm at the proxy instead of letting every invocation hit the database's connection limits directly.

Trade-offs and pitfalls

Adding a pooler but leaving it in session mode provides little multiplexing benefit, storms still reach the database's connection cap almost as easily as without one. Prepared-statement-heavy code can silently defeat a managed proxy's pooling through pinning, with no obvious error, just a gradual degradation back toward one connection per session. Assuming connection reuse across serverless invocations is guaranteed is a mistake, cold starts and platform-level environment recycling make it a best-effort optimization, not a hard guarantee, so it cannot be the only mitigation for a real concurrency spike.

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.