Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_stat_user_tables.n_mod_since_analyze persistence?

pg_stat_user_tables.n_mod_since_analyze persistence?

From
Philip Semanchuk
Date:
Hi all,
I saw some unexpected behavior that I'm trying to understand. I suspect it might be a quirk specific to AWS Aurora and
I'dlike to confirm that. 

When I restart my local Postgres instance (on my Mac), the values in pg_stat_user_tables.n_mod_since_analyze are
preserved.In other words, if table foo had n_mod_since_analyze=33 before the reboot, it still has
n_mod_since_analyze=33after the restart.  

When I restart an AWS Aurora instance, the values in pg_stat_user_tables.n_mod_since_analyze all seem to be reset to 0.


Can anyone confirm (or refute) that the behavior I see on my Mac (preservation of these values through a restart) is
common& expected behavior? 

Thanks
Philip

Re: pg_stat_user_tables.n_mod_since_analyze persistence?

From
Tom Lane
Date:
Philip Semanchuk <philip@americanefficient.com> writes:
> I saw some unexpected behavior that I'm trying to understand. I suspect it might be a quirk specific to AWS Aurora
andI'd like to confirm that. 

> When I restart my local Postgres instance (on my Mac), the values in pg_stat_user_tables.n_mod_since_analyze are
preserved.In other words, if table foo had n_mod_since_analyze=33 before the reboot, it still has
n_mod_since_analyze=33after the restart.  

> When I restart an AWS Aurora instance, the values in pg_stat_user_tables.n_mod_since_analyze all seem to be reset to
0. 

> Can anyone confirm (or refute) that the behavior I see on my Mac (preservation of these values through a restart) is
common& expected behavior? 

Yeah, in PG those stats would be preserved, at least as long as it's
a clean shutdown.

            regards, tom lane

Re: pg_stat_user_tables.n_mod_since_analyze persistence?

From
Philip Semanchuk
Date:

> On Feb 15, 2021, at 3:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Philip Semanchuk <philip@americanefficient.com> writes:
>> I saw some unexpected behavior that I'm trying to understand. I suspect it might be a quirk specific to AWS Aurora
andI'd like to confirm that. 
>
>> When I restart my local Postgres instance (on my Mac), the values in pg_stat_user_tables.n_mod_since_analyze are
preserved.In other words, if table foo had n_mod_since_analyze=33 before the reboot, it still has
n_mod_since_analyze=33after the restart.  
>
>> When I restart an AWS Aurora instance, the values in pg_stat_user_tables.n_mod_since_analyze all seem to be reset to
0. 
>
>> Can anyone confirm (or refute) that the behavior I see on my Mac (preservation of these values through a restart) is
common& expected behavior? 
>
> Yeah, in PG those stats would be preserved, at least as long as it's
> a clean shutdown.


Thanks, Tom. A colleague pointed me to a blog post by Michael Vitale that confirms this bug on AWS and contains more
detail:
https://elephas.io/685-2/


Hope this helps someone else
Philip