3 of 4 edited body

Need help designing a database

We are designing a system which holds statistical parameters about a particular user. As the users use the system the statistics change and the updated statistics are stored in the database. So for a new user a record is inserted and after that the records are only updated. So I expect this database to be update heavy.

Also when we see the user online we want to query his record from the database so that we can use the stored data to do some characterization.

The fields of the database which are important are

  1. user Identifier to uniquely identify the user.
  2. last accessed time to know when the record was last updated.

If a user is idle for more than 60 days we want to prune the entry from the database. We currently use postgres as our server.

I have been working on how to implement a efficient way to prune the database while keeping update and query performance satisfactory.

Approach 1 : Using the postgres range partitioning the table based on date and then adding triggers for insert and update. A cron job would delete the required old partition and add a new partition. If a user frequently accesses our system the record would move between the partitions because of the update trigger. As a result stale records stay in the partition. With such a solution deleting stale records is fast since all we need to do is truncate the old partition table. Update performance is pretty good because if the records are update frequently in the single day they stay in the same partition and a update across day causes it to move from one partition to another.

But the query performance will be bad. When the user comes online we do not know which of the partitions has the user record. So this would cause a scan on all tables for a query which would be expensive.

Approach 2 : A multilevel partition with primary partition being userid%number of partitions and the second level partition based on last date is another solution. I do not know if this would work since all it would do is reduce it to a subset of all records.

Any help or suggestions on this would be appreciated