Runbook: Database Disk / Space Issues

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:

railway run --service humanwork-postgres \
  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;
  "

Check largest tables:

railway run --service humanwork-postgres \
  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;
  "

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

railway run --service humanwork-postgres \
  psql "$DATABASE_URL" -c "
    SELECT COUNT(*), pg_size_pretty(pg_total_relation_size('agent_memories')) AS size
    FROM agent_memories;
  "

---

Immediate Remediation

1. Upgrade Railway Postgres Plan

Fastest fix. 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:

railway run --service humanwork-postgres \
  psql "$DATABASE_URL" -c "VACUUM ANALYZE;"

For more aggressive cleanup (requires brief table locks):

railway run --service humanwork-postgres \
  psql "$DATABASE_URL" -c "VACUUM FULL ANALYZE;"

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

3. Clear Old Audit Logs

If audit_log is large, archive old entries:

railway run --service humanwork-postgres \
  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
railway run --service humanwork-postgres \
  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
railway run --service humanwork-postgres \
  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)

railway run --service humanwork-postgres \
  psql "$DATABASE_URL" -c "SELECT pg_walfile_name(pg_current_wal_lsn());"

# Check WAL directory size (requires superuser)
railway run --service humanwork-postgres \
  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:

railway run --service humanwork-postgres \
  psql "$DATABASE_URL" -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

# Drop an inactive slot
railway run --service humanwork-postgres \
  psql "$DATABASE_URL" -c "SELECT pg_drop_replication_slot('<slot_name>');"

---

Preventive Monitoring

Set up a Railway alert on Postgres disk usage (Railway dashboard → humanwork-postgres → Observability → Alerts) at 70% and 85% thresholds.

Add a query to the weekly health check:

railway run --service humanwork-postgres \
  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)
• Archive audit logs to S3 after 90 days
• Consider per-org pgvector quotas once memory usage becomes significant
• Consider TimescaleDB for time-series data (audit_log, messages) if volume grows substantially