Author Archives: Stanislav_Panteleev

Eventual consistency means that if no new updates are made, all replicas will eventually converge to the same state, but reads may temporarily see stale data. Key phrase to remember: Consistency is delayed, not lost. Why eventual consistency exists Strong … Continue reading →

CDC (Change Data Capture) is a technique for capturing changes in a database (INSERT, UPDATE, DELETE) and streaming them to other systems without querying tables directly. In simple words: Instead of asking the DB “what changed?”, CDC listens to the … Continue reading →

Recommended architecture for “income/outcome ops + big reports” 1) OLTP is the source of truth (writes) Your service consumes Kafka events (income/outcome) and writes them to an OLTP database: Postgres (or similar) Proper constraints, FK, idempotency keys, transactions Optimized for … Continue reading →

Short, interview-ready definition OLTP systems are optimized for many small, fast transactions (reads/writes).OLAP systems are optimized for complex analytical queries over large datasets. 1️⃣ OLTP — Online Transaction Processing What it is Systems that support day-to-day operations of an application. … Continue reading →

1️⃣ Full Scan (a.k.a. Sequential Scan) Definition A full scan reads the entire table from start to end, checking every row. In PostgreSQL it’s called: Example If: No index on status, or Optimizer thinks many rows match Plan: Characteristics ✅ … Continue reading →

What the optimizer considers 1) Sizes and selectivity If orders is huge and users is smaller → hash join is often cheapest (build hash on users, probe with orders). If orders is filtered down to a few rows (e.g., WHERE … Continue reading →

1️⃣ Equality joins (= joins) Example 1: classic PK → FK join Join condition: = Can use: Nested Loop + index Hash Join Merge Join Most common join type in OLTP Example 2: joining by business key Again: Pure equality … Continue reading →

Short definition (interview-ready) Join algorithms are the physical strategies a database engine uses to execute a JOIN efficiently, depending on data size, indexes, memory, and join condition. Logical JOIN (INNER / LEFT / …) ≠ how it is executed. The … Continue reading →

This is a classic SQL trap and a favorite interview question. Let’s break it down cleanly and precisely. Short answer (what interviewers want to hear) IN breaks when the subquery contains NULL because SQL uses three-valued logic.Any comparison with NULL … Continue reading →

1️⃣ What the relational model is (Codd) The relational model describes data as: Relations (tables) → unordered sets of tuples (rows) Attributes → typed columns Primary keys → tuple identity Foreign keys → integrity between relations Declarative operations (relational algebra … Continue reading →