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
WHERE backend_type = 'client backend'
ORDER BY 1 DESC;
age             |  pid  |   xid    |   xmin   |        state       
----------------+-------+----------+----------+------------------
00:00:25.791098 | 27624 |  ...