Category Archives: Без рубрики

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 →

What the relational model guarantees The relational model defines what states of data are valid. These guarantees are logical, not performance-related. 1️⃣ Set-based data (no duplicates by default) Relations are sets, not lists Rows have no inherent order Duplicates are … Continue reading →