Re: Correct/optimal DML query for application session management ?

>> 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