Note: Data links between Heroku Redis and Heroku Postgres will be sunset on January 30, 2018
Getting Started
Add Heroku Postgres and Heroku Redis add-ons to your app (if not attached) - you will need a Production tier Postgres plan to use Data Links (Standard 0 or higher).
Make sure you have an up to date version of the Heroku Toolbelt.
Create the Data Link - replace appname with the name of your app
heroku pg:links create REDIS_URL DATABASE_URL --as redis -a appname
That's it! The Data Link has been created - behind the scenes your Postgres database has been configured with a foreign server and foreign table pointing to your Redis database using a Foreign Data Wrapper.
To setup additional foreign tables we need the details of the foreign server - retrieve the details of the foreign server with the following query:
\des
The response includes the name of the foreign server connection:
List of foreign servers
Name | Owner | Foreign-data wrapper
-----------------------+----------------+----------------------
<server_name> | <username> | redis_fdw
(1 row)
Hashes
Create a foreign table specifying the 'hash' tabletype - use the server name from the previous query to replace server_name:
sql
CREATE FOREIGN TABLE redis.hashtable (key text, value text[])
SERVER server_name
OPTIONS (database '0', tabletype 'hash');
Insert some data:
sql
INSERT INTO redis.hashtable (key, value)
VALUES ('my-hash-key','{key-1,value-1,key-2,value-2}');
The value column is actually defined as a 1-dimensional array - so you could also insert using the ARRAY keyword:
sql
INSERT INTO redis.hashtable (key, value)
VALUES ('my-hash-key',ARRAY['key-1','value-1','key-2','value-2']);
Query the table:
sql
SELECT * FROM redis.hashtable;
key | value
-------------+-------------------------------
my-hash-key | {key-1,value-1,key-2,value-2}
(1 row)
You could also query using some of the ARRAY functions and if you really wanted to get funky you could also use JSON functions - for example, to get all the keys within your hash:
sql
SELECT json_object_keys(json_object(value)) AS inner_hash_keys FROM redis.hashtable;
inner_hash_keys
------------------
key-1
key-2
(2 rows)
I'm not exactly sure of the use-case - but thought it worth sharing!
Next, check the data in Redis - list the KEYS:
> keys *
1) my-key
2) my-hash-key
Get the hash keys using HKEYS:
> hkeys my-hash-key
1) key-1
2) key-2
Retrieve the value for the hash keys using HMGET:
> hmget my-hash-key key-1 key-2
1) value-1
2) value-2
Singleton Key Hashes
Singleton Key Hashes can be defined by including the singleton_key OPTION in the create table statement
sql
CREATE FOREIGN TABLE redis.singletonhashtable (key text, value text)
SERVER server_name
OPTIONS (database '0', tabletype 'hash', singleton_key 'always-my-hash-key');
Insert multiple values - you only specify the key/value pairs; the hash key is set in the foreign table options
sql
INSERT INTO redis.singletonhashtable (key, value)
VALUES ('key-1','val-1'),('key-1','val-2');
Select the values:
sql
SELECT * FROM redis.singletonhashtable;
key | value
---------+-------
key-1 | val-1
key-2 | val-2
Next, check the data in Redis - list the KEYS:
> keys *
1) my-key
2) my-hash-key
3) always-my-hash-key
Get the hash keys using HKEYS:
> hkeys always-my-hash-key
1) key-1
2) key-2
Retrieve the value for the hash keys using HMGET:
> hmget always-my-hash-key key-1 key-2
1) value-1
2) value-2
Lists
Create a foreign table specifying the 'list' tabletype - use the server name to replace server_name - much like the hash the value is an ARRAY:
sql
CREATE FOREIGN TABLE redis.listtable (key text, value text[])
SERVER server_name
OPTIONS (database '0', tabletype 'list');
Insert some data:
sql
INSERT INTO redis.listtable (key, value)
VALUES ('my-list-key',ARRAY['1st','2nd','3rd','4th','5th']);
Select the values:
sql
SELECT * FROM redis.listtable;
key | value
-------------+-----------------------
my-list-key | {1st,2nd,3rd,4th,5th}
(1 row)
Append items to the list using Array concatenation:
sql
UPDATE redis.listtable SET value = value || ARRAY['6th'] WHERE key = 'my-list-key';
Next, check the data in Redis - list the KEYS:
> keys *
1) my-key
2) my-hash-key
3) always-my-hash-key
4) my-list-key
Get the items in the list using LRANGE:
> LRANGE my-list-key 0 -1
1) 1st
2) 2nd
3) 3rd
4) 4th
5) 5th
6) 6th
Singleton Key Lists
Singleton Key Lists can be defined by including the singleton_key OPTION in the create table statement
sql
CREATE FOREIGN TABLE redis.singletonlisttable (value text)
SERVER server_name
OPTIONS (database '0', tabletype 'list', singleton_key 'always-my-list-key');
Insert single or multiple values - you only specify the value pairs; the list key is set in the foreign table options:
sql
INSERT INTO redis.singletonlisttable (value)
VALUES ('alpha'),('beta');
Select the values:
sql
SELECT * FROM redis.singletonlisttable;
value
-------
alpha
beta
(2 rows)
Next, check the data in Redis - list the KEYS:
> keys *
1) my-key
2) my-hash-key
3) always-my-hash-key
4) my-list-key
5) always-my-list-key
Get the items in the list using LRANGE:
> LRANGE always-my-list-key 0 -1
1) alpha
2) beta
To add values to the list within Postgres, issue additional INSERT statements - this will add new items to the end of the list (RPUSH).
Singleton Key Lists do not support UPDATE operations in Postgres.
Sets
Working with Sets is similar to Lists - remember to specify the 'set' tabletype in the foreign table create command.
Singleton Key Sets
Much like the similarities working with Sets and Lists - Singleton Key Sets work like Singleton Key Lists. Remember to specify the 'set' tabletype and the singleton_key in the foreign table create command.
Redis Usage
Most of the content in this post is also relevant to using the Redis FDW in conjunction with a standard Postgres and Redis database - manual configuration of the FDW would be required.
The Redis FDW documentation lists the supported table types:
tabletype: can be 'hash', 'list', 'set' or 'zset' Default: none, meaning only look at scalar values.
There are some limitations on the commands available on the different table types - take a look at the Redis FDW documentation for more details.
As above, the default uses scalar values; therefore the default foreign table for a Data Link uses scalar values.