Wednesday, May 15, 2013

PostgreSQL tables for single Redis objects

After playing a bit with the Redis FDW and the Redis command extension, Josh Berkus was slightly dissatisfied. He was looking for a way to map the possibly huge set of values in a single Redis object to a set of rows on a PostgresSQL table, and the Redis FDW currently maps each object to a single row - as an array if it's appropriate. Of course, we could call unnest () on the array, but it seems roundabout to construct an array only to have to unpack it immediately. These aren't terribly cheap operations.

I've been thinking a bit about his complaint, and I think this will be doable. What we'll need is a new table option that specifies the key and designates the table as one sourced from a single key rather than a range of keys. Say we call this option singleton_key. Then we might do something like:
CREATE FOREIGN TABLE hugeset (value text)
SERVER localredis
OPTIONS (tabletype 'list', singleton_key 'myhugelist');
This option would be incompatible with the tablekeyset and tablekeyprefix options. If given, the key won't be looked up at all. We would simply use the given key and return the corresponding list of values. That would make selecting from such a table faster - possibly lots faster. For scalars, sets and lists, the table would have one column. In the case of a scalar there would only be one row. For zsets, it would have two columns - one for the value and one for the score. Finally, for hashes it would have two, one for the property and one for the value.

This looks like a useful possible enhancement.


  1. Might this become a path towards exposing Redis data to systems which only speak RDBMS ODBC or JDBC?

    Tableau is my example, although noting that Amazon Redshift uses the Postgres client, it seems the "Postgres protocol" could become useful for accessing both local and foreign data.

    1. well, I suppose you could use Postgres as sort of proxy to enable your JDBC or ODBC client to talk to Redis. But it doesn't seem likely to be very efficient. You'd probably be better off uing the hiredis client library directly, or for the java case implementing the Redis wire protocol in pure java.

    2. In my example, where I want to access Redis data from Tableau, I would need to implement an ODBC compatible layer. JDBC is not an option.

      Its also a case where the bulk of my historical data is in Postgres, and temporal or intra-day data would be in Redis. If the experiment proves out, the Redis FDW becomes the common bridge for both live visualization and ETL.

      The lack of efficiency in the extra DB engine layer may well better than my attempts at writing a new data access client ;)