Render Debug

list_postgres_instances()

get_postgres(postgresId: "<postgres-id>")

get_metrics(
  resourceId: "<postgres-id>",
  metricTypes: ["active_connections"],
  startTime: "<1-hour-ago-ISO8601>"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT state, count(*) FROM pg_stat_activity GROUP BY state"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT max_conn, used, res_for_super, max_conn - used - res_for_super AS available FROM (SELECT count(*) used FROM pg_stat_activity) t, (SELECT setting::int res_for_super FROM pg_settings WHERE name = 'superuser_reserved_connections') t2, (SELECT setting::int max_conn FROM pg_settings WHERE name = 'max_connections') t3"
)

const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,  // Maximum connections in pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

from sqlalchemy import create_engine
engine = create_engine(
    DATABASE_URL,
    pool_size=5,
    max_overflow=10,
    pool_timeout=30,
    pool_recycle=1800
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT query, calls, mean_exec_time, total_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE email = '[email protected]'"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 10"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan DESC LIMIT 20"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted LIMIT 10"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '1 minute' AND state != 'idle' ORDER BY duration DESC"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_relation_size(relid)) AS table_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size"
)

query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT schemaname, relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_percentage FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC LIMIT 10"
)

get_metrics(
  resourceId: "<postgres-id>",
  metricTypes: ["cpu_usage"],
  startTime: "<1-hour-ago-ISO8601>"
)

get_metrics(
  resourceId: "<postgres-id>",
  metricTypes: ["memory_usage"],
  startTime: "<1-hour-ago-ISO8601>"
)

# 1. Check database exists and status
list_postgres_instances()

# 2. Check connection count (might be at limit)
get_metrics(resourceId: "<postgres-id>", metricTypes: ["active_connections"])

# 3. Check for stuck connections
query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT state, count(*) FROM pg_stat_activity GROUP BY state"
)

# 4. Verify service has correct DATABASE_URL
get_service(serviceId: "<service-id>")

# 1. Find slowest queries
query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5"
)

# 2. Check for missing indexes
query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE seq_scan > idx_scan ORDER BY seq_scan DESC LIMIT 5"
)

# 3. Check database CPU (might be overloaded)
get_metrics(resourceId: "<postgres-id>", metricTypes: ["cpu_usage"])

# 1. Check current size
query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT pg_size_pretty(pg_database_size(current_database()))"
)

# 2. Find largest tables
query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 5"
)

# 3. Check for bloat
query_render_postgres(
  postgresId: "<postgres-id>",
  sql: "SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5"
)