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.)

This can be useful in situations like where there are many remote
postgres servers that are connected to a single coordinator on which
foreign tables are defined for each of the remote tables. In this
case, the DBA (or whoever is responsible to do that job) doesn't have
to figure out which remote server should be logged onto to perform the
VACUUM. They can issue VACUUM command on the foreign table from the
coordinator server.

> > this may not be much useful for FDWs that connect to remote non-MVCC
> > databases where the concept of VACUUM may not apply, but for
> > postgres_fdw and others it might help.
>
> Can you show some examples of "others"? I believe we should be careful not to make the FDW interface a swamp for functions that are only convenient for PostgreSQL.

There are other databases that have MVCC implemented for which the
bloat clean up might be necessary at some point. They may not have the
same terminology that postgres has for cleaning up the bloat. For
instance, MySQL (instead of VACUUM they have OPTIMIZE TABLE command)
which can be connected to postgres_fdw using supported fdw

And see [1] for the databases that have MVCC support. I'm not sure if
all of them have a FDW to connect to postgres.

[1] https://dbdb.io/browse?concurrency-control=multi-version-concurrency-control-mvcc

> How about adding a routine to the FDW interface that allows to execute an arbitrary command like the following? VACUUM will be able to use this.
>
> PGresult *DoCommandPathThrough(ForeignTable *table, const char *command);
>
> Or, maybe it's more flexible to use ForeignServer instead of ForeignTable.

I agree with Michael Paquier's response to this point that it can be
an issue from a security standpoint. But we could have had such kind
of a generic API for commands like TRUNCATE, ANALYZE, VACUUM etc. void
ExecRemoteCommand(ForeignServer *server, const char *command, void
*input_params, void *output_params); with the API knowing all the
supported commands and erroring out on unsupported commands. Now, we
have a separate API for each of the supported commands which looks
actually cleaner.

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