Welcome back to another edition of Level Up Coding’s newsletter.

In today’s issue:

• Understanding the Various Approaches to Effective Database Indexing

• How Does OAuth 2.0 Work? (recap)

• HTTP vs HTTPS (recap)

• Why You Need Fault Tolerance (recap)

Read time: 6 minutes

Understanding the Various Approaches to Effective Database Indexing

Most databases require some form of indexing to keep up with performance benchmarks. Searching through a database is much simpler when the data is correctly indexed, which improves the system's overall performance.

A database index is a lot like the index on the back of a book. It saves you time and energy by allowing you to easily find what you're looking for without having to flick through every page. Database indexes work the same way. An index is a key-value pair where the key is used to search for data instead of the corresponding indexed column(s), and the value is a pointer to the relevant row(s) in the table.

To get the most out of your database, you should use the right index type for the job.

One of the most commonly used indexing structures is the B-tree, where keys are sorted and organized in a hierarchical tree structure. When searching data, the tree is traversed down to the leaf node that contains the appropriate key and pointer to the relevant rows in the table. B-tree is most commonly used because of its efficiency in storing and searching through ordered data. Their balanced structure means that all keys can be accessed in the same number of steps, making performance consistent.

Hash indexes are best used when you are searching for an exact value match. The key component of a hash index is the hash function. When searching for a specific value, the search value is passed through a hash function which returns a hash value. That hash value tells the database where the key and pointers are located in the hash table.

For indexing columns with a low set of unique values, bitmap indexing can be used. With bitmap indexing, each bitmap represents a unique value. A bitmap indicates the presence or absence of a value in a dataset, using 1’s and 0’s. For existing values, the position of the 1 in the bitmap shows the location of the row in the table. Bitmap indexes are very effective in handling complex queries where multiple columns are used.

When you are indexing a table, make sure to carefully select the columns to be indexed based on the most frequently used columns in WHERE clauses. A composite index may be used when multiple columns are often used in a WHERE clause together. With a composite index, a combination of two or more columns are used to create a concatenated key. The keys are then stored based on the index strategy, such as the options mentioned above.

Indexing can be a double-edged sword. It significantly speeds up queries, but it also takes up storage space and adds overhead to operations. Balancing performance and optimal storage is crucial to get the most out of your database without introducing inefficiencies.

How Does OAuth 2.0 Work? (recap)

OAuth 2.0 is an authorization framework that enables applications to access a user’s data on another service (like Facebook or GitHub) without sharing the user’s password. It’s essentially a digital handshake between the app, service, and user, with everyone agreeing on what is shared.

The OAuth authorization code flow process generally follows 6 steps with 4 components typically involved.

Components:

• Client (app wanting access)

• Resource owner (typically the user)

• Authorization server

• Resource server

Steps:

1. Authorization request

2. Redirect to external login page

3. User login and consent

4. Authorization grant

5. Exchange authorization code for access token

6. Use the token to access protected resources

For a visual illustrating the flow, as well as an detailed explanation of the process, check out the full post here 

HTTP vs HTTPS (recap)

• HTTP → Hypertext Transfer Protocol.

• HTTPS → Hypertext Transfer Protocol Secure.

• The primary difference between these two protocols is security.

• HTTP is not secure. Data exchanged between your browser & the site you're visiting is in plain text (unencrypted). If someone intercepts this transmission, they can read and manipulate the data.

• HTTPS is a secure protocol. Information transferred is encrypted using TLS (Transport Layer Security) or SSL (Secure Sockets Layer) protocols, providing privacy and integrity of information.

For an extended comparison of these two protocols, as well as a visual, see the full post here

Why You Need Fault Tolerance (recap)

Fault tolerance ensures that a system continues to work even when errors and failures happen. Adding fault tolerance to mission-critical components will boost the reliability, availability, and reputation of your system.

You can add fault tolerance into your system by doing the following:

🔸 Redundancy: Make duplicates of critical components so that if one component fails, you have a second ready to take its place.
🔸 Error detection and correction: Add automated monitoring, alerts and self-correcting mechanisms to minimise the need for immediate human intervention.
🔸 Fault isolation: Isolate failing components to ensure the rest of the system is not impacted.
🔸 Load balancing: Minimize the impact of a single failing node by distributing the workload across multiple servers.

For a visual depiction of fault tolerance, see here

That wraps up this week’s issue of Level Up Coding’s newsletter!

Join us again next week where we’ll explore Linux permissions, adaptive streaming algorithms, and components of a URL.