Thread: Correct/optimal DML query for application session management ?
Hi, I'm probably being incredibly stupid and missing something incredibly simple but I've got a bit of query-writers block here ! create table app_sessions( session_id char(64) unique not null, user_id char(32) unique not null, session_start bigint not null, session_lastactive bigint not null ); The rules are : Enforced session timeout after 86400 seconds (1 day) Last active less than 1 hour ago. My idea to clean out stale sessions : delete from app_sessions where extract (epoch from now())-session_start>86400 or session_lastactive<=extract (epoch from now())-3600; But of course that's going to be a nasty query, so that's why I think I'm missing something and need a fresh pair of eyes (or a whole mailing list's worth of eyes !!). Thanks guys !
On 1/6/2015 12:02 PM, Tim Smith wrote: > Hi, > > I'm probably being incredibly stupid and missing something incredibly > simple but I've got a bit of query-writers block here ! > > create table app_sessions( > session_id char(64) unique not null, > user_id char(32) unique not null, > session_start bigint not null, > session_lastactive bigint not null > ); > > > The rules are : > Enforced session timeout after 86400 seconds (1 day) > Last active less than 1 hour ago. > > My idea to clean out stale sessions : > delete from app_sessions where extract (epoch from > now())-session_start>86400 or session_lastactive<=extract (epoch from > now())-3600; > > But of course that's going to be a nasty query, so that's why I think > I'm missing something and need a fresh pair of eyes (or a whole > mailing list's worth of eyes !!). > > Thanks guys ! > > I don't see any other way. Why do you think it'll be so nasty? Cuz it'll table scan? You have no indexes so it doesn't matter what you write, it'll have to scan all rows. How many rows do you expect to have? 500? 1000? Table scan will be fine. If you wanted to make it more readable .. but work the same, you could use timestamptz instead of bigint, and then write: where current_timestamp - '1 day'::interval < session_start -Andy
Hi Andy, Yeah, the table scan was what worried me. As for no indexes ? I just didn't put the "create index" statements in my post ... ;-) Tim On 6 January 2015 at 18:35, Andy Colson <andy@squeakycode.net> wrote: > On 1/6/2015 12:02 PM, Tim Smith wrote: >> >> Hi, >> >> I'm probably being incredibly stupid and missing something incredibly >> simple but I've got a bit of query-writers block here ! >> >> create table app_sessions( >> session_id char(64) unique not null, >> user_id char(32) unique not null, >> session_start bigint not null, >> session_lastactive bigint not null >> ); >> >> >> The rules are : >> Enforced session timeout after 86400 seconds (1 day) >> Last active less than 1 hour ago. >> >> My idea to clean out stale sessions : >> delete from app_sessions where extract (epoch from >> now())-session_start>86400 or session_lastactive<=extract (epoch from >> now())-3600; >> >> But of course that's going to be a nasty query, so that's why I think >> I'm missing something and need a fresh pair of eyes (or a whole >> mailing list's worth of eyes !!). >> >> Thanks guys ! >> >> > > I don't see any other way. Why do you think it'll be so nasty? Cuz it'll > table scan? You have no indexes so it doesn't matter what you write, it'll > have to scan all rows. How many rows do you expect to have? 500? 1000? > Table scan will be fine. > > If you wanted to make it more readable .. but work the same, you could use > timestamptz instead of bigint, and then write: > > where current_timestamp - '1 day'::interval < session_start > > -Andy >
>> On 1/6/2015 12:02 PM, Tim Smith wrote: >>> >>> Hi, >>> >>> I'm probably being incredibly stupid and missing something incredibly >>> simple but I've got a bit of query-writers block here ! >>> >>> create table app_sessions( >>> session_id char(64) unique not null, >>> user_id char(32) unique not null, >>> session_start bigint not null, >>> session_lastactive bigint not null >>> ); >>> >>> >>> The rules are : >>> Enforced session timeout after 86400 seconds (1 day) >>> Last active less than 1 hour ago. >>> >>> My idea to clean out stale sessions : >>> delete from app_sessions where extract (epoch from >>> now())-session_start>86400 or session_lastactive<=extract (epoch from >>> now())-3600; >>> >>> But of course that's going to be a nasty query, so that's why I think >>> I'm missing something and need a fresh pair of eyes (or a whole >>> mailing list's worth of eyes !!). >>> >>> Thanks guys ! >>> >>> >> >> I don't see any other way. Why do you think it'll be so nasty? Cuz it'll >> table scan? You have no indexes so it doesn't matter what you write, it'll >> have to scan all rows. How many rows do you expect to have? 500? 1000? >> Table scan will be fine. >> >> If you wanted to make it more readable .. but work the same, you could use >> timestamptz instead of bigint, and then write: >> >> where current_timestamp - '1 day'::interval < session_start >> >> -Andy >> On 1/6/2015 1:56 PM, Tim Smith wrote:> Hi Andy, > > Yeah, the table scan was what worried me. > > As for no indexes ? I just didn't put the "create index" statements > in my post ... ;-) > > Tim > Please don't top post. With and without indexes is a completely different answer. See the performance section at: https://wiki.postgresql.org/wiki/Guide_to_reporting_problems -Andy
On 1/6/2015 1:56 PM, Tim Smith wrote: > Hi Andy, > > Yeah, the table scan was what worried me. > > As for no indexes ? I just didn't put the "create index" statements > in my post ... ;-) > > Tim > > On 6 January 2015 at 18:35, Andy Colson <andy@squeakycode.net> wrote: >> On 1/6/2015 12:02 PM, Tim Smith wrote: >>> >>> Hi, >>> >>> I'm probably being incredibly stupid and missing something incredibly >>> simple but I've got a bit of query-writers block here ! >>> >>> create table app_sessions( >>> session_id char(64) unique not null, >>> user_id char(32) unique not null, >>> session_start bigint not null, >>> session_lastactive bigint not null >>> ); >>> >>> >>> The rules are : >>> Enforced session timeout after 86400 seconds (1 day) >>> Last active less than 1 hour ago. >>> >>> My idea to clean out stale sessions : >>> delete from app_sessions where extract (epoch from >>> now())-session_start>86400 or session_lastactive<=extract (epoch from >>> now())-3600; >>> >>> But of course that's going to be a nasty query, so that's why I think >>> I'm missing something and need a fresh pair of eyes (or a whole >>> mailing list's worth of eyes !!). >>> >>> Thanks guys ! >>> >>> >> >> I don't see any other way. Why do you think it'll be so nasty? Cuz it'll >> table scan? You have no indexes so it doesn't matter what you write, it'll >> have to scan all rows. How many rows do you expect to have? 500? 1000? >> Table scan will be fine. >> >> If you wanted to make it more readable .. but work the same, you could use >> timestamptz instead of bigint, and then write: >> >> where current_timestamp - '1 day'::interval < session_start >> >> -Andy >> > > Oh, more important, if its only gonna be 1000 rows or less, then I'd say drop the indexes and let it table scan. I bet it'll be faster. -Andy
> On 06 Jan 2015, at 19:02, Tim Smith <randomdev4+postgres@gmail.com> wrote: > create table app_sessions( > session_id char(64) unique not null, > user_id char(32) unique not null, > session_start bigint not null, > session_lastactive bigint not null > ); Just an observation: Are you sure that you don’t want to allow your users to have multiple sessions? You made users uniqueacross the entire table, which mean there can only ever be one session for a user. Unless you delete ‘expired’ sessionsASAP, users won’t be able to start a new session again after their original session expired. That may be desirable for your application and thus intended, but perhaps it was not? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.