postgres-query

name: postgres-query description: Run PostgreSQL queries for testing, debugging, and performance analysis. Use when you need to query the database directly, run EXPLAIN ANALYZE, compare query results, or test SQL optimizations. Always uses read-only connections unless explicitly directed otherwise.

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

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

Querying the dev database (cnpg)

The dev database lives in the cnpg-database-dev namespace and is reached through the civitai SSH bastion, which forwards local port 15432 to the in-cluster pgbouncer pooler.

Setup

1. Make sure your ~/.ssh/config has the civitai host with this forward (already configured):

   # dev db (cnpg pgbouncer pooler)
   LocalForward 15432 pgbouncer-pooler-dev.cnpg-database-dev.svc.cluster.local:5432
   

2. Open the tunnel in a terminal (stays open):

   ssh civitai -N
   

3. Make sure DEV_DATABASE_URL is set in .claude/skills/postgres-query/.env:

   DEV_DATABASE_URL=postgresql://postgres:<password>@localhost:15432/civitai?sslmode=no-verify&schema=public
   

   Use sslmode=no-verify (not require) — the cnpg pooler presents a self-signed cert, and the connection is already encrypted inside the SSH tunnel.

Running dev queries

# Read-only (default — writes are blocked client-side)
node .claude/skills/postgres-query/query.mjs --dev "SELECT count(*) FROM \"User\""

# Writable (the dev postgres role is a superuser; needs user permission)
node .claude/skills/postgres-query/query.mjs --dev --writable "UPDATE ..."

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

1. 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.pub to:

   clusters/production/apps/notifications-db/secrets/bastion-ssh-keys.enc.yaml

2. Get the bastion host, port, and forward target from zach (or read them out of the datapacket-talos repo: bastion deployment is at clusters/production/apps/notifications-db/bastion.yaml, public host/port are in clusters/production/apps/minio/nginx-reverse-proxy.yaml).

3. 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
   

4. 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=disable
   

   Get the password from zach (stored in the bastion-pg-creds.enc.yaml secret in the datapacket-talos repo). The same password is also preloaded inside the bastion's .pgpass for 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 notifications
• UserNotification — 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

1. Read-only by default: Uses DATABASE_REPLICA_URL to prevent accidental writes
2. Write protection: Blocks INSERT/UPDATE/DELETE/DROP unless --writable flag is used
3. Notifications is always read-only: --notifications blocks writes client-side AND the database role/pooler reject them
4. 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.json

Verifying 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'"