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.

Restoring a logical backup of a database with a materialized view using a foreign table can fail. For this reason, either do not use foreign tables in materialized views or use them in databases with physical backups enabled.

Supported Redis Data Types

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

Redis Type	Foreign Table Type (src_type)
List	list
Set	set
Hash	hash
Sorted Set	zset
Stream	stream
Multiple List	multi_list
Multiple Set	multi_set
Multiple Hash	multi_hash
Multiple Sorted Set	multi_zset

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

Preparation

Before you can query Redis, you need to enable the Wrappers extension and store your credentials in Postgres.

Enable Wrappers

Make sure the wrappers extension is installed on your database:


_10create extension if not exists wrappers with schema extensions;

Enable the Redis Wrapper

Enable the redis_wrapper FDW:


_10create foreign data wrapper redis_wrapper
_10  handler redis_fdw_handler
_10  validator redis_fdw_validator;

Store 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.


_10-- Save your Redis connection URL in Vault and retrieve the `key_id`
_10insert into vault.secrets (name, secret)
_10values (
_10  'redis_conn_url',
_10  'redis://username:password@127.0.0.1:6379/db'
_10)
_10returning key_id;

Connecting to Redis

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


_10create server redis_server
_10  foreign data wrapper redis_wrapper
_10  options (
_10    conn_url_id '<key_ID>' -- The Key ID from above.
_10  );

Create a schema

We recommend creating a schema to hold all the foreign tables:


_10create schema if not exists redis;

Options

The following options are available when creating Redis foreign tables:

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.

Entities

List

This is an object representing a Redis List.

Ref: Redis docs

Operations

Object	Select	Insert	Update	Delete	Truncate
List	✅	❌	❌	❌	❌

Usage


_10create foreign table redis.list (
_10  element text
_10)
_10  server redis_server
_10  options (
_10    src_type 'list',
_10    src_key 'my_list'
_10  );

Notes

Elements are stored in insertion order
Query returns all elements in the list
No query pushdown support

Set

This is an object representing a Redis Set.

Ref: Redis docs

Operations

Object	Select	Insert	Update	Delete	Truncate
Set	✅	❌	❌	❌	❌

Usage


_10create foreign table redis.set (
_10  element text
_10)
_10  server redis_server
_10  options (
_10    src_type 'set',
_10    src_key 'set'
_10  );

Notes

Elements are unique within the set
No guaranteed order of elements
No query pushdown support

Hash

This is an object representing a Redis Hash.

Ref: Redis docs

Operations

Object	Select	Insert	Update	Delete	Truncate
Hash	✅	❌	❌	❌	❌

Usage


_10create foreign table redis.hash (
_10  key text,
_10  value text
_10)
_10  server redis_server
_10  options (
_10    src_type 'hash',
_10    src_key 'hash'
_10  );

Notes

Key-value pairs within the hash
No query pushdown support
Both key and value are returned as text

Sorted Set

This is an object representing a Redis Sorted Set.

Ref: Redis docs

Operations

Object	Select	Insert	Update	Delete	Truncate
Sorted Set	✅	❌	❌	❌	❌

Usage


_10create foreign table redis.zset (
_10  element text
_10)
_10  server redis_server
_10  options (
_10    src_type 'zset',
_10    src_key 'zset'
_10  );

Notes

Elements are ordered by their score
Elements are unique within the set
Score information is not exposed in the foreign table

Stream

This is an object representing a Redis Stream.

Ref: Redis docs

Operations

Object	Select	Insert	Update	Delete	Truncate
Stream	✅	❌	❌	❌	❌

Usage


_10create foreign table redis.stream (
_10  id text,
_10  items jsonb
_10)
_10  server redis_server
_10  options (
_10    src_type 'stream',
_10    src_key 'stream'
_10  );

Notes

Stream entries have unique IDs
Items are stored in JSONB format
Entries are ordered by their IDs

Multiple Objects

Redis wrapper supports querying multiple objects of the same type using pattern matching.

Operations

Object Type	Select	Insert	Update	Delete	Truncate
Multiple List	✅	❌	❌	❌	❌
Multiple Set	✅	❌	❌	❌	❌
Multiple Hash	✅	❌	❌	❌	❌
Multiple ZSet	✅	❌	❌	❌	❌

Usage


_10create foreign table redis.multi_lists (
_10  key text,
_10  items jsonb
_10)
_10  server redis_server
_10  options (
_10    src_type 'multi_list',
_10    src_key 'list:*'
_10  );

Notes

Use pattern matching in src_key option
Results include object key and items in JSONB format
Items format varies by object type

Query Pushdown Support

This FDW doesn't support pushdown.

Examples

Some examples on how to use Redis foreign tables.

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


_18127.0.0.1:6379> RPUSH list foo bar 42
_18127.0.0.1:6379> SADD set foo bar 42
_18127.0.0.1:6379> HSET hash foo bar baz qux
_18127.0.0.1:6379> ZADD zset 30 foo 20 bar 10 baz
_18127.0.0.1:6379> XADD stream * foo bar
_18127.0.0.1:6379> XADD stream * aa 42 bb 43
_18
_18127.0.0.1:6379> RPUSH list:100 foo bar
_18127.0.0.1:6379> RPUSH list:200 baz
_18
_18127.0.0.1:6379> SADD set:100 foo
_18127.0.0.1:6379> SADD set:200 bar
_18
_18127.0.0.1:6379> HSET hash:100 foo bar
_18127.0.0.1:6379> HSET hash:200 baz qux
_18
_18127.0.0.1:6379> ZADD zset:100 10 foo 20 bar
_18127.0.0.1:6379> ZADD zset:200 40 baz 30 qux

Basic example

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

List

_10create foreign table redis.list ( _10 element text _10) _10server redis_server _10options ( _10 src_type 'list', _10 src_key 'list' _10); _10 _10select * from redis.list;

Query result:

_10 element _10--------- _10 foo _10 bar _10 42 _10(3 rows)
Set

_10create foreign table redis.set ( _10 element text _10) _10server redis_server _10options ( _10 src_type 'set', _10 src_key 'set' _10); _10 _10select * from redis.set;

Query result:

_10 element _10--------- _10 42 _10 foo _10 bar _10(3 rows)
Hash

_11create foreign table redis.hash ( _11 key text, _11 value text _11) _11server redis_server _11options ( _11 src_type 'hash', _11 src_key 'hash' _11); _11 _11select * from redis.hash;

Query result:

_10 key | value _10-----+------- _10 foo | bar _10 baz | qux _10(2 rows)
Sorted set

_10create foreign table redis.zset ( _10 element text _10) _10server redis_server _10options ( _10 src_type 'zset', _10 src_key 'zset' _10); _10 _10select * from redis.zset;

Query result:

_10 element _10--------- _10 baz _10 bar _10 foo _10(3 rows)
Stream

_11create foreign table redis.stream ( _11 id text, _11 items jsonb _11) _11server redis_server _11options ( _11 src_type 'stream', _11 src_key 'stream' _11); _11 _11select * from redis.stream;

Query result:

_10 id | items _10-----------------+-------------------------- _10 1704343825989-0 | {"foo": "bar"} _10 1704343829799-0 | {"aa": "42", "bb": "43"} _10(2 rows)

Query multiple objects example

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

List

_11create foreign table redis.multi_lists ( _11 key text, _11 items jsonb _11) _11 server redis_server _11 options ( _11 src_type 'multi_list', _11 src_key 'list:*' _11 ); _11 _11select * from redis.multi_lists;

Query result:

_10 key | items _10----------+---------------- _10 list:100 | ["foo", "bar"] _10 list:200 | ["baz"] _10(2 rows)
Set

_11create foreign table redis.multi_sets ( _11 key text, _11 items jsonb _11) _11 server redis_server _11 options ( _11 src_type 'multi_set', _11 src_key 'set:*' _11 ); _11 _11select * from redis.multi_sets;

Query result:

_10 key | items _10---------+--------- _10 set:100 | ["foo"] _10 set:200 | ["bar"] _10(2 rows)
Hash

_11create foreign table redis.multi_hashes ( _11 key text, _11 items jsonb _11) _11 server redis_server _11 options ( _11 src_type 'multi_hash', _11 src_key 'hash:*' _11 ); _11 _11select * from redis.multi_hashes;

Query result:

_10 key | items _10----------+---------------- _10 hash:200 | {"baz": "qux"} _10 hash:100 | {"foo": "bar"} _10(2 rows)
Sorted set

_11create foreign table redis.multi_zsets ( _11 key text, _11 items jsonb _11) _11 server redis_server _11 options ( _11 src_type 'multi_zset', _11 src_key 'zset:*' _11 ); _11 _11select * from redis.multi_zsets;

Query result:

_10 key | items _10----------+---------------- _10 zset:200 | ["qux", "baz"] _10 zset:100 | ["foo", "bar"] _10(2 rows)

Redis

Supported Redis Data Types#

Preparation#

Enable Wrappers#

Enable the Redis Wrapper#

Store your credentials (optional)#

Connecting to Redis#

Create a schema#

Options#

Entities#

List#

Operations#

Usage#

Notes#

Set#

Operations#

Usage#

Notes#

Hash#

Operations#

Usage#

Notes#

Sorted Set#

Operations#

Usage#

Notes#

Stream#

Operations#

Usage#

Notes#

Multiple Objects#

Operations#

Usage#

Notes#

Query Pushdown Support#

Examples#

Basic example#

Query multiple objects example#

Supported Redis Data Types

Preparation

Enable Wrappers

Enable the Redis Wrapper

Store your credentials (optional)

Connecting to Redis

Create a schema

Options

Entities

List

Operations

Usage

Notes

Set

Operations

Usage

Notes

Hash

Operations

Usage

Notes

Sorted Set

Operations

Usage

Notes

Stream

Operations

Usage

Notes

Multiple Objects

Operations

Usage

Notes

Query Pushdown Support

Examples

Basic example

Query multiple objects example