psql Best Practices

Safe habits for interactive SQL, GUI clients, and scripted checks against production PostgreSQL.

How to Use This List

• Onboard every engineer who touches prod data with sections A and B first.
• Enforce B and C via role grants - culture alone fails at 2 AM.
• Re-audit quarterly when new BI tools or jump hosts appear.

A - Connection Safety

• Default production access through read replicas. Primary is for writes and controlled migrations only.
• Use read-only roles (app_ro) for exploration. No INSERT/UPDATE/DELETE on production human paths.
• Set application_name to identify humans. PGAPPNAME=psql-alice in jump box profiles.
• Require TLS for remote psql. sslmode=verify-full when CA is manageable.
• Never store prod passwords in shell history. Use ~/.pgpass with 600 perms or a vault CLI.

B - Query Discipline

• Always LIMIT unbounded exploratory SELECTs. Pair with ORDER BY when order matters.
• Enable \timing for performance investigations. Follow with EXPLAIN on staging before prod ANALYZE.
• Avoid SELECT * on wide tables in prod. Name columns explicitly; JSONB rows are expensive.
• Use BEGIN READ ONLY for multi-statement exploration. Prevents accidental writes in same session.
• Set statement_timeout on human roles. 30s-120s typical for ad-hoc SQL.

C - Scripting & Automation

• Use -v ON_ERROR_STOP=1 in all CI psql scripts. Partial success is worse than loud failure.
• Prefer \copy over server path COPY for operators. Fewer superuser file privileges.
• Wrap DDL scripts in transactions when safe. Know exceptions: CREATE INDEX CONCURRENTLY cannot.
• Log script name via PGAPPNAME. Appears in pg_stat_activity during deploy smoke tests.
• Version control every non-trivial SQL script. Ad-hoc fix.sql on laptop is not a runbook.

D - GUI Clients

• Color-code prod vs staging servers in pgAdmin/DBeaver. Red/green or distinct host prefixes.
• Disable auto-commit grid edits on production profiles. Read-only roles as backup enforcement.
• Ban saved superuser connections on laptops. Break-glass credentials live in vault with TTL.
• Route GUI users through same timeout policies as psql. idle_in_transaction_session_timeout mandatory.
• Export large datasets with COPY, not GUI click-export. Avoid client OOM on million-row mistakes.

E - Team Norms

• Teach psql meta-commands before GUI-only onboarding. \d, \conninfo, \timing reduce magic clicks.
• Document approved client versions in onboarding. Client/server major mismatch breaks dump/restore.
• Post-incident review when wrong-environment write occurs. Update jump box aliases and coloring, not just blame.
• Sanitize dumps before dev restore. pg_dump from prod to laptop is a data breach path.
• Pair analytical SQL with EXPLAIN review standards. See query review docs for merge gates.

FAQs

Related

• psql Basics - connect, timing, LIMIT
• pgAdmin & DBeaver - GUI guardrails
• Permissions Best Practices - role design

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.