![Page 1: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/1.jpg)
Rimas Silkaitis
Postgres & Redis Sitting in a Tree
![Page 2: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/2.jpg)
![Page 3: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/3.jpg)
App
![Page 4: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/4.jpg)
Workers
App
![Page 5: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/5.jpg)
http://oldblog.antirez.com/post/take-advantage-of-redis-adding-it-to-your-stack.html
![Page 6: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/6.jpg)
![Page 7: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/7.jpg)
What’s changed since then?
![Page 8: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/8.jpg)
redis_fdw
https://github.com/pg-redis-fdw/redis_fdw
![Page 9: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/9.jpg)
redis_fdw
Foreign Data Wrapper
![Page 10: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/10.jpg)
![Page 11: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/11.jpg)
FDW
![Page 12: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/12.jpg)
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
![Page 13: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/13.jpg)
![Page 14: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/14.jpg)
app cloud
![Page 15: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/15.jpg)
![Page 16: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/16.jpg)
DEPLOY MANAGE SCALE
![Page 17: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/17.jpg)
$ 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
![Page 18: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/18.jpg)
Rimas Silkaitis
Product
![Page 19: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/19.jpg)
Heroku PostgresOver 1 Million Active DBs
![Page 20: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/20.jpg)
Heroku RedisOver 100K Active Instances
![Page 21: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/21.jpg)
Heroku Kafka
![Page 22: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/22.jpg)
Configuring redis_fdw
![Page 23: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/23.jpg)
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
![Page 24: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/24.jpg)
$ heroku pg:links create DATABASE_URL REDIS_URL —as redis_db -a sushi
![Page 25: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/25.jpg)
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
![Page 26: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/26.jpg)
redis> SET presentation awesome
OK
redis>
neovintage::DB=> SELECT * from redis_scalar;
key | value
--------------+-------
presentation | awesome
(1 row)
![Page 27: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/27.jpg)
![Page 28: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/28.jpg)
More Options for PG Tables
• tabletype
• tablekeyprefix
• tablekeyset
• singleton_key
![Page 29: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/29.jpg)
tabletype 'hash'
![Page 30: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/30.jpg)
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
![Page 31: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/31.jpg)
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
![Page 32: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/32.jpg)
Column Value Type redis_fdw return value
text[] array of text
text text as array
![Page 33: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/33.jpg)
tabletype 'list'
![Page 34: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/34.jpg)
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
![Page 35: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/35.jpg)
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
![Page 36: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/36.jpg)
tabletypes set and zset are similar to list
![Page 37: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/37.jpg)
tablekeyset
![Page 38: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/38.jpg)
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
![Page 39: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/39.jpg)
neovintage::DB=> SELECT * from redis_set;
value
-------
hello
world
(2 row)
redis> SADD myset “hello”
1
redis> SADD myset “world”
1
![Page 40: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/40.jpg)
http://oldblog.antirez.com/post/take-advantage-of-redis-adding-it-to-your-stack.html
![Page 41: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/41.jpg)
Counting Things
![Page 42: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/42.jpg)
redis> INCR user:<id>
redis> EXPIRE user:<id> 60
![Page 43: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/43.jpg)
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
![Page 44: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/44.jpg)
neovintage::DB=> SELECT * from redis_counts;
user_id | count
----------+------------
user:2 | 10
user:3 | 200
(2 row)
GROSS
![Page 45: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/45.jpg)
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;
![Page 46: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/46.jpg)
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;
![Page 47: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/47.jpg)
Benefits
• Cross reference data in Postgres with high velocity information
• Issue one query to take snapshots of counts in Redis. Make data warehousing easier.
![Page 48: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/48.jpg)
Slow Queries
![Page 49: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/49.jpg)
SELECT * FROM foo WHERE ... ORDER BY rank DESC LIMIT 10
![Page 50: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/50.jpg)
Workers
App
![Page 51: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/51.jpg)
psudeo-codeFUNCTION 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
![Page 52: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/52.jpg)
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
![Page 53: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/53.jpg)
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
![Page 54: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/54.jpg)
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
![Page 55: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/55.jpg)
neovintage::DB=> UPDATE top_commenters
set commenters = subquery.names
from (select array_agg(name) as names
from users) AS subquery
;
![Page 56: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/56.jpg)
What if we need to show score or count?
zset
![Page 57: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/57.jpg)
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
![Page 58: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/58.jpg)
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
![Page 59: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/59.jpg)
What if we need to update results?
![Page 60: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/60.jpg)
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
![Page 61: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/61.jpg)
UPSERT?
![Page 62: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/62.jpg)
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
![Page 63: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/63.jpg)
![Page 64: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/64.jpg)
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 :-(
![Page 65: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/65.jpg)
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.
![Page 66: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/66.jpg)
Caveats
•Postgres 9.3+
•Redis 2.8+
![Page 67: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/67.jpg)
![Page 68: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/68.jpg)
Redis GEO
![Page 69: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/69.jpg)
Is it possible to replace Postgis with Redis GEO ?
![Page 70: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/70.jpg)
¯\_( )_/¯
![Page 71: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/71.jpg)
zset under the hood
![Page 72: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/72.jpg)
redis> GEOADD Sicily 13.361389 38.115556 "Palermo" 15.087269 37.502669
“Catania"
2
redis> GEOHASH Sicily Palermo
sqc8b49rny0
redis> GEOHASH Sicily Catania
sqdtr74hyu0
![Page 73: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/73.jpg)
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
![Page 74: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/74.jpg)
neovintage::DB=> SELECT * FROM redis_geohash;
city | geohash
----------+------------
Palermo | sqc8b49rny0
Catania | sqdtr74hyu0
![Page 75: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/75.jpg)
![Page 76: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/76.jpg)
•Still need to install Postgis (geohash functions)
•Can’t use any of the GEO functions from redis
![Page 77: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/77.jpg)
Maybe someday
![Page 78: Postgres & Redis Sitting in a Tree- Rimas Silkaitis, Heroku](https://reader031.vdocuments.us/reader031/viewer/2022021919/587c05091a28ab7c668b756f/html5/thumbnails/78.jpg)
Thank You!Rimas Silkaitis / neovintage.org / @neovintage