RE: Support for VACUUMing Foreign Tables

From: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
> 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.

I thought the FDW mechanism was initially, and probably still is, designed to access data on other data sources that are operated independently, mostly for data integration. Are you thinking of shared-nothing clustering that consist of tightly coupled servers, because you're referring to a coordinator server? (Is EDB (re-)starting the sharding scale-out?)

> 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

MySQL's OPTIMIZE TABLE looks like VACUUM FULL, not plain VACUUM, although I'm not completely sure.

How would the various options map to the FDW interface, such as FREEZE, VERBOSE, vacuum_truncate, index_cleanup? Also, how would the following GUC settings propagate to the foreign server?

SET vacuum_freeze_table_age = 0;
SET vacuum_freeze_min_age = 0;
VACUUM mytable;

I think people who want to run manual VACUUM will want to control VACUUM behavior. But I'm afraid VACUUM is too specific to Postgres to be not very good to be incorporated into the FDW interface.

What's our stance toward the FDW interface? I've thought 1 so far.

1) We carefully choose FDW routines so that many other data sources can provide implementations for. We want to allow access to various data sources through the frame of Postgres.

2) We don't care other data sources. We are happy if multiple Postgres instances can interoperate with each other. Other data source providers can choose to implement suitable routines when they can fit their implementations into the Postgres world.

Regards
Takayuki Tsunakawa