Caching views on historical data
In a general aspect, a computer-implemented method for executing a query on a table of a database, where the table has multiple partitions, can include receiving a query requesting a view on the table. The view on the table can be based on data included in a partition of the multiple partitions of the table. The method can also include determining a cached result to the query is not available in the database and generating a result to the query from, at least, the data of the partition of the multiple partitions. After the generating, the method can include building a cache including the result to the query, associating a transaction identifier for the query with the result to the query result in the cache; and returning the result to the query.
1. A computer-implemented method for executing queries on a table of a database, the table having multiple partitions, the method comprising:
building, by a computer, a first cache entry, the first cache entry in a memory including a first query result of a view on the table and a first transaction identifier corresponding with the building of the first cache entry, the first cache entry being based on data of a first partition of the multiple partitions;
building, by the computer, a second cache entry in the memory, the second cache entry including a second query result of the view on the table and a second transaction identifier corresponding with the building of the second cache entry, the second cache entry being based on data of a second partition of the multiple partitions;
after the building the first cache entry:
receiving, by the computer, a data manipulation language statement including a request to update the first partition;
generating, by the computer, a partition update record including a third transaction identifier corresponding with the data manipulation language statement, the third transaction identifier being greater than the first transaction identifier;
receiving, by the computer, a query including a request for the first query result;
comparing, by the computer, the first transaction identifier with the third transaction identifier to determine the third transaction identifier being greater than the first transaction identifier;
in response to the comparing, invalidating the first cache entry:
generating, by the computer, a regenerated first query result, wherein the generating of the regenerated first query result further includes reloading the updated first partition into the memory;
rebuilding, by the computer, the first cache entry including the regenerated first query result; and
returning, by the computer, a response to the query including the regenerated first query result.
2. The computer-implemented method as in claim 1 , wherein the first partition includes a first historical partition and the second partition includes a second historical partition.
3. The computer-implemented method as in claim 1 , the query being a first query, the method further comprising:
receiving a second query including a request for the second query result; comparing the second transaction identifier with a fourth transaction identifier corresponding with a most recent modification of the second partition, the fourth transaction identifier being less than the second transaction identifier; and
in response to the comparing the second transaction identifier to the fourth transaction identifier, returning a response to the second query including the second query result from the second cache entry.
4. The computer-implemented method as in claim 1 , wherein the database includes an in-memory database.
5. The computer-implemented method as in claim 4 , wherein:
the first partition and the second partition are stored on disk-based storage;
generating the first query result of the first cache entry includes loading the first partition into the in-memory database;
generating the second query result of the second cache entry includes loading the second partition into the in-memory database; and
generating the regenerated first query result of the rebuilt first cache entry includes reloading the updated first partition into the in-memory database.
6. A system for executing queries on one or more database tables, the system comprising:
a memory;
a database including a table having multiple partitions, the database further including: memory modules storing one or more of the multiple partitions of the table;
a disk-based storage device storing one or more of the multiple partitions of the table, and a query processor configured to:
build a first cache entry, the first cache entry including a first query result of a view on the table and a first transaction identifier corresponding with the building of the first cache entry, the first cache entry being based on data of a first partition of the multiple partitions; and
build a second cache entry, the second cache entry including a second query result of the view on the table and a second transaction identifier corresponding with the building of the second cache entry, the second cache entry being based on data of a second partition of the multiple partitions;
a data manipulation language (DML) processor configured to, after the building the first cache entry:
receive a data manipulation language statement including a request to update the first partition; and
generate a partition update record including a third transaction identifier corresponding with the data manipulation language statement, the third transaction identifier being greater than the first transaction identifier;
the query processor being further configured to:
receive a query including a request for the first query result;
compare the first transaction identifier with the third transaction identifier to determine the third transaction identifier being greater than the first transaction identifier;
in response to the comparing; invalidate the first cache entry:
generate, by the computer, a regenerated first query result, wherein the generating of the regenerated first query result further includes reloading the updated first partition into the memory;
rebuild the first cache entry including the regenerated first query result; and return a response to the query including the regenerated first query result.
7. The system as in claim 6 , wherein the first partition includes a first historical partition and the second partition includes a second historical partition.
8. The system as in claim 6 , the query being a first query, the query processor being further configured to:
receive a second query including a request for the second query result;
compare the second transaction identifier with a fourth transaction identifier corresponding with a most recent modification of the second partition, the fourth transaction identifier being less than the second transaction identifier; and
in response to the comparing the second transaction identifier to the fourth transaction identifier, return a response to the second query including the second query result from the second cache entry.
9. The system as in claim 6 , wherein the database includes an in memory database.
10. The system as in claim 9 , wherein: the first partition and the second partition are stored on the disk-based storage device;
generating the first query result of the first cache entry includes loading the first partition into the in-memory database;
generating the second query result of the second cache entry includes loading the second partition into the in-memory database; and
generating the regenerated first query result of the rebuilt first cache entry includes reloading the updated first partition into the in-memory database.
11. The system as in claim 6 , wherein the query processor includes an SOL processor.
12. The system as in claim 6 , wherein the query processor and the DML processor are included in a single processor, the single processor including an SQL processor.