Tweeted twitter.com/#!/StackDBAs/status/533906017730322432
4 added 1063 characters in body
source | link

Approach 1 Current Design plan: Using the

EDIT: Estimated Number of Records: 100 million.

Set up partition on postgres range. Each partition corresponds to one day of usage. The partitioning criteria would be the table based on date and then adding triggersof the last accessed time. The primary key for insertthe partition would be UserId.

If a record is accessed frequently in the same day, it would map to the same partition but across different days i would have to delete the entry from the old partition and move it to the new partition using a update trigger. 

A cron job would run daily and delete the required old required partition and add a new partition. If a user frequently accesses our system the record would move between the new partitions because of the update trigger. As a resultA stale records stayentry would probably remain in the partition. With such a solution deleting stale records is fast since all we need to do is and truncate table on the oldrequired partition tablewould drop all the entries. Update performance is pretty good because

The query would be done based on the UserID, if the records are update frequently inpartition logic is correct we would find the single day they stayuserid in only one of the same partition and a updatepartitions. So the query would take place across day causes it to move fromall partitions but find only one partition to another.record

ButI need the querypruning performance willto be badgood. WhenThis is because the user comes online wetable has primary key by User ID. It has no indexes based on time. Since the last accessed time keeps changing it cant be made part of any index. Also there is no logical clustered index field for this table.

If partitioning is not done, this would mean i would have to do a full table scan and since last accessed time is not know whichpart of the partitions has the userkey, it would bring every record. So to the database eventually and this would causereplace existing frequently used pages used by our system. I would want to avoid a full scan on all tables for a query which would be expensivethe table.

Approach 2 : A multilevelAs pointed out, moving between partitions would require a delete and a insert into the other table. Space is not a issue. I can add the partition withdate as primary partition being userid%numberkey along with the UserID field of partitionsthe database and modify the second level partition basedquery method to take the latest record. The query would be done on last date is another solutionthe primary keys and hence the computation would only be on the index files. I do not know if this would work since all itcould avoid the delete and let the entries be in the database with the cron job clearing the table eventually.

But nevertheless insert into another partition would do is reduce it toalways happen on moving a subset of all recordsrecord across paritions. A single table approach would solve this but then the delete performance would suffer.

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.

Current Design plan:

EDIT: Estimated Number of Records: 100 million.

Set up partition on postgres. Each partition corresponds to one day of usage. The partitioning criteria would be the date of the last accessed time. The primary key for the partition would be UserId.

If a record is accessed frequently in the same day, it would map to the same partition but across different days i would have to delete the entry from the old partition and move it to the new partition using a update trigger. 

A cron job would run daily and delete the old required partition and add the new partitions. A stale entry would probably remain in the partition and truncate table on the required partition would drop all the entries.

The query would be done based on the UserID, if the partition logic is correct we would find the userid in only one of the partitions. So the query would take place across all partitions but find only one record

I need the pruning performance to be good. This is because the table has primary key by User ID. It has no indexes based on time. Since the last accessed time keeps changing it cant be made part of any index. Also there is no logical clustered index field for this table.

If partitioning is not done, this would mean i would have to do a full table scan and since last accessed time is not part of the key, it would bring every record to the database eventually and this would replace existing frequently used pages used by our system. I would want to avoid a full scan on the table.

As pointed out, moving between partitions would require a delete and a insert into the other table. Space is not a issue. I can add the partition date as primary key along with the UserID field of the database and modify the query method to take the latest record. The query would be done on the primary keys and hence the computation would only be on the index files. I could avoid the delete and let the entries be in the database with the cron job clearing the table eventually.

But nevertheless insert into another partition would always happen on moving a record across paritions. A single table approach would solve this but then the delete performance would suffer.

3 edited body
source | link

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

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

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

2 edited body
source | link

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 corn 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 stakestale records stay in the partition. With such a solution deleting stakestale 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

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 corn 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 stake records stay in the partition. With such a solution deleting stake 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

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

1
source | link