Finding a Postgres Row-Lock Bottleneck with Prometheus + InfluxDB

As part of my SGU thesis work at Commsult Indonesia on Ontego Traces, I spent a good chunk of the project not writing load-test scripts or autoscaler logic, but wiring up the observability layer that made sense of everything those other pieces produced. This post is about that layer: why I split it into two backends instead of one, why it ran on a completely separate host from the service under test, and the actual detective work that took me from "something is slow" to "this exact row in this exact table is the constraint." I already covered the load-test numbers and the autoscaler behavior in other posts, so I will only reference those results briefly here for context and focus on the observability design and the diagnostic method itself.

Why one monitoring backend was not enough

Early on I tried pushing everything into a single time-series store and quickly ran into an impedance mismatch. k6, the load-testing tool driving the experiments, is built to stream fine-grained per-request metrics like http_req_duration and iteration counts to InfluxDB as a first-class output target. Infrastructure metrics are a different shape entirely: container CPU and memory from cAdvisor, and JVM heap, garbage collection, and HikariCP connection-pool stats from Spring Boot Actuator, both of which speak Prometheus's pull-based scrape model natively. Rather than forcing one tool to imitate the other's protocol, I let each side use the backend it was designed for and rendered both on the same Grafana dashboard, on the same time axis. That single decision turned out to matter more than any dashboard panel I built on top of it.

k6 metrics into InfluxDB

Every k6 run wrote directly to an InfluxDB instance using k6's native output flag, so response time percentiles, error rates, and throughput were available as soon as the test started, not after it finished. That mattered because Giamattei et al.'s 2024 survey of DevOps monitoring tools found that most pipelines only collect performance metrics after a test run completes, which is exactly the gap a live dashboard closes: an operator watching the run can see p95 climbing in real time instead of discovering it in a post-run report.

Infrastructure metrics into Prometheus

cAdvisor exposed container-level CPU and memory as a Prometheus scrape target, and Spring Boot Actuator exposed the same for the JVM heap, garbage collection pauses, and the HikariCP connection pool size and usage. Both were scraped on a short interval and federated into the monitoring host's own Prometheus instance, so an operator staring at the Grafana dashboard during a load test could watch application latency in one panel and container CPU climbing in the panel right next to it, on the same timestamp. That correlation is the entire point: a latency spike with flat CPU tells you something completely different from a latency spike with CPU pegged at 100%.

Why the monitoring stack lived on its own host

The application under test ran on a Google Cloud Platform instance with 4 vCPUs and 8GB of RAM running the Docker Swarm manager and the service containers. The load generator, InfluxDB, Prometheus, and Grafana all ran on a separate DigitalOcean droplet with 2 vCPUs and 2GB of RAM. This was a deliberate choice, not a cost-saving afterthought: an observability stack that competes with the service under test for the same CPU and memory budget corrupts its own measurements. If cAdvisor, Prometheus, and Grafana are eating CPU cycles on the same box being benchmarked, every latency number they report is partly measuring their own overhead. I verified cAdvisor's footprint on the application host stayed under 1% CPU, but that number is only meaningful because the heavier pieces, the load generator and the time-series databases, were never on that host to begin with.

Federating across a locked-down reverse proxy

Since Prometheus needs to scrape the application host's metrics endpoints but the two hosts sit on different cloud providers, I put an Nginx reverse proxy in front of the application host's Prometheus instance and restricted it to accept connections only from the monitoring droplet's IP address. The monitoring host's own Prometheus then federated from that proxy on a regular scrape interval, pulling in the cAdvisor and Actuator series without exposing the application host's monitoring endpoints to the open internet. It is a small piece of configuration, but it is the thing that makes the two-host split actually work end to end instead of just moving the problem.

Golden signals tell you something is wrong; they do not tell you where

With both backends live on one dashboard, the standard SRE framing of four golden signals, latency, traffic, errors, and saturation, was fully covered: k6 gave latency, traffic, and errors, while cAdvisor and Actuator gave saturation. But watching all four during a load test only told me the system was struggling. p95 climbing past 8 seconds and CPU sitting at a moderate level told me the bottleneck was not raw compute, but nothing in those four signals pointed at which downstream dependency was actually the constraint. That gap is exactly where live database and message-broker introspection had to take over.

Then live-sampling pg_stat_activity joined with pg_locks

With the broker cleared, I turned to Postgres directly. Every 3 seconds during a two-replica load test run, I queried pg_stat_activity joined against pg_locks to see exactly which backend sessions were active, which were waiting, and what they were waiting on. The pattern was immediate and consistent: 6 to 8 sessions were blocked at any given sample, every single one waiting on an exclusive tuple lock against the same row in a tenant_config table. The blocking query was always the same UPDATE statement, issued by every request in the workload against a table that in the staging environment had only a handful of distinct tenant rows, meaning all concurrent traffic funneled through one row's lock. That is not a query performance problem or an indexing problem; it is a concurrency design problem, and no amount of connection-pool tuning or replica count changes it, because the constraint sits at the row level, enforced by Postgres's own MVCC locking, not at the CPU or connection level.

-- Sampled every 3s during load test: who is blocked, and on what row?
SELECT
  blocked.pid            AS blocked_pid,
  blocked.usename        AS blocked_user,
  blocked.query          AS blocked_query,
  blocked.wait_event_type,
  blocked.wait_event,
  blocking.pid            AS blocking_pid,
  blocking.query          AS blocking_query,
  blocking.state          AS blocking_state,
  locks.relation::regclass AS locked_relation,
  locks.mode              AS lock_mode,
  locks.locktype
FROM pg_stat_activity blocked
JOIN pg_locks locks
  ON locks.pid = blocked.pid AND NOT locks.granted
JOIN pg_locks blocking_locks
  ON blocking_locks.locktype = locks.locktype
 AND blocking_locks.database IS NOT DISTINCT FROM locks.database
 AND blocking_locks.relation IS NOT DISTINCT FROM locks.relation
 AND blocking_locks.page IS NOT DISTINCT FROM locks.page
 AND blocking_locks.tuple IS NOT DISTINCT FROM locks.tuple
 AND blocking_locks.pid != locks.pid
 AND blocking_locks.granted
JOIN pg_stat_activity blocking
  ON blocking.pid = blocking_locks.pid
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocked.query_start;

-- Output during the load test consistently showed 6-8 rows,
-- all pointing at the same tenant_config tuple:
--  blocked_pid | locked_relation | lock_mode         | blocking_query
-- -------------+-----------------+-------------------+------------------------------------------
--        18422 | tenant_config   | ExclusiveLock      | UPDATE tenant_config SET modified_at=...
--        18430 | tenant_config   | ExclusiveLock      | UPDATE tenant_config SET modified_at=...
--        18441 | tenant_config   | ExclusiveLock      | UPDATE tenant_config SET modified_at=...

Ruling out the broker first

Before touching the database, I wanted to eliminate the Artemis message broker as a suspect, since every successful request in the workload published one message to a queue after the write path completed. I sampled the queue's MESSAGE_COUNT every 2 seconds throughout a load test run and watched it stay at zero at essentially every poll, with the publish rate closely matching the consumer's ack rate. A saturated consumer would show a queue backlog growing over the run; there was none. That single, simple check ruled out an entire subsystem in a few minutes and kept me from chasing a broker-tuning rabbit hole that would not have moved the needle.

What this means for anyone building a similar stack

If you are setting up observability for a load-testing project, the two decisions I would repeat without hesitation are: keep the monitoring stack physically separate from the system under test, and treat golden-signal dashboards as the first triage step, not the last diagnostic step. When latency degrades and your CPU, memory, and error-rate panels do not point at an obvious culprit, the next tool you reach for should be a live query against your database's session and lock views, sampled repeatedly during the load, not a new Grafana panel.

Sources

• Prometheus documentation: Federation, prometheus.io
• PostgreSQL documentation: pg_locks system view, postgresql.org
• PostgreSQL documentation: The Cumulative Statistics System (pg_stat_activity), postgresql.org
• cAdvisor project repository, github.com/google/cadvisor