Database Performance Tuning and Scaling Questions

Direct answer

Key the retention policy by tenant (and by data category within a tenant), not by one
global rule, tier storage by data age so cost tracks how data is actually accessed
instead of paying hot-tier prices for data nobody queries, and make the cold tier
queryable through the same interface so retention doesn't turn into "the analyst has to
go find this in a different tool."

Design

Tenant isolation in the policy. Store retention rules per tenant, and per category
within a tenant where categories genuinely differ (audit logs may need years of
retention under a regulation that raw clickstream events don't). Evaluate each tenant's
rule independently in the purge job, rather than one cron job with a single hardcoded
cutoff. This matters twice: correctness (tenant A's multi-year regulatory retention must
never be truncated by tenant B's 30-day default) and blast radius (a bug in one tenant's
rule shouldn't be able to expire another tenant's data).

Tiering by age. Hot (recent, fast local storage, queried constantly) moves to warm
(older, cheaper object storage, queried occasionally) moves to cold or archive tier
(oldest, cheapest, rarely queried, possibly with retrieval latency or per-retrieval
cost). Movement between tiers is driven by age under the same per-tenant policy, not a
manual decision.

Regulatory constraints per tenant. Represent both a floor (certain categories under
regulation cannot be deleted before a minimum period, regardless of tenant preference)
and a ceiling (data-minimization rules requiring deletion after a maximum period) per
tenant and category. The automated purge job must refuse to delete anything still inside
its regulatory floor even if a tenant's own setting requests shorter retention: the
regulation binds, not the tenant's preference.

Transparent access to cold data. The query layer needs to reach cold-tier data
without a manual restore step: either a view spanning hot, warm, and cold storage where
the query planner decides where to look based on the requested date range, or a
federated/external-table capability querying the archive tier directly, so a query
spanning both a recent and a historical range doesn't require two separate tools or a
support ticket.

Worked example: tiering's storage-cost effect

For one tenant generating 50 GB/day, retained one year under hot (0 to 30 days), warm
(31 to 120 days), and cold (121 to 365 days) tiers:

(Hot's 30 days, plus warm's 90 days, plus cold's 245 days sum to 365, the full one-year
window these tiers are meant to cover. Cold spans day 121 to day 365 inclusive, which is
245 days, not 275.)

Using illustrative per-gigabyte-month prices (local fast storage $0.115, standard object
storage $0.023, archive-tier storage $0.004):

all-hot:  18250 GB * $0.115            = $2098.75/month
tiered:   1500*0.115 + 4500*0.023 + 12250*0.004
        = 172.50 + 103.50 + 49.00      = $325.00/month
savings:  $2098.75 - $325.00 = $1773.75/month  (about 85% reduction)

Keeping all 18,250 GB on hot-tier pricing costs roughly six and a half times what the
tiered layout does, for data that, by the retention design itself, is accessed less and
less as it ages past 30 days. The exact dollar figures are illustrative; the ratio between tiers is what
generalizes.

Trade-offs and pitfalls

• A purge job that deletes on schedule without checking both the tenant preference and
  the regulatory floor is a compliance incident waiting to happen; the floor check has to
  be unconditional, not something a tenant setting, or an engineer under time pressure,
  can accidentally bypass.
• Cold-tier retrieval is usually slower, and archive-class storage sometimes carries a
  per-retrieval cost; a query spanning hot and cold ranges will have visibly bimodal
  latency, communicate that to users as expected behavior rather than letting it surface
  as unexplained "sometimes slow" bug reports.
• Tiering by age alone ignores actual access pattern; a tenant that regularly re-queries
  year-old data for compliance audits gets a poor experience if "old" always means "cold
  and slow" regardless of how often they actually touch it. Consider an access-frequency
  signal alongside age for tenants with unusual patterns.

Direct answer

Layer the defense: detect resource usage per tenant, contain with logical throttles
first because they're cheapest and fastest to roll back, escalate to physical isolation
only for genuinely disproportionate tenants, and back the whole thing with per-tenant-
class SLA (service-level agreement, a stated performance guarantee) commitments and a
cost model that makes "move to dedicated capacity" a real lever rather than a punishment.

Architecture

flowchart TD
    T[Tenant request] --> TAG[Tag query with tenant_id]
    TAG --> MON[Per tenant resource monitor]
    BATCH[Cross tenant reporting job] --> MON
    MON --> CHECK{Over fair use threshold repeatedly?}
    CHECK -->|No| SHARED[Shared cluster: standard tier]
    CHECK -->|Yes| ESC[Offer dedicated tier]
    ESC --> DEDICATED[Dedicated instance: premium tier]
    SHARED --> THROTTLE[Per tenant rate limit and statement timeout]
    DEDICATED --> SLA[Per tenant class SLA guarantee]

Detection. Attribute resource usage to a tenant, either by tagging every connection
or query with a tenant identifier (Postgres will show a tagged application_name in
pg_stat_activity, so usage can be joined back to a tenant) or by giving each tenant its
own connection pool (a shared, reusable set of already-open database connections that requests
borrow from and return instead of opening a new one each time) or role, so OS-level accounting can
separate them. A real, common
trigger scenario doesn't look like one loud tenant: it's a cross-tenant reporting job,
something like a nightly "generate this week's report for every account" batch that
scans and joins across many tenants in a single run. That job belongs to no single
tenant, so a naive per-request tenant tag won't flag it; detection has to separately
watch for platform jobs whose blast radius is everyone, not just single-tenant spikes.

Logical isolation. Schema-per-tenant or a shared schema with a tenant_id column,
paired with per-tenant limits enforced at the application or connection-pool layer:
capped concurrent queries, a tenant-specific statement_timeout. Cheapest to build and
revert. Limitation: throttling caps concurrency, not the shared resource's total
capacity, so a tenant staying within its concurrency cap can still exhaust shared I/O
bandwidth or the shared buffer pool (the database's in-memory page cache) if its queries
are individually expensive enough.

Physical isolation. Move the worst, persistently offending tenants to a dedicated
instance or read replica (a synced, read-only copy of the database that offloads read traffic from the
primary). Fully solves the shared-resource problem for everyone else, at
a real operational cost: more instances to patch and monitor, and a routing layer that
has to send each tenant's traffic to the right place.

Resource limits. Container/OS-level cgroup limits (CPU shares, I/O weight) if
tenants run in separate processes. Database-level equivalents: per-role
statement_timeout and work_mem caps, and, with an extension, I/O throttling. A
governor pattern where a tenant that exceeds its allotment gets queued or served a
degraded (e.g. cached, slightly stale) response rather than allowed to starve others.

Cost and billing. Tie the isolation tiers to pricing. A standard tier runs on the
shared cluster with logical throttles and a best-effort SLA. A premium tier gets a
dedicated instance and a per-tenant-class SLA guarantee, a specific stated p99
latency (the response time slower than 99% of requests, a worst-case-ish bound) and uptime number
that tenant is paying for. This gives you an objective,
revenue-positive trigger for escalation: a tenant consistently exceeding the shared
tier's fair-use threshold gets offered the paid isolated tier, instead of engineering
absorbing the cost of accommodating them for free indefinitely.

Worked example: sizing the fair-use threshold

$\text{fair\_share} = \frac{\text{cluster\_IOPS}}{\text{tenant\_count}} = \frac{20{,}000}{200} = 100 \text{ IOPS/tenant}$

(IOPS: input/output operations per second, a measure of storage throughput.) A noisy
tenant observed consuming 14,000 IOPS is consuming 14000/20000 = 70% of the entire
cluster's capacity, 140 times its fair share, degrading the other 199 tenants who are
collectively left fighting over the remaining 30%. That gap, 140x fair share versus a
threshold you might set at, say, 5 to 10x, is what a detection rule alerts on; it's large
enough that a single noisy tenant is unambiguously the cause, not normal variance.

Trade-offs and pitfalls

• Logical throttles are fast to ship but don't protect against a tenant that stays within
  its concurrency cap while running individually expensive queries; watch actual resource
  consumption, not just request counts.
• Physical isolation is the strongest guarantee and the most expensive to operate;
  reserve it for tenants whose usage justifies it, not as a default.
• A billing model that treats isolation purely as a cost center misses the upsell
  opportunity; framing the premium tier's SLA as something tenants pay for turns
  engineering effort into a product feature.
• Detecting only per-request tenant spikes misses cross-tenant batch jobs entirely; a
  reporting job that touches every tenant at once needs its own monitoring category.

Direct answer

Every open connection to a relational database consumes a real operating-system process (or thread) and memory on the server, and there is a hard configured cap on how many can exist at once. Without pooling, each request opening its own new connection pays a TCP handshake plus authentication on every single request, and under any real concurrency the application either overwhelms that cap or spends most of its time on connection setup instead of doing work. A connection pool holds a small set of already-open, already-authenticated connections and hands them out to callers, so the expensive setup happens once and the database only ever sees a bounded number of clients.

How to think about it

What actually breaks without one. In Postgres, each new connection is a forked backend process with real per-process memory overhead. A burst of naive per-request connections either queues at the accept layer or gets rejected outright once the server's connection cap is hit, a hard failure, not a graceful slowdown.

What a connection storm is. A spike where many clients open new connections at once, faster than they close. Two classic triggers: a deploy or autoscaling event starting many application instances simultaneously, each opening its own pool, and a downstream slowdown that makes queries take longer, so callers pile up holding connections instead of releasing them, which looks like "we need more connections" but is really "connections are being held too long." Mitigation is a pooling proxy in front of the database (PgBouncer for Postgres, or a managed equivalent) that multiplexes many client-side connections onto a much smaller, bounded set of real database connections, and queues or sheds load once that bound is hit, rather than letting the database itself absorb the storm.

Session vs transaction pooling, verified against PgBouncer's own current documentation:

A model-serving endpoint issuing one query per inference request is exactly the profile transaction pooling is built for: short, independent, no session state needed. An extract-transform-load (ETL) job that opens a session, sets session-level parameters once (like a statement timeout or search path), and then runs many statements over minutes needs session pooling, or it has to re-issue those SET statements inside every transaction under transaction pooling.

Sizing rules of thumb. This is not "as many connections as application threads." The database has a fixed number of CPU cores; once queries running concurrently equal that core count, more connections add queueing and per-connection memory overhead instead of throughput. A widely used starting formula, from HikariCP's own published connection-pool sizing guidance:

where effective spindle count accounts for genuine disk-wait headroom, usually 0 or small on modern SSD-backed cloud storage, since there is little seek-time blocking left to hide behind extra connections. That same guidance reports Postgres throughput plateauing around 50 connections on typical hardware: past a fairly small number, more connections make things worse, not better. Size the pool small, and size it per service instance: if 20 application pods target one database and the formula says 20 connections total, each pod's local pool needs roughly one, not 20, and this arithmetic has to be redone separately for every distinct target a service talks to, a primary and its replicas are separate budgets, not one shared number.

Worked example

A real, reproducible demonstration of what exhaustion actually looks like, executed against a live PostgreSQL 16 instance: Postgres started with max_connections = 8 and the default superuser_reserved_connections = 3, a non-superuser application role connecting (the realistic case, since a real service authenticates as its own role, not as the database superuser), five long-running connections opened concurrently to simulate sustained load, then one more connection attempted:

$ psql -U app_user -c "SELECT 1;"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: 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, not a simulated one, and it is more specific than a blanket "too many clients": a non-superuser role can use at most max_connections minus the reserved pool, five connections here, so the sixth fails immediately with this message, before the server's raw eight-connection cap is ever reached. Only once every slot, including the three reserved ones, is actually exhausted, for example by superuser connections filling all eight, does the server instead report the more generic FATAL: sorry, too many clients already. The two messages tell you different things: the reserved-connections message means an application workload hit its own quota with the reserved pool still intact, while the generic message means the server is genuinely out of slots for everyone, including superuser access needed to intervene. This is exactly the failure a pooling proxy prevents, by queueing or shedding a storm at the proxy layer instead of letting every caller hit either cap directly.

Trade-offs and pitfalls

A pool sized too large can crash the database under its own weight (memory, context switching) instead of protecting it. A pool sized too small moves the real bottleneck from "the database is slow" to "requests are queued waiting for a pool slot," which shows up as application-level latency, not database-level slow queries, a genuinely different diagnostic picture. Switching pool modes without checking a client driver's own assumptions is the single most common cause of a transaction-pooling outage, always verify the driver's guidance before changing modes. Pooling solves the CPU and memory overhead of connection setup; it does nothing for a genuinely slow query still holding a connection for its full duration, making the pool bigger to compensate just hides that problem for longer.

Approach

Native Postgres materialized views do not support incremental refresh, REFRESH MATERIALIZED VIEW, with or without CONCURRENTLY, always recomputes the entire result. To hit a 10-minute freshness target over a dataset ingesting about 1 TB per day, build incremental refresh directly: a trigger-maintained summary table that updates only the rows affected by each write, so refresh cost scales with the volume of change since the last update, not with total dataset size.

The source table should itself be partitioned by time at this ingest rate (the same pattern used for retention). Combined with the trigger, a single write never touches a whole day's partition to update the summary, only the specific rows its own key maps to. If a full rebuild is ever needed, recovering from a bug, or onboarding this pattern onto existing history, doing it one partition at a time bounds the blast radius and lets the rebuild be resumed or parallelized instead of running one multi-terabyte query. Partition-level refresh is what makes that occasional full-rebuild cost tractable; it is not what keeps the steady-state refresh fast, the trigger is.

When a late-arriving correction changes a row that was already aggregated, the trigger's update branch must remove the old value's contribution before adding the new one. A naive trigger that only handles inserts will silently under- or over-count once corrections start arriving, a very easy defect to ship and not notice until the numbers are visibly wrong.

Code

CREATE TABLE daily_customer_totals (
  customer_id int,
  day date,
  total_amount numeric(12,2) NOT NULL DEFAULT 0,
  n_orders int NOT NULL DEFAULT 0,
  PRIMARY KEY (customer_id, day)
);

CREATE OR REPLACE FUNCTION orders_incremental_refresh() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO daily_customer_totals (customer_id, day, total_amount, n_orders)
    VALUES (NEW.customer_id, NEW.created_at::date, NEW.amount, 1)
    ON CONFLICT (customer_id, day) DO UPDATE
      SET total_amount = daily_customer_totals.total_amount + EXCLUDED.total_amount,
          n_orders = daily_customer_totals.n_orders + 1;
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE daily_customer_totals
      SET total_amount = total_amount - OLD.amount, n_orders = n_orders - 1
      WHERE customer_id = OLD.customer_id AND day = OLD.created_at::date;
    INSERT INTO daily_customer_totals (customer_id, day, total_amount, n_orders)
    VALUES (NEW.customer_id, NEW.created_at::date, NEW.amount, 1)
    ON CONFLICT (customer_id, day) DO UPDATE
      SET total_amount = daily_customer_totals.total_amount + EXCLUDED.total_amount,
          n_orders = daily_customer_totals.n_orders + 1;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE daily_customer_totals
      SET total_amount = total_amount - OLD.amount, n_orders = n_orders - 1
      WHERE customer_id = OLD.customer_id AND day = OLD.created_at::date;
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_incremental
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_incremental_refresh();

Key points

The ON CONFLICT ... DO UPDATE upsert makes the insert branch safe under concurrent writers touching the same key. The update branch's subtract-then-add is what makes backfill corrections correct, the detail a naive implementation misses. An initial backfill over existing historical data must overwrite (DO UPDATE SET ... = EXCLUDED...), not skip on conflict, or any row already touched by the trigger before the backfill ran is left with an incorrect partial value forever, this exact bug was caught and fixed while building this demo: an initial ON CONFLICT DO NOTHING backfill left an undercount for every key the trigger had already partially updated.

Worked example

Real, executed output. A new $100.00 order inserted for an existing customer was picked up immediately with no full recompute, total_amount moved from 15,911.04 to 16,011.04 and n_orders from 55 to 56. Then a backfill correction, adding $50.00 to an existing order's stored amount:

UPDATE orders SET amount = amount + 50 WHERE id = 765;

The summary correctly reflected it, total_amount became 16,061.04 and n_orders stayed at 56, a correction to an existing order, not a new one. Verified directly against a fresh SUM/COUNT over the source rows for that same customer and day, which returned the identical 16061.04 / 56, confirming the incremental summary and a full recompute agree exactly.

Complexity

Each write costs O(1) additional work, one indexed upsert on the summary table's primary key, independent of total dataset size. A full rebuild, if ever needed, costs O(n) over whatever slice is rebuilt, which is why partition-level rebuilds bound that cost instead of paying O(n) over the entire multi-terabyte history at once.

Edge cases

Concurrent writers updating the same customer-and-day key simultaneously: ON CONFLICT DO UPDATE makes this safe at the row level since Postgres serializes conflicting upserts on the same key, but two concurrent update-branch subtract-then-add sequences on the same key are two separate statements each, not one atomic unit, so under heavy concurrent correction traffic on the same key this needs review for whether it should run inside a stricter transaction boundary. A delete of a row inserted before the trigger existed, with no corresponding insert having ever incremented the summary, would drive the summary negative, worth a floor check or a periodic reconciliation job. A correction that moves a row from one day bucket to another (changing created_at itself) is already handled correctly, since the trigger looks up the old day for the subtraction and the new day for the addition separately, but it is easy to get wrong if the day were stored as its own independent column instead of derived from created_at on each side of the trigger.

Trade-offs and pitfalls

This pattern is custom code per aggregation shape, unlike a native materialized view, every new summary needs its own trigger logic, that is the real cost of O(1) incremental refresh. Triggers add write-path latency and lock contention on the source table, every insert now also performs a summary-table upsert inside the same transaction, profile this under real write concurrency before assuming it is free. At very high write volume the trigger itself can become the bottleneck it was meant to avoid, since every source write now also pays for an extra upsert inside the same transaction; the usual next step up is a change-data-capture (CDC) pipeline, which reads the database's replication log to build the summary from a stream of row changes outside the source transaction entirely, trading the trigger's simplicity for a separate system to run and monitor. A bug in the trigger logic, as demonstrated above with the ON CONFLICT DO NOTHING backfill mistake, corrupts the summary silently while the source data stays correct, so this pattern needs its own periodic reconciliation check comparing the summary against a fresh aggregate on a sample, precisely because there is no database-native correctness guarantee the way a REFRESH-based materialized view has: however stale, a materialized view is always exactly correct as of some point in time, while a hand-rolled incremental one can be wrong at every point in time if the trigger logic has a bug.

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.