Find what is filling the disk, and what can safely be freed right now.
promptread-only tools
triage-disk-space([connection])The text below is exactly what prompts/get returns for the example arguments. A client inserts it as the opening message of a conversation, and the model then calls the tools it names.
Find what is consuming disk on connection shop_prod and what can be freed.
Call diskUsage first. It answers this from SQL alone -- WAL size and file count, the archive status backlog, temporary files on disk now, the log directory, and sizes per tablespace and per database across the whole cluster -- so no shell on the server is needed for any of what follows.
One thing it cannot report, because PostgreSQL exposes no function for it: how much room is left. Sizes here are what PostgreSQL accounts for, not the volume, and anything outside PostgreSQL on the same device is invisible. Ask the operator for df if the headroom matters to the decision; everything else below is answerable without it.
Read tablespaces before anything else. A tablespace sits on whatever volume it was created on and carries its location, so the database that is growing and the disk that is full need not be the same device -- and the log directory is frequently a third one. Establish which volume the alert is about before ranking anything on it.
Four things consume space and they need different fixes, so identify which before proposing anything. They are in this order because it is the order of how fast each can be undone, which is what matters at three in the morning.
0. Call checkPrivileges. Without stats access replicationSlots and currentActivity are degraded, and those are the first two steps.
1. WAL, which is the most common answer and the most recoverable. diskUsage.wal is what is actually on disk; replicationSlots says who is pinning it. Read retained_wal_bytes and wal_status per slot. An inactive slot pins WAL indefinitely and nothing reclaims it while the slot exists. wal_status says how far gone it is, and read it in the direction the server means it:
reserved - the WAL this slot needs is within max_wal_size.
extended - past max_wal_size, but still retained, EITHER by this slot or by wal_keep_size. Check the setting before blaming the slot.
unreserved - the slot NO LONGER retains what it needs; some of that WAL is due to be removed at the next checkpoint. This is the server choosing the disk over the consumer, and it happens when max_slot_wal_keep_size is set. For a disk-space page it is good news -- the space is about to come back on its own -- and bad news for whatever was reading the slot. It can still return to reserved or extended if the consumer catches up in time.
lost - the WAL is already gone; the slot is unusable and its consumer cannot resume whatever you do next.
Do not read unreserved as 'this slot is what is holding the disk'. It is the opposite, and acting on it as though the slot were still pinning space wastes the one checkpoint of warning it gives.
Then get the budget, and read it rather than deriving it: safe_wal_size is how many bytes can still be written before this slot is in danger of becoming lost, which is exactly the 'how long have I got' figure. It is null when max_slot_wal_keep_size is -1, and that null is itself the finding -- nothing bounds this, and the slot will take the disk down before PostgreSQL intervenes. Where it is set, PostgreSQL invalidates the slot rather than fill the disk, trading a broken replica for a live primary.
Before concluding it is a slot, read diskUsage.archive_status. A failing archive_command retains every segment it has not archived, and a climbing .ready count is exactly that -- indistinguishable from an abandoned slot by the WAL size alone, and a completely different fix. pg_stat_archiver carries failed_count and last_failed_time if you need the reason; this server does not read it, but the backlog is enough to tell which of the two you have.
2. Temporary files, which are the fastest to free and the easiest to miss. diskUsage.temp_files is what is on disk at this moment, while databaseStats temp_files and temp_bytes are the running totals since the counters were reset -- the first says whether it is happening now, the second whether it is habitual. Both are sorts and hashes that exceeded work_mem. A single runaway statement can fill a volume in minutes, and every byte returns the moment it ends -- so currentActivity for what is running now is both the diagnosis and the fix. If this is the cause, the incident is over as soon as that statement is, and the follow-up is work_mem rather than storage.
3. Bloat: space the tables hold and are not using. bloat-and-vacuum-review is the full investigation and tableBloat measures one table.
THE TRAP HERE IS THE OBVIOUS FIX. VACUUM FULL rewrites the table and needs free space equal to the table plus its indexes before it releases any -- on a disk that is already full it fails, and it holds an AccessExclusiveLock for the whole attempt. Ordinary VACUUM mostly does not return space to the filesystem either -- it makes it reusable inside the file. The exception is worth knowing on a full disk: VACUUM truncates empty pages at the END of a table and does give those back, so a table whose rows were deleted from the tail, or one that was bulk-loaded and then largely emptied, can return real space for the cost of an ordinary vacuum. Check whether the table has vacuum_truncate disabled in reloptions before promising it. Anywhere else in the file the space stays put, so bloat is usually not the thing to act on during the incident, and saying that plainly is more useful than proposing it.
If a database grew and no table in it did, call largeObjects. Large objects live in a catalog rather than in any user relation, so every size tool here is blind to them while diskUsage.databases counts their bytes -- that combination is the whole signature. It reports a count and owners and deliberately no sizes, because the bytes live in pg_largeobject which is not publicly readable. Do not propose lo_unlink from a count: an object is unreferenced only if no column holds its oid, which the catalog cannot tell you, and unlinking a live one loses data.
4. Genuine growth. diskUsage.databases already ranked every database in the cluster, which matters because the one filling the disk is frequently not the one anybody is connected to. Within the database that stands out, listTableSizes ranks a schema. Do not forget the log directory, which diskUsage also sized: a stuck rotation fills a volume with nobody looking at it.
5. Rank what can be freed by how reversible it is, and say the cost of each rather than only the size.
- Ending a statement that is spilling temp files: immediate, and costs that one statement.
- Raising or setting max_slot_wal_keep_size: bounds future growth, frees nothing now.
- Dropping a replication slot: frees the most, immediately, and is IRREVERSIBLE for its consumer -- a replica or subscriber behind that slot must be rebuilt from scratch. Say exactly what would have to be rebuilt, and prefer fixing or decommissioning the consumer. replication-slot-review establishes whether the consumer is stuck but alive or actually gone, and those two deserve opposite decisions.
- VACUUM FULL or a table rewrite: needs space you do not have, and locks. Not an incident action.
- More storage: buys time and fixes nothing, which is sometimes exactly the right call at three in the morning. Say so when it is.
6. Report what is consuming the space, how fast it is growing, how long there is at that rate, and the one action that buys the most time for the least damage. Then say what the permanent fix is, because it is usually not the same thing.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "triage-disk-space",
"arguments": {
"connection": "shop_prod"
}
}
}