10 Actually Useful PostgreSQL Commands (2026)

You know SELECT, INSERT, and CREATE TABLE. This list targets the commands that database administrators and performance engineers use daily — debugging slow queries, managing connections, inspecting bloat, and safely migrating schemas on production systems. Every command here solves a problem you don’t know you have until 2 AM.

Performance & Debugging

EXPLAIN ANALYZE — Executes a query and shows the actual execution plan with timings. The single most important tool for query optimization. Add BUFFERS to see cache hit ratios.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM orders WHERE status = 'pending';

Output: Shows sequential scan vs index scan, actual vs estimated rows, execution time per node, and buffer usage. Look for “Seq Scan on large_table” with high rows removed by filter — that’s your missing index.

pg_stat_statements — Tracks execution statistics for every query. Find your top time-consuming queries, most frequent ones, and worst cache misses.

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Output: The top 10 queries by total execution time. Mean time under 1ms is good. High calls + high mean time is your biggest optimization opportunity.

pg_terminate_backend — Kills a specific database connection by its PID. Essential when a query won’t cancel, a connection is stuck, or you need to drop a database that has active connections.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction' AND age(now(), state_change) > interval '1 hour';

Output: Terminates all idle transactions older than one hour. Run this before DROP DATABASE to disconnect everyone.

Maintenance

Table Bloat Check — Estimates wasted space in tables and indexes. PostgreSQL’s MVCC creates dead tuples that VACUUM cleans, but bloat accumulates.

SELECT schemaname, tablename,
  n_dead_tup, n_live_tup,
  round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Output: Tables with high dead tuple counts. Above 20% dead ratio means VACUUM is falling behind — check autovacuum settings.

VACUUM and ANALYZE — VACUUM reclaims storage from dead tuples. ANALYZE updates query planner statistics. Run them together weekly on production.

VACUUM (ANALYZE, VERBOSE) your_table;

Output: Shows pages removed, tuples deleted, and new statistics. VERBOSE helps you see actual progress. Never run VACUUM FULL on production — it locks the table.

pg_ls_waldir — Lists Write-Ahead Log (WAL) files. WAL accumulation causes disk-full disasters. If WAL files exceed a few GB, check replication lag or archive failures.

SELECT count(*), sum(size) / 1048576 AS total_mb
FROM pg_ls_waldir();

Output: Total WAL size in MB. 1-2 GB is normal for active systems. 10+ GB means WAL archiving or replication is broken.

Schema & Data Management

pg_dump with –exclude-table — Dumps a database excluding large or unnecessary tables. Essential for creating development snapshots without 50 GB of logs.

pg_dump -d your_db --exclude-table='audit_logs' --exclude-table='raw_events' -Fc > db_dump.dump

Output: A compressed dump file containing everything except those two tables. The -Fc (custom format) allows selective restore.

COPY to/from CSV — The fastest way to import or export bulk data. 10-100x faster than INSERT.

COPY users (email, name, created_at)
TO '/tmp/users_export.csv' DELIMITER ',' CSV HEADER;

COPY users (email, name, created_at)
FROM '/tmp/users_import.csv' DELIMITER ',' CSV HEADER;

Output: First exports all users to CSV. Second imports from CSV. Use FREEZE option for initial data loads to skip WAL logging.

CREATE INDEX CONCURRENTLY — Creates an index without locking the table for writes. Regular CREATE INDEX blocks writes. CONCURRENTLY doesn’t, but takes 2-3x longer.

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status_created
ON orders (status, created_at DESC);

Output: Index is built in the background while production reads and writes continue. Monitor progress with pg_stat_progress_create_index.

SET LOCAL for Session Settings — Changes a setting for the current transaction only, not the whole session. Perfect for setting work_mem for one big query without affecting others.

BEGIN;
SET LOCAL work_mem = '256MB';
SET LOCAL statement_timeout = '5min';
SELECT * FROM massive_table ORDER BY complex_column;
COMMIT;

Output: This query gets 256 MB of working memory. Other queries in the session are unaffected. After COMMIT, settings revert to defaults.