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?

Transcript

1. Rimas Silkaitis
   Postgres & Redis
   Sitting in a Tree
   

   View Slide

2. View Slide

3. App
   

   View Slide

4. Workers
   App
   

   View Slide

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

   View Slide

6. View Slide

7. What’s changed since then?
   

   View Slide

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

   View Slide

9. redis_fdw
   Foreign Data Wrapper
   

   View Slide

10. View Slide

11. FDW
    

    View Slide

12. 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 Slide

13. View Slide

14. app cloud
    

    View Slide

15. View Slide

16. DEPLOY MANAGE SCALE
    

    View Slide

17. $ 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 Slide

18. Rimas Silkaitis
    Product
    

    View Slide

19. Heroku Postgres
    Over 1 Million Active DBs
    

    View Slide

20. Heroku Redis
    Over 100K Active Instances
    

    View Slide

21. Heroku Kafka
    

    View Slide

22. Configuring redis_fdw
    

    View Slide

23. 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 Slide

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

    View Slide

25. 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 Slide

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

    View Slide

27. View Slide

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

    View Slide

29. tabletype 'hash'
    

    View Slide

30. 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 Slide

31. 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 Slide

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

    View Slide

33. tabletype 'list'
    

    View Slide

34. 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 Slide

35. 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 Slide

36. tabletypes set and zset are
    similar to list
    

    View Slide

37. tablekeyset
    

    View Slide

38. 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 Slide

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

    View Slide

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

    View Slide

41. Counting Things
    

    View Slide

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

    View Slide

43. 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 Slide

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

    View Slide

45. 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 Slide

46. 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 Slide

47. 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 Slide

48. Slow Queries
    

    View Slide

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

    View Slide

50. Workers
    App
    

    View Slide

51. 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 Slide

52. 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 Slide

53. 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 Slide

54. 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 Slide

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

    View Slide

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

    View Slide

57. 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 Slide

58. 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 Slide

59. What if we need to update results?
    

    View Slide

60. 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 Slide

61. UPSERT?
    

    View Slide

62. 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 Slide

63. View Slide

64. 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 Slide

65. 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 Slide

66. Caveats
    •Postgres 9.3+
    •Redis 2.8+
    

    View Slide

67. View Slide

68. Redis GEO
    

    View Slide

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

    View Slide

70. ¯\_(ϑ)_/¯
    

    View Slide

71. zset under the hood
    

    View Slide

72. 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 Slide

73. 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 Slide

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

    View Slide

75. View Slide

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

    View Slide

77. Maybe someday
    

    View Slide

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

    View Slide