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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
