Database Performance Tuning and Scaling Questions

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

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

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

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

Intermittent errors plus a pool occasionally hitting its max means something is consuming connections faster than the pool releases them. The fastest way to tell which of the four causes it is, a leak, an undersized pool, slow queries, or a real traffic spike, is to look at the database's own view of its connections, not just the application's pool metrics, because the database always knows exactly how many connections are open, what state each one is in, and how long it has been there.

How to think about it

Metrics and logs to check: pg_stat_activity (state, query, query_start, xact_start, wait_event), specifically filtered to state = 'idle in transaction' for leaks; pg_stat_database.numbackends against the configured connection limit; application-side pool metrics (checked-out count, wait-for-a-connection time), which distinguish a database-side leak from legitimately being at capacity; and slow-query logs to catch the second case directly.

A real, pinned demonstration of the query pattern: a session holding an open transaction and a normal active query, distinguished by transaction age versus query age.

 pid | state  |    xact_age     | since_state_change |            last_query
-----+--------+------------------+---------------------+-----------------------------
  60 | active | 00:00:08.595573 | 00:00:08.595572    | BEGIN; SELECT * FROM orders...

Here xact_age and since_state_change matching each other on a session that began with BEGIN and has been open for seconds, on a table where that lookup should take milliseconds, is exactly the shape a leaked or stuck transaction produces; in a genuine idle-in-transaction leak, state reads idle in transaction with no query running at all, distinguishing it clearly from case two above where a query is genuinely still executing.

Parameter tuning once the cause is known, since the fix is usually a parameter, not just "add more connections." On the application pool side (HikariCP-style naming; PgBouncer, a separate pooling proxy that sits in front of Postgres, exposes equivalent settings under different names): connectionTimeout, how long a caller waits for a pool slot before failing fast, should be short enough that callers fail visibly instead of queuing invisibly and turning an incident into a mystery hang; idleTimeout and maxLifetime, recycling connections instead of holding them forever, which also self-heals a leaked-but-recycled connection faster; maxPoolSize, which should follow a sizing formula grounded in the database's own CPU core count (roughly (core_count x 2) + effective_spindle_count, with throughput on typical hardware tending to plateau around 50 total connections), not get raised reflexively mid-incident, raising it while queries are genuinely slow just moves the bottleneck onto the database's own CPU or IO. On PgBouncer specifically, server_idle_timeout and switching to transaction-mode pooling are the equivalent levers.

On a managed cloud database you do not have OS-level access, so the same triage happens entirely through the engine's own stats views plus the vendor's connection-count monitoring metric; the diagnostic logic is identical, only the access path changes.

Worked example

Immediate remediation, matched to the cause: for a leak, identify and terminate the oldest idle-in-transaction sessions with pg_terminate_backend to free slots right now while the code fix ships; for slow queries, consider pg_cancel_backend (soft) or pg_terminate_backend (hard) on the worst offenders to shed load, and lower statement_timeout at the pool or session level so future slow queries fail fast instead of pinning a connection indefinitely; for an undersized pool with genuine headroom on the database server, raise maxPoolSize a bounded amount as a stopgap, then re-run the sizing formula properly; for a real traffic spike, this is the one case where adding capacity, more replicas or a pooler in front, is the correct immediate lever rather than a code change.

Trade-offs and pitfalls

Raising the pool size as a reflexive first response conflates the four causes, and can make the slow-query case worse by adding more concurrent load onto an already saturated database. Killing sessions is a blunt immediate tool; always confirm the sessions being killed are the leaked or stuck ones, not a legitimate long-running batch job. Terminating a session mid-write can leave the application in an inconsistent retry state if it does not handle the resulting connection error gracefully, pair any forced termination with application-level retry and idempotency, not treat it as a silent fix on its own.