PostgreSQL · Software Engineering Wiki

Cheatsheet#

Task	Command or query
Connect with TLS	`psql "postgresql://alice@db.internal.example:5432/app?sslmode=verify-full"`
List databases / tables / indexes	`\l` / `\dt+` / `\di+`
Describe a table	`\d+ tablename`
Current non-idle sessions	`select pid, state, wait_event, left(query,60) from pg_stat_activity where state <> 'idle';`
Cancel a query, then end the session	`select pg_cancel_backend(pid);` then `select pg_terminate_backend(pid);`
Table size including indexes and TOAST	`select pg_size_pretty(pg_total_relation_size('orders'));`
Slowest statements	`select * from pg_stat_statements order by total_exec_time desc limit 10;`
Real plan with I/O	`explain (analyze, buffers) select ...;`
Who is blocked by whom	`select pid, pg_blocking_pids(pid), left(query,60) from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;`
Replication lag (on primary)	`select client_addr, replay_lag from pg_stat_replication;`
Dead tuples by table	`select relname, n_dead_tup from pg_stat_user_tables order by n_dead_tup desc limit 10;`
Dump one table	`pg_dump -Fc -t orders -d app -f orders.dump`
Time each statement in psql	`\timing on`

A slow or stuck database#

Run these in order; each narrows the cause.

-- 1. What is running, waiting, or idle inside a transaction?
select pid, usename, 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 nulls last;

wait_event_type = 'Lock' means the session waits on another session (see Locks and blocking). state = 'idle in transaction' with a large xact_age is a forgotten BEGIN holding locks and blocking vacuum. IO waits point at disk; LWLock waits under high concurrency often point at too many active connections.

-- 2. Which statements cost the most in total (needs pg_stat_statements)
select calls, round(total_exec_time) 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;

-- 3. How many connections, in which state
select state, count(*) from pg_stat_activity group by state;

Then take the worst statement to Reading a plan. For host-level CPU, memory and disk checks see Linux performance.

pg_stat_statements needs shared_preload_libraries = 'pg_stat_statements' (restart required) and create extension pg_stat_statements; in the database you query.

psql#

psql "postgresql://alice@db.internal.example:5432/app?sslmode=verify-full"
psql -h db.internal.example -U alice -d app -c 'select version();'
psql -Atc 'select count(*) from orders' app          # -A unaligned, -t tuples only: scriptable output
psql -v ON_ERROR_STOP=1 -f migration.sql app         # stop at the first error, exit status 3
psql -1 -v ON_ERROR_STOP=1 -f migration.sql app      # -1 wraps the whole file in one transaction

Put the password in ~/.pgpass (mode 0600) or PGPASSWORD for a single command, never in the connection string on a shared host.

Meta-command	Shows or does
`\d+ table`	Columns, indexes, constraints, triggers, size
`\df+ func`	Function definition
`\dn`, `\du`, `\dp`	Schemas, roles, privileges
`\x auto`	Expanded output when rows are wide
`\timing on`	Execution time per statement
`\watch 5`	Re-run the previous query every 5 seconds
`\copy t from 'f.csv' csv header`	Client-side bulk load; the file is read by psql, not the server
`\e`	Edit the current query in `$EDITOR`
`\conninfo`	Current host, user, database, TLS

A useful ~/.psqlrc:

\timing on
\x auto
\set ON_ERROR_STOP on

Reading a plan#

explain shows the planner’s estimates. explain (analyze) runs the statement and reports actual row counts and times. The gap between estimated and actual rows is usually the diagnosis: a planner that expects 10 rows and gets 100,000 picks the wrong join method and scan type.

explain (analyze, buffers, 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';

explain analyze executes the statement

For insert, update or delete, wrap it in begin; explain analyze ...; rollback; or the change is committed.

From PostgreSQL 18, analyze includes buffer counts by default; on older versions add buffers explicitly. See the EXPLAIN reference.

In the output	Meaning
`Seq Scan` on a large table	No usable index, or the planner expects to read most rows anyway
`rows=10 ... actual ... rows=98234`	Stale or insufficient statistics: `analyze` the table
`Nested Loop` with a large inner side	Usually the same row misestimate
`Batches: 8` on a Hash node	`work_mem` too small, hash spilled to disk
`Sort Method: external merge Disk: ...`	Sort spilled to disk: `work_mem`
`Buffers: shared read=...` high	Pages came from disk (or OS cache), not shared buffers
`Rows Removed by Filter` large	Rows read only to be discarded: index the predicate
`Index Only Scan` with high `Heap Fetches`	Visibility map out of date: vacuum the table

analyze orders;                                              -- refresh statistics
alter table orders alter column status set statistics 1000;  -- finer histogram for a skewed column

Indexes#

An index helps when the predicate is selective and its leading columns match the query. Every index slows writes and uses disk, so unused ones are pure cost.

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_customers_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));

concurrently avoids blocking writes, takes longer, cannot run inside a transaction block, and leaves an INVALID index behind if it fails. Find and drop those:

select indexrelid::regclass from pg_index where not indisvalid;
drop index concurrently idx_orders_open;   -- then retry the create

Composite index column order: equality columns first, then the range or sort column. (customer_id, created_at) serves where customer_id = $1 order by created_at desc; (created_at, customer_id) does not serve it well. A query with a function on the column (where lower(email) = ...) needs an expression index on the same expression.

-- Unused indexes, largest first, excluding those backing constraints
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;

idx_scan counts since the last statistics reset and is per server. Check replicas too before dropping an index that only read traffic uses.

Locks and blocking#

MVCC means plain reads do not block writes and writes do not block reads. Blocking comes from DDL (alter table, create index without concurrently), explicit locks, and transactions that hold row locks, often an idle-in-transaction session.

-- Who blocks whom
select blocked.pid as blocked_pid, left(blocked.query, 60) as blocked_query,
       blocking.pid as blocking_pid, blocking.state as blocking_state,
       left(blocking.query, 60) as blocking_query
from pg_stat_activity blocked
join lateral unnest(pg_blocking_pids(blocked.pid)) as b(pid) on true
join pg_stat_activity blocking on blocking.pid = b.pid;

select pg_cancel_backend(1234);      -- cancel the running query; the session stays
select pg_terminate_backend(1234);   -- close the session; its open transaction rolls back

Both need superuser, the same role as the target, or membership in pg_signal_backend.

A waiting alter table is dangerous in itself: it queues for an ACCESS EXCLUSIVE lock, and every later query on that table queues behind it. Set lock_timeout in migrations so they fail fast instead.

set lock_timeout = '5s';
alter table orders add column note text;

Set timeouts per role or database so one forgotten BEGIN cannot block migrations and vacuum indefinitely:

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

Role settings apply to new sessions only.

Vacuum and bloat#

MVCC keeps old row versions until vacuum marks their space reusable. Autovacuum normally keeps up. When it does not, tables and indexes grow, scans slow down, and the oldest transaction ID (the freeze horizon) stops advancing. Anything holding an old snapshot (a long transaction, an idle-in-transaction session, an abandoned replication slot, a standby with hot_standby_feedback) stops vacuum removing rows newer than that snapshot.

select relname, n_live_tup, n_dead_tup,
       round(100.0 * 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;

-- Transaction ID age; autovacuum forces anti-wraparound vacuums from 200 million by default
select datname, age(datfrozenxid) from pg_database order by 2 desc;

-- Replication slots holding back cleanup and WAL
select slot_name, active, xmin, restart_lsn from pg_replication_slots;

vacuum (analyze, verbose) orders;   -- reclaims space for reuse, no exclusive lock

For a hot table, make autovacuum trigger earlier rather than running vacuum by hand:

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

vacuum full takes an ACCESS EXCLUSIVE lock

It rewrites the table and its indexes, blocking all reads and writes for the duration, and needs free disk roughly equal to the new table size. On a live system use the pg_repack extension, or schedule it in a maintenance window.

Connections and pooling#

Each connection is a separate server process with its own memory. Hundreds of mostly idle connections still cost memory and increase contention. Put a pooler such as PgBouncer between applications and the database, and size the database-side pool by CPU cores and I/O capacity, not by the number of application replicas.

select state, count(*) from pg_stat_activity group by state;
select application_name, count(*) from pg_stat_activity group by 1 order by 2 desc;
show max_connections;
show shared_buffers;

PgBouncer transaction pooling hands a server connection to a different client after each transaction, so session state does not persist: session-level SET, session advisory locks, LISTEN, temporary tables and WITH HOLD cursors break. Protocol-level prepared statements work in transaction mode when max_prepared_statements is non-zero (PgBouncer 1.21 and later). See the PgBouncer config reference.

Replication and backups#

-- On the primary: one row per connected standby
select client_addr, state, sent_lsn, replay_lsn, write_lag, flush_lag, replay_lag
from pg_stat_replication;

-- On a standby
select pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() as replay_delay;

replay_delay on a standby grows when the primary is idle even if nothing is behind, because it measures time since the last replayed transaction. Compare LSNs for a precise answer.

pg_dump -Fc -d app -f app.dump                         # custom format: compressed, selective restore
pg_dump -Fd -j 4 -d app -f app.dir                     # directory format: the only one that dumps in parallel
pg_dump -Fc -t orders -d app -f orders.dump
pg_restore -d app -j 4 app.dump                        # parallel restore works for custom and directory
pg_restore -l app.dump > toc.txt                       # edit the list, then: pg_restore -L toc.txt -d app app.dump
pg_basebackup -D /var/lib/pgsql/restore -Ft -z -P -X stream   # physical base backup, for PITR and new standbys

pg_dump is a logical, consistent snapshot of one database; roles and tablespaces need pg_dumpall --globals-only. Point-in-time recovery needs a physical base backup plus archived WAL. Test restores on a schedule into a scratch database and compare row counts with the source; an untested backup may not restore. See the backup chapter.

Maintenance queries#

-- Largest tables, with heap-only size
select c.oid::regclass as relation, 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;

-- Shared buffer hit ratio per database; a falling value means the working set no longer fits
select datname, round(blks_hit::numeric / nullif(blks_hit + blks_read, 0), 4) as hit_ratio
from pg_stat_database where datname is not null;

-- Tables read mostly by sequential scan
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;

blks_read counts reads outside shared buffers, which may still be served by the OS page cache, so the ratio is a trend indicator rather than a disk-read count.

Roles and grants#

A role is both a user and a group; the distinction is only whether it has LOGIN. Privileges are granted directly to a role or inherited from a group role it is a member of. The common mistake is granting on existing objects and forgetting that future tables get nothing, so a later migration silently breaks the read-only user.

create role app_ro nologin;                          -- a group role for read-only access
create role reporting login password 'set-via-secret' in role app_ro;

grant connect on database app to app_ro;
grant usage on schema public to app_ro;
grant select on all tables in schema public to app_ro;          -- existing tables only
alter default privileges in schema public grant select on tables to app_ro;   -- tables created later

alter default privileges applies only to objects created afterwards by the role that runs it, so run it as the role that owns the migrations (or add for role migrator). Inspect what a role can actually do:

\du+                                                 -- roles, membership, attributes
\dp public.orders                                    -- per-object grants (Access privileges column)
select has_table_privilege('reporting', 'orders', 'select');   -- direct answer
select * from information_schema.role_table_grants where grantee = 'app_ro';

Since PostgreSQL 16, pg_read_all_data and pg_write_all_data are predefined roles that grant blanket access without owning objects, useful for a reporting or backup role. Never grant application logins membership in pg_read_all_data when row-level security is in force: table owners and superusers bypass RLS, and so does anything with BYPASSRLS.

Partitioning#

Declarative partitioning splits one logical table into child tables by a key. It helps when old data is dropped in bulk (detach or drop a partition instead of a slow DELETE), when queries filter on the partition key so the planner prunes to one child, and when autovacuum struggles with a single huge table.

create table events (id bigint, occurred_at timestamptz not null, payload jsonb)
  partition by range (occurred_at);

create table events_2026_09 partition of events
  for values from ('2026-09-01') to ('2026-10-01');
create table events_2026_10 partition of events
  for values from ('2026-10-01') to ('2026-11-01');

-- Drop a month in milliseconds instead of a DELETE that bloats the table
alter table events detach partition events_2026_09 concurrently;   -- 14+; no long lock
drop table events_2026_09;

Partition pruning only happens when the query’s WHERE clause references the partition key with a constant or a stable expression; occurred_at >= now() - interval '1 day' prunes, a join on a non-key column does not. Every partition needs its own indexes (an index on the parent is propagated to children from PostgreSQL 11). Automate partition creation with pg_partman or a scheduled job; a missing future partition makes inserts fail with no partition of relation ... found for row.

select tableoid::regclass as partition, count(*) from events group by 1 order by 1;   -- rows per partition
explain (analyze) select * from events where occurred_at >= '2026-10-15';             -- confirm pruning

Logical replication#

Physical replication (streaming, pg_basebackup) copies the whole cluster byte for byte. Logical replication copies row changes for selected tables via a publication and subscription, so the subscriber can be a different major version, have extra tables and indexes, and take writes. It is the tool for near-zero-downtime major upgrades and for feeding a data warehouse.

-- On the publisher (needs wal_level = logical, restart required)
create publication app_pub for table orders, customers;

-- On the subscriber (schema must already exist)
create subscription app_sub
  connection 'host=primary.internal.example dbname=app user=repl sslmode=verify-full'
  publication app_pub;

select * from pg_stat_replication;              -- on publisher: one row per subscription's walsender
select * from pg_stat_subscription;             -- on subscriber: last received and applied LSN
select * from pg_replication_slots;             -- the slot the subscription created; inactive slots hold WAL

Logical replication does not copy DDL: a schema change on the publisher must be applied to the subscriber first, or apply stalls. It does not replicate sequence values, TRUNCATE before PostgreSQL 11, or large objects. A subscription that falls behind keeps its slot active and WAL accumulates on the publisher, so alert on pg_replication_slots.active and slot lag the same way as for physical slots.

Major version upgrades#

pg_upgrade migrates a data directory in place between major versions far faster than dump and restore, especially with --link (hard-links the data files instead of copying).

pg_upgrade \
  --old-datadir /var/lib/pgsql/16/data \
  --new-datadir /var/lib/pgsql/18/data \
  --old-bindir /usr/lib/postgresql/16/bin \
  --new-bindir /usr/lib/postgresql/18/bin \
  --check                                    # validate first; --check makes no changes

Drop --check to run it for real, with both servers stopped. --link makes the old cluster unusable once the new one starts, so keep a verified backup. After the upgrade, run the script pg_upgrade writes to rebuild optimiser statistics (analyze); until then every plan is guesswork and the database feels slow. Logical replication gives a lower-risk path when downtime must be near zero: replicate into the new-version cluster, let it catch up, then switch traffic.

Troubleshooting#

Symptom	Likely cause	Check
Queries hang, CPU idle	Lock wait	Blocking query above; `wait_event_type = 'Lock'`
Migration hangs and the app stalls	DDL queued for `ACCESS EXCLUSIVE` behind a long transaction	Blocking tree; set `lock_timeout`
`FATAL: sorry, too many clients already`	`max_connections` reached	Connection counts by state and application; add a pooler
`FATAL: remaining connection slots are reserved ...`	Only reserved slots left	Same as above
One query suddenly slow	Plan changed after stats drift or data growth	`explain (analyze)`; `analyze` the table
Table keeps growing, `n_dead_tup` high	Vacuum blocked by an old snapshot or slot	Oldest `xact_start`; `pg_replication_slots`
`database is not accepting commands ... to avoid wraparound data loss`	Transaction ID wraparound protection	`age(datfrozenxid)`; follow the routine vacuuming docs
Disk filling with WAL	Inactive replication slot or failing `archive_command`	`pg_replication_slots`; `pg_stat_archiver`
`SSL connection is required` / `no pg_hba.conf entry`	Client `sslmode` or `pg_hba.conf` mismatch	Server log; `\conninfo` from a working client
Standby lag growing	Network, standby I/O, or replay conflicts	`pg_stat_replication`; standby log
Insert fails `no partition ... found for row`	Missing partition for the key value	List partitions; create the future partition
Read-only role cannot see new tables	`grant` covered existing objects only	`alter default privileges`; re-grant `select`
Logical subscription stopped applying	DDL changed on publisher only, or type mismatch	Subscriber log; apply schema change on subscriber
Slow immediately after a major upgrade	Optimiser statistics not yet rebuilt	Run the `analyze` step `pg_upgrade` generated
`too many clients` only through the pooler	PgBouncer pool exhausted, not the server	`SHOW POOLS` on PgBouncer’s admin console

Oneliners#

# Estimated row counts for every table
psql -Atc "select relname, n_live_tup from pg_stat_user_tables order by n_live_tup desc" app

# End every idle-in-transaction session older than 10 minutes (rolls back their work)
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 on the client
psql -c "\copy (select * from orders where created_at > now() - interval '1 day') to 'orders.csv' csv header" app

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

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

# Active queries running longer than 30 seconds
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

# Invalid indexes left by a failed CREATE INDEX CONCURRENTLY
psql -Atc "select indexrelid::regclass from pg_index where not indisvalid" app

# Confirm this session uses TLS
psql -Atc "select ssl, version, cipher from pg_stat_ssl where pid = pg_backend_pid()" app

# Cache hit ratio for the whole cluster
psql -Atc "select round(100.0*sum(blks_hit)/nullif(sum(blks_hit)+sum(blks_read),0),2) from pg_stat_database" postgres

# Tables with the most sequential scans (candidates for an index)
psql -Atc "select relname, seq_scan, idx_scan from pg_stat_user_tables order by seq_scan desc limit 10" app

# Every lock currently held, with the relation name
psql -c "select l.pid, l.mode, l.granted, c.relname from pg_locks l left join pg_class c on c.oid=l.relation where l.relation is not null order by granted" app

# Longest-running transactions (idle-in-transaction included)
psql -Atc "select pid, state, now()-xact_start as age, left(query,60) from pg_stat_activity where xact_start is not null order by age desc limit 10" app

# Bloat estimate per table using the free pgstattuple extension
psql -Atc "select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables order by pg_relation_size(relid) desc limit 10" app

# Number of connections per source IP address
psql -Atc "select client_addr, count(*) from pg_stat_activity group by 1 order by 2 desc" app

# Replication slot lag in bytes on the primary
psql -Atc "select slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) from pg_replication_slots" app

# Terminate a specific backend by PID (rolls back its work)
psql -c "select pg_terminate_backend(1234)" app

# Show settings that differ from their default
psql -Atc "select name, setting, source from pg_settings where source not in ('default','override') order by name" app

# Force a checkpoint before a maintenance window
psql -c "checkpoint" app

# Count rows in every table without a full scan (estimates)
psql -Atc "select schemaname||'.'||relname, n_live_tup from pg_stat_user_tables order by n_live_tup desc" app

# Age of the oldest prepared transaction (a stuck one blocks vacuum)
psql -Atc "select gid, prepared, database from pg_prepared_xacts order by prepared" app

Scripts#

A backup script that dumps every database in custom format, keeps globals, verifies each dump, and prunes copies older than a retention window. Suitable for a systemd timer.

#!/usr/bin/env bash
set -euo pipefail
dir=${BACKUP_DIR:-/var/backups/pg}
keep_days=${KEEP_DAYS:-14}
ts=$(date +%Y%m%dT%H%M%S)
mkdir -p "$dir"

pg_dumpall --globals-only > "$dir/globals-$ts.sql"

psql -Atc "select datname from pg_database where not datistemplate and datallowconn" postgres |
while IFS= read -r db; do
  out="$dir/$db-$ts.dump"
  pg_dump -Fc -d "$db" -f "$out"
  pg_restore -l "$out" >/dev/null || { echo "verify failed: $out" >&2; exit 1; }
  echo "ok: $out ($(du -h "$out" | cut -f1))"
done

find "$dir" -type f -mtime "+$keep_days" -name '*.dump' -delete
find "$dir" -type f -mtime "+$keep_days" -name 'globals-*.sql' -delete

A bloat and vacuum report that lists tables where dead tuples exceed a threshold and prints the exact vacuum command for each. Read-only; it never runs vacuum itself.

#!/usr/bin/env bash
set -euo pipefail
db=${1:?usage: pg-bloat.sh <database> [dead_pct]}
threshold=${2:-20}

psql -d "$db" -P pager=off <<SQL
select schemaname||'.'||relname as table,
       n_dead_tup as dead,
       round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),1) as dead_pct,
       last_autovacuum,
       'vacuum (analyze, verbose) '||schemaname||'.'||relname||';' as suggested
from pg_stat_user_tables
where n_dead_tup > 0
  and round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),1) >= $threshold
order by dead_pct desc;
SQL

A connection saturation check for monitoring: it warns when used connections approach max_connections and prints the top consumers by application name.

#!/usr/bin/env bash
set -euo pipefail
warn_pct=${WARN_PCT:-80}
read -r used max < <(psql -Atc "select count(*), current_setting('max_connections')::int from pg_stat_activity" postgres | tr '|' ' ')
pct=$(( used * 100 / max ))
echo "connections: $used / $max (${pct}%)"
if (( pct >= warn_pct )); then
  echo "WARNING: over ${warn_pct}% of max_connections" >&2
  psql -c "select coalesce(application_name,'(none)') as app, count(*) from pg_stat_activity group by 1 order by 2 desc limit 10" postgres
  exit 1
fi