Late one night, while fixing a performance issue, I was looking through a specific query that had gotten slow:
insert into abc (x, y, z)
select x, y, z
from a
join b on b.a_id = a.id
join c on c.b_id = b.id;
This query would run constantly, each time preceded by:
delete from abc;
Essentially, it was a custom materialized view as a table being constantly rebuilt. The select query was slow in itself. The reason it kept running was that it was set up in a cron job to run every 15 seconds, but the query took more than 15 seconds, so a new run started before the previous one had finished, and it ended up running continuously, with a full rebuild every 15 to 25 seconds.
While I quickly found a fix for that query related to a misused index, one thing that looked weird was a trigger inserting some revision history. We keep revision tables as a quick solution for always being able to see what was previously in the database. These triggers simply copy the row into a revision table before every insert and delete. The trigger itself was fast, only 5 ms in total for the insert, but it fired 10,000 times on every run, once for each of the 5000 deleted rows and once for each of the 5000 inserted rows, and this query ran constantly:
explain analyze
insert into abc (x, y, z)
select x, y, z
from a
join b on b.a_id = a.id
join c on c.b_id = b.id;
Insert on abc (cost=... rows=0 width=0) (actual time=21874.316..21874.317 rows=0 loops=1)
-> Hash Join (cost=... rows=5000 width=12) (actual time=412.058..21803.129 rows=5000 loops=1)
...
Planning Time: 0.412 ms
Trigger trg_abc_revision: time=5.112 calls=5000
Execution Time: 21880.527 ms
The select was the slow part, and the misused index fixed that. The trigger line was a small detail by comparison, but it still stood out. 10,000 rows copied into a revision table on every run, around the clock, had to add up to something. I decided to look into the revision table to see how big it had gotten. Initially, I just tried querying the size with:
select count(*) from abc_revision;
This query timed out after 5 minutes. A weird thing for a count query to do. I then found out that you can ask PostgreSQL directly for the size of a table, including its indexes:
select pg_size_pretty(pg_total_relation_size('abc_revision'));
It returned 130 GB… An enormous size for a database table in a system where the rest of the database was a few MB.
In hindsight, the cause was obvious. Every 15 to 25 seconds, all 5000 rows in abc were deleted and re-inserted. Every one of those deleted and inserted rows fired the trigger, which copied the row into abc_revision. So the full contents of abc were appended to the revision table twice on every single run, around the clock, and nothing ever deleted anything from it. At 10,000 rows every 15 to 25 seconds, that is somewhere between 35 and 58 million rows a day.
These two features, one for updating what was essentially a materialized view and one for ensuring we had a revision history in case anyone asked, had collided and caused a massive amount of data to be created.
The fix for this was simple. We removed the revision table for abc, since a revision history of a table that is rebuilt from other tables every 15 to 25 seconds is useless anyway. That instantly dropped 130 GB and shrank the database back to its normal size of a few MB.
It was a fortunate way to discover something that could quickly have become a disaster. Otherwise, it would only have shown up in a bill later. Luckily, it never left the staging environments.
My takeaway from this is that I always want monitoring on the size of a database. If it suddenly jumps in size or increases drastically without a reason, someone should look into it.