Tweet
Tweet

Slide 1

Slide 1 text

Rimas Silkaitis Postgres & Redis Sitting in a Tree

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

App

Slide 4

Slide 4 text

Workers App

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

What’s changed since then?

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

redis_fdw Foreign Data Wrapper

Slide 10

Slide 10 text

No content

Slide 11

Slide 11 text

FDW

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

app cloud

Slide 15

Slide 15 text

No content

Slide 16

Slide 16 text

DEPLOY MANAGE SCALE

Slide 17

Slide 17 text

$ 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

Slide 18

Slide 18 text

Rimas Silkaitis Product

Slide 19

Slide 19 text

Heroku Postgres Over 1 Million Active DBs

Slide 20

Slide 20 text

Heroku Redis Over 100K Active Instances

Slide 21

Slide 21 text

Heroku Kafka

Slide 22

Slide 22 text

Configuring redis_fdw

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

No content

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

tabletype 'hash'

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

tabletype 'list'

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

tabletypes set and zset are similar to list

Slide 37

Slide 37 text

tablekeyset

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

Counting Things

Slide 42

Slide 42 text

redis> INCR user: redis> EXPIRE user: 60

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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;

Slide 46

Slide 46 text

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;

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

Slow Queries

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

Workers App

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

What if we need to show score or count? zset

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

What if we need to update results?

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

UPSERT?

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

No content

Slide 64

Slide 64 text

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 :-(

Slide 65

Slide 65 text

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.

Slide 66

Slide 66 text

Caveats •Postgres 9.3+ •Redis 2.8+

Slide 67

Slide 67 text

No content

Slide 68

Slide 68 text

Redis GEO

Slide 69

Slide 69 text

Is it possible to replace Postgis with Redis GEO ?

Slide 70

Slide 70 text

¯\_(ϑ)_/¯

Slide 71

Slide 71 text

zset under the hood

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

No content

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

Maybe someday

Slide 78

Slide 78 text

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