Re: vacuum, performance, and MVCC

>> > You mean systems that are designed so exactly, that they can't take
>> 10%
>> > performance change ?
>>
>> No, that's not really the point, performance degrades over time, in one
>> minute it degraded 10%.
>>
>> The update to session ratio has a HUGE impact on PostgreSQL. If you have
>> a
>> thousand active sessions, it may take a minute to degrade 10% assuming
>> some level of active vs operations per session per action.
>
> So don't do an update. Multiple updates to the same row block anyway
> which is generally not something you want anyway.

The example is a very active web site, the flow is this:

query for session information
process HTTP request
update session information

This happens for EVERY http request. Chances are that you won't have
concurrent requests for the same row, but you may have well over 100 HTTP
server processes/threads answering queries in your web server farm.

>
> If you INSERT into multiple partitions (by time -- say one per minute)
> and TRUNCATE periodically (30 minute old partitions for 30 minute
> expiry) it works much better. Expiring the session is quite fast as well
> since they'll go away with the truncate.
>
> Index on sessionid and time and grab the row with the most recent time.

I doubt that that approach (1) answers the problem or (2) would be more
efficient.
> --
>