PG 12: Partitioning across a FDW?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

PG 12: Partitioning across a FDW?

Chris Morris
Is it even possible to use PG partitioning across a Foreign Server?
Reply | Threaded
Open this post in threaded view
|

Re: PG 12: Partitioning across a FDW?

Laurenz Albe
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



Reply | Threaded
Open this post in threaded view
|

Re: PG 12: Partitioning across a FDW?

Chris Morris
> 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.

Reply | Threaded
Open this post in threaded view
|

Re: PG 12: Partitioning across a FDW?

Stephen Frost
In reply to this post by Laurenz Albe
Greetings,

* Laurenz Albe ([hidden email]) 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: PG 12: Partitioning across a FDW?

Chris Morris
Not yet..  There is ongoing work to make that happen though.

Glad to hear it. :) Thx. 
Reply | Threaded
Open this post in threaded view
|

Re: PG 12: Partitioning across a FDW?

Michael Lewis
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.
Reply | Threaded
Open this post in threaded view
|

Re: PG 12: Partitioning across a FDW?

Chris Morris
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 <[hidden email]> 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.