Re: Request for review of new redis-fdw module

George Silva wrote on 01/28/2015 11:49 AM:
> Great. Congratulations.
>
> How big is the latency in the FDW? This opens up new possibilities
> using redis. Very cool.
>

# explain analyze insert into rstr values ('k4434', '3234234');
QUERY PLAN
--------------------------------------------------------------------------------------------
Insert on rstr (cost=0.00..0.01 rows=1 width=0) (actual
time=0.288..0.288 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.001..0.002 rows=1 loops=1)
Planning time: 0.092 ms
Execution time: 0.582 ms

# explain analyze select * from rstr where key = 'k4434';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Foreign Scan on rstr (cost=10.00..11.00 rows=1 width=68) (actual
time=0.541..0.595 rows=1 loops=1)
Planning time: 0.382 ms
Execution time: 0.642 ms

I did do a benchmark in Golang to see the difference between redis-fdw,
temp-table, and table:

SELECT:
Redis FDW: 240663 ns/op
TEMP TABLE: 1130329 ns/op
TABLE: 764774 ns/op

INSERT:
Redis FDW: 187788 ns/op
TEMP TABLE: 106843 ns/op
TABLE: 3093156 ns/op

redis-fdw is currently unoptimized (no table option caching, etc) so
there is room for improvement. But so far so good.

Leon

>
> > I've implemented a completely new Redis FDW module which has
> little to do with
> > github.com/pg-redis-fdw/redis_fdw
> <http://github.com/pg-redis-fdw/redis_fdw>; although I did
> take some inspiration from in
> > on how the tables were to be designed but most I got from
> looking at the
> > oracle-fdw.
> >
> > My redis-fdw implementation supports read and write to the
> Redis backend, so
> > you can do insert, update, and delete. e.g. you can define a
> hash table as:
>
> is it possible to write multiple row's into the redis?
> something like
>
> insert into foreign_redis_table select * from big_table
>
>
> Thanks for pointing this out. I had a small bug which didn't let
> it succeed, but now it's been fixed and committed; I've also added
> a bulkdata.sql test script in the code to show an example.
>
>
> Anyway, thx, compiled and installed (just for fun, i'm not
> familiar with
> redis, and i'm not a coder)
>
>
> Redis is great for session management as it allows you to set an
> expiry for each key. So by using redis_fdw, you don't need to do
> multiple queries to determine of the session is still valid. e.g.:
>
> -- use a string (key-value) dataset for user sessions
> CREATE FOREIGN TABLE rsessions(
> sessid TEXT,
> value TEXT,
> expiry INT
> ) SERVER localredis
> OPTIONS (tabletype 'string');
> ALTER FOREIGN TABLE rsessions ALTER COLUMN sessid OPTIONS (ADD
> param 'key');
>
> -- a user table in postgres, can contain a whole bunch of other
> fields.
> CREATE TEMP TABLE users (
> userid INT,
> username TEXT,
> sessid TEXT
> );
>
>
> --
> -- get user's details at the same time as determining if they're
> session is still valid
> --
> WITH u AS (SELECT * FROM users WHERE username = 'foo')
> SELECT u.*, r.value, r.expiry
> FROM rsessions r, u
> WHERE r.sessid = (SELECT u.sessid FROM u);
>
>
>
> If the user's session is still valid then a row will be returned
> (Redis automatically destroys the key on expiry).
>
>
> --
> -- to reset the expiry timeout for the user
> --
> UPDATE rsessions SET expiry = 40 WHERE sessid = $1;
>
>
>
> Leon
>