So I came up with a simple idea of mapping a foreign table to a Redis hash. This actually isn't too hard to implement, and I got it working more or less yesterday:
So far so good. You can treat a Redis hash as a little table and select from it. And we could perform similar operations for the other Redis data structures (lists, sets and zsets). But I'm wondering if this is going to fit well with actual Redis practice. Let's say we're dealing with web session objects. In PostgreSQL you would probably have a table for these with a session id and an hstore or maybe a piece of json (especially after my json extractor functions get committed :-) ). But in Redis I think you're at least as likely to have one hash per session object rather than a hash of session objects, and Redis data structures don't nest, so there's no hash of hashes. So if you have thousands or millions of tiny session object hashes in Redis, with names like "web.session.a9f3c1763d" where the last part of the name is some fairly opaque session name, do you really want to be having to map each one of those to its own foreign table in order to be able to read the values nicely? I don't think so.andrew=# \! echo type foo | redis-cli hash andrew=# \d xxx Foreign table "public.xxx" Column | Type | Modifiers | FDW Options --------+------+-----------+------------- key | text | | value | text | | Server: localredis FDW Options: (hashkey 'foo') andrew=# select * from xxx; key | value ---------+------- bar | baz blurflk | asdf qqq | ttt (3 rows)
Following this thought I came up with the idea of a related FDW which would give you access to the whole of the hashspace in Redis. The tables would have three fields, not two, being, say, (hashname, key value). We could even impose a restriction on queries that would require you to specify a hashname, although that's possibly a bit ugly, and maybe we should just warn you against not supplying a hashkey query parameter.
So, which of these patterns is likely to be most useful? They are not actually mutually exclusive, and it's perfectly possible to implement both. But I would like to know what seems most useful to Redis users generally, especially if they are also using PostgreSQL.
Another possibility would be not to use the Foreign Data Wrapper interface at all, and just provide a set returning function which would take some connection parameters and a hash name and return the key value pairs. This too would be reasonably easy to do, as I already have (and will soon publish) a framework for generalised Redis access. On the other hand, one significant advantage of using Foreign Data Wrappers is that with FDWs handling pushed down quals ("where" conditions in effect) is rather easier to do, from what I can see.