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

Can analysts ever write to prod?

Through controlled migration pipelines and approved break-glass roles - not default GUI or psql profiles.

psql vs GUI for DBAs?

psql for automation and precise control; GUI for schema visualization. SMEs should excel at both with same safety rules.

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

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

Can analysts ever write to prod?

Through controlled migration pipelines and approved break-glass roles - not default GUI or psql profiles.

psql vs GUI for DBAs?

psql for automation and precise control; GUI for schema visualization. SMEs should excel at both with same safety rules.

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

psql Best Practices

Search Documentation

psql Best Practices