Re: using postgresql for session

maybe we will just use beaker with our bottle framework - thought it was duplicative to have redis since we have postgres and lookup speed should be  trivial since session only has a couple of small fields like account id and role

Sent from Type Mail

On Oct 14, 2015, 12:16, at 12:16, Jonathan Vanasco <postgres(at)2xlp(dot)com> wrote:
>
>On Oct 7, 2015, at 11:58 AM, john.tiger wrote:
>
>> has anyone used postgres jsonb for holding session ? Since server
>side session is really just a piece of data, why bother with special
>"session" plugins and just use postgres to hold the data and retrieve
>it with psycopg2 ? Maybe use some trigger if session changes? We
>are using python Bottle with psycopg2 (super simple, powerful combo) -
>are we missing something magical about session plugins ?
>
>I previously used TEXT or BLOB for holding session data, and pickled
>the data. I can't remember.
>
>If you're going to use PostgresSQL for the session, the big performance
>tip is to use partial index on the session key (assuming it's an
>md5-like hash).
>
>So you'd want a table that is something like this:
>
> CREATE TABLE session(
> session_id VARCHAR(32) PRIMARY KEY,
> session_data TEXT
> );
> CREATE INDEX idx_session_partial ON session(substr(session_id , 0,5))
>
>Then query like this
>
> SELECT * FROM session WHERE session_id = :session_id AND
>substr(session_id, 0, 5) = :session_id_substring ;
> SELECT * FROM session WHERE session_id = :session_id AND
>substr(session_id, 0, 5) = substr(:session_id, 0, 5) ;
>
>That will get the planner to use the partial index first, before using
>the session_id index. Depending on how many items are in your table,
>it can make your SELECTS several orders of magnitude faster.
>
>As for session plugins -- a lot of people in the web frameworks
>community are abandoning server side sessions for client side sessions.
>They are generally easier to handle state across clusters and data
>centers. Some server side session-like data is still needed, but it's
>often assembled from data in the client side.
>
>Most of the Python session plugins I've used have some sort of status
>check coupled with a cleanup function/middleware component to see if
>the object has changed at all. This way UPDATES only occur when
>needed.
>
>FWIW, I ended up migrating our sessions into redis. We already had
>redis running on the cluster, and offloading it got a lot more
>performance our Postgres without scaling our hardware. There just
>isn't much of a reason for having pg manage a simple KV store.