Обсуждение: Materialized views & dead tuples
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?
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Get materialized views details
Select shcmename,matviewname,matviewowner from pg_matviews;
--Raju
From: Wells Oliver <wells.oliver@gmail.com>
Sent: Tuesday, June 18, 2024 6:29 PM
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Materialized views & dead tuples
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?
--
Wells Oliver
wells.oliver@gmail.com
Στις 19/6/24 01:28, ο/η Wells Oliver έγραψε:
MVs they are just ordinary tables with the addition of having a definition, and not being able to be directly manipulated. They can be bloated just like normal tables, so they need the classic maintenance.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?
-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
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
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.albe@cybertec.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.oliver@gmail.com
wells.oliver@gmail.com