Thread: PG 12: Partitioning across a FDW?
Is it even possible to use PG partitioning across a Foreign Server?
On Tue, 2020-03-24 at 17:50 -0500, Chris Morris wrote: > Is it even possible to use PG partitioning across a Foreign Server? I am not certain what you mean, but you can have foreign tables as partitions of a partitioned table. The partitions won't be processed in parallel though. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
> Is it even possible to use PG partitioning across a Foreign Server?
I am not certain what you mean, but you can have foreign tables as partitions
of a partitioned table. The partitions won't be processed in parallel though.
I have a large, growing table, that I'd like to start partitioning, but also would like "older" partitions to be stored in a separate database, connected via FDW. I haven't played around with partitioning at all yet, so clearly I'm not sure how to ask the question :)
The hope is to still have one "seamless" table users can query, but to spread the storage across different databases. I realize that may be asking for too much.
Greetings, * Laurenz Albe (laurenz.albe@cybertec.at) wrote: > On Tue, 2020-03-24 at 17:50 -0500, Chris Morris wrote: > > Is it even possible to use PG partitioning across a Foreign Server? > > I am not certain what you mean, but you can have foreign tables as partitions > of a partitioned table. The partitions won't be processed in parallel though. Not yet.. There is ongoing work to make that happen though. Also, accesses through the partitioned table to the foreign tables can happen in parallel, of course, just has to be through different connections to the main database. This makes it reasonable to consider using a partitioned table across foreign tables for queries that are pulling back a small set of records, ideally based on the partition key so that only the one foreign table that has the data you need is queried, but it's not so good for large analytical type of workloads where you want to run something across all of the partitions in parallel (and in parallel on each of the partitions, etc). Thanks, Stephen
Attachment
Not yet.. There is ongoing work to make that happen though.
Glad to hear it. :) Thx.
Chris,
Does it actually need to be a different server and database, or would it be possible to have another storage device added to your existing database and make use of tablespaces to accomplish pseudo-archive of older partitions? Just a thought.
Right now my dbs are hosted by Heroku, so I doubt I have any control over the dbs at that level.
Thanks for the idea though! :)
On Wed, Mar 25, 2020 at 12:04 PM Michael Lewis <mlewis@entrata.com> wrote:
Chris,Does it actually need to be a different server and database, or would it be possible to have another storage device added to your existing database and make use of tablespaces to accomplish pseudo-archive of older partitions? Just a thought.