Re: Request for review of new redis-fdw module - Mailing list pgsql-general
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: HamQ@viessmann.com
web: www.viessmann.fr
Von: Leon Dang <ldang@nahannisys.com>
An: George Silva <georger.silva@gmail.com>
Kopie: Andreas Kretschmer <akretschmer@spamfence.net>, pgsql-general@postgresql.org
Datum: 28.01.2015 23:02
Betreff: Re: [GENERAL] Request for review of new redis-fdw module
Gesendet von: pgsql-general-owner@postgresql.org
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
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: HamQ@viessmann.com
web: www.viessmann.fr
Von: Leon Dang <ldang@nahannisys.com>
An: George Silva <georger.silva@gmail.com>
Kopie: Andreas Kretschmer <akretschmer@spamfence.net>, pgsql-general@postgresql.org
Datum: 28.01.2015 23:02
Betreff: Re: [GENERAL] Request for review of new redis-fdw module
Gesendet von: pgsql-general-owner@postgresql.org
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
Attachment
pgsql-general by date: