2

I have a table in PostgreSQL 9.2 that records login sessions called user_login_session. This table contains a user_id, start_timestamp and end_timestamp columns, amongst others. This table has started growing as my system gains traction, and I can't purge old records for now.

I've defined an index on this table but I'm concerned about its growth over time. Is it possible to define an index which only keeps a handle to the newest login session row per user ID? Older rows are not accessed operationally.

Improve this question
2
  • do you have a growth estimation? what about the table size?
    – Ste
    Commented May 27, 2013 at 18:42
  • not really, was more a question on whether an index can be set up like that. for the sake of discussion, growth could be on the order of 10k rows/day or more, table size of 10MB/day or more
    – Emerson Farrugia
    Commented May 27, 2013 at 18:54

2 Answers 2

Reset to default
5

This is a classic case for partitioning.

When you insert a new record, have a trigger delete the old record from the user_login_session_latest sub-table and insert it into the user_login_session_history sub-table. Have both of them inherit from an empty top-level user_login_session table so you can still query the two transparently. You will need an additional trigger-maintained column like is_latest to partition on, since you can't do constraint exclusion on a condition that refers to other rows.

A partial index will work, but not as well as you might hope; it's hard to write a partial index based on the concept of "latest" (you need an app to set a flag), and it might not boost things as much as you expect. The partial index points at a page, but if that page contains 10 old records and one new record you might not gain that much; you'd need to regularly CLUSTER the table to see much benefit. Partitioning will be a much better option.

Alternately, you can maintain a materialized view of the "latest" users in another table using triggers or application logic. I'd generally prefer to use partitioning in preference to a materialized view for this.

Remember to tune autovacuum to vacuum your tables quite frequently if there's lots of updating/deleting going on.

Improve this answer
2
3

Is it possible to define an index which only keeps a handle to the newest login session row per user ID? No. What you can do is whenever you insert into user_login_session you do an "upsert" into another table which contains only the newest_user_login_session

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.