Lazy updates to indexes in a database
System(s) and method(s) facilitate improved performance for insert/update query requests in a database. A lazy updating based on delaying updates of newly inserted records combined with a master-staging partitioning scheme avoid deterioration of performance arising from updating indexes related to new records inserted in a database. Table partitioning as well as partitioning of indexes associated with the table allow new records to reside in manageable sections of memory for pre-configured periods of times prior to being updated. To avoid deterioration of performance associated with increasing size of table/index partitions, the size is maintained below specific thresholds that can be determined based on query workload and other historical data. Deployment of partitions among file systems and design of update delay times can further increase performance of lazy updating.
1. A computer system comprising at least one processor coupled to at least one machine-readable storage medium storing instructions executable by the at least one processor to implement:
an indexing component configured to index a plurality of records in a table in a database;
a partitioning component configured to create a table partition in the table to insert a record in the table; and
an update component configured to append the table partition associated with the inserted record in the table to a master partition at a delayed time with respect to an insertion time of the inserted record.
2. The computer system of claim 1 , wherein the update component is further configured to append an index partition associated with the inserted record or another inserted record to a base index partition at a delayed time with respect to an insertion time of the inserted record or other inserted record.
3. The computer system of claim 1 , wherein the table partition is of a predetermined size.
4. The computer system of claim 1 , wherein the delayed time is at least in part determined by a time at which a system that hosts the database is idle.
5. The computer system of claim 1 , wherein the delayed time is scheduled.
6. The computer system of claim 5 , wherein the partitioning component is further configured to determine a threshold size of the table partition, and the update component is further configured to append the table partition to the master partition when the table partition reaches the threshold size.
7. The computer system of claim 1 , wherein disparate partitions of the table have disparate indexes.
8. The computer system of claim 1 , further comprising a heuristics component configured to infer a maximum allowable size for a partition.
9. The computer system of claim 8 , wherein the heuristics component is further configured to infer a location in a file system to create an index partition based at least in part on a rate of access for the index.
10. The computer system of claim 1 , wherein the partitioning component is configured to generate a plurality of partitions across a plurality of databases.
11. The computer system of claim 1 , wherein the update component is further configured to append the table partition to the master partition at one of a plurality of pre-configured time intervals.
12. A computer-implemented method, comprising:
receiving a new record, the record associated with a table in a database;
creating a table partition of the table in response to receiving the new record, and inserting the record in the partition; and
merging the table partition with a master partition associated with the table at a delayed time with respect to inserting the record in the table partition.
13. The method of claim 12 , comprising merging the table partition with the master partition at a delayed time corresponding to a member of a set of pre-configured times.
14. The method of claim 12 , comprising merging the table partition with the master partition at a delayed time corresponding to a time at which a server that hosts the database is idle.
15. The method of claim 12 , further comprising inferring a maximum allowable size for a partition of an index associated with a record in the table, the size based on query statistics regarding access of the index.
16. The method of claim 12 , further comprising inferring a destination in a file system wherein one or more partitions are to be created based on query workload statistics for the database.
17. The method of claim 12 , further comprising inferring whether to perform the merging based on a database usage.
18. The method of claim 12 , further comprising receiving partitioning specifications relating to a maximum allowable size for the table partition.
19. The method of claim 12 , further comprising deploying the table partition to a disparate file system from a file system on which the table partition was originally created, based on the table partition reaching a threshold size.
20. A computer-readable storage medium storing instructions, the instructions to, if executed by a computing device, cause the computing device to perform operations comprising:
receiving a new record associated with a table in a database;
inserting the new record in a table partition in the table, if the table partition is smaller than a threshold size; and
merging the table partition with a master partition if the table partition reaches the threshold size.