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.)
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?
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).
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.
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.
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
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).
