Redis

Redis is an open-source in-memory storage, used as a distributed, in-memory key–value database, cache and message broker, with optional durability.

The Redis Wrapper allows you to read data from Redis within your Postgres database.

Supported Redis Data Types#

All Redis value will be stored as text or jsonb column in Postgres, below are the supported Redis data types:

See below for more descriptions for the Multiple * types and src_type foreign table option.

Preparation#

Before you get started, make sure the wrappers extension is installed on your database:

and then create the foreign data wrapper:

Secure your credentials (optional)#

By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.

Connecting to Redis#

We need to provide Postgres with the credentials to connect to Redis. We can do this using the create server command:

The connection URL format is:

Creating Foreign Tables#

The Redis Wrapper supports data reads from Redis.

For example:

The foreign table columns names and types must be fixed for each source type, as listed below:

See below for the full list of src_type and descriptions.

Foreign table options#

The full list of foreign table options are below:

• src_type - Foreign table source type in Redis, required.

  This can be one of below types,

  Source type	Description
  list	Single list
  set	Single set
  hash	Single hash
  zset	Single sorted set
  stream	Stream
  multi_list	Multiple lists, specified by src_key pattern
  multi_set	Multiple sets, specified by src_key pattern
  multi_hash	Multiple hashes, specified by src_key pattern
  multi_zset	Multiple sorted sets, specified by src_key pattern

• src_key - Source object key in Redis, required.

  This key can be a pattern for multi_* type of foreign table. For other types, this key must return exact one value. For example,

  Source Type	src_key examples
  list, set, hash, zset, stream	my_list, list:001, hash_foo, zset:1000 and etc.
  multi_list, multi_set, multi_hash, multi_zset	my_list:*, set:*, zset:* and etc.

Query Pushdown Support#

This FDW doesn't supports pushdown.

Examples#

Some examples on how to use Redis foreign tables.

Let's prepare some source data in Redis CLI first:

Basic example#

This example will create foreign tables inside your Postgres database and query their data:

• List

  create foreign table redis_list (
  

  element text
  

  )
  

  server redis_server
  

  options (
  

  src_type 'list',
  

  src_key 'list'
  

  );
  

  
  

  select * from redis_list;
  

  Query result:

  element
  

  ---------
  

  foo
  

  bar
  

  42
  

  (3 rows)
  

• Set

  create foreign table redis_set (
  

  element text
  

  )
  

  server redis_server
  

  options (
  

  src_type 'set',
  

  src_key 'set'
  

  );
  

  
  

  select * from redis_set;
  

  Query result:

  element
  

  ---------
  

  42
  

  foo
  

  bar
  

  (3 rows)
  

• Hash

  create foreign table redis_hash (
  

  key text,
  

  value text
  

  )
  

  server redis_server
  

  options (
  

  src_type 'hash',
  

  src_key 'hash'
  

  );
  

  
  

  select * from redis_hash;
  

  Query result:

  key | value
  

  -----+-------
  

  foo | bar
  

  baz | qux
  

  (2 rows)
  

• Sorted set

  create foreign table redis_zset (
  

  element text
  

  )
  

  server redis_server
  

  options (
  

  src_type 'zset',
  

  src_key 'zset'
  

  );
  

  
  

  select * from redis_zset;
  

  Query result:

  element
  

  ---------
  

  baz
  

  bar
  

  foo
  

  (3 rows)
  

• Stream

  create foreign table redis_stream (
  

  id text,
  

  items jsonb
  

  )
  

  server redis_server
  

  options (
  

  src_type 'stream',
  

  src_key 'stream'
  

  );
  

  
  

  select * from redis_stream;
  

  Query result:

  id | items
  

  -----------------+--------------------------
  

  1704343825989-0 | {"foo": "bar"}
  

  1704343829799-0 | {"aa": "42", "bb": "43"}
  

  (2 rows)
  

Query multiple objects example#

This example will create several foreign tables using pattern in key and query multiple objects from Redis:

• List

  create foreign table redis_multi_lists (
  

  key text,
  

  items jsonb
  

  )
  

  server redis_server
  

  options (
  

  src_type 'multi_list',
  

  src_key 'list:*'
  

  );
  

  
  

  select * from redis_multi_lists;
  

  Query result:

  key | items
  

  ----------+----------------
  

  list:100 | ["foo", "bar"]
  

  list:200 | ["baz"]
  

  (2 rows)
  

• Set

  create foreign table redis_multi_sets (
  

  key text,
  

  items jsonb
  

  )
  

  server redis_server
  

  options (
  

  src_type 'multi_set',
  

  src_key 'set:*'
  

  );
  

  
  

  select * from redis_multi_sets;
  

  Query result:

  key | items
  

  ---------+---------
  

  set:100 | ["foo"]
  

  set:200 | ["bar"]
  

  (2 rows)
  

• Hash

  create foreign table redis_multi_hashes (
  

  key text,
  

  items jsonb
  

  )
  

  server redis_server
  

  options (
  

  src_type 'multi_hash',
  

  src_key 'hash:*'
  

  );
  

  
  

  select * from redis_multi_hashes;
  

  Query result:

  key | items
  

  ----------+----------------
  

  hash:200 | {"baz": "qux"}
  

  hash:100 | {"foo": "bar"}
  

  (2 rows)
  

• Sorted set

  create foreign table redis_multi_zsets (
  

  key text,
  

  items jsonb
  

  )
  

  server redis_server
  

  options (
  

  src_type 'multi_zset',
  

  src_key 'zset:*'
  

  );
  

  
  

  select * from redis_multi_zsets;
  

  Query result:

  key | items
  

  ----------+----------------
  

  zset:200 | ["qux", "baz"]
  

  zset:100 | ["foo", "bar"]
  

  (2 rows)