Design retention before tables grow
This procedure is written for real environments where access, uptime and rollback matter as much as the technical fix.Our specific objective is design retention before tables grow. Measure growth, define bus…
This procedure is written for real environments where access, uptime and rollback matter as much as the technical fix.
Our specific objective is design retention before tables grow. Measure growth, define business retention and test archival before emergency cleanup.
What you need before starting
Use a database account with the minimum diagnostic privilege, a recent verified backup and an isolated restoration target. Confirm database, cluster, role and transaction context. Record storage headroom, connection count and replication state. Never test restoration over the only production copy.
Run the diagnostic in a controlled way
Start by printing the command rather than pasting it into an unidentified shell. Confirm every hostname, path and placeholder, then execute it from a session whose output you can preserve.
SELECT pg_size_pretty(pg_database_size(current_database()));- Check 1:
SELECT pg_size_pretty(pg_database_size(current_database())). Run it without piping away errors and retain the complete output. - Check 2:
. Run this only if the previous check completed as expected; the ordering is deliberate.
If the command contains &&, the shell runs the next check only after the previous one exits successfully. That protects the sequence from continuing on obviously invalid input, but it does not prove the result is operationally correct.
How to interpret what you see
Distinguish active work, waiting work, background maintenance and idle sessions. Size or duration alone is not enough; relate the observation to locks, I/O, query plans and application behaviour. For this task, keep returning to the original question: Measure growth, define business retention and test archival before emergency cleanup.
| Observation | Meaning | Next move |
|---|---|---|
| Expected output and exit status 0 | The diagnostic ran and returned a normal result. | Compare it with the baseline and continue to service-level verification. |
| Empty output | Possibly a healthy state, wrong scope, insufficient privilege or an overly narrow filter. | Confirm context and remove one filter at a time. |
| Permission or connection error | The observation path failed; it says nothing conclusive about the target service. | Fix access or test from an authorized vantage point. |
| Unexpected large result | The question may be too broad or the condition may be systemic. | Save evidence, narrow by time or component, and avoid impulsive bulk action. |
Verification that closes the loop
Repeat the query after the controlled change, check application transactions, watch error and latency metrics, and confirm replicas or backups remain healthy. Record the before and after outputs alongside the exact time and revision. If a user-facing path exists, test it independently; do not substitute an internal status command for customer evidence.
Failure modes worth avoiding
Do not terminate sessions, drop indexes or vacuum aggressively because one snapshot looks unusual. Capture the blocking chain and execution plan first.
The safest operator is not the one who never encounters failure. It is the one who preserves enough evidence to explain the failure and enough control to recover cleanly.
Rollback and handover
Reverse the reviewed schema or configuration change, restore the previous query path, and use the isolated restore procedure if data integrity—not performance—is in doubt. Close the task with a short note containing the symptom, diagnostic output, decision, verification and any follow-up monitoring.
This guide follows the official reference linked below. Check the documentation for the exact version running in your environment because flags, defaults and output fields can change.
Primary source: Review the official reference ↗