Re: pg_stat_get_last_vacuum_time(): why non-FULL?

On 2013-03-28 13:11, Martín Marqués wrote:
> 2013/3/27 CR Lender <crlender(at)gmail(dot)com>:
>> In this case I was only trying to make sense of an existing database
>> (8.3). The statistics in pg_stats were way off for some tables, so I
>> wanted to see if (auto)vacuum and (auto)analyze were being run.
>> pg_stat_all_tables() showed last_autoanalyze at >400 days for some of
>> the larger tables. There used to be a weekly cron job with VACUUM FULL
>> ANALYZE, and I was trying to find out if that cron job was still active.
>
> What's your autovacuum configuration? autovacuum_vacuum_threshold?
> autovacuum_analyze_threshold? autovacuum_vacuum_scale_factor?
> autovacuum_analyze_scale_factor?

autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 3
autovacuum_naptime | 1min
autovacuum_vacuum_cost_delay | 20ms
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50

The database is running on PostgreSQL 8.3.6.

I don't maintain this server, and my knowledge about the autovacuum
feature and its settings are sketchy. The values above could be the
defaults for 8.3.6, or they may have been adjusted by the admin.

> Related to your 400+ days not vacuumed tables, are you sure those
> tables have data changes (INSERT/UPDATE/DELETE)? I have some static
> tables with over a year of no vacuum (and autovacuum field never ran
> on that relation).

Yes. Autovacuum and autoanalyze are active, and tables with frequent
DELETEs and UPDATEs appear to be vacuumed and analyzed at least once a
day. Other tables with relatively frequent INSERTs, but irregular
UPDATEs and rare DELETEs go without vacuum/analyze for long periods of
time. Static tables never get analyzed or vacuumed (as expected).

> What does n_dead_tup show?

Here are the statistics for three exemplary tables:

relname: | r____ | oe____ | mv____
| | |
n_tup_ins | 35335 | 179507 | 9562
n_tup_upd | 46727 | 824898 | 0
n_tup_del | 0 | 9709 | 3567
n_tup_hot_upd | 2016 | 793169 | 0
n_live_tup | 206086 | 1132164 | 57964
n_dead_tup | 35583 | 46932 | 5436
last_autovacuum | 2011-05-25 | NULL | NULL
last_autoanalyze | 2013-01-07 | 2012-12-27 | 2012-04-16

I'm not saying that autovacuum/autoanalyze aren't working as designed, I
was just surprised by the long delays.

Concerning the earlier reply to my question...

> On 2013-03-26 19:28, Kevin Grittner wrote:
>> Because FULL is a bit of a misnomer -- there are important things a
>> non-FULL vacuum does which a FULL vacuum does not. In general, a
>> VACUUM FULL should be followed by a non-FULL vacuum to keep the
>> database in good shape.

I've read the manual more carefully now, and I can't see any mention of
what VACUUM does that VACUUM FULL does not. The point about extreme
maintainance is taken, but from what I read, VACUUM FULL should include
everything a normal VACUUM does.

Thanks,
crl