pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLocal parent table and Foreign table(s) as partition(s), is it possible?
4+ messages / 2 participants
[nested] [flat]
* Local parent table and Foreign table(s) as partition(s), is it possible?
@ 2022-09-14 17:41 Sbob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 4+ messages in thread
From: Sbob @ 2022-09-14 17:41 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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|
<https://www.postgresql.org/docs/current/sql-createtable.html; 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|
<https://www.postgresql.org/docs/current/sql-createtable.html; 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|
<https://www.postgresql.org/docs/current/sql-altertable.html;.)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Local parent table and Foreign table(s) as partition(s), is it possible?
@ 2022-09-14 18:01 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Sbob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2022-09-14 18:01 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Local parent table and Foreign table(s) as partition(s), is it possible?
@ 2022-09-14 18:17 Sbob <sbob@quadratum-braccas.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Sbob @ 2022-09-14 18:17 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Local parent table and Foreign table(s) as partition(s), is it possible?
@ 2022-09-15 08:18 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Sbob <sbob@quadratum-braccas.com>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2022-09-15 08:18 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-09-15 08:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-09-14 17:41 Local parent table and Foreign table(s) as partition(s), is it possible? Sbob <sbob@quadratum-braccas.com>
2022-09-14 18:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-09-14 18:17 ` Sbob <sbob@quadratum-braccas.com>
2022-09-15 08:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox