Beyond the Blame: Ten PostgreSQL Performance Checks Every Engineer Must Run

Introduction

In modern web ecosystems, latency is a symptom, not a diagnosis. When a user clicks “Submit” and the response stalls for several seconds, the instinctive reaction is to point fingers at the database—especially when the stack includes PostgreSQL, a system often perceived as a “black box” for data‑intensive workloads. Yet experience shows that the majority of perceived slowness originates from layers surrounding the database: application logic, connection management, hardware provisioning, or mis‑configured PostgreSQL parameters.

Relying on anecdotal blame can lead teams down costly rabbit holes, consuming engineering hours on “optimising the DB” while the real culprit lies elsewhere. This article reframes the conversation by presenting a systematic, data‑driven checklist of ten essential performance tests. By executing these checks before declaring PostgreSQL at fault, engineers can isolate bottlenecks, improve system reliability, and allocate resources where they truly matter.

While the checklist is universally applicable, the analysis emphasizes practical applications for three distinct sectors—financial technology (FinTech), e‑commerce, and SaaS platforms—illustrating how regional infrastructure, traffic patterns, and compliance constraints shape the diagnostic process.

Main Analysis

1. Validate Query Execution Plans with EXPLAIN ANALYZE

At the heart of any performance issue is the query plan. PostgreSQL’s planner decides whether to use an index scan, sequential scan, bitmap heap scan, or a more exotic method such as a parallel hash join. A mis‑estimated row count can cause the planner to choose a sub‑optimal path, inflating execution time by orders of magnitude.

Key metrics to capture:

• Actual vs. estimated rows (ratio > 10:1 often signals a statistics problem).
• Total execution time (including planning time) – a typical OLTP query should stay under 5 ms; anything above 50 ms warrants scrutiny.
• Number of “Rows Removed by Filter” – high values indicate unnecessary data processing.

For example, a FinTech platform processing 2 million daily trades observed a 120 ms latency spike on a “SELECT * FROM trades WHERE trade_date = $1”. EXPLAIN ANALYZE revealed a sequential scan over a 150 GB table, despite an index on trade_date. Adding a partial index reduced the average query time to 8 ms, saving the company roughly $15 k per month in compute costs.

2. Assess Connection Pooling Efficiency

PostgreSQL can handle thousands of concurrent connections, but each connection consumes memory (approximately 10 MB per backend by default). Unchecked connection growth leads to swapping, increased context switches, and ultimately, latency spikes.

Two common pooling strategies dominate the landscape:

• PgBouncer (transaction‑level pooling) – maintains a small pool of server‑side connections, reusing them across client sessions.
• Pgpool‑II (session‑level pooling) – offers load balancing and read‑write splitting but consumes more memory per connection.

In a SaaS CRM with 5 000 active users, the team measured an average of 3 200 open connections during peak hours. By introducing PgBouncer with a pool size of 200, the average CPU utilization dropped from 85 % to 45 %, and request latency fell from 350 ms to 120 ms.

3. Verify Vacuum and Autovacuum Activity

PostgreSQL’s MVCC model leaves “dead tuples” after updates and deletes. If these are not reclaimed, table bloat can increase I/O dramatically. Autovacuum runs automatically, but its thresholds (e.g., autovacuum_vacuum_scale_factor) may be too lax for high‑write workloads.

Key indicators:

• Table size vs. pg_total_relation_size – a bloat ratio > 30 % signals a vacuuming issue.
• Number of dead tuples reported by pg_stat_user_tables.
• Autovacuum lag (time since last vacuum) – a lag > 12 hours on a table receiving > 10 000 writes per minute is problematic.

A European e‑commerce site with a “cart_items” table grew from 12 GB to 28 GB in three weeks despite only 5 GB of logical data. Manual vacuuming reduced the table size back to 14 GB, cutting disk I/O by 40 % and improving checkout latency from 1.2 s to 0.7 s.

4. Examine Index Usage and Redundancy

Indexes accelerate reads but impose write overhead. Over‑indexing can double the cost of INSERT/UPDATE operations. Conversely, missing indexes cause full‑table scans.

Diagnostic steps:

• Run pg_stat_user_indexes to identify indexes with low idx_scan counts.
• Check pg_index for duplicate index definitions.
• Use pg_total_relation_size to quantify index bloat.

In a North‑American fintech startup, a “users” table had three single‑column indexes on email, phone, and username. The application only ever queried email and username. Dropping the unused phone index reduced write latency by 18 % and saved ~250 MB of RAM on the primary node.

5. Monitor Disk I/O Patterns

PostgreSQL’s performance is tightly coupled to storage subsystem characteristics. SSDs deliver sub‑millisecond latency, while spinning disks can add 5–10 ms per random read. Tools such as iostat or pg_stat_bgwriter expose write‑ahead log (WAL) and checkpoint activity.

Critical metrics:

• Average read/write latency (target < 2 ms on SSD, < 10 ms on HDD).
• WAL write rate – high rates (> 500 MB/s) may indicate insufficient log buffer size.
• Checkpoint duration – checkpoints taking > 30 seconds can stall queries.

A SaaS analytics platform running on a mixed‑SSD/HDD environment observed checkpoint spikes of 45 seconds during nightly batch loads. By moving the pg_wal directory to a dedicated NVMe volume, checkpoint duration fell to 12 seconds, eliminating a nightly