Your Postgres Observability Stack is Lying to You

TL;DR

• pg_stat_statements and APM tools report on what is slow, not why. They're lagging indicators of symptoms, not diagnostic tools.
• The root cause of most performance fires is a bad execution plan chosen by the optimizer, a layer to which these tools are completely blind. Finding it requires manual, expert-level EXPLAIN analysis.
• Fixing a query is a temporary patch. The real liability often lies in schema and indexing decisions made years ago, which are nearly impossible to revert at scale. Your database is not a stateless application.

---

It’s 2 AM. A PagerDuty alert jolts you awake. CPU utilization on the primary Postgres replica is pegged at 95%. Your APM dashboard is a sea of red. Traces point to a dozen slow API endpoints, all bottlenecked on database calls. The on-call engineer, following the runbook, declares “database contention” and escalates to you.

Your playbook says to check pg_stat_statements. You pop open a psql shell and run the standard query, ordering by total_exec_time. You see a few candidates, but nothing obviously wrong. Just standard application queries running slower than usual. This is the start of a wild goose chase that plagues even the most sophisticated engineering teams. Your entire observability stack is telling you that you have a problem, but it offers zero insight into why.

The pg_stat_statements Trap

pg_stat_statements is the default tool for query analysis in Postgres, and it's fundamentally a high-level aggregation engine. It groups queries by a hash of their normalized text, then aggregates metrics like execution time, calls, and rows returned. This is useful for a 30,000-foot view, but it's dangerously misleading for diagnostics.

Its primary flaw is that it decouples symptoms from causes. It might show that SELECT * FROM products WHERE id = $1 has a high mean_exec_time. Is that because of one pathologically slow plan for a specific $1 value that skewed the average? Or is it consistently slow for all values? You have no idea. The specific parameter values that lead to bad plans are lost. The context is gone.

Even worse, it tells you nothing about how the query executed. It doesn't store the plan hash, the wait events, or the buffer cache behavior. Seeing a high total_exec_time is like a doctor seeing a high temperature. It confirms the patient is sick but provides no clue as to the illness. Is it a common cold or a life-threatening infection? pg_stat_statements can't tell the difference.

APMs Only Show the Exit Wound

Your APM tool—be it Datadog, New Relic, or another—is not a database analysis tool. It’s an application analysis tool that happens to have database metrics. It excels at tracing a request through microservices and showing you which span was slow. It will tell you, with great precision, that your application spent 900ms inside a db.query() call.

Then it stops. It has zero visibility into the Postgres query planner. It cannot tell you that the 900ms was spent performing a Sequential Scan on a 2TB table that should have been an Index Only Scan taking 5ms. It cannot tell you that autovacuum hasn't run on the table in three days, leading to stale statistics and a terrible plan choice. It just reports the number. The APM shows you the exit wound, not the bullet's trajectory through the system.

This leads to a cycle of futility: alert fires -> engineer looks at APM -> engineer sees 'db slow' -> engineer checks pg_stat_statements -> engineer finds nothing conclusive -> engineer scales up the replica, hoping more hardware solves it. The root cause, the bad plan, persists, waiting to cause the next outage.

From Symptom to Diagnosis: How a Real DBA Works

Let’s walk through a real-world scenario that your observability stack will never catch. An alert fires for high I/O wait. The APM points to a high-throughput INSERT query.

An INSERT causing read I/O? That shouldn't happen. A junior engineer might dismiss it. A senior DBA knows to be suspicious. The query is simple: INSERT INTO user_events (event_id, user_id, payload) VALUES (...) ON CONFLICT (event_id) DO NOTHING;.

The key is ON CONFLICT. To check for a conflict, Postgres must check for the existence of event_id in the user_events_pkey unique index. The only way to truly understand the cost is with EXPLAIN ANALYZE.

EXPLAIN (ANALYZE, BUFFERS) INSERT INTO user_events ... ON CONFLICT (event_id) DO NOTHING;

--                                                        QUERY PLAN
-- ----------------------------------------------------------------------------------------------------------------------
--  Insert on user_events  (cost=0.56..8.58 rows=1 width=78) (actual time=12.158..12.159 rows=1 loops=1)
--    Conflict Resolution: ON CONFLICT (event_id) DO NOTHING
--    Buffers: shared hit=5 read=4
--    ->  Index Scan using user_events_pkey on user_events  (cost=0.56..8.58 rows=1 width=78) (actual time=0.031..0.032 rows=0 loops=1)
--          Index Cond: (event_id = 'some-uuid-string')
--          Buffers: shared hit=2 read=3

The plan reveals the truth. Look at the Buffers line for the Index Scan: read=3. This means for this single INSERT, Postgres had to fetch 3 blocks (24KB) from disk because they weren't in the buffer cache. Now, multiply that by 20,000 events per minute. You're not CPU-bound; you are I/O-bound, thrashing your storage subsystem because your primary key index is too fragmented and bloated to fit in RAM.

This is the diagnosis. The APM only saw a slow INSERT. pg_stat_statements only saw an aggregate time. Neither could tell you that your choice of a UUID as a primary key, which has no natural locality, has led to an index that is 90% larger than it needs to be and is destroying your cache efficiency.

Back when I was on the Oracle query engine team, this level of diagnosis was built-in. Tools like the Automatic Workload Repository (AWR) and Active Session History (ASH) sample session activity every second, capturing not just the SQL ID but the exact plan hash value and, critically, the specific wait events. You could instantly correlate a spike in db file sequential read waits directly to a plan hash for a specific query and see exactly when performance degraded. The Postgres open-source ecosystem is years behind this integrated approach.

The Irreversible Liability of a Bad Schema

The choice of a UUID primary key is not a bug you can fix in a pull request. On a 10TB table, changing the primary key type to BIGSERIAL to improve locality is a multi-quarter, high-risk migration project. You cannot simply ALTER TABLE. You have to create a new table, backfill data, build new indexes, and orchestrate a cutover with downtime. This single, seemingly innocuous schema decision, made years ago, has become a massive, permanent liability.

This is the core problem. Databases are not stateless. You can't just revert a commit. Bad architectural decisions create technical debt with compound interest measured in terabytes and downtime. Your observability stack is designed for a stateless world, alerting you to fires but completely ignorant of the faulty wiring that guarantees they will happen again.

What DeepSQL does about this

DeepSQL was built to close this diagnostic gap. It doesn't just scrape pg_stat_statements; it hooks directly into Postgres to capture execution plans, buffer stats, and wait events for every significant query. It correlates this runtime information with your schema, index definitions, and table health statistics. For the INSERT ON CONFLICT example, it would automatically flag the high rate of physical reads tied to the user_events_pkey index, identify the index bloat, and trace the root cause back to the UUID key anti-pattern. Instead of paging a human at 2 AM to start a goose chase, it provides a complete diagnosis, from application symptom to schema-level cause, so you can prevent the next fire instead of just fighting the last one.