>Digital-flame

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:

-- Offset method: skips rows
LIMIT 10 OFFSET 20;

You say:

-- Seek method: starts "after" a known value
WHERE created_at < '2024-01-01 10:00:00'
ORDER BY created_at DESC
LIMIT 10;

🧭 You’re saying: “Give me the next 10 posts older than this timestamp.”

✅ Example

Table: posts

id	content	created_at
105	Hello world	2024-06-10 10:00:00
104	Post 2	2024-06-10 09:55:00
103	Post 3	2024-06-10 09:45:00
…	…	…

First Page:

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;

You get the latest 10 posts. The last post’s created_at is 2024-06-10 09:00:00.

Next Page (Seek):

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

→ Returns the next 10 posts older than that date.

Repeat by tracking the last created_at on each page.

✅ Advantages

Benefit	Why it Matters
🔄 Consistent results	No shifting from inserts/deletes on other rows
⚡ Very fast	Especially for deep pages (no OFFSET scan)
💪 Works with indexes	Optimized for indexed columns

⚠️ Limitations

Drawback	Description
❌ No page numbers	You can’t jump directly to page 10
❌ Need state	The client must remember the last seen value

🧰 Pro Tips

• Use unique values in ORDER BY, like created_at, id, to avoid duplicates:

ORDER BY created_at DESC, id DESC
WHERE (created_at, id) < (?, ?)

ORDER BY created_at DESC, id DESC
WHERE (created_at, id) < (?, ?)

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

🔁 Summary

Feature	OFFSET	Seek Method (Keyset)
Speed	Slower on deep pages	Fast even for page 1000
Result Stability	Can shift on inserts	Stable and consistent
Page jumping	✅ Yes	❌ No (next/prev only)
Requires bookmark?	❌ No	✅ Yes (last seen value)

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 Pagination)

📌 PostgreSQL / MySQL / SQLite:

-- Get page 3, 10 items per page
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;  -- OFFSET = (page - 1) * pageSize

📌 SQL Server:

-- Requires ORDER BY for OFFSET-FETCH
SELECT * FROM posts
ORDER BY created_at DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Page	Limit	Offset
1	10	0
2	10	10
3	10	20

⚠️ Downsides of OFFSET

• ❌ Slower for large OFFSET values (e.g., page 1000)
• ❌ Inconsistent results if data changes between queries (insert/delete)

✅ 2. Keyset Pagination (a.k.a. Seek Method)

Much faster and more stable for large or real-time datasets.

Example:

-- After last post on previous page had created_at = '2024-01-01 10:00'
SELECT * FROM posts
WHERE created_at < '2024-01-01 10:00'
ORDER BY created_at DESC
LIMIT 10;

Works best when you have an indexed column to paginate by (e.g. created_at, id).

Benefits:

• ✅ Fast even for deep pages
• ✅ More stable if data changes

Drawback:

• ❌ Can’t jump to arbitrary page number directly (only “next page”)

✅ 3. Window Function for Pagination + Total Count

If you need page results and the total number of rows:

SELECT *, COUNT(*) OVER() AS total_count
FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

This gives:

• 10 rows for current page
• A total_count column for UI (e.g., “Page 3 of 100”)

🔍 Indexing Tips

• Always add indexes on ORDER BY columns used in pagination:

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

For keyset pagination, the column used in WHERE should be indexed

🧠 Summary

Method	Use Case	Pros	Cons
LIMIT OFFSET	Simple UI paging	Easy to implement	Slow for deep pages
Keyset (Seek)	Feeds, infinite scroll	Fast, stable	Can’t jump to arbitrary page
Window Functions	Pagination + total count	Accurate, feature-rich	Slightly slower

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 updating your profile picture on a global social network. You update it in Russia, but a friend in the US still sees the old picture for a few seconds. After a short delay, they see the new one.

That’s eventual consistency — the system prioritizes availability and performance, with temporary inconsistency allowed.

📚 Key Characteristics

Property	Description
Temporary inconsistency	Reads may return stale data
High availability	Nodes can serve requests even if out of sync
No strict ordering	Updates can be applied in different order
Convergence	Eventually, all replicas apply all updates

🆚 Compared to Strong Consistency

Feature	Strong Consistency	Eventual Consistency
Read after write	Always reflects latest write	Might return stale data
Latency	Higher	Lower
Availability	Lower in partition failures	Higher
Use cases	Banking, critical apps	Social feeds, caching, DNS

🛠️ Where It’s Used

• Amazon DynamoDB
• Cassandra
• MongoDB (in eventual consistency mode)
• DNS systems
• CDNs (Content Delivery Networks)

---

🔄 Techniques to Achieve Eventual Consistency

• Background synchronization
• Conflict resolution (last-write-wins, vector clocks, CRDTs)
• Quorum-based writes/reads (e.g., read from R, write to W, where R + W > N)

---

🧰 Example: Amazon Dynamo

• Writes propagate to multiple replicas asynchronously
• A read may hit a stale node, but the system will repair it in the background

---

✅ Benefits

• High availability
• Better performance
• Scales well in distributed environments

---

⚠️ Trade-offs

• Data might be inconsistent temporarily
• Requires app developers to handle conflicts or stale reads

---

🔍 Summary

Term	Meaning
Eventual consistency	All nodes will agree eventually, not immediately
When to use it	For systems where real-time accuracy isn’t critical
Benefits	Speed, fault tolerance, scalability
Drawbacks	Complexity, stale reads

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 Sharding?

• ⚡ Improves performance: Reduces load per server
• 🌍 Increases scalability: More shards = more capacity
• 🧩 Enables horizontal scaling: Add servers to scale out

---

🗂️ Example

Suppose you have a users table with 1 billion users:

Instead of storing all users in one huge table on one server, you could:

Shard	Range
1	Users with ID 1–100M
2	Users with ID 100M–200M
3	Users with ID 200M–300M
…	…

Each shard lives on a separate server or database instance.

🛠️ Types of Sharding

Type	Description	Example
Horizontal sharding	Split rows	users by region or ID range
Vertical sharding	Split columns	users_basic vs users_private
Directory-based sharding	Use a lookup table	Keeps track of which user is in which shard

⚙️ How It Works

• Shard key: A column (e.g. user_id, region, tenant_id) used to decide which shard data goes into
• Application logic or middleware routes queries to the right shard

📦 Benefits

• ✅ Load is spread across multiple machines
• ✅ Parallelism improves throughput
• ✅ Reduces contention and I/O bottlenecks

⚠️ Challenges

Issue	Description
❌ Cross-shard joins	Harder to query across shards
❌ Resharding	Moving data when scaling or changing strategy
❌ Complex logic	App must handle routing and aggregation
❌ Consistency	More difficult to ensure strong consistency across shards

---

🔄 Example Sharding Strategy

// Pseudo-code for picking a shard:
shard_id = hash(user_id) % num_shards

Then route the query to shard[shard_id].

🧰 Common Use Cases

• Large-scale social networks
• Multi-tenant SaaS apps
• High-volume e-commerce platforms
• Large log/event stores

---

🧱 Summary

Concept	Description
Sharding	Splitting data across DB instances
Shard Key	Field used to determine shard
Goal	Scale out performance + storage
Trade-offs	Complexity, joins, consistency

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 much faster.

---

📌 Key Concepts

Feature	Materialized View
Data storage	✅ Stored on disk
Performance	✅ Fast reads (no re-computation)
Freshness	❌ May become stale unless refreshed
Updates	Manual or scheduled refresh needed
Use cases	Reporting, analytics, caching expensive joins/aggregates

🧠 How It’s Different from a Regular View

	Regular View	Materialized View
Definition	Stored SQL query	Stored query result
Query speed	Slower (computed live)	Faster (precomputed)
Data updates	Always current	Must be refreshed

---

🧪 Example in PostgreSQL

1. Create a Materialized View

CREATE MATERIALIZED VIEW recent_orders AS
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id;

Query it like a table

SELECT * FROM recent_orders;

Refresh the view (to update the data)

REFRESH MATERIALIZED VIEW recent_orders;

Optionally use WITH CONCURRENTLY (PostgreSQL) to avoid locking reads.

🐬 MySQL Support?

MySQL doesn’t support true materialized views, but you can simulate them using:

• A normal table + triggers
• A scheduled job (via EVENTs or cron + INSERT INTO ... SELECT)

🧰 Use Cases

• Expensive joins or aggregations (e.g. daily reports)
• Caching filtered subsets of large datasets
• Improving read performance when real-time data isn’t needed

---

⚠️ Trade-offs

Benefit	Drawback
Faster queries	Needs manual/scheduled refresh
Reduces CPU load	Uses disk space
Simplifies complex logic	Can become stale

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
• Raise an error like:
  • MySQL: ERROR 1213 (40001): Deadlock found when trying to get lock
  • PostgreSQL: ERROR: deadlock detected
  • SQL Server: Transaction (Process ID xx) was deadlocked...

✅ 2. View Deadlock Information

🐘 PostgreSQL

• Enable logging:

SET deadlock_timeout = '1s'; -- time to wait before detecting

Check logs (postgresql.log) for:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ...

🐬 MySQL (InnoDB)

• Run:

SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section.

🪟 SQL Server

• Use the Deadlock Graph in SQL Server Management Studio (SSMS).
• Or:

SELECT * FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL;

🔧 How to Resolve Deadlocks

✅ 1. Access Resources in Consistent Order

All transactions should lock rows/tables in the same order to prevent cycles.

✅ 2. Keep Transactions Short

Minimize the duration of locks by:

• Doing work outside the transaction when possible
• Avoiding user input inside a transaction

✅ 3. Add Indexes to Reduce Lock Scanning

If your query has to scan many rows, it may lock more than needed. Indexes help target exact rows.

✅ 4. Use Lower Isolation Levels (Carefully)

E.g., in PostgreSQL:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

This reduces locking but may allow anomalies.

✅ 5. Use Explicit Locking If Needed

To control locking behavior:

• PostgreSQL / MySQL:

SELECT ... FOR UPDATE;

✅ 6. Retry Logic in Application Code

If you catch a deadlock error, automatically retry the transaction:

int attempts = 3;
while (attempts-- > 0) {
  try {
    // start transaction
    // do work
    // commit
    break;
  } catch (DeadlockException e) {
    if (attempts == 0) throw e;
    // wait and retry
  }
}

📊 Summary Table

Action	Detection	Resolution
Enable logging	✅ PostgreSQL/MySQL logs	🔍 Use EXPLAIN, logs
Use admin tools	✅ SQL Server Graph	🎯 Visual deadlock detection
Fix lock order	❌ Prevents future issues	✅ Best long-term fix
Add retry logic	❌ Post-deadlock strategy	✅ Common in real systems

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

BEGIN;  -- or START TRANSACTION

At this point, nothing is locked yet.

❗ Locks are acquired only when you access data (e.g. with SELECT, UPDATE, DELETE, etc.)

---

🔍 What Gets Locked?

1. Row-level Locks (Most Common)

• For most UPDATE, DELETE, SELECT ... FOR UPDATE queries
• Only the specific rows that match your query are locked

✅ Efficient and allows high concurrency

-- Locks only the row where id = 1
UPDATE users SET name = 'Alice' WHERE id = 1;

— Locks only the row where id = 1
UPDATE users SET name = ‘Alice’ WHERE id = 1;

2. Table-level Locks

• Acquired explicitly (LOCK TABLE) or in some implicit cases:
  • DDL operations (ALTER TABLE, DROP)
  • Full table scans in some MyISAM tables (MySQL)
  • Sometimes used for performance optimization

LOCK TABLE users IN EXCLUSIVE MODE;  -- PostgreSQL

❗ Prevents any other access (read/write) depending on lock type

3. Page or Predicate Locks (Advanced/rare)

• Used in some isolation levels like REPEATABLE READ or SERIALIZABLE
• May lock ranges or “gaps” between rows (to prevent phantom reads)

-- In SERIALIZABLE mode, PostgreSQL might lock ranges
SELECT * FROM users WHERE age > 30;

🔄 Isolation Level Influence

Isolation Level	Lock Behavior
READ COMMITTED	Only rows touched are locked
REPEATABLE READ	Locks rows and prevents non-repeatable reads
SERIALIZABLE	May lock row ranges to prevent phantoms

🧠 Summary

Action	Locks Acquired
BEGIN	None (yet)
UPDATE, DELETE	Row-level locks
SELECT FOR UPDATE	Row-level locks (read + lock)
LOCK TABLE	Table-level lock
DDL (ALTER, DROP)	Implicit table lock

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 unless the system intervenes.

---

🔄 Simple Example of a Deadlock

Imagine two transactions:

🧵 Transaction A

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- waits for Transaction B to release row id=2

🧵 Transaction B

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- waits for Transaction A to release row id=1

Both are waiting for each other to release a lock, creating a cycle → 💥 deadlock.

🔒 How Deadlocks Happen

Transaction A	Transaction B
Locks row 1
	Locks row 2
Tries to lock 2	Tries to lock 1
🚫 Waits	🚫 Waits

Neither can proceed. Database must choose one to rollback to break the cycle.

---

🛡️ How Databases Handle It

Most databases (PostgreSQL, MySQL InnoDB, SQL Server) automatically detect deadlocks and:

• Abort one transaction
• Roll back changes
• Return an error (e.g., Deadlock found; try restarting transaction)

🧠 How to Prevent Deadlocks

✅ 1. Access resources in consistent order

-- Always lock row 1, then row 2 (not the reverse)

✅ 2. Keep transactions short

• Don’t hold locks for longer than needed.

✅ 3. Use appropriate isolation levels

• Lower levels (like READ COMMITTED) reduce locking.
• SELECT ... FOR UPDATE should be used carefully.

✅ 4. Retry on deadlock

• Implement retry logic for deadlock errors in your app.

🧪 Detecting Deadlocks

• PostgreSQL: pg_stat_activity, logs with deadlock detected
• MySQL: SHOW ENGINE INNODB STATUS
• SQL Server: Deadlock Graph in Management Studio

---

⚠️ Summary

Term	Meaning
Deadlock	Two transactions waiting on each other’s locks indefinitely
Result	DB engine kills one to free up resources
Solution	Use consistent locking order, short transactions, and retries

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

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100),
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

👥 Friendships / Follows

Depending on whether the network is mutual (like Facebook) or asymmetric (like Twitter/Instagram):

a. Mutual friendship

CREATE TABLE friendships (
    user_id INT REFERENCES users(id),
    friend_id INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, friend_id),
    CHECK (user_id < friend_id)
);

Use CHECK (user_id < friend_id) to avoid duplication (A–B is same as B–A)

b. Asymmetric follow (like Twitter)

CREATE TABLE follows (
    follower_id INT REFERENCES users(id),
    followee_id INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, followee_id)
);

📝 2. Posts (Status Updates, Images, etc.)

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Add media tables later (images, videos, etc.).

💬 3. Comments and Likes

Comments:

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(id),
    user_id INT REFERENCES users(id),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Likes:

CREATE TABLE likes (
    user_id INT REFERENCES users(id),
    post_id INT REFERENCES posts(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, post_id)
);

🔔 4. Notifications (Optional)

CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    recipient_id INT REFERENCES users(id),
    sender_id INT REFERENCES users(id),
    type VARCHAR(20), -- e.g., 'like', 'comment', 'follow'
    reference_id INT, -- e.g., post_id or comment_id
    seen BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

📥 5. Messages (DMs)

CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    sender_id INT REFERENCES users(id),
    receiver_id INT REFERENCES users(id),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

📸 6. Media (Optional)

CREATE TABLE media (
    id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(id),
    url TEXT NOT NULL,
    media_type VARCHAR(20) -- 'image', 'video', etc.
);

🔍 7. Indexes to Add

• Indexes on foreign keys for joins and lookups
• Composite index on posts(user_id, created_at) for user feeds
• Index on follows(followee_id) for getting followers efficiently

---

🧠 Additional Considerations

Feature	Strategy
News Feed	Precompute or generate on-the-fly using posts from followees
Scaling	Sharding by user_id or use message queues for async fan-out
Privacy Settings	Add fields to users and/or posts
Hashtags, Mentions	Use full-text search or separate tables for tagging

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

A covering index “covers” a query when all columns used in the SELECT, WHERE, JOIN, or ORDER BY clauses are included in the index.

---

✅ Why It’s Fast

• Fewer I/O operations: No need to fetch the full row from the table.
• Especially powerful for read-heavy workloads and OLAP-style queries.

---

📌 Example (MySQL / PostgreSQL)

Table:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    status VARCHAR(20)
);

Query:

SELECT email FROM users WHERE status = 'active';

Covering Index:

CREATE INDEX idx_status_email ON users(status, email);

• status → used in WHERE
• email → used in SELECT
• ✅ The index covers the query → no need to fetch from table

---

🔎 MySQL Note (InnoDB)

In MySQL, the primary key is always implicitly included in every secondary index (since it’s needed to locate the row), so you don’t need to add it explicitly unless your query needs it directly.

📌 PostgreSQL Note

PostgreSQL uses the term “Index Only Scan” when a covering index is used. But it requires visibility map bits to confirm that the row is visible (i.e., not recently updated), so sometimes it still touches the table.

Use:

EXPLAIN ANALYZE SELECT email FROM users WHERE status = 'active';

And look for:

Index Only Scan using idx_status_email

🧪 Test If You Need It

• Run EXPLAIN or EXPLAIN ANALYZE and check if a table/heap fetch occurs
• If yes, a covering index could eliminate that extra step

📎 Recap

Feature	Covering Index ✅
All columns in index	✅
Avoids table lookup	✅
Improves performance	✅ Especially for reads
Maintained on writes	⚠️ Slower inserts/updates