does refreshing materialized view make the database bloat?

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: does refreshing materialized view make the database bloat?
Date: 2023-01-14 06:04:13
Message-ID: CACJufxFm0zkdf6CX4PXjoYHPRB-VSstLb4SRP=5Q9cXn+gwCWA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

src_backend_commands_matview.c
547: /*
548: * refresh_by_match_merge
549: *
550: * Refresh a materialized view with transactional semantics, while
allowing
551: * concurrent reads.
552: *
553: * This is called after a new version of the data has been created in a
554: * temporary table. It performs a full outer join against the old
version of
555: * the data, producing "diff" results. This join cannot work if there
are any
556: * duplicated rows in either the old or new versions, in the sense
that every
557: * column would compare as equal between the two rows. It does work
correctly
558: * in the face of rows which have at least one NULL value, with all
non-NULL
559: * columns equal. The behavior of NULLs on equality tests and on
UNIQUE
560: * indexes turns out to be quite convenient here; the tests we need to
make
561: * are consistent with default behavior. If there is at least one
UNIQUE
562: * index on the materialized view, we have exactly the guarantee we
need.
563: *
564: * The temporary table used to hold the diff results contains just the
TID of
565: * the old record (if matched) and the ROW from the new table as a
single
566: * column of complex record type (if matched).
567: *
568: * Once we have the diff table, we perform set-based DELETE and INSERT
569: * operations against the materialized view, and discard both temporary
570: * tables.
571: *
572: * Everything from the generation of the new data to applying the
differences
573: * takes place under cover of an ExclusiveLock, since it seems as
though we
574: * would want to prohibit not only concurrent REFRESH operations, but
also
575: * incremental maintenance. It also doesn't seem reasonable or safe
to allow
576: * SELECT FOR UPDATE or SELECT FOR SHARE on rows being updated or
deleted by
577: * this command.
578: */
579:

> Once we have the diff table, we perform set-based DELETE and INSERT
> operations against the materialized view, and discard both temporary
> tables.
>

Here the temporary tables are "discard" meaning the temporary tables are
deleted and the temporary tables' spaces are reclaimed immediately?
Or the temporary tables are deleted and the spaces will be reclaimed by
another mechanism?

simplify:does refreshing materialized view make the database bloat.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ken Tanzer 2023-01-14 06:17:27 Re: Intervals and ISO 8601 duration
Previous Message Pavel Stehule 2023-01-14 06:01:00 Re: Intervals and ISO 8601 duration