Database Performance Vocabulary

Describing query performance improvements professionally

The professional description includes:

1. What changed: "composite index on (user_id, created_at)"
2. The absolute change: from 840ms to 4ms
3. The relative change: 99.5% reduction (the most compelling framing)
4. Why it improved: query uses the index rather than a full table scan

The relative improvement calculation: (840 - 4) / 840 × 100 = 99.5%

Why "800 times faster" (C) is imprecise: Technically 840/4 = 210x faster, not 800x. Precision matters in performance reports.

Index performance vocabulary:

• "The query now uses the index — execution plan shows an index seek instead of a table scan."
• "Index selectivity is high — suitable for this query pattern."
• "The composite index covers the WHERE clause and the ORDER BY — eliminating the sort step."

Connection pool saturation — what it means and why it matters

A database connection pool is a fixed set of pre-established connections reused across application requests. When the pool saturates:

• New requests requiring a DB connection must wait for a connection to become available
• Wait time adds directly to request latency
• If wait exceeds the connection timeout, the request fails
• At 100% saturation, the system effectively stops being able to serve database-backed requests

Why this cascades: The application may be healthy (CPU, memory fine), but if all 200 DB connections are busy, all 201st-N requests fail simultaneously — causing what looks like a total application outage.

Mitigation vocabulary:

• "Connection pool is saturated — we need to identify and optimise slow queries holding connections."
• "We can increase the pool size as a temporary mitigation — but the root cause is long-running queries."
• "We need connection timeout and retry logic to handle pool saturation gracefully."

Sequential scan (Seq Scan) — what the query plan is telling you

Seq Scan = the database is reading every single row in the table. For a table with 180,000 rows, this means 180,000 row reads.

The cost number (42000.00) is PostgreSQL's internal cost estimate — an arbitrary unit representing relative work, not milliseconds. What matters is comparing it to an index scan cost (typically much lower).

When is a Seq Scan a concern?

• On large tables (>10,000 rows) with selective WHERE clauses — an index would find matching rows in O(log n) instead of O(n)
• When called frequently — a slow query called 1,000 times/second multiplies the impact

When is a Seq Scan acceptable?

• Very small tables — a full scan of 500 rows is faster than index overhead
• Queries returning most of the table — if you need 90% of rows, an index adds no benefit

Query plan vocabulary:

• "The query plan shows a seq scan — this table needs an index on [column]."
• "After adding the index, the plan changed from seq scan to index scan."
• "EXPLAIN ANALYZE shows [X] rows scanned, [Y] rows returned — very low selectivity."

Cache hit ratio degradation — a common post-launch pattern

Database buffer cache: Most databases keep frequently-accessed data pages in memory. A cache hit = the data was in memory (fast). A cache miss = the data had to be read from disk (slow).

94% → 71% drop:

• Before: 94% of queries served from memory, 6% from disk
• After: 71% from memory, 29% from disk — 4.8x more disk reads

Why reporting features cause this: Reporting queries often scan large date ranges across many rows. These large scans bring millions of rows into the buffer cache, evicting the hot working set that transaction queries rely on — a phenomenon called cache pollution.

Mitigations:

• Route reporting queries to a read replica
• Increase buffer pool size
• Use a separate data warehouse for analytical queries

Vocabulary:

• "Cache hit ratio degraded after [event] — disk I/O increased significantly."
• "The analytical queries are polluting the buffer cache."
• "We should isolate reporting workloads to a replica."

N+1 Query Problem — explained clearly

The N+1 problem is one of the most common ORM-related performance issues:

Example:

1. Query 1: SELECT * FROM users LIMIT 50 — fetch 50 users
2. Query 2–51: SELECT * FROM avatars WHERE user_id = [X] — one query per user
3. Total: 51 queries instead of 2 (one join or one IN clause would suffice)

Impact: At 50 users, 51 queries is manageable. At 500 users, it becomes 501 queries. At 5,000 users in a paginated list, this can bring down the database.

The fix: Eager loading (JOIN or preload) — fetch related data in one query instead of one-per-item.

For a non-technical stakeholder: "The page is making 50 separate database requests where it could make 1. It's like looking up each word in a dictionary one by one instead of getting all definitions at once."

N+1 vocabulary:

• "We identified an N+1 query problem causing [N] database calls per page load."
• "Fixing the N+1 reduced DB calls from [X] to [Y]."
• "We enabled eager loading to resolve the N+1 pattern."