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?

Dfc1f1582a8680e4f5840f79892339b2?s=128

Rimas Silkaitis

May 11, 2016
Tweet

Transcript

  1. Rimas Silkaitis Postgres & Redis Sitting in a Tree

  2. None
  3. App

  4. Workers App

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

  6. None
  7. What’s changed since then?

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

  9. redis_fdw Foreign Data Wrapper

  10. None
  11. FDW

  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
  13. None
  14. app cloud

  15. None
  16. DEPLOY MANAGE SCALE

  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
  18. Rimas Silkaitis Product

  19. Heroku Postgres Over 1 Million Active DBs

  20. Heroku Redis Over 100K Active Instances

  21. Heroku Kafka

  22. Configuring redis_fdw

  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
  24. $ heroku pg:links create DATABASE_URL REDIS_URL —as redis_db -a sushi

  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
  26. redis> SET presentation awesome OK redis> neovintage::DB=> SELECT * from

    redis_scalar; key | value --------------+------- presentation | awesome (1 row)
  27. None
  28. More Options for PG Tables • tabletype • tablekeyprefix •

    tablekeyset • singleton_key
  29. tabletype 'hash'

  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
  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
  32. Column Value Type redis_fdw return value text[] array of text

    text text as array
  33. tabletype 'list'

  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
  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
  36. tabletypes set and zset are similar to list

  37. tablekeyset

  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
  39. neovintage::DB=> SELECT * from redis_set; value ------- hello world (2

    row) redis> SADD myset “hello” 1 redis> SADD myset “world” 1
  40. http://oldblog.antirez.com/post/take-advantage-of-redis-adding-it-to-your-stack.html

  41. Counting Things

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

  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
  44. neovintage::DB=> SELECT * from redis_counts; user_id | count ----------+------------ user:2

    | 10 user:3 | 200 (2 row) GROSS
  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;
  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;
  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.
  48. Slow Queries

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

    LIMIT 10
  50. Workers App

  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
  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
  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
  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
  55. neovintage::DB=> UPDATE top_commenters set commenters = subquery.names from (select array_agg(name)

    as names from users) AS subquery ;
  56. What if we need to show score or count? zset

  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
  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
  59. What if we need to update results?

  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
  61. UPSERT?

  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
  63. None
  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 :-(
  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.
  66. Caveats •Postgres 9.3+ •Redis 2.8+

  67. None
  68. Redis GEO

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

  70. ¯\_(ϑ)_/¯

  71. zset under the hood

  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
  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
  74. neovintage::DB=> SELECT * FROM redis_geohash; city | geohash ----------+------------ Palermo

    | sqc8b49rny0 Catania | sqdtr74hyu0
  75. None
  76. •Still need to install Postgis (geohash functions) •Can’t use any

    of the GEO functions from redis
  77. Maybe someday

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