Small Table, Big Bill: How a 1 GB Postgres Table Pinned Our CPU at 100%

A 1.6-million-row table, a 20-row query, and 200,000 calls an hour. The table didn’t need to be big to hurt us; the query only needed to run often.

When people picture a database performance problem, they usually picture a huge table: hundreds of millions of rows, terabytes of data, a query that has slowly gotten worse as the table grew. That’s the story we tell in design reviews, and it’s the reason many teams put off optimisation until “the table gets big.”

This is a story about the opposite. Our Cloud SQL for PostgreSQL instance hit 100% CPU, and the culprit was a query against a table of about 1.1 GB and 1.6 million rows. By modern standards, that’s small. The query returned just 20 rows. On its own, it was slow but survivable. What made it a problem was how often it ran.

Finding the query in the GCP console

The database runs on Google Cloud SQL for PostgreSQL. When CPU pinned at 100%, I went straight to the Cloud SQL console and opened Query Insights, which ranks normalised queries by the load they put on the instance.

One query sat clearly at the top. It was a lookup that the application runs whenever it needs the most recent records for a given item:

select alias.*
from table_name alias
where alias.placeholder_item_id = $var1
  and alias.status = $var2
order by alias.created_at desc
fetch first $var3 rows only

The developers confirmed that $var3 defaults to 20. Nothing about this query looks dangerous. It filters on two columns, sorts by time, and returns a page of 20 rows.

The number that stood out was beside it: Query Insights showed this query had been called about 200,000 times in the past hour. That’s roughly 55 executions every second, all day.

Sizing up the table without scanning it

To reproduce the problem, I needed realistic values for the placeholders. The last thing a database at 100% CPU needs is me running COUNT(*) or GROUP BY over the same table. So I read the planner's statistics instead. They're already collected and cost almost nothing to query.

Table size and estimated rows, from pg_class:

SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_size,
       reltuples::bigint AS est_rows
FROM pg_class
WHERE relname = 'table_name';

Result: 1143 MB, about 1,588,486 rows. A modest table.

Existing indexes, to see what the planner had to work with:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'table_name';

Result: five indexes, and none of them fit this query.

• table_name_pkey on (id)
• uq_table_name_col_a_col_b on (placeholder_col_a, placeholder_col_b)
• uq_table_name_col_c on (placeholder_col_c)
• idx_table_name_col_a_item on (placeholder_col_a, placeholder_item_id)
• idx_table_name_partial on (placeholder_col_d), partial: WHERE placeholder_col_e = true AND status = 'IN_PROGRESS' AND placeholder_col_d IS NOT NULL

placeholder_item_id only shows up as the second column of an index that starts with placeholder_col_a, so Postgres can't use it to look up an item on its own. The partial index only covers a subset of rows this query doesn't filter for, and it's built on a different column. Nothing covered the item, the status and the sort order together.

Column types of the three columns the query filters and sorts on, from information_schema:

SELECT column_name, data_type, character_maximum_length, is_nullable
FROM information_schema.columns
WHERE table_name = 'table_name'
  AND column_name IN ('placeholder_item_id', 'status', 'created_at');

• placeholder_item_id: varchar(100), not null
• status: varchar(20), not null
• created_at: timestamp without time zone, nullable

Both filter columns are short, non-null strings, so they’re cheap to index and good leading columns. created_at allows nulls. That's fine here: a DESC index puts nulls first by default, and so does ORDER BY created_at DESC, so the index order still matches the query.

Value distribution, from pg_stats:

SELECT attname, most_common_vals::text, most_common_freqs::text
FROM pg_stats
WHERE tablename = 'table_name'
  AND attname IN ('placeholder_item_id', 'status');

Share of rows by status:

• FINISHED: 76.5%
• TERMINATED: 17.7%
• RAN_OUT: 3.2%
• IN_PROGRESS: 2.7%

The most common placeholder_item_id value, placeholder_serial, accounted for 1.9% of rows.

From this I picked the test values: the busiest item, placeholder_serial, and two statuses at opposite ends of the distribution, FINISHED (most common) and IN_PROGRESS (rare). Row count stayed at the default of 20.

What the planner was actually doing

With the placeholders replaced by real values, I ran the query under EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM public.table_name
WHERE placeholder_item_id = 'placeholder_serial'
  AND status = 'IN_PROGRESS'
ORDER BY created_at DESC
FETCH FIRST 20 ROWS ONLY;

Then I ran it again with status = 'FINISHED'. Both plans told the same story:

• A Parallel Seq Scan over the whole table, about 1.6 million rows.
• A top-N sort on created_at to find the newest 20.
• FINISHED: 21,403 ms and 72,297 buffer hits.
• IN_PROGRESS: 12,733 ms and 72,298 buffer hits.

The buffer counts are the giveaway. Whether the status matched 76% of rows or under 3%, Postgres touched the same ~72,000 pages, roughly the entire table, to hand back 20 rows. There was no index that matched the filter and the sort together, so every call meant reading everything.

Get Emmanuel Makinde’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Some of that time was probably the instance already being busy. But even on an idle server, scanning 72,000 pages to return 20 rows is a lot of work to repeat.

The fix: one composite index

CREATE INDEX CONCURRENTLY idx_table_name_item_status_created
  ON public.table_name (placeholder_item_id, status, created_at DESC);

The column order is the whole point:

1. placeholder_item_id and status first. Both are equality filters, so Postgres can jump straight to the slice of the index for one item and one status.
2. created_at DESC last. Inside that slice, entries are already in the order the query wants. Postgres reads the first 20 and stops. No sort, no scan of rows it will throw away.

CONCURRENTLY lets the build run without blocking inserts and updates on a table that's being written to all the time. It takes longer and can't run inside a transaction, but on a busy, write-heavy table that trade is worth it.

Rolling it out

Failover first. The instance was already overwhelmed, with CPU pinned at 100%, and starting an index build on top of that would only add to the load. After getting the green light from the service owner, I failed the instance over before the build. That cleared the pile-up of existing connections and gave the build a calmer starting point.

Watch the build. pg_stat_progress_create_index shows which phase it's in and how far along:

SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total, current_locker_pid
FROM pg_stat_progress_create_index;

Confirm it’s valid. A concurrent build that fails leaves an invalid index behind, so check before trusting it:

SELECT c.relname, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'idx_table_name_item_status_created';

Refresh statistics. So the planner has current numbers to plan with:

ANALYZE public.table_name;

After the index

I re-ran the same statements with the same values, both at 20 rows. Both plans became an Index Scan using the new index, with no Sort node.

• Plan: Parallel Seq Scan + top-N sort → Index Scan, no sort
• FINISHED: 21,403 ms → 0.174 ms
• IN_PROGRESS: 12,733 ms → 0.133 ms
• Buffer hits per call: ~72,300 → 23
• Buffer hits per hour (200k calls): ~14.5 billion → ~4.6 million
• Instance CPU: 100% → below 50%

The last two lines are the ones that matter. Per call, the index saved about 72,000 page reads. Multiplied by 200,000 calls an hour, that’s the difference between roughly 14.5 billion page reads an hour and 4.6 million. At 8 KB a page, that’s on the order of 100 TB of memory reads an hour down to about 35 GB.

CPU on the instance dropped from 100% to below 50%.

The real lesson: load is cost × frequency

It’s tempting to file this under “missing index, added index, done.” But the more useful lesson is about when the problem showed up and why.

The load a query puts on a database is roughly:

load = cost per call × calls per second

We tend to watch only the first term, and we tend to tie it to table size. “It’s only 1.6 million rows, a scan is fine.” And for a query that runs a few times an hour, it probably is. Nobody would notice a one-second scan in a nightly report.

But the second term changes without any change to the schema. A new feature calls the endpoint more often. A mobile app starts polling. Merchant or user numbers grow. A retry loop kicks in during an incident. None of that makes the table bigger, and all of it multiplies the cost of every inefficient plan.

At 55 calls a second, our “small” table was being read in full about 55 times a second. The table never grew into the problem. The traffic did.

That’s why “we’ll optimise when the table gets big” is the wrong trigger. Table size is only one of the two numbers. By the time either of them gets big enough to hurt, you’re fixing it in production, under pressure, with CPU at 100%.

Building a forward-looking impact check into development

The fix itself was one index. The cheapest place to catch this was before the query ever shipped. Here’s the short check I’d now put on any new or changed query that touches a shared database:

1. Estimate call frequency, not just table size. How many times a second will this run at today’s traffic? At 10× traffic? Is it on a hot path, a polling loop, or a retry path?
2. Run EXPLAIN (ANALYZE, BUFFERS) on realistic data. Look for sequential scans and sort nodes on large inputs. Watch buffer counts, not only time; time is flattering on a quiet dev box.
3. Multiply it out. Buffers per call × calls per hour gives a number people can reason about. 72,000 × 200,000 is hard to argue with.
4. Match the index to the query shape. Equality filters first, then the sort column in the direction the query sorts. Check that it serves the exact WHERE and ORDER BY the ORM generates.
5. Project growth on both axes. Ask what happens when rows double and when calls double. A plan that’s fine on one axis can fall over on the other.
6. Make it part of the review, not the incident. Add the plan and the frequency estimate to the pull request for any query on a hot table, so a DBA or senior engineer can sign off before release.

None of this needs special tooling. On Cloud SQL, Query Insights already shows call counts and load per query in production, which makes it a good source for step 1 when you’re changing an existing query.

Closing

A 1.1 GB table is not a big table. A 20-row query is not a big query. Run 200,000 times an hour without the right index, together they were enough to pin a production database at 100% CPU.

Optimisation shouldn’t wait for a table to grow. Size and frequency both decide the load, and frequency can climb in a single release. Treat both as part of the design, check them before code ships, and you won’t have to learn this one from a CPU graph.