Profiling a Postgres CPU Spike in Our Django App
Our own Postgres was spiking past 240% CPU while handling a few requests a second. Cleaning out 76 GB of telemetry, one missing index on a JSON key, and three small Django settings brought CPU down by about 60% and disk reads by 98%.
I got the CPU alert for our own database on a Wednesday afternoon, which is a slightly embarrassing thing to admit when the product you sell is the one sending those alerts. The machine it runs on has four cores and is shared with a handful of other small services, and Postgres was swinging between almost nothing and a little over 240% every few seconds, with the load average sitting around four and a half. What bothered me more than the numbers was that the API was handling a few requests a second at the time. A database that busy with that little traffic is doing work somebody wrote by accident, and I wanted to find out whose.
Starting with the size
Before looking at a single query I checked how big the database had grown, mostly out of habit, and it came back at 87 GB. For us that's absurd. Users, projects, monitors and incidents fit in a gigabyte or so. A query against pg_statio_user_tables showed where the rest had gone: two tables held 76 GB between them. One had every line of stdout from every Docker container our agents could see, about 42 million rows. The other had every request from our customers' nginx access logs, about 64 million rows, and most of those were 200s.
I've been on call long enough to know that nobody opens a dashboard during an outage hoping to scroll through forty million successful requests. You want the 502s, the tracebacks, and the minute the 4xx rate jumped. We had changed nginx ingest a few months earlier to keep only 4xx and 5xx responses, but nobody had gone back to clean out the old rows, and container log collection was still on for every server whether anyone had asked for it or not. So container logs became opt-in per server and off by default. The backend also discards anything that arrives from a server that hasn't opted in, which takes care of agents that haven't upgraded yet.
That left emptying both tables. DELETE on tables that size is a bad idea. It takes forever and writes a mountain of WAL, and at the end Postgres keeps the space anyway until a VACUUM FULL rewrites the table, which locks it and needs about as much free disk as the table itself. TRUNCATE just drops the underlying files, so it was the obvious choice. The catch is that it needs an exclusive lock, and I always set a lock timeout before asking for one on a live table, because otherwise your statement sits in the lock queue and every ordinary query lines up behind it.
1SET lock_timeout = '5s';2TRUNCATE TABLE access_events;
It failed on the first try with canceling statement due to lock timeout, which was interesting enough that I went to see who was holding the table.
1SELECT a.pid, now() - a.query_start AS running, l.mode, left(a.query, 80)2FROM pg_locks l3JOIN pg_stat_activity a ON a.pid = l.pid4WHERE l.relation = 'access_events'::regclass;
It was a SELECT COUNT(*) that had been running for four and a half minutes, split across a leader process and two parallel workers, counting this month's rows for a plan usage check. A read-only count is safe to cancel, so I stopped it with pg_cancel_backend, ran the truncate again, and it finished in well under a second. The database went from 87 GB to 11 GB and the disk from 76% full to 24%.
That was also when I realized there were no automated backups on that host. Most of what we'd just removed was noise, but the access events table also held a few months of 4xx and 5xx history I'd have liked to keep, and with nothing to restore from, it was gone. I set up a nightly pg_dump before doing anything else. It writes a compressed custom-format dump and runs pg_restore --list against it, so a half-written file can't pass for a good one. It keeps the last seven and skips the run if the disk is over 85% full. The whole database now dumps to about 800 MB, which tells you roughly what the other 76 GB were worth.
Then I looked at the CPU graph again, and it was still spiking.
Looking for queries without the right tool
pg_stat_statements is the tool for this, and it wasn't installed. Turning it on means adding it to shared_preload_libraries and restarting Postgres, and I didn't want to spend a restart without a lead, so I started with something cruder. A small script polled pg_stat_activity five times a second for thirty seconds. It stripped the literals out of each running query so similar ones would group together, and counted them. It also read the sequential scan counters from pg_stat_user_tables before and after, so I could see which tables were being read without an index.
The sampling pointed at our daily uptime calculation, which walks every check result in a window and showed up in about 40% of samples. That's a real workload, but an indexed one, so I made a note and moved on. The scan counters told a different story. The incidents table has around 153,000 rows, and in those thirty seconds Postgres had sequentially read 767,675 rows from it across fifteen scans. Something kept asking that table a question it couldn't answer with an index, and that was reason enough to schedule the restart.
What pg_stat_statements showed
I added the library to the Postgres command line, deployed, created the extension, reset the counters, and gave it about twelve minutes of normal traffic. The first thing I noticed was the total. Query execution added up to around 75 seconds across the whole window, roughly a tenth of one core, while the container regularly showed more than a full core. So whatever was eating the CPU, a good part of it wasn't query execution at all. I'll come back to that.
At the top of the list by execution time was the incident lookup, with 22% of the total from only 30 calls. When a monitor fails we open an incident and store the monitor's id in the incident's metadata JSON, and a lot of code then asks which monitors currently have an open incident:
1Incident.objects.filter(2 metadata__check_id__in=[str(c.id) for c in checks],3 resolved_at__isnull=True,4).values_list("metadata__check_id", flat=True)
The monitor list does this, the alert checker does it, and so do status pages and the mobile app. It's ordinary Django, and every call was a full scan of a 280 MB table, because the indexes covered started_at and alert_id and nothing covered a key inside the JSON. The column that mattered most was shared_blks_read. Those 30 calls had read about 780,000 blocks from disk, which at 8 KB a block comes to something like 6 GB in twelve minutes. Our shared_buffers is set to 512 MB, kept small because the machine is shared. A 280 MB table dragged through the cache over and over was pushing everything else out as well. That explained the I/O wait and the high system CPU I'd been seeing in sar.
Django can index an expression, JSON key transforms included, so the fix itself was short:
1from django.db.models.fields.json import KeyTransform23class Meta:4 indexes = [5 models.Index(6 KeyTransform("check_id", "metadata"),7 name="incident_check_idx",8 ),9 ]
The migration deserves as much attention as the index. A normal CREATE INDEX blocks writes for the whole build. This table gets written to whenever something breaks, which is exactly when you don't want inserts waiting. AddIndexConcurrently from django.contrib.postgres builds the index without taking that lock, as long as the migration is marked non-atomic, since CONCURRENTLY can't run inside a transaction.
1from django.contrib.postgres.operations import AddIndexConcurrently23class Migration(migrations.Migration):4 atomic = False56 operations = [7 AddIndexConcurrently(8 model_name="incident",9 index=models.Index(10 KeyTransform("check_id", "metadata"),11 name="incident_check_idx",12 ),13 ),14 ]
Afterwards the plan showed an index scan on incident_check_idx with its condition on metadata -> 'check_id'. The expressions have to line up exactly. metadata__check_id compiles to the -> operator, which returns jsonb. An index built on ->>, which returns text, would be ignored, and you'd keep the full scan while paying to maintain an index nobody uses.
Back to the CPU that wasn't query time. pg_stat_statements only measures statements. It knows nothing about what it costs Postgres to fork a backend, authenticate it and warm its caches for every new connection. So I counted connections instead, reading sessions from pg_stat_database twice, 51 seconds apart. There had been 175 new sessions in that gap, about 3.4 a second. We had never set CONN_MAX_AGE, and Django's default of zero opens a connection at the start of every request and closes it at the end.
Setting it to 60 with CONN_HEALTH_CHECKS turned on fixed that. The health check makes Django test a reused connection before handing it to a request, so a connection that died during a database restart gets replaced instead of turning into a 500. I did the arithmetic before shipping it. Three sync Gunicorn workers plus a prefork Celery pool comes to about ten persistent connections, against a max_connections of 100. With threaded or async workers the numbers change, and that's usually the point where PgBouncer comes in.
Two smaller fixes rounded it out. Our API key authentication set last_used and saved the row on every request. Agents poll for queued commands about twice a second across the fleet, so that was 1,905 updates in twelve minutes. Each one created a new row version, more WAL and a dead tuple for autovacuum, all to keep a timestamp accurate to the millisecond that people only ever read to the day. It now writes only when the stored value is more than a minute old. The other was the plan limit check on every server metrics report. It ran a COUNT over the month's metric history, 123 times in the window, at about 76 ms each. A monthly quota can live with being a minute out of date. So the count is now cached for 60 seconds, under a key that includes the year and month, and it resets itself on the first of each month without anyone touching it.
Afterwards
I let the same kind of traffic run for about thirteen minutes after the deploy and compared per-minute rates, since the two windows weren't quite the same length.
| Before | After | |
|---|---|---|
| Query execution time | 6.5 s/min | 2.9 s/min |
| Blocks read from disk | ~67,000/min | ~1,000/min |
| New connections | 3.4/s | 0.1/s |
| DB container CPU, sampled | ~80% avg, 242% peak | ~30% avg, 82% peak |
| Database size | 87 GB | 11 GB |
The disk reads are the number I like best, down about 98%. The incident lookups, the last_used writes and the connection churn have all dropped out of the top of the rankings. What's left up there is ingesting customers' error events, which is the work the database is there to do. The after window also started right after the deploy had restarted everything, so Postgres began it with a cold cache, and I'd expect the steady-state numbers to be a bit better than these.
If I had to pick what's worth taking from this to another Django codebase, sorting pg_stat_statements by shared_blks_read would come first. The query that hurt us most ran only 30 times and would never have stood out by call count. Second, I'd treat any filter on a JSONField key in a hot path as a full scan until EXPLAIN says otherwise. Third, count your connections, because with CONN_MAX_AGE at zero you pay a startup cost that never shows up next to any query. And set up a backup job long before the day you truncate something. That's the lesson from this one I'd most like to have learned some other way.