DEV Community: Anusha Renangi

Database Indexing – Part 5: Sequential Scan vs. Index Scan vs. Bitmap Index Scan in PostgreSQL

Anusha Renangi — Sun, 25 Oct 2026 14:23:19 +0000

In my previous articles, we explored different types of indexes in PostgreSQL.

But creating an index doesn't mean PostgreSQL will always use it.

Depending on the query and the number of matching records, PostgreSQL may choose a Sequential Scan, an Index Scan, or a Bitmap Index Scan.

In this article, we'll understand how these scan methods work using practical examples and PostgreSQL execution plans.

Understanding Scan Methods with an Example

Let's use a students table to understand when PostgreSQL chooses a Sequential Scan, Index Scan, or Bitmap Index Scan.

1. Creating the Table

CREATE TABLE students (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    grades INTEGER
);

Since id is the primary key, PostgreSQL automatically creates an index on it.

2. Inserting Records

Let's insert 1 million student records.

INSERT INTO students (name, grades)
SELECT
    'Student_' || g,
    (g % 100) + 1
FROM generate_series(1, 1000000) AS g;

The grades column contains values from 1 to 100.

3. Creating an Index on Grades

CREATE INDEX idx_students_grades
ON students (grades);

Now, we have two indexes:

Primary key index: On id
Separate index: On grades

4. Updating Table Statistics

Before running our experiments, let's understand why updating table statistics is important.

PostgreSQL uses statistics to estimate how many records a query might return. Based on these estimates, it decides whether scanning the table or using an index would be more efficient.

We can check the statistics using:

SELECT
    relname,
    n_live_tup,
    n_mod_since_analyze,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'students';

Here:

n_live_tup: Estimated number of live records.
n_mod_since_analyze: Estimated number of records modified since the last analysis.
last_analyze: Last time statistics were updated manually.
last_autoanalyze: Last time statistics were updated automatically.

Before ANALYZE:

We can see that n_mod_since_analyze is 1,000,000, meaning PostgreSQL has recorded approximately 1 million modifications since the last analysis.

Now, let's update the statistics manually.

ANALYZE students;

After ANALYZE:

After running ANALYZE, we can observe that:

n_mod_since_analyze changed from 1,000,000 to 0.
last_analyze was updated with the execution time.
PostgreSQL refreshed the statistics used for estimating query costs.

PostgreSQL also performs this automatically through auto-analyze. We ran it manually here to understand how the statistics change.

Refer more here

Now that our table and indexes are ready, let's run a few queries and observe which scan methods PostgreSQL chooses.

1. Index Scan

Let's start with a query that retrieves a small number of records from our students table.

EXPLAIN (ANALYZE, BUFFERS)
SELECT name
FROM students
WHERE id < 100;

Execution Plan:

PostgreSQL chose an Index Scan using students_pkey, the primary key index on id.

How Does an Index Scan Work?

PostgreSQL searches the index to find entries where id < 100.
Each matching index entry contains a reference to the corresponding row in the table (heap).
PostgreSQL uses these references to fetch the rows from the heap and retrieve the name values.
This process continues until all matching records are retrieved.

Since we're selecting name, which is not stored in the primary key index, PostgreSQL needs to visit the heap to retrieve it.

Why Did PostgreSQL Choose an Index Scan?

Our table contains 1 million records, but this query needs only 99.

Instead of scanning the entire table, PostgreSQL uses the primary key index to locate the matching records.

An Index Scan is generally useful when a query retrieves a small number of records, especially when the required rows can be found through an index without scanning most of the table.

2. Sequential Scan

Now, let's change the condition to retrieve most of the records from our students table.

EXPLAIN (ANALYZE, BUFFERS)
SELECT name
FROM students
WHERE id > 100;

Execution Plan:

This time, PostgreSQL chose a Sequential Scan (Seq Scan), even though we already have an index on id.

How Does a Sequential Scan Work?

PostgreSQL reads the table pages sequentially.
It checks each row against the condition id > 100.
If the row matches, PostgreSQL retrieves its name.
This continues until the entire table has been scanned.

Why Didn't PostgreSQL Use the Index?

Our table contains 1 million records, and this query returns almost all of them.

If PostgreSQL used an Index Scan, it would need to find the matching entries in the index and then visit the corresponding heap rows to retrieve their names.

Since almost every row matches, repeatedly accessing the table through index entries would involve unnecessary work.

Instead, PostgreSQL chose to scan the table directly.

A Sequential Scan can be more efficient than an Index Scan when a query needs to retrieve a large portion of the table.

3. Bitmap Index Scan

Now, let's filter students whose grades are greater than 95.

EXPLAIN (ANALYZE, BUFFERS)
SELECT name
FROM students
WHERE grades > 95;

Execution Plan:

PostgreSQL used a Bitmap Index Scan on idx_students_grades, followed by a Bitmap Heap Scan.

How Does a Bitmap Index Scan Work?

In a regular Index Scan, PostgreSQL uses the index to locate matching rows and fetches them from the heap.

In a Bitmap Index Scan, PostgreSQL first collects the locations of all matching rows into a bitmap. The Bitmap Heap Scan then uses this information to fetch the required heap pages.

Let's take a small example.

Each index entry contains a TID (Tuple Identifier), which identifies a row using its heap page number and tuple offset.

Suppose the index finds these matching locations:

Page 10 → Tuples [2, 5, 8]
Page 20 → Tuples [3, 7]
Page 30 → Tuple [4]

PostgreSQL stores these locations in a bitmap and processes the heap pages in page order.

When Page 10 is fetched into memory, the entire page (usually 8 KB) is loaded, including all the tuples stored on it.

However, PostgreSQL already knows that only tuples 2, 5, and 8 matched the index condition. So it directly accesses those tuples, checks their visibility, and retrieves the required values.

This is called an exact bitmap.

PostgreSQL can also combine multiple indexes using BitmapAnd and BitmapOr when queries have multiple filtering conditions.

I've explained both methods with practical examples in my previous article: Database Indexing – Part 3: Understanding Composite Indexes in PostgreSQL.

Why Did PostgreSQL Choose a Bitmap Index Scan?

Our table contains 1 million records, and this query returns 50,000 records.

Using a regular Index Scan could involve repeatedly accessing heap pages for individual matching rows.

A Sequential Scan would need to scan the entire table.

A Bitmap Index Scan allows PostgreSQL to collect the matching row locations first and then access the required heap pages in page order, reducing unnecessary page accesses.

Understanding Recheck Cond and Lossy Bitmap Pages

If we look at our execution plan, we can see:

Recheck Cond: (grades > 95)
Heap Blocks: exact=7352

Here, exact=7352 means PostgreSQL had the exact matching tuple locations for all 7,352 heap pages.

But storing all these tuple locations requires memory.

When there isn't enough memory, PostgreSQL can convert some exact bitmap entries into lossy entries.

Instead of remembering the matching tuple offsets:

Page 10 → Tuples [2, 5, 8]

It remembers only the page:

Page 10 → May contain matching tuples

When PostgreSQL fetches this page, it has to examine the candidate tuples and check the condition grades > 95 again, since the bitmap no longer contains the exact matching offsets.

This is where Recheck Cond is used.

Let's try this practically by reducing work_mem to 64 KB.

BEGIN;

SET LOCAL work_mem = '64kB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT name
FROM students
WHERE grades > 95;

ROLLBACK;

Execution Plan:

This time, PostgreSQL used a Parallel Bitmap Heap Scan.

Recheck Cond: (grades > 95)
Rows Removed by Index Recheck: 288507
Heap Blocks: exact=209 lossy=1660

Here:

exact=209: Heap-block accesses with exact tuple-location information.
lossy=1660: Heap-block accesses where only page-level information was available.
Rows Removed by Index Recheck: 288507: Candidate rows that PostgreSQL rejected after evaluating the condition again.

Since this was a parallel scan, the heap-block counts shown are not necessarily the total number of distinct pages processed by all workers.

With sufficient memory, PostgreSQL can remember the exact matching tuple locations. When memory is limited, it may retain only page locations and recheck the filtering condition while scanning the heap.

PostgreSQL doesn't always use an index just because one is available. It chooses a scan method based on what it estimates will be most efficient for the query.

In the next article, we'll explore how PostgreSQL's query optimizer makes these decisions.

Thanks for reading! I hope this article helped you understand how different scan methods work in PostgreSQL.

Database Indexing – Part 4: Covering vs. Composite Indexes in PostgreSQL – Which One Should You Use?

Anusha Renangi — Fri, 23 Oct 2026 15:29:47 +0000

In my previous articles, we explored covering indexes and composite indexes separately.

Both indexes can improve query performance, but how do they differ in terms of storage, INSERT, UPDATE, and SELECT performance?

To understand this, I tested both index types in PostgreSQL using two different datasets: one with unique values in the first indexed column and another with duplicate values.

In this article, we'll compare the results of these experiments and understand when to use covering and composite indexes in real-world applications.

Experiment 1: Covering vs. Composite Indexes with Unique Key Values

To compare covering and composite indexes, we'll perform a few experiments in PostgreSQL using 10 million records.

We'll use two separate tables with identical structures and data, so we can test both index types independently.

1. Creating the Tables

CREATE TABLE documents_covering (
    document_id BIGSERIAL PRIMARY KEY,
    document_key VARCHAR(100),
    document_version VARCHAR(200)
);

CREATE TABLE documents_composite (
    document_id BIGSERIAL PRIMARY KEY,
    document_key VARCHAR(100),
    document_version VARCHAR(200)
);

2. Inserting 10 Million Records

First, let's populate the covering-index table.

INSERT INTO documents_covering (document_key, document_version)
SELECT
    'DOC_' || LPAD(g::TEXT, 96, '0'),
    'VERSION_' || LPAD(g::TEXT, 192, '0')
FROM generate_series(1, 10000000) AS g;

Now, copy the same records into the composite-index table.

INSERT INTO documents_composite
    (document_id, document_key, document_version)
SELECT document_id, document_key, document_version
FROM documents_covering;

3. Creating the Indexes

Covering Index:

CREATE INDEX idx_documents_covering
ON documents_covering (document_key)
INCLUDE (document_version);

Composite Index:

CREATE INDEX idx_documents_composite
ON documents_composite (document_key, document_version);

Finally, update the table statistics.

ANALYZE documents_covering;
ANALYZE documents_composite;

Both tables now contain the same 10 million records, with different index configurations.

We'll compare their storage usage, SELECT, INSERT, and UPDATE performance to understand how these indexes behave.

1. Storage Comparison

Let's start by comparing how much storage each index occupies after indexing 10 million records.

Query 1: Comparing Index Sizes

We can use PostgreSQL's pg_relation_size() function to check the storage occupied by each index.

SELECT
    'Covering Index' AS index_type,
    pg_size_pretty(pg_relation_size('idx_documents_covering')) AS index_size,
    pg_relation_size('idx_documents_covering') AS size_bytes
UNION ALL
SELECT
    'Composite Index',
    pg_size_pretty(pg_relation_size('idx_documents_composite')),
    pg_relation_size('idx_documents_composite');

Results:

Both indexes occupied exactly the same storage space: 3,625 MB (3,801,243,648 bytes).

Query 2: Comparing Index Pages

Next, let's check the number of pages occupied by each index using PostgreSQL's pg_class system catalog.

SELECT
    relname AS index_name,
    relpages AS total_pages
FROM pg_class
WHERE relname IN (
    'idx_documents_covering',
    'idx_documents_composite'
);

Results:

Why Are Their Sizes the Same?

PostgreSQL uses a B-tree structure that works similarly to a B+ tree.

In PostgreSQL, B-tree indexes contain two main types of pages:

Leaf pages: Store the actual index entries containing indexed values and references to table rows.
Internal pages: Store separator keys and pointers that help PostgreSQL navigate to the correct leaf pages.

You can read more about this in the PostgreSQL B-tree Implementation documentation.

PostgreSQL also mentions that typically more than 99% of B-tree pages are leaf pages.

Both covering and composite indexes store their indexed column values in leaf entries. The difference is in how PostgreSQL handles those columns in internal pages.

Covering Index

In a covering index, additional columns are included using INCLUDE.

These columns are stored in leaf entries for data retrieval, but they are not needed for searching or navigating the B-tree.

So, PostgreSQL completely excludes included columns from internal navigation entries.

Composite Index

In a composite index, all columns are key columns and can participate in searching and sorting.

However, PostgreSQL has an optimization called suffix truncation.

Suffix truncation removes unnecessary trailing key columns from internal B-tree entries.

For example, if the first indexed column is enough to distinguish two page ranges, PostgreSQL doesn't need to store the second column in the corresponding internal separator.

You can read about this in the PostgreSQL documentation on suffix truncation.

In our case, document_key contains unique values, so the second column is generally unnecessary for distinguishing page ranges.

Since both indexes store the same column values in their leaf entries, and their internal entries can also be similarly compact, their storage requirements can be very close.

In our experiment, both indexes occupied exactly 3,625 MB across 464,019 pages.

2. SELECT Performance Comparison

Next, let's compare how covering and composite indexes perform when retrieving records.

We'll select 6,500 records using document_key and retrieve both document_key and document_version.

We'll execute each query twice to compare the initial run and the warm-cache run.

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_key, document_version
FROM documents_covering
WHERE document_key BETWEEN
    'DOC_' || LPAD('100000'::TEXT, 96, '0')
    AND
    'DOC_' || LPAD('106499'::TEXT, 96, '0');

Initial Run:

Warm-Cache Run:

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_key, document_version
FROM documents_composite
WHERE document_key BETWEEN
    'DOC_' || LPAD('100000'::TEXT, 96, '0')
    AND
    'DOC_' || LPAD('106499'::TEXT, 96, '0');

Initial Run:

Warm-Cache Run:

Results Comparison

Metric	Covering Index	Composite Index
Initial execution time	25.255 ms	29.351 ms
Warm-cache execution time	26.493 ms	24.660 ms
Rows returned	6,500	6,500
Initial shared-buffer reads	301	301
Warm-cache shared-buffer hits	301	301
Heap fetches	0	0

Observations

Both indexes used an Index Only Scan, meaning PostgreSQL retrieved the required column values directly from the indexes.

Both also reported 0 heap fetches, so PostgreSQL didn't need to visit the table rows to check their visibility.

During the initial runs, both indexes reported 301 shared-buffer reads. During the warm-cache runs, those became 301 shared-buffer hits, as the required index pages were already available in PostgreSQL's shared buffers.

The execution times were nearly identical, with small variations between runs.

For this SELECT query, neither index showed a clear performance advantage.

3. INSERT Performance Comparison

Next, let's compare INSERT performance between covering and composite indexes.

Both tables already contain 10 million records. We'll insert another 100,000 records into each table and measure the execution time using EXPLAIN (ANALYZE, BUFFERS).

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO documents_covering
    (document_id, document_key, document_version)
SELECT
    g,
    'DOC_' || LPAD(g::TEXT, 96, '0'),
    'VERSION_' || LPAD(g::TEXT, 192, '0')
FROM generate_series(10000001, 10100000) AS g;

Execution Result:

Execution time: 4,814.892 ms

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO documents_composite
    (document_id, document_key, document_version)
SELECT
    g,
    'DOC_' || LPAD(g::TEXT, 96, '0'),
    'VERSION_' || LPAD(g::TEXT, 192, '0')
FROM generate_series(10000001, 10100000) AS g;

Execution Result:

Execution time: 4,681.164 ms

Results Comparison

Index Type	Records Inserted	Execution Time
Covering Index	100,000	4,814.892 ms
Composite Index	100,000	4,681.164 ms

Observations

Both indexes showed similar INSERT performance, with the composite index completing around 134 ms faster in this run.

When inserting records, PostgreSQL needs to write the new rows to the table and create the corresponding entries in its indexes.

Both index types store the same column values in their leaf entries, so the amount of index maintenance can be similar.

These tests were performed after deleting and vacuuming previously inserted records, so existing free space and index maintenance may also affect the results.

We didn't observe a significant performance difference between covering and composite indexes in this INSERT test.

4. UPDATE Performance Comparison

Next, let's compare how covering and composite indexes handle updates.

We'll update document_version for 100,000 existing records in each table.

In the covering index, document_version is an included column, while in the composite index, it's a key column.

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS)
UPDATE documents_covering
SET document_version = 'UPDATED_' || LPAD(document_id::TEXT, 190, '0')
WHERE document_id BETWEEN 1 AND 100000;

Execution Result:

Execution time: 5,165.132 ms

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS)
UPDATE documents_composite
SET document_version = 'UPDATED_' || LPAD(document_id::TEXT, 190, '0')
WHERE document_id BETWEEN 1 AND 100000;

Execution Result:

Execution time: 5,691.076 ms

Results Comparison

Index Type	Records Updated	Execution Time
Covering Index	100,000	5,165.132 ms
Composite Index	100,000	5,691.076 ms

Observations

Both indexes showed relatively similar UPDATE performance, with the covering index completing around 526 ms faster in this test.

When an indexed column is updated, PostgreSQL needs to maintain the corresponding index entries.

This applies to both index types:

Covering Index: Updating an included column requires index maintenance.
Composite Index: Updating a key column also requires index maintenance.

Normally, PostgreSQL can sometimes perform a HOT (Heap-Only Tuple) update, avoiding the need to create new index entries.

However, updating a column referenced by a regular B-tree index prevents HOT updates, whether the column is a key or an included column. You can read about this in the PostgreSQL HOT Updates documentation.

In our experiment, both indexes required index maintenance, and neither showed a clear, consistent performance advantage based on this single test.

Experiment 2: Covering vs. Composite Indexes with Duplicate Key Values

In our first experiment, document_key contained unique values, and both indexes showed almost identical storage usage and similar performance.

Now, let's see what happens when the first indexed column contains duplicate values.

We'll create two new tables with 1 million records, where each document_key repeats 10,000 times, while document_version remains unique.

Setting Up the Experiment

1. Creating the Tables

CREATE TABLE documents_duplicate_covering (
    document_id BIGINT,
    document_key VARCHAR(100),
    document_version VARCHAR(200)
);

CREATE TABLE documents_duplicate_composite (
    document_id BIGINT,
    document_key VARCHAR(100),
    document_version VARCHAR(200)
);

2. Inserting 1 Million Records

First, insert the records into the covering-index table.

INSERT INTO documents_duplicate_covering
    (document_id, document_key, document_version)
SELECT
    g,
    'DOC_' || LPAD((g % 100)::TEXT, 96, '0'),
    'VERSION_' || LPAD(g::TEXT, 192, '0')
FROM generate_series(1, 1000000) AS g;

Copy the same data into the composite-index table.

INSERT INTO documents_duplicate_composite
    (document_id, document_key, document_version)
SELECT document_id, document_key, document_version
FROM documents_duplicate_covering;

3. Creating the Indexes

Covering Index:

CREATE INDEX idx_duplicate_covering
ON documents_duplicate_covering (document_key)
INCLUDE (document_version);

Composite Index:

CREATE INDEX idx_duplicate_composite
ON documents_duplicate_composite (document_key, document_version);

Both tables now contain identical data, but their indexes handle document_version differently.

1. Storage Comparison

Let's first compare the index sizes after creating both indexes on 1 million records.

SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_relation_size(indexrelid) AS size_bytes
FROM pg_index
WHERE indexrelid IN (
    'idx_duplicate_covering'::regclass,
    'idx_duplicate_composite'::regclass
);

Results:

Index Type	Index Size
Covering Index	363 MB
Composite Index	376 MB

This time, the composite index occupied approximately 13 MB more storage than the covering index.

Unlike our first experiment, the leading indexed column now contains duplicate values.

In a covering index, PostgreSQL stores included columns only in leaf entries.

In a composite index, trailing key columns may also be needed in internal B-tree entries to distinguish page ranges when the leading column contains duplicate values.

Both indexes still store the same column values in their leaf entries, but their internal navigation entries can differ.

In our experiment, the composite index required slightly more storage.

2. SELECT Performance Comparison

Next, let's retrieve records using a document_key that appears multiple times.

Before running the queries, we updated the table statistics and visibility information.

VACUUM (ANALYZE) documents_duplicate_covering;
VACUUM (ANALYZE) documents_duplicate_composite;

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_key, document_version
FROM documents_duplicate_covering
WHERE document_key = 'DOC_' || LPAD('50', 96, '0');

Results:

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_key, document_version
FROM documents_duplicate_composite
WHERE document_key = 'DOC_' || LPAD('50', 96, '0');

Results:

Metric	Covering Index	Composite Index
Records returned	10,000	10,000
Execution time	2.846 ms	2.626 ms
Scan type	Index Only Scan	Index Only Scan
Heap fetches	0	0
Shared-buffer reads	458	459

Observations

Both indexes used an Index Only Scan, returning 10,000 records without fetching any rows from the table heap.

The execution times were almost identical, and both accessed approximately the same number of index pages.

Even with duplicate values in the first indexed column, we didn't observe a noticeable SELECT performance difference.

3. INSERT Performance Comparison

Now, let's insert another 100,000 records into each table while keeping duplicate values in document_key.

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO documents_duplicate_covering
    (document_id, document_key, document_version)
SELECT
    g,
    'DOC_' || LPAD((g % 100)::TEXT, 96, '0'),
    'VERSION_' || LPAD(g::TEXT, 192, '0')
FROM generate_series(1000001, 1100000) AS g;

Results:

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO documents_duplicate_composite
    (document_id, document_key, document_version)
SELECT
    g,
    'DOC_' || LPAD((g % 100)::TEXT, 96, '0'),
    'VERSION_' || LPAD(g::TEXT, 192, '0')
FROM generate_series(1000001, 1100000) AS g;

Results:

Metric	Covering Index	Composite Index
Records inserted	100,000	100,000
Execution time	2,892.511 ms	5,710.005 ms

Observations

This time, the covering index completed the INSERT in approximately 2.89 seconds, while the composite index took 5.71 seconds.

The composite-index test also performed more buffer reads and writes.

The difference was much larger than in our first experiment. However, these are single-run measurements, and cache conditions and disk activity can also affect the execution time.

4. UPDATE Performance Comparison

Finally, let's update document_version for 100,000 records in both tables.

Before the test, we ran:

VACUUM (ANALYZE) documents_duplicate_covering;
VACUUM (ANALYZE) documents_duplicate_composite;

Query 1: Covering Index

EXPLAIN (ANALYZE, BUFFERS)
UPDATE documents_duplicate_covering
SET document_version = 'UPDATED_' || LPAD(document_id::TEXT, 190, '0')
WHERE document_id BETWEEN 1 AND 100000;

Results:

Query 2: Composite Index

EXPLAIN (ANALYZE, BUFFERS)
UPDATE documents_duplicate_composite
SET document_version = 'UPDATED_' || LPAD(document_id::TEXT, 190, '0')
WHERE document_id BETWEEN 1 AND 100000;

Results:

Metric	Covering Index	Composite Index
Records updated	100,000	100,000
Execution time	3,288.368 ms	4,598.804 ms
Scan type	Sequential Scan	Sequential Scan

Observations

The covering index completed the UPDATE approximately 1.31 seconds faster than the composite index in this test.

Both queries used a Sequential Scan because these new tables didn't have an index on document_id.

Also, changing document_version required index maintenance in both cases, whether it was an included column or a composite-index key column.

The composite-index test recorded more buffer writes, which may have contributed to its longer execution time.

Comparison of Both Experiments

Experiment 1: Unique Values in the First Key Column

Operation	Covering Index	Composite Index
Storage (10 million records)	3,625 MB	3,625 MB
SELECT (6,500 records, warm cache)	26.493 ms	24.660 ms
INSERT (100,000 records)	4,814.892 ms	4,681.164 ms
UPDATE (100,000 records)	5,165.132 ms	5,691.076 ms

Experiment 2: Duplicate Values in the First Key Column

Operation	Covering Index	Composite Index
Storage (1 million records)	363 MB	376 MB
SELECT (10,000 records)	2.846 ms	2.626 ms
INSERT (100,000 records)	2,892.511 ms	5,710.005 ms
UPDATE (100,000 records)	3,288.368 ms	4,598.804 ms

In the first experiment, both indexes occupied the same storage space and showed similar SELECT, INSERT, and UPDATE performance.

In the second experiment, where the first indexed column contained duplicate values, the composite index occupied more storage. The covering index also completed the INSERT and UPDATE operations faster in our tests.

This shows why we should carefully decide which columns need to be index keys.

Which One Should You Choose: Composite Index or Covering Index?

In real-world applications, covering and composite indexes are not competing approaches. We can combine both in a single index.

Key columns: Keep columns required for filtering, joining, or sorting as index keys.
Non-key columns: Keep additional columns needed only for retrieving data in the INCLUDE clause.

Unnecessarily adding columns as index keys can waste storage, especially when the leading columns contain duplicate values.

PostgreSQL may need those additional key columns in internal B-tree entries. But columns added using INCLUDE are stored only in leaf entries, which can help reduce unnecessary storage.

At the same time, we shouldn't add too many columns to INCLUDE, because they still increase index size and require maintenance whenever their values change.

So, keep only the required columns as index keys, and use INCLUDE for additional columns needed only for retrieval. Choose both carefully based on your actual query requirements.

Thanks for reading! I hope this article helped you better understand covering and composite indexes in PostgreSQL.

Database Indexing – Part 3: Understanding Composite Indexes in PostgreSQL

Anusha Renangi — Wed, 21 Oct 2026 18:40:47 +0000

In PostgreSQL, we often come across queries that filter records based on multiple conditions.

Consider an employees table containing information such as employee ID, name, department, and salary.

Suppose we want to find employees working in the Engineering department who earn more than 50,000.

SELECT *
FROM employees
WHERE department = 'Engineering'
  AND salary > 50000;

This query looks simple, but what happens when the table contains millions of employee records?

PostgreSQL needs to identify the rows that satisfy both conditions. As the amount of data grows, finding those records efficiently becomes more important.

How can we optimize queries that filter on multiple columns?

In this article, we'll explore composite indexes in PostgreSQL and understand how they can help with such queries.

What Is a Composite Index?

A composite index, also called a multicolumn index, is an index created on two or more columns of a table.

In PostgreSQL, we can create a composite index using:

CREATE INDEX idx_employees_department_salary
ON employees (department, salary);

Here, we're creating a single index on two columns: department and salary.

Unlike separate indexes on each column, a composite index stores the values together in a specific order.

PostgreSQL organizes the index entries first by department, and then by salary within each department.

For example, consider these employee records:

employee_id	department	salary
1	Engineering	70000
2	HR	45000
3	Engineering	50000
4	Sales	60000
5	HR	55000
6	Engineering	90000

The composite index on (department, salary) organizes its entries in this order:

department	salary	Employee
Engineering	50000	3
Engineering	70000	1
Engineering	90000	6
HR	45000	2
HR	55000	5
Sales	60000	4

Notice that the employees are grouped by department. Within each department, their salaries are arranged in ascending order.

This ordering is important because PostgreSQL can use it to narrow down the records it needs to examine.

Also, column order matters. An index on (department, salary) is not organized in the same way as an index on (salary, department).

Understanding Index Behavior with an Example

Let's use an employees table to understand how PostgreSQL handles queries with multiple filtering conditions.

First, we'll create the table:

CREATE TABLE employees (
    employee_id BIGSERIAL PRIMARY KEY,
    employee_name TEXT,
    department TEXT,
    salary INTEGER
);

Now, let's insert 1 million employee records.

INSERT INTO employees (employee_name, department, salary)
SELECT
    'Employee_' || g,
    (ARRAY['Engineering', 'HR', 'Sales', 'Finance', 'Marketing'])[1 + (g % 5)],
    30000 + (g % 100) * 1000
FROM generate_series(1, 1000000) AS g;

We'll compare three index configurations:

Case 1: Two separate indexes on department and salary.
Case 2: A composite index on (department, salary), after removing the separate indexes.
Case 3: A composite index on (department, salary) along with a separate index on salary.

For each configuration, we'll execute the same four queries and observe the execution plans and performance.

Case 1: Creating Two Separate Indexes

Let's start by creating two individual indexes on the employees table.

CREATE INDEX idx_employees_department
ON employees (department);

CREATE INDEX idx_employees_salary
ON employees (salary);

Now, let's execute a few queries and see how PostgreSQL uses these indexes.

Query 1: Filtering by Department

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE department = 'Engineering';

Execution Plan:

PostgreSQL used a Bitmap Index Scan on idx_employees_department, followed by a Bitmap Heap Scan.

A Bitmap Index Scan first identifies the locations of matching records using the index. PostgreSQL then uses those locations to access the required heap pages through a Bitmap Heap Scan.

Here, the query returned 200,000 records by accessing 8,161 heap pages.

Execution Time: 123.040 ms

Query 2: Filtering by Salary

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE salary = 70000;

Execution Plan:

This time, PostgreSQL used idx_employees_salary.

The execution plan again contained a Bitmap Index Scan followed by a Bitmap Heap Scan.

The query returned 10,000 records, accessing 8,161 heap pages.

Execution Time: 130.689 ms

Query 3: Filtering Using AND

Now, let's filter employees using both department and salary.

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE department = 'Engineering'
  AND salary = 70000;

Execution Plan:

PostgreSQL used two Bitmap Index Scans, combined their results using BitmapAnd, and then performed a Bitmap Heap Scan.

How does BitmapAnd work?

PostgreSQL searches idx_employees_department and identifies 200,000 matching records.
It searches idx_employees_salary and identifies 10,000 matching records.
BitmapAnd combines the two bitmaps, keeping only the row locations that satisfy both conditions.
The Bitmap Heap Scan uses the resulting bitmap to fetch matching rows from the table.

For example, suppose the two indexes identify these employee IDs:

Department = Engineering → 1, 3, 5, 7
Salary = 70000          → 2, 3, 5, 8

BitmapAnd                 → 3, 5

These IDs are just an example to illustrate the intersection. PostgreSQL's bitmaps represent heap tuple locations, not employee IDs directly.

In our actual execution:

Rows returned: 10,000
Heap pages accessed: 8,161
Execution time: 117.610 ms

Notice that PostgreSQL scanned both indexes separately before combining their results. We will compare this behavior with a composite index in the next case.

Query 4: Filtering Using OR

Now, let's find employees who belong to the Engineering department or have a salary of 70,000.

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE department = 'Engineering'
   OR salary = 70000;

Execution Plan:

PostgreSQL used two Bitmap Index Scans, combined their results using BitmapOr, and then performed a Bitmap Heap Scan.

How does BitmapOr work?

PostgreSQL searches idx_employees_department and identifies 200,000 matching records.
It searches idx_employees_salary and identifies 10,000 matching records.
BitmapOr combines the two bitmaps, keeping row locations that match either condition.
The Bitmap Heap Scan visits the corresponding heap pages and retrieves the matching employee IDs.

For example:

Department = Engineering → 1, 3, 5, 7
Salary = 70000          → 2, 3, 5, 8

BitmapOr                  → 1, 2, 3, 5, 7, 8

PostgreSQL combines heap tuple locations rather than employee IDs directly.

Actual Results:

Rows returned: 200,000
Heap pages accessed: 8,161
Execution time: 126.784 ms

Notice that the query returned 200,000 records, not 210,000. In our generated dataset, all employees earning 70,000 belong to the Engineering department, so those records already satisfy the first condition.

PostgreSQL used both indexes and combined their results without returning duplicate rows.

Case 2: Creating a Composite Index

Now, let's remove the two separate indexes and create a composite index on department and salary.

DROP INDEX idx_employees_department;
DROP INDEX idx_employees_salary;

Create the composite index:

CREATE INDEX idx_employees_department_salary
ON employees (department, salary);

Let's run the same four queries and observe how PostgreSQL uses this index.

Query 1: Filtering by Department

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE department = 'Engineering';

Execution Plan:

PostgreSQL used a Bitmap Index Scan on idx_employees_department_salary, followed by a Bitmap Heap Scan.

Here's how it works:

PostgreSQL searches the composite index using department = 'Engineering'.
Since department is the first column in the index, PostgreSQL can directly locate the matching index entries without requiring a condition on salary.
The Bitmap Index Scan collects the matching heap tuple locations into a bitmap.
The Bitmap Heap Scan visits the corresponding heap pages, checks tuple visibility, and retrieves the employee IDs.

Results:

Rows returned: 200,000
Heap pages accessed: 8,161
Execution time: 130.189 ms

Even though the index contains two columns, PostgreSQL was able to use it with a condition on the first column alone.

Query 2: Filtering by Salary

Now, let's filter employees using only salary, which is the second column in our composite index (department, salary).

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE salary = 70000;

Execution Plan:

This time, PostgreSQL chose a Parallel Sequential Scan instead of using the composite index.

Rows returned: 10,000
Heap pages read: 8,161
Workers launched: 2
Execution time: 98.216 ms

PostgreSQL scanned the table using three processes (the main process and two parallel workers), checking the salary condition against the rows.

Why didn't PostgreSQL use the composite index?

Our composite index is created on:

CREATE INDEX idx_employees_department_salary
ON employees (department, salary);

PostgreSQL organizes the index entries first by department, then by salary within each department.

For example:

Department	Salary
Engineering	50000
Engineering	70000
Engineering	90000
Finance	60000
Finance	70000
HR	45000
HR	70000
Sales	55000
Sales	70000

Notice that employees earning 70,000 are spread across different departments in the index.

When we filter by department, PostgreSQL can directly locate the corresponding entries because it is the first indexed column.

But when we filter only by salary, the matching values are scattered across different departments. PostgreSQL cannot retrieve all matching entries through a single continuous range of the index.

This behavior is commonly described by the leftmost prefix rule.

An index on (department, salary) efficiently supports filtering by department.
It also supports filtering by both department and salary.
Filtering only by salary is generally less efficient because the leading column is missing.

PostgreSQL can sometimes use a composite index even without a condition on its first column, but whether that is worthwhile depends on the data and estimated query cost.

In our execution, PostgreSQL chose a Parallel Sequential Scan rather than using the composite index.

Query 3: Filtering Using AND

Now, let's filter employees using both department and salary.

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE department = 'Engineering'
  AND salary = 70000;

Execution Plan:

PostgreSQL used a Bitmap Index Scan on idx_employees_department_salary, followed by a Bitmap Heap Scan.

Unlike Case 1, where PostgreSQL used two separate indexes and combined their results using BitmapAnd, this time it used a single composite index.

Here's how it works:

PostgreSQL searches the composite index for entries where department = 'Engineering'.
Since salary values are ordered within each department, PostgreSQL can further narrow the search to entries where salary = 70000.
The Bitmap Index Scan collects the matching heap tuple locations into a bitmap.
The Bitmap Heap Scan visits the corresponding heap pages, checks tuple visibility, and retrieves the matching employee IDs.

Results:

Rows returned: 10,000
Heap pages accessed: 8,161
Execution time: 115.172 ms

PostgreSQL was able to use both filtering conditions directly within a single composite index.

Query 4: Filtering Using OR

Now, let's filter employees using either department or salary.

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE department = 'Engineering'
   OR salary = 70000;

Execution Plan:

This time, PostgreSQL chose a Sequential Scan instead of using the composite index.

Here's why:

Our composite index is created on (department, salary), where department is the first column.
PostgreSQL can efficiently search using department, but the OR condition also requires finding employees with salary = 70000, regardless of their department.
Since salary is the second column, its matching values are spread across different departments in the index.
PostgreSQL estimated that scanning the table and checking both conditions would be cheaper than using the composite index.

Results:

Rows returned: 200,000
Rows removed by filter: 800,000
Heap pages read: 8,161
Execution time: 138.981 ms

Unlike Case 1, where PostgreSQL combined two separate indexes using BitmapOr, here it performed a Sequential Scan because there was no separate index on salary.

Case 3: Composite Index with a Separate Salary Index

We have kept the composite index on (department, salary) and created an additional index on salary.

CREATE INDEX idx_employees_salary
ON employees (salary);

Now, let's run the same four queries again.

Query 1: Filtering by Department

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE department = 'Engineering';

Execution Plan:

PostgreSQL used a Bitmap Index Scan on idx_employees_department_salary, followed by a Bitmap Heap Scan.

Here's how it works:

PostgreSQL searches the composite index using department = 'Engineering'.
Since department is the first column in the index, PostgreSQL can locate the matching entries without requiring a condition on salary.
The Bitmap Index Scan collects the matching heap tuple locations.
The Bitmap Heap Scan visits the corresponding heap pages, checks tuple visibility, and retrieves the employee IDs.

Results:

Rows returned: 200,000
Heap pages accessed: 8,161
Execution time: 127.859 ms

PostgreSQL continued to use the composite index for this query. The separate salary index was not needed because the query filtered only on department.

Query 2: Filtering by Salary

Now, let's filter employees using only salary.

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE salary = 70000;

Execution Plan:

PostgreSQL used a Bitmap Index Scan on idx_employees_salary, followed by a Bitmap Heap Scan.

Here's how it works:

PostgreSQL searches the separate salary index for entries where salary = 70000.
The Bitmap Index Scan collects the matching heap tuple locations into a bitmap.
The Bitmap Heap Scan visits the corresponding heap pages, checks tuple visibility, and retrieves the matching employee IDs.

Results:

Rows returned: 10,000
Heap pages accessed: 8,161
Execution time: 12.315 ms

In Case 2, PostgreSQL chose a Parallel Sequential Scan because only the composite index (department, salary) was available.

Now that we have a separate index on salary, PostgreSQL can directly search for matching salary values without depending on the leading department column.

Query 3: Filtering Using AND

Now, let's filter employees using both department and salary.

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE department = 'Engineering'
  AND salary = 70000;

Execution Plan:

PostgreSQL used a Bitmap Index Scan on idx_employees_department_salary, followed by a Bitmap Heap Scan.

Even though we have a separate index on salary, PostgreSQL chose to use only the composite index.

Here's how it works:

PostgreSQL searches the composite index for entries where department = 'Engineering'.
Since salary values are ordered within each department, PostgreSQL can further narrow the search using salary = 70000.
The Bitmap Index Scan collects the matching heap tuple locations into a bitmap.
The Bitmap Heap Scan visits the corresponding heap pages, checks tuple visibility, and retrieves the matching employee IDs.

Results:

Rows returned: 10,000
Heap pages accessed: 8,161
Execution time: 94.164 ms

Unlike Case 1, where PostgreSQL combined two separate indexes using BitmapAnd, here it used a single composite index to apply both filtering conditions.

The separate salary index was not needed for this query.

Query 4: Filtering Using OR

Now, let's filter employees using either department or salary.

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id
FROM employees
WHERE department = 'Engineering'
   OR salary = 70000;

Execution Plan:

PostgreSQL used two Bitmap Index Scans, combined their results using BitmapOr, and then performed a Bitmap Heap Scan.

This time, PostgreSQL used both the composite index idx_employees_department_salary and the separate index idx_employees_salary.

Here's how it works:

PostgreSQL searches the composite index using department = 'Engineering' and identifies 200,000 matching records.
It searches the separate salary index using salary = 70000 and identifies 10,000 matching records.
BitmapOr combines both bitmaps, keeping the heap tuple locations that match either condition.
The Bitmap Heap Scan visits the corresponding heap pages, checks tuple visibility, and retrieves the matching employee IDs.

Results:

Rows returned: 200,000
Heap pages accessed: 8,161
Execution time: 63.308 ms

In Case 2, PostgreSQL performed a Sequential Scan for the same OR query because only the composite index was available.

With the additional salary index, PostgreSQL was able to use BitmapOr to combine both indexes and retrieve the matching records.

Comparing the Three Index Configurations

We executed the same four queries using different index configurations.

Query	Separate Indexes	Composite Index	Composite + Salary Index
Department	Bitmap Index Scan	Bitmap Index Scan	Bitmap Index Scan
Salary	Bitmap Index Scan	Parallel Seq Scan	Bitmap Index Scan
Department AND Salary	BitmapAnd	Bitmap Index Scan	Bitmap Index Scan
Department OR Salary	BitmapOr	Seq Scan	BitmapOr

Conclusion

Composite indexes are useful when queries frequently filter or sort using multiple columns.

Column order matters: The order of columns in a composite index determines how efficiently PostgreSQL can use it for searching and sorting.
Leftmost prefix rule: PostgreSQL generally uses composite indexes most efficiently when queries filter using the leading indexed columns. Filtering only by a non-leading column may be less efficient.
AND conditions: A composite index can use multiple filtering conditions in a single index scan, avoiding the need to combine separate indexes.
OR conditions: PostgreSQL can combine multiple indexes using BitmapOr when suitable indexes are available. Otherwise, it may choose a Sequential Scan.
Separate indexes: A composite index may not replace the need for individual indexes, especially when non-leading columns are frequently filtered independently.

Composite indexes can improve query performance when columns are indexed in the right order. However, no single index works best for every query, so choose indexes based on your most frequently used filtering and sorting conditions.

Database Indexing – Part 2: Understanding Covering Indexes in PostgreSQL

Anusha Renangi — Tue, 20 Oct 2026 15:34:37 +0000

Recently, while working on a performance optimization task, I came across an interesting problem.

I had a query where I needed to fetch document versions for around 6,500 document IDs.

Something like this:

SELECT document_id, document_version
FROM documents
WHERE document_id IN (101, 102, 103, ..., 6500);

This looks like a simple query, right?

But the entire operation was taking around 4 seconds. When I checked where the time was being spent, I noticed that this single database query was taking almost 85% of the total time.

That's around 3.4 seconds for just one database query!

So, I started looking for ways to reduce the query execution time.

My first thought was to create an index on both document_id and document_version.

CREATE INDEX idx_document_version
ON documents (document_id, document_version);

But I'm filtering only using document_id. I just need document_version in the result.

Also, document_version changes frequently, so I was concerned about the additional index maintenance during updates.

I wanted to know whether both columns really needed to be part of the index key.

PostgreSQL also has an INCLUDE option, where we can store additional columns in an index without making them key columns.

CREATE INDEX idx_document_version
ON documents (document_id) INCLUDE (document_version);

Both indexes contain the columns required by my query. But how are they different internally? Does using INCLUDE make any difference to query performance, index size, or updates?

Let's look at how both indexes work.

1. Understanding Covering Indexes

A covering index is an index that contains all the columns required by a query, so PostgreSQL may be able to return the result without fetching the actual row from the table.

For example, consider this query:

SELECT document_id, document_version
FROM documents
WHERE document_id = 500;

Here, we're using document_id to search and document_version to get the required value.

If we have an index only on document_id, PostgreSQL can use it to find the matching record. But it still needs to visit the heap to fetch document_version.

What if both values were already available in the index?

We could create an index like this:

CREATE INDEX idx_document_version
ON documents (document_id) INCLUDE (document_version);

Here, we have two types of columns.

1. Key Column — document_id

Used to search and navigate through the B-tree.
Determines the ordering of index entries.
Can be used for filtering and sorting.

2. Non-Key Column — document_version

Stored along with the key in the leaf pages.
Doesn't participate in B-tree searching or ordering.
Can be returned from the index without fetching that value from the heap.

How Are These Columns Stored?

PostgreSQL's B-tree has three types of pages: root, internal, and leaf pages.

In an index created using INCLUDE:

Root and internal pages contain navigation keys based on document_id and pointers to lower-level pages.
Leaf pages contain document_id, the included document_version, and a reference to the corresponding heap tuple.

So, PostgreSQL searches the B-tree using document_id and finds document_version in the matching leaf entry.

Since both required columns are available in the index, PostgreSQL may be able to return the result without fetching the actual row from the heap. This is called an Index Only Scan.

2. Testing Covering Indexes in PostgreSQL

Let's create a documents table with 10 million records and compare how PostgreSQL performs with a primary key index and an index using INCLUDE.

Step 1: Create the Table

CREATE TABLE documents (
    document_id BIGINT PRIMARY KEY,
    document_name TEXT,
    document_version INTEGER
);

Since document_id is the primary key, PostgreSQL automatically creates a B-tree index named documents_pkey.

Step 2: Insert 10 Million Records

INSERT INTO documents (
    document_id,
    document_name,
    document_version
)
SELECT
    g,
    'Document_' || g,
    (random() * 10)::INTEGER + 1
FROM generate_series(1, 10000000) AS g;

Now, our table contains 10 million documents.

Let's fetch document versions for 6,500 document IDs and compare both approaches.

Query 1: Using the Primary Key Index

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_id, document_version
FROM documents
WHERE document_id = ANY(
    ARRAY(
        SELECT generate_series(1, 6500)::BIGINT
    )
);

Output:

PostgreSQL used an Index Scan.

The primary key index contains document_id, but not document_version.

So PostgreSQL:

Searches the index for the matching document IDs.
Gets the corresponding heap tuple references.
Visits the heap to fetch document_version.

The query returned 6,500 records and took 11.028 ms.

Query 2: Creating an Index with INCLUDE

Now, let's create an index that also contains document_version.

CREATE INDEX idx_documents_covering
ON documents (document_id) INCLUDE (document_version);

Here, document_id is the key column, while document_version is a non-key column stored in the leaf pages.

Let's execute the same query again.

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_id, document_version
FROM documents
WHERE document_id = ANY(
    ARRAY(
        SELECT generate_series(1, 6500)::BIGINT
    )
);

Output:

This time, PostgreSQL used an Index Only Scan.

Since both document_id and document_version are available in the index, PostgreSQL was able to return the required values without visiting the heap in this execution.

Notice:

Heap Fetches: 0

The query took 9.144 ms.

Comparing the Execution Times

Metric	Primary Key Index	Index with INCLUDE
Scan type	Index Scan	Index Only Scan
Rows returned	6,500	6,500
Execution time	11.028 ms	9.144 ms

In this experiment, the execution time decreased from 11.028 ms to 9.144 ms, approximately a 17% improvement.

With the primary key index, PostgreSQL had to visit the heap to fetch document_version.

But with INCLUDE, both required columns were available in the index, and PostgreSQL avoided those heap fetches.

A Note on Caching

I tested both approaches on my local Ubuntu machine. Before each test, I restarted PostgreSQL and cleared the Linux filesystem cache using the following commands:

sudo systemctl stop postgresql
sync
echo 3 | sudo tee /proc/sys/vm/drop_caches
sudo systemctl start postgresql

These commands are only intended for a dedicated local test environment, not a production or shared server.

The covering index improved query performance in our experiment by avoiding heap fetches.

However, since we can't completely eliminate caching effects, it's difficult to say exactly how much improvement we can expect.

These results are from my local system, and the actual performance improvement may vary depending on the environment and workload.

3. Why Can an Index Only Scan Still Have Heap Fetches?

In our previous query, PostgreSQL used an Index Only Scan with Heap Fetches: 0.

But an Index Only Scan doesn't always mean PostgreSQL will completely avoid accessing the heap.

Let's update some records and check.

UPDATE documents
SET document_version = document_version + 1
WHERE document_id BETWEEN 1 AND 6500;

Now, let's execute the same query again.

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_id, document_version
FROM documents
WHERE document_id = ANY(
    ARRAY(
        SELECT generate_series(1, 6500)::BIGINT
    )
);

Output:

PostgreSQL still used an Index Only Scan, but this time:

Heap Fetches: 13000

We updated 6,500 records, but PostgreSQL performed 13,000 heap fetches.

Why?

How Does PostgreSQL Check Row Visibility?

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to manage updates and transactions.

When we update a row, PostgreSQL creates a new row version instead of overwriting the existing one.

The index contains the values we need, but it doesn't have enough information to determine whether a row version is visible to the current transaction.

For this, PostgreSQL maintains a Visibility Map.

The visibility map keeps track of which heap pages contain only row versions that are visible to all transactions.

If a heap page is marked all-visible, PostgreSQL can return the required values from the index without checking the heap.
If a heap page isn't marked all-visible, PostgreSQL needs to visit the heap to check row visibility.

What Happened After the UPDATE?

When we updated those 6,500 records, PostgreSQL created new row versions and cleared the all-visible flags for the affected heap pages.

Since document_version is included in our index, the update also required new index entries.

During the next Index Only Scan, PostgreSQL had to check the heap for row visibility.

That's why we got:

Heap Fetches: 13000

These are heap tuple fetch attempts, not 13,000 different heap pages.

What About VACUUM?

PostgreSQL provides VACUUM to clean up dead tuples so their space can be reused. It also updates the visibility map when pages qualify as all-visible.

VACUUM (ANALYZE) documents;

This can help reduce heap fetches during Index Only Scans, although it doesn't guarantee that they will become zero.

We'll cover how VACUUM works internally in a separate article.

Conclusion

Covering indexes can help reduce heap access when all the required columns are available in the index.

But indexes also come with a cost.

Even though INCLUDE columns are not part of the index key, PostgreSQL still needs to store and maintain their values. If an included column is updated frequently, it can increase write overhead.

So, we need to consider both read and write performance before creating additional indexes.

In the next article, we'll look at composite indexes, how column order affects index usage, and why the order of columns matters when filtering and sorting records.

That's it for now!

Thanks for reading! 😊

Database Indexing – Part 1: How Indexes Help Find Data Faster

Anusha Renangi — Sat, 17 Oct 2026 15:10:09 +0000

Recently, I was reading two books: Good Vibes, Good Life and The Mountain Is You.

I usually forget things after some time 😅. So whenever I want to revisit something I've read, I go back to the book.

But I noticed one difference between these two books.

Good Vibes, Good Life has an index at the end of the book. Whenever I want to read about a particular topic, I can look at the index, find the page number, and directly go to that page.

But The Mountain Is You doesn't have that kind of index.

So if I remember a topic but don't remember where I read it, I have to go through the chapters one by one until I find it.

That takes more time, right?

The same thing happens in databases.

Imagine we have a table containing millions of records, and we want to find one particular record.

Without an index, the database may need to scan the entire table, checking rows to find the data we're looking for.

But if we have a suitable index, the database can use it to quickly locate the required record instead of checking every row.

This is why we use indexes in databases.

An index is a separate data structure that helps the database find records efficiently, just like the index in a book helps us find a topic without going through every page.

Before moving forward, check out my previous article, Database Storage Concepts – How Tables Are Stored on Disk, to understand how databases store data internally.

Now, let's see how indexing actually works in PostgreSQL.

Creating a Table with an Index and 10 Million Records

First, let's create an employees table with three columns: id, name, and salary.

Step 1: Create the table

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary INTEGER
);

Here, we defined id as the PRIMARY KEY.

In PostgreSQL, whenever we define a primary key, it automatically creates a unique B-tree index on that column.

In our case, PostgreSQL created an index named employees_pkey on the id column.

Step 2: Insert 10 million records

INSERT INTO employees (id, name, salary)
SELECT
    g,
    'Employee_' || g,
    30000 + (random() * 70000)::INTEGER
FROM generate_series(1, 10000000) AS g;

We now have a table containing 10 million employee records.

Let's understand how PostgreSQL uses indexes when we execute different queries.

Understanding Indexes Through Queries

Query 1: Fetching a Record Using an Indexed Column

Let's find the employee whose id is 20.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM employees WHERE id = 20;

Before looking at the output, let's understand what EXPLAIN, ANALYZE, and BUFFERS mean.

EXPLAIN: Shows how PostgreSQL plans to execute a query without actually running it.
ANALYZE: Executes the query and shows what actually happened, including the number of rows returned and execution time.
BUFFERS: Shows how many data block accesses PostgreSQL made during planning and execution, including blocks already available in memory and blocks that had to be read into memory.

By combining these options, we can understand how PostgreSQL executes our query and how it accesses data.

Output (First Execution):

Now, let's understand each part of the output.

1. Index Only Scan

Index Only Scan using employees_pkey on employees

PostgreSQL used the employees_pkey index, which was automatically created when we defined id as the primary key.
We're searching by id and selecting only id.
Since id is already stored in the index, PostgreSQL can retrieve the required value directly from the index.
It doesn't need to fetch the full employee record from the table, provided PostgreSQL can confirm that the row is visible without checking the heap.

This is called an Index Only Scan.

2. Cost

cost=0.43..4.45

0.43: Estimated startup cost before PostgreSQL can begin returning rows.
4.45: Estimated total cost of executing this operation.

These values are PostgreSQL's internal cost estimates, not actual execution times in milliseconds.

3. Rows and Width

rows=1 width=4

rows=1: PostgreSQL estimates that one row will be returned.
width=4: The estimated average size of each returned row is 4 bytes.

We're selecting only id, which is an integer occupying 4 bytes.

4. Actual Time, Rows, and Loops

actual time=0.058..0.059 rows=1 loops=1

0.058: Time taken until the first row was returned.
0.059: Time taken until this scan operation completed.
rows=1: One row was actually returned.
loops=1: This operation executed once.

The actual times are measured in milliseconds.

5. Index Condition

Index Cond: (id = 20)

This tells us that PostgreSQL used the condition id = 20 to search the index.

6. Heap Fetches

Heap Fetches: 0

PostgreSQL stores actual table records in heap pages.

An Index Only Scan can avoid visiting heap pages when all the required values are available in the index and PostgreSQL can confirm row visibility using its visibility map.

Here, Heap Fetches: 0 means PostgreSQL didn't need to visit any heap pages to check row visibility.

7. Buffers

Buffers: shared read=4

PostgreSQL stores frequently accessed data pages in memory, in an area called shared buffers.

shared read=4: PostgreSQL had to read 4 blocks into its shared buffer cache.
Each PostgreSQL block is typically 8 KB.

This doesn't necessarily mean 4 physical disk reads, because the operating system may already have those pages cached.

8. Planning Time and Execution Time

Planning:
  Buffers: shared hit=41 read=20
Planning Time: 0.616 ms
Execution Time: 0.097 ms

Before executing a query, PostgreSQL first decides how to execute it.

For example, it checks what indexes are available, estimates how many rows might match, and decides which execution plan would be more efficient.

During this process, PostgreSQL may need to access database pages containing metadata and statistics.

That's why we see:

Planning:
  Buffers: shared hit=41 read=20

shared hit=41: During planning, PostgreSQL made 41 buffer accesses that found pages already available in shared buffers.
shared read=20: During planning, PostgreSQL made 20 block reads into shared buffers.
Planning Time: 0.616 ms: Time taken to prepare the execution plan.
Execution Time: 0.097 ms: Time taken to execute the plan and retrieve the result.

So, PostgreSQL first decides how to fetch the data (planning) and then actually fetches the data (execution).

What Happens When We Run the Same Query Again?

I executed the exact same query one more time.

Output (Second Execution):

Notice the difference:

Metric	First execution	Second execution
Execution shared reads	4	0
Execution shared hits	0	4
Planning time	0.616 ms	0.059 ms
Execution time	0.097 ms	0.027 ms

In the first execution, PostgreSQL had to read 4 blocks into shared buffers.

But during the second execution, those blocks were already available in PostgreSQL's memory.

That's why we see:

Buffers: shared hit=4

A shared hit means PostgreSQL found the required block in its shared buffer cache without needing to read it into that cache again.

The second execution was faster in our experiment, although execution times can also vary for other reasons.

Testing Without PostgreSQL's Existing Cache

If you want to observe how a query behaves when its pages are not already cached in PostgreSQL's shared buffers, you can restart PostgreSQL on your local test machine.

On Ubuntu:

sudo systemctl restart postgresql

Then execute:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM employees WHERE id = 20;

Restarting PostgreSQL clears its shared buffer cache, but it does not clear the operating system's page cache. Some pages may also be loaded again before your query runs.

So this is useful for observing PostgreSQL's buffer behavior, but it doesn't guarantee a completely cold disk-cache experiment.

Only restart PostgreSQL for this purpose on a local test environment, not on a production or shared database.

Query 2: Fetching a Column That Is Not in the Index

In the previous query, we selected id, which was already available in the primary key index.

Now, let's try fetching name using the same index.

EXPLAIN (ANALYZE, BUFFERS)
SELECT name FROM employees WHERE id = 500;

Output:

Here, PostgreSQL used an Index Scan instead of an Index Only Scan.

Why?

Our employees_pkey index contains id, but not name.

So PostgreSQL:

Searches the index to find id = 500.
Gets the reference to the corresponding heap row.
Visits the heap to fetch the name.

This is called an Index Scan.

Notice the estimated cost:

cost=0.43..8.45 rows=1 width=16

The total estimated cost is 8.45, compared with 4.45 in Query 1, because PostgreSQL also needs to fetch the row from the heap.

The estimated width is now 16 bytes instead of 4, because we're selecting a text column rather than an integer.

Buffers: shared hit=2 read=2

During execution, PostgreSQL made 2 shared-buffer hits and 2 shared-buffer reads, covering the index search and heap access.

The execution took 0.093 ms.

So the difference is simple: an Index Only Scan can return data from the index without visiting the heap when visibility permits, whereas this Index Scan uses the index to find the row and then visits the heap to retrieve name.

Query 3: Searching Using a Column Without an Index

Until now, we've been searching using id, which has an index.

But what happens if we search using a column that doesn't have an index?

Let's try searching using name.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM employees
WHERE name = 'Employee_500';

Output:

Here, PostgreSQL used a Parallel Sequential Scan.

Let's understand what happened.

1. Parallel Sequential Scan

Parallel Seq Scan on employees

Since we haven't created an index on name, PostgreSQL cannot use an index on that column to locate the required employee.

Instead, it scans the table and checks the name of each row against our condition.

But rather than using a single process, PostgreSQL decided to scan the table in parallel.

2. Workers Planned and Launched

Workers Planned: 2
Workers Launched: 2

PostgreSQL planned to use 2 additional worker processes and successfully launched both.

Along with the main process, 3 processes participated in scanning the table.

Each process works on different portions of the table, allowing the scan to finish faster than a single-process scan might.

3. Filter and Rows Removed

Filter: (name = 'Employee_500'::text)
Rows Removed by Filter: 3333333

PostgreSQL checks each row against the condition name = 'Employee_500'.

Rows that don't match are removed by the filter.

Here, Rows Removed by Filter: 3333333 is the average number of rows rejected per execution of the parallel scan, rounded for display.

Since loops=3, approximately 10 million rows were checked across all three participants.

Even though we wanted only one employee, PostgreSQL had to examine the entire table.

4. Gather

Gather  (cost=1000.00..125620.26 rows=1 width=4)

The Gather operation collects the rows produced by the parallel scan participants and passes them to the main query process.

In our case, only one row matched the condition.

5. Buffer Usage

Buffers: shared hit=1 read=72545

During execution, PostgreSQL made:

1 shared-buffer hit.
72,545 shared-buffer reads.

With the usual 8 KB page size, those reads represent approximately 567 MB of block-read traffic.

Notice that the Gather and Parallel Seq Scan show the same buffer totals. We shouldn't add them together, because the parent node's statistics include the work of its child node.

6. Execution Time

Execution Time: 374.165 ms

The query took 374.165 ms to execute.

Compare that with Query 2, where we searched using the indexed id column and the execution took 0.093 ms.

The difference is significant in these particular runs.

With the index, PostgreSQL could locate the row directly. Without an index on name, it scanned all 10 million records to find one matching employee.

Query 4: Creating an Index on the Name Column and Comparing Query Performance

In the previous query, PostgreSQL scanned all 10 million records because there was no index on name.

Now, let's create an index on that column.

CREATE INDEX employees_name_idx
ON employees (name);

This creates a B-tree index on name.

Let's execute the query again.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM employees
WHERE name = 'Employee_500';

Output:

This time, PostgreSQL used an Index Scan.

1. Index Scan

Index Scan using employees_name_idx on employees

PostgreSQL searches the new index for Employee_500.

Since we're selecting id, which isn't stored in this index, PostgreSQL then visits the heap to fetch it.

2. Buffer Usage

Buffers: shared hit=1 read=4

During execution, PostgreSQL made just 5 shared-buffer accesses, compared with 72,546 in the previous query.

3. Execution Time

Execution Time: 0.050 ms

Let's compare the results before and after creating the index.

Metric	Without index	With index
Scan type	Parallel Seq Scan	Index Scan
Shared hits	1	1
Shared reads	72,545	4
Execution time	374.165 ms	0.050 ms

The execution time decreased from 374.165 ms to 0.050 ms — approximately 7,483 times faster in these two runs.

Of course, caching and other execution conditions can also affect these timings, so we shouldn't expect exactly the same improvement every time.

But the important difference is how PostgreSQL searches for the record.

Earlier, PostgreSQL had to scan the entire table to find Employee_500.

After creating the index, it searched the B-tree index for that particular name and used the matching entry to fetch the row from the heap.

So even if the execution time varies due to caching, PostgreSQL no longer needs to scan all 10 million table records just to find that employee.

Query 5: Sorting Records Without an Index

So far, we've seen how indexes help PostgreSQL search for records. But indexes can also help when sorting data.

Let's find the top 100 employees with the highest salaries.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, salary
FROM employees
ORDER BY salary DESC
LIMIT 100;

Output:

Note: When reading an EXPLAIN ANALYZE execution plan, it's usually easier to start from the bottom and move toward the top.

The lower-level operations produce data, which is then passed to their parent operations.

In our query, the data flows through:

Parallel Seq Scan – Reads the records from the table.
Sort – Finds the highest salaries.
Gather Merge – Combines the sorted results from the parallel workers.
Limit – Returns only the top 100 records.

This is a useful way to understand execution plans, although some operations can run at the same time.

Now, let's understand each operation.

1. Parallel Sequential Scan

Parallel Seq Scan on employees

Since there is no index on salary, PostgreSQL scans the table to find the highest salaries.

Even though we requested only 100 records, PostgreSQL still needs to examine all 10 million records to determine which employees have the highest salaries.

2. Sort

Sort Key: salary DESC
Sort Method: top-N heapsort  Memory: 31kB

PostgreSQL needs to arrange the results by salary in descending order.

But notice that it used top-N heapsort.

Since we requested only the top 100 records, PostgreSQL doesn't need to fully sort all 10 million records.

Instead, each parallel scan participant keeps track of its best 100 candidates while processing the rows.

This reduces the memory required for sorting, but the table still has to be scanned.

3. Gather Merge

Gather Merge
Workers Planned: 2
Workers Launched: 2

The parallel participants produce sorted results.

Gather Merge combines these results while maintaining the salary order.

4. Limit

Limit

Finally, PostgreSQL returns only the first 100 records from the sorted results.

5. Buffer Usage and Execution Time

Buffers: shared hit=273 read=72385
Execution Time: 575.346 ms

During execution, PostgreSQL made 273 shared-buffer hits and 72,385 shared-buffer reads.

The query took 575.346 ms to execute.

Even though we requested just 100 records, PostgreSQL had to examine all 10 million records to identify the highest salaries.

Query 6: Sorting Records After Creating an Index on Salary

In the previous query, PostgreSQL scanned all 10 million records and performed sorting to find the top 100 highest salaries.

Now, let's create an index on salary.

CREATE INDEX employees_salary_idx
ON employees (salary);

This creates a B-tree index on the salary column, where the index entries are maintained in sorted order.

Let's run the same query again.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, salary
FROM employees
ORDER BY salary DESC
LIMIT 100;

Output:

Notice that PostgreSQL used an Index Scan Backward instead of a Parallel Sequential Scan followed by sorting.

1. Index Scan Backward

Index Scan Backward using employees_salary_idx on employees

A B-tree index keeps its entries in sorted order.

Since our query uses ORDER BY salary DESC, PostgreSQL can scan the index backward, starting from the highest salary values.

It doesn't need to sort all 10 million records separately.

And because we're also selecting id, which isn't stored in the salary index, PostgreSQL visits the heap to fetch it.

2. Limit

Limit (cost=0.43..5.17 rows=100 width=8)

This is where the LIMIT 100 makes a big difference.

PostgreSQL starts reading the index from the highest salary and stops after returning 100 records.

Even though the index contains entries for 10 million records, PostgreSQL doesn't need to scan all of them.

3. Buffer Usage

Buffers: shared read=103

PostgreSQL made only 103 shared-buffer reads during execution.

Compare that with the previous query, which required 72,658 buffer accesses.

4. Execution Time

Execution Time: 1.123 ms

Let's compare both executions.

Metric	Without salary index	With salary index
Execution plan	Parallel Seq Scan + Sort	Index Scan Backward
Shared hits	273	0
Shared reads	72,385	103
Execution time	575.346 ms	1.123 ms

The execution time decreased from 575.346 ms to 1.123 ms, approximately 512 times faster in these runs.

Although caching can affect execution times, the important difference is that PostgreSQL no longer needs to scan the entire table and sort the results.

It can simply scan the index backward, retrieve the highest salary records, and stop after finding 100 rows.

Query 7: Why Doesn't PostgreSQL Always Use an Index?

Until now, we've seen how indexes improved query performance.

But does PostgreSQL always use an index whenever one is available?

Let's try searching for employees whose salary is greater than 40000.

Remember, we already created an index on the salary column.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, salary
FROM employees
WHERE salary > 40000;

Output:

Interesting! PostgreSQL chose a Sequential Scan, even though an index exists on salary.

Why?

1. Sequential Scan

Seq Scan on employees

Our condition is:

WHERE salary > 40000

This matches a large number of employees.

In our case, PostgreSQL found 8,572,502 matching records out of 10 million, which is approximately 85.7% of the table.

If PostgreSQL used the salary index, it would need to find all matching index entries and then visit the heap to fetch id, since id isn't stored in that index.

For so many matching records, accessing the heap through the index could require more work than simply scanning the table.

So PostgreSQL estimated that a Sequential Scan would be cheaper.

2. Rows Removed by Filter

Filter: (salary > 40000)
Rows Removed by Filter: 1427498

PostgreSQL scanned all 10 million records.

Of those:

8,572,502 records matched the condition.
1,427,498 records were removed by the filter.

3. Buffer Usage

Buffers: shared hit=16247 read=56299

PostgreSQL accessed 72,546 shared-buffer blocks while scanning the table.

Some pages were already cached, while others had to be read into shared buffers.

4. Execution Time

Execution Time: 1123.251 ms

The query took approximately 1.12 seconds to execute and return over 8.5 million rows to the query executor.

Here, the important thing is that having an index doesn't mean PostgreSQL will always use it.

PostgreSQL's query planner estimates the cost of different execution plans and chooses the one it expects to be cheaper.

When a query returns a small number of records, an index can be very useful. But when most of the table matches, a sequential scan may be more efficient.

What's Next?

In this article, we focused on understanding indexing through practical queries and execution plans.

In Part 2, we'll explore different types of index scans, when PostgreSQL chooses each one, and how we can design indexes more efficiently.

That's it for now! 😊

Thanks for reading!

Database Storage Concepts – How Tables Are Stored on Disk

Anusha Renangi — Wed, 14 Oct 2026 17:51:48 +0000

In my previous article, I discussed database transactions and ACID properties.

Now, let's understand how databases actually store data.

Whenever we create a table and insert records, we see the data in rows and columns. But how is this data stored on disk? Does the database read one row at a time? How does it know where a particular row is stored?

Let's learn the following topics in this article:

Tables
Row ID
Pages
Disk I/O
Heap
Indexes
Row-oriented and column-oriented storage

1. Tables

We know that a table contains rows and columns.

For example, consider an employee table.

emp_id	name	salary	language
1	Anu	10000	English
2	Sam	20000	Telugu
3	John	30000	Hindi
4	Ravi	40000	English
5	Harry	50000	Telugu

This is how we logically see the data.

But internally, databases store these records in pages on disk.

Before understanding pages, let's see what a Row ID is.

2. Row ID

A Row ID is an identifier used internally by a database to locate or reference a row.

It is different from the primary key we define in a table.

For example, emp_id is the primary key in our employee table. But databases may also maintain their own internal row identifiers.

In PostgreSQL, we have a system column called ctid, which tells us the physical location of a particular row version.

SELECT ctid, * FROM employees;

The result might look like:

ctid	emp_id	name
(0,1)	1	Anu
(0,2)	2	Sam
(0,3)	3	John
(1,1)	4	Ravi
(1,2)	5	Harry

Here, (0,1) means the row is at page 0, item position 1.

Unlike a primary key, ctid is not permanent. It can change when a row is updated or moved.

3. Pages

A page is a fixed-size block used to store and read data.

For example:

PostgreSQL generally uses 8 KB pages.
MySQL InnoDB generally uses 16 KB pages.

Let's assume each page can hold three employee records.

Page 0

emp_id	name	salary
1	Anu	10000
2	Sam	20000
3	John	30000

Page 1

emp_id	name	salary
4	Ravi	40000
5	Harry	50000

In reality, the number of rows a page can hold depends on their sizes and the space used for page metadata.

One important thing is that the database generally reads data in pages, not as individual rows directly from disk.

Suppose we need the record with emp_id = 2.

If Page 0 needs to be fetched from disk, the database reads that page into memory. Since the page also contains records 1 and 3, those records are available in the same page.

4. I/O (Input/Output)

I/O refers to reading data from or writing data to storage.

When a database needs a page that is not already in memory, it has to read it from disk.

Disk access is generally slower than accessing data from RAM.

So databases try to minimize unnecessary I/O operations.

Let's take an example.

Suppose we have 1,000 records, and each page holds 10 records.

Then we need approximately 100 pages to store those records, ignoring some storage overhead.

Now suppose we want to find a particular record but don't know which page contains it.

The database may need to read multiple pages until it finds the required record.

Reading 100 pages is more expensive than reading just one page, especially if those pages are not already in memory.

Does every query read data from disk?

No.

Databases also use memory to cache pages.

For example, PostgreSQL uses a memory area called Shared Buffers. The operating system also maintains a page cache.

When a page is read for the first time, it may need to be fetched from disk.

If the same page is needed again and is still cached in memory, the database may be able to access it without another physical disk read.

This reduces I/O and improves performance.

5. Heap

In PostgreSQL, ordinary tables use a storage structure called a Heap.

A heap is a collection of pages containing the actual table rows.

For example, the employee table might have:

Heap – Employees

Page	Records
Page 0	Anu, Sam, John
Page 1	Ravi, Harry
Page 2	Priya, Kiran, Rahul
Page 3	Sneha, Arjun, Meena

The actual data of the table is stored in these pages.

The important point about a PostgreSQL heap is that rows are not maintained in any particular order based on a column.

For example, even if we insert records in the order 1, 2, 3, 4, 5, we cannot assume that they will always remain physically stored in that order.

Now suppose we want to find an employee whose ID is 100.

Without knowing the location of that record, PostgreSQL may need to examine many heap pages to find it.

For a table with millions of records, this can require a lot of data to be read.

Databases maintain separate structures called indexes, which help locate records more efficiently.

6. Index

An index is a separate data structure that helps the database find the location of required records.

For example, suppose we create an index on emp_id.

The index keeps employee ID values along with references to their corresponding rows.

A simplified example:

Employee ID	Row Location
1	(0,1)
2	(0,2)
3	(0,3)
4	(1,1)
5	(1,2)

Here, the row location tells us which heap page and item contain the record.

Instead of checking every heap page, the database can use the index to find the required location.

Indexes are also stored in pages on disk. So reading an index can also involve I/O.

One commonly used index data structure is a B-tree.

An index doesn't replace the heap in PostgreSQL. It is maintained separately and contains information that helps locate the actual table records.

I'll cover indexes, B-trees, and how queries use indexes in more detail in a separate article.

7. Row-Oriented Database

In a row-oriented database, the values belonging to the same row are stored together.

PostgreSQL and MySQL InnoDB are examples of row-oriented databases.

Let's take our employee table.

id	name	salary	language
1	Anu	10000	English
2	Sam	20000	Telugu
3	John	30000	Hindi
4	Ravi	40000	English

In row-oriented storage, the data is organized approximately like this:

Page 0

[1, Anu, 10000, English]
[2, Sam, 20000, Telugu]

Page 1

[3, John, 30000, Hindi]
[4, Ravi, 40000, English]

The values of each employee are stored together.

Let's consider three queries.

Query 1: Find an employee's name

SELECT name
FROM employees
WHERE id = 2;

The database needs to find the row where id = 2.

Suppose there is no index on the ID column.

The database starts scanning the table pages.

First, it reads Page 0 and checks the employee IDs in that page.

It checks the first row, where id = 1. Since this is not the required ID, it moves to the next row.

The next row contains id = 2, so the database finds the required employee.

Since the row contains all the columns together, it can directly get the name Sam from that row.

If the record were not in Page 0, the database would continue checking the next pages until it found the matching row.

If an index is available and the database chooses to use it, it can locate the required row without scanning every table page.

Query 2: Fetch all employee details

SELECT *
FROM employees
WHERE id = 2;

Here, we need all columns of one employee.

Row-oriented storage is useful because all the values belonging to that row are stored together.

Query 3: Calculate the total salary

SELECT SUM(salary)
FROM employees;

Here, we only need the salary column.

But in row-oriented storage, salary values are stored along with the other columns of each employee.

So, when reading the table pages, the database may also bring unnecessary data into memory.

For a large table containing many columns, this can result in more data being read than we actually need.

This is one limitation of row-oriented storage for analytical queries.

8. Column-Oriented Database

In a column-oriented database, values belonging to the same column are stored together.

Let's consider the same employee table.

Instead of storing complete rows together, the data is organized by columns.

Each column can be divided into multiple blocks depending on the amount of data.

For example, let's assume each block holds two values.

ID column

Block 0: [1:1001, 2:1002]
Block 1: [3:1003, 4:1004]

Name column

Block 0: [Anu:1001, Sam:1002]
Block 1: [John:1003, Ravi:1004]

Salary column

Block 0: [10000:1001, 20000:1002]
Block 1: [30000:1003, 40000:1004]

Language column

Block 0: [English:1001, Telugu:1002]
Block 1: [Hindi:1003, English:1004]

Here, 1001, 1002, etc., represent row IDs.

For example, Sam:1002 and 20000:1002 belong to the same employee.

This is a simplified representation. Actual column-oriented databases may use row positions or other methods instead of storing an individual row ID with every value.

Column-oriented databases store values in column blocks, often in compressed form.

Let's use the same three queries.

Query 1: Find an employee's name

SELECT name
FROM employees
WHERE id = 2;

The database needs the ID and Name columns.

Assuming there is no index or other information to locate the value directly, it searches the ID blocks until it finds 2:1002.

Now it knows the row ID is 1002.

Next, it accesses the Name column and finds Sam:1002.

So the result is Sam.

Here, the database accesses two different columns to get the result.

Query 2: Fetch all employee details

SELECT *
FROM employees
WHERE id = 2;

Here, we need every column.

First, the database finds 2:1002 in the ID column.

Then it retrieves the corresponding values from the other columns using row ID 1002.

Name: Sam:1002
Salary: 20000:1002
Language: Telugu:1002

Since values are stored column-wise, the database may need to read multiple column blocks to get the complete employee record.

This can involve more work compared with retrieving a complete row from row-oriented storage.

Similarly, updates or deletes involving several columns may require more work because the values are stored separately. Some column-oriented databases handle this using update or delete markers.

Query 3: Calculate the total salary

SELECT SUM(salary)
FROM employees;

Here, we only need the Salary column.

The database can read only the Salary blocks:

Block 0: [10000, 20000]
Block 1: [30000, 40000]

There is no need to read the Name, ID, or Language columns.

This reduces the amount of data read.

Column-oriented storage is very useful for queries involving aggregations over large numbers of records.

Compression

Column-oriented databases can also achieve better compression.

For example, suppose we have 1 million employee records, and many employees have the same language or department.

Since the values of a particular column are stored together, repeated values can be compressed efficiently.

In row-oriented storage, different data types and values are stored together as part of each row, so this type of compression may not be as effective.

9. Row-Oriented vs Column-Oriented Storage

Row-Oriented	Column-Oriented
Stores data row by row	Stores data column by column
Good for reading complete records	Good for reading selected columns across many records
Generally efficient for frequent inserts and updates	Small updates can be more expensive
Commonly used for OLTP	Commonly used for OLAP
Aggregations over a few columns may read unnecessary data	Aggregations can read only the required columns
Compression is generally less effective	Often achieves better compression
PostgreSQL, MySQL InnoDB	Amazon Redshift, Google BigQuery, ClickHouse

OLTP (Online Transaction Processing) usually involves frequent inserts, updates, deletes, and fetching individual records.

For example, creating an order, updating customer details, or retrieving a particular employee.

OLAP (Online Analytical Processing) usually involves queries that process many records, such as calculating total sales, generating reports, or finding average salaries.

Both types of databases can execute these operations. The difference is in how their storage is organized and the workloads they are generally optimized for.

Software Design Principles Every Developer Should Know: SOLID, DRY, KISS, and YAGNI

Anusha Renangi — Mon, 12 Oct 2026 14:18:41 +0000

When we write code, our first goal is usually to make it work.

But what happens when requirements change?

Initially, our code might work perfectly. But as the application grows, even a small change can become difficult if the code is poorly structured.

So, writing code that works is important, but writing code that is easy to understand and maintain is equally important.

While learning software design, I came across a few principles that help us write better code.

I have already written about the SOLID principles with real-life examples.

You can read that article here:

SOLID Principles Explained Through Real-Life Examples

In this article, I'll cover three more principles:

DRY: Don't Repeat Yourself
KISS: Keep It Simple, Stupid
YAGNI: You Aren't Gonna Need It

1. DRY Principle (Don't Repeat Yourself)

DRY stands for Don't Repeat Yourself.

The idea is to avoid writing the same logic in multiple places.

The Problem: Repeating the Same Logic

Suppose we are developing an online shopping application and need to calculate the final price after applying a discount.

price = 1000
discount = price * 0.10
final_price = price - discount

print(final_price)

Now, suppose we need the same calculation on another page. We write the same logic again.

If the discount changes, we must update both places. If we forget one, the application might calculate different prices.

Applying DRY

Instead, we can create a reusable function.

def calculate_final_price(price):
    discount = price * 0.10
    return price - discount


print(calculate_final_price(1000))
print(calculate_final_price(2000))

Now, the calculation is written in one place and can be reused wherever needed.

Use Cases of DRY

1. Reusing Common Logic

Suppose we need to check whether a person is an adult in different parts of an application.

Instead of writing the same condition repeatedly, we can create a reusable function.

def is_adult(age):
    return age >= 18


print(is_adult(22))
print(is_adult(16))

Now, we can call is_adult() wherever we need to check a person's age.

2. Sharing Common Functionality Through Inheritance

Suppose we have two types of employees: Developers and Testers.

Both have a name and need to display it. However, developers write code, while testers test applications.

Instead of repeating the common functionality, we can put it in a parent class.

class Employee:
    def __init__(self, name):
        self.name = name

    def display_name(self):
        print(f"Employee: {self.name}")


class Developer(Employee):
    def write_code(self):
        print(f"{self.name} is writing code")


class Tester(Employee):
    def test_application(self):
        print(f"{self.name} is testing the application")


developer = Developer("Anu")
tester = Tester("Ravi")

developer.display_name()
developer.write_code()

tester.display_name()
tester.test_application()

Both classes reuse display_name() from the Employee class, while each class has its own functionality.

3. Using Constants for Repeated Values

Suppose we use the same tax rate in multiple calculations.

Instead of hardcoding the value everywhere, we can define a constant.

TAX_RATE = 0.18

price = 1000
tax = price * TAX_RATE

print(tax)

If the tax rate changes, we can update it in one place.

4. Creating Reusable Modules

Suppose multiple Python files need to calculate the area of a circle.

Instead of writing the same function in every file, we can create a separate module.

In math_utils.py:

import math

def circle_area(radius):
    return math.pi * radius ** 2

In another file:

from math_utils import circle_area

print(circle_area(5))

Now, we can reuse the same function in different files.

Benefits of DRY

Less duplicated code: We avoid repeating the same logic.
Easier maintenance: We can update shared logic in one place.
Consistent behavior: The same logic works across different parts of the application.
Easier testing: We can test reusable functions independently.

Cons

Over-abstraction: Combining unrelated logic can make code difficult to understand.
Unwanted dependencies: Changes to shared code can affect multiple parts of the application.

We should avoid repeating the same logic, but we don't need to combine code just because it looks similar.

2. KISS Principle (Keep It Simple, Stupid)

KISS stands for Keep It Simple, Stupid.

The idea is to avoid making code more complicated than necessary.

The Problem

Suppose we want to check whether a number is even.

def is_even(number):
    if number % 2 == 0:
        return True
    else:
        return False

But do we really need an if-else statement here?

Applying KISS

We can simplify it:

def is_even(number):
    return number % 2 == 0

Both functions produce the same result, but the second one is simpler and easier to read.

How to Apply KISS

Break large problems into smaller functions.
Use clear variable and function names.
Avoid unnecessary complexity.
Choose straightforward solutions when they meet the requirements.

Benefits of KISS

Easier to read and understand.
Easier to debug and maintain.
Less unnecessary complexity.
Faster code reviews.

Cons

Oversimplifying code can make it difficult to handle important requirements or edge cases.

Remember, shorter code is not always simpler code.

3. YAGNI Principle (You Aren't Gonna Need It)

YAGNI stands for You Aren't Gonna Need It.

The idea is to build what is required now instead of implementing features just because we might need them in the future.

The Problem: Building Unnecessary Features

Suppose we are developing a payment system that currently supports only credit card payments.

While developing it, we think:

"What if we need PayPal or cryptocurrency payments in the future?"

So, we start implementing support for those payment methods even though nobody has requested them.

This takes extra time and makes the code more complicated.

Applying YAGNI

For now, we should implement only the required credit card payment functionality.

If the requirements change later, we can add the necessary features then.

How to Apply YAGNI

Understand the current requirements.
Focus on features that are actually needed.
Avoid implementing hypothetical features.
Add functionality when there is a real requirement for it.

Benefits of YAGNI

Faster development.
Less unnecessary code.
Reduced development effort.
Better focus on current requirements.

Cons

Future requirements may require refactoring.
New features may take longer if the existing design needs changes.

YAGNI doesn't mean ignoring future maintainability. It means avoiding unnecessary implementation before it is needed.

The goal is to write code that solves the current problem and is easy to understand and maintain.

SOLID Principles Explained Through Real-Life Examples

Anusha Renangi — Mon, 12 Oct 2026 13:03:38 +0000

When we write code, making it work is only one part of the job. As requirements change and applications grow, we also need to think about how easy our code is to understand, maintain, and extend.

While learning software design, I came across the SOLID principles. I didn't want to just memorize their definitions, so I tried to understand each principle through real-life situations and then relate it to code.

In this article, I'll share those examples to explain the five SOLID principles in simple terms.

S: Single Responsibility Principle

A real-life example

The owner of my hostel also managed a curry point.

Whenever something went wrong in the hostel, he had to spend time fixing it. During that time, managing the curry point became difficult. The same thing happened when there was a problem at the curry point.

Both businesses depended on the same person, so a problem in one affected the other.

Now, imagine having separate people to manage the hostel and the curry point. A problem in the hostel could be handled by the hostel manager while the curry point continued its work.

We can apply a similar idea when designing software.

What is a responsibility?

A responsibility is the job a class is meant to handle.

Let's say the owner builds software to manage both businesses.

class BusinessManager:
    def book_hostel_room(self):
        pass

    def schedule_hostel_repairs(self):
        pass

    def take_food_order(self):
        pass

    def calculate_food_bill(self):
        pass

This class handles two different responsibilities:

Hostel management

Booking rooms
Scheduling repairs

Curry point management

Taking food orders
Calculating food bills

Although all these methods belong to the same BusinessManager class, they handle two different areas of work.

If hostel booking rules change, we should not need to modify the code that handles food orders. Similarly, a change in food billing should not affect hostel management.

We can separate these responsibilities into two classes.

class HostelManager:
    def book_room(self):
        pass

    def schedule_repairs(self):
        pass


class CurryPointManager:
    def take_food_order(self):
        pass

    def calculate_food_bill(self):
        pass

Now, hostel-related changes can be handled in HostelManager, and curry point-related changes can be handled in CurryPointManager.

Notice that we did not create a separate class for every method. Room booking and repair scheduling can stay together because both are part of hostel management.

The principle

The Single Responsibility Principle (SRP) states:

A class should have only one reason to change.

This does not mean a class should have only one method. It means that the methods in a class should belong to one clear responsibility.

When a class handles different responsibilities that change for different reasons, we should consider separating them.

The goal is to make changes in one area without unnecessarily affecting other areas.

O: Open/Closed Principle

I was thinking of building an app for my uncle to manage his curry point.

He sells egg curry and chicken curry. I wanted the app to help him manage the items and their prices.

Now, suppose he decides to add mutton curry to the menu. I would have to change the code to add its price.

What if he wants to add another curry later? I would have to make changes again.

I wanted to write the code in a way that adding a new curry would be easier, without changing the code that already works.

What is the Open/Closed Principle?

The Open/Closed Principle says:

Software entities should be open for extension, but closed for modification.

In simple words, we should be able to add new features without having to change the existing code every time.

1. My first approach

I can write a function to calculate the price of each curry.

def calculate_price(item):
    if item == "egg_curry":
        return 80
    elif item == "chicken_curry":
        return 150

This works for the current menu.

But when my uncle wants to add mutton curry, I need to modify the same function.

elif item == "mutton_curry":
    return 250

Every time he adds a new curry, I have to add another condition to this function.

Can we avoid changing this function whenever a new curry is added?

2. Create a common class for curries

Let's create a class called Curry that defines a common method for calculating prices.

from abc import ABC, abstractmethod


class Curry(ABC):
    @abstractmethod
    def calculate_price(self):
        pass

The Curry class defines a rule: every curry must have a calculate_price() method.

It doesn't calculate the actual price. Each curry will provide its own implementation.

3. Create classes for each curry

Now, let's create classes for egg curry and chicken curry.

class EggCurry(Curry):
    def calculate_price(self):
        return 80


class ChickenCurry(Curry):
    def calculate_price(self):
        return 150

Each class implements the same method, but returns a different price.

4. Write the billing function

Now, we can write a common function to calculate the price.

def generate_bill(curry: Curry):
    return curry.calculate_price()

Let's use it:

print(generate_bill(EggCurry()))      # 80
print(generate_bill(ChickenCurry()))  # 150

Notice that generate_bill() doesn't check whether the curry is egg curry or chicken curry. It simply calls calculate_price().

Python calls the method implemented by the actual object. This is called polymorphism.

5. Add mutton curry

Now my uncle wants to add mutton curry to the menu.

I just need to create another class.

class MuttonCurry(Curry):
    def calculate_price(self):
        return 250

We can use the same billing function:

print(generate_bill(MuttonCurry())  # 250

When my uncle wants to add mutton curry, I don't have to touch the generate_bill() function. I just add the MuttonCurry class.

The same function works for egg curry, chicken curry, and mutton curry. That's what I wanted from the design.

L: Liskov Substitution Principle

LSP says that

Objects of a child class should be replaceable with objects of their parent class without breaking the application.

Let's understand this with an example.

An Authentication Example

Imagine we are building an application that supports different authentication methods, such as passwords, biometrics, and two-factor authentication.

We create a parent class called AuthenticationProvider.

class AuthenticationProvider:
    def authenticate(self, credentials: dict) -> bool:
        """
        Returns True if authentication succeeds,
        or False if the credentials are invalid.
        """
        raise NotImplementedError

The contract is simple. Every authentication provider must return True or False.

Password Authentication

class PasswordAuthProvider(AuthenticationProvider):
    def authenticate(self, credentials: dict) -> bool:
        return database.verify(
            credentials["user"],
            credentials["pass"]
        )

If the credentials are valid, it returns True. Otherwise, it returns False.

This follows the parent's contract.

Biometric Authentication

class BiometricAuthProvider(AuthenticationProvider):
    def authenticate(self, credentials: dict) -> bool:
        return mobile_hardware.verify_fingerprint(
            credentials["fingerprint_token"]
        )

This also returns True or False, so it follows the same contract.

Two-Factor Authentication

Now, let's implement authentication using SMS verification.

class TwoFactorSmsAuthProvider(AuthenticationProvider):
    def authenticate(self, credentials: dict) -> bool:
        sms_gateway.send_pin(credentials["phone_number"])
        raise MultiFactorChallengeRequired()

This implementation sends an SMS PIN but then raises an exception instead of returning a Boolean.

The problem is not that two-factor authentication requires an extra step. The problem is that this implementation breaks the contract promised by the parent class.

How Does This Break the Application?

Consider the client code:

def process_user_login(provider, user_data):
    is_logged_in = provider.authenticate(user_data)

    if is_logged_in:
        display_dashboard()
    else:
        display_error_message("Invalid credentials")

This client expects authenticate() to return a Boolean.

It works with password and biometric authentication. But when we pass TwoFactorSmsAuthProvider, the unexpected exception interrupts the flow.

The child class cannot safely replace the parent class in this client.

That is an LSP violation.

How Can We Fix It?

We need to design a contract that supports all the expected authentication outcomes.

For example, authentication can return one of these results:

SUCCESS
INVALID_CREDENTIALS
MFA_REQUIRED

The two-factor provider can send the PIN and return MFA_REQUIRED instead of throwing an unexpected exception.

The client can handle these common outcomes:

def process_user_login(provider, user_data):
    result = provider.authenticate(user_data)

    if result == "SUCCESS":
        display_dashboard()
    elif result == "INVALID_CREDENTIALS":
        display_error_message("Invalid credentials")
    elif result == "MFA_REQUIRED":
        display_pin_screen()

Now, every provider follows the same contract. The client handles the defined outcomes without needing to know which concrete provider it is using.

The if/elif statements are not the problem here. They handle common outcomes defined by the contract. The problem would be forcing the client to add separate logic for each authentication provider.

I: Interface Segregation Principle

The Interface Segregation Principle (ISP) says that a class should not be forced to implement methods it does not need.

Instead of creating one large interface with many methods, we should split it into smaller interfaces based on what different classes actually need.

The Problem: One Large Interface

Imagine we are building a user management system. We have two types of users: Customers and Administrators.

Customers can log in and change their passwords. Administrators can do these things too, but they can also delete user accounts.

Now, suppose we put all these actions into a single IUser interface.

class IUser:
    def login(self) -> None:
        pass

    def change_password(self) -> None:
        pass

    def delete_user_account(self) -> None:
        pass

When we create a Customer class, it must implement all three methods, even though customers are not allowed to delete user accounts.

class Customer(IUser):
    def login(self) -> None:
        print("Customer logged in.")

    def change_password(self) -> None:
        print("Password changed.")

    def delete_user_account(self) -> None:
        pass

The delete_user_account() method does nothing for a customer. We have included it only because the interface forces us to implement it.

This is the problem ISP helps us avoid.

The Solution: Split the Interface

Instead of putting every action into one interface, we can separate the actions based on their responsibilities.

class IStandardUser:
    def login(self) -> None:
        pass

    def change_password(self) -> None:
        pass


class IAdminUser:
    def delete_user_account(self) -> None:
        pass

Now, each interface represents a smaller set of actions.

1. Customer

A customer needs only standard user actions.

class Customer(IStandardUser):
    def login(self) -> None:
        print("Customer logged in.")

    def change_password(self) -> None:
        print("Password changed.")

The Customer class no longer needs an unnecessary delete_user_account() method.

2. Administrator

An administrator can perform both standard user actions and administrative actions. So, the class can implement both interfaces.

class Administrator(IStandardUser, IAdminUser):
    def login(self) -> None:
        print("Admin logged in.")

    def change_password(self) -> None:
        print("Admin password changed.")

    def delete_user_account(self) -> None:
        print("Admin deleted an account.")

Each class now implements only the actions it is expected to support.

D: Dependency Inversion Principle

Imagine you are working as a backend developer in a software company. You know how to build applications using Django.

Your company currently uses one technology, but after some time, the team decides to move to a different technology.

If you know only Django and are unwilling to learn anything new, you may struggle to adapt to the team's changing requirements.

The same situation can happen in software design.

Imagine we have an application that sends SMS notifications using Twilio. If our code is tightly connected to Twilio, switching to another provider, such as Vonage, may require us to change the existing code.

Instead, we can design our code so that it does not depend on one specific SMS provider. Then, we can change the provider without changing the main business logic.

This is the idea behind the Dependency Inversion Principle.

What Is Dependency Inversion Principle?

The Dependency Inversion Principle says that high-level modules should not depend directly on low-level modules. Both should depend on abstractions.

In simple words, our main business logic should not depend directly on a specific implementation.

The Problem: Depending Directly on Twilio

Imagine our application sends an SMS whenever a customer places an order.

class TwilioService:
    def send_sms(self, message: str) -> None:
        print(f"Sending SMS through Twilio: {message}")


class OrderService:
    def __init__(self):
        self.sms_service = TwilioService()

    def place_order(self) -> None:
        print("Order placed.")
        self.sms_service.send_sms("Your order has been placed!")

Here, OrderService creates a TwilioService object directly.

If we want to switch to Vonage, we need to change how OrderService creates and uses the SMS service.

The order-processing logic is unnecessarily tied to Twilio.

How Can We Fix It?

We can define a common interface that describes how an SMS service should work.

from abc import ABC, abstractmethod


class SMSService(ABC):
    @abstractmethod
    def send_sms(self, message: str) -> None:
        pass

Now, both providers can implement this interface.

class TwilioService(SMSService):
    def send_sms(self, message: str) -> None:
        print(f"Sending SMS through Twilio: {message}")


class VonageService(SMSService):
    def send_sms(self, message: str) -> None:
        print(f"Sending SMS through Vonage: {message}")

Next, we update OrderService to receive an SMS service instead of creating a Twilio object itself.

class OrderService:
    def __init__(self, sms_service: SMSService):
        self.sms_service = sms_service

    def place_order(self) -> None:
        print("Order placed.")
        self.sms_service.send_sms("Your order has been placed!")

Now, we can choose the provider when creating the service.

order_service = OrderService(TwilioService())

Or we can use Vonage instead.

order_service = OrderService(VonageService())

We do not need to change the OrderService class when switching providers.

⭐ Hope this helps! 😊

I was really confused about the difference between LSP and ISP. I went through several Reddit discussions, Stack Overflow answers, and other resources to understand them better.

If you're struggling with the same confusion, let's discuss it in the comments!

Database Transactions - ACID Properties

Anusha Renangi — Fri, 09 Oct 2026 20:39:40 +0000

Introduction

Yesterday, I went to a restaurant with my friends. We ordered some food, finished eating, and asked the waiter for the bill.

While the waiter was preparing the bill, I started thinking about how the billing system works behind the scenes.

When we place an order, the system has to do multiple things, like saving the order details, calculating the bill, and updating the payment status.

But what if something goes wrong in the middle? What if the system crashes before completing all these operations?

This is where database transactions come into the picture.

Recently, I was learning about database transactions, ACID properties, and isolation levels. In this article, I'll share what I understood, along with some simple examples.

What is a Database Transaction?

A database transaction is a group of one or more database operations that are treated as a single unit of work.

Let's take our restaurant example.

When a customer places an order, the system might need to perform these operations:

Create an order.
Save the ordered items.
Update the available stock.
Calculate and save the total bill.

All these operations are related. If one of them fails, we don't want the database to be left with incomplete information.

So, instead of executing them as separate independent operations, we can group them into a single transaction.

A transaction generally involves three important commands:

BEGIN — Starts a transaction.
COMMIT — Saves the changes made by the transaction.
ROLLBACK — Undoes the uncommitted changes if something goes wrong.

For example:

BEGIN;

INSERT INTO orders (order_id, table_id, total_amount)
VALUES (101, 5, 500);

INSERT INTO order_items (order_id, item_id, quantity)
VALUES (101, 10, 2);

UPDATE inventory
SET quantity = quantity - 2
WHERE item_id = 10;

COMMIT;

Here, all three database operations are part of one transaction.

If everything succeeds, we commit the transaction.

If an operation fails, we can roll back the transaction so that the database doesn't keep only part of the changes.

But what happens if the database itself crashes while executing a transaction?

A database can crash for several reasons, such as power failures, hardware problems, operating system failures, or software bugs.

Even in these situations, the database needs a way to recover without leaving incomplete transactions behind.

To handle these kinds of problems and maintain reliable data, database transactions follow four important properties, commonly called ACID properties.

ACID Properties

ACID stands for:

A — Atomicity
C — Consistency
I — Isolation
D — Durability

Each property solves a different problem.

Atomicity ensures that either all operations in a transaction happen or none of them happen.

Consistency ensures that a transaction preserves the rules and constraints defined in the database.

Isolation controls how multiple transactions running at the same time interact with each other.

Durability ensures that once a transaction is committed, its changes survive a crash, under the database's durability guarantees.

Let's understand each property with examples.

Atomicity

Atomicity means that either all the operations in a transaction should succeed, or none of them should happen.

Let's take our restaurant example.

Suppose a customer orders 2 pizzas, and each pizza costs ₹200.

When the waiter confirms the order, the system needs to perform two operations:

Create an order with a total amount of ₹400.
Reduce the available pizza stock by 2.

Now, let's say the order is created successfully, but the system crashes before updating the stock.

In this case, the order exists in the database, but the stock has not been updated. This leaves the data in an incorrect state.

To avoid this, we can execute both operations inside a transaction.

BEGIN;

INSERT INTO orders (order_id, amount)
VALUES (101, 400);

UPDATE inventory
SET quantity = quantity - 2
WHERE item_id = 1;

COMMIT;

If both operations succeed, the transaction is committed.

But if something goes wrong before the transaction is committed, the database rolls back the changes.

So, even if the order was inserted successfully, it won't remain in the database if the transaction fails.

This is what atomicity guarantees: All or nothing.

How does the database maintain atomicity?

Databases use transaction logs and recovery mechanisms to handle failures.

For example, PostgreSQL uses Write-Ahead Logging (WAL).

Before modified data pages are written to their final location on disk, PostgreSQL records the changes in WAL.

If the database crashes, it uses WAL and transaction status information during recovery to ensure that committed transactions are recovered and incomplete transactions do not become visible as committed changes.

We'll understand WAL in more detail when we discuss durability.

Consistency

Consistency means that a transaction should maintain the rules and constraints defined in the database.

While learning about consistency, I came across two different concepts: Data Consistency and Read Consistency.

1. Data Consistency (Correctness)

Data consistency is about keeping the data correct and making sure it follows the rules defined in the database.

Let's say we have three tables.

Blogs Table

blog_id	likes
1	5
2	1

Users Table

user_id	name
1	John
2	Edmon

Pictures Table

user_id	picture_id
1	1
1	2
2	3
2	4

Here, user_id in the Pictures table is a foreign key referencing the Users table.

Now suppose we try to insert a picture with user_id = 5, but there is no user with that ID in the Users table.

INSERT INTO pictures (user_id, picture_id)
VALUES (5, 5);

The database rejects this operation because it violates the foreign key constraint.

This is called referential integrity.

Similarly, databases maintain data consistency using constraints such as primary keys, foreign keys, UNIQUE, NOT NULL, and CHECK.

Apart from these constraints, we may also have business rules that need to be maintained by our application or database.

So, the main idea of ACID Consistency is that a transaction should move the database from one valid state to another valid state without breaking the defined rules.

In simple terms, it focuses on correctness of data.

2. Read Consistency (Availability and Freshness)

Now let's look at consistency from another perspective.

In real applications, we may have multiple copies of the same database, called replicas.

Why do we need replicas?

One reason is availability. If one database server goes down, another server may still be able to serve requests. Replicas can also help distribute read traffic.

Let's say we have one primary database and two replicas.

Initially, all three databases contain the same value.

Primary Database → 90
Replica 1 → 90
Replica 2 → 90

Now suppose a transaction updates the value from 90 to 100 in the primary database and commits.

But replication may take some time.

At that moment, the data might look like this:

Primary Database → 100
Replica 1 → 90
Replica 2 → 90

Now imagine two users trying to read that value.

T1:

User 1 reads from the primary database and gets 100.

T2:

User 2 reads from Replica 1 and gets 90.

Both users are reading the same record, but they are getting different values.

This happens because the replicas haven't received the latest update yet.

After some time, replication catches up.

Primary Database → 100
Replica 1 → 100
Replica 2 → 100

Now all three databases have the same value.

This is an example of Eventual Consistency.

It means different replicas may temporarily have different values, but eventually they become consistent if updates stop and replication completes successfully.

Availability vs. Consistency

Here, we have an interesting trade-off.

Suppose a replica hasn't received the latest update yet.

We have two possible approaches.

Option 1: Allow the read

We can allow the user to read from that replica, even though it has older data.

The user gets a response immediately, but the value might be stale.

This favors availability, although it doesn't guarantee that the user gets the latest data.

Option 2: Wait for the latest data

We can wait until the replica catches up, or route the request to a database that has the required version.

The user gets fresher data, but the request may take longer or become unavailable if the required data cannot be reached.

So, depending on the system's requirements, we may need to make trade-offs between availability and the consistency of reads, especially during network failures.

What's the Difference?

The main thing I understood is that these two concepts focus on different problems.

ACID Consistency → Correctness

It makes sure the database follows its defined rules and constraints. A transaction should not leave the data in an invalid state.

Read Consistency → Freshness of Data

It defines what values a reader is allowed to see, especially when data is replicated across multiple servers.

Availability is related to read consistency because allowing reads from replicas with slightly older data can help a system continue serving requests.

So, ACID consistency is mainly about keeping data correct, while consistency in distributed reads is about how up-to-date and consistent the data needs to be across different replicas.

Isolation

Until now, we have seen how transactions maintain atomicity and consistency.

But what happens when multiple transactions run at the same time?

Let's take our restaurant example.

Suppose two waiters are taking orders from different tables. Both are using the same billing system.

Now imagine only one pizza is left in stock, and both waiters are trying to place an order for it.

If both transactions read the available quantity at the same time, they might both think the pizza is available.

This is where Isolation comes in.

Isolation controls how multiple transactions running at the same time interact with each other.

Ideally, we want the result of concurrent transactions to be the same as some valid order of executing them one after another. But running every transaction one after another can affect performance.

So databases provide different isolation levels.

Before understanding them, let's look at four common problems that happen when transactions execute concurrently.

Dirty Read
Non-Repeatable Read
Phantom Read
Lost Update

1. Dirty Read

A dirty read happens when a transaction reads a change made by another transaction that hasn't committed yet.

Let's take a sales table.

pid	qnt	price
P1	10	5
P2	20	4

Suppose two transactions are running.

Time	TX1	TX2
T1	`BEGIN;`
T2	`SELECT pid, qnt * price FROM sales;` → P1: 50, P2: 80
T3		`BEGIN;`
T4		`UPDATE sales SET qnt = qnt + 5 WHERE pid = 'P1';`
T5	`SELECT SUM(qnt * price) FROM sales;` → 155
T6		`ROLLBACK;`
T7	`COMMIT;`

At T5, TX1 reads the updated value from TX2 even though TX2 hasn't committed yet.

Later, TX2 rolls back its changes, but TX1 has already used that uncommitted data.

This is called a Dirty Read.

PostgreSQL note: This example represents a database that allows dirty reads. PostgreSQL doesn't allow them, even with Read Uncommitted.

2. Non-Repeatable Read

A non-repeatable read happens when a transaction reads the same record twice and gets different values because another transaction committed an update in between.

Let's use the same Sales table.

Time	TX1	TX2
T1	`BEGIN;`
T2	`SELECT pid, qnt * price FROM sales;` → P1: 50, P2: 80
T3		`BEGIN;`
T4		`UPDATE sales SET qnt = qnt + 5 WHERE pid = 'P1';`
T5		`COMMIT;`
T6	`SELECT SUM(qnt * price) FROM sales;` → 155
T7	`COMMIT;`

Here, TX1 initially reads the sales values.

Meanwhile, TX2 updates the quantity and commits.

When TX1 later calculates the total, it sees the updated data.

So TX1 gets different results within the same transaction.

This demonstrates the effect of a non-repeatable read. Strictly speaking, the standard definition involves reading the same row twice; here, the second statement is an aggregation over the changed rows.

This can happen under PostgreSQL's default Read Committed isolation level.

3. Phantom Read

A phantom read happens when a transaction executes a query and later gets a different set of matching rows because another transaction inserts, deletes, or changes matching records.

Suppose we have this table:

id	ch
1	a
2	a
3	b
4	b

Time	TX1	TX2
T1	`BEGIN;`
T2	`SELECT * FROM test WHERE ch = 'b';` → 2 rows
T3		`BEGIN;`
T4		`INSERT INTO test (id, ch) VALUES (5, 'b');`
T5		`COMMIT;`
T6	`SELECT * FROM test WHERE ch = 'b';` → 3 rows
T7	`COMMIT;`

Initially, TX1 gets two records.

Meanwhile, TX2 inserts another record with ch = 'b' and commits.

When TX1 executes the same query again, it gets three records.

The newly appearing record is called a phantom row.

PostgreSQL's Repeatable Read prevents phantom reads because the transaction continues using the same snapshot.

4. Lost Update

A lost update happens when two transactions read the same value and update it independently, causing one transaction's change to be overwritten.

Suppose P1's current quantity is 10.

TX1 wants to increase it by 10, and TX2 wants to increase it by 5.

The expected final quantity is 25.

Time	TX1	TX2
T1	`BEGIN;`
T2	`SELECT qnt FROM sales WHERE pid = 'P1';` → 10
T3		`BEGIN;`
T4		`SELECT qnt FROM sales WHERE pid = 'P1';` → 10
T5	`UPDATE sales SET qnt = 20 WHERE pid = 'P1';`
T6		`UPDATE sales SET qnt = 15 WHERE pid = 'P1';` → Waits for TX1
T7	`COMMIT;`
T8		TX2's UPDATE completes
T9		`COMMIT;`

The final quantity becomes 15 instead of 25.

TX2 overwrites the value written by TX1, so TX1's update is lost.

This is called a Lost Update.

This can happen when an application calculates a new value using an earlier read and then writes that fixed value back.

One way to prevent it is by using SELECT FOR UPDATE.

BEGIN;

SELECT qnt
FROM sales
WHERE pid = 'P1'
FOR UPDATE;

UPDATE sales
SET qnt = qnt + 10
WHERE pid = 'P1';

COMMIT;

Here, PostgreSQL locks the selected row. Another transaction trying to update the same row must wait.

If we simply want to increment a value, we can also do it directly:

UPDATE sales
SET qnt = qnt + 5
WHERE pid = 'P1';

This avoids calculating the new value using an outdated application-side read.

Database Isolation Levels

Now that we have seen these problems, let's understand how databases prevent them.

SQL defines four common isolation levels:

Read Uncommitted
Read Committed
Repeatable Read
Serializable

1. Read Uncommitted

This is the weakest standard isolation level.

A transaction may read changes made by another transaction before they are committed.

So dirty reads, non-repeatable reads, and phantom reads are possible.

For example, in our Dirty Read scenario, TX1 could read the updated sales total even though TX2 later rolls back.

We can request this isolation level using:

BEGIN TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED;

However, PostgreSQL treats Read Uncommitted as Read Committed.

So we cannot reproduce dirty reads in PostgreSQL.

2. Read Committed

Read Committed prevents dirty reads.

A transaction only reads committed data.

However, each statement gets a new snapshot, so the same query may return different results when executed twice.

Let's take our sales example.

Time	TX1	TX2
T1	`BEGIN ISOLATION LEVEL READ COMMITTED;`
T2	`SELECT pid, qnt * price FROM sales;` → P1: 50, P2: 80
T3		`BEGIN;`
T4		`UPDATE sales SET qnt = qnt + 5 WHERE pid = 'P1';`
T5		`COMMIT;`
T6	`SELECT SUM(qnt * price) FROM sales;` → 155
T7	`COMMIT;`

Even though TX1 hasn't finished, its next query sees the changes committed by TX2.

This is allowed because Read Committed creates a new snapshot for each statement.

Read Committed is PostgreSQL's default isolation level.

3. Repeatable Read

In Repeatable Read, a transaction continues reading from the same snapshot.

Let's use the same example.

Time	TX1	TX2
T1	`BEGIN ISOLATION LEVEL REPEATABLE READ;`
T2	`SELECT pid, qnt * price FROM sales;` → P1: 50, P2: 80
T3		`BEGIN;`
T4		`UPDATE sales SET qnt = qnt + 5 WHERE pid = 'P1';`
T5		`COMMIT;`
T6	`SELECT SUM(qnt * price) FROM sales;` → 130
T7	`COMMIT;`

Notice the difference.

Even though TX2 committed its update, TX1 still calculates the total using its original snapshot.

This happens because PostgreSQL uses a consistent snapshot established when the transaction first executes a statement that needs one.

So changes committed by other transactions after that snapshot aren't visible to TX1.

One interesting thing about PostgreSQL is that Repeatable Read also prevents phantom reads.

This is stronger than the minimum Repeatable Read guarantees described by the SQL standard.

However, Repeatable Read doesn't prevent every possible concurrency anomaly.

For example, write skew can still happen.

4. Serializable

Serializable is the strongest standard isolation level.

It ensures that concurrent transactions produce a result equivalent to executing those transactions one after another in some order.

This doesn't mean PostgreSQL actually runs every transaction sequentially.

Transactions can still run concurrently.

But PostgreSQL checks for dangerous dependencies that could produce a result that isn't equivalent to any serial execution.

PostgreSQL uses Serializable Snapshot Isolation (SSI).

Let's take an example.

Suppose we have two records.

Record	Value
A	100
B	100

Our business rule is that at least one record must always have a value of 100.

Two transactions are running at Serializable isolation.

Time	TX1	TX2
T1	`BEGIN ISOLATION LEVEL SERIALIZABLE;`
T2	`SELECT value FROM test WHERE id IN ('A', 'B');` → 100, 100
T3		`BEGIN ISOLATION LEVEL SERIALIZABLE;`
T4		`SELECT value FROM test WHERE id IN ('A', 'B');` → 100, 100
T5	`UPDATE test SET value = 0 WHERE id = 'A';`
T6		`UPDATE test SET value = 0 WHERE id = 'B';`
T7	`COMMIT;`
T8		`COMMIT;` → Serialization failure

Both transactions initially read A = 100 and B = 100.

TX1 changes A to 0.

TX2 changes B to 0.

If both transactions committed successfully, we would have:

A = 0
B = 0

This would violate our business rule.

This kind of anomaly is called Write Skew.

Under PostgreSQL Repeatable Read, both transactions may successfully commit because they update different rows.

But PostgreSQL Serializable can detect the dangerous dependency and abort one transaction.

We may see an error like:

ERROR: could not serialize access due to
read/write dependencies among transactions

When this happens, the application needs to retry the entire transaction.

Serializable is useful when we need stronger correctness guarantees, but we should also be prepared to handle transaction retries.

Pessimistic and Optimistic Concurrency Control

While learning about isolation, I also came across two ways of handling concurrent updates.

Pessimistic Concurrency Control

Here, we lock the data before changing it.

For example:

BEGIN;

SELECT qnt
FROM sales
WHERE pid = 'P1'
FOR UPDATE;

UPDATE sales
SET qnt = qnt + 10
WHERE pid = 'P1';

COMMIT;

The FOR UPDATE statement locks the selected row.

Another transaction trying to acquire a conflicting lock must wait until the first transaction releases it.

This is useful when conflicts are likely.

Optimistic Concurrency Control

In optimistic concurrency control, we allow transactions to proceed without locking the record exclusively in advance.

Before applying the update, we check whether another transaction has changed the data.

If a conflicting change is detected, the operation may fail and need to be retried.

This approach can be useful when conflicts are less frequent.

PostgreSQL's Serializable Snapshot Isolation also uses conflict detection, although its implementation is different from application-level version checking.

Comparison of Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible under SQL standard
Serializable	Prevented	Prevented	Prevented

This table describes the general SQL-standard guarantees.

PostgreSQL has two important differences:

Read Uncommitted behaves like Read Committed, so dirty reads are prevented.
Repeatable Read also prevents phantom reads.

Lost updates need separate consideration because their behavior depends on the type of update and the database's concurrency-control mechanisms.

What I Understood About Isolation

Isolation is not simply about making transactions run one after another.

It is about controlling how concurrent transactions interact.

Different isolation levels provide different guarantees, and the right choice depends on what our application needs.

For many applications, Read Committed is sufficient.

But when multiple queries need to see a consistent snapshot, Repeatable Read may be useful.

And when concurrent transactions must behave like some serial execution, Serializable provides the strongest guarantee.

The important thing is to understand what problems each isolation level prevents and choose accordingly.

Durability

Durability means that once a transaction is committed, its changes should not be lost, even if the database crashes or restarts.

Let's take our restaurant example.

Suppose a customer finishes eating, pays the bill, and the waiter confirms the payment in the billing system.

The database successfully commits the transaction.

But immediately after that, the database server crashes.

When the server restarts, the payment information should still be there.

This is what Durability guarantees.

How do databases maintain durability?

Databases use different techniques to make sure committed changes survive failures.

Some common techniques are:

Write-Ahead Logging (WAL)
OS Page Cache and fsync
Checkpoints
Asynchronous Snapshots
Append-Only Files (AOF)

1. Write-Ahead Logging (WAL)

Writing every change directly to the database files can be expensive.

For example, when we update a record, the database may need to modify multiple data pages and indexes.

Instead of writing all these changes to their final locations immediately, databases like PostgreSQL use Write-Ahead Logging (WAL).

The idea is simple.

Before modified data pages are written to disk, the database first records the changes in a log.

Let's see how PostgreSQL handles an UPDATE query.

BEGIN;

UPDATE orders
SET payment_status = 'Paid'
WHERE order_id = 101;

COMMIT;

When PostgreSQL executes this query, it modifies the data page in shared buffers and creates the required WAL records in WAL buffers.

Both are initially in RAM.

Under normal synchronous commit settings, PostgreSQL persists the required WAL before confirming the transaction.

The modified data pages can be written to disk later.

Here is how the process works.

Now suppose the database crashes after COMMIT, before the modified data pages are written to disk.

When PostgreSQL restarts, it can use the persisted WAL records to recover the committed changes.

This is why WAL plays an important role in durability.

2. OS Page Cache and fsync

One interesting thing I learned is that writing data to a file doesn't always mean it has reached the physical disk.

Usually, when a program writes to a file, the operating system may first keep that data in its page cache, which is part of RAM.

Why?

Because writing to RAM is much faster than writing directly to disk. The operating system can write the cached data to disk later.

But how is this related to database durability?

Let's take our PostgreSQL example.

When PostgreSQL executes an UPDATE query, it modifies the data in shared buffers and generates WAL records in WAL buffers.

Both are initially in RAM.

When PostgreSQL writes these WAL records to its WAL file, they may first go through the operating system's page cache.

So even though PostgreSQL has written the WAL to a file, the data might still be sitting in RAM.

The flow looks like this:

Now suppose the WAL has been written to the OS page cache, but it hasn't reached the disk yet.

If the machine suddenly loses power, the cached WAL data may be lost.

And if the modified data page was also only in RAM, PostgreSQL might lose that change.

This is where fsync() comes in.

fsync() is an operating system call that requests the modified data of a file to be synchronized to stable storage.

In simple terms, it helps make sure the data isn't just sitting in the OS page cache.

Under normal durability settings, PostgreSQL uses fsync() or other appropriate synchronization methods to persist the required WAL before confirming a synchronous commit.

So even if the database crashes immediately after COMMIT, PostgreSQL can use the persisted WAL to recover the changes.

The important difference is:

WAL records the changes needed for recovery, while fsync or a similar synchronization mechanism helps make those WAL records durable.

There is also a performance cost. Synchronizing data to storage takes time, so frequent synchronization can increase write latency.

PostgreSQL provides settings such as fsync and synchronous_commit to control this behavior.

3. Checkpoints

We have seen that PostgreSQL can commit a transaction before writing all the modified data pages to their final locations.

But those modified pages cannot remain only in memory forever.

They eventually need to be written to disk.

This is where Checkpoints come in.

A checkpoint is a process where PostgreSQL ensures that dirty data pages (modified in memory but not yet on disk) are written to storage and records a point from which crash recovery can proceed.

For example, suppose the database has been running for several hours and thousands of transactions have modified data.

If the database crashes, PostgreSQL needs to replay WAL records to recover changes that weren't already reflected in the database files.

Without checkpoints, the amount of WAL that needs to be processed during recovery could become very large.

By performing checkpoints periodically, PostgreSQL can reduce the amount of recovery work required.

So checkpoints mainly help with writing modified data pages to disk and controlling crash recovery time.

4. Asynchronous Snapshots

Some databases use snapshots as a persistence mechanism.

Instead of saving every change immediately, they periodically save a snapshot of their data.

For example, Redis supports RDB snapshots.

Suppose a database takes a snapshot at 10:00 AM.

After that, some new records are created at 10:05 AM.

Now imagine the server crashes at 10:06 AM, before the next snapshot is taken.

If snapshots are the only persistence mechanism, those recent changes may be lost.

So the durability guarantee depends on how frequently snapshots are taken and whether other persistence methods are enabled.

5. Append-Only Files (AOF)

Another persistence technique is an Append-Only File (AOF).

Redis supports this mechanism.

Instead of saving the entire dataset after every update, Redis records write operations in an append-only log.

When the server restarts, Redis can use the persisted AOF to rebuild its data.

However, durability depends on how frequently the AOF is synchronized to disk.

For example, Redis can be configured to synchronize after every write or approximately once per second.

So AOF provides another way to recover data, but recent changes may still be lost depending on the persistence settings.

Conclusion

When I started learning about database transactions, I thought it was mostly about BEGIN, COMMIT, and ROLLBACK.

But as I went deeper, I came across some interesting things, especially what happens when two transactions run at the same time and how PostgreSQL handles failures.

There is still a lot more to learn about how databases work internally, but understanding these concepts helped me see what actually happens behind a simple database query.

OpenSearch Under the Hood: How It Actually Works

Anusha Renangi — Sun, 04 Oct 2026 15:31:30 +0000

I have been using OpenSearch for querying and aggregating data at work. Initially, I was just maintaining the code, but after working with it for some time, I started wondering what actually happens behind those queries.

When I search for something, does OpenSearch search the original JSON documents?

And when an aggregation runs across multiple shards, how does OpenSearch get one final result?

So I decided to understand OpenSearch from the inside rather than treating it as just another API that returns search results.

First, why do we need OpenSearch?

Let’s say an application has millions of documents.

We may want to do things like:

Search text
Filter documents
Sort by fields
Group documents
Calculate aggregations

Doing this efficiently over a large amount of data is not the same as simply storing JSON and scanning every document whenever a query comes in.

OpenSearch is designed for search and analytics over large datasets.

To understand how it does this, let’s start with how the data is organized.

Cluster → Nodes → Indexes → Shards → Documents

These terms are easy to mix up when we first start working with OpenSearch.

Here is the simplest way I understand them now.

Cluster

A cluster is a collection of OpenSearch nodes that work together.

A cluster can contain multiple indexes, and each index can be divided into multiple shards that are distributed across the cluster’s nodes.

You can loosely compare an OpenSearch cluster to a database system in the relational-database world, but they are not exactly the same thing.

Node

A node is an OpenSearch server that is part of the cluster.

Nodes provide the compute and storage where shards are placed.

A cluster can have multiple nodes, and a node can hold multiple shards from one or more indexes.

Index

An index is a logical collection of related documents.

It is somewhat similar to a table in a relational database.

For example:
orders
products
users

An index can contain a large number of documents, so OpenSearch divides the index into shards.

Shard

A shard is a partition of an index.

These shards can be distributed across different nodes in the cluster.

Each shard contains a subset of the documents belonging to the index.

Document

A document is the actual piece of data stored in the index.

For example:
{
“name”: “Laptop”,
“region”: “India”,
“amount”: 75000
}

What actually lives inside a shard?

A shard is backed by Apache Lucene.

And OpenSearch doesn’t simply keep our JSON documents somewhere and scan them whenever we search.

It maintains different structures that are useful for different operations.

Inverted Index

Let’s start with search.

Suppose we have these documents:

Document 1: “I love OpenSearch”
Document 2: “OpenSearch is fast”
Document 3: “I love search”

If we stored only the documents in their original form, one simple way to search would be to look through the documents and check which ones contain the word we are searching for.

An inverted index changes the way we look at the data.

Instead of:
Document → Words
we have something more like:
Word → Documents containing that word
For example:
I → 1, 3
love → 1, 3
OpenSearch → 1, 2
is → 2
fast → 2
search → 3

Now if the query is: “OpenSearch”

the inverted index can tell us: OpenSearch → Document 1, Document 2

We don’t have to think of search as scanning every JSON document anymore.

Doc Values

Search is only one type of operation.

Suppose I want to run: SUM(amount)

or sort documents by: amount

For these operations, we need efficient access to field values.

A simplified way to think about them is:

Document amount

Doc 1 75000
Doc 2 45000
Doc 3 90000
Doc 4 30000

_source

Then there is the original JSON.

Suppose we indexed:

{
“name”: “Laptop”,
“region”: “India”,
“amount”: 75000
}

When we retrieve the document, we generally want to get something close to what we originally sent.

That’s what _source is for.

“_source”: {
“name”: “Laptop”,
“region”: “India”,
“amount”: 75000
}
It isn’t simply storing JSON documents and searching through them.

Different data structures are maintained because different operations need different ways of accessing the data.

What happens when we actually send a query?

Now let’s say our orders index has three shards.

Suppose we send a search query.

The request reaches a node, which coordinates the request.

The query is sent to the relevant shards.

Each shard searches its own local data.

Then the results come back.

The coordinating node merges the shard results and produces the final response.

But does OpenSearch always give us the exact global result?

Suppose we run an aggregation:

Group documents by region
Return the top 2 regions by document count

The documents are distributed across multiple shards.

Each shard does its own local processing and sends results back to the coordinating node.

The coordinating node then combines those results.

A value that isn’t among the top candidates from one shard can still become important when the results from all shards are combined.

For example, a region might have a lower count on each individual shard, but when those counts are combined across all shards, that region could have a high global count.

So how can OpenSearch confidently say, “These are the top results across the entire cluster”?

That’s where distributed aggregation gets tricky and sometimes, approximate.

In the next article, we’ll see exactly why this happens and how OpenSearch improves the accuracy of these results.