We are running:
user@host:~$ psql -d database -c "SELECT version();"
version
---------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)
on:
user@host:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.6 LTS
Release: 16.04
Codename: xenial
and have the following setup:
database=# \d+ schema.table
Table "schema.table"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------+-----------------------------+-----------+----------+-------------------------------------------------+----------+--------------+-------------
column_1 | bigint | | not null | nextval('table_id_seq'::regclass) | plain | |
column_2 | character varying | | not null | | extended | |
column_3 | character varying | | not null | | extended | |
column_4 | character varying | | not null | | extended | |
column_5 | timestamp without time zone | | not null | | plain | |
column_6 | timestamp without time zone | | | | plain | |
column_7 | character varying | | not null | | extended | |
column_8 | jsonb | | not null | | extended | |
column_9 | jsonb | | | | extended | |
column_10 | character varying | | not null | | extended | |
column_11 | character varying | | not null | | extended | |
column_12 | character varying | | | | extended | |
column_13 | character varying | | | | extended | |
column_14 | timestamp with time zone | | not null | | plain | |
column_15 | timestamp with time zone | | not null | | plain | |
Indexes:
"table_pkey" PRIMARY KEY, btree ( column_1 )
"table_idx_1" btree ( column_11)
"table_idx_2" btree ( column_4, column_2, column_7, column_5, column_6 )
"table_idx_3" btree ( column_7, column_11, column_15 )
"table_idx_4" btree ( column_7, column_11, column_14 )
"table_idx_5" btree ( column_7, column_11, column_5 )
"table_idx_6" btree ( column_7, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER ), column_5 )
"table_idx_7" btree ( column_15 )
"table_idx_8" btree ( column_4, column_2, column_7, column_5, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER ) )
"table_idx_9" btree ( column_4, column_2, column_7, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER) )
"table_idx_a" btree ( column_7, column_4, column_2, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER), ( ( column_8 ->> 'string_2'::TEXT )::INTEGER ) ) WHERE column_7::TEXT = 'string_3'::TEXT
Check constraints:
"table_check_constraints" CHECK ( lower( column_10::TEXT ) <> 'string_4'::TEXT OR column_9 IS NOT NULL AND column_6 IS NOT NULL )
Autovacuum is on and configured with:
user@host:~$ psql -d database -c "SELECT name, setting, pending_restart FROM pg_settings WHERE NAME ILIKE '%autovacuum%' ORDER BY name;"
name | setting | pending_restart
-------------------------------------+-----------------------+-----------------
autovacuum | on | f
autovacuum_analyze_scale_factor | 0.002 | f
autovacuum_analyze_threshold | 10 | f
autovacuum_freeze_max_age | 200000000 | f
autovacuum_max_workers | 5 | f
autovacuum_multixact_freeze_max_age | 400000000 | f
autovacuum_naptime | 30 | f
autovacuum_vacuum_cost_delay | 10 | f
autovacuum_vacuum_cost_limit | 1000 | f
autovacuum_vacuum_scale_factor | 0.001 | f
autovacuum_vacuum_threshold | 25 | f
autovacuum_work_mem | -1 | f
log_autovacuum_min_duration | 0 (env 1) /-1 (env 2) | f
(13 rows)
The following sequence of events took place in environment 1, during which autovacuum
was on and configured as above:
- Nightly
VACUUM (VERBOSE, ANALYZE)
of database added. - Some time passes during which bloat is at normal operational level.
- Nightly
VACUUM (VERBOSE, ANALYZE)
of database is removed. - Index
table_idx_8
that includes a JSONB data type column is added. - Index
table_idx_9
that includes a JSONB data type column is added. - Bloat growth spurt starts and continues for 2 days until it peaks.
VACUUM (VERBOSE, FULL)
of table.- Bloat returns to normal operational levels and stays there.
The database size (GB) looked like this in environment 1 during this sequence of events:
And this is what bloat (GB) looked like in environment 1:
The number of live rows in environment 1:
The number of dead rows in environment 1:
The following sequence of events took place in environment 2, during all of which autovacuum
was on and configured as above:
- Nightly
VACUUM (VERBOSE, ANALYZE)
of database added. - Some time passes during which bloat is at normal operational level.
- Nightly
VACUUM (VERBOSE, ANALYZE)
of database is removed. - Index
table_idx_8
that includes a JSONB data type column is added. - Index
table_idx_9
that includes a JSONB data type column is added. - Bloat growth spurt starts and continues for 2 days until it peaks and brings DB down (disk full).
TRUNCATE TABLE schema.table
.- The schema.table table is populated again.
- Bloat does not stabilise and grows until it peaks again.
TRUNCATE TABLE schema.table
before the disk fills up again.- VACUUM (VERBOSE, FULL) of database.
- The schema.table table is populated again.
- Bloat continues to grow!
The database size (GB) in environment 2 looked like this during this sequence of events :
And this is what bloat (GB) looked like in environment 2:
The only difference between these two environments is that they are speced slightly differently (with 2 being less powerful). During these sequences of events, write/read volumes were unchanged on each environment. We are using this query to measure the bloat in bytes.
I have cross checked PostgreSQL logs, monitoring logs and commit logs (Git) and identified the addition of the two indexes as the trigger for the bloat but:
- Is that right? Can adding an index trigger such a bloat growth spurt?
- Why did adding the indexes trigger the bloat, if it did?
- Why did environment 1 stabilise and environment 2 not?
- How can we stabilise environment 2?
Any help with answering these questions would be greatly appreciated and needless to say, I'm happy to provide any other information that I have missed that could be helpful.
log_autovacuum_min_duration
to a value different from -1 so that you get log messages about long autovacuum runs. Examinepg_stat_user_tables
, particularly the columnsn_live_tup
,n_dead_tup
andlast_autovacuum
. Add your findings to the question. Perhaps the index made autovacuum slower. – Laurenz Albe May 10 at 15:39table_idx_6
was added a while back and monitoring didn't register any changes. I suspect thattable_idx_8
&table_idx_9
are the issue because bloat started growing just after they were added, but like I said, I would really appreciate a second/third, etc. opinion/interpretation. – dw8547 May 10 at 17:19n_live_tup/n_dead_tup
plots for environment 1 to the question. I will addautovacuum
timeline shortly. – dw8547 May 11 at 11:48