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 | active 00:00:00.022492 | 37506 | 146916 | 40970 | active 00:00:00.015581 | 37505 | 146917 | 40970 | active 00:00:00.006147 | 37503 | 146918 | 40970 | active 00:00:00        | 37475 |        | 40970 | active

The preceding example shows an update workload of four sessions...