title: Refreshing materialized view CONCURRENTLY causes table bloat
body: <p>In PostgreSQL 9.5 I've decided to create a materialized view "effects" and scheduled an hourly concurrent refresh, since I wanted it to be always available:</p>
  
  <pre><code>REFRESH MATERIALIZED VIEW CONCURRENTLY effects;
  </code></pre>
  
  <p>In the beginning everything worked well, my materialized view was refreshing and disk space usage remained more or less constant.</p>
  
  <hr>
  
  <h2>The Issue</h2>
  
  <p>After some time though, disk usage started to <em>linearly</em> grow.</p>
  
  <p>I've concluded that the reason for this growth is the materialized view and ran the query from <a href="https://dba.stackexchange.com/a/23933/157163">this</a> answer to get the following result:</p>
  
  <pre><code>               what                |  bytes/ct   | bytes_pretty | bytes_per_row
  -----------------------------------+-------------+--------------+---------------
   core_relation_size                | 32224567296 | 30 GB        |         21140
   visibility_map                    |      991232 | 968 kB       |             0
   free_space_map                    |     7938048 | 7752 kB      |             5
   table_size_incl_toast             | 32233504768 | 30 GB        |         21146
   indexes_size                      | 22975922176 | 21 GB        |         15073
   total_size_incl_toast_and_indexes | 55209426944 | 51 GB        |         36220
   live_rows_in_text_representation  |   316152215 | 302 MB       |           207
   ------------------------------    |             |              |
   row_count                         |     1524278 |              |
   live_tuples                       |      676439 |              |
   dead_tuples                       |     1524208 |              |
  (11 rows)
  </code></pre>
  
  <p>Then, I found that the last time this table was autovacuumed was two days ago, by running:</p>
  
  <pre><code>SELECT relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup desc;
  </code></pre>
  
  <p>I decided to manually call <code>vacuum (VERBOSE) effects</code>. It ran for about half an hour and produced the following output:</p>
  
  <pre><code>vacuum (VERBOSE) effects;
  INFO:  vacuuming "public.effects"
  INFO:  scanned index "effects_idx" to remove 129523454 row versions
  DETAIL:  CPU 12.16s/55.76u sec elapsed 119.87 sec
  
  INFO:  scanned index "effects_campaign_created_idx" to remove 129523454 row versions
  DETAIL:  CPU 19.11s/154.59u sec elapsed 337.91 sec
  
  INFO:  scanned index "effects_campaign_name_idx" to remove 129523454 row versions
  DETAIL:  CPU 28.51s/151.16u sec elapsed 315.51 sec
  
  INFO:  scanned index "effects_campaign_event_type_idx" to remove 129523454 row versions
  DETAIL:  CPU 38.60s/373.59u sec elapsed 601.73 sec
  
  INFO:  "effects": removed 129523454 row versions in 3865537 pages
  DETAIL:  CPU 59.02s/36.48u sec elapsed 326.43 sec
  
  INFO:  index "effects_idx" now contains 1524208 row versions in 472258 pages
  DETAIL:  113679000 index row versions were removed.
  463896 index pages have been deleted, 60386 are currently reusable.
  CPU 0.00s/0.00u sec elapsed 0.01 sec.
  
  INFO:  index "effects_campaign_created_idx" now contains 1524208 row versions in 664910 pages
  DETAIL:  121637488 index row versions were removed.
  41014 index pages have been deleted, 0 are currently reusable.
  CPU 0.00s/0.00u sec elapsed 0.00 sec.
  
  INFO:  index "effects_campaign_name_idx" now contains 1524208 row versions in 711391 pages
  DETAIL:  125650677 index row versions were removed.
  696221 index pages have been deleted, 28150 are currently reusable.
  CPU 0.00s/0.00u sec elapsed 0.00 sec.
  
  INFO:  index "effects_campaign_event_type_idx" now contains 1524208 row versions in 956018 pages
  DETAIL:  127659042 index row versions were removed.
  934288 index pages have been deleted, 32105 are currently reusable.
  CPU 0.00s/0.00u sec elapsed 0.00 sec.
  
  INFO:  "effects": found 0 removable, 493 nonremovable row versions in 3880239 out of 3933663 pages
  DETAIL:  0 dead row versions cannot be removed yet.
  
  There were 666922 unused item pointers.
  Skipped 0 pages due to buffer pins.
  0 pages are entirely empty.
  CPU 180.49s/788.60u sec elapsed 1799.42 sec.
  
  INFO:  vacuuming "pg_toast.pg_toast_1371723"
  INFO:  index "pg_toast_1371723_index" now contains 0 row versions in 1 pages
  DETAIL:  0 index row versions were removed.
  0 index pages have been deleted, 0 are currently reusable.
  CPU 0.00s/0.00u sec elapsed 0.00 sec.
  
  INFO:  "pg_toast_1371723": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
  DETAIL:  0 dead row versions cannot be removed yet.
  There were 0 unused item pointers.
  Skipped 0 pages due to buffer pins.
  0 pages are entirely empty.
  CPU 0.00s/0.00u sec elapsed 0.00 sec.
  
  VACUUM
  </code></pre>
  
  <p>At this point I thought the problem was resolved and started thinking what could interfere with the autovacuum. To be sure, I ran again the query to find space usage by that table and to my surprise it didn't change.</p>
  
  <p>Only after I called <code>REFRESH MATERIALIZED VIEW effects;</code> <strong>not concurrently</strong>. Only now the output of the query to check table size was:</p>
  
  <pre><code>               what                | bytes/ct  | bytes_pretty | bytes_per_row
  -----------------------------------+-----------+--------------+---------------
   core_relation_size                | 374005760 | 357 MB       |           245
   visibility_map                    |         0 | 0 bytes      |             0
   free_space_map                    |         0 | 0 bytes      |             0
   table_size_incl_toast             | 374013952 | 357 MB       |           245
   indexes_size                      | 213843968 | 204 MB       |           140
   total_size_incl_toast_and_indexes | 587857920 | 561 MB       |           385
   live_rows_in_text_representation  | 316175512 | 302 MB       |           207
   ------------------------------    |           |              |
   row_count                         |   1524385 |              |
   live_tuples                       |    676439 |              |
   dead_tuples                       |   1524208 |              |
  (11 rows)
  </code></pre>
  
  <p>And everything went back to normal...</p>
  
  <hr>
  
  <h2>Questions</h2>
  
  <p>The problem is solved but there's still a fair amount of confusion</p>
  
  <ol>
  <li>Could anyone please explain what was the issue I experienced?</li>
  <li>How could I avoid this in the future?</li>
  </ol>