Slow database? How to create PostgreSQL indexes

Why your database needs to search row by row

When you run a query on a database table without any optimization, the system performs what we call a table scan. In practice, this means the database opens the entire table and reads every record one by one, from the first to the last, to check if it matches your search criteria. If you have ten thousand rows, the work is substantial. If you have ten million rows, the application freezes and the page takes seconds to load.

To avoid this unnecessary effort, we use indexes. A database index works just like an index at the back of a textbook: instead of reading every page to find a concept, you go straight to the indicated page. In PostgreSQL, the default structure used for this purpose is the B-Tree, a balanced tree that guarantees extremely fast searches while keeping data ordered and accessible in logarithmic time, completely changing system scalability.

Before applying any mechanical optimization, it is essential to audit the real behavior of your queries to understand where the bottleneck lies, a concept we detail in the article on how to audit queries in PostgreSQL. The database engine needs accurate diagnostics so that index creation brings the expected performance boost without introducing hidden operational costs to write operations.

How the internal mechanics of B-Tree trees work

The acronym B-Tree stands for Balanced Tree. Under the hood, it organizes data into a hierarchy of blocks or nodes, divided into root nodes, intermediate nodes, and leaves. Each node holds search keys and pointers to other nodes or to the exact row in the physical table. The secret of the structure is that all paths from the root to the leaves have exactly the same distance, ensuring predictable and very fast behavior.

Imagine you are looking for the number 42 in a sorted list of one million items. Instead of looking one by one, the B-Tree looks at the central node and asks: is the number greater or smaller than 500 thousand? Since it is smaller, it instantly discards half of the data. The process repeats in successive divisions until it finds the leaf page where the data resides. In practice, instead of reading millions of records, PostgreSQL reads only half a dozen blocks on disk.

This reading gain, however, requires constant maintenance by the storage engine whenever data changes. When a record is inserted or modified, the tree must reorganize itself to maintain balance. This overhead is the primary reason why we should not create indexes on absolutely every column, maintaining a healthy balance with other infrastructure needs, as discussed when investigating how to tune your connection pool under heavy loads.

The hidden cost of creating excessive indexes

There is a common myth that adding indexes to every column in a table solves any slowness issue. In practice, this creates a dangerous trap for overall application performance. Every time an INSERT, UPDATE, or DELETE command runs, PostgreSQL does not just alter the row in the main table; it must rigorously update every index associated with that table. If a table has ten indexes, a single insertion generates eleven distinct disk writes.

This phenomenon generates write amplification, elevating disk usage, consuming more RAM, and causing block fragmentation over time. Additionally, indexes occupy physical disk space. In large databases, index files can easily surpass the size of the raw data itself, exhausting precious storage resources and reducing the efficiency of the server's memory cache.

Therefore, choosing which columns to index must be strategic and based on real usage data. Columns that undergo constant changes and rarely appear in search clauses or JOINs should stay far away from indexes. The goal is to accelerate critical queries without turning write operations into an insurmountable bottleneck for the database.

How to choose the right columns for indexing

To get the maximum return on performance investment, you should concentrate indexing efforts on columns with high cardinality. Cardinality is a technical term indicating the degree of uniqueness of values in a column. A column like 'user_id' or 'email' has high cardinality because almost all values are unique. In contrast, an 'is_active' status column with only two values (true or false) has low cardinality, making the index unhelpful for the query planner.

Another ideal scenario for indexing involves columns frequently used in WHERE, JOIN, and ORDER BY clauses. Foreign keys relating main tables are also natural candidates, preventing full scans during complex joins. When PostgreSQL needs to cross large tables, an index on the join key reduces processing time from minutes to milliseconds.

Constant database monitoring helps identify which queries consume the most resources and which filters recur most often. With this information in hand, creating indexes shifts from guesswork to precise optimization engineering, ensuring the system responds agilely regardless of data volume growth. To explore these architectural concepts further in production, see our related technical analysis on How to test SMD resistors on a circuit board with multimeter.

Final considerations on query optimization

Mastering the internal mechanics of B-Tree trees and understanding index behavior in PostgreSQL transforms how we design scalable systems. Avoiding the dreaded table scan is not just a matter of applying random commands, but understanding the trade-off between read speed and write cost that each index introduces to the architecture.

Maintaining a routine of analyzing execution plans, combined with the judicious use of indexes based on cardinality and real application access patterns, guarantees a healthy, responsive database prepared to grow sustainably without unpleasant surprises in production.