Redis in my Postgres

http://craigkerstiens.com/2012/10/18/connecting_to_redis_from_postgres/

by jorde • 14 years ago

98 22 14 years ago

phpnode2• 14 years ago

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".

JonnieCache2• 14 years ago

> "both, togther"

That's always been the answer.

phpnode2• 14 years ago

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

wyclif• 14 years ago

Yo dawg, I heard you like the Web, so I put Redis in your Postgres.

rabidsnail• 14 years ago

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.

Detrus2• 14 years ago

Is the performance on these queries good?

samstokes• 14 years ago

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• 14 years ago

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?

bbromhead2• 14 years ago

Some of the postgres fdws dont pass conditions to the foreign data source because the mapping is sometimes "too hard". So the fdw will do something like select * from table, stream the results and postgres will do the rest of the heavy lifting for you.

Though for the fdws that talk to other SQL based databases, they will pass most of the conditions on to the foreign data source as it is "usually" all just sql.

So the answer is... it depends! :P

edited: I forgot how to write sentences good and stuff

Detrus2• 14 years ago

At a glance it seems the wrapper complicates things if you need performance and when using Redis performance is often the point.

fdr• 14 years ago

Although there are some details about what kind of pushdown are supported by the FDWs (for example: to do a WHERE key=x, a non-toy Redis FDW should push the equality predicate to redis), it's no faster nor slower than calling a C function a bunch of times.

Because SQL execution in Postgres (and most other systems) has an advantage of being functional, most executors can save a lot of time avoiding garbage collection and reference management if one is to compare them to, say, Ruby. It's probably going to be faster than bouncing to redis in your application and then to Postgres, for reasons of network overhead.

So, all in all, "it depends", but it should not be slow for a good-quality FDW that pushes down operators well.

JonnieCache2• 14 years ago

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

pjscott• 14 years ago

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• 14 years ago

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

smegel• 14 years ago

Your optimism on gender equality in tech is heartwarming - although I think postgres will be off the hook for a few more years ;-)

CoffeeDregs• 14 years ago

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.

spullara• 14 years ago

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

Redis in my Postgres

Related Stories