In this topic we tried to get to the bottom of how databases perform storage and retrieval. What happens when you store data in a database, and what does the database do when you query for the data again later?
“Operational Versus Analytical Systems” introduced the distinction between transaction processing (OLTP) and analytics (OLAP). In this topic we saw that storage engines optimized for OLTP look very different from those optimized for analytics:
-
OLTP systems are optimized for a high volume of requests, each of which reads and writes a small number of records and needs fast responses. The records are typically accessed via a primary key or a secondary index, and these indexes are typically ordered mappings from key to record, which also support range queries.
-
Data warehouses and similar analytical systems are optimized for complex read queries that scan over a large number of records. They generally use a column-oriented storage layout with compression that minimizes the amount of data that such a query needs to read off disk, and JIT compilation of queries or vectorization to minimize the amount of CPU time spent processing the data.
On the OLTP side, we saw storage engines from two main schools of thought:
-
The log-structured approach, which permits appending to files and deleting obsolete files but never updates a file that has been written. In general, log-structured storage engines tend to provide high write throughput. SSTables, LSM-trees, RocksDB, Cassandra, HBase, ScyllaDB, Lucene, and others belong to this group.
-
The update-in-place approach, which treats the disk as a set of fixed-size pages that can be overwritten. B-trees, the most common example of this philosophy, are used in all major relational OLTP databases and many nonrelational ones. As a rule of thumb, B-trees tend to be better for reads, providing higher read throughput and lower response times than log-structured storage.
We then looked at indexes that can search for multiple conditions at the same time: multidimensional indexes such as R-trees that can search for points on a map by latitude and longitude at the same time, and full-text search indexes that can search for multiple keywords appearing in the same text. Finally, we saw that vector databases are used for semantic search on text documents and other media; they use vectors with a larger number of dimensions and find similar documents by comparing vector similarity.
As an application developer, being armed with this knowledge about the internals of storage engines puts you in a much better position to know which tool is best suited for your particular application. If you need to adjust a database’s tuning parameters, this understanding allows you to imagine what effect a higher or a lower value may have.
Although this topic couldn’t make you an expert in tuning any one particular storage engine, it has hopefully equipped you with enough vocabulary and ideas that you can make sense of the documentation for the database of your choice.