Database Storage Architecture

• US 20110016157A1
• Filed: 07/14/2009
• Published: 01/20/2011
• Est. Priority Date: 07/14/2009
• Status: Active Grant

• Current Assignee: Micro Focus LLC (Open Text Corporation)
• Original Assignee: Vertica Systems Incorporated (Hewlett-Packard Enterprise Company)
• Inventors: Bodagala, Sreenath, Vandiver, Benjamin, Venkatesh, Rajat, Bear, Chuck, Lawande, Shilpa
• Granted Patent: US 8,700,674 B2
• Time in Patent Office: Days
• US Class Current: 707/804
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 3/061 Improving I/O performance
• CPC Class Codes: G06F 3/0649 Lifecycle management
• CPC Class Codes: G06F 3/0685 Hybrid storage combining heterogeneous device types, e.g. hierarchical storage, hybrid arrays

Abstract

Methods, systems and program products for database storage. In one implementation, data of a projection of a database is stored at least partly in grouped ROS format and partly in column format based on patterns of updating the projection data. The projection data is updated so that the updated projection is stored partly in grouped ROS format and partly in column format.

Claims

1. A computer-implemented method comprising:

storing data of a projection of a database at least partly in grouped ROS format and partly in column format based on patterns of updating the projection data; and

updating the projection data so that the updated projection is stored partly in grouped ROS format and partly in column format.

2. The method of claim 1 in which updating further comprises storing rows of data for columns of the projection persistently.

3. The method of claim 2 in which a time when the rows are stored is based on at least one of an age of the rows or an aggregate size of the rows.

4. The method of claim 1 in which updating further comprises storing rows of data for columns of the projection in a write-optimized store.

5. The method of claim 1 in which updating further comprises:

estimating a storage size of rows of projection data to be stored based on a size of previously stored projection rows; and

storing the rows in grouped ROS format or in column format based on the estimated storage size.

6. The method of claim 1 in which the projection data that is stored in grouped ROS format comprises one or more rows of a first set of the projection'"'"'s columns.

7. The method of claim 6 in which each row'"'"'s first columns are stored together in a single file with at least one other row'"'"'s first columns.

8. The method of claim 1 in which the projection data that is stored in column format comprises one or more rows for one or more first columns of the projection.

9. The method of claim 8 in which each row'"'"'s first columns are stored in separate files and each separate file stores the same first column from more than one row.

10. The method of claim 1 in which updating further comprises storing rows of projection data for columns in grouped ROS format based on a frequency of use of the columns of the rows.

11. The method of claim 1 wherein updating is performed in response to a request to write data to the projection.

12. The method of claim 1 wherein updating is performed in response to processing a database operator to write data to storage.

13. A computer-implemented method comprising:

selecting at least two different storage formats for data of a database projection based on patterns of updating data of the projection; and

updating projection data based on the patterns using the selected storage formats.

14. The method of claim 13 in which the storage formats comprise at least two of a grouped ROS storage format, a column storage format, and a write-optimized storage format.

15. The method of claim 13 in which updating further comprises storing rows of the projection data of columns of the projection persistently.

16. The method of claim 15 in which the rows are stored at a time based on at least one of an age of the rows or an aggregate size of the rows.

17. The method of claim 13 in which updating further comprises storing rows of the projection data for columns of the projection in a write-optimized store.

18. The method of claim 13 in which updating further comprises:

estimating a storage size of rows of the updated projection data based on a size of previously stored projection rows; and

storing the rows of the updated projection data in grouped ROS format or in column format based on the estimated storage size.

19. The method of claim 13 in which the updated projection data is stored in grouped ROS format and wherein the updated projection data comprises one or more rows for a first plurality of the projection'"'"'s columns.

20. The method of claim 19 in which each row'"'"'s first columns are stored together in a single file with at least one other row'"'"'s first columns.

21. The method of claim 13 in which the updated projection data is stored in column format and wherein the updated projection data comprises one or more rows for one or more first columns of the projection.

22. The method of claim 21 in which each row'"'"'s first columns are stored in separate files and each separate file stores the same first column from more than one row.

23. The method of claim 13 in which updating further comprising storing rows of the projection data in grouped ROS format based on frequency of use of columns of the rows.

24. The method of claim 13 wherein updating is performed in response to processing a database operator to write data to storage.

25. A computer readable medium storing a projection of a table of a database system, the projection comprising columns of data of the table, the projection comprising:

first rows for at least two of the columns in grouped ROS format, each of the first row'"'"'s columns being stored in a single file with the columns of at least one other first row; and

distinct second rows for at least two of the columns in column format, each of the second row'"'"'s columns being stored in separate files and each separate file storing the same column of more than one of the second rows.

26. A computer-implemented method for associating at least two columns of a projection of a database system with respective storage locations, the method comprising:

ranking storage locations according to their respective speeds;

ranking the columns according to their preferred speeds; and

for each of the columns and according to their ranked order, assigning the column to the fastest storage location based on free space of the storage location and a size of the column.

27. The method of claim 26 wherein ranking the columns further comprises:

identifying one or more first columns in the columns that are in a sort order for the projection; and

assuming a faster preferred speed for the first columns than other columns when determining an order for the columns.

28. The method of claim 26 wherein if the columns are to be stored in grouped ROS format, the preferred speed for each of the columns is an average of all the columns'"'"' preferred speed.

29. The method of claim 26 wherein if the columns are to be stored in grouped ROS format, the preferred speed for each of the columns is a fastest preferred speed of the columns.

30. A computer-implemented method comprising:

determining that a plurality of new rows for a plurality of a projection'"'"'s columns are to be stored in persistent storage wherein the determining is based a criterion, and wherein the projection'"'"'s columns store data for a table in a database system;

selecting a storage format for the new rows wherein the storage format is grouped ROS format or column format and wherein grouped ROS format stores a plurality of the projection'"'"'s columns together in a single file and wherein column format stores columns of the projection in separate files; and

storing the new rows in the selected format in persistent storage.

31. The method of claim 30 wherein the criterion is an age of the new rows.

32. The method of claim 30 wherein the criterion is a total size of the new rows.

33. The method of claim 30 wherein selecting a storage format is based on an assessment of existing storage allocated to the table'"'"'s data such that some but not all of the table'"'"'s data is always stored in grouped ROS format.

34. A computer-implemented method comprising:

selecting a plurality of candidate projection containers wherein each candidate container holds rows for one or more columns of the projection;

identifying a plurality of containers to merge in the candidate containers;

selecting a storage format wherein the storage format is grouped ROS format or column format and wherein grouped ROS format stores a plurality of the projection columns together in a single file and wherein column format stores columns of the projection in separate files;

merging the identified containers into a new container according to the selected storage format; and

wherein selecting a storage format is based on an assessment of existing storage allocated to the projection such that some but not all of projection data is stored in grouped ROS format.

35. The method of claim 34 wherein the selected candidate containers are in a common size range.

36. The method of claim 34 wherein the identified containers are each associated with a start epoch that is smaller than the remaining candidate containers.

37. A computer-implemented method comprising:

determining that data is to be added to a projection of a database based on at least one of a size or an age of the data; and

storing the data in at least partly in grouped ROS format and partly in column format.