The VLDB Journal

, Volume 23, Issue 2, pp 303–328 | Cite as

Transactional support for adaptive indexing

  • Goetz Graefe
  • Felix Halim
  • Stratos Idreos
  • Harumi KunoEmail author
  • Stefan Manegold
  • Bernhard Seeger
Special Issue Paper


Adaptive indexing initializes and optimizes indexes incrementally, as a side effect of query processing. The goal is to achieve the benefits of indexes while hiding or minimizing the costs of index creation. However, index-optimizing side effects seem to turn read-only queries into update transactions that might, for example, create lock contention. This paper studies concurrency control and recovery in the context of adaptive indexing. We show that the design and implementation of adaptive indexing rigorously separates index structures from index contents; this relaxes constraints and requirements during adaptive indexing compared to those of traditional index updates. Our design adapts to the fact that an adaptive index is refined continuously and exploits any concurrency opportunities in a dynamic way. A detailed experimental analysis demonstrates that (a) adaptive indexing maintains its adaptive properties even when running concurrent queries, (b) adaptive indexing can exploit the opportunity for parallelism due to concurrent queries, (c) the number of concurrency conflicts and any concurrency administration overheads follow an adaptive behavior, decreasing as the workload evolves and adapting to the workload needs.


Databases Indexes Adaptive indexing Concurrency control Robust query processing Database cracking Adaptive merging Single-page failure 


  1. 1.
    Bayer, R., Schkolnick, M.: Concurrency of operations on B-trees. Acta Inf. 9, 1–21 (1977)CrossRefzbMATHMathSciNetGoogle Scholar
  2. 2.
    Bayer, R., Unterauer, K.: Prefix b-trees. ACM TODS 2(1), 11–26 (1977)CrossRefGoogle Scholar
  3. 3.
    Bruno, N., Chaudhuri, S.: An online approach to physical design tuning. In ICDE, pp. 826–835 (2007)Google Scholar
  4. 4.
    Bruno, N., Chaudhuri, S.: Physical design refinement: The ‘merge-reduce’ approach. ACM TODS 32(4), 1–41 (2007)CrossRefGoogle Scholar
  5. 5.
    Chaudhuri, S. Narasayya, V.R.: Self-tuning database systems: A decade of progress. In VLDB, pp. 3–14 (2007)Google Scholar
  6. 6.
    Finkelstein, S.J., Schkolnick, M., Tiberio, P.: Physical database design for relational databases. ACM TODS 13(1), 91–128 (1988)CrossRefGoogle Scholar
  7. 7.
    Graefe, G.: Sorting and indexing with partitioned B-trees. In CIDR (2003)Google Scholar
  8. 8.
    Graefe, G.: Hierarchical locking in b-tree indexes. In BTW, pp. 18–42 (2007)Google Scholar
  9. 9.
    Graefe, G.: A survey of B-tree locking techniques. ACM TODS 35(2), 16:1–16:26 (2010)Google Scholar
  10. 10.
    Graefe, G.: Modern B-tree techniques. Found Trends Databases 3(4), 203–402 (2011)CrossRefGoogle Scholar
  11. 11.
    Graefe, G.: A survey of B-tree logging and recovery techniques. ACM TODS 37(1), 1:1–1:35 (2012)CrossRefGoogle Scholar
  12. 12.
    Graefe, G., Idreos, S., Kuno, H., Manegold, S.: Benchmarking adaptive indexing. In TPCTC, pp. 169–184 (2010)Google Scholar
  13. 13.
    Graefe, G., Kimura, H., Kuno, H.: Foster b-trees. ACM Trans. Database Syst. (TODS) 37(3), 17 (2012)CrossRefGoogle Scholar
  14. 14.
    Graefe, G., Kuno, H.: Adaptive indexing for relational keys. In SMDB, pp. 69–74 (2010)Google Scholar
  15. 15.
    Graefe, G., Kuno, H.: Fast loads and queries. Trans. Large Scale Data Knowl. Cent. Syst. 4, 31–72 (2010)CrossRefGoogle Scholar
  16. 16.
    Graefe, G., Kuno, H.: Self-selecting, self-tuning, incrementally optimized indexes. In EDBT, pp. 371–381 (2010)Google Scholar
  17. 17.
    Graefe, G., Kuno, H.: Definition, detection, and recovery of single-page failures, a fourth class of database failures. PVLDB 5(7), 646–655 (2012)Google Scholar
  18. 18.
    Graefe, G., Seeger, B.: Logical recovery from single-page failures. In BTW, pp. 113–132 (2013)Google Scholar
  19. 19.
    Gray, J., Reuter, A.: Transaction Processing: Concepts and Techniques. Morgan Kaufmann, Los Altos, CA (1993)zbMATHGoogle Scholar
  20. 20.
    Halim, F., Idreos, S., Karras, P., Yap, R.H.C.: Stochastic database cracking: Towards robust adaptive indexing in main-memory column-stores. PVLDB 5(6), 502–513 (2012)Google Scholar
  21. 21.
    Härder, T.: Selecting an optimal set of secondary indices. In ECI, pp. 146–160 (1976)Google Scholar
  22. 22.
    Härder, T., Reuter, A.: Principles of transaction-oriented database recovery. ACM Comput. Surv. 15(4), 287–317 (1983)CrossRefGoogle Scholar
  23. 23.
    Harris, T., Larus, J.R., Rajwar, R.: Transactional Memory, 2nd edition. Synthesis Lectures on Computer Architecture. Morgan & Claypool Publishers (2010)Google Scholar
  24. 24.
    Hoshino, T., Goda, K., Kitsuregawa, M.: Online monitoring and visualisation of database structural deterioration. In IJAC, pp. 297–323 (2010)Google Scholar
  25. 25.
    Idreos, S.: Database cracking: Towards auto-tuning database kernels. CWI, PhD Thesis (2010) Google Scholar
  26. 26.
    Idreos, S., Kersten, M.L., Manegold, S.: Database cracking. In CIDR, pp. 68–78 (2007)Google Scholar
  27. 27.
    Idreos, S., Kersten, M.L., Manegold, S.: Updating a cracked database. In SIGMOD, pp. 413–424 (2007)Google Scholar
  28. 28.
    Idreos, S., Kersten, M.L., Manegold, S.: Self-organizing tuple reconstruction in column stores. In SIGMOD, pp. 297–308 (2009)Google Scholar
  29. 29.
    Idreos, S., Manegold, S., Kuno, H., Graefe, G.: Merging what’s cracked, cracking what’s merged: Adaptive indexing in main-memory column-stores. PVLDB 4(9), 585–597 (2011)Google Scholar
  30. 30.
    Kersten, M.L., Manegold, S.: Cracking the database store. In CIDR, pp. 213–224 (2005)Google Scholar
  31. 31.
    Lehman, P.L., Yao, S.B.: Efficient locking for concurrent operations on B-trees. ACM Trans. Database Syst. 6, 650–670 (1981)CrossRefzbMATHGoogle Scholar
  32. 32.
    Lühring, M., Sattler, K.-U., Schmidt, K., Schallehn, E.: Autonomous management of soft indexes. In SMDB, pp. 450–458 (2007)Google Scholar
  33. 33.
    Mohan, C., Haderle, D.J., Lindsay, B.G., Pirahesh, H., Schwarz, P.M.: ARIES: A transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging. ACM TODS 17(1), 94–162 (1992)CrossRefGoogle Scholar
  34. 34.
    Mohan, C., Narang, I.: Algorithms for creating indexes for very large tables without quiescing updates. In SIGMOD Conference, pp. 361–370 (1992)Google Scholar
  35. 35.
    Praveen Seshadri, A.N.S.: Generalized partial indexes. In ICDE, pp. 420–427 (1995)Google Scholar
  36. 36.
    Saracco, C.M., Bontempo, C.J.: Getting a lock on integrity and concurrency. Database Program. Des. (1997)Google Scholar
  37. 37.
    Schnaitter, K., Abiteboul, S., Milo, T., Polyzotis, N.: COLT: Continuous on-line tuning. In SIGMOD, pp. 793–795 (2006)Google Scholar
  38. 38.
    Severance, D.G., Lohman, G.M.: Differential files: Their application to the maintenance of large databases. ACM TODS 1(3), 256–267 (1976)CrossRefGoogle Scholar
  39. 39.
    Srinivasan, V., Carey, M.: Performance of on-line index construction algorithms. In EDBT, pp. 293–309 (1992)Google Scholar
  40. 40.
    Stonebraker, M.: The case for partial indexes. SIGMOD Record 18(4), 4–11 (1989)CrossRefGoogle Scholar
  41. 41.
    Weikum, G.: Principles and realization strategies of multilevel transaction management. ACM Trans. Database Syst. 16(1), 132–180 (1991)CrossRefGoogle Scholar
  42. 42.
    Weikum, G., Schek, H.-J.: Concepts and applications of multilevel transactions and open nested transactions. In Database Transaction Models for Advanced Applications, pp. 515–553. Morgan Kaufmann, Los Altos, CA (1992)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2014

Authors and Affiliations

  • Goetz Graefe
    • 1
  • Felix Halim
    • 2
  • Stratos Idreos
    • 3
  • Harumi Kuno
    • 1
    Email author
  • Stefan Manegold
    • 4
  • Bernhard Seeger
    • 5
  1. 1.HP LabsPalo AltoUSA
  2. 2.GoogleMountain ViewUSA
  3. 3.Harvard UniversityCambridgeUSA
  4. 4.CWIAmsterdamThe Netherlands
  5. 5.University of MarburgMarburgGermany

Personalised recommendations