Database Indexing

Databases are great at storing and retrieving data, but as tables grow, query performance tends to suffer. Large datasets can lead to slow reads, locked writes, and frustrated users.

Indexing is a powerful solution to this problem. When used effectively, indexes can reduce query times from seconds to milliseconds. They help databases find the data they need quickly, improving both speed and user experience. However, poorly implemented or missing indexes can do more harm than good, leading to inefficient queries and system strain.

This article is a practical deep dive into how indexing works under the hood, based on real-world experience. Topics covered are:

• How to create larger datasets in Postgres
• Indexing overview
• SQL Query Planner & Optimizer
• Table Scan vs Index Scan vs Bitmap Index Scan
• Key vs Non-Key Index Columns
• Index Scan vs Index-Only Scan
• Combining sindexes for performance
• How Database Optimizers Decide to Use Indexes
• Create indexes concurrently
• Bloom Filters
• Handling Billion-Row Tables in Production
• Cost of Long-Running Transactions

Creating a Large Dataset in Postgres (1M Rows)

Before diving deep into how indexes improve performance, let’s first set up a realistic dataset to work with. To truly understand the impact of indexing, we need volume — and a table with 1 million rows is perfect for the job.

We’ll start by creating a simple table to store temperature data.

CREATE TABLE temperature (
  temp INT
);

This creates a table with a single column, temp which we'll populate with random temperature values ranging from 0 to 100.

We’ll use PostgreSQL’s generate_series() and random() functions to generate 1 million rows in one shot:

INSERT INTO temperature(temp)
SELECT (random() * 100)::INT
FROM generate_series(1, 1000000);

Run a quick count to make sure the table is properly populated:

SELECT COUNT(*) FROM temperature;

Getting Started with Indexing in PostgreSQL

As your data grows, so does the time it takes to search through it. That’s where indexes come in. An index is like a shortcut — a specially structured data layer that speeds up lookups without scanning the entire table.

What is an Index?

An index is a data structure built on top of an existing table that helps the database engine find rows faster, much like a book index helps you locate topics quickly.

PostgreSQL supports multiple index types, but the two most common are:

• B-Tree: The default and most widely used, ideal for range and equality lookups.
• LSM Tree: This is more common in NoSQL databases (like Cassandra), optimized for write-heavy workloads.

In this guide, we’ll focus on B-Trees, which PostgreSQL uses by default.

Using EXPLAIN ANALYZE to Measure Performance

To understand how queries perform, use:

EXPLAIN ANALYZE SELECT id FROM employees WHERE id = 20;

This shows the query execution plan, telling you if Postgres used an index, how long it took, and whether it had to scan the entire table or not.

Inline Index Lookups Are Super Fast

Let’s say your id column is indexed. Then:

EXPLAIN ANALYZE SELECT id FROM employees WHERE id = 20;

This query is extremely fast. Postgres finds the result directly from the index without touching the full table (called the heap). This is known as an index-only scan or inline query.

Caching (OS-level & Postgres-level) can impact query speed on repeated queries.

Accessing Non-Indexed Columns Slows Things Down

EXPLAIN ANALYZE SELECT name FROM employees WHERE id = 20;

Although we’re filtering by an indexed id, we're selecting the name, which isn’t part of the index. Postgres finds the row using the index but then still needs to access the table (the heap) to fetch the name. This extra step adds overhead.

Pro tip: Use covering indexes (multi-column indexes) to include frequently accessed columns directly in the index.

Therefore, avoid using the SELECT * query as it forces the database to fetch unnecessary columns.

When There’s No Index

EXPLAIN ANALYZE SELECT id FROM employees WHERE name = 'zs';

This is slow because name isn’t indexed. Postgres is forced to do a sequential scan, reading each row one by one. To optimize this, add an index on name.

Beware of Wildcard Queries

EXPLAIN ANALYZE SELECT id FROM employees WHERE name LIKE '%Zs%';

Even if the name column is indexed, this query is very slow. Why?

Because the condition %Zs% prevents the index from being used. It’s a pattern match across the entire string, so Postgres must still do a full table scan to find rows with matching conditions.

Index Creation Takes Time

Creating an index is a heavy operation — especially on large tables — because the database has to read all the rows and build the index structure:

CREATE INDEX idx_employees_name ON employees(name);

The result? Faster queries on name, especially if it's frequently used in filters or joins.

Did you know? In many databases (like MySQL), primary keys are automatically indexed and often stored alongside other indexes for quick access.

Understand the SQL Query Planner & Optimizer

When your SQL query is slow, the first place to look is PostgreSQL’s query planner. The tool for this is the EXPLAIN command.

EXPLAIN SELECT * FROM employees;

This doesn’t run your query — it shows the execution plan: how Postgres intends to execute it, step by step.

A query plan is a roadmap the database engine uses to execute your SQL. Postgres looks at your SQL, your table’s structure, existing indexes, and available statistics — and then picks the fastest path (ideally) to get the data.

Sequential Scan (a.k.a Full Table Scan)

When you see this in EXPLAIN:

Seq Scan on employees

It means Postgres is reading the entire table from top to bottom — a full table scan. This happens with SELECT * when:

• There are no filters
• There’s no usable index
• Or the planner decides scanning the whole table is cheaper than using an index (This is an important point, as indexes are not always used)

In other databases, this is called a Full Table Scan. Same thing.

Postgres can even parallelize this using multiple threads (called Parallel Seq Scan), but it’s still expensive.

One of the quirks of Postgres? The execution plan is read from bottom to top. The bottom-most line is the first operation Postgres performs. Higher lines are based on the results of lower ones.

Understanding the cost Output

When you run:

EXPLAIN SELECT * FROM employees;

You’ll see output like:

Seq Scan on employees  (cost=0.00..12.25 rows=1000 width=64)

Here’s what those numbers mean:

• cost=0.00..12.25: It has two parts: 0.00 (Estimated cost to fetch the first row (this is crucial — slow start = slow UI)) and 12.25 (Estimated total cost to execute the full query)
• rows=1000: How many rows Postgres thinks it’ll retrieve (based on stats)
• width=64: Estimated average size (in bytes) of each row returned

Never rely on SELECT COUNT(*) to guess row sizes—it's expensive. Use EXPLAIN instead.

Performance Tips from the Planner

• Parallel Scans: Postgres may parallelize scans automatically for large tables, thus boosting speed.
• Use indexes for ORDER BY; otherwise, Postgres fetches all rows and then sorts them—very costly.
• Take note of width: The larger your rows, the more memory and bandwidth are consumed, especially when returning results to your backend
• Avoid SELECT * if you have large columns (e.g., blobs)—you're transferring a lot of unnecessary data
• Avoid using INTEGER as a primary key if your table might grow beyond 2 billion rows—go with BIGINT instead

To get the actual query plan, including real execution times, use:

EXPLAIN ANALYZE SELECT * FROM employees;

This runs the query and provides real performance metrics. It’s perfect for debugging purposes.

Understanding the query planner helps you go from “my query is slow” to “here’s exactly why — and how to fix it.” Indexes, filtering, column sizes, and sort operations all play a role.

Table Scan vs Index Scan vs Bitmap Index Scan

When a SQL query runs, the PostgreSQL query planner decides the most efficient path to retrieve your data. That path depends on how many rows match your condition, what indexes exist, and how expensive it is to access the table itself.

Let’s break down the 3 main strategies PostgreSQL uses:

• Sequential Scan (a.k.a. Full Table Scan)
• Index Scan
• Bitmap Index Scan (Unique to Postgres)

Sequential Scan (a.k.a. Full Table Scan)

This is the simplest strategy.

EXPLAIN SELECT name FROM grades WHERE id > 100;

If your query matches a large number of rows, Postgres skips any index and just scans the entire table from top to bottom. Why? Because if it has to visit most rows anyway, using the index (and then going to the table) becomes more expensive than just scanning the whole thing once.

You’ll see this in EXPLAIN as:

Seq Scan on grades

Index Scan

Now, let’s say your query matches just a small subset of rows. Postgres will use an index (if one exists) to quickly find and fetch only the matching rows.

EXPLAIN SELECT name FROM grades WHERE id < 100;

This works great when:

• The index includes the column you’re filtering on
• Only a few rows satisfy the condition

You’ll see this in EXPLAIN as:

Index Scan using grades_id_idx on grades

Bonus: If all required columns are in the index, Postgres doesn’t need to touch the main table (called the “heap”). This is super fast and is called an Index-Only Scan.

Bitmap Index Scan (Unique to Postgres)

This is the clever middle ground — used when:

• Your query matches more rows than ideal for an Index Scan
• But not so many that a Sequential Scan is cheaper

EXPLAIN SELECT name FROM grades WHERE grade > 90;

Here’s how it works:

• Postgres scans the index and marks matching pages in a bitmap
• Once it has the bitmap, it jumps to the table and pulls all the marked pages in bulk
• It then filters out non-matching rows from those pages
• You’ll also see a Recheck Cond step where it double-checks the filter condition

You’ll see this in EXPLAIN as:

Bitmap Index Scan on grades_grade_idx
Bitmap Heap Scan on grades

This combo improves performance by reducing random disk access — it batches reads from the table.

Multiple Indexes? Bitmap Scan Has Your Back

Bitmap scans can combine multiple indexes to satisfy a single query.

EXPLAIN SELECT name FROM grades WHERE grade > 90 AND id < 10000;

• Postgres creates two bitmaps: one for grade > 90, one for id < 10000
• It performs an AND operation on these two bitmaps
• Then it jumps to the table with the resulting bitmap to fetch the data

This is incredibly efficient for complex queries where multiple indexes are involved.

Key vs Non-Key Index Columns

When creating indexes in a relational database like PostgreSQL, it’s important to understand the difference between key and non-key columns within an index — and how each affects query performance.

What is a Key Column?

Key columns are the columns you explicitly index. PostgreSQL builds a data structure (like a B-tree) on top of those fields. These are the columns used for searching or filtering in your queries.

For example:

CREATE INDEX grade_idx ON students(grade);

Here, grade is the key column, and the database will use this index to quickly find rows that match a WHERE grade = ... condition.

When the database uses this index, it will follow row pointers from the index back to the table (called the heap) to retrieve additional data.

What is a Non-Key Column?

Non-key columns are additional fields that you include in the index — not for filtering or searching, but to avoid extra table lookups.

This is a PostgreSQL-specific feature (not available in all databases).

For example:

CREATE INDEX grade_idx ON students(grade) INCLUDE (id);

Here:

• grade is the key column
• id is the non-key column (also called a covering column)

This means that if your query selects both grade and id, the database can satisfy the entire query just from the index, without touching the table at all—this is known as an Index-Only Scan.

Performance implications for this are that you now’ve faster queries when all requested columns are present in the index. This reduces I/O by avoiding table (heap) access. Most helpful for large tables where table lookups are expensive, but larger indexes may not fit entirely in memory, which may slow things down.

Use non-key columns to avoid hitting the disk, but don’t bloat your indexes unnecessarily.

You don’t always need to use non-key columns, but knowing when and how to use them can give you an edge in performance-critical scenarios. If you’re building a query that frequently fetches additional fields along with the indexed one, including those extra columns in your index can save valuable milliseconds at scale.

Analyzing Query Performance

To see how PostgreSQL executes a query and whether it uses indexes, run:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT id, grade 
FROM students 
WHERE grade > 80 AND grade < 95 
ORDER BY grade DESC 
LIMIT 1000;

• ANALYZE gives you the actual runtime
• BUFFERS shows whether the query hit the cache or disk

Note that repeated queries may hit the cache (database or OS), which makes them look faster than they really are.

Vacuum

PostgreSQL retains deleted or updated rows until they’re cleaned up. To keep your table and indexes optimized:

VACUUM (VERBOSE) students;

This command helps Postgres remove dead tuples and is especially important after lots of inserts/updates.

PostgreSQL usually runs vacuum in the background automatically, but running it manually can help you simulate production behavior more closely.

Index Scan vs Index-Only Scan

Understanding how PostgreSQL decides whether to perform an Index Scan or an Index-Only Scan is crucial for writing high-performance queries.

Sequential Scan (The Default)

When there’s no index on the filtered column, PostgreSQL will perform a Sequential Scan (a.k.a. full table scan). It checks every row in the table to find matching records.

EXPLAIN ANALYZE 
SELECT name FROM grades WHERE id = 7;

If there’s no index on id, Postgres will scan the entire grades table.

Index Scan

Let’s add an index:

CREATE INDEX id_idx ON grades(id);

Now run the same query:

EXPLAIN ANALYZE 
SELECT name FROM grades WHERE id = 7;

Even though we’re searching by id, and there’s an index, this will still be an Index Scan (not Index-Only). Because we’re selecting the name, which is not stored in the index, PostgreSQL uses the index to find matching rows, then follows row pointers to the table (heap) to fetch the name.

Index Scan = Use index to find row → Fetch additional columns from table (heap).

Index-Only Scan

Now try this query:

EXPLAIN ANALYZE 
SELECT id FROM grades WHERE id = 7;

This results in an Index-Only Scan. Why? Because:

• We’re filtering by id
• We’re selecting only id, which is already in the index
• No need to access the table at all

This is faster since it avoids touching the heap entirely.

Index-Only Scan = Everything needed is already in the index

Using Non-Key Columns for Better Index-Only Scans

You can go one step further. What if you want to select both id and name without causing an Index Scan? Here’s the trick:

DROP INDEX id_idx;

-- Recreate index with 'name' as a non-key (included) column
CREATE INDEX id_idx ON grades(id) INCLUDE (name);

EXPLAIN ANALYZE 
SELECT name FROM grades WHERE id = 7;

Now, it’ll be an Index-Only Scan. The name field is included within the index as a non-key column so PostgreSQL can fetch everything it needs without hitting the table.

EXPLAIN ANALYZE 
SELECT grade FROM grades WHERE id = 7;

If grade wasn’t included in the index, this will result in an Index Scan because Postgres will go to the heap to get grade.

Combining Indexes for Better Performance

Building the right database indexes is key to achieving blazing-fast query performance — especially as your tables scale into the millions of rows.

Get AkashSDas’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Let’s break down how PostgreSQL handles different types of index usage and how combining indexes (composite keys, bitmap scans, etc.) can help — or hurt — your query performance.

Composite Indexes (Multi-Column Indexes)

A composite index is an index that includes more than one column. These are especially efficient for queries that filter on multiple columns using AND conditions.

Let’s say you have a table test(a, b, c) with 12 million rows. You can create a composite index like this:

CREATE INDEX ON test (a, b);

This index will help in specific ways.

EXPLAIN ANALYZE 
SELECT c FROM test WHERE a = 70;

PostgreSQL uses a Bitmap Index Scan on test_a_b_idx. Why? Because a is the left-most column in the index. PostgreSQL can efficiently search from left to right in a composite index. It cannot scan from right to left.

Composite indexes are expensive to build but highly performant when aligned with your query patterns.

Index Scan vs Bitmap Index Scan

Index Scan (small result set)

EXPLAIN ANALYZE 
SELECT c FROM test WHERE a = 70 LIMIT 10;

Since the result set is small, Postgres skips the overhead of building a bitmap and uses a faster Index Scan.

Sequential Scan (right-side only)

EXPLAIN ANALYZE 
SELECT c FROM test WHERE b = 70;

Even though b is in the composite index, PostgreSQL performs a Sequential Scan. Why? Because b is not the first column in the index.

To fix this, create a separate index on b.

Combining Indexes with AND

EXPLAIN ANALYZE 
SELECT c FROM test WHERE a = 70 AND b = 70;

Postgres now performs a very efficient Index Scan on the composite index test_a_b_idx. This is a great use case for composite keys, especially if your app frequently runs similar queries.

BitmapOr and OR Conditions

When filtering with OR, Postgres has a different approach.

EXPLAIN ANALYZE 
SELECT c FROM test WHERE a = 70 OR b = 70;

Even if you have indexes on a and b, Postgres often defaults to a Parallel Sequential Scan. That’s because OR conditions generally return more rows and can’t benefit from linear index filtering.

If you have individual indexes on both:

-- Bitmap Index Scan on both indexes
-- BitmapOr
-- Recheck Cond
-- Bitmap Heap Scan
SELECT c FROM test WHERE a = 70 OR b = 70;

Still slower compared to AND conditions due to the larger result set and re-checking conditions.

Bitmap vs Index Scan Tradeoff

Bitmap Index Scan is used for larger result sets. Builds a bitmap of matching rows, then fetches them in bulk from the heap.

Index Scan is preferred when a small number of rows are expected (e.g. with LIMIT), as bitmap creation has overhead.

For example:

-- Uses Bitmap Scan (more rows)
SELECT c FROM test WHERE a = 70;

-- Uses Index Scan (fewer rows, fast limit)
SELECT c FROM test WHERE a = 70 LIMIT 10;

How Database Optimizers Decide to Use Indexes

So, you’ve created multiple indexes on your table — but how does the database know which one to use? Or whether to use any at all?

That’s where the query planner/optimizer comes in. It analyzes your query and picks the fastest path based on statistics, cost, and estimated row counts. Here’s how it works in practice.

PostgreSQL and other databases use a cost-based optimizer — this means it’ll simulate different execution plans, estimate how many rows each one will return, and pick the cheapest one.

Let’s walk through 3 common cases to understand how this decision is made by the optimizer.

Case 1: Using Multiple Indexes

-- Indexes: f1_idx, f2_idx
SELECT * FROM T WHERE f1 = 1 AND f2 = 4;

In this case, the optimizer decides to use both indexes:

• Uses f1_idx to find row IDs matching f1 = 1
• Uses f2_idx to find row IDs matching f2 = 4
• Then, it merges the row IDs (using Bitmap AND)

This happens when:

• The result set is not too small, so using just one index isn’t enough
• But also not too large, so a full table scan isn’t worth it

This strategy helps avoid scanning irrelevant rows by combining indexes efficiently.

Case 2: Using Only One Index

-- Indexes: f1_idx, f2_idx
SELECT * FROM T WHERE f1 = 1 AND f2 = 4;

In this version, the optimizer chooses just one index, say f1_idx:

• Retrieves rows where f1 = 1
• Goes to the table (heap) and checks if f2 = 4

Why only one?

• Using two indexes would be more expensive in this case
• Maybe f1 is a primary key or highly selective (few rows)
• The statistics show that this route is cheaper overall

Postgres tracks stats like cardinality, most common values, null ratios, etc. to estimate how many rows each condition will return.

Case 3: Using No Indexes (Full Table Scan)

SELECT * FROM T WHERE f1 = 1 AND f2 = 4;

Here, Postgres ignores both f1_idx and f2_idx and goes with a Sequential Scan (i.e., full table scan).

Why?

• The expected number of matching rows is very large, so reading the whole table sequentially is faster than jumping around using indexes.
• Or the stats are outdated or missing, and Postgres doesn’t have good information to make a smarter choice.

Important: Keep Your Stats Fresh

When you bulk-load data, PostgreSQL doesn’t automatically update stats:

VACUUM (VERBOSE) table_name;
ANALYZE table_name;

Running these helps the planner get an accurate picture of your data.

Example: If you insert 300 million rows into an empty table and don’t analyze, Postgres still thinks it only has a few rows — so it may make really bad decisions.

Advanced: Forcing Index Usage (Hints)

PostgreSQL doesn’t support traditional optimizer hints like Oracle or MySQL, but there are ways to nudge the planner:

• Rewrite queries to filter on the most selective condition first
• Use expression indexes if you’re filtering on computed columns
• Set planner cost settings manually (e.g., enable_seqscan = off) in edge cases
• Or use extensions like pg_hint_plan for actual hints

Use these with caution — 99% of the time, the optimizer does a great job on its own.

Create Index Concurrently

Creating indexes in production can be dangerous — it blocks writes to the table while the index is being built. That’s fine in dev… but in production?

The problem with regular index creation:

-- This blocks all INSERTs/UPDATEs/DELETEs while index is being created
CREATE INDEX grade_idx ON grades (grade);

When you run this:

• Reads are fine
• But writes are blocked (and users get locked out!)
• This can be a major issue for high-traffic production systems

To fix this, we’ve to create indexes concurrently:

CREATE INDEX CONCURRENTLY grade_idx ON grades (grade);

Using CONCURRENTLY:

• Postgres performs multiple scans of the table
• It waits for all transactions to finish instead of locking writes
• Writes continue normally during index creation

This is much safer in production, but there’s a tradeoff.

The tradeoffs are that it’s slower (as multiple scans are involved), it requires higher resource usage, and it can fail if you’re building a unique index and duplicates sneak in during the build. If it fails, you need to manually DROP INDEX and recreate. The index might end up in an invalid state if duplicates exist (especially for UNIQUE constraints).

PostgreSQL core team has discussed making CONCURRENTLY the default behavior for index creation in the future—because the risks of locking writes during index creation are just too high in real-world systems.

Bloom Filters

Ever had a simple query like “does this username exist?” and wondered why it hits the DB every time?

GET /username?q=paul

That call might look harmless… until you scale.

The Naive Approach

1. Receive request → query the database
2. DB grows → performance degrades
3. Maybe you cache usernames in Redis. Now you’ve: Users in Postgres, Usernames in Redis. Double storage = wasted memory.

Using Bloom Filters

A Bloom Filter is a space-efficient, in-memory, probabilistic data structure to test whether an element might be in a set.

It lets you:

• Say NO with 100% confidence
• Say MAYBE (with a false positive risk)

Not meant to store data, only test for existence

Example: 64-bit Bloom Filter

Let’s say we use a 64-bit integer as our bloom filter:

Insertion (paul):

Hash(paul) % 64 = 3
Set the 3rd bit to 1

Lookup (paul):

Hash(paul) % 64 = 3
If bit 3 is 0 → DEFINITELY not in DB
If bit 3 is 1 → MAYBE in DB → Query DB

You avoid querying the DB when bit is 0! That’s the win.

Cassandra uses them in consistent hashing to avoid unnecessary disk I/O. Great for cache misses, deduplication, input validation, etc.

Tradeoffs

With all of its benefits like super memory efficient, fast to check for existence and thus avoiding unnecessary DB hits, it has few considerable drawbacks. The big one is false positives. If too many bits are set (i.e., too many collisions), everything starts returning “maybe” → you hit the DB every time → defeats the purpose. So, size it right.

Others are that there’s no way to delete elements and that it needs tuning, i.e. hash funcs + size.

The actual implementation of Bloom Filters uses multiple hash functions (e.g., 3 different mod positions). The bit arrays are larger than 64 bits (usually thousands or more). Also, more complex structures like Counting Bloom Filters (for deletes).

Handling Billion-Row Tables in Production

You’re working with a table that’s ballooning toward billions of rows. What are your options? You can do the following things:

• Brute Force (Big Data style)
• Smart Subset Processing (what you actually want)
• Rethink the Data Model

Brute Force (Big Data Style)

Process everything using raw compute.

• Split table into chunks
• Run parallel processing (via multiprocessing or distributed computing)
• This is how tools like Hadoop or MapReduce work

The downside to this approach is that it’s expensive in compute, has higher latency, and all in all, it isn’t scalable for frequent querying.

Smart Subset Processing (What You Actually Want)

Instead of scanning the whole table, reduce the data using these 3 layered strategies:

• Indexing: It’s a DB optimization as indexes act like a shortcut to jump to the right rows, and we still operate on the entire table, just smartly.
• Partitioning: Cut the table up into smaller chunks on disk. This breaks the table into vertical slices (based on a range or hash). The benefit is that you search fewer partitions, and each partition has its indexes.
• Sharding: Distribute partitions across multiple machines. This is horizontal scaling, where you spread rows across DB nodes. It has some serious issues, like your client logic must handle shard routing, and then you’ve cross-shard joins, transactions, and aggregations get complex

Hierarchy of optimization:

Sharding → Partitioning → Indexes → Disk Pages

Rethink the Data Model

Ask yourself: Why does this table have billions of rows? Sometimes, the best optimization is:

• Archival (move old rows out of hot table)
• Data retention policies
• Summary tables or materialized views
• Denormalization or pre-aggregation

Design your schema to age out stale rows and avoid keeping everything forever

Cost of Long-Running Transactions

When a long-running transaction modifies millions of rows and then fails/rolls back, it’s not just a “whoops.” There’s a very real cost — in performance, disk usage, and recovery complexity.

MVCC: How Postgres Handles Row Updates

Postgres uses Multi-Version Concurrency Control (MVCC). That means:

• Every UPDATE or DELETE it creates a new row version (a.k.a. a tuple)
• The old version stays behind until cleaned up
• Indexes that point to the row must update to reference the new version

If the updated row can fit on the same page, Postgres may use a HOT (Heap-Only Tuple) update — no index change is needed. But only possible if: There’s free space on the page (determined by the table’s fill factor)

What Happens on Transaction Rollback?

Let’s say a transaction touched millions of rows and eventually rolled back:

• All the new row versions are now invalid (dead)
• But… they still exist on disk and in memory
• Postgres doesn’t clean them up immediately

Why? Because Postgres uses a lazy cleanup strategy → VACUUM

VACUUM

ACUUM is a background task that scans for dead tuples, removes them (if safe), and thus reclaiming disk space; But until then… pages are bloated with dead rows and you waste I/O reading pages where 99% of the rows are garbage as every read has to check: “Is this tuple from a committed transaction?”. This visibility check adds overhead

The worst case is when you have a page with 1 live row + 999 dead. That’s a full disk read for just 1 usable row.

Use VACUUM and ANALYZE aggressively in write-heavy systems and monitor dead tuples with pg_stat_user_tables.

Other Databases: Eager Cleanup (Undo Logs)

Some databases (like Oracle, SQL Server, MySQL InnoDB) go with the eager strategy:

• Use undo logs to roll back transactions immediately
• Database won’t even start unless rollback finishes
• Cleaner disk state but longer downtime/startup time