Storage Management

#9 What is the difference between columnar and row-based databases?

Row-based databases store the data on disk row by row, whereas, columnar databases store the data column by column. Each method has its own advantages. The former is very fast and efficient for the operations on rows and the latter is fast and efficient for the operations on columns, e.g. aggregating large volumes of data for a subset of columns.

Typically the operations that need the whole row are writing operations like INSERT , DELETE , UPDATE . The operations that need columns are typically read operations like SELECT , GROUP BY , JOIN , etc.

In general, columnar database are ideal for analytical operations and row databases are ideal for transaction processing.

#10 What are OLTP and OLAP and their differences?

OLTP and OLAP are both online processing systems. OLTP stands for “Online Transaction Processing” and it is a system that manages transaction-oriented applications, and OLAP stands for “Online Analytical Processing”, and it is a system to manage analytical queries.

The major difference between the two systems is that OLTP is a write-heavy system and OLAP is a read-heavy system. This difference has a major impact on their implementation. For example, it is very important for OLTP systems to adopt a proper concurrency control, while this is not a major concern in read-heavy operations. Another difference between the two systems is that OLTP queries are generally simple and return relatively small number of records while OLAP queries are very complex and involve many intricate joins and aggregations.

The other difference is that due to the real-time nature of OLTP systems, they often follow a decentralized architecture to avoid single points of failure, while OLAP systems often have centralized architecture.

Also, in the majority of DBMSs, OLTP is row-based database and OLAP is columnar database.

#11 What is normalization and de-normalization?

Normalization is a process that organizes the data into multiple tables to minimize redundancy. De-normalization is the opposite process. It combines the normalized tables into one table so that data retrieval becomes faster. The main advantage of normalization is the better use of disk spaces. It is also easier to maintain the integrity of the database when it is normalized.

JOIN is the operation that allows us to reverse the normalization and create a de-normalized form of the data.

#12 What is Data Warehousing?

It is a process of collecting (extracting, transforming, and loading) data from heterogeneous sources and storing them into one database. You can consider the data warehouse as a central repository where data flows into it from the transactional systems and other relational databases. It can correlate broad business data to provide greater executive insight into an organization performance. The data warehouse is the core of the business intelligence, which is a system for data analysis and reporting.

This database is maintained separately from standard operational databases. They are two separate systems, the latter are optimized to update real-time data quickly and accurately, while the former is mostly suitable for offline operations to give a long-range view of data over time.

Data Warehousing

Concurrency control

Photo by Chris Sabor on Unsplash

Concurrency control is the procedure in DBMS that ensure simultaneous operations do not conflict with each another.

#13 What are database locks and its types?

In general, it is fair to say that locks are mostly used to ensure that only one user/session is allowed to update a particular data. Here I describe two types of locks: shared lock (S) and exclusive lock (X). These locks can be held on a table, a page, an index key, or an individual row.

Shared lock: When an operation requests a shared lock on a table, if granted, that table becomes open to reading. This lock can be shared with other read operations and they can read the table at the same time.

Exclusive lock: When an operation requests an exclusive lock on a table, if granted, has an exclusive right to write on the table. Other operations, if they request an access on that locked table, will be blocked.

Lock compatibility matrix

There is another related concept for locks, called Intent (I) locks. We have Intent Shared (IS) and Intent Exclusive (IX) locks. These locks allow more granular concurrency control. Technically, we do not need them. S and X locks are enough, but they are helpful for query optimization. More details about Intent locks are typically beyond the scope of even advanced questions.

#14 What is “lock escalation”?

Database locks can exist on rows, pages or whole tables or indexes. When a transaction is in progress, the locks held by the transaction take up resources. Lock escalation is where the system consolidates multiple locks into a higher level one (for example consolidating multiple row locks to a page or multiple pages to a whole table) typically to recover resources taken up by large numbers of fine-grained locks.

#15 What is “lock contention”?

When multiple operations request an exclusive lock on one table, lock contention occurs. In this scenario, operations must wait in a queue. If you run into chronic lock contention, it means that some parts of your database is hot, you must divide those data blocks further to allow more operations to obtain exclusive lock at the same time. lock contention can be a bottleneck for scaling up a database.

#16 What is “deadlock”?

A deadlock is a situation that some transactions are waiting indefinitely for each other to give up locks. Typically, there is two approach to address this issue, one is the lazy way, which means do nothing, and if it happened then detect it and restart one operation to disentangle the deadlock. The other approach is proactive, which means preventing deadlocks to ever happen. If you want to learn more about deadlock prevention, read it here.

#17 What are isolation levels?

Isolation is the third letter in ACID properties. With this property, our goal is to make all transactions completely isolated from each other (serializable). However, there are some applications that they do not need full isolation. As a result, we define some other isolation levels that are less stringent than full isolation. In general, five isolation levels are defined.

Read Uncommitted: No lock at all. Concurrent transactions can read uncommitted data of other transactions and can also write on them. In the database vernacular, they say DBMS allows dirty reads.

Read Committed: In this level, DBMS does not allow dirty reads. In this level, each transaction holds a read/write lock on the current row and only release when it commits the changes. This isolation level still allows non-repeatable read, which means a transaction return different value when it reads the same row. It is more clear if you take a look at below picture. As it is clear, no dirty read is allowed, but non-repeatable reads still exist.

An example of Read Committed isolation level

Repeatable Read: As you saw earlier, the problem with “read committed” isolation level was “non-repeatable reads”. To avoid non-repeatable reads, each transaction must hold a read lock on the rows they read and write lock on the rows they write (e.g. insert, update, and delete) until they commit the changes. This level of isolation is repeatable read.

However, there is still one scenario in this isolation level, which can make the database inconsistent. If we insert or delete new rows to a table and then range query, then the results would be inconsistent. Look at below example. The same query in transaction 1 returns two different results. This scenario is known as ‘phantom read’.

Serializable: This is the highest isolation level. As you saw in “repeatable read”, phantom read can happen. In order to prevent the phantom read, we must hold the lock on the entire table and not the rows. Below picture is summary of all isolation levels so far.

Snapshot: This isolation level is different from the others I have described so far. The others were based on locks and blocks. This one does not use locks. In this isolation level, when a transaction modifies (i.e. insert, update, and delete) a row, the committed version of the modified row will be copied to a temporary database (tempdb) and receives a version number. It is also known as row versioning. Then if another session tries to read the modified object, the committed version of that object will be returned from tempdb to that operation.

If what I described for snapshot isolation sounds fundamentally different from other isolation levels, it is because it really is. Other isolation levels are based on a pessimistic concurrency control model, but snapshot isolation is based an optimistic model. Optimistic model assumes conflicts are rare and decides not to prevent them and handle them if they occur. It is different from pessimistic model, which ensures that no conflict happens whatsoever.

Access methods

Photo by Markus Spiske on Unsplash

Access methods are organization techniques or data structures that support fast access to subsets of rows/columns. Some of the most common data structures are variants of hash tables and B-trees.

#18 What is hashing and its advantages and disadvantages?

Hashing is a look up technique. Basically, a way to map keys to values. Hash functions convert a string of characters into a usually shorter fixed-length values, which can then be used as an index to store the original element.

Hashing, if we use a good hash function, can be used to index and retrieve items in a database in a constant time, which is faster than the other look up techniques.

Advantages:

Hash table is an ideal data structure for point look up (a.k.a equality queries), especially when the database is large, because regardless of the input size, you can search, insert and delete data in constant time.

Disadvantages:

There are situations that hashing is not necessarily the best option. For example, for small data, the cost of a good hash function makes hashing more expensive than a simple sequential search.

Another situation is range scan operation (a.k.a range query), for this operation, B+ tree is an ideal data structure.

The other situation is looking for a sub-string or prefix matching, hashing is basically useless for these operations.

Another disadvantage of hashing is scalability. The performance of hash table degrades as the database grows (more collisions and higher cost of collision resolution, e.g. adding more buckets or rehashing the existing items)

#19 What is B+ tree and its advantages and disadvantages?

B+ tree is a data structure from the family of B trees. This data structure and its variants is very popular for indexing purpose. This tree is a self-balancing tree and there are mechanisms in place to ensure that the nodes are at least half full. In B+ tree, the data is stored in leaf nodes, and leaf nodes are sequentially linked to each other. These sequential links between leaf nodes allow sequential access to data without traversing the tree structure. This sequential access allows fast and efficient range scan.

B+ tree allows searches, sequential access, insertions, and deletions in logarithmic time. At the end of this answer, you can find a B+ tree sample visualization. If you are interested, you can plot one for yourself by this visualization tool (here).

Typically, in database systems, B+ tree data structure is compared against hash table. So, here I try to explain advantages and disadvantages of B+ tree compared to hash table. Advantages of B+ tree is in range query and also searching for substrings, using LIKE command. On the other hand, for equivalence query, the hash index is better than B+ tree. Another advantage of B+ tree is that it can easily grow with the data, and as a result, it is more suitable for storing large amount of data on disk.

One common follow up question is that what is the difference between B+ tree and Binary Search Tree (BST)? B+ tree is a generalization of BST, which allows tree nodes to have more than two children.

If someone asked you about the difference of B tree and B+ tree, you can mention two things. First, in B+ three, the records are only stored in leaves, and the internal nodes store the pointers (key). Unlike B+ tree, in B tree, keys and records both can be stored in the internal and leaf nodes. Second, the leaf nodes of a B+ tree are linked together, but in B tree, they are not linked. You can see these two differences in the below example.

B tree visualization

B+ tree visualization

#20 What is the difference between clustered and non-clustered indexes?

Indexes are used to speed-up query process. Without them, a DBMS needs to perform full table scan, which is very slow.

Clustered index is related to physical storage of data. Basically, we ask our DBMS to sort our rows by a column and physically store them with that order. As you can see, we only can have one clustered index per table. Clustered index allows us to retrieve our data very fast because it provides fast sequential scan. You can either create a customized clustered index or let the DBMS to automatically create one for you using the primary key.

In contrast, non-clustered index is not related to physical storage. These indexes are sorted based on a column and stored somewhere different from the table. You can imagine these indexes as a lookup table with two columns. One column is a sorted form of one of the table columns, the other is the their physical address on memory (row address). For these indexes, if we look for a record, we first go and search its index on the look up table, then we go to the physical memory to fetch all the records associated with that row address.

In summary, non-clustered indexes are slower than clustered indexes because they involve extra lookup step. Moreover, since we need to store these lookup tables, we need extra storage space. The other difference is that we can have one clustered index per table while we can have as many non-clustered index as we want.

operator execution

Photo by Sebastián León Prado on Unsplash

#21 What are correlated and non-correlated sub-queries?

In terms of inter-dependency, there are two types of sub-queries, in one of them, the inner query is dependent to the value of the outer query, we call this kind of queries “correlated” queries and in the other one, inner and outer queries are independent, which we call them “non-correlated” queries.

It is needless to mention that correlated sub-queries are very slow because it requires the inner sub-query to run once for every row in the outer query.

#22 What are different JOIN algorithms?

There are three major algorithms to perform JOIN. Here I try to describe them briefly and mention their advantages.

Nested Loop: It compares all values of outer and inner tables against each other. It is the only algorithm that is capable of cross join (many-to-many joins). It serves as a fallback option in the absence of better algorithms.

Hash Join: This is the most versatile join method. In a nutshell, it builds an in-memory hash table of the smaller of its two inputs, and then reads the larger input and probes the in-memory hash table to find matches. Hash joins can only be used to compute equi-joins. It is typically more efficient than nested loops, except when the probe side of the join is very small.

Sort-Merge Join: This algorithm, first, sorts two tables based on the join attributes. Then it finds the first match and scrolls down on two tables and merge the rows for the matching attributes.

#23 What is stored procedure?

You can consider it as a semi-program. A set of SQL statements to perform a specific task. If this task is a common task, then instead of running the query each time, we can store that query into a procedure and execute it when we need it. Below is a simple structure of a procedure.

CREATE PROCEDURE <Procedure-Name> AS

Begin

<SQL STATEMENT>

End

After creating a procedure, whenever we need, we can execute it using EXECUTE command

EXECUTE <Procedure-Name>

Stored procedure has many benefits. The major one is reusability of the SQL code. If the procedure is in common use, it helps to avoid writing the code multiple times. Another benefit is when we use distributed databases, it reduces the amount of information sent over the network.

#24 What is a database trigger?

A trigger is a stored procedure, which automatically runs before/after an event occurs. These events can be DML, DDL, DCL, or database operations such as LOGON/LOGOFF. The general trigger syntax is as below:

CREATE [ OR ALTER ] TRIGGER [ Trigger_name ]

[BEFORE | AFTER | INSTEAD OF]

{[ INSERT ] | [ UPDATE ] | [ DELETE ]}

ON [table_name]

AS

{SQL Statement}

Some applications of triggers are: checking the validity of transaction, enforcing referential integrity, event logging, generating some derived columns automatically, and security authorizations before/after user LOGON.

If you are asked about the difference of trigger and stored procedure, you can mention that triggers can not be called by their own. They are invoked during events. In contrast, a stored procedure is an independent query and can be called independently.

Query planning and optimization

Photo by Alvaro Reyes on Unsplash

Query planning and optimization is a feature of many RDBMSs. The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans.

#25 What is an execution plan?

An execution plan is the description of the operations that the database engine plans to perform to run the query efficiently. You can look at it as a view into your DBMS query optimizer, which basically is a software to find the most efficient way to implement a query. Execution plan is a primary means of troubleshooting a poorly performing query.

Reading an execution plan, understanding it and troubleshooting based on the plan is an art. So, if If you want to learn more about it, a great reference is here.

#26 What is query optimization?

SQL is a declarative language and not procedural. Basically you tell the DBMS what you want, but you don’t say how to get those results. It is up to DBMS to figure it out. DBMS can adopt multiple query strategies to get the correct results. However, these execution plans incur different costs. It is the query optimizer’s job to compare these strategies and pick the one with the least expected cost. The cost in this context is a weighted combination of I/O and processing costs. The I/O cost is the cost of accessing index and data pages from disk.

#27 Mention a few best practices to improve query performance?

It is a general question, and in practice there are many ways to improve query performance. Here only I mention a few.

Avoid multiple joins in a single query

Use joins instead of sub-queries.

Use stored procedure for frequently used data and more complex queries.

Use WHERE expressions to limit the size of your results as much as possible.

Here is another good read to answer this question.

Crash Recovery

Photo by Romson Preechawit on Unsplash

Crash recovery is the process to roll back to a consistent and usable state. This is done by undoing incomplete transactions and redoing committed transactions that were still in memory when the crash occurred

#28 What is Write-Ahead Log (WAL)?

In DBMSs, the de facto technique for recovery management and maintaining Atomicity and Durability is using Write-Ahead Log (WAL). With WAL, all changes are written to a log first, then the log itself must be written to a stable storage before the changes are allowed to be physically applied. That is why it is called Write-Ahead log. A simple technique that guarantees that when we come back from the crash, we still can figure out what we were doing before the crash and pick up from where we left off.

#29 What is a checkpoint?

checkpoints are relevant to log-based recovery system. For restoring a database after crash, we must redo all the log records. But if we redo all the log records from the beginning, it takes forever to recover a database. So, we must ignore some of the records after a while. Checkpoints are the points where we decide to ignore records before them for the recovery purpose. As you can see, by using checkpoints, the DBMS can reduce the amount of work to restart a database in the event of a crash.