Re: Support for VACUUMing Foreign Tables

On Fri, May 14, 2021 at 11:48 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Fri, May 14, 2021 at 6:35 AM tsunakawa(dot)takay(at)fujitsu(dot)com
> <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> >
> > From: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
> > > I think it will be useful to allow foreign tables to be VACUUMed if
> > > the underlying FDW supports, currently VACUUM doesn't support foreign
> > > tables, see [1].
> >
> > Could you let us imagine more concretely how useful it will be? While TRUNCATE can be part of an application's data processing as alternative to DELETE, I think VACUUM is purely the data storage maintenance that's performed by the DBA and can be done naturally locally on the server where the table resides. (The existing ANALYZE on FDW is an exception; it's useful to also have data statistics locally.)
>
> I agree that TRUNCATE is a user-visible command so it is good to send
> such a command to a remote server. But, sending ANALYZE and VACUUM to
> FDW can have a similar use case. I mean based on the current data
> changes/operation the DBA can decide whether it needs to generate the
> statistic or whether it needs to do garbage collection. I think
> ideally both these operations can be performed locally on the remote
> server but if we are sending ANALYZE to the remote server then IMHO
> there is some merit to sending VACUUM as well. Having said that, I
> think the purpose of ANALYZE can be generic across the databases that
> we want to update the statistic but VACUUM is different, it too much
> depends upon how the data is stored (what MVCC mechanism they are
> using) on the remote server and what is the vacuuming need for that
> database. So maybe garbage collection should be controlled locally by
> the DBA on that server.

Agree. Different MVCC databases can have different commands to clean
up the bloat, their implementation of the vacuum's FdwRoutine can be
implemented accordingly. postgres_fdw can prepare the "VACUUM rel;"
command. Having said that, I don't think all the remote databases will
have the same ANALYZE rel; or TRUNCATE rel; commands either. It's
better left to the implementation of the FdwRoutine for a particular
remote database.

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com