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
3. App
4. Workers App
5. http://oldblog.antirez.com/post/take-advantage-of-redis-adding-it-to-your-stack.html
7. What’s changed since then?
8. redis_fdw https://github.com/pg-redis-fdw/redis_fdw
9. redis_fdw Foreign Data Wrapper
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
14. app cloud
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)
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
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+
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
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