agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedMaterialized views & dead tuples
5+ messages / 4 participants
[nested] [flat]
* Materialized views & dead tuples
@ 2024-06-18 22:28 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Wells Oliver @ 2024-06-18 22:28 UTC (permalink / raw)
To: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Materialized views & dead tuples
@ 2024-06-18 22:38 lennam@incisivetechgroup.com
parent: Wells Oliver <wells.oliver@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: lennam@incisivetechgroup.com @ 2024-06-18 22:38 UTC (permalink / raw)
To: 'Wells Oliver' <wells.oliver@gmail.com>; pgsql-admin
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 <mailto:wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Materialized views & dead tuples
@ 2024-06-19 04:24 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Wells Oliver <wells.oliver@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Achilleas Mantzios @ 2024-06-19 04:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Στις 19/6/24 01:28, ο/η Wells Oliver έγραψε:
> 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?
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.
>
> --
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt (as agents only)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Materialized views & dead tuples
@ 2024-06-19 07:27 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Wells Oliver <wells.oliver@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-06-19 07:27 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Materialized views & dead tuples
@ 2024-06-19 16:18 Wells Oliver <wells.oliver@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Wells Oliver @ 2024-06-19 16:18 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: 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.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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-06-19 16:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-18 22:28 Materialized views & dead tuples Wells Oliver <wells.oliver@gmail.com>
2024-06-18 22:38 ` lennam@incisivetechgroup.com
2024-06-19 04:24 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2024-06-19 07:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-06-19 16:18 ` Wells Oliver <wells.oliver@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox