Re: PG 12: Partitioning across a FDW?

2020-03-25 Thread 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  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.
>
>>

Re: PG 12: Partitioning across a FDW?

2020-03-25 Thread 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.

>

Re: PG 12: Partitioning across a FDW?

2020-03-25 Thread Chris Morris 
>
> Not yet..  There is ongoing work to make that happen though.


Glad to hear it. :) Thx.

Re: PG 12: Partitioning across a FDW?

2020-03-25 Thread Stephen Frost 
Greetings,

* Laurenz Albe (laurenz.a...@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


signature.asc
Description: PGP signature

Re: PG 12: Partitioning across a FDW?

2020-03-25 Thread 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.

Re: PG 12: Partitioning across a FDW?

2020-03-25 Thread 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