< /p>
REFRESH MATERIALIZED VIEW CONCURRENTLY effects;
In the beginning, everything worked well, my materialized view was refreshing, and the disk space usage remained more or less the same.
Question
After a period of time, the disk usage started to increase linearly.
I came to the conclusion that the reason for this increase is the materialized view and run the query from this answer To obtain the following results:
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_rep resentation | 316152215 | 302 MB | 207
------------------------------ | | |
row_count | 1524278 | |
live_tuples | 676439 | |
dead_tuples | 1524208 | |
(11 rows)
Then I found that the last time this table was automatically restored was two days Before, by running:
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup desc;
I decided to manually invoke the vacuum (VERBOSE) effect .It ran for about half an hour and produced the following output:
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 r emove 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.00 s/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 71139 1 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 in dex 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
At this time, I think the problem has been solved, and I started to think about the problem that might interfere with autovacuum To be sure, I ran the query again to find the space usage of the table, and to my surprise it did not change.
Only after the effect of calling REFRESH MATERIALIZED VIEW; not at the same time. Only now the query output of the query table size is:
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 MB br /> ------------------------------ | | |
row_count | 1524385 | |
live_tuples | 676439 | |
dead_tuples | 1524208 | |
(11 rows)
Everything is back to normal…
Problems
Problems It has been resolved, but there is still quite a lot of confusion
>Can someone explain me the problem?
>How can I avoid this in the future?
REFRESH MATERIALIZED CONCURRENTLY Implemented in src/backend/commands/matview.c, the comments are very enlightening:
/*
* refresh_by_match_merge
*
* Refresh a materialized view with transactional semantics, while allowing
* concurrent reads.
*
* This is called after a new version of the data has been created in a
* temporary table. It performs a full outer join against the old version of
* the data, producing "diff" results. This join cannot work if there are any
* duplicated rows in either the old or new versions, in the sense that every
* column would compare as equal between the two rows. It does work correctly
* in the face of rows which have at least one NULL value, with all non-NULL
* columns equal. The behavior of NULLs on equality tests and on UNIQUE
* indexes turns out to be quite convenient here; the tests we ne ed to make
* are consistent with default behavior. If there is at least one UNIQUE
* index on the materialized view, we have exactly the guarantee we need.
*
* The temporary table used to hold the diff results contains just the TID of
* the old record (if matched) and the ROW from the new table as a single
* column of complex record type (if matched) .
*
* Once we have the diff table, we perform set-based DELETE and INSERT
* operations against the materialized view, and discard both temporary
* tables.
*
* Everything from the generation of the new data to applying the differences
* takes place under cover of an ExclusiveLock, since it seems as though we
* would want to prohibit not only concurrent REFRESH operations, but also
* incremental maintenance. It also doesn't seem reasonable or safe to allow
* SELECT FOR UPDATE or SELECT FOR SHARE on rows being updated or deleted by
* this co mmand.
*/
So, refresh the materialized view by deleting rows and inserting new rows from the temporary table. This of course will cause dead tuples and table bloat, which can be passed through your VACUUM (VERBOSE) output to confirm.
In a way, this is the price you pay for the same time.
Secondly, let us debunk the myth that VACUUM cannot remove dead tuples
VACUUM will delete dead rows, but it cannot reduce bloat (it can be done with VACUUM(FULL), but this will lock the view like REFRESH MATERIALIZED VIEW and not happen at the same time).
I suspect that the query you used to determine the number of dead tuples is just an estimate, and it will cause the number of dead tuples to be wrong.
I exemplify all this
CREATE TABLE tab AS SELECT id,'row '|| id AS val FROM generate_series(1, 100000) AS id;
-- make sure autovacuum doesn't spoil our demonstration
CREATE MATERIALIZED VIEW tab_v WITH (autovacuum_enabled = off)
AS SELECT * FROM tab;
-- required for CONCURRENTLY
CREATE UNIQUE INDEX ON tab_v (id);
Use the pgstattuple extension to accurately measure table expansion:
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('tab_v');< br />-[ RECORD 1 ]------+--------
table_len | 4431872
tuple_count | 100000
tuple_len | 3788895
tuple_percent | 85.49
dead_tuple_count | 0
dead_tup le_len | 0
dead_tuple_percent | 0
free_space | 16724
free_percent | 0.38
Now let us delete some rows in the table, refresh and measure again:
DELETE FROM tab WHERE id BETWEEN 40001 AND 80000;
REFRESH MATERIALIZED VIEW CONCURRENTLY tab_v;
SELECT * FROM pgstattuple('tab_v' );
-[ RECORD 1 ]------+--------
table_len | 4431872
tuple_count | 60000
tuple_len | 2268895
tuple_percent | 51.19
dead_tuple_count | 40000
dead_tuple_len | 1520000
dead_tuple_percent | 34.3
free_space | 16724
free_percent | 0.38
Many dead tuples . VACUUM got rid of these:
VACUUM tab_v;
SELECT * FROM pgstattuple('tab_v');
-[ RECORD 1] ------+--------
table_len | 4431872
tuple_count | 60000
tuple_len | 2268895
tuple_percent | 51.19
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 1616724
free_percent | 36.48
The dead tuple disappeared, but now there is a lot of empty space.
In In PostgreSQL 9.5, I decided to create a materialized view “effects” and schedule hourly concurrent refreshes, because I want it to be always available:
REFRESH MATERIALIZED VIEW CONCURRENTLY effects;
In the beginning everything worked well, my materialized view was refreshing, and the disk space usage remained more or less the same.
Problems
After After some time, the disk usage started to increase linearly.
I concluded that the reason for this increase was materialized views and I ran the query from this answer to get the following results:
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_inde xes | 55209426944 | 51 GB | 36220
live_rows_in_text_representation | 316152215 | 302 MB | 207
-------------------------- ---- | | |
row_count | 1524278 | |
live_tuples | 676439 | |
dead_tuples | 1524208 | |
(11 rows)
Then , I found that the last time this table was automatically restored was two days ago, by running:
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup desc;
I decided to manually invoke the vacuum (VERBOSE) effects. It ran for about half an hour and produced the following output:
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.9 1 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< br />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< br />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
At this point, I think the problem has been solved, and I started to think about issues that might interfere with autovacuum. To be sure, I ran the query again to find the space usage of the table, and to my surprise it did not change. < /p>
Only after I call the REFRESH MATERIALIZED VIEW effect; not at the same time. It’s just that the query output of the query table size now is:
what | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+------ -----+--------------+---------------
core_relation_size | 374005760 | 357 MB | 245
visibility_map | 0 | 0 bytes | 0
free_s pace_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_rowspresentation | 316175512 | 302 MB | 207
------------------------------ | | | |
row_count | 1524385 | |
live_tuples | 676439 | |
dead_tuples | 1524208 | |
(11 rows)
Everything is back to normal…
Question< /p>
The problem has been solved, but there is still quite a lot of confusion
>Can someone explain me the problem?
>How can I avoid this in the future?
First, let’s explain this expansion
REFRESH MATERIALIZED CONCURRENTLY is in src/backend/commands/matview. cImplementation, the comments are very enlightening:
/*
* refresh_by_match_merge
*
* Refresh a materialized view with transactional semantics, while allowing
* concurrent reads.
*
* This is called after a new version of the data has been created in a
* temporary table. It performs a full outer join against the old version of
* the data, producing "diff" results. This join cannot work if there are any
* duplicated rows in either the old or new versions, in the sense that every< br /> * column would compare as equal between the two rows. It does work correctly
* in the face of rows which have at least one NULL value, with all non-NULL
* columns equal. The behavior of NULLs on equality tests and on UNIQUE
* indexes turns out to be quite convenient here; the tests we need to make
* are consistent with default behavio r. If there is at least one UNIQUE
* index on the materialized view, we have exactly the guarantee we need.
*
* The temporary table used to hold the diff results contains just the TID of
* the old record (if matched) and the ROW from the new table as a single
* column of complex record type (if matched).
*
* Once we have the diff table, we perform set-based DELETE and INSERT
* operations against the materialized view, and discard both temporary
* tables.
*
* Everything from the generation of the new data to applying the differences
* takes place under cover of an ExclusiveLock, since it seems as though we
* would want to prohibit not only concurrent REFRESH operations, but also
* incremental maintenance. It also doesn't seem reasonable or safe to allow
* SELECT FOR UPDATE or SELECT FOR SHARE on rows being updated or deleted by
* this command.
*/
So, refresh the materialized view by deleting rows and inserting new rows from the temporary table. This of course Will cause dead tuples and table inflation, which can be confirmed by your VACUUM (VERBOSE) output.
To some extent, this is the price you pay for the same time.
Secondly, let us debunk the myth that VACUUM cannot remove dead tuples
VACUUM will delete dead rows, but it cannot reduce bloat (it can be done with VACUUM(FULL), but it will be like REFRESH MATERIALIZED VIEW) Lock the view and not happen at the same time).
I suspect that the query you use to determine the number of dead tuples is just an estimate, and it will cause the number of dead tuples to be wrong.
For example It explains all this
CREATE TABLE tab AS SELECT id,'row '|| id AS val FROM generate_series(1, 100000) AS id;
-- make sure autovacuum doesn't spoil our demonstration
CREATE MATERIALIZED VIEW tab_v WITH (autovacuum_enabled = off)
AS SELECT * FROM tab;
-- required for CONCURRENTLY< br />CREATE UNIQUE INDEX ON tab_v (id);
Use pgstattuple extension to accurately measure table expansion:
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('tab_v');
-[ RECORD 1 ]------+--------
table_len | 4431872
tuple_count | 100000
tuple_len | 3788895
tuple_percent | 85.49
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 16724
free_percent | 0.38
Now let us delete some rows in the table, refresh and measure again:
DELETE FROM tab WHERE id BETWEEN 40001 AND 80000;
REFRESH MATERIALIZED VIEW CONCURRENTLY tab_v;
SELECT * FROM pgstattuple('tab_v');
-[ RECORD 1 ]---- --+--------
table_len | 4431872
tuple_count | 60000
tuple_len | 2268895
tuple_percent | 51.19
dead_tuple_count | 40000
dead_tuple_len | 1520000
dead_tuple_percent | 34.3
free_space | 16724
free_percent | 0.38
A lot of dead tuples. VACUUM gets rid of these:
VACUUM tab_v;
SELECT * FROM pgstattuple('tab_v');
-[ RECORD 1 ]------+--------
table_len | 4431872
tuple_count | 60000
tuple_len | 2268895
tuple_percent | 51.19
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0< br />free_space | 1616724
free_percent | 36.48
< p>The dead tuple has disappeared, but now there is a lot of empty space.