PostgreSQL 9.3 beta: Federated databases and more(lwn.net)
lwn.net
PostgreSQL 9.3 beta: Federated databases and more
http://lwn.net/SubscriberLink/550418/0e518fa2972b21c5/
9 comments
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.
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.
>>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.
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.
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.
>>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 :(
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 :(
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.
[deleted]
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.
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.
* 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.
DBLink connects to remote PostgreSQL databases only.
FDW extends that idea to arbitrary data stores. Wrappers exist for Oracle, MySQL, Redis, Amazon S3, text files, etc.
FDW extends that idea to arbitrary data stores. Wrappers exist for Oracle, MySQL, Redis, Amazon S3, text files, etc.
...and still no REPLACE, INSERT IGNORE or MERGE statements.
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.
Considering the rising popularity of Postgres, I'd love to see someone set up a kind of Kickstarter mechanism so the user community can fund large pieces of development work, eg partitioning , extending the work on DB checksums and so on as well as fill in any holes in the feature set like the ones you mention.
MERGE or upsert has some reasonable chance of making the next release, though of course its never guaranteed until its in there.
Right, but there are good work-arounds via stored proces and triggers. I'm using this on a couple of projects and it works perfectly.
Here is a discussion and answer in Stack overflow: http://stackoverflow.com/a/1109198/1646663
Here is a discussion and answer in Stack overflow: http://stackoverflow.com/a/1109198/1646663
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.
Now, just give us a way to apply exclusion constraints across federated partitions.
Do you have an idea how that might be done?
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.
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.
"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.
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.
I intended the second one. I assumed it would be necessary for writable federated views.
[deleted]
Sadly, no cassandra support yet..