Обсуждение: Local parent table and Foreign table(s) as partition(s), is it possible?

All;

I see the below options in the CREATE FOREIGN TABLE, however I assume these would both connect to a master/parent table on the foreign server. Is it possible to have a Foreign Table that acts as a partition of a local parent table, via either method (INHERITS or PARTITION OF)?

Thanks in advance...

INHERITS ( parent_table [, ... ] )

The optional INHERITS clause specifies a list of tables from which the new foreign table automatically inherits all columns. Parent tables can be plain tables or foreign tables. See the similar form of CREATE TABLE for more details.

PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }

This form can be used to create the foreign table as partition of the given parent table with specified partition bound values. See the similar form of CREATE TABLE for more details. Note that it is currently not allowed to create the foreign table as a partition of the parent table if there are UNIQUE indexes on the parent table. (See also ALTER TABLE ATTACH PARTITION.)

On Wed, 2022-09-14 at 11:41 -0600, Sbob wrote:
> I see the below options in the CREATE FOREIGN TABLE, however I assume these would both connect
> to a master/parent table on the foreign server. Is it possible to have a Foreign Table that
> acts as a partition of a local parent table, via either method (INHERITS or PARTITION OF)?

Yes, exactly: a foreign table can be a partition of a local partitioned table.
This can be used to implement sharding, if the foreign data wrapper supports the "parallel append"
node introduced in PostgreSQL v14.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On 9/14/22 12:01, Laurenz Albe wrote:
> On Wed, 2022-09-14 at 11:41 -0600, Sbob wrote:
>> I see the below options in the CREATE FOREIGN TABLE, however I assume these would both connect
>> to a master/parent table on the foreign server. Is it possible to have a Foreign Table that
>> acts as a partition of a local parent table, via either method (INHERITS or PARTITION OF)?
> Yes, exactly: a foreign table can be a partition of a local partitioned table.
> This can be used to implement sharding, if the foreign data wrapper supports the "parallel append"
> node introduced in PostgreSQL v14.
>
> Yours,
> Laurenz Albe


Nice!  so do I simply use the INHERITS clause or the PARTITION OF clause 
when I create the foreign table and reference the local parent table?

On Wed, 2022-09-14 at 12:17 -0600, Sbob wrote:
> 
> On 9/14/22 12:01, Laurenz Albe wrote:
> > On Wed, 2022-09-14 at 11:41 -0600, Sbob wrote:
> > > I see the below options in the CREATE FOREIGN TABLE, however I assume these would both connect
> > > to a master/parent table on the foreign server. Is it possible to have a Foreign Table that
> > > acts as a partition of a local parent table, via either method (INHERITS or PARTITION OF)?
> > Yes, exactly: a foreign table can be a partition of a local partitioned table.
> > This can be used to implement sharding, if the foreign data wrapper supports the "parallel append"
> > node introduced in PostgreSQL v14.
> 
> Nice!  so do I simply use the INHERITS clause or the PARTITION OF clause 
> when I create the foreign table and reference the local parent table?

Precisely.  Use declarative partitioning rather than inheritance.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com