From: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Materialized views & dead tuples |
Date: | 2024-06-19 16:18:27 |
Message-ID: | CAOC+FBUS6CX7QyJuqhtc94UC2Sg45a7kPxS-h=nDYEcUiLz7BA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Ah, thank you, most of these are CONCURRENTLY so this makes sense.
Appreciate it.
On Wed, Jun 19, 2024 at 12:27 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:
> On Tue, 2024-06-18 at 15:28 -0700, Wells Oliver wrote:
> > Apologies for the daft question, but I am surprised to see materialized
> views
> > show up in pg_stat_user_tables with lots of dead tuples. These are
> rematerialized
> > nightly and, I thought, this had the effect of replacing/recreating them
> anew.
> > Can someone shed some light on this?
>
> It makes a difference if you use REFRESH MATERIALIZED VIEW or
> REFRESH MATERIALIZED VIEW CONCURRENTLY.
>
> The first statement will just discard the materialized table and create it
> anew,
> and you will never see a dead tuple.
> The second statement executes the query and updates the materialized
> table, which
> can lead to dead tuples just like a normal UPDATE or DELETE.
>
> Yours,
> Laurenz Albe
>
--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | James Pang | 2024-06-20 05:05:28 | password_rollover_time like Oracle |
Previous Message | Idelette Tchakounte | 2024-06-19 16:07:43 | Re: Excluding event triggers from a pg_dump |