Removing issues that cause bloat

Bloat can be caused by long running queries or long running write transactions that execute alongside write-heavy workloads. Resolving that is mostly down to understanding the workloads running on the server.

Getting ready

Look at the age of the oldest snapshots that are running, like this:

postgres=# SELECT now() - case when backend_xid is not null then xact_start else query_start end as age,
pid, backend_xid as xid, backend_xmin as xmin, state
FROM pg_stat_activity ORDER BY 1 desc;
age       |  pid  |  xid   | xmin  |       state
-----------------+-------+--------+-------+---------------------
00:06:59.248634 | 37485 |  40970 |       | idle in transaction
00:00:00.036321 | 37504 | 146914 | 40970...