# PostgreSQL

> Diagnose a slow or blocked PostgreSQL database: psql, query plans, indexes, locks, vacuum and bloat, pooling, replication and backups.

Canonical: https://www.wiki.jodisand.me/postgresql/
Reviewed: 2026-09-24
Related: [Vault](https://www.wiki.jodisand.me/vault/index.md), [Redis](https://www.wiki.jodisand.me/redis/index.md), [Linux performance](https://www.wiki.jodisand.me/linux-performance/index.md), [Kubernetes](https://www.wiki.jodisand.me/kubernetes/index.md)


## 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.

```sql
-- 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](#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.

```sql
-- 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](#reading-a-plan). For host-level CPU, memory and disk checks see [Linux performance](https://www.wiki.jodisand.me/linux-performance/#the-first-minute).

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

## psql

```sh
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`:

```text
\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.

```sql
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';
```

> [!WARNING] `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](https://www.postgresql.org/docs/current/sql-explain.html).

| 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 |

```sql
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.

```sql
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:

```sql
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.

```sql
-- 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.

```sql
-- 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;
```

```sql
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.

```sql
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:

```sql
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.

```sql
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;
```

```sql
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:

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

> [!WARNING] `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.

```sql
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](https://www.pgbouncer.org/config.html).

## Replication and backups

```sql
-- 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.

```sh
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](https://www.postgresql.org/docs/current/backup.html).

## Maintenance queries

```sql
-- 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.

```sql
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:

```sql
\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.

```sql
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`.

```sql
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.

```sql
-- 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;
```

```sql
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).

```sh
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](https://www.postgresql.org/docs/current/routine-vacuuming.html) |
| 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

```sh
# 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.

```sh
#!/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.

```sh
#!/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.

```sh
#!/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
```

## Further reading

- [PostgreSQL documentation](https://www.postgresql.org/docs/current/) — the version-matched manual
- [Monitoring statistics views](https://www.postgresql.org/docs/current/monitoring-stats.html) and the [EXPLAIN reference](https://www.postgresql.org/docs/current/sql-explain.html)
- [Routine vacuuming](https://www.postgresql.org/docs/current/routine-vacuuming.html) and [logical replication](https://www.postgresql.org/docs/current/logical-replication.html)
- [pg_upgrade](https://www.postgresql.org/docs/current/pgupgrade.html) and the [PgBouncer configuration reference](https://www.pgbouncer.org/config.html)