Database Designer

This skill includes executable scripts — I'll need your permission before running them.

The ERD generator queries information_schema and pg_constraint to extract all relationships:

python3 scripts/generate_erd.py \
  --dsn $DATABASE_URL \
  --format mermaid \
  --output docs/erd.md

Sample output for a typical e-commerce schema:

erDiagram
  users {
    uuid id PK
    string email UK
    timestamp created_at
  }
  orders {
    uuid id PK
    uuid user_id FK
    string status
    int total_cents
  }
  order_items {
    uuid id PK
    uuid order_id FK
    uuid product_id FK
    int quantity
  }
  users ||--o{ orders : "places"
  orders ||--|{ order_items : "contains"

What the analysis also surfaces:

• Tables with no foreign keys (possible orphaned tables)
• FK columns missing indexes (e.g., orders.user_id has no index → slow joins)
• Columns that look like FKs by name (user_id) but have no DB constraint
• Tables with >50 columns (likely candidates for decomposition)

For 45 tables, the ERD is automatically split into domain clusters (inferred from table name prefixes). Want me to run the script against your DATABASE_URL?

Root cause: 12M dead tuples = ~19% bloat. Autovacuum default threshold is 20% — it's just barely not triggering.

Immediate: manual VACUUM

-- Confirm bloat first
SELECT tablename,
  pg_size_pretty(pg_total_relation_size('events')) AS total_size,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables WHERE tablename = 'events';

-- Non-blocking vacuum (runs alongside normal traffic)
VACUUM ANALYZE events;

-- Monitor progress
SELECT phase, heap_blks_scanned, heap_blks_total,
  round(heap_blks_scanned::numeric/nullif(heap_blks_total,0)*100,1) AS pct
FROM pg_stat_progress_vacuum WHERE relid = 'events'::regclass;

Expect 30-60 min on 50M rows.

Fix: tune autovacuum for this table

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- trigger at 1% dead (default is 20%)
  autovacuum_vacuum_cost_delay = 2,        -- less throttling
  autovacuum_vacuum_cost_limit = 400       -- more work per autovacuum cycle
);

Long-term: range partitioning by month

CREATE TABLE events_new (
  LIKE events INCLUDING ALL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_03 PARTITION OF events_new
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

With partitioning: drop old partitions instantly (DROP TABLE events_2024_01), autovacuum only touches the active partition — the bloat problem goes away permanently.