10 Actually Useful PostgreSQL Commands (2026)

In this tutorial, you'll learn about 10 actually useful postgresql commands (2026). We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

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.