How Database Actually Works (Internally)

›Simple Hinglish, No Heavy Jargon

Most log sochte hain database ka kaam bas itna hota hai:

Query aayi → Data nikla → Kaam khatam

Par reality isse kaafi zyada interesting hai.

Database ek full-fledged system hota hai jo har query ke saath sochta hai, samajhta hai, plan banata hai, optimize karta hai, execute karta hai, aur saath-saath data ko safe, consistent aur fast bhi rakhta hai.

Chalo database ke andar ghus ke dekhte hain 👇

Step 1: Query aati hai (Raw Text)

Jab hum likhte hain:

SELECT name FROM users WHERE age > 18;

Database ke liye yeh sirf plain text hota hai.

Na isse English samajh aati hai, na SQL naturally.

Database internally sochta hai:

“Yeh sirf ek string hai.

Pehle mujhe samajhna padega ki iska matlab kya hai.”

Isliye actual processing yahin se start hoti hai.

Step 2: Parsing – Query ko todna

Parsing ka matlab hota hai query ko structure me convert karna.

Parser query ko chhote logical parts me tod deta hai:

• SELECT
• name
• FROM
• users
• WHERE
• age > 18

Is step ke baad database ko rough idea milta hai:

• Yeh ek SELECT type ki query hai
• Yeh kisi table se data read karna chahti hai

Abhi database ne sirf pehchana hai, execute nahi kiya.

Step 3: Tokenization – Keywords pehchanna

Ab database har word ko token bana deta hai.

Uske paas pehle se reserved keywords ki list hoti hai, jaise:

• SELECT
• INSERT
• CREATE
• UPDATE
• DELETE

Database har word ke liye check karta hai:

• Yeh keyword hai?
• Yeh table ka naam ho sakta hai?
• Yeh column lag raha hai?

Agar koi word match nahi karta, database turant error deta hai:

Kaafi errors isi stage par pakad liye jaate hain.

Step 4: Syntax Check – Grammar sahi hai?

Ab database query ki grammar check karta hai, bilkul language grammar jaise.

Yeh check hota hai:

• SELECT ke baad column likha hai ya nahi
• FROM ke baad table ka naam hai ya nahi
• WHERE clause sahi jagah pe hai ya nahi

Agar tum likho:

SELECT FROM users name;

Database bolega:

Jaise English me “I going market” galat hota hai.

Step 5: Semantic Check – Meaning sahi hai?

Grammar sahi hone ke baad database meaning check karta hai.

Database verify karta hai:

• Kya users table exist karta hai?
• Kya name column us table me hai?
• Kya age ka data type comparison ke liye valid hai?

Grammar correct ho sakti hai,

lekin agar logic galat hai, query reject ho jaati hai.

Step 6: Parse Tree / Query Tree – Blueprint banana

Ab database ek tree structure banata hai jise parse tree ya query tree kehte hain.

Is tree me define hota hai:

• Pehle kaunsa operation chalega
• Phir kaunsa
• Kis order me data process hoga

Is stage par:

• Data read nahi hota
• Disk access nahi hoti

Yeh sirf ek blueprint hota hai, jaise building ka plan.

Step 7: Query Optimizer – Best rasta kaunsa?

Ab query jaati hai Query Optimizer ke paas.

Same kaam karne ke multiple tareeke ho sakte hain:

• Full table scan
• Index use karke direct search
• Pehle filter, phir join
• Pehle join, phir filter

Optimizer har option ka cost calculate karta hai.

Cost ka matlab kya hai?

Yahan cost ka matlab paisa nahi hota.

Cost ka matlab hota hai:

• CPU usage
• Disk reads
• Memory consumption
• Execution time

Optimizer data aur environment ke hisaab se best plan choose karta hai.

Step 8: Execution Engine – Asli kaam yahin hota hai

Execution Engine database ka main worker hota hai.

Optimizer jo plan decide karta hai, execution engine usi ko follow karta hai.

Execution Engine:

• Index use karta hai
• Disk se data uthwata hai
• Operations correct order me chalata hai

Yahin se actual data movement start hota hai.

Utility Systems – Query ke alawa bhi bahut kuch

Database sirf query execute nahi karta.

Uske saath kaafi support systems bhi chalte rehte hain.

›Authentication & Authorization

• User kaun hai
• Read / write permission
• Access control

›Backup & Recovery

• Regular backups
• Crash ke baad data restore
• Logs ke through consistency

›Metrics & Monitoring

• Slow queries
• Memory usage
• Disk I/O
• Active connections

Cache – Disk ko baar-baar kyun jagana?

Disk slow hoti hai, memory fast hoti hai.

Database pehle check karta hai:

• Kya requested data cache me already hai?

Agar haan → turant client ko return

Agar nahi → disk se uthao, cache me daalo, phir return

Isse performance kaafi improve hoti hai.

Storage Format – Data har jagah same nahi hota

Database data ko alag-alag formats me store karta hai:

• Rows
• Documents
• Compressed blocks
• Binary files

Har database ka apna internal storage format hota hai.

Index File – Data ke baare me data

Index actual data nahi hota.

Index hota hai:

Data about data

Jaise book ke last me index page hota hai jo batata hai kaunsa topic kis page par hai.

Index file me hota hai:

• Value
• Pointer
• Disk location

Isse search bahut fast ho jaati hai.

Storage Engine – Junior CEO of Database

Storage Engine decide karta hai:

• Data kaise disk me likha jaaye
• Index kaise maintain ho
• Cache kaise handle ho
• Logs kaise likhe jaayein

Isliye bol sakte hain:

Storage Engine = Database ka Junior CEO

Managers – Har kaam ka apna boss

Database ke andar multiple managers hote hain:

›Transaction Manager

• Transaction start / commit / rollback
• ACID rules follow karwata hai

›Lock Manager

• Read / write locks
• Conflicts avoid karta hai

›Recovery Manager

• Crash ke baad system recover karta hai
• Logs ke through undo / redo karta hai

Yeh managers aapas me connected hote hain aur continuously coordinate karte rehte hain.

Transaction History – Sab kuch yaad rakha jaata hai

Database har transaction ka record rakhta hai:

• Kya change hua
• Kab change hua
• Pehle value kya thi

Isse:

• Rollback possible hota hai
• Crash recovery hoti hai
• Data consistent rehta hai

Final Flow (One-Look Summary)

Query
 → Parser
 → Tokenization
 → Syntax + Semantic Check
 → Parse Tree
 → Query Optimizer
 → Execution Engine
 → Cache / Storage Engine
 → Client

Final Thought

Database ek dumb storage nahi hai.

Yeh ek intelligent system hai jo har query ke peeche

planning, optimization, safety aur history maintain karta hai.

Agar tumhe lagta tha database sirf find() chalata hai,

toh ab clear ho gaya hoga:

Andar poora ecosystem kaam karta hai.