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@gmail.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.
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.