Database

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:

Redis TypeForeign Table Type (src_type)
Listlist
Setset
Hashhash
Sorted Setzset
Streamstream
Multiple Listmulti_list
Multiple Setmulti_set
Multiple Hashmulti_hash
Multiple Sorted Setmulti_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:


_10
create extension if not exists wrappers with schema extensions;

and then create the foreign data wrapper:


_10
create 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`
_10
insert into vault.secrets (name, secret)
_10
values (
_10
'redis_conn_url',
_10
'redis://username:password@127.0.0.1:6379/db'
_10
)
_10
returning 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:

The connection URL format is:


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

Creating Foreign Tables

The Redis Wrapper supports data reads from Redis.

IntegrationSelectInsertUpdateDeleteTruncate
Redis

For example:


_10
create 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_typeColumn nameColumn type
list, set, zsetelementtext
hashkeytext
valuetext
streamidtext
itemsjsonb
multi_*keytext
itemsjsonb

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 typeDescription
    listSingle list
    setSingle set
    hashSingle hash
    zsetSingle sorted set
    streamStream
    multi_listMultiple lists, specified by src_key pattern
    multi_setMultiple sets, specified by src_key pattern
    multi_hashMultiple hashes, specified by src_key pattern
    multi_zsetMultiple 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 Typesrc_key examples
    list, set, hash, zset, streammy_list, list:001, hash_foo, zset:1000 and etc.
    multi_list, multi_set, multi_hash, multi_zsetmy_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:


_18
127.0.0.1:6379> RPUSH list foo bar 42
_18
127.0.0.1:6379> SADD set foo bar 42
_18
127.0.0.1:6379> HSET hash foo bar baz qux
_18
127.0.0.1:6379> ZADD zset 30 foo 20 bar 10 baz
_18
127.0.0.1:6379> XADD stream * foo bar
_18
127.0.0.1:6379> XADD stream * aa 42 bb 43
_18
_18
127.0.0.1:6379> RPUSH list:100 foo bar
_18
127.0.0.1:6379> RPUSH list:200 baz
_18
_18
127.0.0.1:6379> SADD set:100 foo
_18
127.0.0.1:6379> SADD set:200 bar
_18
_18
127.0.0.1:6379> HSET hash:100 foo bar
_18
127.0.0.1:6379> HSET hash:200 baz qux
_18
_18
127.0.0.1:6379> ZADD zset:100 10 foo 20 bar
_18
127.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


    _10
    create foreign table redis_list (
    _10
    element text
    _10
    )
    _10
    server redis_server
    _10
    options (
    _10
    src_type 'list',
    _10
    src_key 'list'
    _10
    );
    _10
    _10
    select * from redis_list;

    Query result:


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

  • Set


    _10
    create foreign table redis_set (
    _10
    element text
    _10
    )
    _10
    server redis_server
    _10
    options (
    _10
    src_type 'set',
    _10
    src_key 'set'
    _10
    );
    _10
    _10
    select * from redis_set;

    Query result:


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

  • Hash


    _11
    create foreign table redis_hash (
    _11
    key text,
    _11
    value text
    _11
    )
    _11
    server redis_server
    _11
    options (
    _11
    src_type 'hash',
    _11
    src_key 'hash'
    _11
    );
    _11
    _11
    select * from redis_hash;

    Query result:


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

  • Sorted set


    _10
    create foreign table redis_zset (
    _10
    element text
    _10
    )
    _10
    server redis_server
    _10
    options (
    _10
    src_type 'zset',
    _10
    src_key 'zset'
    _10
    );
    _10
    _10
    select * from redis_zset;

    Query result:


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

  • Stream


    _11
    create foreign table redis_stream (
    _11
    id text,
    _11
    items jsonb
    _11
    )
    _11
    server redis_server
    _11
    options (
    _11
    src_type 'stream',
    _11
    src_key 'stream'
    _11
    );
    _11
    _11
    select * 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


    _11
    create 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
    _11
    select * from redis_multi_lists;

    Query result:


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

  • Set


    _11
    create 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
    _11
    select * from redis_multi_sets;

    Query result:


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

  • Hash


    _11
    create 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
    _11
    select * 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


    _11
    create 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
    _11
    select * from redis_multi_zsets;

    Query result:


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