PostgreSQL

psql "postgresql://api@db.internal:5432/app?sslmode=require"
psql -h db.internal -U api -d app -c 'select version();'
psql -Atc 'select count(*) from orders' app        # unaligned, tuples only: scriptable
psql -f migration.sql -v ON_ERROR_STOP=1 app       # abort on the first error

explain (analyze, buffers, verbose, settings)
select o.id, c.name
from orders o join customers c on c.id = o.customer_id
where o.created_at >= now() - interval '7 days';

analyze orders;                                   -- refresh statistics
alter table orders alter column status set statistics 1000;   -- more detail for skewed columns

create index concurrently idx_orders_customer_created on orders (customer_id, created_at desc);
create index concurrently idx_orders_open on orders (created_at) where status = 'open';   -- partial
create index concurrently idx_orders_lower_email on customers (lower(email));             -- expression
create index concurrently idx_docs_tags on docs using gin (tags);                          -- arrays, jsonb
create unique index concurrently idx_users_email on users (lower(email));

-- Indexes nobody uses, biggest first
select s.relname, i.indexrelname, pg_size_pretty(pg_relation_size(i.indexrelid)) as size, i.idx_scan
from pg_stat_user_indexes i join pg_stat_user_tables s using (relid)
where i.idx_scan = 0 and not exists (select 1 from pg_constraint c where c.conindid = i.indexrelid)
order by pg_relation_size(i.indexrelid) desc;

select pid, state, wait_event_type, wait_event, now() - xact_start as xact_age,
       left(query, 80) as query
from pg_stat_activity
where state <> 'idle'
order by xact_start;

-- Who blocks whom
select blocked.pid as blocked_pid, blocked.query as blocked_query,
       blocking.pid as blocking_pid, blocking.query as blocking_query
from pg_stat_activity blocked
join lateral unnest(pg_blocking_pids(blocked.pid)) as bpid on true
join pg_stat_activity blocking on blocking.pid = bpid
where cardinality(pg_blocking_pids(blocked.pid)) > 0;

select pg_cancel_backend(1234);      -- ask nicely: cancels the query
select pg_terminate_backend(1234);   -- close the connection

alter role api set statement_timeout = '30s';
alter role api set idle_in_transaction_session_timeout = '60s';
alter role api set lock_timeout = '5s';

select relname, n_live_tup, n_dead_tup,
       round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) as dead_pct,
       last_autovacuum, last_autoanalyze
from pg_stat_user_tables
order by n_dead_tup desc limit 10;

select datname, age(datfrozenxid) from pg_database order by 2 desc;   -- wraparound risk
vacuum (analyze, verbose) orders;
vacuum full orders;      -- rewrites the table: exclusive lock, needs free space equal to the table

alter table orders set (autovacuum_vacuum_scale_factor = 0.02, autovacuum_analyze_scale_factor = 0.01);

select count(*), state from pg_stat_activity group by state;
show max_connections;
select setting::int * 8192 / 1024 / 1024 as shared_buffers_mb from pg_settings where name = 'shared_buffers';

-- On the primary
select client_addr, state, sent_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
from pg_stat_replication;

-- On a replica
select pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() as lag;

pg_dump -Fc -Z9 -d app -f app.dump                 # custom format: parallel restore, selective
pg_dump -Fc -t orders -d app -f orders.dump
pg_restore -d app -j 4 app.dump
pg_restore -l app.dump > toc.txt                   # edit, then -L toc.txt for a subset
pg_basebackup -D /var/lib/postgresql/restore -Ft -z -P -X stream   # physical, for PITR

-- Largest relations, including indexes and TOAST
select relname, pg_size_pretty(pg_total_relation_size(c.oid)) as total,
       pg_size_pretty(pg_relation_size(c.oid)) as heap
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname not in ('pg_catalog', 'information_schema') and c.relkind = 'r'
order by pg_total_relation_size(c.oid) desc limit 10;

-- Statements by total time (requires pg_stat_statements)
select calls, round(total_exec_time::numeric, 0) as total_ms,
       round(mean_exec_time::numeric, 2) as mean_ms, rows,
       left(query, 100) as query
from pg_stat_statements order by total_exec_time desc limit 10;

-- Cache hit ratio: below ~0.99 on an OLTP system means memory pressure
select sum(blks_hit)::float / nullif(sum(blks_hit) + sum(blks_read), 0) from pg_stat_database;

-- Sequential scans on big tables
select relname, seq_scan, seq_tup_read, idx_scan
from pg_stat_user_tables where seq_scan > 0 order by seq_tup_read desc limit 10;

# Row counts for every table, fast estimate
psql -Atc "select relname, n_live_tup from pg_stat_user_tables order by n_live_tup desc" app

# Kill every idle-in-transaction session older than 10 minutes
psql -c "select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle in transaction' and now() - state_change > interval '10 minutes'" app

# Watch activity live
watch -n2 "psql -Atc \"select pid, state, wait_event, left(query,60) from pg_stat_activity where state <> 'idle'\" app"

# Database sizes
psql -Atc "select datname, pg_size_pretty(pg_database_size(datname)) from pg_database order by pg_database_size(datname) desc" postgres

# Export a query to CSV
psql -c "\copy (select * from orders where created_at > now() - interval '1 day') to 'orders.csv' csv header" app

# Load a CSV
psql -c "\copy orders from 'orders.csv' csv header" app

# Compare schema between two databases
diff <(pg_dump -s -d app_staging) <(pg_dump -s -d app_prod)

# Long-running queries right now
psql -Atc "select pid, now()-query_start as dur, left(query,80) from pg_stat_activity where state='active' and now()-query_start > interval '30 s' order by dur desc" app

# Reset statement statistics before a load test
psql -c 'select pg_stat_statements_reset()' app

# Check for invalid indexes after a failed CREATE INDEX CONCURRENTLY
psql -Atc "select indexrelid::regclass from pg_index where not indisvalid" app

# Connection count by application
psql -Atc "select application_name, count(*) from pg_stat_activity group by 1 order by 2 desc" app

# Confirm TLS is in use
psql -Atc "select ssl, version, cipher from pg_stat_ssl join pg_stat_activity using (pid) where pid = pg_backend_pid()" app