Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Postgres & Redis Sitting In a Tree

Postgres & Redis Sitting In a Tree

In today's world of polyglot persistence, it's likely that companies will be using multiple data stores for storing and working with data based on the use case. Typically a company will start with a relational database like Postgres and then add Redis for more high velocity use-cases. What if you could tie the two systems together to enable so much more?

Rimas Silkaitis

May 11, 2016
Tweet

More Decks by Rimas Silkaitis

Other Decks in Programming

Transcript

  1. Rimas Silkaitis
    Postgres & Redis
    Sitting in a Tree

    View full-size slide

  2. http://oldblog.antirez.com/post/take-advantage-of-redis-adding-it-to-your-stack.html

    View full-size slide

  3. What’s changed since then?

    View full-size slide

  4. redis_fdw
    https://github.com/pg-redis-fdw/redis_fdw

    View full-size slide

  5. redis_fdw
    Foreign Data Wrapper

    View full-size slide

  6. By using redis_fdw
    •Cache results without leaving Postgres
    •Cross reference data
    •Reduce complexity in app code
    •Maybe even replace PG functionality
    with that of Redis

    View full-size slide

  7. DEPLOY MANAGE SCALE

    View full-size slide

  8. $ git push heroku master
    Counting objects: 11, done.
    Delta compression using up to 8 threads.
    Compressing objects: 100% (10/10), done.
    Writing objects: 100% (11/11), 22.29 KiB | 0 bytes/s, done.
    Total 11 (delta 1), reused 0 (delta 0)
    remote: Compressing source files... done.
    remote: Building source:
    remote:
    remote: -----> Ruby app detected
    remote: -----> Compiling Ruby
    remote: -----> Using Ruby version: ruby-2.3.1

    View full-size slide

  9. Rimas Silkaitis
    Product

    View full-size slide

  10. Heroku Postgres
    Over 1 Million Active DBs

    View full-size slide

  11. Heroku Redis
    Over 100K Active Instances

    View full-size slide

  12. Heroku Kafka

    View full-size slide

  13. Configuring redis_fdw

    View full-size slide

  14. neovintage::DB=> CREATE EXTENSION redis_fdw;
    CREATE EXTENSION
    neovintage::DB=> CREATE SERVER redis_server
    neovintage::DB-> FOREIGN DATA WRAPPER redis_fdw
    neovintage::DB-> OPTIONS (
    neovintage::DB-> ADDRESS ‘127.0.0.1’,
    neovintage::DB-> PORT ‘6379’
    neovintage::DB-> );
    CREATE SERVER
    neovintage::DB=> CREATE USER MAPPING FOR PUBLIC
    neovintage::DB-> SERVER redis_server OPTIONS (password ‘pass’);
    CREATE USER MAPPING

    View full-size slide

  15. $ heroku pg:links create DATABASE_URL REDIS_URL —as redis_db -a sushi

    View full-size slide

  16. neovintage::DB=> CREATE FOREIGN TABLE redis_scalar (
    neovintage::DB-> key text,
    neovintage::DB-> value text
    neovintage::DB-> )
    neovintage::DB-> SERVER redis_server
    neovintage::DB-> OPTIONS (
    neovintage::DB-> database ‘0’
    neovintage::DB-> );
    CREATE FOREIGN TABLE

    View full-size slide

  17. redis> SET presentation awesome
    OK
    redis>
    neovintage::DB=> SELECT * from redis_scalar;
    key | value
    --------------+-------
    presentation | awesome
    (1 row)

    View full-size slide

  18. More Options for PG Tables
    • tabletype
    • tablekeyprefix
    • tablekeyset
    • singleton_key

    View full-size slide

  19. tabletype 'hash'

    View full-size slide

  20. neovintage::DB=> CREATE FOREIGN TABLE redis_hash (
    neovintage::DB-> key text,
    neovintage::DB-> value text[]
    neovintage::DB-> )
    neovintage::DB-> SERVER redis_server
    neovintage::DB-> OPTIONS ( database ‘0’, tabletype ‘hash’ );
    CREATE FOREIGN TABLE

    View full-size slide

  21. neovintage::DB=> SELECT * from redis_hash;
    key | value
    ---------+------------
    awesome | {today,10}
    (1 row)
    neovintage::DB=> SELECT key, json_object(value) from redis_hash;
    key | json_object
    ---------+---------------
    awesome | {“today”: “10”}
    (1 row)
    redis> HSET awesome today 10
    1

    View full-size slide

  22. Column Value Type redis_fdw return value
    text[] array of text
    text text as array

    View full-size slide

  23. tabletype 'list'

    View full-size slide

  24. neovintage::DB=> CREATE FOREIGN TABLE redis_list (
    neovintage::DB-> key text,
    neovintage::DB-> value text[]
    neovintage::DB-> )
    neovintage::DB-> SERVER redis_server
    neovintage::DB-> OPTIONS ( database ‘0’, tabletype ‘list’ );
    CREATE FOREIGN TABLE

    View full-size slide

  25. neovintage::DB=> SELECT * from redis_list;
    key | value
    ----------+------------
    mylist | {hello,world}
    yourlist | {awesome}
    (2 row)
    redis> RPUSH mylist “hello”
    1
    redis> RPUSH mylist “world”
    1
    redis> RPUSH yourlist “awesome”
    1

    View full-size slide

  26. tabletypes set and zset are
    similar to list

    View full-size slide

  27. neovintage::DB=> CREATE FOREIGN TABLE redis_set (
    neovintage::DB-> value text
    neovintage::DB-> )
    neovintage::DB-> SERVER redis_server
    neovintage::DB-> OPTIONS (
    neovintage::DB-> database ‘0’,
    neovintage::DB-> tabletype ‘set’,
    neovintage::DB-> tablekeyset ‘myset’
    neovintage::DB-> );
    CREATE FOREIGN TABLE

    View full-size slide

  28. neovintage::DB=> SELECT * from redis_set;
    value
    -------
    hello
    world
    (2 row)
    redis> SADD myset “hello”
    1
    redis> SADD myset “world”
    1

    View full-size slide

  29. http://oldblog.antirez.com/post/take-advantage-of-redis-adding-it-to-your-stack.html

    View full-size slide

  30. Counting Things

    View full-size slide

  31. redis> INCR user:
    redis> EXPIRE user: 60

    View full-size slide

  32. neovintage::DB=> CREATE FOREIGN TABLE redis_counts (
    neovintage::DB-> user_id text,
    neovintage::DB-> count bigint
    neovintage::DB-> )
    neovintage::DB-> SERVER redis_server
    neovintage::DB-> OPTIONS (
    neovintage::DB-> database ‘0’,
    neovintage::DB-> tablekeyprefix ‘user:’
    neovintage::DB-> );
    CREATE FOREIGN TABLE

    View full-size slide

  33. neovintage::DB=> SELECT * from redis_counts;
    user_id | count
    ----------+------------
    user:2 | 10
    user:3 | 200
    (2 row)
    GROSS

    View full-size slide

  34. neovintage::DB=> SELECT * from user_counts;
    user_id | count
    ----------+------------
    2 | 10
    3 | 200
    (2 row)
    neovintage::DB=> CREATE VIEW user_counts AS
    neovintage::DB-> SELECT split_part(user_id, ‘:’, 2) as user_id
    neovintage::DB-> , count
    neovintage::DB-> FROM redis_counts;

    View full-size slide

  35. neovintage::DB=> INSERT INTO user_count_snapshots
    neovintage::DB-> (created_at, user_id, count)
    neovintage::DB-> SELECT date_trunc(‘hour’, now())
    neovintage::DB-> , user_id
    neovintage::DB-> , count
    neovintage::DB-> FROM user_counts;

    View full-size slide

  36. Benefits
    • Cross reference data in Postgres with high velocity
    information
    • Issue one query to take snapshots of counts in Redis.
    Make data warehousing easier.

    View full-size slide

  37. Slow Queries

    View full-size slide

  38. SELECT *
    FROM foo
    WHERE ...
    ORDER BY rank DESC
    LIMIT 10

    View full-size slide

  39. psudeo-code
    FUNCTION get_top_commenters():
    list = redis.get(“top:comments”)
    time = redis.get(“top:comments:refresh_time”)
    IF (Time.now - time) > 90
    mutex do
    list = SQL_DB("SELECT ... ORDER BY rank LIMIT …”)
    redis.set(“top:comments”, list)
    redis.set(“top:comments:refresh_time”, Time.now)
    end
    END
    RETURN list
    END

    View full-size slide

  40. neovintage::DB=> \d users
    Table "public.users"
    Column | Type | Modifiers
    ----------+---------+----------------------------------------------------
    id | bigint | not null default nextval('users_id_seq'::regclass)
    name | text |
    comments | integer |
    neovintage::DB=> select * from users;
    id | name | comments
    -----+---------+----------
    1 | rimas | 10
    2 | chuck | 10000
    3 | lucy | 300

    View full-size slide

  41. neovintage::DB=> CREATE FOREIGN TABLE top_commenters (
    neovintage::DB-> cache_key text,
    neovintage::DB-> commenter text[]
    neovintage::DB-> )
    neovintage::DB-> SERVER redis_server
    neovintage::DB-> OPTIONS (
    neovintage::DB-> database ‘0’,
    neovintage::DB-> tabletype ‘list’
    neovintage::DB-> );
    CREATE FOREIGN TABLE

    View full-size slide

  42. neovintage::DB=> INSERT INTO top_commenters (cache_key, commenter)
    neovintage::DB-> SELECT ‘mylist’
    neovintage::DB-> , array_agg(name)
    neovintage::DB-> FROM users
    neovintage::DB-> GROUP BY 1;
    INSERT 0 1
    redis> LRANGE mylist 0 3
    1) chuck
    2) lucy
    3) rimas

    View full-size slide

  43. neovintage::DB=> UPDATE top_commenters
    set commenters = subquery.names
    from (select array_agg(name) as names
    from users) AS subquery
    ;

    View full-size slide

  44. What if we need to
    show score or count?
    zset

    View full-size slide

  45. neovintage::DB=> CREATE FOREIGN TABLE top_commenters (
    neovintage::DB-> value text,
    neovintage::DB-> score numeric
    neovintage::DB-> )
    neovintage::DB-> SERVER redis_server
    neovintage::DB-> OPTIONS (
    neovintage::DB-> database ‘0’,
    neovintage::DB-> tabletype ‘zset’,
    neovintage::DB-> singleton_key ‘mycache’
    neovintage::DB-> );
    CREATE FOREIGN TABLE

    View full-size slide

  46. neovintage::DB=> CREATE FOREIGN TABLE top_commenters (
    neovintage::DB-> value text,
    neovintage::DB-> score numeric
    neovintage::DB-> )
    neovintage::DB-> SERVER redis_server
    neovintage::DB-> OPTIONS (
    neovintage::DB-> database ‘0’,
    neovintage::DB-> tabletype ‘zset’,
    neovintage::DB-> singleton_key ‘mycache’
    neovintage::DB-> );
    CREATE FOREIGN TABLE

    View full-size slide

  47. What if we need to update results?

    View full-size slide

  48. neovintage::DB=> INSERT INTO top_commenters (cache_key, commenter)
    neovintage::DB-> SELECT ‘mylist’
    neovintage::DB-> , array_agg(name)
    neovintage::DB-> FROM users
    neovintage::DB-> GROUP BY 1;
    ERROR: key already exists: mylist

    View full-size slide

  49. neovintage::DB=> INSERT INTO redis_set (cache_key, commenters)
    (SELECT ‘mylist’
    , array_agg(name) as top_commenters
    FROM users
    GROUP BY 1) as subquery
    ON CONFLICT (cache_key)
    DO UPDATE SET value = subquery.top_commenters
    WHERE cache_key = ‘mylist’;
    ERROR: there is no unique or exclusion constraint matching the ON
    CONFLICT specification

    View full-size slide

  50. Challenges
    • You will get errors if keys already exist in Redis
    • sets, lists, zsets can be more of a challenge to update
    in place if you have many processes trying to grab the
    same key
    • Unique constraints on foreign tables in postgres
    aren’t a thing :-(

    View full-size slide

  51. Tips
    • Updates work well for scalar keys
    • Atomic updates to lists, sets and zsets will require
    some creativity
    • If you’re going to use zset, try the singleton_key when
    defining the foreign table in postgres.
    • Get rid of nasty mutex code by running a cron job on a
    periodic basis that executes update queries.

    View full-size slide

  52. Caveats
    •Postgres 9.3+
    •Redis 2.8+

    View full-size slide

  53. Is it possible to replace
    Postgis with Redis GEO ?

    View full-size slide

  54. ¯\_(ϑ)_/¯

    View full-size slide

  55. zset under the hood

    View full-size slide

  56. redis> GEOADD Sicily 13.361389 38.115556 "Palermo" 15.087269 37.502669
    “Catania"
    2
    redis> GEOHASH Sicily Palermo
    sqc8b49rny0
    redis> GEOHASH Sicily Catania
    sqdtr74hyu0

    View full-size slide

  57. neovintage::DB=> CREATE FOREIGN TABLE redis_geo (
    neovintage::DB-> city text,
    neovintage::DB-> geohash text
    neovintage::DB-> )
    neovintage::DB-> SERVER redis_server
    neovintage::DB-> OPTIONS (
    neovintage::DB-> database ‘0’,
    neovintage::DB-> tabletype ‘zset’,
    neovintage::DB-> singleton_key ‘Sicily’ );
    CREATE FOREIGN TABLE

    View full-size slide

  58. neovintage::DB=> SELECT * FROM redis_geohash;
    city | geohash
    ----------+------------
    Palermo | sqc8b49rny0
    Catania | sqdtr74hyu0

    View full-size slide

  59. •Still need to install Postgis
    (geohash functions)
    •Can’t use any of the GEO
    functions from redis

    View full-size slide

  60. Maybe someday

    View full-size slide

  61. Thank You!
    Rimas Silkaitis / neovintage.org / @neovintage

    View full-size slide