Measuring a Single Application's Footprint on a Shared PostgreSQL VM

When several applications share a PostgreSQL instance, "who consumes what?" is a surprisingly stubborn question. The raw metrics are aggregate: VM-level CPU doesn't distinguish between tenants, and top on the database host just shows a pile of postgres backends. This post walks through a methodology to produce a defensible per-tenant estimate of CPU, RAM, and storage — using only tools that are free, standard, and almost always already installed.

The context: two production Laravel-style workloads, Application A and Application B, sharing one Postgres VM. Before committing to migrate Application A to a dedicated instance, we wanted a real number for its footprint, not a guess.

The principle: two independent angles

The core idea is to measure from two angles and reconcile:

1. Database side — pg_stat_statements attributes query time to specific databases / roles. This tells you Application A's share of database work.

2. OS side — atop / sar on the VM gives CPU history. This tells you how much work the VM is actually doing.

Multiply the two and you have a proportional estimate. Then cross-check with an absolute calculation from pg_stat_statements alone. Two methods should bracket the truth; if they don't, one of your assumptions is wrong.

Step 1 — Enable pg_stat_statements without disruption

pg_stat_statements is almost always the right starting point, but requires the library in shared_preload_libraries, which needs a server restart. Before scheduling one, check — it's often already loaded on shared production VMs:

SHOW shared_preload_libraries;

If the extension library is already preloaded (frequently the case on managed Postgres: RDS, Azure DB for PostgreSQL, Cloud SQL all enable it by default), activating the view in your app's database is a single-line DDL, no restart, no coordination:

CREATE EXTENSION pg_stat_statements;

Harmless, instant, reversible with DROP EXTENSION. The underlying library has been collecting data cluster-wide since the last server restart — once the view exists, you can read it.

If you also want to inspect cache pressure later, add:

CREATE EXTENSION pg_buffercache;

Step 2 — Isolate your tenant's queries

pg_stat_statements has a dbid column referencing each database's OID. One JOIN isolates your application:

SELECT s.calls,
       round(s.total_exec_time::numeric, 2) AS total_ms,
       round(s.mean_exec_time::numeric, 2)  AS mean_ms,
       left(s.query, 120) AS query
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
WHERE d.datname = 'app_a_db'
ORDER BY s.total_exec_time DESC
LIMIT 20;

What the output tells you

In our case, the top 5 fingerprints were variants of SELECT SUM(stock) FROM items WHERE parent_id = $1 — 26 million calls, cumulative 188,000 seconds. A textbook Eloquent N+1: the ORM iterating over parent records and firing a sum query per child.

The top 20 also surfaced:

• A polymorphic comments lookup called 117 million times (missing with('comments') eager-load).

• A reference-table lookup (order_modes WHERE slug = ?) called 100 million times (should be cached in application memory).

• A Sanctum token UPDATE ... last_used_at called 5 million times (expected; no action).

The most valuable output of this step is often not a VM sizing number — it's a ranked list of application bugs. Finding the list takes minutes; fixing the top entry takes a day of engineering work and often reduces database load by an order of magnitude. That's usually cheaper than any infrastructure change.

Step 3 — Compute the share of total query time

Aggregate by database and take percentages over the window:

SELECT d.datname,
       (sum(s.total_exec_time) / 1000)::bigint AS total_seconds,
       round((100.0 * sum(s.total_exec_time)
              / sum(sum(s.total_exec_time)) OVER ())::numeric, 2) AS pct_of_vm
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
GROUP BY d.datname
ORDER BY total_seconds DESC;

Result for our case: Application A = 61.5% of all query time on the VM over 54 days of Postgres uptime.

Check the accumulation window first so you know what the number represents:

SELECT pg_postmaster_start_time(),
       now() - pg_postmaster_start_time() AS uptime;

Longer windows give stabler numbers. Hours of data after a recent restart is too little to trust.

Key input #1: Application A performs 61.5% of the DB query work.

Step 4 — Get VM-wide CPU history from what's already installed

Most production Linux VMs already persist CPU history without anyone setting it up explicitly. Check in order:

1. atop — ls /var/log/atop/ should list daily binary snapshot files. Typical retention: 28–30 days.

2. sar / sysstat — /var/log/sa/ with saNN files. Typical retention: 7–28 days.

3. Cloud provider metrics — Azure Monitor, CloudWatch, etc. via the cloud console.

4. Prometheus / Grafana — if node_exporter is on :9100, something is probably scraping it.

We had atop logs — 30 days worth. atopsar -c reads them.

A quirk worth knowing: atopsar's "all" line is summed across cores

The "all" aggregate row in atopsar -c reports summed per-core percentages. On an 8-core VM the scale is 0–800%, not 0–100%. Missing this gives you utilization numbers off by a factor of nproc.

Verify by checking the column header (atopsar -c -r <file> | head -15). For modern atopsar:

cpu %usr %nice %sys %irq %softirq %steal %guest %wait %idle

True VM utilization:

util% = 100 − (%idle_summed / core_count)

30-day average VM utilization (one-liner)

for f in /var/log/atop/atop_*; do
  atopsar -c -r "$f" 2>/dev/null
done | awk '\(2=="all" {i+=\)11; n++} END {printf "30d avg util=%.1f%%\n", 100-(i/n/8)}'

(Replace /8 with your core count.)

Result for our case: 9.3% average, 43% peak. The VM is lightly loaded.

Key input #2: The VM uses ~9% of its CPU capacity on average.

Step 5 — Reconcile two framings

Framing A — proportional share

Assume VM CPU is mostly Postgres (true on a dedicated DB VM):

App_A_CPU ≈ VM_avg_util × App_A_query_share
         ≈ 9.0% × 0.615 = 5.5% of VM capacity

Framing B — absolute share from query time

Treat total_exec_time as CPU-seconds consumed:

App_A_CPU = total_exec_time / (uptime_seconds × core_count)
         = 603,704 / (54 × 86,400 × 8) = 1.6% of capacity

Why they differ

Framing B only counts execution time of user queries — it excludes autovacuum, WAL writer, checkpointer, background workers, OS overhead, monitoring agents. Framing A captures everything the VM does, but distributes non-query CPU in the same ratio as query CPU (an approximation).

The real answer is between the two bounds. For our case: Application A uses 2–6% of the 8-core VM's capacity on average, ~0.15–0.45 cores.

Having two bounds is load-bearing. A single estimate that nobody can challenge isn't credible; a bracketed range with the reasoning exposed survives pushback from DBAs and platform teams.

Step 6 — Don't forget cache pressure

CPU is rarely the only concern on a database VM. pg_buffercache reveals who owns shared_buffers:

SELECT d.datname,
       count(*) * 8 / 1024 AS mb_in_shared_buffers,
       round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct_of_cache
FROM pg_buffercache b
JOIN pg_database d ON b.reldatabase = d.oid
GROUP BY d.datname
ORDER BY mb_in_shared_buffers DESC;

In our case: Application B held ~92% of shared_buffers; Application A held ~2%. The same two tenants looked very different through the cache lens: B has a large working set and a few heavy queries, A has a small working set with millions of short queries.

Two tenants can each look like "the heavy one" from different angles. The noisy-neighbor story for RAM is the opposite of the story for CPU.

Pair this with cache hit ratio per database to know whether the cache footprint is enough:

SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_ratio_pct
FROM pg_stat_database
WHERE datname IN ('app_a_db', 'app_b_db');

99% = cache is doing its job; < 95% = the database is genuinely cache-starved.

Step 7 — Translate into VM sizing

The pattern we used:

For our case, 4 vCPU / 32 GB / Premium SSD P20 covered current load with 10–15% utilization and 2× growth headroom. A 2 vCPU / 16 GB option became viable only after the N+1 fixes identified in Step 2.

Takeaways

1. pg_stat_statements + dbid filter is the single highest-leverage tool for multi-tenant Postgres investigations. It's built-in. The only cost is a one-time restart to preload the library, which has usually already happened.

2. Don't trust a single number. Two framings that disagreed by 3× told us the truth was in the middle. A single number is a guess with extra steps.

3. OS history is usually already there. Check atop, sar, cloud metrics, and Prometheus before installing anything. In our case, 30 days of data were sitting in /var/log/atop/ that nobody had queried.

4. atopsar "all" rows are summed across cores. Miss this and your utilization numbers are off by nproc. Always divide.

5. The answer to "move to a dedicated VM?" is often "fix the app first." The top five N+1 fingerprints accounted for more wasted CPU in our system than any infrastructure change could recover. Find them before you move anything.

6. Measure cache separately from CPU. The tenant dominating CPU may not be the tenant dominating RAM. Different pressure, different fix, different sizing implications.

Appendix — Copy-paste commands

PostgreSQL

-- Activate (library already preloaded)
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_buffercache;

-- Accumulation window
SELECT pg_postmaster_start_time(), now() - pg_postmaster_start_time() AS uptime;

-- Top queries for one tenant
SELECT s.calls, s.total_exec_time, s.mean_exec_time, left(s.query, 120) AS query
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
WHERE d.datname = 'app_db'
ORDER BY s.total_exec_time DESC LIMIT 20;

-- Tenant share of DB query time
SELECT d.datname,
       (sum(s.total_exec_time)/1000)::bigint AS total_seconds,
       round((100.0 * sum(s.total_exec_time)
              / sum(sum(s.total_exec_time)) OVER ())::numeric, 2) AS pct
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
GROUP BY d.datname
ORDER BY total_seconds DESC;

-- Cache composition
SELECT d.datname,
       count(*) * 8 / 1024 AS mb_in_shared_buffers,
       round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct_of_cache
FROM pg_buffercache b
JOIN pg_database d ON b.reldatabase = d.oid
GROUP BY d.datname
ORDER BY mb_in_shared_buffers DESC;

-- Cache hit ratio
SELECT datname, blks_hit, blks_read,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_ratio_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0','template1','postgres');

OS (atopsar — replace /8 with your core count)

# Column header check (do this first)
atopsar -c -r /var/log/atop/atop_YYYYMMDD 2>/dev/null | head -15

# 30-day average utilization
for f in /var/log/atop/atop_*; do
  atopsar -c -r "$f" 2>/dev/null
done | awk '\(2=="all" {i+=\)11; n++} END {printf "30d avg util=%.1f%%\n", 100-(i/n/8)}'

# 7-day peak
for f in $(ls -1 /var/log/atop/atop_* | tail -7); do
  atopsar -c -r "$f" 2>/dev/null
done | awk '\(2=="all" {u=100-(\)11/8); if (u>max) {max=u; t=$1}}
            END {printf "7d peak util=%.1f%% at %s\n", max, t}'

# Per-core breakdown
for f in $(ls -1 /var/log/atop/atop_* | tail -7); do
  atopsar -c -r "$f" 2>/dev/null
done | awk '\(2=="all" {u+=\)3; s+=\(5; w+=\)10; st+=\(8; id+=\)11; n++}
           END {printf "7d per-core avg: usr=%.1f%% sys=%.1f%% wait=%.1f%% steal=%.1f%% idle=%.1f%% util=%.1f%%\n",
                       u/n/8, s/n/8, w/n/8, st/n/8, id/n/8, 100-(id/n/8)}'

PgBouncer (if present — alternative to pg_stat_statements when you can't enable it)

psql -h <vm> -p 6432 -U <admin> pgbouncer -c "SHOW STATS;"
psql -h <vm> -p 6432 -U <admin> pgbouncer -c "SHOW POOLS;"

SHOW STATS returns total_query_time per database — the same per-tenant attribution as pg_stat_statements, available without a restart and without DDL.