>Digital-flame

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: Only scan relevant partition(s)
• ✅ Manageability: Easier to archive, purge, or back up
• ✅ Parallelism: Different partitions can be read/written in parallel
• ✅ Maintenance: Indexes and constraints apply per partition

📦 Types of Partitioning

🔍 Example: Range Partitioning (PostgreSQL)

CREATE TABLE sales (
  id SERIAL,
  sale_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (sale_date);

Then define partitions:

CREATE TABLE sales_2023_q1 PARTITION OF sales
  FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_2023_q2 PARTITION OF sales
  FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

Now, when you query by date:

SELECT * FROM sales WHERE sale_date = '2023-02-10';

PostgreSQL automatically routes the query to sales_2023_q1 only → faster.

🧪 Other Partitioning Examples

✅ List Partitioning (MySQL, PostgreSQL 11+)

CREATE TABLE users (
  id INT,
  country_code TEXT
) PARTITION BY LIST (country_code);

CREATE TABLE users_us PARTITION OF users
  FOR VALUES IN ('US');

CREATE TABLE users_uk PARTITION OF users
  FOR VALUES IN ('UK');

🧪 Other Partitioning Examples

✅ List Partitioning (MySQL, PostgreSQL 11+)

CREATE TABLE users (
  id INT,
  country_code TEXT
) PARTITION BY LIST (country_code);

CREATE TABLE users_us PARTITION OF users
  FOR VALUES IN ('US');

CREATE TABLE users_uk PARTITION OF users
  FOR VALUES IN ('UK');

✅ Hash Partitioning (PostgreSQL / MySQL)

Useful for spreading rows evenly when ranges/lists aren’t practical.

CREATE TABLE logs (
  id INT,
  message TEXT
) PARTITION BY HASH (id);

CREATE TABLE logs_p0 PARTITION OF logs FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE logs_p1 PARTITION OF logs FOR VALUES WITH (modulus 4, remainder 1);
-- etc.

🚦 Partition Pruning

Modern databases (PostgreSQL, MySQL, Oracle, etc.) prune unneeded partitions automatically during query execution, so only relevant partitions are scanned.

This boosts performance dramatically for filtered queries like:

SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

⚠️ Downsides / Gotchas

✅ Summary

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 is like a hidden “watchdog” that runs custom logic automatically when data changes in a table.

🧠 When Would You Use a Trigger?

✅ Common Use Cases:

🧾 Basic Trigger Syntax (PostgreSQL)

-- 1. Create a trigger function
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. Attach trigger to a table
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

✅ Now every time a users row is updated, updated_at is set to NOW() automatically.

🚦 Trigger Timing Options

🧮 FOR EACH ROW vs FOR EACH STATEMENT

🐬 Example: MySQL Trigger

CREATE TRIGGER log_deletes
AFTER DELETE ON users
FOR EACH ROW
INSERT INTO deleted_users_log(user_id, deleted_at)
VALUES (OLD.id, NOW());

⚠️ Trigger Considerations

🧠 Summary

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 the same value in the ORDER BY column).

📊 Quick Comparison

🔍 Visual Example

Let’s say we have this table ordered by score DESC:

Query:

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

📈 Output:

🧠 Key Differences Explained

✅ ROW_NUMBER()

• Ignores ties.
• Simply numbers rows sequentially.
• Always unique.

✅ RANK()

• Gives same rank to tied rows.
• Skips the next rank(s) to maintain total ordering.

Two rows tied at rank 2 → next row gets rank 4 (gap).

✅ DENSE_RANK()

• Also gives same rank to tied rows.
• Does not skip the next rank.

Two rows tied at rank 2 → next row gets rank 3.

📦 When to Use Each

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

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

Think of a CTE as a temporary view that only exists during the execution of the main query.

🧾 Example 1: Simple CTE

WITH recent_orders AS (
  SELECT * FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) 
FROM recent_orders
GROUP BY customer_id;

✅ What it does:

• Creates a CTE called recent_orders
• Then uses it as if it’s a table in the main SELECT

✅ Benefits of CTEs

🧾 Example 2: Recursive CTE (Hierarchy)

Suppose you have an employees table with a manager_id column:

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL  -- start from the CEO

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

✅ This will return the full hierarchy of employees, with levels!

🆚 CTE vs Subquery vs Temp Table

🧠 Summary

• A CTE is a named temporary result set used in a query.
• Declared with the WITH clause
• Can be recursive for things like org charts or tree traversal
• Improves query clarity and modularity

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 like:

SUM(sale_amount) OVER (
  PARTITION BY employee_id
  ORDER BY sale_date
)

You are implicitly using:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

✅ So YES — it’s the same as using it explicitly.

🧪 What Happens Under the Hood

The default window frame depends on what you specify:

🧾 Example 1: With ORDER BY

SUM(sale_amount) OVER (
  PARTITION BY employee_id
  ORDER BY sale_date
)

→ Behaves like a running total

🧾 Example 2: Without ORDER BY

SUM(sale_amount) OVER (
  PARTITION BY employee_id
)

→ Behaves like SUM() per group (same result for every row in the partition), because it considers all rows in the partition.

🧠 Summary

Great question! The clause:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

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 row in the partition (group)
• CURRENT ROW: End at the current row (inclusive)

So you’re saying:

“Include all rows from the beginning of the group up to and including this one.”

✅ Used For: Running Totals, Running Averages, Running Max/Min

🧾 Example

SELECT
  employee_id,
  sale_date,
  sale_amount,
  SUM(sale_amount) OVER (
    PARTITION BY employee_id
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

This computes a running total for each employee’s sales in order of sale_date.

📊 Visualization

Let’s say we have this:

Here’s what each row sees:

🔁 Variants

🧠 Summary

Let’s explore how to use window functions with SUM(), AVG(), and MAX() in SQL.

These functions compute aggregates over a “window” of rows, but unlike GROUP BY, they don’t collapse rows — they return a result per row.

🎯 Sample Table: sales

✅ 1. SUM() — Running Total per Employee

SELECT
  employee_id,
  sale_date,
  sale_amount,
  SUM(sale_amount) OVER (
    PARTITION BY employee_id
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

🔍 What it does:

• Partitions by employee_id
• Orders by sale_date
• For each row: sums all previous + current rows in the window

✅ 2. AVG() — Average Sale per Employee

SELECT
  employee_id,
  sale_date,
  sale_amount,
  AVG(sale_amount) OVER (
    PARTITION BY employee_id
  ) AS avg_sale_per_employee
FROM sales;

🔍 What it does:

• Computes the average sale_amount per employee
• Same result on all rows for the same employee

✅ 3. MAX() — Max Sale So Far (Running Max)

SELECT
  employee_id,
  sale_date,
  sale_amount,
  MAX(sale_amount) OVER (
    PARTITION BY employee_id
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS max_so_far
FROM sales;

🔍 What it does:

• Tracks the maximum sale up to the current date
• One running max per employee

🧠 Summary Table

window functions (also called analytic functions) are a powerful feature in SQL that let you perform calculations across rows related to the current row, without collapsing them into a single result like GROUP BY does.

🧠 Simple Definition

A window function performs a calculation over a “window” (subset) of rows that are related to the current row, while still returning one row per input row.

🧾 Common Window Functions

🔍 Basic Syntax

<function>() OVER (
  PARTITION BY <column>
  ORDER BY <column>
  ROWS BETWEEN ... AND ...
)

PARTITION BY: Like GROUP BY, but keeps rows separate

ORDER BY: Defines the order within each partition

ROWS BETWEEN: Optional frame (default is “range to current row”)

📌 Example 1: ROW_NUMBER()

🧾 Get top 3 most recent orders per customer:

SELECT
  customer_id,
  order_id,
  order_date,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC
  ) AS row_num
FROM orders;

Then filter:

SELECT * FROM (
  -- use above query as subquery
) AS numbered
WHERE row_num <= 3;

📌 Example 2: Running Total

SELECT
  user_id,
  created_at,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM transactions;

This gives a running total per user by date.

📌 Example 3: Difference Between Rows (LAG, LEAD)

SELECT
  user_id,
  amount,
  LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount,
  amount - LAG(amount, 1) OVER (...) AS diff
FROM transactions;

Useful for time-series, trends, deltas.

🔍 Window Functions vs. GROUP BY

🧠 Summary

• Window functions let you calculate aggregates, rankings, and comparisons over a “sliding window” of rows
• They’re perfect for:
  • Leaderboards
  • Pagination
  • Running totals
  • Trend analysis

-- Page 1
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;

Let’s say the last row returned has:

created_at = '2024-06-10 09:00:00'

Now for Page 2, we run:

-- Page 2
SELECT * FROM posts
WHERE created_at < '2024-06-10 09:00:00'
ORDER BY created_at DESC
LIMIT 10;

This query says: “Give me the next 10 rows older than what I already saw.”

❓What Happens If Rows Were Deleted?

🧼 If a previously returned row is deleted:

• It won’t affect the next page.
• The Seek query still returns “the next 10 older rows” — just skipping over the now-missing row.

✅ No duplication
✅ No skipping unintended rows
✅ Pagination still correct

🧪 Example

Posts table (simplified):

Now suppose row with id = 3 (created_at = 09:00) is deleted.

You rerun page 2:

SELECT * FROM posts
WHERE created_at < '2024-06-10 09:00:00'
ORDER BY created_at DESC
LIMIT 10;

You get:

D | 08:30
E | 08:00

✅ Works fine! The deleted row is just skipped.

🛑 BUT: If You Delete the Seek Anchor Row Itself

If you store the last-seen value (like '2024-06-10 09:00:00'), and then that row is deleted, your anchor still works:

WHERE created_at < '2024-06-10 09:00:00'

You’re not relying on the actual row — just its timestamp.

✅ Seek still works even if the anchor row is deleted.

✅ Summary

The Seek Method avoids “shifting” caused by inserts or deletes because it doesn’t depend on row positions like OFFSET does—it uses stable, deterministic values (like timestamps or IDs) to continue pagination.

Let me explain it clearly:

🔁 The Problem with OFFSET

Imagine this:

-- Get page 2, 10 per page
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;

You fetch rows 11–20, assuming rows 1–10 came before.

But if someone inserts or deletes rows between queries, the row positions shift:

Your query might:

• Skip Post A
• Repeat Post B
• Return inconsistent pages

⚠️ This makes OFFSET-based pagination unstable if data changes.

✅ Why Seek Method Doesn’t Shift

Seek pagination uses the value of the last item seen:

-- Get first 10
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;

You get the latest 10. Let’s say the last one has:

created_at = '2024-06-10 09:00:00'

Then the next page uses:

SELECT * FROM posts
WHERE created_at < '2024-06-10 09:00:00'
ORDER BY created_at DESC
LIMIT 10;

Even if new rows are inserted:

• They’ll have newer timestamps
• So they don’t affect this page
• This page starts after the last-seen value → no skipping or duplication

🧠 Summary