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 get started, make sure the wrappers extension is installed on your database:


_10create extension if not exists wrappers with schema extensions;

and then create the foreign data wrapper:


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

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.


_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  );

The connection URL format is:


_10redis://[<username>][:<password>@]<hostname>[:port][/<db>]

The connection URL also supports TLS:


_10rediss://[<username>][:<password>@]<hostname>[:port][/<db>]

To enable insecure mode, append #insecure at the end of the URL:


_10rediss://[<username>][:<password>@]<hostname>[:port]/[<db>]#insecure

Client certificate and custom root certificates are not supported when using TLS.

Creating Foreign Tables

The Redis Wrapper supports data reads from Redis.

Integration	Select	Insert	Update	Delete	Truncate
Redis	✅	❌	❌	❌	❌

For example:


_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  );

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

src_type	Column name	Column type
list, set, zset	`element`	text
hash	`key`	text
	`value`	text
stream	`id`	text
	`items`	jsonb
multi_*	`key`	text
	`items`	jsonb

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.

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:


_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#

Secure your credentials (optional)#

Connecting to Redis#

Creating Foreign Tables#

Foreign table options#

Query Pushdown Support#

Examples#

Basic example#

Query multiple objects example#