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. App

  2. FDW

  3. 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
  4. $ 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
  5. 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
  6. 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
  7. redis> SET presentation awesome OK redis> neovintage::DB=> SELECT * from

    redis_scalar; key | value --------------+------- presentation | awesome (1 row)
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. neovintage::DB=> SELECT * from redis_set; value ------- hello world (2

    row) redis> SADD myset “hello” 1 redis> SADD myset “world” 1
  14. 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
  15. 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;
  16. 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;
  17. Benefits • Cross reference data in Postgres with high velocity

    information • Issue one query to take snapshots of counts in Redis. Make data warehousing easier.
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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 :-(
  27. 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.
  28. redis> GEOADD Sicily 13.361389 38.115556 "Palermo" 15.087269 37.502669 “Catania" 2

    redis> GEOHASH Sicily Palermo sqc8b49rny0 redis> GEOHASH Sicily Catania sqdtr74hyu0
  29. 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