Removing issues that cause bloat

Bloat is disk space previously used by tables that is available for reuse by the database but no longer by the filesystem. It 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 that are 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 |          | 10671262 | active
00:00:08.018103 | 27591 |          |          | idle in transaction
00:00:00.002444...