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 <postg...@2xlp.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.
