Skip to main content

Runbook: Database Disk / Space Issues

Where prod runs (2026-06-02): Production Postgres is RDS behind RDS Proxy (see #1303 staging boot, #917 env-driven TypeORM pool sizing). The railway run --service humanwork-postgres psql … invocations below remain valid for the legacy Railway Postgres, which is becoming dev-only as the AWS migration completes. Lead with the RDS path during a prod incident; the Railway commands are kept as the legacy / staging-fallback annex at the bottom of each step.

From a bastion or aws ecs execute-command into a running API task, the API container already has DATABASE_URL pointed at RDS Proxy — psql "$DATABASE_URL" -c "…" works directly. Outside the VPC, use the bastion / AWS Session Manager pattern; never expose RDS to the public internet for ad-hoc psql.

How to Detect​

Symptoms:

  • API logs: no space left on device or could not extend file
  • PostgreSQL errors: ERROR: could not write to file "pg_wal/..."
  • GET /ready fails with DB connectivity error
  • Insert queries failing while reads succeed

Check current usage (RDS — primary):

# From inside an API task (aws ecs execute-command) or bastion:
psql "$DATABASE_URL" -c "
  SELECT pg_database_size(current_database()) AS db_size,
         pg_size_pretty(pg_database_size(current_database())) AS db_size_pretty;
"

# From outside: RDS metadata + allocated/free storage via CloudWatch
aws rds describe-db-instances --db-instance-identifier humanwork-<env>-pg \
  --query 'DBInstances[0].{Allocated:AllocatedStorage,MaxAlloc:MaxAllocatedStorage,Status:DBInstanceStatus}'
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS --metric-name FreeStorageSpace \
  --dimensions Name=DBInstanceIdentifier,Value=humanwork-<env>-pg \
  --start-time "$(date -u -d '15 min ago' +%FT%TZ 2>/dev/null || date -u -v-15M +%FT%TZ)" --end-time "$(date -u +%FT%TZ)" \
  --period 60 --statistics Minimum

Legacy Railway PG fallback:

railway run --service humanwork-postgres \
  psql "$DATABASE_URL" -c "SELECT pg_size_pretty(pg_database_size(current_database()));"

Check largest tables (RDS — primary):

psql "$DATABASE_URL" -c "
  SELECT schemaname, tablename,
         pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
  FROM pg_tables
  WHERE schemaname = 'public'
  ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
  LIMIT 10;
"

Legacy Railway PG fallback:

railway run --service humanwork-postgres psql "$DATABASE_URL" -c "<same query>"

Check vector table specifically (likely largest due to 1536-dim embeddings):

psql "$DATABASE_URL" -c "
  SELECT COUNT(*), pg_size_pretty(pg_total_relation_size('agent_memories')) AS size
  FROM agent_memories;
"

Immediate Remediation​

1. Scale Storage​

RDS (primary): Increase AllocatedStorage (or rely on Storage Autoscaling if enabled). Online operation; no downtime.

aws rds modify-db-instance \
  --db-instance-identifier humanwork-<env>-pg \
  --allocated-storage <new-GiB> \
  --apply-immediately
aws rds describe-db-instances --db-instance-identifier humanwork-<env>-pg \
  --query 'DBInstances[0].{Status:DBInstanceStatus,Pending:PendingModifiedValues}'

Storage Autoscaling (if not yet enabled — recommended):

aws rds modify-db-instance \
  --db-instance-identifier humanwork-<env>-pg \
  --max-allocated-storage <ceiling-GiB> \
  --apply-immediately

Legacy Railway plan upgrade (dev/legacy only): Railway dashboard → humanwork-postgres → Settings → Plan → upgrade storage tier.

2. Vacuum Dead Tuples​

After heavy write operations, dead tuples accumulate. Run VACUUM to reclaim space:

psql "$DATABASE_URL" -c "VACUUM ANALYZE;"

For more aggressive cleanup (requires brief table locks):

psql "$DATABASE_URL" -c "VACUUM FULL ANALYZE;"

Warning: VACUUM FULL acquires an exclusive lock and will block reads/writes. Run during off-hours.

Legacy Railway PG fallback: prefix with railway run --service humanwork-postgres.

3. Clear Old Audit Logs​

If audit_log is large, archive old entries:

psql "$DATABASE_URL" -c "
  DELETE FROM audit_log
  WHERE created_at < NOW() - INTERVAL '90 days';
"

Audit logs older than 90 days are safe to archive (check compliance requirements before deleting permanently).

4. Clear Old pgvector Memories​

The agent_memories table stores 1536-dimensional vectors — each row is ~6KB of vector data plus overhead. For orgs with high conversation volume this grows quickly.

# Check per-org memory count
psql "$DATABASE_URL" -c "
  SELECT org_id, COUNT(*) as memories,
         pg_size_pretty(SUM(octet_length(embedding::text))) as approx_vector_size
  FROM agent_memories
  GROUP BY org_id
  ORDER BY COUNT(*) DESC;
"

# Archive memories older than 6 months for a specific org
psql "$DATABASE_URL" -c "
  DELETE FROM agent_memories
  WHERE created_at < NOW() - INTERVAL '6 months'
  AND org_id = '<org-id>';
"

5. Clear WAL Files (if WAL is bloated)​

psql "$DATABASE_URL" -c "SELECT pg_walfile_name(pg_current_wal_lsn());"

# Check WAL directory size (requires superuser; RDS exposes this via pg_ls_waldir if rds_superuser)
psql "$DATABASE_URL" -c "SELECT * FROM pg_ls_waldir() LIMIT 5;"

If WAL is bloated due to a replication slot not being consumed, identify and drop unused slots:

psql "$DATABASE_URL" -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

# Drop an inactive slot
psql "$DATABASE_URL" -c "SELECT pg_drop_replication_slot('<slot_name>');"

All step-3/4/5 commands above also work via railway run --service humanwork-postgres against the legacy Railway PG instance.

Preventive Monitoring​

RDS (primary): Add a CloudWatch alarm on FreeStorageSpace for the RDS instance via the Terraform monitoring module (infra/terraform/modules/monitoring/). Wire alarms at 30% and 15% remaining free storage to SNS → Slack. This is the canonical alarm path; do not configure storage alarms in dashboards by hand.

Legacy Railway PG: Railway dashboard → humanwork-postgres → Observability → Alerts at 70% and 85% disk-usage thresholds (dev/legacy environments only).

Sentry (#845) also receives Postgres connection errors from the API — a sudden spike of connection terminated unexpectedly or no space left on device events in the humanwork-api Sentry project is the early-warning signal even before the CloudWatch/Railway disk alert fires. See observability.md for Sentry triage.

Add a query to the weekly health check:

psql "$DATABASE_URL" -c "
  SELECT pg_size_pretty(pg_database_size(current_database())) as total,
         (SELECT COUNT(*) FROM agent_memories) as vector_rows,
         (SELECT COUNT(*) FROM audit_log) as audit_rows,
         (SELECT COUNT(*) FROM messages) as message_rows;
"

Long-Term Fixes​

  • Implement automatic agent_memories retention policy (keep last N memories per org, or TTL). Note: MemoryService and agent_memories are deprecated in favour of Haystack as the KB owner, but the table is retained.
  • Archive audit logs to S3/R2 after 90 days (R2 buckets hwork-dev, hwork-staging, hwork are provisioned)
  • Consider per-org pgvector quotas once memory usage becomes significant
  • Consider TimescaleDB for time-series data (audit_log, messages) if volume grows substantially

Source documents are stored in Cloudflare R2. Haystack chunks and embeddings are stored in pgvector on Postgres. The following Postgres tables are relevant:

  • org_documents — metadata index for documents uploaded to each org's Haystack KB
  • corrections — expert correction records fed back to Haystack for KB refinement (via LearningService / correction processor PR #303)
  • agent_memories — deprecated pgvector table; retained for backward compat but no longer the primary KB store

Monitor org_documents growth as onboarding business-context upload (issue #296) is rolled out.

Last updated on by Degen