Re: Support for VACUUMing Foreign Tables

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.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com