PG 12: Partitioning across a FDW?

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

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: