Most of the time lost working with databases is not query time, it is the loop around it: retyping the same connection string, squinting at wrapped output, running a query you meant to run against staging. Fixing that loop is a one-evening job and it pays back permanently.
\set QUIET 1
\timing on
\x auto
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTSIZE 20000
\set PROMPT1 '%[%033[1;33m%]%n@%/%R%[%033[0m%]%# '
\set COMP_KEYWORD_CASE upper
\pset null '[null]'
\set ON_ERROR_ROLLBACK interactive
\set QUIET 0Four of those lines do most of the work. \timing on prints execution time after every statement so you notice regressions without asking. \x auto flips to expanded output only when a row is too wide, which ends horizontal scrolling forever. Per-database history means arrow-up in your analytics database does not resurrect a query you wrote against production. ON_ERROR_ROLLBACK interactive means one typo inside a transaction does not abort the whole thing.
pip install pgcli gives you autocompletion of table and column names from the live schema, syntax highlighting, and multiline editing. It speaks the same backslash commands, so nothing you know is wasted. Keep psql for scripts and \copy; use pgcli when you are poking around a schema you do not have memorised. The equivalents for other engines are mycli and litecli.
Enable pg_stat_statements in shared_preload_libraries and this becomes your first stop in any performance conversation:
SELECT calls,
round(total_exec_time::numeric, 0) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;Sort by total time, not mean. A 4 ms query called two million times an hour costs far more than a 900 ms report that runs nightly, and only the total column shows you that.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) adds the numbers that matter. shared read means blocks came from disk or OS cache; shared hit means they came from the buffer pool. A plan reading 200,000 blocks to return 12 rows is doing something wrong regardless of how fast it ran on a warm cache. Watch for a large gap between rows= estimated and actual rows= — a 100× misestimate almost always means stale statistics, and ANALYZE tablename fixes it in seconds.
Give yourself a read-only role and connect as that by default; switch users deliberately when you intend to write. Colour your prompt by environment (PROMPT1 above accepts ANSI codes) so a red prompt means production. Set SET statement_timeout = '30s' in your session so an accidental cross join gets killed instead of eating a connection slot for an hour.
Ad hoc ALTER TABLE in a psql session is how schemas drift between environments. Tools like Flyway, Liquibase, Alembic, Atlas or your framework's built-in migrator all solve this; which one you use matters far less than the rule that every schema change lands as a reviewed file. For Postgres specifically, always create indexes with CREATE INDEX CONCURRENTLY in production — the plain form takes a lock that blocks writes for the duration.