3.2.4: Advanced SQL Concepts

Learning Objectives

1. SQL indexes can increase lookup speed on specific columns in SQL databases by creating a hash-table-like data structure referencing those columns

2. SQL transactions allow us to run either all or none of the queries in a specified series of queries, helping to prevent database corruption

Introduction

We will likely not need to use SQL transactions and indexes during Rocket's Bootcamp, but these concepts are commonly used in industry especially for larger and more sensitive data. These concepts have appeared often enough in software engineering interviews that we thought it would be relevant to mention them.

SQL Indexes

SQL indexes can increase lookup speed on specific columns in SQL databases by creating and maintaining a hash-table-like data structure referencing data in those columns. Without SQL indexes, SQL databases often resort to linear search to find the data they need in specific columns. Column data is typically not sorted. As we have learned from algorithms, for large data sets, an O(1) lookup speed with a hash table will be significantly faster than an O(n) lookup speed with an unsorted array.

Engineers building apps with large SQL databases that query specific columns often may wish to add indexes on those columns. Sequelize docs provide examples and API references for adding indexes on specific columns.

Data analysts repeatedly querying specific columns in large SQL databases outside of an app context may wish to add indexes on those columns. Postgres docs explain the motivation and execution of SQL indexes in Postgres databases with raw SQL queries. Creating indexes with raw SQL is helpful for data analysts

The drawback of SQL indexes is that they take up space. This is why we do not create SQL indexes on every column by default.

SQL Transactions

SQL transactions allow us to run either all or none of the SQL queries in a specified series of queries. This prevents database corruption where one or more queries may have succeeded, but one or more may have failed afterward.

For example, banking apps typically use transactions to perform money transfers to prevent situations where money is removed from the source account but not added to the destination account. By putting all transfer operations in a transaction, removing from source and adding to destination either both happen or not at all.

We will not need to use transactions at Rocket. Our Rocket projects likely involve small amounts of non-sensitive data, hence transactions are less important for now. But when we start work with large amounts of live user data, especially financial or healthcare data, data integrity becomes paramount and SQL transactions are a crucial tool.

Sequelize provides a transactions tutorial to learn SQL transactions with Sequelize. Managed transactions should work for most cases, and in cases where we may want more fine-grained control we can use unmanaged transactions. Postgres also explains how to use transactions in raw SQL, although we will almost never be updating our databases with raw SQL except in exceptional situations.