Redis in my Postgres(craigkerstiens.com)
craigkerstiens.com
Redis in my Postgres
http://craigkerstiens.com/2012/10/18/connecting_to_redis_from_postgres/
6 comments
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".
> "both, togther"
That's always been the answer.
That's always been the answer.
At this rate postgres will be making the tea by the end of next week.
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.
It will create something almost, but not quite, entirely unlike tea.
Your optimism on gender equality in tech is heartwarming - although I think postgres will be off the hook for a few more years ;-)
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
https://github.com/dpage/redis_fdw/blob/master/README
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.
Is the performance on these queries good?
My guess unfortunately would be "probably not" - e.g. I can't think of a generic way to implement
I'd love to be contradicted, though :)
SELECT * from redis_db0 limit 5;
I'd love to be contradicted, though :)
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?
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?
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...
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.
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...
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.
> 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.
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.
Ah, I will be careful how I write it :) Thanks!
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.
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.
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
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
At a glance it seems the wrapper complicates things if you need performance and when using Redis performance is often the point.
It can certainly do that, though for most cases we have found that network throughput and disk I/O will get in the way first (for other FDWs.. we have never used the Redis one).
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.