Re: How can i be certain autovacuum is causing reuse if table still grows

On Tue, Aug 4, 2020 at 11:46 AM Sidney Aloisio Ferreira Pryor <
sidney(at)tjrj(dot)jus(dot)br> wrote:

>
> Thank you very much Guillaume and Keith for your straight and quick
> answers.
>
> I was looking this database size and tup_update history.
> It never shrank even with autovacuum enabled (growing 100GB a month).
> The number of updates was always this high.
> But on last 2 months its size started to grow a lot (growing 50GB a day).
>
> We will schedule a vacuum full to stop adding disks.
> But i am truly afraid postgres is not suitable for this application.
> We are talkiing with dev team for the last three weeks.
> And so far no answer about optimizing the number of updates.
>
> It is true we have never executed a vacuum full on this database.
> But its size even growing was not so much to worry about.
> And if postgres was never able to follow the number of updates and reuse
> efficiently.
> I am afraid an autovacuum tuning may not be sufficente to reuse after we
> finish vacuum full and downsize the database from 4tb to 200gb.
>
>
>
>
You stated in your opening email that you just had the default autovacuum
settings in place. The defaults are often nowhere near sufficient for most
production use cases. But everyone's production use case is different, so
common settings that work with the least issues are the default. If
autovacuum is not running frequently enough, especially on larger tables
with frequent updates, you will see exactly what happened here over time:
autovacuum will not keep up with the write rate and take longer and longer
to run over time.

If autovacuum, or manual vacuums, are scheduled often enough to keep up
with marking old rows as reusable space, then any new writes will use that
empty space vs allocating additional pages and constantly growing your
table's size. This includes indexes as well. If you're adding NEW rows, of
course it will continue to grow, but that is not autovacuum's problem. The
trick is, for now, figuring out how often autovac will need to run to keep
up with your write rate. You may need to adjust your cost settings as well
to ensure autovac runs a little more aggressively, at least on this table.
The blog I just shared goes over how to figure out how to ensure autovacuum
is run at least once per day based on a consistent avg row change per day.
If you have occasions where higher writes than normal are occurring,
autovac may run more often in that case or you can even schedule a manual
vacuum.

Thankfully you are on at least PG 9.6 as well, where vacuum has been made
to run much more efficiently. Please just make sure you are on the latest
minor release. Also, more recent major versions of PostgreSQL have improved
upon this even more, so I would highly suggest starting to plan for a major
version upgrade. PG9.6 is scheduled to be EOL next year as well, so you
want to be looking into that not just for vacuum efficiency.

If you get vacuum tuned properly, on a table that isn't as massively
bloated as that one was, I think you will be fine.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com