PostgreSQL Query Testing
Use this skill to run ad-hoc PostgreSQL queries for testing, debugging, and performance analysis.
Running Queries
Use the included query script:
node .claude/skills/postgres-query/query.mjs "SELECT * FROM \"User\" LIMIT 5"Options
| Flag | Description |
|---|---|
--explain | Run EXPLAIN ANALYZE on the query |
--writable | Use primary database instead of read replica (requires user permission) |
--data-packet | Use the DataPacket replica (DATABASE_DATA_PACKET_URL) — read-only |
--notifications | Query the notifications-db (DataPacket) — read-only via SSH bastion (see setup below) |
--timeout <s>, -t | Query timeout in seconds (default: 30) |
--file, -f | Read query from a file |
--json | Output results as JSON |
--quiet, -q | Minimal output, only results |
Examples
# Simple query
node .claude/skills/postgres-query/query.mjs "SELECT id, username FROM \"User\" LIMIT 5"
# Check query performance
node .claude/skills/postgres-query/query.mjs --explain "SELECT * FROM \"Model\" WHERE id = 1"
# Override default 30s timeout for longer queries
node .claude/skills/postgres-query/query.mjs --timeout 60 "SELECT ... (complex query)"
# Query the notifications-db
node .claude/skills/postgres-query/query.mjs --notifications "SELECT count(*) FROM \"Notification\""
# Query from file
node .claude/skills/postgres-query/query.mjs -f my-query.sql
# JSON output for processing
node .claude/skills/postgres-query/query.mjs --json "SELECT id, username FROM \"User\" LIMIT 3"Connection Targets
| Flag | Connection string | Use when |
|---|---|---|
| (default) | DATABASE_REPLICA_URL (falls back to DATABASE_URL) | Most queries — read-only main replica |
--writable | DATABASE_URL | Writes against primary; needs user permission |
--data-packet | DATABASE_DATA_PACKET_URL | Querying the DataPacket replica (read-only) |
--notifications | NOTIFICATION_DB_REPLICA_URL | Querying notifications-db (read-only); requires SSH tunnel |
Querying the notifications-db (DataPacket)
The notifications-db lives on the DataPacket cluster. Direct network access from your laptop isn't allowed — connect via the SSH bastion.
One-time setup
- Make sure your SSH public key has been added to the bastion. If you don't have access yet, ask zach to add your
~/.ssh/id_ed25519.pubto:clusters/production/apps/notifications-db/secrets/bastion-ssh-keys.enc.yaml - Get the bastion host, port, and forward target from zach (or read them out of the
datapacket-talosrepo: bastion deployment is atclusters/production/apps/notifications-db/bastion.yaml, public host/port are inclusters/production/apps/minio/nginx-reverse-proxy.yaml). - Add an SSH config entry (
~/.ssh/config) so the tunnel is one command:Host notif-bastion HostName <bastion-host> Port <bastion-port> User bastion IdentityFile ~/.ssh/id_ed25519 # Tunnel local 5433 → in-cluster ro pgbouncer pooler LocalForward 5433 <ro-pooler-host>:5432 ServerAliveInterval 60 - Add the connection string to your project
.env(or.claude/skills/postgres-query/.env):NOTIFICATION_DB_REPLICA_URL=postgresql://notifications_readonly:<password>@127.0.0.1:5433/notification_prod?sslmode=disableGet the password from zach (stored in thebastion-pg-creds.enc.yamlsecret in the datapacket-talos repo). The same password is also preloaded inside the bastion's.pgpassfor in-pod use.
Running queries
# 1. Open the SSH tunnel in one terminal (stays open)
ssh notif-bastion
# The bastion's MOTD shows the available tables and tools.
# You can run ad-hoc psql in this terminal too — `psql` is preloaded
# with .pgpass and PGHOST/PGUSER env vars.
# 2. In another terminal, run queries via the skill
node .claude/skills/postgres-query/query.mjs --notifications \
"SELECT count(*) FROM \"Notification\""
node .claude/skills/postgres-query/query.mjs --notifications --explain \
"SELECT * FROM \"UserNotification\" WHERE \"userId\" = 12345 ORDER BY \"createdAt\" DESC LIMIT 50"Available tables (read-only)
Notification— canonical notificationsUserNotification— per-user fanout (largest table)PendingNotification— processing queue (often empty)
The role notifications_readonly only has SELECT. Writes are also rejected at the pooler level (replica routing).
Safety Features
- Read-only by default: Uses
DATABASE_REPLICA_URLto prevent accidental writes - Write protection: Blocks INSERT/UPDATE/DELETE/DROP unless
--writableflag is used - Notifications is always read-only:
--notificationsblocks writes client-side AND the database role/pooler reject them - Explicit permission required: Before using
--writable, you MUST ask the user for permission
When to Use --writable
Only use the --writable flag when:
- The user explicitly requests write access
- You need to test write operations
- You're verifying transaction behavior
IMPORTANT: Always ask the user for permission before running with --writable.
Comparing Query Performance
To compare two query approaches:
# Run first approach
node .claude/skills/postgres-query/query.mjs --explain "SELECT ... (approach 1)"
# Run second approach
node .claude/skills/postgres-query/query.mjs --explain "SELECT ... (approach 2)"
# Compare actual results
node .claude/skills/postgres-query/query.mjs --json "SELECT ... (approach 1)" > /tmp/q1.json
node .claude/skills/postgres-query/query.mjs --json "SELECT ... (approach 2)" > /tmp/q2.jsonVerifying Index Usage
Run with --explain and look for:
- Good: "Index Scan", "Bitmap Index Scan", "Index Only Scan"
- Bad: "Seq Scan" on large tables (indicates missing or unused index)
node .claude/skills/postgres-query/query.mjs --explain "SELECT * FROM \"Account\" WHERE provider = 'discord'"