Author Archives: Stanislav_Panteleev

The Seek Method paginates by using a stable, indexed column value (like id or created_at) from the last row of the previous page, instead of counting and skipping rows like OFFSET does. 🧠 Core Idea Instead of saying: You say: … Continue reading →

Implementing pagination in SQL lets you retrieve a subset of rows at a time—useful for showing results page-by-page in apps (like social feeds, product listings, etc.). Here’s how to do it using different techniques: ✅ 1. OFFSET-FETCH / LIMIT (Simple … Continue reading →

Eventual consistency is a consistency model used in distributed systems and databases where: ⚠️ Not all nodes are immediately consistent, but if no new updates are made, all replicas will eventually become consistent over time. 🧠 Real-World Analogy Imagine you’re … Continue reading →

Sharding is a database scaling technique where you split a large database into smaller, faster, more manageable pieces called “shards.” Each shard contains a subset of the data, and all shards together make up the full dataset. 🧠 Why Use … Continue reading →

A materialized view is a database object that stores the result of a query physically on disk—like a snapshot of data. Unlike regular views (which are virtual and recompute data on access), materialized views cache the results, making read queries … Continue reading →

Detecting and resolving deadlocks involves monitoring, logging, and possibly retrying transactions. Here’s a detailed overview: 🔍 How to Detect Deadlocks ✅ 1. Automatic Detection by the DBMS Most modern databases detect deadlocks automatically and: Choose a “victim” transaction to rollback … Continue reading →

The answer depends on the type of SQL statements you run, your database engine, and the transaction isolation level. Here’s a clear breakdown: 🔐 When You Start a Transaction At this point, nothing is locked yet. ❗ Locks are acquired … Continue reading →

A deadlock in a database occurs when two or more transactions block each other, each waiting for a resource (e.g., a row or table) that the other transaction holds. As a result, none of the transactions can proceed—they’re stuck forever … Continue reading →

Designing a schema for a social network involves balancing core functionality, scalability, and query efficiency. Here’s a structured breakdown: 🧱 1. Core Entities and Relationships 👤 Users 👥 Friendships / Follows Depending on whether the network is mutual (like Facebook) … Continue reading →

A covering index is an index that contains all the columns a query needs—so the database can satisfy the query entirely from the index, without touching the underlying table (also known as avoiding a table (or heap) lookup). 🧠 Definition … Continue reading →