Thread: using postgresql for session
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 ?
On Wed, 7 Oct 2015 09:58:04 -0600 "john.tiger" <john.tigernassau@gmail.com> 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 ? Nothing that I'm aware of. I've worked on large projects that keep the session data in a Postgres table with great success. -- Bill Moran
On 10/07/2015 08: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 ? That is how Django does it. It uses the django_session table. Maybe use some trigger if session changes? That would seem to the tricky part, determining when a session changes. We are > using python Bottle with psycopg2 (super simple, powerful combo) - are > we missing something magical about session plugins ? What sort of plugin are you talking about? > > -- Adrian Klaver adrian.klaver@aklaver.com
On 10/07/2015 10:53 AM, Bill Moran wrote:
Previous job gave up on mongo and switched to postgres since it was faster, more reliable.On Wed, 7 Oct 2015 09:58:04 -0600 "john.tiger" <john.tigernassau@gmail.com> 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 ?Nothing that I'm aware of. I've worked on large projects that keep the session data in a Postgres table with great success.
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
botherwith special "session" plugins and just use postgres to hold the data and retrieve it with psycopg2 ? Maybe use
sometrigger if session changes? We are using python Bottle with psycopg2 (super simple, powerful combo) - are we
missingsomething 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
(assumingit'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
itemsare 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
clientside sessions. They are generally easier to handle state across clusters and data centers. Some server side
session-likedata 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
componentto 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
alot more performance our Postgres without scaling our hardware. There just isn't much of a reason for having pg
managea simple KV store.
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, at 12:16, Jonathan Vanasco <postgres@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.
On 10/14/15 8:57 PM, Tiger Nassau wrote: > 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 The problem with sessions in Postgres tends to be the update rate, since every update results in a new table tuple, and possible new index tuples. That gets painful really fast for high-update workloads. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com