The Online Works of Chris Anderton

Writing to Redis from Postgres with Heroku Data Links

Please Note 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.

Rather than describe what Heroku Data Links are and how they work, I'll defer to the Dev Center article:

Heroku Data Links allows you to connect disparate data sources, such as Heroku Redis, to a Heroku Postgres database; this lets you use SQL semantics to read and write data, regardless of where the data lives.

The article currently describes how to read data from Redis via SQL in Postgres - but (at the time of writing) doesn't currently provide much (any) detail on how to write data to Redis via an SQL insert statement.

Keep in mind that writing to Redis from Postgres will add some overhead - but sometimes it's useful. Here's how you do it.. (hint, you can now insert by default without any additional work, but you can also tweak the foreign tables to use different Redis data types)

Add Heroku Postgres and Heroku Redis add-ons to your app (if they are 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 is established - the toolbelt configured your Postgres database with a foreign server and table pointing to your Redis database using a Foreign Data Wrapper.

Connect to your Postgres database - we'll use the Heroku toolbelt's built-in support for psql - again, replace appname with the name of your app

heroku pg:psql -a appname

Inspect the table definition using standard Postgres commands (nb. if you gave the Data Link a different name, tweak the command accordingly)

\d redis.redis

The foreign table has two columns - key and value - you can also see that this table is pointing to database '0' in Redis

       Foreign table "redis.redis"
 Column | Type | Modifiers | FDW Options 
 key    | text |           | 
 value  | text |           | 
Server: <server_name>
FDW Options: (database '0')

Insert data into Redis from Postgres (and check it worked!)

Inserting data is a standard SQL insert - run the insert command against the Postgres database (use psql as above)

sql INSERT INTO redis.redis (key, value) VALUES ('my-key','my-value');

That's all it takes - the SQL query 'inserts' the data into the foreign table; the Redis Foreign Data Wrapper manages the data to Redis.

Querying the table works as expected (query executed from psql)

sql SELECT * FROM redis.redis; key | value --------+---------- my-key | my-value (1 row)

Check the data in Redis using the Heroku Redis CLI (replace appname with the name of your app)

heroku redis:cli -a <appname>

List the keys in your Redis database

> keys *
1) my-key        

Get the value of the key

> get 'my-key'

The key and value have been successfully set from Postgres using the Data Link.