Author Archives: Stanislav_Panteleev

Surrogate keys are artificial, system-generated keys used to uniquely identify rows in a database table. They are not derived from application data and have no business meaning — typically auto-increment integers or UUIDs. 🧠 Definition A surrogate key is a … Continue reading →

OLTP and OLAP are two fundamental types of database systems, each optimized for different workloads and purposes. 🧾 Definitions Term Stands For Purpose OLTP Online Transaction Processing Fast, real-time operations (e.g. purchases, signups) OLAP Online Analytical Processing Complex analysis, reports, … Continue reading →

Partitioning and sharding are both techniques for splitting large datasets into smaller parts — but they solve different problems and work at different levels. Here’s a clear breakdown: ⚖️ Partitioning vs Sharding: Core Differences Feature Partitioning Sharding 📍 Level Within … Continue reading →

🔍 In DataGrip (or pgAdmin, MySQL Workbench, etc.) When you’re using partitioning, you typically see: The main table (logical parent) Its child tables (actual partitions) Example (PostgreSQL): In your table list, you’ll see: You query the parent table (sales) The … Continue reading →

Database partitioning is a technique used to split a large table into smaller, more manageable pieces, called partitions, while keeping them logically part of the same table. This improves performance, scalability, and sometimes maintenance. 🧠 Why Partition? ✅ Query performance: … Continue reading →

A trigger in SQL is a database object that automatically executes (or “fires”) a specified block of code in response to certain events (like INSERT, UPDATE, or DELETE) on a table or view. 🔧 What Is a Trigger? A trigger … Continue reading →

Great question! ROW_NUMBER(), RANK(), and DENSE_RANK() are ranking window functions in SQL that assign a number to each row based on some ordering criteria. They look similar but behave differently when there are ties in the data (i.e., rows with … Continue reading →

CTEs (Common Table Expressions) are a way to write temporary, reusable result sets (like named subqueries) in SQL, using the WITH keyword. They’re especially useful for making complex queries more readable, modular, and recursive. 📌 Basic Syntax Think of a … Continue reading →

Excellent question — and the answer depends on which window function you’re using, and whether you’re using ORDER BY in the OVER() clause. Let’s clarify it fully: ✅ If You Use ORDER BY Without ROWS BETWEEN When you write something … Continue reading →

Great question! The clause: is used within a window function to define the range of rows (called the window frame) that the function operates on relative to the current row. 🔍 What It Means UNBOUNDED PRECEDING: Start from the first … Continue reading →