DEV Community: Abdul Rahman

Chai aur CSS — From Basics to Modern Features

Abdul Rahman — Sun, 24 May 2026 08:26:23 +0000

CSS stands for Cascading Stylesheet. Cascading means overwriting. The stylesheet is just for styling. When overwriting the style of an element, CSS first checks the specificity and then the line order.

Specificity and Priority

The style with the highest specificity wins. If two rules have the same specificity, the one written last wins.

p { color: blue; }          /* lowest priority — tag name */
.class { color: blue; }     /* medium priority — class */
#id { color: blue; }        /* high priority — ID */
/* inline style in HTML tag — highest priority */

Same element, same specificity → last rule wins:

p {
  color: red;
}

p {
  color: blue;   /* This will apply */
}

<p>Hello world</p>

Same element → higher priority selector wins:

p {
  color: red;        /* element selector — lowest */
}

.highlight {
  color: green;      /* class selector — higher */
}

#special {
  color: blue;       /* ID selector — highest */
}

<p id="special" class="highlight">Hello world</p>

Font

font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; /* Sets prioritized fonts */
font-size: 1rem;       /* Sets text size, responsive with rem */
font-weight: bold;     /* or 100–900 for thickness control */
font-style: italic;    /* Makes text italic */

Responsive Font with clamp:

font-size: clamp(16px, 4vw, 24px);

Minimum: 16px — font will never go below this
Preferred: 4vw — 4% of the viewport width
Maximum: 24px — font will never go above this

If screen is large, font is large. If screen is small, font is small. You can adjust these values based on your needs.

Text

text-align: center;          /* Aligns text in the center */
letter-spacing: 0.15em;      /* Adjusts space between characters */
text-decoration: underline;  /* Adds underline/overline/line-through */
word-spacing: 0.25em;        /* Sets spacing between words */
line-height: 1.5;            /* Sets vertical spacing between lines */
text-transform: uppercase;   /* Converts text to uppercase */

Background

background: linear-gradient(...);    /* Adds gradient */
background-image: url("path");       /* Adds image */
background-position: center;         /* Positions background image */
background-size: cover;              /* Scales image to cover element */
background-blend-mode: multiply;     /* Blends background layers */
background-clip: text;               /* Clips background to text */

Border and Outline

border: 2px solid #000;              /* Adds border with size, style, color */
border-radius: 15px;                 /* Rounds corners */
outline: 2px dashed red;             /* Adds outline outside element box */
border-image: url(border.png) 30 round; /* Uses image as border */

Box Model

margin: 10px 20px 10px 20px;   /* Outer spacing */
padding: 5px 10px 5px 10px;    /* Inner spacing */
width: 100%;                    /* Element width */
height: fit-content;            /* Height based on content */

Box-Sizing and Overflow

box-sizing: border-box;   /* Includes padding/border in total size */
overflow: hidden;          /* Hides overflow */
overflow-x: scroll;        /* Scroll horizontally */
overflow-y: auto;          /* Scroll vertically as needed */
aspect-ratio: 16/9;        /* Keeps width:height ratio */
object-fit: cover;         /* Scales image/video to fill box */

Always use box-sizing: border-box — without it, padding and border add to the element's width and break your layout.

Colors

color: #333;                    /* Text color */
opacity: 0.8;                   /* Element transparency */
color: hsl(200, 80%, 40%);     /* HSL color format */

Positioning

position: relative;   /* Sets positioning context */
z-index: 10;          /* Stacking order */

CSS Variables — Set Primary and Secondary Colors

You can define colors in :root using CSS variables and reuse them anywhere in the stylesheet. This is widely used in modern CSS.

:root {
  --primary: #C9A84C;
  --secondary: #0A0A0A;
  --text: #E8E3D9;
}

h1 {
  color: var(--primary);
}

body {
  background: var(--secondary);
  color: var(--text);
}

Change the value in one place — it updates everywhere. This is how design systems work.

Flexbox

Flexbox works on two axes inside a container.

Main Axis — goes left to right. Controlled by justify-content
Cross Axis — goes top to bottom. Controlled by align-items

When you apply display: flex on a container, all items inside move to the start of the main axis.

Flex Direction:

flex-direction: row;     /* Items in a row (default) */
flex-direction: column;  /* Items in a column */

Flex for child items:

flex: 1;   /* Give all extra width to this item */
flex: 2;   /* Take double the extra space compared to flex: 1 */

If you have 5 items and item 1 has flex: 1 and item 2 has flex: 2 — after all items take their natural width, the remaining space is divided into 3 parts. Item 2 gets 2 parts, item 1 gets 1 part.

Order in Flexbox:

order: 1;   /* Moves item to the end — default is 0 for all items */

Higher order number means it comes later. Lower order number means it comes first.

Wrap:

flex-wrap: wrap;          /* Items wrap to next line */
flex-wrap: wrap-reverse;  /* Items wrap in reverse */

All Flexbox Properties:

/* Parent (Container) */
display: flex;
flex-direction: row;
justify-content: space-between;
align-items: center;
gap: 1rem;

/* Child (Items) */
flex: 1;
align-self: center;
order: 2;

CSS Grid

Control whole container columns:

display: grid;
grid-template-columns: 1fr 1fr 1fr;   /* 3 equal columns */

1fr means 1 fraction of the available space. Three 1fr values = three equal columns.

Control whole container rows:

.grid-container {
  display: grid;
  grid-template-rows: 80px 100px 100px;   /* 3 rows with different heights */
}

Control specific item width:

grid-column: 1 / 4;   /* Covers columns 1, 2, and 3 */
grid-column: 2 / 4;   /* Covers columns 2 and 3 */

Remember — if you have 3 columns, the lines are 4:

| 1 | 2 | 3 |
^   ^   ^   ^
1   2   3   4  ← lines

Always apply this on a specific item, not the whole container.

Control specific item height:

grid-row: 1 / 4;   /* Covers rows 1, 2, and 3 */
grid-row: 2 / 4;   /* Covers rows 2 and 3 */

Same logic — 3 rows = 4 lines. Apply on specific item only.

Media Queries

@media (max-width: 768px) {
  body {
    font-size: 14px;   /* Applies styles on small screens */
  }
}

max-width: 768px means this style applies when the screen is 768px or smaller — tablets and phones. This is how you make websites responsive without a framework.

Animations and Transitions

Keyframe Animation:

@keyframes slide {
  from {
    transform: translateX(-100%);
  }
  to {
    transform: translateX(0);
  }
}

.element {
  animation: slide 1s ease-in-out infinite alternate;
}

Transition — smooth property changes:

transition: all 0.3s ease-in-out;

Transform:

transform: scale(1.1) rotate(10deg);      /* Scale and rotate */
transform: translate(10px, 20px);          /* Move on X and Y axis */

Modern CSS Features

scroll-snap-type: x mandatory;   /* Creates snap scrolling */
backdrop-filter: blur(10px);      /* Adds blur behind element */

/* Custom scrollbar */
::-webkit-scrollbar { width: 8px; }
::-webkit-scrollbar-thumb { background: #888; border-radius: 4px; }

/* Responsive font size */
font-size: clamp(1rem, 2vw, 1.5rem);

backdrop-filter: blur() is what gives that frosted glass effect you see on modern UIs. scroll-snap-type makes sections snap into place when scrolling — no JavaScript needed.

You can find more of my work at abdulrdeveloper.me
Read more posts at blog.abdulrdeveloper.me

Chai aur HTML — A Beginner's Complete Guide

Abdul Rahman — Sun, 24 May 2026 08:21:33 +0000

HTML stands for HyperText Markup Language. It is the skeleton of every website you see on the internet. Before CSS makes it beautiful and JavaScript makes it interactive, HTML defines the structure.

What is a Web Server?

Those servers who return HTML are known as Web Servers. When you type a URL in your browser, the browser sends a request to a web server. The server responds back with an HTML file. Your browser reads that file and shows you the webpage.

The HTML Boilerplate

Every HTML file starts with this structure. In VS Code, just type ! and press Tab — it generates this automatically.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <!-- Content goes here -->
</body>
</html>

<!DOCTYPE html> tells the browser this is an HTML5 document. lang="en" tells search engines and screen readers the language of the page. Everything visible goes inside <body>. Everything about the page (title, meta, CSS links) goes inside <head>.

Headings

There are six levels of headings. <h1>, <h2>, and <h3> are the most important. <h4>, <h5>, <h6> are rarely used.

<h1>Heading 1</h1>      <!-- Most Important -->
<h2>Heading 2</h2>      <!-- Most Important -->
<h3>Heading 3</h3>      <!-- Important -->
<h4>Heading 4</h4>
<h5>Heading 5</h5>
<h6>Heading 6</h6>

Always use only one <h1> per page — it is important for SEO. Search engines use headings to understand the structure of your content.

Containers

Containers are used to group and organize content on a page.

<div>This is a block container (div)</div>         <!-- Most Important -->
<span>This is an inline container (span)</span>    <!-- Important -->
<p>This is a paragraph</p>                         <!-- Most Important -->
<pre>Preserves formatting and spaces</pre>
<code>console.log("Code snippet")</code>
<blockquote>A block quotation of text</blockquote>

<div> is a block element — it takes the full width and starts on a new line. <span> is an inline element — it sits inside text without breaking the line. <p> is for paragraphs — always use it for readable text content.

Text Formatting

<b>Bold text</b>
<strong>Important text (semantic)</strong>     <!-- Most Important -->
<i>Italic text</i>
<em>Emphasized text (semantic)</em>
<u>Underlined text</u>
<mark>Highlighted text</mark>
<small>Smaller text</small>
<time datetime="2026-01-27">Published on January 27, 2026</time>
<del>Deleted text</del>
<ins>Inserted text</ins>
<sub>Subscript</sub>                            <!-- H₂O, CO₂ -->
<sup>Superscript</sup>                          <!-- x², E=mc² -->

<strong> and <em> are semantic — they tell the browser and search engines that this text is important. <b> and <i> are just visual. Always prefer <strong> over <b> and <em> over <i>.

The <time> tag is good practice for SEO — use it on blogs and articles to represent specific dates.

Lists

Ordered List:

<ol>
  <li>First</li>
  <li>Second</li>
</ol>

You can control the style using type and start attributes:

<ol type="A" start="5">
  <li>Item</li>
</ol>

type="1" → 1, 2, 3 (default)
type="A" → A, B, C (uppercase)
type="a" → a, b, c (lowercase)
type="I" → I, II, III (Roman numerals uppercase)
type="i" → i, ii, iii (Roman numerals lowercase)
start="5" → starts from 5th value

Unordered List:

<ul>
  <li>First Item</li>
  <li>Second Item</li>
</ul>

Definition List:

<dl>
  <dt>What is HTML</dt>
  <dd>HTML is a HyperText Markup Language. It is the skeleton of the website.</dd>
</dl>

Media

<!-- Image — always use alt -->
<img src="image.jpg" alt="Description" width="300" height="200">

<!-- Audio -->
<audio controls>
  <source src="audio.mp3" type="audio/mpeg">
</audio>

<!-- Video — use track for captions -->
<video width="480" height="320" controls>
  <source src="video.mp4" type="video/mp4">
  <track src="captions.vtt">
</video>

<!-- Embed another website -->
<iframe src="https://www.example.com" width="600" height="400"></iframe>

<!-- PDF from Google Drive — always add /preview at the end -->
<iframe src="https://drive.google.com/file/d/FILE_ID/preview"></iframe>

Always use alt with images — it helps screen readers and improves SEO. For Google Drive PDFs, add /preview at the end of the URL or the PDF will not display correctly.

Tables

Tables have three main parts — <thead> for headings, <tbody> for data, and <tfoot> for totals or summary.

<table border="1">

  <colgroup>
    <col style="background-color: skyblue;">
    <col style="background-color: lightblue;">
    <col style="background-color: aquamarine;">
  </colgroup>

  <thead>
    <tr>
      <th>Column 1</th>
      <th colspan="2">Column 2</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td rowspan="2">Rowspan</td>
      <td>Data A</td>
      <td>Data B</td>
    </tr>
    <tr>
      <td>Data C</td>
      <td>Data D</td>
    </tr>
  </tbody>

  <tfoot>
    <tr>
      <td>Total</td>
      <td colspan="2">100</td>
    </tr>
  </tfoot>

</table>

colspan="2" makes a cell span across 2 columns horizontally. rowspan="2" makes a cell span across 2 rows vertically. <colgroup> only changes background colors — not the actual structure.

Links

<!-- External link — always use target="_blank" with rel="noopener" -->
<a href="https://www.example.com" target="_blank" rel="noopener">Visit Site</a>

<!-- Email link -->
<a href="mailto:support@example.com">Send Email</a>

<!-- Jump to section on same page -->
<a href="#section1">Jump to Section</a>
<h1 id="section1">Learn Coding</h1>

<!-- Google Drive direct download -->
<!-- https://drive.google.com/uc?export=download&id=FILE_ID -->

Always use rel="noopener" with target="_blank" — without it, the opened tab can access your page through window.opener. It is a security issue.

Forms

<form action="/action.php" method="post">

  <label for="username">Username:</label>
  <input type="text" id="username" name="username"><br>

  <input type="password" name="password" placeholder="Enter Password"><br>

  <input type="checkbox" name="subscribe" value="yes"> Subscribe<br>

  <input type="radio" name="gender" value="male"> Male
  <input type="radio" name="gender" value="female"> Female<br>

  <select name="country">
    <option value="us">USA</option>
    <option value="ca">Canada</option>
  </select><br>

  <textarea name="comments" rows="4" cols="30"></textarea><br>

  <input type="file" name="resume" multiple accept=".pdf,.doc,.docx,.jpg,.png"><br>

  <input type="number" name="age" min="18" max="100"><br>
  <input type="range" name="volume" min="0" max="100"><br>
  <input type="date" name="dob"><br>
  <input type="email" name="email"><br>
  <input type="url" name="website"><br>
  <input type="search" name="query"><br>

  <button type="submit">Submit</button>

</form>

multiple on file input lets users select more than one file. accept restricts which file types are allowed. min and max on number input set the allowed range.

Auto Suggestion Search — Datalist

HTML has a built-in autocomplete feature using <datalist> — no JavaScript needed.

<input type="text" list="fruits" placeholder="Type fruit name">

<datalist id="fruits">
  <option value="Apple">
  <option value="Banana">
  <option value="Mango">
  <option value="Orange">
  <option value="Grapes">
  <option value="Strawberry">
  <option value="Pineapple">
</datalist>

The list attribute on the input connects to the id of the datalist. As the user types, matching options appear automatically.

Semantic Elements

Semantic elements tell the browser and search engines what each part of the page means — not just how it looks.

<header>Page header</header>
<main>Main content</main>
<footer>Page footer</footer>
<nav>Navigation links</nav>
<section>Section of content</section>
<article>Independent article</article>
<aside>Sidebar content</aside>

Using <div> for everything works — but semantic elements are better for SEO and accessibility. Google understands <article> and <nav> better than a bunch of unnamed <div> tags.

You can find more of my work at abdulrdeveloper.me
Read more posts at blog.abdulrdeveloper.me

Chai aur SQL — A Beginner's Journey into Databases

Abdul Rahman — Sun, 24 May 2026 07:31:28 +0000

SQL stands for Structured Query Language. It is the language we use to talk to relational databases like MySQL, PostgreSQL, and SQLite.

What is a Server and Where Does the Database Live?

Most beginners think the database is somewhere floating in the "cloud." It is not.

A database is software running on a server. A server is just a powerful computer that runs 24/7 inside a data center — like Amazon AWS, Google Cloud, or DigitalOcean. When that software saves your data, it writes it to actual files on the server's SSD or hard drive.

MySQL and PostgreSQL save data as .ibd files or WAL (Write-Ahead Log) files. MongoDB saves as .bson files. These files sit on disk just like any file on your laptop.

When you write this in your backend code:

const pool = new Pool({ host: 'localhost', port: 5432 })

You are telling your backend to open a connection to the port where the database is listening. That is it. It is a TCP connection — like a phone call between your application and the database.

Why Docker for PostgreSQL?

Instead of installing PostgreSQL directly on my system, I used Docker. Docker creates an isolated environment (called a container) so I do not mess up my laptop's setup.

Here is the command I used to create a PostgreSQL container with persistent data:

Mac / Linux / WSL:

docker run -d --name sql_class \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=sql_class_01_db \
  -p 5433:5432 \
  -v "$(pwd)/pgdata:/var/lib/postgresql/data" \
  postgres

Windows (PowerShell):

docker run -d --name sql_class -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=sql_class_01_db -p 5433:5432 -v "${PWD}/pgdata:/var/lib/postgresql/data" postgres

The -v flag maps a pgdata folder inside your current directory to the container's storage. So even if the container is deleted, your data stays on your laptop.

To check if the container is running:

docker ps

To remove the container:

docker rm -f sql_class

Why Learn Raw SQL When We Have Prisma and Drizzle?

This was my first question. Why learn raw SQL when Prisma can generate the queries for me?

Here is what I found out:

ORMs like Prisma and Drizzle translate your JavaScript/TypeScript code into SQL strings and send them to the database. If you do not know SQL, you do not know what Prisma is actually doing behind the scene.

The N+1 problem is a real thing. ORMs sometimes generate 100 queries instead of 1 optimized query. You will only catch this if you understand SQL.

For complex queries — joining 6 tables, generating financial reports — ORM syntax breaks down. Developers fall back to raw SQL.

And SQL has not changed in 50 years. Sequelize came and went. Prisma came. Drizzle came. SQL is still the same.

DDL — How You Create Tables

DDL stands for Data Definition Language. These are the commands that define the structure of your database — not the data, just the structure.

The four main DDL commands are CREATE, ALTER, DROP, and TRUNCATE.

Here is a real table I created while practicing:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number CHAR(10) UNIQUE,
    age INT CHECK (age > 12),
    current_status VARCHAR(20) DEFAULT 'active'
        CHECK (current_status IN ('active', 'graduated', 'dropped_out', 'on_leave')),
    has_joined_masterji BOOLEAN DEFAULT FALSE,
    current_score NUMERIC(5, 2) CHECK (current_score >= 0 AND current_score <= 100),
    enrollment_date DATE DEFAULT CURRENT_DATE
);

SERIAL means auto-incrementing integer — PostgreSQL handles the ID for you. NOT NULL means the column cannot be empty. UNIQUE means no two rows can have the same value. CHECK is a custom condition — here age must be greater than 12. DEFAULT sets a value automatically if nothing is provided.

To add a new column to an existing table later:

ALTER TABLE students ADD COLUMN batch_name VARCHAR(50) DEFAULT 'Web Dev 2026';

SELECT Queries — Reading Your Data

SELECT is the command to read data from a table.

-- Get everything
SELECT * FROM ipl_players;

-- Get specific columns
SELECT name, team, auction_price_crores FROM ipl_players;

Filtering with WHERE:

-- Basic condition
SELECT * FROM ipl_players WHERE team = 'Mumbai Indians';

-- Multiple conditions
SELECT * FROM ipl_players WHERE role = 'All-Rounder' AND wickets_taken > 10;

-- Either condition
SELECT * FROM ipl_players WHERE team = 'CSK' OR team = 'RCB';

-- Range
SELECT * FROM ipl_players WHERE auction_price_crores BETWEEN 5 AND 12;

-- From a list
SELECT * FROM ipl_players WHERE team NOT IN ('Mumbai Indians', 'CSK', 'RCB');

-- Pattern matching — names starting with R
SELECT * FROM ipl_players WHERE name LIKE 'R%';

-- Check for empty values
SELECT * FROM ipl_players WHERE team IS NULL;

% means zero or more characters. _ means exactly one character. LIKE is case-sensitive. ILIKE is case-insensitive (PostgreSQL only).

Sorting and limiting:

-- Sort by price, highest first
SELECT name, auction_price_crores FROM ipl_players ORDER BY auction_price_crores DESC;

-- Top 3 results
SELECT name, auction_price_crores FROM ipl_players ORDER BY auction_price_crores DESC LIMIT 3;

-- Skip first 3, get next 3 (pagination)
SELECT name, auction_price_crores FROM ipl_players ORDER BY auction_price_crores DESC LIMIT 3 OFFSET 3;

DML — Insert, Update, Delete

DML stands for Data Manipulation Language. This is how you actually work with the data inside the tables.

INSERT:

-- Single row
INSERT INTO canteen_menu (item_name, category, price)
VALUES ('Vada Pav', 'Snacks', 15);

-- Multiple rows at once
INSERT INTO canteen_menu (item_name, category, price) VALUES
('Masala Chai', 'Beverages', 10),
('Samosa', 'Snacks', 12),
('Maggi', 'Snacks', 25);

UPDATE:

UPDATE canteen_menu
SET price = 20
WHERE item_name = 'Vada Pav';

DELETE:

DELETE FROM canteen_menu WHERE item_name = 'Cold Coffee';

Always use WHERE with UPDATE and DELETE. If you forget the WHERE clause, SQL will update or delete every single row in the table. I learned this the hard way in practice.

Aggregate Functions — COUNT, SUM, AVG, MAX, MIN

Aggregate functions take a whole column and return one number.

-- How many rows?
SELECT COUNT(*) FROM smart_watch_sales;

-- Total revenue
SELECT SUM(units_sold * price_per_unit) AS total_revenue FROM smart_watch_sales;

-- Average price
SELECT AVG(price_per_unit) AS avg_price FROM smart_watch_sales;

-- Cheapest and most expensive
SELECT MIN(price_per_unit) AS cheapest, MAX(price_per_unit) AS costliest FROM smart_watch_sales;

GROUP BY lets you split these calculations by category:

-- Total units sold per brand
SELECT brand, SUM(units_sold) AS total_units_sold
FROM smart_watch_sales
GROUP BY brand
ORDER BY total_units_sold DESC;

HAVING filters the groups after they are created. WHERE cannot do this because WHERE runs before grouping:

-- Only show brands that sold more than 20 units total
SELECT brand, SUM(units_sold) AS total_units
FROM smart_watch_sales
GROUP BY brand
HAVING SUM(units_sold) > 20;

Joins — Connecting Multiple Tables

This is where relational databases get powerful.

We keep data in separate tables to avoid duplication. Students in one table, internships in another. Joins let you combine them in one query.

Foreign Key is what connects the tables. The internships table has a student_id column that references the student_id in the students table:

CREATE TABLE internships (
    internship_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id) ON DELETE SET NULL,
    company_name VARCHAR(100),
    role VARCHAR(50),
    stipend INT,
    status VARCHAR(20)
);

ON DELETE SET NULL means if a student is deleted, the internship record stays but student_id becomes NULL. ON DELETE CASCADE would delete the internship too.

INNER JOIN — only matching rows:

SELECT s.name, s.branch, i.company_name, i.role
FROM students s
INNER JOIN internships i ON s.student_id = i.student_id;

Students without internships will not show up here.

LEFT JOIN — all students, matched or not:

SELECT
    s.name,
    s.branch,
    COALESCE(i.company_name, 'No Internship') AS company_name,
    COALESCE(i.stipend, 0) AS stipend
FROM students s
LEFT JOIN internships i ON s.student_id = i.student_id;

COALESCE replaces NULL with a default value. Students without internships will appear with 'No Internship' instead of NULL.

To find students who have NOT applied anywhere:

SELECT s.name, s.email
FROM students s
LEFT JOIN internships i ON s.student_id = i.student_id
WHERE i.internship_id IS NULL;

RIGHT JOIN returns all rows from the right table. In practice, developers rarely use RIGHT JOIN — they just flip the table order and use LEFT JOIN instead. It reads more naturally.

FULL OUTER JOIN — everything from both tables:

SELECT s.name AS student_name, i.company_name
FROM students s
FULL OUTER JOIN internships i ON s.student_id = i.student_id;

This shows all students and all internships. Where there is no match on either side, you get NULL.

Indexes — Making Queries Fast

I inserted 1,000,000 rows into a table to test this.

Without an index, when you search by name, PostgreSQL scans every single row from top to bottom. This is called a Sequential Scan.

EXPLAIN ANALYZE SELECT marks FROM marks WHERE name = '809E15792322';

EXPLAIN ANALYZE shows you the query plan and execution time. Before adding an index, my query took around 40-50 milliseconds on 1 million rows.

After creating an index:

CREATE INDEX idx_name ON marks (name);

The same query dropped to under 1 millisecond. PostgreSQL now uses a B-Tree structure to find the row directly instead of scanning everything.

A covering index goes one step further. If your query asks for marks along with name, you can store both in the index itself:

CREATE INDEX idx_name ON marks (name) INCLUDE (marks);

Now PostgreSQL does not even need to visit the main table. It gets everything from the index directly. This is called an Index Only Scan.

Transactions and ACID

A transaction is a way to group multiple SQL operations into one unit of work. Either all of them succeed, or none of them do.

The classic example is a bank transfer:

BEGIN;

    UPDATE accounts SET balance = balance - 500 WHERE owner = 'Shubham';
    UPDATE accounts SET balance = balance + 500 WHERE owner = 'Hitesh';

COMMIT;

If the server crashes between the two UPDATE statements, the database will rollback both changes. The money will not disappear from Shubham's account and fail to reach Hitesh.

If something goes wrong, you can manually undo everything:

BEGIN;
    UPDATE accounts SET balance = balance - 5000 WHERE owner = 'Shubham';
ROLLBACK;

ACID is the set of guarantees that make this work:

Atomicity — All or nothing. If Step 2 fails, Step 1 is also undone. I tested this by putting SELECT 1/0 (division by zero error) in the middle of a transaction. The whole thing rolled back.

Consistency — The database only moves from one valid state to another. I set CHECK (balance >= 0) on the accounts table. When I tried to deduct 5000 from an account with 1000, PostgreSQL threw an error and refused the operation completely.

Isolation — Uncommitted transactions are invisible to other connections. I opened two terminal windows to test this. Terminal 1 started a transaction and updated a balance but did not COMMIT. Terminal 2 could still see the old balance. Only after Terminal 1 ran COMMIT did Terminal 2 see the new number.

Durability — Once committed, data is permanent. PostgreSQL writes committed transactions to the Write-Ahead Log (WAL) on disk before returning a success response. Even if the server crashes one second after the COMMIT, the data will be there when the server restarts.

All the code from this blog is in my GitHub repo: github.com/abdulrdeveloper/Learn-SQL

You can find more of my work at abdulrdeveloper.me
Read more posts at blog.abdulrdeveloper.me