Busy database

A busy database is a database server spending its CPU on application work: building JSON and formatted strings, running business rules in stored procedures and triggers, calling a function for every row, looping with cursors, or crunching reports on the primary. The database is usually the one tier every request shares and the hardest one to scale, so when its CPU fills up, every query slows down, including the ones that did nothing wrong.

Picture it first: the bank manager’s desk

A small bank branch has six tellers and one manager. Only the manager can open the strongroom and approve withdrawals above ₹50,000. That takes her a minute a customer, and on a normal day she’s idle half the time.

Then head office adds jobs to her desk because “she has the ledger right there”: handwriting each passbook entry, working out interest for every account she touches, and compiling the monthly branch report, at 11 am. Each strongroom visit now takes four minutes. Customers who only needed a signature queue behind her desk. The branch hires two more tellers, and the queue gets longer: more people waiting for the same manager.

The fix isn’t a faster manager. Tellers print passbooks and work out interest on their own terminals, and the regional office compiles the report overnight from a copy of the ledger. The manager goes back to the work only she can do.

Map it back:

• The manager is your primary database: one per shard, the only node that takes writes, and slow and costly to replace with a bigger one.
• Tellers are stateless app servers: cheap, and you can add ten in a minute.
• Strongroom work is what only the database does well: finding rows through indexes, joining, aggregating, enforcing constraints, committing atomically.
• Passbooks and interest are formatting and per-row business logic.
• The monthly report is analytics on the OLTP primary; the regional office is a replica or a warehouse.

Why the database is the wrong place for spare work

Moving work into the database feels free: the data is right there, and nothing extra crosses the network. The price is that the work lands on the tier with the least room to spare.

• It’s shared. Every endpoint, service, cron job and analyst hits the same primary. Its CPU is a common pool, so a procedure that takes more than its share takes it from login and checkout.
• It’s hard to scale out. Add an app pod and you’re done. A relational primary is one machine that takes every write: you can buy a bigger instance, add read replicas that only serve reads, or shard, which is months of work. Azure’s guidance puts it plainly: databases have finite capacity to scale up, and scaling them horizontally isn’t trivial.
• It’s priced per core. Microsoft’s list price for SQL Server 2022 Enterprise is $15,123 per 2-core pack, so 16 cores cost 8 × $15,123 = $120,984 in licences before any hardware. Managed services bill by instance size, or by metered units such as Azure SQL Database’s DTUs.

Count CPU-seconds, not queries

Google’s SRE book warns that modelling capacity in queries per second misleads, because requests differ wildly in cost; measure it in CPU instead. A 16-vCPU database can supply at most 16 CPU-seconds of work every second. Demand is calls per second × CPU per call.

Take a Flipkart-style “My orders” endpoint whose SQL computes tax and discounts, formats rupees and dates, and builds the JSON reply. It costs 40 ms of database CPU per call, and the sale brings 500 calls a second:

order summary, built in SQL:   500 calls/s × 40 ms = 20 CPU-s/s
everything else on the primary:                       5 CPU-s/s
demand on a 16-vCPU primary:                         25 CPU-s/s  → 156% ✗

order summary, lean query:     500 calls/s ×  8 ms =  4 CPU-s/s
everything else:                                      5 CPU-s/s
demand:                                               9 CPU-s/s  →  56% ✓

The summary alone needs 20 cores on a 16-core box, though at 40 ms it would pass most code reviews. The other 32 ms per call moves to the app tier: 500 × 32 ms = 16 CPU-s/s, which eight pods of 4 vCPUs (32 in total) absorb alongside their existing 2 CPU-s/s, at 18 ÷ 32 = 56%. That assumes the app needs the same 32 ms; app code that doesn’t run a query per row usually needs less.

Why 156% isn’t “a bit slow”

Above 100%, work arrives faster than the cores can finish it, so the queue grows until clients time out. Below 100%, queueing still bends latency upwards. For a single queue, the textbook M/M/1 approximation is R = S ÷ (1 − U): response time is service time divided by the idle fraction. With a 5 ms query, 50% utilisation gives 10 ms, 90% gives 50 ms and 95% gives 100 ms. A 16-core server stays flat for longer and then turns more sharply; neither survives 100%.

That shared queue is why a busy database hurts endpoints that changed nothing: a 1 ms login lookup waits behind 40 ms summaries for the same cores.

Symptoms you’d see

• Database CPU pinned at 90–100% while app servers idle at 10–20%. Bytes sent stay modest: lots of processing, little data, the tell Azure’s guidance points to.
• Everything slows, not one endpoint. Primary-key lookups that took 1 ms take 20 ms. Latency on unrelated endpoints rises and falls with traffic to the heavy one.
• Throughput plateaus while latency climbs. In Azure’s load test, the version that formatted XML in SQL topped out at about 12 requests a second with average response times above 4 seconds, while database CPU and DTU use hit 100%.
• Adding app servers does nothing, or makes it worse: more pods open more sessions that compete for the same cores.
• Read replicas don’t help. A PostgreSQL hot standby is strictly read-only (not even temporary tables can be written), so procedures that write, and every trigger, stay on the primary.
• App connection pools run dry. By Little’s law, 500 calls/s × 0.041 s holds about 21 connections; when queueing stretches each call to 400 ms, it holds 200, more than most pools allow. See connection pooling.
• The bill climbs every quarter, because the standing fix is the next instance size.
• Slowdowns follow the calendar: the 11 am finance dashboard, month-end close, the start of a sale.

Why it happens

Each of these starts as a reasonable decision.

“Move the logic to the data”, taken too far

The principle is right for work that shrinks data: summing a million rows in SQL beats shipping them. It gets stretched to work that doesn’t shrink anything, such as formatting 20 rows you return anyway, where the database saves no bytes and spends its scarcest CPU.

Procedure-centric legacy apps

Many systems from the 2000s treated stored procedures as the API: every screen calls a procedure, and business rules live in thousands of lines of PL/SQL or T-SQL. Ownership keeps it there: a database team can change a procedure without an app release, and the app team won’t touch code it can’t test.

Display-ready queries

Developers write SQL whose result goes straight to the screen: concatenated names, dates and currency formatted for the locale, JSON built with json_build_object, XML with FOR XML. Azure lists this as a typical cause, along with a twist: teams fixing extraneous fetching sometimes push computation into the database to send fewer bytes.

Per-row functions, cursors and triggers

A scalar function in the select list (tax_rate(category)) runs once per row, and if it runs a query, that is a query per row inside the database: chatty I/O that never leaves the server. Cursor loops process a set one row at a time. Triggers multiply writes: an insert into orders fires an audit trigger, which updates a counter table, which fires another trigger, and every write now does several times the work the app asked for, invisibly.

Reports and analytics on the primary

The orders table is right there, so the finance dashboard runs a GROUP BY over 18 months of orders on the OLTP primary at 11 am. One such query can scan millions of blocks and hold cores for minutes. Analytics wants a different engine; see OLTP vs OLAP.

ORMs and computed properties

ORMs can translate a computed property into SQL expressions evaluated per row, sort on a function result, or generate one statement that joins and aggregates far more than the screen needs. Nobody wrote that SQL, so nobody reviews it.

How to detect it

Azure’s method is a good outline: measure how much time goes to database work, look at what the database does in that time, and suspect processing when you see heavy CPU but little data traffic. On one dashboard, that is database CPU high, app CPU low, and queries per second and bytes sent by the database flat.

Is the database on CPU or waiting?

In PostgreSQL, pg_stat_activity shows each session’s state and wait_event, which is NULL when it isn’t waiting. An active session with no wait event is running, which almost always means on a CPU. Sample it a few times at peak:

SELECT coalesce(wait_event_type || ':' || wait_event, 'CPU (no wait)') AS doing,
       count(*)                                                     AS sessions
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
GROUP BY 1
ORDER BY 2 DESC;

doing                  | sessions
CPU (no wait)          |       41
LWLock:BufferMapping   |        3
IO:DataFileRead        |        2
Lock:transactionid     |        1
(illustrative output, 16-vCPU server)

Forty-one sessions want a CPU on a 16-vCPU server, so about 25 sit in the run queue. Lock contention would show Lock waits instead, and a disk-bound scan IO:DataFileRead.

On Amazon RDS, Performance Insights draws DB load (active sessions) against a Max vCPU line equal to the instance’s vCPU count. AWS’s guidance is that load often above the line, with CPU as the main wait state, means the CPU is overloaded. It is the CPU-seconds arithmetic, drawn for you.

Which statements burn the time?

pg_stat_statements totals execution time, calls, rows and blocks for every normalised statement. Rank by total time to find where the database’s time goes, then read the ratios:

SELECT left(query, 38)                                     AS statement,
       calls,
       round(total_exec_time::numeric / 1000)              AS total_s,
       round(mean_exec_time::numeric, 1)                   AS mean_ms,
       round(rows::numeric / nullif(calls, 0), 1)          AS rows_per_call,
       round((shared_blks_hit + shared_blks_read)::numeric
             / nullif(calls, 0))                           AS blocks_per_call,
       round((100 * total_exec_time
             / sum(total_exec_time) OVER ())::numeric, 1)  AS pct_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

statement                              | calls     | total_s | mean_ms   | rows_per_call | blocks_per_call | pct_time
SELECT json_build_object($1, json_agg( |   720,000 |  29,520 |      41.0 |           1.0 |           1,205 |     71.2
SELECT date_trunc($1, created_at) AS d |        12 |   4,800 | 400,000.0 |         540.0 |       2,150,000 |     11.6
SELECT id, name, phone FROM users WHER | 3,600,000 |   2,520 |       0.7 |           1.0 |               4 |      6.1
INSERT INTO orders (user_id, subtotal, |   360,000 |   1,080 |       3.0 |           1.0 |              18 |      2.6
(illustrative output, one evening hour)

Read it like this:

• The summary is 71% of database time: 41 ms per call to return one row, touching about 1,200 blocks that turn out to be cache hits in the plan below. The time isn’t going to I/O; it’s going to computation.
• The finance report runs only 12 times an hour but reads over 2 million blocks each time: analytics on the OLTP primary.
• The users lookup is the victim, not the cause: 0.7 ms and four blocks per call.

Two caveats. total_exec_time is wall time inside the server, waits included; with track_io_timing on, subtracting shared_blk_read_time gets you closer to CPU time. And by default only top-level statements are counted, so a procedure is one opaque row. Set pg_stat_statements.track = all to see the statements nested inside functions, and track_functions = pl to fill pg_stat_user_functions with each function’s total_time and self_time.

On SQL Server and Azure SQL, Query Store does the same job: sys.query_store_runtime_stats keeps count_executions, avg_cpu_time (in microseconds) and avg_rowcount per plan, so rank by avg_cpu_time × count_executions. Azure SQL Database also charts CPU and DTU percentage, which Azure describes as a measure of how much processing the database performs.

Where inside the statement?

EXPLAIN (ANALYZE, BUFFERS) runs the statement and times each plan node. For the order summary:

Aggregate (actual time=38.410..38.412 rows=1 loops=1)
  Buffers: shared hit=1205
  ->  Index Scan Backward using orders_user_created_idx on orders o (actual time=0.031..0.912 rows=240 loops=1)
        Index Cond: ((user_id = 42) AND (created_at > (now() - '90 days'::interval)))
        Buffers: shared hit=245
Planning Time: 0.210 ms
Execution Time: 38.530 ms
(illustrative, COSTS OFF)

The index scan finds 240 rows in under a millisecond. The aggregate above it spends another 37.5 ms and touches 960 more blocks, calling a tax function and a discount function for each row and formatting the result. Another index won’t help; the plan is fine and the work is wrong. EXPLAIN ANALYZE also reports time spent in each trigger, and since PostgreSQL 17 a SERIALIZE option measures the cost of converting output to text.

What a trace looks like

In a distributed trace, a busy database is a fat database span under thin app spans. The giveaway is in the other traces: during the same minute, a request that does nothing heavy shows a cheap query taking ten or twenty times its normal duration. See observability: logs, metrics and traces.

Telling it apart from look-alikes

They overlap. A per-row function that runs a query is chatty I/O inside a busy database, and a report on the primary is both analytics in the wrong place and a noisy neighbour to checkout.

How to fix it

The principle: let the database do what only it can do (find, filter, join, aggregate, enforce constraints, commit atomically) and move everything else to tiers you can scale.

Move formatting and per-row maths to the app tier

Before: the database computes tax and discount per row, formats rupees and dates, and assembles the JSON.

-- BEFORE: tax_rate() and discount_for() each run a query per row,
-- and every byte of the reply is formatted inside the database
SELECT json_build_object('orders', json_agg(json_build_object(
         'id',     'ORD-' || lpad(o.id::text, 10, '0'),
         'placed', to_char(o.created_at AT TIME ZONE 'Asia/Kolkata', 'DD Mon YYYY, HH12:MI AM'),
         'total',  '₹' || to_char(o.subtotal * (1 + tax_rate(o.category))
                                  - discount_for(o.user_id, o.id), 'FM99,99,99,990.00'),
         'status', initcap(replace(o.status, '_', ' '))
       ) ORDER BY o.created_at DESC))
FROM orders o
WHERE o.user_id = $1
  AND o.created_at > now() - interval '90 days';

After: the database returns plain columns through the same index, and the app does the rest. The discount is computed once at checkout and stored on the order, instead of on every read.

-- AFTER: find and return; no per-row functions, no formatting
SELECT o.id, o.created_at, o.subtotal, o.category, o.discount, o.status
FROM orders o
WHERE o.user_id = $1
  AND o.created_at > now() - interval '90 days'
ORDER BY o.created_at DESC;

from decimal import Decimal, ROUND_HALF_UP

def order_view(o, tax):
    total = (o.subtotal * (1 + tax[o.category]) - o.discount).quantize(
        Decimal("0.01"), rounding=ROUND_HALF_UP)
    return {
        "id": f"ORD-{o.id:010d}",
        "placed": o.created_at.astimezone(IST).strftime("%d %b %Y, %I:%M %p"),
        "total": format_inr(total),                    # "₹12,34,567.50"
        "status": o.status.replace("_", " ").title(),
    }

def get_orders(user_id):
    tax = cache.get_or_load("tax_rates", load_tax_rates, ttl=300)  # ~40 Decimal rates
    rows = db.fetch_all(ORDERS_SQL, user_id)                        # the lean query above
    return {"orders": [order_view(o, tax) for o in rows]}

The 480 nested lookups (two per row for 240 orders) become one cached table read and a stored column, and the formatting runs on pods you can add in a minute. In Azure’s similar refactor, which moved XML formatting into the app, throughput rose from 12 to over 400 requests a second and average response time fell from over 4 seconds to just above 0.1.

You pay with more code to own, and rules that every renderer must share: if three services show order totals, put the calculation in one library or one service, or they will drift. Rewriting a legacy procedure needs tests that compare old and new output byte for byte (Azure’s refactor had to reproduce the exact XML). And the app tier needs the headroom to absorb the work.

Keep set-based work in the database

Don’t overcorrect. SELECT sum(amount) FROM payments WHERE merchant_id = $1 AND day = $2 over a million rows returns one number; pulling the rows to sum them in the app ships about 1,000,000 × 100 bytes = 100 MB. That’s extraneous fetching, and Azure’s guidance warns against exactly this trade. Move work out when it runs on rows you return anyway; keep it in when it shrinks the result.

Take reports off the primary

Read replica. Point dashboards and operational reports at a replica. You gain primary CPU for the work only the primary can do. You pay with replication lag and with conflicts: on a busy PostgreSQL standby, long queries get cancelled when replay can’t wait any longer, and turning on hot_standby_feedback to prevent that delays cleanup of dead rows on the primary, which can bloat its tables.

Warehouse. Stream changes with change data capture (CDC) into a columnar warehouse for finance and analytics. You gain an engine built for scans over months of data. You pay with a pipeline to run, lag of minutes or more, and a second copy of the truth. See OLTP vs OLAP and change data capture.

Precompute: materialised views and summary tables

A Swiggy-style partner app shows each of 200,000 restaurants its orders per hour today, polled every 30 seconds: 200,000 ÷ 30 ≈ 6,667 aggregations a second at dinner time. Compute it once a minute instead:

CREATE MATERIALIZED VIEW restaurant_hourly AS
SELECT restaurant_id,
       date_trunc('hour', placed_at) AS hour,
       count(*)                      AS orders,
       avg(prep_seconds)             AS avg_prep_s
FROM orders
WHERE placed_at >= date_trunc('day', now())
GROUP BY restaurant_id, date_trunc('hour', placed_at);

CREATE UNIQUE INDEX ON restaurant_hourly (restaurant_id, hour);

-- Every minute, from a scheduler; readers are not blocked
REFRESH MATERIALIZED VIEW CONCURRENTLY restaurant_hourly;

The dashboard now reads a few indexed rows, and the aggregation runs once a minute instead of 6,667 times a second.

You pay with staleness (up to a minute plus the refresh time) and refresh cost: PostgreSQL replaces the whole view on each refresh rather than updating it incrementally. CONCURRENTLY needs a unique index on plain columns covering every row, and only one refresh runs at a time. When the full refresh gets too heavy, keep a summary table that a worker updates incrementally from order events, and pay for idempotent counting and hot rows instead.

Cache, and queue the side effects

Cache what’s read far more than it changes: tax rates, category trees, a restaurant’s rendered menu; see no caching. You pay with staleness and invalidation.

Move side-effect triggers (audit copies, counters, notifications) into a queue. The transaction writes one outbox row; workers do the rest outside the primary (transactional outbox). You pay with eventual consistency, retries and idempotent consumers.

Scale the database last, not first

A bigger instance is a fine stopgap: no code changes, and it works this week. You pay every month, you hit the largest instance eventually, and with per-core licences, going from 16 to 32 cores of SQL Server Enterprise adds another 8 × $15,123 = $120,984. Sharding spreads the load but is a project, not a fix; see database scaling and scalability.

Compare the fixes

Verify the fix

Rerun the same load test. Watch database CPU and DB load against the vCPU line, the p99 of an unrelated endpoint such as login, and app CPU, which should rise: the work has landed where you can scale it.

When it’s fine

Logic in the database is sometimes exactly right:

• Aggregation that shrinks data. Sums, counts and GROUP BY over many rows return a few; that’s what the engine is built for.
• Integrity and atomic checks. Constraints, foreign keys and short conditional updates belong there. UPDATE wallets SET balance = balance - $2 WHERE id = $1 AND balance >= $2 debits a UPI-style wallet only if it has the money, in one round trip with no race.
• A lean procedure for atomicity. A tatkal-style seat allocation that locks, allocates and records the booking in one call is fine if it’s set-based, indexed and takes single-digit milliseconds.
• Small scale with headroom. If the primary peaks at 20% CPU, a working legacy procedure isn’t worth a rewrite yet. Alert on database CPU and revisit.

The test is CPU-seconds: logic in the database is fine while its cost at peak fits comfortably inside the primary’s headroom.

Cheat sheet

How it shows up in interviews

Busy database is rarely the headline; it’s the follow-up when a design leans on one relational primary: order-history and statement pages (a UPI transaction history with formatted amounts), food-delivery partner dashboards, discount rules at checkout during a flash sale, a ledger with month-end statements, and “can analytics query the orders database?”

The probes: “Your primary is at 95% CPU and the app servers are at 15%. What do you do?” “Where would you compute the discount?” “Finance wants a live revenue dashboard; can it run on the orders database?” Answer with CPU-seconds first, then say which tier each piece of work belongs on and what moving it costs.

How to say it in the interview

“A busy database is one doing the application’s work: formatting, business rules in procedures and triggers, per-row functions, reports on the primary. It’s the shared tier I can’t scale out, so I budget its CPU: 500 calls a second at 40 ms of CPU is 20 cores, on a 16-core box. I’d confirm it with wait events showing sessions on CPU, pg_stat_statements ranked by total time, and traces where cheap queries on unrelated endpoints slow down too. Then I’d keep filtering, joins and aggregation in SQL, move formatting and per-row maths to the stateless app tier, send reports to a replica or a warehouse, precompute hot aggregates, and push side effects onto a queue. A bigger instance only buys time.”

Isn’t it faster to compute next to the data? For work that shrinks the data, yes: a sum over a million rows ships one number instead of 100 MB. For work on rows you return anyway, like formatting or per-row tax, the database saves no bytes and spends its scarcest CPU.

Why not just add read replicas? Replicas take reads only. A PostgreSQL standby refuses every write, so procedures that write and all triggers stay on the primary. Replicas also lag, and long reports on a busy standby can be cancelled. They’re the right home for reports, not a cure for CPU burned by writes.

When would you keep a stored procedure? When it buys atomicity in one round trip and stays lean: debit a wallet only if the balance covers it, or allocate a seat under a row lock. What I’d remove is formatting, and rules that loop row by row.

How do you prove it’s CPU and not locks or I/O? Wait events. Active sessions with no wait event are on CPU; Lock waits mean contention; DataFileRead means disk reads. On RDS, load above the Max vCPU line with CPU as the top wait is CPU overload. Then rank pg_stat_statements by total time.

Won’t moving logic out fetch more data? It can, which is extraneous fetching. I move only per-row work on rows the app receives anyway. If the app would need a million rows to compute one number, that work stays in the database or goes into a precomputed table.

Where to go next

• Extraneous fetching: the ditch on the other side of “move it out”.
• Chatty I/O: per-row calls, inside or outside the database.
• Monolithic persistence and no caching: neighbours that load the same primary.
• Database scaling: replicas, bigger instances and sharding.
• Query execution plans and database indexes: when the problem is the query.
• OLTP vs OLAP, warehouses and columnar storage: where reports belong.
• Connection pooling, observability and scalability: the pools, signals and scaling model used here.

References and further reading

1. Busy Database antipattern (Azure Architecture Center)learn.microsoft.comWhy processing in the database hurts (shared, metered, hard to scale out), its usual causes, detection steps, and a load test that went from 12 to over 400 requests per second after moving XML formatting into the app.
2. PostgreSQL documentation: pg_stat_statementspostgresql.orgPer-statement calls, total and mean execution time, rows and block counters, and pg_stat_statements.track = all for statements nested inside functions.
3. PostgreSQL documentation: The Cumulative Statistics Systempostgresql.orgpg_stat_activity’s state and wait_event columns (NULL when a backend isn’t waiting), wait events such as ClientRead, DataFileRead and transactionid, and pg_stat_user_functions.
4. PostgreSQL documentation: EXPLAINpostgresql.orgANALYZE with buffer counts (automatic with ANALYZE in current versions), trigger statistics, and the SERIALIZE option that measures output conversion.
5. PostgreSQL documentation: REFRESH MATERIALIZED VIEWpostgresql.orgA refresh replaces the whole contents; CONCURRENTLY keeps readers unblocked but needs a unique index, and only one refresh runs at a time.
6. PostgreSQL documentation: Hot Standbypostgresql.orgStandby sessions are strictly read-only, and long queries can be cancelled by replay conflicts or, with hot_standby_feedback, delay cleanup on the primary.
7. sys.query_store_runtime_stats (SQL Server and Azure SQL)learn.microsoft.comPer-plan count_executions, avg_cpu_time (microseconds) and avg_rowcount, for ranking statements by CPU.
8. Amazon RDS Performance Insights: Maximum CPUdocs.aws.amazon.comThe Max vCPU line on the DB load chart, and why load above it with CPU as the main wait state means the CPU is overloaded.
9. Google SRE book, chapter 21: Handling Overloadsre.googleWhy queries per second misleads as a capacity measure, and modelling capacity and request cost in CPU instead.
10. SQL Server 2022 pricing (Microsoft)microsoft.comPer-core licensing: Enterprise edition lists at $15,123 per 2-core pack.