Essential techniques for data consistency — In the world of distributed systems, ensuring data consistency is crucial, especially when multiple transactions are trying to mutate the same record simultaneously. That’s where locking mechanisms come in — they help prevent race conditions and keep your data in check. In this article, I’ll be your guide through the…

Key reasons why we need isolation in SQL — If you have worked with SQL databases, you’re likely familiar with the ACID model — Atomicity, Consistency, Isolation, and Durability. Isolation is a key aspect of this model. It ensures that each transaction occurs in a distinct order without overlapping with one another. Without proper isolation, transactions can overlap, leading…

Demystify using SQL Explain Analyze — When it comes to pagination, two of the most commonly used algorithms are offset limit and cursor pagination. One of the significant differences between them is that offset pagination can be much slower when dealing with large offset. Why is there such a significant difference in performance, given that both…

Clustered vs non-clustered indexes — For most developers, database indexing is a powerful tool when it comes to database tuning. Despite its importance, many developers struggle to comprehend the internal mechanics of indexing. If you’ve ever found yourself in such position, you’re in luck. In this article, I will walk you through the fundamental concepts…

What are the differences and why should you know? — In database management, there are two fundamental approaches for storing data: row-based and column-based database. Each approach carries its own strengths and weaknesses and is suited for different use cases. In this article, we will learn the key differences between row and column-oriented databases so that you can choose the…

A brief introduction to SQL data page — Starting out in development, SQL database can often feel like a mysterious black box that magically stores and retrieves data. However, have you ever wondered how your data is stored internally? That’s where the concept of a data page comes in. Data page is the most fundamental unit of storage…

Cache penetration, cache breakdown and cache avalanche — While caching is known to be a performance booster for every software system, it’s also prone to bugs if not handled with care. In this article, I will walk you through 3 common caching problems that could be disastrous at times so you don’t need to learn them the hard…

When and how to update your cache — Caching in an integral part of every software system. When done right, it can yield a significant performance boost in your application. However, knowing when and how to update your cache is a critical design consideration and it’s often tested in a system design interview. In this article, I will…

Design choices that have stood the test of time — As an in-memory data store, Redis is known for its speed and performance and is often leveraged as a caching solution for most backend services. However, internally, Redis employs a single-threaded architecture. Why would a single-threaded design lead to high performance? …