Performance problem of *_src_tmp tables

Summary

As mentioned in https://github.com/powa-team/powa-collector/issues/23 having a single table to store all the transient data used for snapshot of remote servers doesn't scale. It leads to massive bloat and autovacuum activity.

The idea to fix that problem is to basically split those *_src_tmp tables per remote server, and replace the DELETE FROM XXX_src_tmp WHERE srvid = X commands with plain TRUNCATE xxx_src_tmp.

While the idea is simple, there are multiple approaches to do it, and multiple problems to consider.

Possible approaches

Here are the possible approaches:

use temporary tables

• Pros:
  • easy to implement
• Cons:
  • will likely lead to massive catalog bloat instead
  • will generate an infinite number of pgss statements on the repository server, so it's basically mean that you cannot use pgss on the repository server anymore

use partitioned tables

• Pros:
  • does not bloat the catalog or have problems with pgss on the repository server
• Cons:
  • harder to implement (compatibility with dump/restore)
  • only compatible with pg10+

use inherited tables

• Pros:
  • does not bloat the catalog or have problems with pgss on the repository server
  • compatible with pg9.6-
• Cons:
  • harder to implement (compatibility with dump/restore)

Problems to consider

general compatibility

powa-collector

Any solution has to ensure that it maintain the compatibility with powa-collector. For instance, anything changed in a new-version of powa-collector has to be compatible with older version of powa-archivist, and vice versa.

As a reminder, powa-collector writes the data to the *_src_tmp using

COPY xxx_src_tmp FROM stdin;

So any changes in powa-archivist have to maintain that compatibility (either natively with partitions, or explicitly with a trigger), as otherwise older versions of powa-collector wouldn't work anymore.