
PostgreSQL 9.3 beta: Federated databases and more - sickpig
http://lwn.net/SubscriberLink/550418/0e518fa2972b21c5/
======
dopamean
I am pretty excited to see this. I was just discussing with my partner a
situation where we have a lot of data in separate DBs, and would like to keep
it that way, but are unable to JOIN across the DBs. We also are considering
moving a geocoder DB to another server but were concerned about the
implications of that when it comes to accessing that data at the same time as
other.

------
voidlogic
Nit, geography lesson needed:

>>Of course, if you're in the southern hemisphere, it's not spring for you.
_Nor if you live in the north Midwest of the US or central Canada._ Sorry
about that; we just track the weather with PostgreSQL, we don't control it.

I live in the north Midwest (Wisconsin) and it is currently 72F (should hit 80
today) and sunny, with birds chirping outside- I'm pretty sure it is spring-
which started Wednesday, March 20 regardless of it being cold as hell here at
the time.

I think some native Californians have trouble with the idea it can be under
-15F in February and over 100F in August in some places.

~~~
mitchty
My birthday is in May, it snowed on it this year (Twin Cities Minnesota). A
little over a week later after a 40F(4C) day, it was over 90F(32C) the next
day.

We have weird weather but May is definitely spring, I think the coastal people
have a weird perception of what the midwest/north is actually like. Once May
starts we're basically a few weeks from god awful summer heat like anywhere
else.

~~~
voidlogic
>>We have weird weather but May is definitely spring

Yeah, it snowed here for a moment on Saturday, but Tuesday it hit 89F. :)

>>Once May starts we're basically a few weeks from god awful summer heat like
anywhere else.

With exceptions of course, like last year where the snow was gone by mid March
and I went camping on St. Patties day- in shorts and a T-shirt... :) and was
bit my mosquitoes :(

------
gklitt
Another feature I'm really looking forward to in 9.3 is native operators to
query inside stored JSON documents. It works well in the dev build and will be
a great addition to the NoSQL functionality in Postgres.

------
pjungwir
Can anyone give a quick explanation of what FDW offers beyond the existing
dblink functionality? I haven't used either, but it seems like it'd be helpful
to many to spell out the difference.

~~~
jeffdavis
There are a lot of conceptual differences, but I'll give a couple very
important examples:

* With FDWs, you can push down predicates to the remote end. So, if you do: "SELECT * FROM myRemoteTable WHERE id = 2345", then it can just use the index on the remote side rather than pulling all the data to the local side and filtering.

* You can (as of 9.3) insert, update, and delete on the local side and it will pass through to the remote side.

------
pcrjk
...and still no REPLACE, INSERT IGNORE or MERGE statements.

~~~
ajtaylor
From what I've read, at least part of the problem is that the SQL standards
are very murky and/or contradictory on the definiton of UPSERT-like
functionality. And since Pg likes to be standards compliant they have focused
on other areas of functionality.

------
mason55
We've been using the FDW functionality quite heavily and it works great. Joins
and "transactions" between Postgres & MongoDB have been an amazing feature. I
can't wait for 9.3 for write functionality, although I'm not looking forward
to another database upgrade.

------
jpitz
Now, just give us a way to apply exclusion constraints across federated
partitions.

~~~
jeffdavis
Do you have an idea how that might be done?

~~~
jpitz
Wow, sorry. I misspoke.

I meant to say constraint exclusion. Exclusion constraints would be pretty
hard to implement in a distributed system.

That said - constraint exclusion wouldn't be all that rough, would it? The
eventual goal I am thinking of is more effective query plans for UNION views
over a bunch of federated partitions.

~~~
jeffdavis
"Exclusion constraints would be pretty hard to implement in a distributed
system."

It would be cool, though ;-)

"constraint exclusion wouldn't be all that rough, would it"

There are two interpretations of that:

* Passing a predicate down to the remote side so that the remote server can use it to exclude partitions. I believe this already works.

* Using CHECK constraints on the local side so that foreign tables can be excluded on the local side. This is a little trickier because it's hard to know that there are really no tuples on the remote side that violate the constraint. It's debatable how important that is, but it does need to be handled sanely somehow if the situation arises.

~~~
jpitz
I intended the second one. I assumed it would be necessary for writable
federated views.

------
crypto5
Sadly, no cassandra support yet..

