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

Thank you Ron
I would appreciate if you could share a good documentation or review link about partitioning tables on postgres.

Enviado do meu iPhone

Em 28 de jul de 2020, à(s) 02:16, Ron <ronljohnsonjr(at)gmail(dot)com> escreveu:


> All lines even old ones might be updated by our application.

Ah. Even so, there are benefits to maintaining "lots of smaller chunks" instead of One Giant Table: "pg_dump --jobs=X" will run faster, for example, when there are more small-to-mid-sized tables instead of A Few Giant Tables. Ditto "reindexdb --jobs=X".

Given that even "old" records get updated, I'd look into some other key that you can "naturally" partition on.

On 7/27/20 10:54 PM, Sidney Aloisio Ferreira Pryor wrote:
Thank you Ron.
Yes there is a column id in which value is provided by a sequence.

But it was not clear to me exactly how to partition?
The idea is to cover a limited range of "id" so there will be less lines to vacuum on each operation?

Because yet we will need eventually to vacuum all others.
All lines even old ones might be updated by our application.

________________________________
De: Ron <ronljohnsonjr(at)gmail(dot)com><mailto:ronljohnsonjr(at)gmail(dot)com>
Enviado: terça-feira, 28 de julho de 2020 00:39
Para: pgsql-admin(at)lists(dot)postgresql(dot)org<mailto:pgsql-admin(at)lists(dot)postgresql(dot)org> <pgsql-admin(at)lists(dot)postgresql(dot)org><mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows

On 7/27/20 5:19 PM, Keith Fiske wrote:
[snip]
> Also, I would not recommend partitioning simply to improve vacuuming.
> Especially if extensive tuning hasn't been tried first. Most times you can
> get per-table tuning working well enough to get autovacuum running
> properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> itself been improved.

SIMPLY to improve vacuum performance? No. But there are reasons that
partitioning was "invented", and minimizing the work needed to be done on
the whole of a Very Large and Rapidly Growing table is one of them.

--
Angular momentum makes the world go 'round.

--
Angular momentum makes the world go 'round.