Please remove me from mailing list. I have deleted my account from pgsql forum and I still get emails!
Thanks for your understanding. Bien cordialement, / Mit freundlichen Grüßen / Yours sincerely, Quirin HAMP _____________________________ Viessmann Faulquemont S.A.S Responsable développement systèmes thermiques Avenue André GOUY F 57380 Faulquemont Tel.: +33 (0)3.87.90.64.12 Mob.: +33 (0)6.88.54.57.08 e-mail: [email protected] web: www.viessmann.fr Von: Leon Dang <[email protected]> An: George Silva <[email protected]> Kopie: Andreas Kretschmer <[email protected]>, [email protected] Datum: 28.01.2015 23:02 Betreff: Re: [GENERAL] Request for review of new redis-fdw module Gesendet von: [email protected] 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; 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 _________________________________________________________________________________ Viessmann Faulquemont SAS Avenue André Gouy 57380 FAULQUEMONT www.viessmann.fr
