
Redis in my Postgres - jorde
http://craigkerstiens.com/2012/10/18/connecting_to_redis_from_postgres/
======
CoffeeDregs
Holy awesome! I really like postgres and, with FDW, a question comes to mind.

Can I basically put up a PG instance and have that manage/proxy to all of my
backend stores? It would be nice to be able to just use my one DB library to
query Riak, Redis and Postgresql [my usual datastore stack]. I didn't see
anything in here about doing CUD operations on the FDW stores, but the benefit
of having a single consistent interface for READs might be large enough to
adopt this.

------
phpnode
very cool, anyone using these foreign data wrappers in production? along with
the json support this is the kind of thing that will push me over the edge and
make me switch from mysql to postgres. It looks like the answer to the nosql
vs sql debate might be "both, togther".

~~~
JonnieCache
_> "both, togther"_

That's always been the answer.

~~~
phpnode
yes, but having separate systems that cannot interact is problematic. The
ability to join "tables" from different systems is a game changer.

~~~
jaytaylor
Game changer.

------
JonnieCache
At this rate postgres will be making the tea by the end of next week.

~~~
pjscott
Coffee, maybe; it shouldn't be too hard to get Hyper Text Coffee Pot Control
Protocol working, as specified in RFC 2324. Sadly, HTCPCP doesn't support tea,
and trying to use it for this purpose may result in HTTP 418 "I'm a teapot"
errors.

~~~
jmcqk6
It will create something almost, but not quite, entirely unlike tea.

------
spullara
The FDW wrapper that they have is really not worthy of Redis. It only supports
K/V and none of the structured types. Might as well be talking to memcached.
The README is useful in this case:

<https://github.com/dpage/redis_fdw/blob/master/README>

------
rabidsnail
You can even write your own foreign data wrappers in python:
<http://multicorn.org/implementing-an-fdw/#id3> Their "Hello, world" example
is less than 15 lines of code.

------
Detrus
Is the performance on these queries good?

~~~
samstokes
My guess unfortunately would be "probably not" - e.g. I can't think of a
generic way to implement

    
    
        SELECT * from redis_db0 limit 5;
    

other than running the Redis command "KEYS *" - which does a linear scan of
the entire Redis keyspace, locking Redis while it does so, and returning every
single key in a potentially large streaming response - and truncating to the
first 5. (That's fine if you have a few hundred keys, probably not if you have
ten million.)

I'd love to be contradicted, though :)

~~~
naww
So you would need to write your own Foreign Data Wrapper and implement some
mechanic to tell it if the SQL code is meant for first or the second
interpreter?

Like: SELECT * FROM redis_db0 USING_REDIS limit 5; And Postgres would just
pass that"USING_REDIS limit 5" through to Redis? Then Postgres would pass that
table or array? back to caller.

Wait, wouldn't this also need modification for DB engine itself?

~~~
malkia
Today was my first real usage of PostGres (moving one of our assetdb from
sqlite to it, and although the update of the db is 5 time slower - well I
haven't really done well there, the query later (which is more important) is
from 1.5sec to 300msec on one recent example - about 100,000 assets for a
video game).

I've also looked briefly about fdw, as a coworker of mine is trying out
mongodb for our level editor... Looked at all implementation and read the
every fdw code there (except oracle - as it seems most complex, but for good I
guess - it checks the pgsql's AST (is that how it's called?) more deeply).

As for redis, maybe this line is relevant:

[https://github.com/dpage/redis_fdw/blob/master/redis_fdw.c#L...](https://github.com/dpage/redis_fdw/blob/master/redis_fdw.c#L562)

yes, for certain cases (I guess "select _") it does "keys_ ". I'm also trying
out redis for our distributed caching in the studio.

So PostGres is my new favourite thing :) - the fdw looks easy to start, but
probably hard to master it.

~~~
deafbybeheading
> Today was my first real usage of PostGres

Then let us welcome you with some pedantry! Postgres or PostgreSQL, but never
PostGres or--FSM forbid--PostGreSQL.

More seriously,

> fdw looks easy to start, but probably hard to master it.

Yep, like a number of Postgres features, such as user-defined types and user-
defined aggregates. It's a great system, and not nearly as stuffy or hard to
use as you may have heard.

~~~
malkia
Ah, I will be careful how I write it :) Thanks!

